2 files changed, 129 insertions, 0 deletions
diff --git a/store/psql/migrations/20221116062550_bootstrap.sql b/store/psql/migrations/20221116062550_bootstrap.sql
new file mode 100644
index 0000000..fd64958
--- /dev/null
+++ b/store/psql/migrations/20221116062550_bootstrap.sql
@@ -0,0 +1,97 @@
+create extension if not exists pgcrypto;
+create extension if not exists citext;
+create schema if not exists auth;
+
+create table if not exists auth.identity (
+       identity_id          bigserial          primary key
+       , identity_public_id uuid               default gen_random_uuid()
+       , created_at         timestamp not null default current_timestamp
+       , unique(identity_public_id)
+);
+
+create table if not exists auth.email (
+       email_id bigserial primary key
+       , address text not null
+);
+
+create table if not exists auth.email_challenge_request (
+       email_challenge_request_id bigserial primary key
+       , email_id bigint not null references auth.email(email_id)
+       , code text not null
+       , created_at timestamp not null default current_timestamp
+       , expires_at timestamp
+       , revoked_at timestamp
+);
+
+create table if not exists auth.email_challenge_response (
+       email_challenge_response_id bigserial primary key
+       , email_challenge_request_id bigint not null references auth.email_challenge_request(email_challenge_request_id)
+       , is_valid bool not null
+       , raw_response text not null
+       , created_at timestamp
+);
+
+create table if not exists auth.identity_email (
+       identity_id bigint not null references auth.identity(identity_id)
+       , email_id bigint not null references auth.email(email_id)
+       , created_at timestamp not null default current_timestamp
+       , deleted_at timestamp
+);
+
+create table if not exists auth.phone_number (
+       phone_number_id bigserial primary key
+       , digits text not null
+);
+
+create table if not exists auth.phone_number_challenge_request (
+       phone_number_challenge_request_id bigserial primary key
+       , phone_number_id bigint not null references auth.phone_number(phone_number_id)
+       , code text not null
+       , created_at timestamp not null default current_timestamp
+       , expires_at timestamp
+       , revoked_at timestamp
+);
+
+create table if not exists auth.phone_number_challenge_response (
+       phone_number_challenge_response_id bigserial primary key
+       , phone_number_challenge_request_id bigint not null references auth.phone_number_challenge_request(phone_number_challenge_request_id)
+       , is_valid bool not null
+       , raw_response text not null
+       , created_at timestamp
+);
+
+create table if not exists auth.identity_phone_number (
+       identity_id bigint not null references auth.identity(identity_id)
+       , phone_number_id bigint not null references auth.phone_number(phone_number_id)
+       , created_at timestamp not null default current_timestamp
+       , deleted_at timestamp
+);
+
+create table if not exists auth.oauth_provider (
+       oauth_provider_id bigserial primary key
+       , provider text not null
+       , consent_uri text not null
+       , client_id text not null
+       , client_secret_encrypted text not null
+       , redirect_uri text
+       , created_at timestamp not null default current_timestamp
+       , unique(provider, client_id)
+);
+
+create table if not exists auth.oauth_request (
+       oauth_request_id bigserial primary key
+       , oauth_provider_id bigint not null references auth.oauth_provider(oauth_provider_id)
+       , identity_id bigint not null references auth.identity(identity_id)
+       , state text not null
+       , created_at timestamp not null default current_timestamp
+);
+
+create table if not exists auth.oauth_response (
+       oauth_response_id bigserial primary key
+       , oauth_request_id bigint not null references auth.oauth_request(oauth_request_id)
+       , is_error boolean not null
+       , raw_response text not null
+       , access_token text not null
+       , expires_at timestamp not null
+       , created_at timestamp not null default current_timestamp
+);
diff --git a/store/sqlite/migrations/20221125051738_bootstrap.sql b/store/sqlite/migrations/20221125051738_bootstrap.sql
new file mode 100644
index 0000000..70a8892
--- /dev/null
+++ b/store/sqlite/migrations/20221125051738_bootstrap.sql
@@ -0,0 +1,32 @@
+create table if not exists identity (
+       identity_id integer primary key autoincrement
+       , identity_public_id uuid
+       , data text default '{}'
+       , created_at timestamp not null default current_timestamp
+       , unique(identity_public_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 default current_timestamp
+       , deleted_at timestamp not null default current_timestamp
+);
+
+create table if not exists email_validation_request (
+       email_validation_request_id integer primary key autoincrement
+       -- uuid
+       , email_validation_request_public_id text not null
+       , identity_email_id integer not null references identity_email(identity_email_id)
+       , is_validated boolean not null default false
+       , created_at timestamp not null default current_timestamp
+       , expires_at timestamp
+       , revoked_at timestamp
+);