From c2268c285648ef02ece04de0d9df0813c6d70ff8 Mon Sep 17 00:00:00 2001 From: benj Date: Sat, 24 Dec 2022 00:43:38 -0800 Subject: refactor everything with more abstraction and a nicer interface --- .../pg/migrations/20221116062550_bootstrap.sql | 86 ---------------------- .../pg/migrations/20221222002434_bootstrap.sql | 85 +++++++++++++++++++++ 2 files changed, 85 insertions(+), 86 deletions(-) delete mode 100644 crates/secd/store/pg/migrations/20221116062550_bootstrap.sql create mode 100644 crates/secd/store/pg/migrations/20221222002434_bootstrap.sql (limited to 'crates/secd/store/pg/migrations') 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 +); -- cgit v1.2.3