Repository Public
1-- SP Mail — API universelle /api/v1 : adresses de première classe, clés d'API, file d'envoi persistante, webhooks.2-- Migration purement additive : aucune colonne ni table de 001 n'est modifiée, hormis l'ajout de sessions.reauth_at.34alter table sessions add column reauth_at timestamptz;56-- Chaque adresse utilisable est une identité de première classe, portée par une boîte physique (compte Stalwart).7create table addresses (8 id uuid primary key default gen_random_uuid(),9 address text not null unique check (address = lower(address)),10 kind text not null check (kind in ('mailbox','alias','shared','notification','send_only','receive_only','temporary')),11 mailbox_id uuid not null references mailboxes(id) on delete cascade,12 display_name text not null default '',13 signature_text text not null default '',14 signature_html text not null default '',15 avatar text check (avatar is null or length(avatar) <= 90000),16 can_send boolean not null default true,17 can_receive boolean not null default true,18 status text not null default 'active' check (status in ('active','suspended')),19 suspended_reason text,20 expires_at timestamptz,21 retention_days int check (retention_days is null or retention_days >= 1),22 retention_trash_days int check (retention_trash_days is null or retention_trash_days >= 1),23 retention_junk_days int check (retention_junk_days is null or retention_junk_days >= 1),24 quota_bytes bigint check (quota_bytes is null or quota_bytes > 0),25 reply_policy text not null default 'reject' check (reply_policy in ('reject','accept','discard')),26 protected boolean not null default false,27 extra_destinations text[] not null default '{}',28 stalwart_list_id text,29 created_at timestamptz not null default now(),30 updated_at timestamptz not null default now()31);32create index on addresses(mailbox_id);33create index on addresses(expires_at) where expires_at is not null;3435-- Boîte partagée : droits de lecture / d'envoi accordés à d'autres utilisateurs (mailboxes.user_id reste le propriétaire).36create table mailbox_grants (37 user_id uuid not null references users(id) on delete cascade,38 mailbox_id uuid not null references mailboxes(id) on delete cascade,39 can_read boolean not null default true,40 can_send boolean not null default false,41 created_at timestamptz not null default now(),42 primary key (user_id, mailbox_id)43);4445-- Réglages d'automatisation propres au serveur (réinjectés dans le script Sieve « spmail » après chaque sauvegarde).46create table mailbox_automation (47 mailbox_id uuid primary key references mailboxes(id) on delete cascade,48 forward_to text,49 forward_keep_copy boolean not null default true,50 updated_at timestamptz not null default now()51);5253-- Clés d'API : seul le SHA-256 du secret est conservé ; jeton = spm_<prefix>_<secret>.54create table api_keys (55 id uuid primary key default gen_random_uuid(),56 name text not null,57 description text not null default '',58 prefix text not null unique,59 secret_hash bytea not null,60 scopes text[] not null default '{}',61 address_ids uuid[] not null default '{}',62 all_addresses boolean not null default false,63 rate_per_minute int not null default 60 check (rate_per_minute between 1 and 6000),64 daily_send_limit int not null default 200 check (daily_send_limit between 0 and 100000),65 external_send_policy text not null default 'approval' check (external_send_policy in ('allow','approval','deny')),66 allowed_recipient_domains text[] not null default '{}',67 expires_at timestamptz,68 revoked_at timestamptz,69 last_used_at timestamptz,70 created_by uuid references users(id) on delete set null,71 rotated_from uuid,72 created_at timestamptz not null default now(),73 updated_at timestamptz not null default now()74);7576-- Jetons d'exploration : 10 minutes, portent exactement les droits d'une clé, marqués « explorer » dans le journal d'usage.77create table explorer_tokens (78 token_hash bytea primary key,79 key_id uuid not null references api_keys(id) on delete cascade,80 created_by uuid references users(id) on delete set null,81 expires_at timestamptz not null,82 created_at timestamptz not null default now()83);8485-- Journal d'usage : jamais de corps, de jeton ni de chaîne de requête.86create table api_usage (87 id bigserial primary key,88 key_id uuid not null references api_keys(id) on delete cascade,89 at timestamptz not null default now(),90 method text not null,91 route text not null,92 status int not null,93 duration_ms int not null,94 ip text,95 error_code text,96 explorer boolean not null default false97);98create index on api_usage(key_id, at desc);99create index on api_usage(at);100101create table idempotency_keys (102 principal text not null,103 idem_key text not null,104 payload_hash bytea not null,105 status int,106 response jsonb,107 created_at timestamptz not null default now(),108 primary key (principal, idem_key)109);110create index on idempotency_keys(created_at);111112-- File d'envoi persistante et suivi de remise. Aucun contenu : le message lui-même est un brouillon dans Stalwart.113create table outbound_messages (114 id text primary key,115 key_id uuid references api_keys(id) on delete set null,116 user_id uuid references users(id) on delete set null,117 address_id uuid references addresses(id) on delete set null,118 from_address text not null,119 mailbox_id uuid not null references mailboxes(id) on delete cascade,120 email_id text,121 submission_id text,122 recipients jsonb not null default '[]',123 status text not null check (status in ('queued','scheduled','pending_approval','sent','delivered','deferred','bounced','failed','cancelled')),124 external boolean not null default false,125 send_at timestamptz,126 attempts int not null default 0,127 last_error text,128 next_check_at timestamptz,129 locked_until timestamptz,130 submitted_at timestamptz,131 final_at timestamptz,132 created_at timestamptz not null default now(),133 updated_at timestamptz not null default now()134);135create index on outbound_messages(status, next_check_at);136create index on outbound_messages(key_id, created_at desc);137create index on outbound_messages(created_at desc);138139create table send_approvals (140 id uuid primary key default gen_random_uuid(),141 outbound_id text not null references outbound_messages(id) on delete cascade,142 key_id uuid references api_keys(id) on delete set null,143 status text not null default 'pending' check (status in ('pending','approved','rejected','expired')),144 external_recipients text[] not null default '{}',145 decided_by uuid references users(id) on delete set null,146 decided_at timestamptz,147 expires_at timestamptz not null,148 created_at timestamptz not null default now()149);150create index on send_approvals(status, expires_at);151152-- Fichiers téléversés (blobs JMAP) en attente d'être joints à un message.153create table files (154 id text primary key,155 key_id uuid references api_keys(id) on delete cascade,156 user_id uuid references users(id) on delete cascade,157 mailbox_id uuid not null references mailboxes(id) on delete cascade,158 blob_id text not null,159 name text not null,160 type text not null,161 size bigint not null,162 expires_at timestamptz not null,163 created_at timestamptz not null default now()164);165166create table webhooks (167 id uuid primary key default gen_random_uuid(),168 name text not null,169 key_id uuid references api_keys(id) on delete cascade,170 user_id uuid references users(id) on delete set null,171 url text not null,172 secret_enc bytea not null,173 events text[] not null,174 address_ids uuid[] not null default '{}',175 include_content boolean not null default false,176 active boolean not null default true,177 created_at timestamptz not null default now(),178 updated_at timestamptz not null default now()179);180181create table webhook_events (182 id text primary key,183 seq bigserial unique,184 type text not null,185 occurred_at timestamptz not null default now(),186 address_id uuid,187 address text,188 mailbox_id uuid,189 payload jsonb not null,190 content jsonb191);192create index on webhook_events(occurred_at);193194create table webhook_deliveries (195 id uuid primary key default gen_random_uuid(),196 event_id text not null references webhook_events(id) on delete cascade,197 webhook_id uuid not null references webhooks(id) on delete cascade,198 attempt int not null default 0,199 status text not null default 'pending' check (status in ('pending','retrying','delivered','failed')),200 next_attempt_at timestamptz,201 last_status int,202 last_error text,203 delivered_at timestamptz,204 locked_until timestamptz,205 created_at timestamptz not null default now()206);207create index on webhook_deliveries(status, next_attempt_at);208create index on webhook_deliveries(webhook_id, created_at desc);209210-- Destinations internes explicitement permises aux webhooks malgré la garde SSRF (hôte:port).211create table webhook_internal_allowlist (212 host_port text primary key,213 note text not null default '',214 created_by uuid references users(id) on delete set null,215 created_at timestamptz not null default now()216);217218-- Veilleur de réception : état JMAP persistant par boîte (rien n'est perdu ni réémis après un redémarrage).219create table mailbox_sync (220 mailbox_id uuid primary key references mailboxes(id) on delete cascade,221 email_state text,222 resyncs int not null default 0,223 locked_until timestamptz,224 updated_at timestamptz not null default now()225);226-- Messages reçus depuis l'initialisation du veilleur : sert à détecter « lu » et « supprimé » (au mieux).227create table watched_messages (228 mailbox_id uuid not null references mailboxes(id) on delete cascade,229 email_id text not null,230 address text,231 seen boolean not null default false,232 trashed boolean not null default false,233 received_at timestamptz not null default now(),234 primary key (mailbox_id, email_id)235);236237create table worker_state (238 name text primary key,239 last_run_at timestamptz not null default now()240);241