SPB Git

Repository Public

001_init.sql 8.0 KB 210 lines sql

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
Files main
Menu

Theme