3 files changed, 37 insertions, 2 deletions
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
+);