Repository Public
1-- SP Mail Next — notifications push (appareils, préférences, file durable) et mises en veille / rappels côté serveur.2-- Migration purement additive : push_devices reçoit des colonnes à valeur par défaut, tout le reste est nouveau.34alter table push_devices5 add column device_name text,6 add column locale text,7 add column app_version text,8 add column enabled boolean not null default true,9 add column prefs jsonb not null default '{}',10 add column last_registered_at timestamptz not null default now(),11 add column last_push_at timestamptz,12 add column last_error text,13 add column failure_count int not null default 0,14 -- dernière pastille réellement envoyée : une mise à jour silencieuse qui ne changerait rien n'est pas émise15 add column last_badge int;16create index if not exists push_devices_user_idx on push_devices(user_id);17create index if not exists push_devices_session_idx on push_devices(session_id);1819-- Préférences de notification d'un utilisateur (valables pour tous ses appareils).20create table push_prefs (21 user_id uuid primary key references users(id) on delete cascade,22 enabled boolean not null default true,23 preview text not null default 'sender' check (preview in ('none','sender','full')),24 quiet_hours jsonb, -- { start: "22:00", end: "07:00", tz: "America/Toronto" } ou null25 priority_senders text[] not null default '{}', -- adresses ou « @domaine » : passent outre les heures calmes26 priority_only boolean not null default false,27 mailboxes jsonb not null default '{}', -- { <mailbox_id>: false } coupe une boîte28 badge text not null default 'inbox' check (badge in ('inbox','none')),29 updated_at timestamptz not null default now()30);3132-- File durable des notifications : une ligne par (appareil, événement). `dedup_key` garantit une seule alerte par33-- (appareil, boîte, message) même si le veilleur rejoue un lot ; `attempts` compte les tentatives commencées.34create table push_outbox (35 id uuid primary key default gen_random_uuid(),36 device_id uuid not null references push_devices(id) on delete cascade,37 user_id uuid not null references users(id) on delete cascade,38 mailbox_id uuid references mailboxes(id) on delete cascade,39 email_id text,40 thread_id text,41 kind text not null check (kind in ('new','badge','snooze','reminder','alert','test')),42 payload jsonb not null default '{}',43 dedup_key text not null unique,44 status text not null default 'pending' check (status in ('pending','sent','failed','dropped')),45 attempts int not null default 0,46 next_attempt_at timestamptz not null default now(),47 locked_until timestamptz,48 apns_id text,49 last_status int,50 last_error text,51 created_at timestamptz not null default now(),52 sent_at timestamptz53);54create index push_outbox_due_idx on push_outbox(next_attempt_at) where status = 'pending';55create index push_outbox_user_idx on push_outbox(user_id, created_at desc);56create index push_outbox_device_idx on push_outbox(device_id);5758-- Mise en veille : le message est rangé dans le dossier « Snoozed » de sa boîte ; la ligne retient d'où il vient.59create table snoozes (60 id uuid primary key default gen_random_uuid(),61 user_id uuid not null references users(id) on delete cascade,62 mailbox_id uuid not null references mailboxes(id) on delete cascade,63 email_id text not null,64 thread_id text,65 subject text not null default '',66 sender jsonb, -- { name, email } de l'expéditeur, pour la liste et la notification67 origin_folder_ids text[] not null default '{}',68 snoozed_folder_id text not null,69 until timestamptz not null,70 status text not null default 'active' check (status in ('active','done','cancelled','missing')),71 attempts int not null default 0,72 locked_until timestamptz,73 last_error text,74 created_at timestamptz not null default now(),75 woken_at timestamptz76);77create unique index snoozes_active_idx on snoozes(mailbox_id, email_id) where status = 'active';78create index snoozes_due_idx on snoozes(until) where status = 'active';79create index snoozes_user_idx on snoozes(user_id, status, until);8081-- Rappel de suivi : évalué sur le serveur à l'échéance (« seulement si personne n'a répondu »).82create table reminders (83 id uuid primary key default gen_random_uuid(),84 user_id uuid not null references users(id) on delete cascade,85 mailbox_id uuid not null references mailboxes(id) on delete cascade,86 email_id text not null,87 thread_id text,88 subject text not null default '',89 counterpart jsonb, -- { name, email } de l'interlocuteur90 due_at timestamptz not null,91 only_if_no_reply boolean not null default false,92 thread_email_ids text[] not null default '{}', -- messages du fil à la création : tout message apparu depuis est une réponse possible93 note text,94 status text not null default 'pending' check (status in ('pending','due','done','resolved','cancelled')),95 attempts int not null default 0,96 locked_until timestamptz,97 last_error text,98 created_at timestamptz not null default now(),99 fired_at timestamptz100);101create index reminders_due_idx on reminders(due_at) where status = 'pending';102create index reminders_user_idx on reminders(user_id, status, due_at);103