8 files changed, 33 insertions, 39 deletions
diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
index b2ce45d..0a182e1 100644
--- a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
+++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
@@ -34,9 +34,7 @@ create table if not exists credential (
        , deleted_at integer
 );
 
-create unique index if not exists credential_passphrase_type_key_ix
-on credential (partial_key)
-where type = 'Passphrase';
+create unique index if not exists credential_partial_key_type_key_ix on credential (partial_key);
 
 create table if not exists address (
        address_id integer primary key
@@ -63,16 +61,6 @@ create table if not exists address_validation (
        , unique(address_validation_public_id)
 );
 
-create table if not exists session (
-       session_id integer primary key
-       , identity_id integer not null references identity(identity_id)
-       , token_hash blob not null
-       , created_at integer not null
-       , expired_at integer not null
-       , revoked_at integer
-       , unique(token_hash)
-);
-
 create table if not exists message (
        message_id integer primary key
        , address_id integer not null references address(address_id)
@@ -83,3 +71,10 @@ create table if not exists message (
        , created_at integer not null
        , sent_at integer
 );
+
+create table if not exists impersonator (
+       impersonator_id integer not null references identity(identity_id)
+       , target_id integer not null references identity(identity_id)
+       , credential_id integer not null references credential(credential_id)
+       , created_at integer not null
+);
diff --git a/crates/secd/store/sqlite/sql/find_credential.sql b/crates/secd/store/sqlite/sql/find_credential.sql
index 9062914..0590dee 100644
--- a/crates/secd/store/sqlite/sql/find_credential.sql
+++ b/crates/secd/store/sqlite/sql/find_credential.sql
@@ -9,4 +9,4 @@ 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))
+and (($3 is null or $4 is null) or (c.partial_key = $4))
diff --git a/crates/secd/store/sqlite/sql/find_identity.sql b/crates/secd/store/sqlite/sql/find_identity.sql
index 1528407..0d32a9b 100644
--- a/crates/secd/store/sqlite/sql/find_identity.sql
+++ b/crates/secd/store/sqlite/sql/find_identity.sql
@@ -7,9 +7,7 @@ select distinct
 from identity i
 left join address_validation av using (identity_id)
 left join address            a  using (address_id)
-left join session            s  using (identity_id)
 where (($1 is null) or (i.identity_public_id = $1))
 and (($2 is null) or (a.value = $2))
 and (($3 is null) or (($3 is true) and (av.validated_at is not null)))
-and (($4 is null) or (s.token_hash = $4))
 and i.deleted_at is null;
diff --git a/crates/secd/store/sqlite/sql/find_impersonator.sql b/crates/secd/store/sqlite/sql/find_impersonator.sql
new file mode 100644
index 0000000..786e9ba
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_impersonator.sql
@@ -0,0 +1,10 @@
+select i2.identity_public_id as impersonator_public_id
+       , i3.identity_public_id as target_public_id
+       , i.created_at
+from impersonator i
+join identity i2 on i.impersonator_id = i2.identity_id
+join identity i3 on i.target_id = i3.identity_id
+join credential c using (credential_id)
+where (($1 is null) or (i2.identity_public_id = $1))
+and (($2 is null) or (i3.identity_public_id = $2))
+and c.revoked_at > $3;
diff --git a/crates/secd/store/sqlite/sql/find_session.sql b/crates/secd/store/sqlite/sql/find_session.sql
deleted file mode 100644
index 31640dd..0000000
--- a/crates/secd/store/sqlite/sql/find_session.sql
+++ /dev/null
@@ -1,11 +0,0 @@
-select distinct
-       i.identity_public_id
-       , s.created_at
-       , s.expired_at
-       , s.revoked_at
-from session  s
-join identity i using (identity_id)
-where (($1 is null) or (s.token_hash = $1))
-and (($2 is null) or (i.identity_public_id = $2))
-and (($3 is null) or (s.expired_at > $3))
-and ((revoked_at is null) or ($4 is null) or (s.revoked_at > $4));
diff --git a/crates/secd/store/sqlite/sql/write_credential.sql b/crates/secd/store/sqlite/sql/write_credential.sql
index 3319226..06cb389 100644
--- a/crates/secd/store/sqlite/sql/write_credential.sql
+++ b/crates/secd/store/sqlite/sql/write_credential.sql
@@ -16,4 +16,6 @@ insert into credential (
        , $6
        , $7
        , $8
-);
+) on conflict (partial_key) do update
+  set revoked_at = excluded.revoked_at
+      , deleted_at = excluded.deleted_at;
diff --git a/crates/secd/store/sqlite/sql/write_impersonator.sql b/crates/secd/store/sqlite/sql/write_impersonator.sql
new file mode 100644
index 0000000..ae81466
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_impersonator.sql
@@ -0,0 +1,11 @@
+insert into impersonator (
+    impersonator_id
+    , target_id
+    , credential_id
+    , created_at
+) values (
+  (select identity_id from identity where identity_public_id = $1)
+  , (select identity_id from identity where identity_public_id = $2)
+  , (select credential_id from credential where credential_public_id = $3)
+  , $4
+);
diff --git a/crates/secd/store/sqlite/sql/write_session.sql b/crates/secd/store/sqlite/sql/write_session.sql
deleted file mode 100644
index 9ffb105..0000000
--- a/crates/secd/store/sqlite/sql/write_session.sql
+++ /dev/null
@@ -1,11 +0,0 @@
-insert into session (
-       identity_id
-       , token_hash
-       , created_at
-       , expired_at
-       , revoked_at
-) values (
-  (select identity_id from identity where identity_public_id = $1)
-  , $2, $3, $4, $5
-) on conflict (token_hash) do update
-  set revoked_at = excluded.revoked_at;