path: root/migrations/20220626163140_init.up.sql

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;