- basic functionality with psql and sqlite

- cli helper tool

1 files changed, 45 insertions, 0 deletions

diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
new file mode 100644
index 0000000..aa95afc
--- /dev/null
+++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql
@@ -0,0 +1,45 @@
+create table if not exists identity (
+       identity_id integer primary key autoincrement
+       , identity_public_id uuid
+       , data text
+       , created_at timestamp not null
+       , 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
+       , 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
+       , code text
+       , is_validated boolean not null
+       , created_at timestamp not null
+       , expires_at timestamp
+       , revoked_at timestamp
+       , 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)
+);