Repository Public
1-- SP Vault schema v1. The server stores ciphertext and public material only (docs/crypto-spec/SPV1.md §8).2create table accounts (3 id uuid primary key,4 handle text not null unique check (handle ~ '^[a-z0-9][a-z0-9._-]{1,31}$'),5 sig_pub text not null, enc_pub text not null,6 keyset jsonb not null,7 recovery_epoch int not null, recovery_envelope text not null, recovery_auth_hash text not null,8 key_epoch int not null default 1, -- incremented by account rekey9 audit_head text not null default '',10 agents_killed_at timestamptz,11 created_at timestamptz not null default now()12);13create table invites (14 token_hash text primary key, label text not null default '', created_at timestamptz not null default now(),15 expires_at timestamptz not null, used_at timestamptz, used_by uuid16);17create table credentials (18 id text primary key, account_id uuid not null references accounts on delete cascade,19 public_key bytea not null, counter bigint not null default 0, transports text[] not null default '{}',20 label text not null default '', created_at timestamptz not null default now(), last_used_at timestamptz21);22create index on credentials (account_id);23create table devices (24 id uuid primary key, account_id uuid not null references accounts on delete cascade,25 enc_pub text not null, sig_pub text not null, created_at_signed text not null, cert text not null,26 ark_envelope text not null, platform text not null, hardware boolean not null default false,27 created_at timestamptz not null default now(), last_seen_at timestamptz, revoked_at timestamptz28);29create index on devices (account_id);30create table sessions (31 token_hash text primary key, account_id uuid not null references accounts on delete cascade,32 device_id uuid references devices on delete cascade, kind text not null check (kind in ('pending','device','recovery')),33 created_at timestamptz not null default now(), expires_at timestamptz not null, strong_at timestamptz, last_seen_at timestamptz34);35create index on sessions (account_id);36create table challenges (37 id uuid primary key, kind text not null, account_id uuid, challenge text not null, data jsonb not null default '{}',38 expires_at timestamptz not null39);40create table pairings (41 id uuid primary key, account_id uuid not null references accounts on delete cascade, session_hash text not null,42 device_id uuid not null, enc_pub text not null, sig_pub text not null, created_at_signed text not null, tag text not null,43 platform text not null, hardware boolean not null default false,44 status text not null default 'pending' check (status in ('pending','approved','rejected','completed')),45 cert text, ark_envelope text, approved_by uuid, created_at timestamptz not null default now(), expires_at timestamptz not null46);47create table vaults (48 id uuid primary key, owner_id uuid not null references accounts on delete cascade, epoch int not null default 1,49 seq bigint not null default 0, created_at timestamptz not null default now(), deleted_at timestamptz50);51create table vault_members (52 vault_id uuid not null references vaults on delete cascade, account_id uuid not null references accounts on delete cascade,53 role text not null check (role in ('owner','editor','reader')), added_at timestamptz not null default now(),54 primary key (vault_id, account_id)55);56create index on vault_members (account_id);57create table vault_grants (58 vault_id uuid not null references vaults on delete cascade, epoch int not null, member_id uuid not null references accounts on delete cascade,59 role text not null, envelope text not null, granter_id uuid not null, sig text not null, created_at timestamptz not null default now(),60 primary key (vault_id, epoch, member_id)61);62create table records (63 vault_id uuid not null references vaults on delete cascade, id uuid not null, type text not null check (type in ('item','vault-meta','collection')),64 rev int not null, envelope jsonb not null, deleted boolean not null default false, seq bigint not null,65 attachments uuid[] not null default '{}', updated_at timestamptz not null default now(), device_id uuid,66 primary key (vault_id, id)67);68create index on records (vault_id, seq);69create table record_history (70 vault_id uuid not null, id uuid not null, rev int not null, envelope jsonb not null, deleted boolean not null default false,71 created_at timestamptz not null default now(), device_id uuid,72 primary key (vault_id, id, rev), foreign key (vault_id) references vaults on delete cascade73);74create table idempotency (75 account_id uuid not null, key text not null, status int not null, response jsonb not null, created_at timestamptz not null default now(),76 primary key (account_id, key)77);78create table attachments (79 id uuid primary key, vault_id uuid not null references vaults on delete cascade, account_id uuid not null,80 chunks int not null, size bigint not null, complete boolean not null default false, created_at timestamptz not null default now()81);82create table shares (83 id uuid primary key, sender_id uuid not null references accounts on delete cascade, kind text not null check (kind in ('account','link')),84 recipient_id uuid references accounts on delete cascade, box text not null, sig text, auth_hash text,85 expires_at timestamptz not null, max_views int, views int not null default 0, revoked_at timestamptz, created_at timestamptz not null default now()86);87create index on shares (recipient_id); create index on shares (sender_id);88create table audit_events (89 id bigserial primary key, account_id uuid not null references accounts on delete cascade, at timestamptz not null default now(),90 device_id uuid, actor text not null default 'user', kind text not null, detail jsonb not null default '{}',91 prev_hash text not null, hash text not null92);93create index on audit_events (account_id, id desc);94