SPB Git

Repository Public

003_templates.sql 2.0 KB 52 lines sql

1-- SP Mail — modèles de courriel (bibliothèque personnelle, versions, journal des générations par IA).2create table templates (3  id          uuid primary key default gen_random_uuid(),4  user_id     uuid not null references users(id) on delete cascade,5  name        text not null,6  category    text not null default 'other',7  subject     text not null default '',8  preheader   text not null default '',9  html        text not null,10  text_body   text not null default '',11  variables   jsonb not null default '[]',   -- [{name, description, example, required}]12  brand       jsonb not null default '{}',13  source      text not null default 'manual' check (source in ('manual','ai')),14  ai_model    text,15  ai_brief    text,16  design_notes text not null default '',17  version     int not null default 1,18  created_at  timestamptz not null default now(),19  updated_at  timestamptz not null default now(),20  archived_at timestamptz21);22create index on templates(user_id, updated_at desc);2324-- Chaque enregistrement conserve la version précédente : rien n'est perdu par une retouche (manuelle ou par IA).25create table template_versions (26  template_id uuid not null references templates(id) on delete cascade,27  version     int not null,28  subject     text not null,29  preheader   text not null,30  html        text not null,31  text_body   text not null,32  variables   jsonb not null,33  note        text not null default '',34  created_at  timestamptz not null default now(),35  primary key (template_id, version)36);3738-- Journal des appels au modèle : qui, quand, combien de jetons. Ni le brief ni la sortie n'y figurent.39create table ai_generations (40  id            bigserial primary key,41  user_id       uuid references users(id) on delete set null,42  template_id   uuid references templates(id) on delete set null,43  at            timestamptz not null default now(),44  kind          text not null,45  model         text,46  input_tokens  int,47  output_tokens int,48  duration_ms   int,49  status        text not null50);51create index on ai_generations(user_id, at desc);52
Files main
Menu

Theme