Repository Public
1-- SP Mail — schéma initial : identité (passkeys), sessions par appareil, boîtes reliées à Stalwart, audit.2create extension if not exists pgcrypto;34create table users (5 id uuid primary key default gen_random_uuid(),6 handle text not null unique,7 display_name text not null,8 role text not null default 'member' check (role in ('admin','member')),9 status text not null default 'active' check (status in ('active','disabled')),10 prefs jsonb not null default '{}',11 created_at timestamptz not null default now()12);1314create table passkeys (15 id text primary key,16 user_id uuid not null references users(id) on delete cascade,17 public_key bytea not null,18 counter bigint not null default 0,19 transports text[] not null default '{}',20 device_type text,21 backed_up boolean not null default false,22 label text not null default 'Passkey',23 created_at timestamptz not null default now(),24 last_used_at timestamptz25);26create index on passkeys(user_id);2728-- Une session par appareil : cookie (web) ou jeton porteur (apps natives). Seul le hachage du jeton est conservé.29create table sessions (30 id uuid primary key default gen_random_uuid(),31 token_hash bytea not null unique,32 user_id uuid not null references users(id) on delete cascade,33 kind text not null default 'web' check (kind in ('web','native')),34 device_name text,35 platform text,36 passkey_id text,37 ip text,38 user_agent text,39 created_at timestamptz not null default now(),40 last_seen_at timestamptz not null default now(),41 expires_at timestamptz not null,42 revoked_at timestamptz43);44create index on sessions(user_id);4546create table challenges (47 id uuid primary key default gen_random_uuid(),48 kind text not null,49 challenge text not null,50 user_id uuid,51 data jsonb not null default '{}',52 expires_at timestamptz not null53);5455-- Lien à usage unique : premier administrateur, récupération, ou ajout d'un utilisateur.56create table enrollment_tokens (57 token_hash bytea primary key,58 purpose text not null check (purpose in ('bootstrap','recover','invite')),59 user_id uuid references users(id) on delete cascade,60 expires_at timestamptz not null,61 used_at timestamptz62);6364-- Une boîte = un compte Stalwart. Le secret (mot de passe du compte, généré par SP Mail) est chiffré AES-256-GCM.65create table mailboxes (66 id uuid primary key default gen_random_uuid(),67 user_id uuid not null references users(id) on delete cascade,68 address text not null unique,69 display_name text not null default '',70 stalwart_id text not null,71 secret_enc bytea not null,72 position int not null default 0,73 created_at timestamptz not null default now()74);75create index on mailboxes(user_id);7677-- Jetons APNs des apps natives (aucun contenu de message n'est poussé).78create table push_devices (79 id uuid primary key default gen_random_uuid(),80 session_id uuid not null references sessions(id) on delete cascade,81 user_id uuid not null references users(id) on delete cascade,82 apns_token text not null,83 environment text not null default 'production' check (environment in ('production','sandbox')),84 bundle_id text not null,85 created_at timestamptz not null default now(),86 unique (apns_token, bundle_id)87);8889create table audit_log (90 id bigserial primary key,91 at timestamptz not null default now(),92 actor uuid,93 action text not null,94 target text,95 meta jsonb not null default '{}',96 ip text97);98create index on audit_log(at desc);99