Repository Public
1-- SP Mail Next — couche d'intelligence facultative (réglages, usage, cache, index sémantique), moteur de règles après livraison,2-- alertes d'exploitation et relevés quotidiens. Ajouts seulement : aucune table ni colonne existante ne change.3-- Les tables d'autres migrations (labels, snippets, snoozes, push_outbox…) ne sont référencées qu'à l'exécution, jamais par clé étrangère.45-- Réglages d'intelligence par utilisateur. `off` par défaut : rien n'est envoyé à un modèle sans choix explicite.6create table ai_settings (7 user_id uuid primary key references users(id) on delete cascade,8 provider text not null default 'off' check (provider in ('off','local','anthropic')),9 consent_external_at timestamptz, -- consentement explicite à un fournisseur externe (Anthropic)10 features jsonb not null default '{}', -- { summary, reply, write, triage, semantic }11 language text not null default 'auto' check (language in ('auto','fr','en')),12 updated_at timestamptz not null default now()13);1415-- Journal d'usage : qui, quand, quoi, quel fournisseur, combien — JAMAIS de contenu (ni invite, ni sortie).16create table ai_usage (17 id bigserial primary key,18 user_id uuid not null references users(id) on delete cascade,19 at timestamptz not null default now(),20 feature text not null,21 provider text not null,22 model text not null default '',23 input_tokens int not null default 0,24 output_tokens int not null default 0,25 duration_ms int not null default 0,26 status text not null, -- ok | cached | error | invalid_output | refused27 error_code text28);29create index ai_usage_user_idx on ai_usage(user_id, at desc);3031-- Cache court des sorties de modèle (tri, résumés). Clé = SHA-256 de (boîte, fil ou message, dernier message, fournisseur, modèle…).32-- La valeur est scellée (AES-256-GCM, clé maîtresse) : aucune sortie de modèle n'est stockée en clair.33create table ai_cache (34 user_id uuid not null references users(id) on delete cascade,35 kind text not null,36 key bytea not null,37 provider text not null,38 model text not null,39 value_enc bytea not null,40 created_at timestamptz not null default now(),41 expires_at timestamptz not null,42 primary key (user_id, kind, key)43);44create index ai_cache_expiry_idx on ai_cache(expires_at);4546-- Index sémantique : un vecteur par (boîte, message, modèle d'embedding). Classement par cosinus dans le processus (D-N8).47create table mail_embeddings (48 id bigserial primary key,49 user_id uuid not null references users(id) on delete cascade,50 mailbox_id uuid not null references mailboxes(id) on delete cascade,51 email_id text not null,52 thread_id text,53 model text not null,54 dim int not null,55 vec real[] not null,56 received_at timestamptz,57 created_at timestamptz not null default now(),58 unique (mailbox_id, email_id, model)59);60create index mail_embeddings_user_idx on mail_embeddings(user_id, model);6162-- File d'indexation : remplie par le crochet du veilleur (réceptions) et par la reconstruction ; vidée par un travailleur.63create table semantic_queue (64 id bigserial primary key,65 user_id uuid not null references users(id) on delete cascade,66 mailbox_id uuid not null references mailboxes(id) on delete cascade,67 email_id text not null,68 attempts int not null default 0,69 next_attempt_at timestamptz not null default now(),70 locked_until timestamptz,71 last_error text,72 created_at timestamptz not null default now(),73 unique (mailbox_id, email_id)74);75create index semantic_queue_due_idx on semantic_queue(next_attempt_at);7677-- Règles après livraison (D-N9). Conditions et actions validées par schéma à l'écriture.78create table rules (79 id uuid primary key default gen_random_uuid(),80 user_id uuid not null references users(id) on delete cascade,81 name text not null check (length(name) between 1 and 80),82 enabled boolean not null default true,83 position int not null default 0,84 mailbox_ids uuid[], -- null = toutes les boîtes de l'utilisateur85 match text not null default 'all' check (match in ('all','any')),86 conditions jsonb not null default '[]',87 actions jsonb not null default '[]',88 stop boolean not null default false,89 created_at timestamptz not null default now(),90 updated_at timestamptz not null default now()91);92create index rules_user_idx on rules(user_id, position);9394-- Exécutions : une seule par (règle, boîte, message) — c'est la garantie d'idempotence. `actions` = ce qui a été fait et les erreurs.95create table rule_runs (96 id bigserial primary key,97 rule_id uuid not null references rules(id) on delete cascade,98 user_id uuid not null references users(id) on delete cascade,99 mailbox_id uuid not null references mailboxes(id) on delete cascade,100 email_id text not null,101 status text not null default 'running' check (status in ('running','ok','partial','error','interrupted')),102 actions jsonb not null default '[]',103 error text,104 at timestamptz not null default now(),105 finished_at timestamptz,106 unique (rule_id, mailbox_id, email_id)107);108create index rule_runs_rule_idx on rule_runs(rule_id, at desc);109110-- File durable des évaluations : une ligne par message reçu, écrite dans la transaction du veilleur.111create table rule_jobs (112 id bigserial primary key,113 user_id uuid not null references users(id) on delete cascade,114 mailbox_id uuid not null references mailboxes(id) on delete cascade,115 email_id text not null,116 attempts int not null default 0,117 next_attempt_at timestamptz not null default now(),118 locked_until timestamptz,119 last_error text,120 created_at timestamptz not null default now(),121 unique (mailbox_id, email_id)122);123create index rule_jobs_due_idx on rule_jobs(next_attempt_at);124125-- Alertes d'exploitation : une seule alerte non résolue par condition (`key`).126create table alerts (127 id uuid primary key default gen_random_uuid(),128 key text not null,129 severity text not null check (severity in ('warn','fail')),130 title text not null,131 detail text not null default '',132 state text not null default 'open' check (state in ('open','acked','resolved')),133 first_seen timestamptz not null default now(),134 last_seen timestamptz not null default now(),135 count int not null default 1,136 notified_at timestamptz,137 acked_by uuid references users(id) on delete set null,138 acked_at timestamptz,139 resolved_at timestamptz140);141create unique index alerts_open_key on alerts(key) where state <> 'resolved';142create index alerts_seen_idx on alerts(last_seen desc);143144-- Relevé quotidien par boîte : espace utilisé et compteurs de remise (les tables sources sont purgées, le relevé reste).145create table usage_snapshots (146 day date not null,147 mailbox_id uuid not null references mailboxes(id) on delete cascade,148 used_bytes bigint,149 received int not null default 0,150 sent int not null default 0,151 delivered int not null default 0,152 bounced int not null default 0,153 failed int not null default 0,154 updated_at timestamptz not null default now(),155 primary key (day, mailbox_id)156);157