refactor everything with more abstraction and a nicer interface

21 files changed, 99 insertions, 228 deletions

diff --git a/crates/secd/store/pg/sql/find_address.sql b/crates/secd/store/pg/sql/find_address.sql
new file mode 100644
index 0000000..5eaafbb
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_address.sql
@@ -0,0 +1,8 @@
+select address_public_id
+       , type
+       , value
+       , created_at
+from secd.address
+where (($1::uuid is null) or (address_public_id = $1))
+and (($2::text is null) or (type = $2))
+and (($3::text is null) or (value = $3));
diff --git a/crates/secd/store/pg/sql/find_address_validation.sql b/crates/secd/store/pg/sql/find_address_validation.sql
new file mode 100644
index 0000000..3874994
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_address_validation.sql
@@ -0,0 +1,18 @@
+select av.address_validation_public_id
+       , i.identity_public_id
+       , a.address_public_id
+       , a.type
+       , a.value
+       , a.created_at
+       , av.method
+       , av.token_hash
+       , av.code_hash
+       , av.attempts
+       , av.created_at
+       , av.expires_at
+       , av.revoked_at
+       , av.validated_at
+from secd.address_validation av
+join secd.address            a  using(address_id)
+left join secd.identity      i  using(identity_id)
+where (($1::uuid is null) or (address_validation_public_id = $1));
diff --git a/crates/secd/store/pg/sql/find_email_validation.sql b/crates/secd/store/pg/sql/find_email_validation.sql
deleted file mode 100644
index 1eb3e43..0000000
--- a/crates/secd/store/pg/sql/find_email_validation.sql
+++ /dev/null
@@ -1,18 +0,0 @@
-select
-        ev.email_validation_public_id
-        , i.identity_public_id
-        , e.address
-        , ev.code
-        , ev.is_oauth_derived
-        , ev.created_at
-        , 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 135ff9a..37105cb 100644
--- a/crates/secd/store/pg/sql/find_identity.sql
+++ b/crates/secd/store/pg/sql/find_identity.sql
@@ -1,11 +1,15 @@
-select
-        identity_public_id
-        , data
-        , i.created_at
-        , i.deleted_at
-from secd.identity       i
-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));
+select distinct
+       identity_public_id
+       , data::text
+       , i.created_at
+       , i.updated_at
+       , i.deleted_at
+from secd.identity i
+left join secd.address_validation av using (identity_id)
+left join secd.address            a  using (address_id)
+left join secd.session            s  using (identity_id)
+where (($1::uuid is null) or (i.identity_public_id = $1))
+and (($2::text is null) or (a.value = $2))
+and (($3::bool is null) or (($3::bool is true) and (av.validated_at is not null)))
+and (($4::bytea is null) or (s.token_hash = $4))
+and i.deleted_at is null;
diff --git a/crates/secd/store/pg/sql/find_identity_by_code.sql b/crates/secd/store/pg/sql/find_identity_by_code.sql
deleted file mode 100644
index e5a0970..0000000
--- a/crates/secd/store/pg/sql/find_identity_by_code.sql
+++ /dev/null
@@ -1,11 +0,0 @@
-select identity_email_validation_id
-from secd.email_validation
-where email_validation_public_id = $1::uuid
---
-select
-        identity_public_id
-        , data
-        , i.created_at
-from secd.identity            i
-left join secd.identity_email ie using (identity_id)
-where ie.identity_email_validation_id = $1;
diff --git a/crates/secd/store/pg/sql/find_session.sql b/crates/secd/store/pg/sql/find_session.sql
new file mode 100644
index 0000000..ca58480
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_session.sql
@@ -0,0 +1,11 @@
+select distinct
+       i.identity_public_id
+       , s.created_at
+       , s.expired_at
+       , s.revoked_at
+from secd.session  s
+join secd.identity i using (identity_id)
+where (($1::bytea is null) or (s.token_hash = $1))
+and (($2::uuid is null) or (i.identity_public_id = $2))
+and (($3::timestamptz is null) or (s.expired_at > $3))
+and ((revoked_at is null) or ($4::timestamptz is null) or (s.revoked_at > $4));
diff --git a/crates/secd/store/pg/sql/read_email_raw_id.sql b/crates/secd/store/pg/sql/read_email_raw_id.sql
deleted file mode 100644
index 6604fb0..0000000
--- a/crates/secd/store/pg/sql/read_email_raw_id.sql
+++ /dev/null
@@ -1 +0,0 @@
-select email_id from secd.email where address = $1
diff --git a/crates/secd/store/pg/sql/read_identity.sql b/crates/secd/store/pg/sql/read_identity.sql
deleted file mode 100644
index e69de29..0000000
--- a/crates/secd/store/pg/sql/read_identity.sql
+++ /dev/null
diff --git a/crates/secd/store/pg/sql/read_identity_raw_id.sql b/crates/secd/store/pg/sql/read_identity_raw_id.sql
deleted file mode 100644
index 5b5d95c..0000000
--- a/crates/secd/store/pg/sql/read_identity_raw_id.sql
+++ /dev/null
@@ -1,2 +0,0 @@
-select identity_id from secd.identity where identity_public_id = $1;
---
diff --git a/crates/secd/store/pg/sql/read_oauth_provider.sql b/crates/secd/store/pg/sql/read_oauth_provider.sql
deleted file mode 100644
index edaa114..0000000
--- a/crates/secd/store/pg/sql/read_oauth_provider.sql
+++ /dev/null
@@ -1,12 +0,0 @@
-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
deleted file mode 100644
index d8361ea..0000000
--- a/crates/secd/store/pg/sql/read_oauth_validation.sql
+++ /dev/null
@@ -1,23 +0,0 @@
-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_session.sql b/crates/secd/store/pg/sql/read_session.sql
deleted file mode 100644
index b1f98d4..0000000
--- a/crates/secd/store/pg/sql/read_session.sql
+++ /dev/null
@@ -1,8 +0,0 @@
-select
-        i.identity_public_id
-        , s.created_at
-        , s.expired_at
-        , s.revoked_at
-from secd.session  s
-join secd.identity i using (identity_id)
-where secret_hash = $1;
diff --git a/crates/secd/store/pg/sql/read_validation_type.sql b/crates/secd/store/pg/sql/read_validation_type.sql
deleted file mode 100644
index 2eceb98..0000000
--- a/crates/secd/store/pg/sql/read_validation_type.sql
+++ /dev/null
@@ -1,7 +0,0 @@
-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_address.sql b/crates/secd/store/pg/sql/write_address.sql
new file mode 100644
index 0000000..da1bf3a
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_address.sql
@@ -0,0 +1,8 @@
+insert into secd.address (
+       address_public_id
+       , type
+       , value
+       , created_at
+) values (
+  $1, $2, $3, $4
+);
diff --git a/crates/secd/store/pg/sql/write_address_validation.sql b/crates/secd/store/pg/sql/write_address_validation.sql
new file mode 100644
index 0000000..3be830e
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_address_validation.sql
@@ -0,0 +1,27 @@
+insert into secd.address_validation (
+       address_validation_public_id
+       , identity_id
+       , address_id
+       , "method"
+       , token_hash
+       , code_hash
+       , attempts
+       , created_at
+       , expires_at
+       , revoked_at
+       , validated_at
+) values(
+  $1
+  , (
+        select identity_id from secd.identity where identity_public_id = $2
+    )
+  , (
+        select address_id from secd.address where address_public_id = $3
+    )
+  , $4, $5, $6, $7, $8, $9, $10, $11
+) on conflict (address_validation_public_id) do update
+  set identity_id = excluded.identity_id
+      , attempts = excluded.attempts
+      , revoked_at = excluded.revoked_at
+      , validated_at = excluded.validated_at
+returning (xmax = 0);
diff --git a/crates/secd/store/pg/sql/write_email.sql b/crates/secd/store/pg/sql/write_email.sql
deleted file mode 100644
index 06a1dc5..0000000
--- a/crates/secd/store/pg/sql/write_email.sql
+++ /dev/null
@@ -1,6 +0,0 @@
-insert into secd.email (
-       address
-) values (
-       $1
-) on conflict (address) do nothing
-returning email_id;
diff --git a/crates/secd/store/pg/sql/write_email_validation.sql b/crates/secd/store/pg/sql/write_email_validation.sql
deleted file mode 100644
index ff25b87..0000000
--- a/crates/secd/store/pg/sql/write_email_validation.sql
+++ /dev/null
@@ -1,43 +0,0 @@
-insert into secd.email_validation
-       (
-           email_validation_public_id
-           , email_id
-           , code
-           , is_oauth_derived
-           , created_at
-           , validated_at
-           , expired_at
-       )
-values (
-           $1
-           , $2
-           , $3
-           , $4
-           , $5
-           , $6
-           , $7
-) on conflict (email_validation_public_id) do update
-  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 94a51fe..67662a6 100644
--- a/crates/secd/store/pg/sql/write_identity.sql
+++ b/crates/secd/store/pg/sql/write_identity.sql
@@ -1,11 +1,12 @@
 insert into secd.identity (
-       identity_public_id,
-       data,
-       created_at
+       identity_public_id
+       , data
+       , created_at
+       , updated_at
+       , deleted_at
 ) values (
-       $1,
-       $2,
-       $3
-) on conflict(identity_public_id) do update
+  $1, $2::jsonb, $3, $4, $5
+) on conflict (identity_public_id) do update
   set data = excluded.data
+      , updated_at = excluded.updated_at
       , 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
deleted file mode 100644
index ba69857..0000000
--- a/crates/secd/store/pg/sql/write_oauth_provider.sql
+++ /dev/null
@@ -1,25 +0,0 @@
-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
deleted file mode 100644
index 11f2578..0000000
--- a/crates/secd/store/pg/sql/write_oauth_validation.sql
+++ /dev/null
@@ -1,45 +0,0 @@
-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 1b238c6..18dc1f1 100644
--- a/crates/secd/store/pg/sql/write_session.sql
+++ b/crates/secd/store/pg/sql/write_session.sql
@@ -1,15 +1,10 @@
 insert into secd.session (
        identity_id
-       , secret_hash
+       , token_hash
        , created_at
        , expired_at
        , revoked_at
 ) values (
-         (select identity_id from secd.identity where identity_public_id = $1)
-       , $2
-       , $3
-       , $4
-       , $5
-) on conflict (secret_hash) do update
-  set revoked_at = excluded.revoked_at;
---
+  (select identity_id from secd.identity where identity_public_id = $1)
+  , $2, $3, $4, $5
+);