initial import

1 files changed, 291 insertions, 0 deletions

diff --git a/migrations/20220626163140_init.up.sql b/migrations/20220626163140_init.up.sql
new file mode 100644
index 0000000..916d31c
--- /dev/null
+++ b/migrations/20220626163140_init.up.sql
@@ -0,0 +1,291 @@
+create domain session_id as bytea check (length(value) = 32);
+create domain user_id as bytea check (length(value) = 16);
+create domain hawk_key as bytea check (length(value) = 32);
+create domain device_id as bytea check (length(value) = 16);
+create domain key_fetch_id as bytea check (length(value) = 32);
+create domain oauth_token_id as bytea check (length(value) = 32);
+create domain oauth_auth_id as bytea check (length(value) = 32);
+create domain secret_key as bytea check (length(value) = 32);
+create domain verify_hash as bytea check (length(value) = 32);
+create domain password_change_id as bytea check (length(value) = 32);
+create domain account_reset_id as bytea check (length(value) = 32);
+create domain avatar_id as bytea check (length(value) = 16);
+
+create type oauth_token_kind as enum ('access', 'refresh');
+create type oauth_access_type as enum ('online', 'offline');
+create type device_command as (name text, body text);
+
+create type device_push_info as (
+       callback    text,
+       public_key  text,
+       auth_key    text
+);
+
+create table users (
+       user_id       user_id not null primary key,
+       auth_salt     text not null,
+       email         text not null check (length(email) <= 256),
+       display_name  text check (length(display_name) <= 256),
+       ka            secret_key not null,
+       wrapwrap_kb   secret_key not null,
+       verify_hash   verify_hash not null,
+       verified      bool not null default false,
+
+       created_at    timestamp with time zone not null default now()
+);
+
+create unique index user__email__idx on users ((lower(email)));
+
+create table user_avatars (
+       user_id       user_id not null primary key references users on delete cascade,
+       id            avatar_id not null,
+       data          bytea not null check (length(data) <= 128 * 1024),
+       content_type  text not null check (length(content_type) < 1024)
+);
+
+create table device (
+       device_id           device_id not null primary key,
+       user_id             user_id not null references users on delete cascade,
+       created_at          timestamp with time zone not null default now(),
+       name                text not null check (length(name) <= 256),
+       type                text not null check (length(type) <= 16),
+       push                device_push_info,
+       available_commands  device_command[] not null,
+       push_expired        boolean not null,
+       location            jsonb not null check (jsonb_typeof(location) = 'object')
+);
+
+create function insert_or_update_device(
+              p_device_id           device_id,
+              p_user_id             user_id,
+              p_name                text,
+              p_type                text,
+              p_push                device_push_info,
+              p_available_commands  device_command[],
+              p_location            jsonb,
+              out result device)
+       returns device
+       language plpgsql
+       as $$
+       begin
+              update device
+              set name = coalesce(p_name, name),
+                     type = coalesce(p_type, type),
+                     push = coalesce(p_push, push),
+                     available_commands = coalesce(p_available_commands, available_commands),
+                     push_expired = push_expired and p_push is null,
+                     location = coalesce(p_location, location)
+              where device_id = p_device_id
+              returning *
+              into result;
+
+              if not found then
+                     insert into device (device_id, user_id, name, type, push, push_expired,
+                            available_commands, location)
+                     values (p_device_id, p_user_id, p_name, p_type, p_push, p_push is null,
+                            p_available_commands, coalesce(p_location, '{}'::jsonb))
+                     returning *
+                     into result;
+              end if;
+       end
+       $$;
+
+create table user_session (
+       session_id    session_id not null primary key,
+       user_id       user_id not null references users on delete cascade,
+       req_hmac_key  hawk_key not null,
+       device_id     device_id references device on delete cascade,
+       last_active   timestamp with time zone default now(),
+
+       created_at    timestamp with time zone not null default now(),
+       verified      bool not null,
+       verify_code   text
+);
+
+create function cascade_session_delete()
+       returns trigger
+       language plpgsql
+       as $$
+       begin
+              delete from device where device.device_id = old.device_id;
+              return null;
+       end
+       $$;
+
+create trigger user_session__cascade_device
+       after delete on user_session
+       for each row
+       when (old.device_id is not null)
+       execute function cascade_session_delete();
+
+create table verify_codes (
+       user_id     user_id not null primary key references users on delete cascade,
+       session_id  session_id references user_session on delete set null,
+       code        text not null,
+       expires_at  timestamp with time zone default (now() + '5 minutes'::interval)
+);
+
+create table device_commands (
+       index      bigserial not null,
+       device_id  device_id not null references device on delete cascade,
+       command    text not null check (length(command) <= 2048),
+       payload    json not null check (length(command) <= 16 * 1024),
+       expires    timestamp with time zone not null,
+       sender     varchar(32), -- hex(sender device_id)
+       primary key (index)
+);
+
+create index device_command__device_id__idx on device_commands (device_id);
+create index device_command__expires__idx on device_commands (expires);
+
+create table key_fetch (
+       id          key_fetch_id not null primary key,
+       hmac_key    hawk_key not null,
+       keys        bytea not null,
+       expires_at  timestamp with time zone not null default (now() + '5 minutes'::interval)
+);
+
+create index key_fetch__expires_at__idx on key_fetch (expires_at);
+
+create table oauth_token (
+       id          oauth_token_id not null primary key,
+       kind        oauth_token_kind not null,
+       user_id     user_id not null references users on delete cascade,
+       client_id   text not null,
+       scope       text not null check (length(scope) < 1024),
+       created_at  timestamp with time zone not null default now(),
+
+       -- refresh tokens can own sessions. refresh tokens are not owned by
+       -- sessions, otherwise the login procedure would immediately invalidate
+       -- tokens for fenix.
+       session_id  session_id
+           references user_session on delete cascade
+           check (session_id is null or kind = 'refresh'),
+
+       -- access tokens can be owned by refresh tokens or sessions. owned tokens
+       -- are invalidated with their parent, revoking eg sync tokens for removed
+       -- devices.
+       parent_refresh   oauth_token_id
+           references oauth_token on delete cascade
+           check (parent_refresh is null or kind = 'access'),
+       parent_session   session_id
+           references user_session on delete cascade
+           check (parent_session is null or kind = 'access'),
+       expires_at       timestamp with time zone check ((expires_at is not null) = (kind = 'access'))
+);
+
+create index oauth_token__expires_at__idx on oauth_token (expires_at);
+
+create function cascade_oauth_delete()
+       returns trigger
+       language plpgsql
+       as $$
+       begin
+              delete from user_session where user_session.session_id = old.session_id;
+              return null;
+       end
+       $$;
+
+create trigger oauth_token__cascade_session
+       after delete on oauth_token
+       for each row
+       when (old.session_id is not null)
+       execute function cascade_oauth_delete();
+
+create function cascade_device_delete()
+       returns trigger
+       language plpgsql
+       as $$
+       begin
+              with sessions as (
+                   delete from user_session where device_id = old.device_id returning session_id
+              ) delete from oauth_token where session_id in (select * from sessions);
+              return null;
+       end
+       $$;
+
+create trigger device__cascade_
+       after delete on device
+       for each row
+       execute function cascade_device_delete();
+
+create table oauth_authorization (
+       id              oauth_auth_id not null primary key,
+       client_id       text not null,
+       user_id         user_id not null references users on delete cascade,
+       scope           text not null,
+       access_type     oauth_access_type not null,
+       code_challenge  text not null,
+       keys_jwe        text check (length(keys_jwe) < 16 * 1024),
+       auth_at         timestamp with time zone not null,
+       expires_at      timestamp with time zone default (now() + '5 minutes'::interval)
+);
+
+create index oauth_authorization__expires_at__idx on oauth_authorization (expires_at);
+
+create table password_change_tokens (
+       id          password_change_id not null primary key,
+       user_id     user_id not null references users on delete cascade,
+       hmac_key    hawk_key not null,
+       expires_at  timestamp with time zone not null default (now() + '5 minutes'::interval),
+       forgot_code  text,
+
+       unique (user_id)
+);
+
+create table account_reset_tokens (
+       id          account_reset_id not null primary key,
+       user_id     user_id not null references users on delete cascade,
+       hmac_key    hawk_key not null,
+       expires_at  timestamp with time zone not null default (now() + '5 minutes'::interval),
+
+       unique (user_id)
+);
+
+create procedure reset_user_auth(
+       uid user_id,
+       salt text,
+       wwkb secret_key,
+       verify verify_hash)
+language sql
+begin atomic
+      delete from device where user_id = uid;
+      delete from user_session where user_id = uid;
+      delete from verify_codes where user_id = uid;
+      delete from oauth_token where user_id = uid;
+      delete from oauth_authorization where user_id = uid;
+      update users set auth_salt = salt, wrapwrap_kb = wwkb, verify_hash = verify where user_id = uid;
+end;
+
+create table invite_codes (
+       code        text not null primary key,
+       expires_at  timestamp with time zone not null
+);
+
+
+
+create procedure prune_expired_tokens()
+language sql
+begin atomic
+       delete from key_fetch where expires_at <= now();
+       delete from oauth_token where expires_at <= now();
+       delete from oauth_authorization where expires_at <= now();
+       delete from device_commands where expires <= now();
+       delete from invite_codes where expires_at <= now();
+end;
+
+create procedure prune_expired_verify_codes()
+language sql
+begin atomic
+       -- verify codes can be removed in two ways: either using them to verify an account,
+       -- in which case the account can stay, or by timeout, in which case the account must
+       -- go. triggers can't distinguish those cases.
+       with old_verify as (
+            delete from verify_codes where expires_at <= now() returning user_id
+       )
+       delete from users where user_id in (select user_id from old_verify);
+
+       delete from user_session where not verified and created_at <= now() - '5 minutes'::interval;
+       delete from password_change_tokens where expires_at <= now();
+       delete from account_reset_tokens where expires_at <= now();
+end;