(oauth2 + email added): a mess that may or may not really work and needs to be refactored...

13 files changed, 226 insertions, 64 deletions

diff --git a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
index 3f5fb40..3d4d84c 100644
--- a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
+++ b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
@@ -3,47 +3,84 @@ create extension if not exists citext;
 create schema if not exists secd;
 
 create table if not exists secd.identity (
-       identity_id          bigserial          primary key
+       identity_id bigserial primary key
        , identity_public_id uuid
        , data text
-       , created_at         timestamptz not null
+       , created_at timestamptz not null
+       , deleted_at timestamptz
        , unique(identity_public_id)
 );
 
-create table if not exists secd.email (
-       email_id bigserial primary key
-       , address text not null
-       , unique(address)
+create table if not exists secd.session (
+       session_id bigserial primary key
+       , identity_id bigint not null references secd.identity(identity_id)
+       , secret_hash bytea not null
+       , created_at timestamptz not null
+       , expired_at timestamptz
+       , revoked_at timestamptz
+       , unique(secret_hash)
 );
 
-create table if not exists secd.identity_email (
-       identity_email_id bigserial primary key
-       , identity_id bigint not null references secd.identity(identity_id)
-       , email_id bigint not null references secd.email(email_id)
+create table if not exists secd.oauth_provider (
+       oauth_provider_id serial primary key
+       , name text not null
+       , flow text not null
+       , base_url text not null
+       , response_type text not null
+       , default_scope text
+       , client_id text not null
+       , client_secret text not null
+       , redirect_url text not null
        , created_at timestamptz not null
        , deleted_at timestamptz
+       , unique (name, flow)
+);
+
+create table if not exists secd.oauth_validation (
+       oauth_validation_id bigserial primary key
+       , oauth_validation_public_id uuid not null
+       , oauth_provider_id integer not null references secd.oauth_provider(oauth_provider_id)
+       , access_token text
+       , raw_response text
+       , created_at timestamptz not null
+       , validated_at timestamptz
+       , unique (oauth_validation_public_id)
+);
+
+create table if not exists secd.identity_oauth_validation (
+       identity_oauth_validation_id bigserial primary key
+       -- A validation does not require an identity to initiate
+       , identity_id bigint references secd.identity(identity_id)
+       , oauth_validation_id bigint not null references secd.oauth_validation(oauth_validation_id)
+       , revoked_at timestamptz
+       , deleted_at timestamptz
+       , unique(identity_id, oauth_validation_id)
+);
+
+create table if not exists secd.email (
+       email_id bigserial primary key
+       , address text not null
+       , unique(address)
 );
 
 create table if not exists secd.email_validation (
        email_validation_id bigserial primary key
        , email_validation_public_id uuid not null
-       , identity_email_id integer not null references secd.identity_email(identity_email_id)
-       , attempts integer not null
+       , email_id bigint not null references secd.email(email_id)
        , code text
-       , is_validated boolean not null default false
+       , is_oauth_derived boolean not null
        , created_at timestamptz not null
-       , expires_at timestamptz
-       , revoked_at timestamptz
+       , validated_at timestamptz
+       , expired_at timestamptz
        , unique(email_validation_public_id)
 );
 
-create table if not exists secd.session (
-       session_id bigserial primary key
-       , identity_id bigint not null references secd.identity(identity_id)
-       , secret_hash bytea not null
-       , created_at timestamptz not null
-       , touched_at timestamptz not null
-       , expires_at timestamptz
+create table if not exists secd.identity_email_validation (
+       identity_email_validation_id bigserial primary key
+       -- A validation does not require an identity to initiate
+       , identity_id bigint references secd.identity(identity_id)
+       , email_validation_id bigint not null references secd.email_validation(email_validation_id)
        , revoked_at timestamptz
-       , unique(secret_hash)
+       , deleted_at timestamptz
+       , unique(identity_id, email_validation_id)
 );
diff --git a/crates/secd/store/pg/sql/find_email_validation.sql b/crates/secd/store/pg/sql/find_email_validation.sql
index 96a8cc4..1eb3e43 100644
--- a/crates/secd/store/pg/sql/find_email_validation.sql
+++ b/crates/secd/store/pg/sql/find_email_validation.sql
@@ -2,16 +2,17 @@ select
         ev.email_validation_public_id
         , i.identity_public_id
         , e.address
-        , ev.attempts
         , ev.code
-        , ev.is_validated
+        , ev.is_oauth_derived
         , ev.created_at
-        , ev.expires_at
-        , ev.revoked_at
-from secd.email_validation ev
-join secd.identity_email   ie using (identity_email_id)
-join secd.email            e  using (email_id)
-join secd.identity         i  using (identity_id)
+        , ev.expired_at
+        , ev.validated_at
+        , iev.revoked_at
+        , iev.deleted_at
+from secd.email_validation               ev
+join secd.email                          e   using (email_id)
+left join secd.identity_email_validation iev using (email_validation_id)
+left join secd.identity                  i   using (identity_id)
 where (($1 is null) or (email_validation_public_id = $1))
 and   (($2 is null) or (code = $2));
 --
diff --git a/crates/secd/store/pg/sql/find_identity.sql b/crates/secd/store/pg/sql/find_identity.sql
index f4c9cbf..135ff9a 100644
--- a/crates/secd/store/pg/sql/find_identity.sql
+++ b/crates/secd/store/pg/sql/find_identity.sql
@@ -1,9 +1,11 @@
 select
-        identity_public_id,
-        data,
-        i.created_at
+        identity_public_id
+        , data
+        , i.created_at
+        , i.deleted_at
 from secd.identity       i
-join secd.identity_email ie using (identity_id)
-join secd.email          e  using (email_id)
+join secd.identity_email_validation iev using (identity_id)
+join secd.email_validation          ev  using (email_validation_id)
+join secd.email                     e   using (email_id)
 where (($1 is null) or (i.identity_public_id = $1))
-and   (($2 is null) or (e.address = $2))
+and   (($2 is null) or (e.address = $2));
diff --git a/crates/secd/store/pg/sql/find_identity_by_code.sql b/crates/secd/store/pg/sql/find_identity_by_code.sql
index e016a0e..e5a0970 100644
--- a/crates/secd/store/pg/sql/find_identity_by_code.sql
+++ b/crates/secd/store/pg/sql/find_identity_by_code.sql
@@ -1,4 +1,4 @@
-select identity_email_id
+select identity_email_validation_id
 from secd.email_validation
 where email_validation_public_id = $1::uuid
 --
@@ -8,4 +8,4 @@ select
         , i.created_at
 from secd.identity            i
 left join secd.identity_email ie using (identity_id)
-where ie.identity_email_id = $1;
+where ie.identity_email_validation_id = $1;
diff --git a/crates/secd/store/pg/sql/read_oauth_provider.sql b/crates/secd/store/pg/sql/read_oauth_provider.sql
new file mode 100644
index 0000000..edaa114
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_oauth_provider.sql
@@ -0,0 +1,12 @@
+select flow
+       , base_url
+       , response_type
+       , default_scope
+       , client_id
+       , client_secret
+       , redirect_url
+       , created_at
+       , deleted_at
+from secd.oauth_provider
+where name = $1
+and flow = $2;
diff --git a/crates/secd/store/pg/sql/read_oauth_validation.sql b/crates/secd/store/pg/sql/read_oauth_validation.sql
new file mode 100644
index 0000000..d8361ea
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_oauth_validation.sql
@@ -0,0 +1,23 @@
+select oauth_validation_public_id
+       , i.identity_public_id
+       , ov.access_token
+       , ov.raw_response
+       , ov.created_at
+       , ov.validated_at
+       , iov.revoked_at
+       , iov.deleted_at
+       , op.name          as oauth_provider_name
+       , op.flow          as oauth_provider_flow
+       , op.base_url      as oauth_provider_base_url
+       , op.response_type as oauth_provider_response_type
+       , op.default_scope as oauth_provider_default_scope
+       , op.client_id     as oauth_provider_client_id
+       , op.client_secret as oauth_provider_client_secret
+       , op.redirect_url  as oauth_provider_redirect_url
+       , op.created_at    as oauth_provider_created_at
+       , op.deleted_at    as oauth_provider_deleted_at
+from secd.oauth_validation               ov
+join secd.oauth_provider                 op  using(oauth_provider_id)
+left join secd.identity_oauth_validation iov using(oauth_validation_id)
+left join secd.identity                  i   using(identity_id)
+where oauth_validation_public_id = $1;
diff --git a/crates/secd/store/pg/sql/read_validation_type.sql b/crates/secd/store/pg/sql/read_validation_type.sql
new file mode 100644
index 0000000..2eceb98
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_validation_type.sql
@@ -0,0 +1,7 @@
+select 'Email'
+from secd.email_validation
+where email_validation_public_id = $1
+union
+select 'Oauth'
+from secd.oauth_validation
+where oauth_validation_public_id = $1;
diff --git a/crates/secd/store/pg/sql/write_email.sql b/crates/secd/store/pg/sql/write_email.sql
index cdcc971..06a1dc5 100644
--- a/crates/secd/store/pg/sql/write_email.sql
+++ b/crates/secd/store/pg/sql/write_email.sql
@@ -4,8 +4,3 @@ insert into secd.email (
        $1
 ) on conflict (address) do nothing
 returning email_id;
---
-select email_id from secd.email where address = $1;
---
-insert into secd.identity_email (identity_id, email_id, created_at) values ($1, $2, $3);
---
diff --git a/crates/secd/store/pg/sql/write_email_validation.sql b/crates/secd/store/pg/sql/write_email_validation.sql
index d99a04c..ff25b87 100644
--- a/crates/secd/store/pg/sql/write_email_validation.sql
+++ b/crates/secd/store/pg/sql/write_email_validation.sql
@@ -1,27 +1,43 @@
 insert into secd.email_validation
        (
            email_validation_public_id
-           , identity_email_id
-           , attempts
+           , email_id
            , code
-           , is_validated
+           , is_oauth_derived
            , created_at
-           , expires_at
+           , validated_at
+           , expired_at
        )
 values (
            $1
-           , (
-               select identity_email_id
-               from secd.identity_email
-               where identity_id = $2
-               and email_id = $3
-           )
+           , $2
+           , $3
            , $4
            , $5
            , $6
            , $7
-           , $8
 ) on conflict (email_validation_public_id) do update
-  set attempts       = excluded.attempts
-      , is_validated = excluded.is_validated
-      , expires_at   = excluded.expires_at;
+  set validated_at = excluded.validated_at
+      , expired_at   = excluded.expired_at;
+--
+insert into secd.identity_email_validation (
+       identity_id
+       , email_validation_id
+       , revoked_at
+       , deleted_at
+) values (
+       (
+       select identity_id
+       from secd.identity
+       where identity_public_id = $1
+       )
+       , (
+         select email_validation_id
+         from secd.email_validation
+         where email_validation_public_id = $2
+         )
+        , $3
+        , $4
+) on conflict (identity_id, email_validation_id) do update
+  set revoked_at = excluded.revoked_at
+      , deleted_at = excluded.deleted_at;
diff --git a/crates/secd/store/pg/sql/write_identity.sql b/crates/secd/store/pg/sql/write_identity.sql
index 7d53ee1..94a51fe 100644
--- a/crates/secd/store/pg/sql/write_identity.sql
+++ b/crates/secd/store/pg/sql/write_identity.sql
@@ -6,4 +6,6 @@ insert into secd.identity (
        $1,
        $2,
        $3
-);
+) on conflict(identity_public_id) do update
+  set data = excluded.data
+      , deleted_at = excluded.deleted_at;
diff --git a/crates/secd/store/pg/sql/write_oauth_provider.sql b/crates/secd/store/pg/sql/write_oauth_provider.sql
new file mode 100644
index 0000000..ba69857
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_oauth_provider.sql
@@ -0,0 +1,25 @@
+insert into secd.oauth_provider (
+       oauth_provider_id
+       , name
+       , flow
+       , base_url
+       , response_type
+       , default_scope
+       , client_id
+       , client_secret
+       , redirect_url
+       , created_at
+       , deleted_at
+) values (
+       default
+       , $1
+       , $2
+       , $3
+       , $4
+       , $5
+       , $6
+       , $7
+       , $8
+       , $9
+       , $10
+) on conflict (name, flow) do nothing;
diff --git a/crates/secd/store/pg/sql/write_oauth_validation.sql b/crates/secd/store/pg/sql/write_oauth_validation.sql
new file mode 100644
index 0000000..11f2578
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_oauth_validation.sql
@@ -0,0 +1,45 @@
+insert into secd.oauth_validation (
+       oauth_validation_public_id
+       , oauth_provider_id
+       , access_token
+       , raw_response
+       , created_at
+       , validated_at
+) values (
+       $1
+       , (
+         select oauth_provider_id
+         from secd.oauth_provider
+         where name = $2
+         and flow = $3
+       )
+       , $4
+       , $5
+       , $6
+       , $7
+) on conflict (oauth_validation_public_id) do update
+  set access_token = excluded.access_token
+      , validated_at = excluded.validated_at
+      , raw_response = excluded.raw_response;
+--
+insert into secd.identity_oauth_validation (
+       identity_id
+       , oauth_validation_id
+       , revoked_at
+       , deleted_at
+) values (
+       (
+       select identity_id
+       from secd.identity
+       where identity_public_id = $1
+       )
+       , (
+         select oauth_validation_id
+         from secd.oauth_validation
+         where oauth_validation_public_id = $2
+       )
+       , $3
+       , $4
+) on conflict (identity_id, oauth_validation_id) do update
+  set revoked_at = excluded.revoked_at
+      , deleted_at = excluded.deleted_at;
diff --git a/crates/secd/store/pg/sql/write_session.sql b/crates/secd/store/pg/sql/write_session.sql
index 86cde55..1b238c6 100644
--- a/crates/secd/store/pg/sql/write_session.sql
+++ b/crates/secd/store/pg/sql/write_session.sql
@@ -2,8 +2,7 @@ insert into secd.session (
        identity_id
        , secret_hash
        , created_at
-       , touched_at
-       , expires_at
+       , expired_at
        , revoked_at
 ) values (
          (select identity_id from secd.identity where identity_public_id = $1)
@@ -11,8 +10,6 @@ insert into secd.session (
        , $3
        , $4
        , $5
-       , $6
 ) on conflict (secret_hash) do update
-  set touched_at = excluded.touched_at
-      , revoked_at = excluded.revoked_at;
+  set revoked_at = excluded.revoked_at;
 --