Repository Public
1-- SP Message — initial schema. The server stores identities, routing metadata and ciphertext only.2create extension if not exists pgcrypto;34create table users (5 id uuid primary key default gen_random_uuid(),6 handle text not null unique check (handle ~ '^[a-z0-9_.-]{2,32}$'),7 display_name text not null check (char_length(display_name) between 1 and 64),8 role text not null default 'member' check (role in ('admin','member')),9 status text not null default 'active' check (status in ('active','pending','disabled','rejected')),10 identity_key bytea, -- Ed25519 public identity key (generated on the client)11 identity_set_at timestamptz,12 quota_bytes bigint not null default 5368709120,13 used_bytes bigint not null default 0,14 presence_opt_in boolean not null default false,15 invited_by uuid references users(id) on delete set null,16 invitation_id uuid,17 created_at timestamptz not null default now()18);1920create table passkeys (21 id text primary key, -- credential id (base64url)22 user_id uuid not null references users(id) on delete cascade,23 public_key bytea not null,24 counter bigint not null default 0,25 transports text[] not null default '{}',26 device_type text,27 backed_up boolean not null default false,28 label text not null default 'Passkey',29 created_at timestamptz not null default now(),30 last_used_at timestamptz31);32create index on passkeys(user_id);3334create table devices (35 id uuid primary key default gen_random_uuid(),36 user_id uuid not null references users(id) on delete cascade,37 label text not null,38 signature_key bytea not null, -- Ed25519 MLS leaf signature key39 cert bytea, -- identity-key signature over (user, device, signature_key)40 status text not null default 'pending' check (status in ('pending','active','revoked')),41 link_pub bytea, -- ephemeral X25519 key used once for enrolment42 link_payload bytea, -- HPKE-sealed identity key for the new device43 enrolled_via text, -- 'first' | 'approval' | 'recovery'44 created_at timestamptz not null default now(),45 last_seen_at timestamptz,46 revoked_at timestamptz47);48create index on devices(user_id);4950create table sessions (51 id uuid primary key default gen_random_uuid(),52 token_hash bytea not null unique,53 user_id uuid not null references users(id) on delete cascade,54 device_id uuid references devices(id) on delete set null,55 passkey_id text,56 ip text,57 user_agent text,58 created_at timestamptz not null default now(),59 last_seen_at timestamptz not null default now(),60 expires_at timestamptz not null,61 revoked_at timestamptz62);63create index on sessions(user_id);6465create table challenges (66 id uuid primary key default gen_random_uuid(),67 kind text not null,68 challenge text not null,69 user_id uuid,70 data jsonb not null default '{}',71 expires_at timestamptz not null72);7374create table bootstrap_tokens (75 token_hash bytea primary key,76 expires_at timestamptz not null,77 used_at timestamptz78);7980create table invitations (81 id uuid primary key default gen_random_uuid(),82 token_hash bytea not null unique,83 created_by uuid not null references users(id) on delete cascade,84 recipient text,85 expires_at timestamptz not null,86 max_uses int not null default 1 check (max_uses between 1 and 1000),87 uses int not null default 0,88 require_approval boolean not null default false,89 revoked_at timestamptz,90 created_at timestamptz not null default now()91);9293create table key_packages (94 id bigserial primary key,95 device_id uuid not null references devices(id) on delete cascade,96 data bytea not null,97 last_resort boolean not null default false,98 created_at timestamptz not null default now()99);100create index on key_packages(device_id);101102create table key_backups (103 user_id uuid primary key references users(id) on delete cascade,104 blob bytea not null, -- identity key sealed under the recovery key (client-side)105 updated_at timestamptz not null default now()106);107108create table conversations (109 id uuid primary key,110 kind text not null check (kind in ('dm','group')),111 dm_key text unique, -- sorted pair of user ids for direct conversations112 epoch bigint not null default 0,113 next_seq bigint not null default 1,114 created_by uuid references users(id) on delete set null,115 created_at timestamptz not null default now()116);117118create table conversation_members (119 conversation_id uuid not null references conversations(id) on delete cascade,120 user_id uuid not null references users(id) on delete cascade,121 role text not null default 'member' check (role in ('owner','member')),122 joined_at timestamptz not null default now(),123 left_at timestamptz,124 primary key (conversation_id, user_id)125);126create index on conversation_members(user_id);127128create table messages (129 conversation_id uuid not null references conversations(id) on delete cascade,130 seq bigint not null,131 sender_user uuid not null,132 sender_device uuid not null,133 kind text not null check (kind in ('app','commit')),134 epoch bigint not null,135 client_id uuid not null,136 data bytea not null, -- MLS ciphertext137 transient boolean not null default false,138 created_at timestamptz not null default now(),139 expires_at timestamptz,140 primary key (conversation_id, seq),141 unique (conversation_id, sender_device, client_id)142);143create index on messages(expires_at) where expires_at is not null;144145create table welcomes (146 id bigserial primary key,147 device_id uuid not null references devices(id) on delete cascade,148 conversation_id uuid not null references conversations(id) on delete cascade,149 after_seq bigint not null,150 data bytea not null,151 created_at timestamptz not null default now()152);153create index on welcomes(device_id);154155create table device_cursors (156 device_id uuid not null references devices(id) on delete cascade,157 conversation_id uuid not null references conversations(id) on delete cascade,158 delivered_seq bigint not null default 0,159 primary key (device_id, conversation_id)160);161162create table resync_requests (163 conversation_id uuid not null references conversations(id) on delete cascade,164 device_id uuid not null references devices(id) on delete cascade,165 created_at timestamptz not null default now(),166 primary key (conversation_id, device_id)167);168169create table files (170 id uuid primary key default gen_random_uuid(),171 owner_id uuid not null references users(id) on delete cascade,172 size bigint not null check (size > 0),173 chunk_size int not null,174 chunk_count int not null,175 completed_at timestamptz,176 created_at timestamptz not null default now(),177 expires_at timestamptz178);179create index on files(owner_id);180181create table file_access (182 file_id uuid not null references files(id) on delete cascade,183 conversation_id uuid not null references conversations(id) on delete cascade,184 primary key (file_id, conversation_id)185);186187create table self_blobs ( -- data a user syncs between their own devices, sealed client-side188 user_id uuid not null references users(id) on delete cascade,189 key text not null check (char_length(key) <= 120),190 data bytea not null,191 updated_at timestamptz not null default now(),192 primary key (user_id, key)193);194195create table settings (196 key text primary key,197 value jsonb not null198);199200create table audit_log (201 id bigserial primary key,202 at timestamptz not null default now(),203 actor uuid,204 action text not null,205 target text,206 meta jsonb not null default '{}',207 ip text208);209create index on audit_log(at desc);210