email templates, sendgrid, creds, and some experimental things

Started playing with namespace configs and integrating with zanzibar impls. Still lot's of experimenting and dead code going on.

7 files changed, 95 insertions, 5 deletions

diff --git a/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql
index 2b89957..0cf3fa0 100644
--- a/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql
+++ b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql
@@ -19,7 +19,7 @@ create table if not exists secd.realm_data (
 create table if not exists secd.identity (
        identity_id bigserial primary key
        , identity_public_id uuid not null
-       , data jsonb -- some things are dervied, others are not
+       , data text -- we do not prescribe JSON or any other serialization format.
        , created_at timestamptz not null
        , updated_at timestamptz not null
        , deleted_at timestamptz
@@ -30,14 +30,18 @@ create table if not exists secd.credential (
        credential_id bigserial primary key
        , credential_public_id uuid not null
        , identity_id bigint not null references secd.identity(identity_id)
+       , partial_key text
        , type text not null-- e.g. password, oidc, totop, lookup_secret, webauthn, ...
        , data jsonb not null
-       , version integer not null
        , created_at timestamptz not null
        , revoked_at timestamptz
        , deleted_at timestamptz
 );
 
+create unique index if not exists credential_passphrase_type_key_ix
+on secd.credential (partial_key)
+where type = 'Passphrase';
+
 create table if not exists secd.address (
        address_id bigserial primary key
        , address_public_id uuid not null
@@ -83,3 +87,23 @@ create table if not exists secd.message (
        , created_at timestamptz not null
        , sent_at timestamptz
 );
+
+create table if not exists secd.namespace_config (
+       namespace text not null
+       , serialized_config text not null
+       , created_at xid8 not null
+       , deleted_at xid8
+       -- TODO: indexes and stuff
+);
+
+create table if not exists secd.relation_tuple (
+       namespace text not null
+       , object_id text not null
+       , relation text not null
+       , userset_namespace text not null
+       , userset_object_id text not null
+       , userset_relation text not null
+       , created_at xid8 not null
+       , deleted_at xid8 not null
+       -- TODO: indexes and stuff
+);
diff --git a/crates/secd/store/pg/sql/find_credential.sql b/crates/secd/store/pg/sql/find_credential.sql
new file mode 100644
index 0000000..e30c0ea
--- /dev/null
+++ b/crates/secd/store/pg/sql/find_credential.sql
@@ -0,0 +1,12 @@
+select c.credential_public_id
+       , i.identity_public_id
+       , c.data::text
+       , c.created_at
+       , c.revoked_at
+       , c.deleted_at
+from secd.credential c
+join secd.identity   i using (identity_id)
+where (($1::uuid is null) or (c.credential_public_id = $1))
+and (($2::uuid is null) or (i.identity_public_id = $2))
+and (($3::text is null) or (c.type = $3))
+and (($3::text is null or $4::text is null) or (c.data->$3->>'key' = $4))
diff --git a/crates/secd/store/pg/sql/write_credential.sql b/crates/secd/store/pg/sql/write_credential.sql
new file mode 100644
index 0000000..17e03a2
--- /dev/null
+++ b/crates/secd/store/pg/sql/write_credential.sql
@@ -0,0 +1,19 @@
+insert into secd.credential (
+       credential_public_id
+       , identity_id
+       , partial_key
+       , type
+       , data
+       , created_at
+       , revoked_at
+       , deleted_at
+) values (
+       $1
+       , (select identity_id from secd.identity where identity_public_id = $2)
+       , $3
+       , $4
+       , $5::jsonb
+       , $6
+       , $7
+       , $8
+);
diff --git a/crates/secd/store/pg/sql/write_identity.sql b/crates/secd/store/pg/sql/write_identity.sql
index 67662a6..4b2745b 100644
--- a/crates/secd/store/pg/sql/write_identity.sql
+++ b/crates/secd/store/pg/sql/write_identity.sql
@@ -5,7 +5,7 @@ insert into secd.identity (
        , updated_at
        , deleted_at
 ) values (
-  $1, $2::jsonb, $3, $4, $5
+  $1, $2, $3, $4, $5
 ) on conflict (identity_public_id) do update
   set data = excluded.data
       , updated_at = excluded.updated_at
diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
index 299f282..b2ce45d 100644
--- a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
+++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
@@ -15,7 +15,7 @@ create table if not exists realm_data (
 create table if not exists identity (
        identity_id integer primary key
        , identity_public_id uuid not null
-       , data text -- some things are dervied, others are not
+       , data text -- we do not prescribe JSON or any other serialization format
        , created_at integer not null
        , updated_at integer not null
        , deleted_at integer
@@ -26,14 +26,18 @@ create table if not exists credential (
        credential_id integer primary key
        , credential_public_id uuid not null
        , identity_id integer not null references identity(identity_id)
+       , partial_key text
        , type text not null-- e.g. password, oidc, totop, lookup_secret, webauthn, ...
        , data text not null
-       , version integer not null
        , created_at integer not null
        , revoked_at integer
        , deleted_at integer
 );
 
+create unique index if not exists credential_passphrase_type_key_ix
+on credential (partial_key)
+where type = 'Passphrase';
+
 create table if not exists address (
        address_id integer primary key
        , address_public_id uuid not null
diff --git a/crates/secd/store/sqlite/sql/find_credential.sql b/crates/secd/store/sqlite/sql/find_credential.sql
new file mode 100644
index 0000000..9062914
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_credential.sql
@@ -0,0 +1,12 @@
+select c.credential_public_id
+       , i.identity_public_id
+       , c.data
+       , c.created_at
+       , c.revoked_at
+       , c.deleted_at
+from credential c
+join identity   i using (identity_id)
+where (($1 is null) or (c.credential_public_id = $1))
+and (($2 is null) or (i.identity_public_id = $2))
+and (($3 is null) or (c.type = $3))
+and (($3 is null or $4 is null) or (c.data->$3->>'key' = $4))
diff --git a/crates/secd/store/sqlite/sql/write_credential.sql b/crates/secd/store/sqlite/sql/write_credential.sql
new file mode 100644
index 0000000..3319226
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_credential.sql
@@ -0,0 +1,19 @@
+insert into credential (
+       credential_public_id
+       , identity_id
+       , partial_key
+       , type
+       , data
+       , created_at
+       , revoked_at
+       , deleted_at
+) values (
+       $1
+       , (select identity_id from identity where identity_public_id = $2)
+       , $3
+       , $4
+       , $5
+       , $6
+       , $7
+       , $8
+);