SPB Git

Repository Public

005_productivity.sql 6.3 KB 133 lines sql

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

Theme