refactor everything with more abstraction and a nicer interface

45 files changed, 348 insertions, 607 deletions

diff --git a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
deleted file mode 100644
index 3d4d84c..0000000
--- a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
+++ /dev/null
@@ -1,86 +0,0 @@
-create extension if not exists pgcrypto;
-create extension if not exists citext;
-create schema if not exists secd;
-
-create table if not exists secd.identity (
-       identity_id bigserial primary key
-       , identity_public_id uuid
-       , data text
-       , created_at timestamptz not null
-       , deleted_at timestamptz
-       , unique(identity_public_id)
-);
-
-create table if not exists secd.session (
-       session_id bigserial primary key
-       , identity_id bigint not null references secd.identity(identity_id)
-       , secret_hash bytea not null
-       , created_at timestamptz not null
-       , expired_at timestamptz
-       , revoked_at timestamptz
-       , unique(secret_hash)
-);
-
-create table if not exists secd.oauth_provider (
-       oauth_provider_id serial primary key
-       , name text not null
-       , flow text not null
-       , base_url text not null
-       , response_type text not null
-       , default_scope text
-       , client_id text not null
-       , client_secret text not null
-       , redirect_url text not null
-       , created_at timestamptz not null
-       , deleted_at timestamptz
-       , unique (name, flow)
-);
-
-create table if not exists secd.oauth_validation (
-       oauth_validation_id bigserial primary key
-       , oauth_validation_public_id uuid not null
-       , oauth_provider_id integer not null references secd.oauth_provider(oauth_provider_id)
-       , access_token text
-       , raw_response text
-       , created_at timestamptz not null
-       , validated_at timestamptz
-       , unique (oauth_validation_public_id)
-);
-
-create table if not exists secd.identity_oauth_validation (
-       identity_oauth_validation_id bigserial primary key
-       -- A validation does not require an identity to initiate
-       , identity_id bigint references secd.identity(identity_id)
-       , oauth_validation_id bigint not null references secd.oauth_validation(oauth_validation_id)
-       , revoked_at timestamptz
-       , deleted_at timestamptz
-       , unique(identity_id, oauth_validation_id)
-);
-
-create table if not exists secd.email (
-       email_id bigserial primary key
-       , address text not null
-       , unique(address)
-);
-
-create table if not exists secd.email_validation (
-       email_validation_id bigserial primary key
-       , email_validation_public_id uuid not null
-       , email_id bigint not null references secd.email(email_id)
-       , code text
-       , is_oauth_derived boolean not null
-       , created_at timestamptz not null
-       , validated_at timestamptz
-       , expired_at timestamptz
-       , unique(email_validation_public_id)
-);
-
-create table if not exists secd.identity_email_validation (
-       identity_email_validation_id bigserial primary key
-       -- A validation does not require an identity to initiate
-       , identity_id bigint references secd.identity(identity_id)
-       , email_validation_id bigint not null references secd.email_validation(email_validation_id)
-       , revoked_at timestamptz
-       , deleted_at timestamptz
-       , unique(identity_id, email_validation_id)
-);
diff --git a/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql
new file mode 100644
index 0000000..2b89957
--- /dev/null
+++ b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql
@@ -0,0 +1,85 @@
+create extension if not exists pgcrypto;
+create extension if not exists citext;
+create schema if not exists secd;
+
+create table if not exists secd.realm (
+       realm_id bigserial primary key
+       , created_at timestamptz not null
+);
+
+create table if not exists secd.realm_data (
+       realm_data_id bigserial primary key
+       , realm_id bigint not null references secd.realm(realm_id)
+       , email_provider jsonb not null
+       , sms_provider jsonb not null
+       , created_at timestamptz not null
+       , deleted_at timestamptz
+);
+
+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
+       , created_at timestamptz not null
+       , updated_at timestamptz not null
+       , deleted_at timestamptz
+       , unique(identity_public_id)
+);
+
+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)
+       , 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 table if not exists secd.address (
+       address_id bigserial primary key
+       , address_public_id uuid not null
+       , type text not null
+       , value text not null
+       , created_at timestamptz not null
+       , unique(value, type)
+);
+
+create table if not exists secd.address_validation (
+       address_validation_id bigserial primary key
+       , address_validation_public_id uuid not null
+       , identity_id bigint references secd.identity(identity_id)
+       , address_id bigint not null references secd.address(address_id)
+       , method text not null -- e.g. email, sms, voice, oidc
+       , token_hash bytea
+       , code_hash bytea
+       , attempts integer not null
+       , created_at timestamptz not null
+       , expires_at timestamptz not null
+       , revoked_at timestamptz
+       , validated_at timestamptz
+       , unique(address_validation_public_id)
+);
+
+create table if not exists secd.session (
+       session_id bigserial primary key
+       , identity_id bigint not null references secd.identity(identity_id)
+       , token_hash bytea not null
+       , created_at timestamptz not null
+       , expired_at timestamptz not null
+       , revoked_at timestamptz
+       , unique(token_hash)
+);
+
+create table if not exists secd.message (
+       message_id bigserial primary key
+       , address_id bigint not null references secd.address(address_id)
+       , subject text
+       , body text
+       , template text not null
+       , template_vars jsonb not null
+       , created_at timestamptz not null
+       , sent_at timestamptz
+);
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
+);
diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
index a8784f5..299f282 100644
--- a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
+++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
@@ -1,82 +1,81 @@
-create table if not exists identity (
-       identity_id integer primary key autoincrement
-       , identity_public_id uuid
-       , data text
-       , created_at timestamptz not null
-       , deleted_at timestamptz
-       , unique(identity_public_id)
+create table if not exists realm (
+       realm_id integer primary key
+       , created_at integer not null
 );
 
-create table if not exists session (
-       session_id integer primary key autoincrement
-       , identity_id bigint not null references identity(identity_id)
-       , secret_hash bytea not null
-       , created_at timestamptz not null
-       , expired_at timestamptz
-       , revoked_at timestamptz
-       , unique(secret_hash)
+create table if not exists realm_data (
+       realm_data_id integer primary key
+       , realm_id integer not null references realm(realm_id)
+       , email_provider text not null
+       , sms_provider text not null
+       , created_at integer not null
+       , deleted_at integer
 );
 
-create table if not exists oauth_provider (
-       oauth_provider_id integer primary key autoincrement
-       , name text not null
-       , flow text not null
-       , base_url text not null
-       , response_type text not null
-       , default_scope text
-       , client_id text not null
-       , client_secret text not null
-       , redirect_url text not null
-       , created_at timestamptz not null
-       , deleted_at timestamptz
-       , unique (name, flow)
+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
+       , created_at integer not null
+       , updated_at integer not null
+       , deleted_at integer
+       , unique(identity_public_id)
 );
 
-create table if not exists oauth_validation (
-       oauth_validation_id integer primary key autoincrement
-       , oauth_validation_public_id uuid not null
-       , oauth_provider_id integer not null references oauth_provider(oauth_provider_id)
-       , access_token text
-       , raw_response text
-       , created_at timestamptz not null
-       , validated_at timestamptz
-       , unique (oauth_validation_public_id)
+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)
+       , 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 table if not exists identity_oauth_validation (
-       identity_oauth_validation_id integer primary key autoincrement
-       -- A validation does not require an identity to initiate
-       , identity_id bigint references identity(identity_id)
-       , oauth_validation_id bigint not null references oauth_validation(oauth_validation_id)
-       , revoked_at timestamptz
-       , deleted_at timestamptz
-       , unique(identity_id, oauth_validation_id)
+create table if not exists address (
+       address_id integer primary key
+       , address_public_id uuid not null
+       , type text not null
+       , value text not null
+       , created_at integer not null
+       , unique(value, type)
 );
 
-create table if not exists email (
-       email_id integer primary key autoincrement
-       , address text not null
-       , unique(address)
+create table if not exists address_validation (
+       address_validation_id integer primary key
+       , address_validation_public_id uuid not null
+       , identity_id integer references identity(identity_id)
+       , address_id integer not null references address(address_id)
+       , method text not null -- e.g. email, sms, voice, oidc
+       , token_hash blob
+       , code_hash blob
+       , attempts integer not null
+       , created_at integer not null
+       , expires_at integer not null
+       , revoked_at integer
+       , validated_at integer
+       , unique(address_validation_public_id)
 );
 
-create table if not exists email_validation (
-       email_validation_id integer primary key autoincrement
-       , email_validation_public_id uuid not null
-       , email_id bigint not null references email(email_id)
-       , code text
-       , is_oauth_derived boolean not null
-       , created_at timestamptz not null
-       , validated_at timestamptz
-       , expired_at timestamptz
-       , unique(email_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 identity_email_validation (
-       identity_email_validation_id integer primary key autoincrement
-       -- A validation does not require an identity to initiate
-       , identity_id bigint references identity(identity_id)
-       , email_validation_id bigint not null references email_validation(email_validation_id)
-       , revoked_at timestamptz
-       , deleted_at timestamptz
-       , unique(identity_id, email_validation_id)
+create table if not exists message (
+       message_id integer primary key
+       , address_id integer not null references address(address_id)
+       , subject text
+       , body text
+       , template text not null
+       , template_vars text not null
+       , created_at integer not null
+       , sent_at integer
 );
diff --git a/crates/secd/store/sqlite/sql/find_address.sql b/crates/secd/store/sqlite/sql/find_address.sql
new file mode 100644
index 0000000..da1df81
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_address.sql
@@ -0,0 +1,8 @@
+select address_public_id
+       , type
+       , value
+       , created_at
+from address
+where (($1 is null) or (address_public_id = $1))
+and (($2 is null) or (type = $2))
+and (($3 is null) or (value = $3));
diff --git a/crates/secd/store/sqlite/sql/find_address_validation.sql b/crates/secd/store/sqlite/sql/find_address_validation.sql
new file mode 100644
index 0000000..81d2cdf
--- /dev/null
+++ b/crates/secd/store/sqlite/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 address_validation av
+join address            a  using(address_id)
+left join identity      i  using(identity_id)
+where (($1 is null) or (address_validation_public_id = $1));
diff --git a/crates/secd/store/sqlite/sql/find_email_validation.sql b/crates/secd/store/sqlite/sql/find_email_validation.sql
deleted file mode 100644
index d7f311c..0000000
--- a/crates/secd/store/sqlite/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 email_validation               ev
-join email                          e   using (email_id)
-left join identity_email_validation iev using (email_validation_id)
-left join 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/sqlite/sql/find_identity.sql b/crates/secd/store/sqlite/sql/find_identity.sql
index f94e7b1..1528407 100644
--- a/crates/secd/store/sqlite/sql/find_identity.sql
+++ b/crates/secd/store/sqlite/sql/find_identity.sql
@@ -1,11 +1,15 @@
-select
-        identity_public_id
-        , data
-        , i.created_at
-        , i.deleted_at
-from identity       i
-join identity_email_validation iev using (identity_id)
-join email_validation          ev  using (email_validation_id)
-join 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
+       , i.created_at
+       , i.updated_at
+       , i.deleted_at
+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_identity_by_code.sql b/crates/secd/store/sqlite/sql/find_identity_by_code.sql
deleted file mode 100644
index b70a13a..0000000
--- a/crates/secd/store/sqlite/sql/find_identity_by_code.sql
+++ /dev/null
@@ -1,11 +0,0 @@
-select identity_email_validation_id
-from email_validation
-where email_validation_public_id = $1::uuid
---
-select
-        identity_public_id
-        , data
-        , i.created_at
-from identity            i
-left join identity_email ie using (identity_id)
-where ie.identity_email_validation_id = ?1;
diff --git a/crates/secd/store/sqlite/sql/find_session.sql b/crates/secd/store/sqlite/sql/find_session.sql
new file mode 100644
index 0000000..31640dd
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_session.sql
@@ -0,0 +1,11 @@
+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/read_email_raw_id.sql b/crates/secd/store/sqlite/sql/read_email_raw_id.sql
deleted file mode 100644
index a65c717..0000000
--- a/crates/secd/store/sqlite/sql/read_email_raw_id.sql
+++ /dev/null
@@ -1 +0,0 @@
-select email_id from email where address = ?1
diff --git a/crates/secd/store/sqlite/sql/read_identity.sql b/crates/secd/store/sqlite/sql/read_identity.sql
deleted file mode 100644
index e69de29..0000000
--- a/crates/secd/store/sqlite/sql/read_identity.sql
+++ /dev/null
diff --git a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql
deleted file mode 100644
index 2bdb718..0000000
--- a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql
+++ /dev/null
@@ -1,2 +0,0 @@
-select identity_id from identity where identity_public_id = ?1;
---
diff --git a/crates/secd/store/sqlite/sql/read_oauth_provider.sql b/crates/secd/store/sqlite/sql/read_oauth_provider.sql
deleted file mode 100644
index 5c33cf0..0000000
--- a/crates/secd/store/sqlite/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 oauth_provider
-where name = ?1
-and flow = ?2;
diff --git a/crates/secd/store/sqlite/sql/read_oauth_validation.sql b/crates/secd/store/sqlite/sql/read_oauth_validation.sql
deleted file mode 100644
index 75f5a94..0000000
--- a/crates/secd/store/sqlite/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 oauth_validation               ov
-join oauth_provider                 op  using(oauth_provider_id)
-left join identity_oauth_validation iov using(oauth_validation_id)
-left join identity                  i   using(identity_id)
-where oauth_validation_public_id = ?1;
diff --git a/crates/secd/store/sqlite/sql/read_session.sql b/crates/secd/store/sqlite/sql/read_session.sql
deleted file mode 100644
index c415c4c..0000000
--- a/crates/secd/store/sqlite/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 session  s
-join identity i using (identity_id)
-where secret_hash = ?1;
diff --git a/crates/secd/store/sqlite/sql/read_validation_type.sql b/crates/secd/store/sqlite/sql/read_validation_type.sql
deleted file mode 100644
index cc02ead..0000000
--- a/crates/secd/store/sqlite/sql/read_validation_type.sql
+++ /dev/null
@@ -1,7 +0,0 @@
-select 'Email'
-from email_validation
-where email_validation_public_id = ?1
-union
-select 'Oauth'
-from oauth_validation
-where oauth_validation_public_id = ?1;
diff --git a/crates/secd/store/sqlite/sql/write_address.sql b/crates/secd/store/sqlite/sql/write_address.sql
new file mode 100644
index 0000000..56dab0c
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_address.sql
@@ -0,0 +1,8 @@
+insert into address (
+       address_public_id
+       , type
+       , value
+       , created_at
+) values (
+  $1, $2, $3, $4
+);
diff --git a/crates/secd/store/sqlite/sql/write_address_validation.sql b/crates/secd/store/sqlite/sql/write_address_validation.sql
new file mode 100644
index 0000000..67ce916
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_address_validation.sql
@@ -0,0 +1,26 @@
+insert into 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 identity where identity_public_id = $2
+    )
+  , (
+        select address_id from 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;
diff --git a/crates/secd/store/sqlite/sql/write_email.sql b/crates/secd/store/sqlite/sql/write_email.sql
deleted file mode 100644
index a64aed4..0000000
--- a/crates/secd/store/sqlite/sql/write_email.sql
+++ /dev/null
@@ -1,6 +0,0 @@
-insert into email (
-       address
-) values (
-       $1
-) on conflict (address) do nothing
-returning email_id;
diff --git a/crates/secd/store/sqlite/sql/write_email_validation.sql b/crates/secd/store/sqlite/sql/write_email_validation.sql
deleted file mode 100644
index d839310..0000000
--- a/crates/secd/store/sqlite/sql/write_email_validation.sql
+++ /dev/null
@@ -1,43 +0,0 @@
-insert into 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 identity_email_validation (
-       identity_id
-       , email_validation_id
-       , revoked_at
-       , deleted_at
-) values (
-       (
-       select identity_id
-       from identity
-       where identity_public_id = ?1
-       )
-       , (
-         select email_validation_id
-         from 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/sqlite/sql/write_identity.sql b/crates/secd/store/sqlite/sql/write_identity.sql
index 8cf46c5..aa59358 100644
--- a/crates/secd/store/sqlite/sql/write_identity.sql
+++ b/crates/secd/store/sqlite/sql/write_identity.sql
@@ -1,11 +1,12 @@
 insert into 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, $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/sqlite/sql/write_oauth_provider.sql b/crates/secd/store/sqlite/sql/write_oauth_provider.sql
deleted file mode 100644
index 421caf7..0000000
--- a/crates/secd/store/sqlite/sql/write_oauth_provider.sql
+++ /dev/null
@@ -1,23 +0,0 @@
-insert into oauth_provider (
-       name
-       , flow
-       , base_url
-       , response_type
-       , default_scope
-       , client_id
-       , client_secret
-       , redirect_url
-       , created_at
-       , deleted_at
-) values (
-       ?1
-       , ?2
-       , ?3
-       , ?4
-       , ?5
-       , ?6
-       , ?7
-       , ?8
-       , ?9
-       , ?10
-) on conflict (name, flow) do nothing;
diff --git a/crates/secd/store/sqlite/sql/write_oauth_validation.sql b/crates/secd/store/sqlite/sql/write_oauth_validation.sql
deleted file mode 100644
index ccb11aa..0000000
--- a/crates/secd/store/sqlite/sql/write_oauth_validation.sql
+++ /dev/null
@@ -1,45 +0,0 @@
-insert into oauth_validation (
-       oauth_validation_public_id
-       , oauth_provider_id
-       , access_token
-       , raw_response
-       , created_at
-       , validated_at
-) values (
-       ?1
-       , (
-         select oauth_provider_id
-         from 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 identity_oauth_validation (
-       identity_id
-       , oauth_validation_id
-       , revoked_at
-       , deleted_at
-) values (
-       (
-       select identity_id
-       from identity
-       where identity_public_id = ?1
-       )
-       , (
-         select oauth_validation_id
-         from 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/sqlite/sql/write_session.sql b/crates/secd/store/sqlite/sql/write_session.sql
index 480af54..4679912 100644
--- a/crates/secd/store/sqlite/sql/write_session.sql
+++ b/crates/secd/store/sqlite/sql/write_session.sql
@@ -1,15 +1,10 @@
 insert into session (
        identity_id
-       , secret_hash
+       , 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 (secret_hash) do update
-  set revoked_at = excluded.revoked_at;
---
+  (select identity_id from identity where identity_public_id = $1)
+  , $2, $3, $4, $5
+);