From 0920c4d4f30a3345870d385d5c6f3e0919228b56 Mon Sep 17 00:00:00 2001
From: benj <benj@rse8.com>
Date: Mon, 12 Dec 2022 17:06:57 -0800
Subject: (oauth2 + email added): a mess that may or may not really work and
 needs to be refactored...

---
 .../sqlite/migrations/20221125051738_bootstrap.sql | 87 +++++++++++++++-------
 1 file changed, 62 insertions(+), 25 deletions(-)

(limited to 'crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql')

diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
index aa95afc..a8784f5 100644
--- a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
+++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
@@ -2,44 +2,81 @@ create table if not exists identity (
        identity_id integer primary key autoincrement
        , identity_public_id uuid
        , data text
-       , created_at timestamp not null
+       , created_at timestamptz not null
+       , deleted_at timestamptz
        , unique(identity_public_id)
 );
 
+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 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 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 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 email (
        email_id integer primary key autoincrement
        , address text not null
        , unique(address)
 );
 
-create table if not exists identity_email (
-       identity_email_id integer primary key autoincrement
-       , identity_id integer not null references identity(identity_id)
-       , email_id integer not null references email(email_id)
-       , created_at timestamp not null
-       , deleted_at timestamp
-);
-
 create table if not exists email_validation (
        email_validation_id integer primary key autoincrement
-       , email_validation_public_id text not null -- uuid
-       , identity_email_id integer not null references identity_email(identity_email_id)
-       , attempts integer not null
+       , email_validation_public_id uuid not null
+       , email_id bigint not null references email(email_id)
        , code text
-       , is_validated boolean not null
-       , created_at timestamp not null
-       , expires_at timestamp
-       , revoked_at timestamp
+       , 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 autoincrement
-       , identity_id not null references identity(identity_id)
-       , secret_hash blob not null
-       , created_at timestamp not null
-       , touched_at timestamp not null
-       , expires_at timestamp
-       , revoked_at timestamp
-       , unique(secret_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)
 );
-- 
cgit v1.2.3