SPB Git

Repository Public

002_api.sql 10.8 KB 241 lines sql

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

Theme