Repository Public
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