foxchat/chat/migrations/20240116203402_init.sql

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

create table identity_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 users (
    id text primary key,
    instance_id text not null references identity_instances (id) on delete cascade,
    remote_user_id text not null,
    username       text not null,

    avatar text -- URL, not hash, as this is a remote file
);

create unique index users_remote_user_id_idx on users (instance_id, remote_user_id);
create unique index users_username_idx on users (instance_id, lower(username));

create table guilds (
    id       text primary key,
    owner_id text not null references users (id) on delete cascade,
    name     text not null
);

create table guilds_users (
    guild_id text not null references guilds (id) on delete cascade,
    user_id  text not null references users (id) on delete cascade,

    joined_at timestamptz not null default now(),

    primary key (guild_id, user_id)
);

create table channels (
    id       text primary key,
    guild_id text not null references guilds (id) on delete cascade,
    name     text not null,
    topic    text
);

create table messages (
    id         text primary key,
    channel_id text not null references channels (id) on delete cascade,
    author_id  text not null,
    updated_at timestamptz not null default now(),

    content text not null
);

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)
);