Repository Public
1-- SP Mail Next — productivité : étiquettes, recherches enregistrées, extraits, signatures, contacts unifiés, préférences de rédaction,2-- annulation d'envoi et rappel à l'envoi. Ajouts seulement : aucune colonne ni table existante ne change de sens.34-- Étiquettes : le nom et la couleur vivent ici ; sur les messages, l'étiquette est le mot-clé JMAP `lbl_<8 hex>` (immuable).5create table labels (6 id uuid primary key default gen_random_uuid(),7 user_id uuid not null references users(id) on delete cascade,8 name text not null check (length(name) between 1 and 40),9 color text not null default '#6b7280' check (color ~ '^#[0-9a-f]{6}$'),10 keyword text not null check (keyword ~ '^lbl_[0-9a-f]{8}$'),11 position int not null default 0,12 created_at timestamptz not null default now(),13 updated_at timestamptz not null default now(),14 unique (user_id, keyword)15);16create unique index labels_user_name on labels (user_id, lower(name));1718-- Recherches enregistrées (dossiers intelligents) : une requête en syntaxe d'opérateurs, rejouée par /api/next/search.19create table saved_searches (20 id uuid primary key default gen_random_uuid(),21 user_id uuid not null references users(id) on delete cascade,22 name text not null check (length(name) between 1 and 80),23 query text not null check (length(query) <= 1000),24 mailbox_ids uuid[],25 icon text,26 color text check (color is null or color ~ '^#[0-9a-f]{6}$'),27 pinned boolean not null default false,28 notify boolean not null default false,29 position int not null default 0,30 created_at timestamptz not null default now(),31 updated_at timestamptz not null default now()32);33create index on saved_searches (user_id, position);3435-- Extraits de texte réutilisables (HTML assaini à l'enregistrement).36create table snippets (37 id uuid primary key default gen_random_uuid(),38 user_id uuid not null references users(id) on delete cascade,39 name text not null check (length(name) between 1 and 80),40 shortcut text check (shortcut is null or shortcut ~ '^[a-z0-9_-]{1,24}$'),41 subject text,42 html text not null default '',43 text text not null default '',44 position int not null default 0,45 created_at timestamptz not null default now(),46 updated_at timestamptz not null default now()47);48create index on snippets (user_id, position);49create unique index snippets_user_shortcut on snippets (user_id, shortcut) where shortcut is not null;5051-- Signatures de l'utilisateur ; `default_for` / `default_for_replies` : identifiants d'adresses (table addresses) qu'elles revendiquent.52create table signatures (53 id uuid primary key default gen_random_uuid(),54 user_id uuid not null references users(id) on delete cascade,55 name text not null check (length(name) between 1 and 80),56 html text not null default '',57 text text not null default '',58 default_for uuid[] not null default '{}',59 default_for_replies uuid[] not null default '{}',60 position int not null default 0,61 created_at timestamptz not null default now(),62 updated_at timestamptz not null default now()63);64create index on signatures (user_id, position);6566-- Contacts : un carnet par utilisateur (toutes ses boîtes). `suggested` = correspondant moissonné, `saved` = fiche gérée par l'utilisateur.67create table contacts (68 id uuid primary key default gen_random_uuid(),69 user_id uuid not null references users(id) on delete cascade,70 kind text not null default 'saved' check (kind in ('saved','suggested')),71 name text not null default '',72 organization text,73 title text,74 phones jsonb not null default '[]',75 notes text,76 favorite boolean not null default false,77 vip boolean not null default false,78 avatar_color text not null default '#6b7280',79 sent_count int not null default 0,80 received_count int not null default 0,81 last_at timestamptz,82 created_at timestamptz not null default now(),83 updated_at timestamptz not null default now()84);85create index on contacts (user_id, kind);86create index on contacts (user_id, lower(name));8788create table contact_emails (89 contact_id uuid not null references contacts(id) on delete cascade,90 user_id uuid not null references users(id) on delete cascade,91 email text not null check (email = lower(email)),92 label text,93 position int not null default 0,94 primary key (contact_id, email),95 unique (user_id, email)96);9798create table contact_groups (99 id uuid primary key default gen_random_uuid(),100 user_id uuid not null references users(id) on delete cascade,101 name text not null check (length(name) between 1 and 60),102 color text not null default '#6b7280' check (color ~ '^#[0-9a-f]{6}$'),103 created_at timestamptz not null default now()104);105create unique index contact_groups_user_name on contact_groups (user_id, lower(name));106107create table contact_group_members (108 group_id uuid not null references contact_groups(id) on delete cascade,109 contact_id uuid not null references contacts(id) on delete cascade,110 primary key (group_id, contact_id)111);112create index on contact_group_members (contact_id);113114-- Copies « Envoyés » déjà comptées par la moisson (un message envoyé n'incrémente qu'une fois ses destinataires).115create table contact_sent_seen (116 mailbox_id uuid not null references mailboxes(id) on delete cascade,117 email_id text not null,118 primary key (mailbox_id, email_id)119);120121-- Préférences de rédaction : { undo_seconds, sensitive, warn_missing_attachment, warn_external }.122create table compose_prefs (123 user_id uuid primary key references users(id) on delete cascade,124 prefs jsonb not null default '{}',125 updated_at timestamptz not null default now()126);127128-- Annulation d'envoi et rappel à l'envoi, portés par la ligne d'envoi (colonnes nulles pour tout envoi ordinaire).129alter table outbound_messages add column undo_until timestamptz;130alter table outbound_messages add column remind_at timestamptz;131alter table outbound_messages add column remind_if_no_reply boolean;132alter table outbound_messages add column reminded_at timestamptz;133