From 2c4eb2d311919ad9fb70738199ecf99bf20c9fce Mon Sep 17 00:00:00 2001
From: benj <benj@rse8.com>
Date: Thu, 1 Dec 2022 10:30:34 -0800
Subject: - basic functionality with psql and sqlite - cli helper tool

---
 .../pg/migrations/20221116062550_bootstrap.sql     | 49 ++++++++++++++++++++++
 1 file changed, 49 insertions(+)
 create mode 100644 crates/secd/store/pg/migrations/20221116062550_bootstrap.sql

(limited to 'crates/secd/store/pg/migrations/20221116062550_bootstrap.sql')

diff --git a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
new file mode 100644
index 0000000..7a1bf07
--- /dev/null
+++ b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql
@@ -0,0 +1,49 @@
+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
+       , data text
+       , created_at         timestamptz not null
+       , unique(identity_public_id)
+);
+
+create table if not exists auth.email (
+       email_id bigserial primary key
+       , address text not null
+       , unique(address)
+);
+
+create table if not exists auth.identity_email (
+       identity_email_id bigserial primary key
+       , identity_id bigint not null references auth.identity(identity_id)
+       , email_id bigint not null references auth.email(email_id)
+       , created_at timestamptz not null
+       , deleted_at timestamptz
+);
+
+create table if not exists auth.email_validation (
+       email_validation_id bigserial primary key
+       , email_validation_public_id uuid not null
+       , identity_email_id integer not null references auth.identity_email(identity_email_id)
+       , attempts integer not null
+       , code text
+       , is_validated boolean not null default false
+       , created_at timestamptz not null
+       , expires_at timestamptz
+       , revoked_at timestamptz
+       , unique(email_validation_public_id)
+);
+
+create table if not exists auth.session (
+       session_id bigserial primary key
+       , identity_id bigint not null references auth.identity(identity_id)
+       , secret_hash bytea not null
+       , created_at timestamptz not null
+       , touched_at timestamptz not null
+       , expires_at timestamptz
+       , revoked_at timestamptz
+       , unique(secret_hash)
+);
-- 
cgit v1.2.3

