foxchat/identity/migrations/20240115015514_init.sql

create type account_role as enum ('user', 'admin');

create table accounts (
    id       text primary key,
    username text not null,
    email    text not null,
    password text not null, -- Hashed + salted password
    role     account_role not null default 'user',

    avatar       text null -- Avatar hash
);

create unique index users_username_idx on accounts (lower(username));

create type instance_status as enum ('active', 'suspended');

create table chat_instances (
    id         text primary key,
    domain     text not null unique,
    base_url   text not null,
    public_key text not null,
    status     instance_status not null default 'active',
    reason     text
);

create table chat_instance_accounts (
    account_id text not null references accounts (id) on delete cascade,
    chat_instance_id text not null references chat_instances (id) on delete cascade,

    primary key (account_id, chat_instance_id)
);

create table instance (
    id integer not null primary key default 1,
    public_key  text not null,
    private_key text not null,

    constraint singleton check (id = 1)
);