11 files changed, 113 insertions, 0 deletions
diff --git a/crates/secd/store/pg/sql/find_email_validation.sql b/crates/secd/store/pg/sql/find_email_validation.sql
new file mode 100644
index 0000000..d16d8e7
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_email_validation.sql
@@ -0,0 +1,17 @@
+select
+        ev.email_validation_public_id
+        , i.identity_public_id
+        , e.address
+        , ev.attempts
+        , ev.code
+        , ev.is_validated
+        , ev.created_at
+        , ev.expires_at
+        , ev.revoked_at
+from auth.email_validation ev
+join auth.identity_email   ie using (identity_email_id)
+join auth.email            e  using (email_id)
+join auth.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
new file mode 100644
index 0000000..3a86a83
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_identity.sql
@@ -0,0 +1,9 @@
+select
+        identity_public_id,
+        data,
+        i.created_at
+from auth.identity       i
+join auth.identity_email ie using (identity_id)
+join auth.email          e  using (email_id)
+where (($1 is null) or (i.identity_public_id = $1))
+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
new file mode 100644
index 0000000..9df6614
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_identity_by_code.sql
@@ -0,0 +1,11 @@
+select identity_email_id
+from auth.email_validation
+where email_validation_public_id = $1::uuid
+--
+select
+        identity_public_id
+        , data
+        , i.created_at
+from auth.identity            i
+left join auth.identity_email ie using (identity_id)
+where ie.identity_email_id = $1;
diff --git a/crates/secd/store/pg/sql/read_email_raw_id.sql b/crates/secd/store/pg/sql/read_email_raw_id.sql
new file mode 100644
index 0000000..f62331c
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_email_raw_id.sql
@@ -0,0 +1 @@
+select email_id from auth.email where address = $1
diff --git a/crates/secd/store/pg/sql/read_identity.sql b/crates/secd/store/pg/sql/read_identity.sql
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_identity.sql
diff --git a/crates/secd/store/pg/sql/read_identity_raw_id.sql b/crates/secd/store/pg/sql/read_identity_raw_id.sql
new file mode 100644
index 0000000..d550cc0
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_identity_raw_id.sql
@@ -0,0 +1,2 @@
+select identity_id from auth.identity where identity_public_id = $1;
+--
diff --git a/crates/secd/store/pg/sql/read_session.sql b/crates/secd/store/pg/sql/read_session.sql
new file mode 100644
index 0000000..febc1ab
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_session.sql
@@ -0,0 +1,8 @@
+select
+        i.identity_public_id
+        , s.created_at
+        , s.expires_at
+        , s.revoked_at
+from auth.session  s
+join auth.identity i using (identity_id)
+where secret_hash = $1;
diff --git a/crates/secd/store/pg/sql/write_email.sql b/crates/secd/store/pg/sql/write_email.sql
new file mode 100644
index 0000000..75fc494
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_email.sql
@@ -0,0 +1,11 @@
+insert into auth.email (
+       address
+) values (
+       $1
+) on conflict (address) do nothing
+returning email_id;
+--
+select email_id from auth.email where address = $1;
+--
+insert into auth.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
new file mode 100644
index 0000000..98fc60e
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_email_validation.sql
@@ -0,0 +1,27 @@
+insert into auth.email_validation
+       (
+           email_validation_public_id
+           , identity_email_id
+           , attempts
+           , code
+           , is_validated
+           , created_at
+           , expires_at
+       )
+values (
+           $1
+           , (
+               select identity_email_id
+               from auth.identity_email
+               where identity_id = $2
+               and email_id = $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;
diff --git a/crates/secd/store/pg/sql/write_identity.sql b/crates/secd/store/pg/sql/write_identity.sql
new file mode 100644
index 0000000..eed1710
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_identity.sql
@@ -0,0 +1,9 @@
+insert into auth.identity (
+       identity_public_id,
+       data,
+       created_at
+) values (
+       $1,
+       $2,
+       $3
+);
diff --git a/crates/secd/store/pg/sql/write_session.sql b/crates/secd/store/pg/sql/write_session.sql
new file mode 100644
index 0000000..cd5892b
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_session.sql
@@ -0,0 +1,18 @@
+insert into auth.session (
+       identity_id
+       , secret_hash
+       , created_at
+       , touched_at
+       , expires_at
+       , revoked_at
+) values (
+         (select identity_id from auth.identity where identity_public_id = $1)
+       , $2
+       , $3
+       , $4
+       , $5
+       , $6
+) on conflict (secret_hash) do update
+  set touched_at = excluded.touched_at
+      , revoked_at = excluded.revoked_at;
+--