SPB Git

Repository Public

006_intelligence_ops.sql 7.4 KB 157 lines sql

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

Theme