SPB Git

Repository Public

004_push_snooze.sql 5.7 KB 103 lines sql

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

Theme