Repository Public
1-- Sheet — initial schema.2-- Conventions: uuid keys, timestamptz everywhere, soft delete through deleted_at where a row may be referenced by3-- history, optimistic locking through an integer `version` on rows edited from the studio.45CREATE EXTENSION IF NOT EXISTS citext;6CREATE EXTENSION IF NOT EXISTS pg_trgm;7CREATE EXTENSION IF NOT EXISTS unaccent;89-- Accent-insensitive full-text configurations (French and English).10CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS $$ SELECT public.unaccent('public.unaccent', $1) $$;1112-- ── Administrator and authentication ─────────────────────────────────────────────────────────────────13-- One administrator is provisioned from the server's shell. There is no sign-up route; the table is plural only so14-- that a second editor could be added later without a schema change.15CREATE TABLE users (16 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),17 email citext NOT NULL UNIQUE,18 display_name text NOT NULL,19 role text NOT NULL DEFAULT 'admin' CHECK (role IN ('admin')),20 locale text NOT NULL DEFAULT 'fr' CHECK (locale IN ('fr', 'en')),21 disabled_at timestamptz,22 created_at timestamptz NOT NULL DEFAULT now()23);2425-- Passkeys: public material only. No biometric data ever reaches the server.26CREATE TABLE passkey_credentials (27 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),28 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,29 credential_id text NOT NULL UNIQUE,30 public_key bytea NOT NULL,31 counter bigint NOT NULL DEFAULT 0,32 transports text[] NOT NULL DEFAULT '{}',33 device_type text,34 backed_up boolean NOT NULL DEFAULT false,35 aaguid text,36 name text NOT NULL DEFAULT 'Passkey',37 created_at timestamptz NOT NULL DEFAULT now(),38 last_used_at timestamptz,39 revoked_at timestamptz40);41CREATE INDEX passkey_credentials_user ON passkey_credentials (user_id) WHERE revoked_at IS NULL;4243-- One-time enrolment tokens, issued from the shell only (first setup, recovery). Only the SHA-256 is stored.44CREATE TABLE setup_tokens (45 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),46 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,47 token_hash text NOT NULL UNIQUE,48 purpose text NOT NULL CHECK (purpose IN ('setup', 'recovery')),49 expires_at timestamptz NOT NULL,50 used_at timestamptz,51 created_at timestamptz NOT NULL DEFAULT now()52);5354-- Recovery codes: shown once at enrolment, stored hashed, one use each.55CREATE TABLE recovery_codes (56 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),57 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,58 code_hash text NOT NULL UNIQUE,59 used_at timestamptz,60 created_at timestamptz NOT NULL DEFAULT now()61);6263-- WebAuthn challenges: random, short-lived, deleted when read (single use). `binding` ties a challenge to the64-- browser that asked for it (hash of a pre-auth cookie) or to a session (re-authentication).65CREATE TABLE webauthn_challenges (66 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),67 challenge text NOT NULL,68 kind text NOT NULL CHECK (kind IN ('registration', 'authentication', 'reauthentication')),69 user_id uuid REFERENCES users(id) ON DELETE CASCADE,70 binding text NOT NULL,71 expires_at timestamptz NOT NULL,72 created_at timestamptz NOT NULL DEFAULT now()73);74CREATE INDEX webauthn_challenges_expiry ON webauthn_challenges (expires_at);7576CREATE TABLE sessions (77 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),78 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,79 token_hash text NOT NULL UNIQUE,80 csrf_token text NOT NULL,81 -- passkey: full access · setup: full access (the passkey was just enrolled) · recovery_code: may only enrol a passkey82 method text NOT NULL CHECK (method IN ('passkey', 'setup', 'recovery_code')),83 user_agent text NOT NULL DEFAULT '',84 ip text,85 created_at timestamptz NOT NULL DEFAULT now(),86 last_seen_at timestamptz NOT NULL DEFAULT now(),87 -- Last strong (passkey) verification in this session; sensitive actions need a recent one.88 reauth_at timestamptz,89 expires_at timestamptz NOT NULL,90 revoked_at timestamptz91);92CREATE INDEX sessions_user ON sessions (user_id) WHERE revoked_at IS NULL;9394CREATE TABLE rate_limits (95 key text NOT NULL,96 window_start timestamptz NOT NULL,97 count integer NOT NULL DEFAULT 0,98 PRIMARY KEY (key, window_start)99);100101CREATE TABLE audit_events (102 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,103 at timestamptz NOT NULL DEFAULT now(),104 user_id uuid REFERENCES users(id) ON DELETE SET NULL,105 actor text NOT NULL CHECK (actor IN ('admin', 'system', 'worker', 'anonymous')),106 action text NOT NULL,107 entity text NOT NULL DEFAULT '',108 entity_id text NOT NULL DEFAULT '',109 meta jsonb NOT NULL DEFAULT '{}',110 ip text111);112CREATE INDEX audit_events_at ON audit_events (at DESC);113CREATE INDEX audit_events_action ON audit_events (action, at DESC);114115-- Non-secret settings only (model choice, budgets, feature switches). API keys never live in the database.116CREATE TABLE settings (117 key text PRIMARY KEY,118 value jsonb NOT NULL,119 updated_at timestamptz NOT NULL DEFAULT now(),120 updated_by uuid REFERENCES users(id) ON DELETE SET NULL121);122123CREATE TABLE worker_heartbeat (124 name text PRIMARY KEY,125 beat_at timestamptz NOT NULL DEFAULT now(),126 info jsonb NOT NULL DEFAULT '{}'127);128129-- ── Taxonomy ─────────────────────────────────────────────────────────────────────────────────────────130CREATE TABLE topics (131 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),132 slug text NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),133 name_fr text NOT NULL,134 name_en text NOT NULL,135 description_fr text NOT NULL DEFAULT '',136 description_en text NOT NULL DEFAULT '',137 tone text NOT NULL DEFAULT 'blue',138 position integer NOT NULL DEFAULT 0,139 created_at timestamptz NOT NULL DEFAULT now()140);141142CREATE TABLE tags (143 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),144 slug text NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),145 name text NOT NULL,146 created_at timestamptz NOT NULL DEFAULT now()147);148149-- ── Sheets ───────────────────────────────────────────────────────────────────────────────────────────150-- A localisation group links the French and English versions of the same sheet (hreflang).151CREATE TABLE sheet_localizations (152 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),153 created_at timestamptz NOT NULL DEFAULT now()154);155156CREATE TABLE sheets (157 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),158 slug text NOT NULL CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$' AND length(slug) <= 96),159 locale text NOT NULL CHECK (locale IN ('fr', 'en')),160 localization_id uuid NOT NULL REFERENCES sheet_localizations(id),161 status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'in_review', 'published', 'archived')),162 title text NOT NULL,163 summary text NOT NULL DEFAULT '',164 -- Working copy shown in the studio. Always equal to the document of current_revision_id.165 doc jsonb NOT NULL,166 current_revision_id uuid,167 -- Pointer to the immutable snapshot the public sees; NULL when not published.168 publication_id uuid,169 first_published_at timestamptz,170 published_at timestamptz,171 featured boolean NOT NULL DEFAULT false,172 featured_position integer NOT NULL DEFAULT 0,173 allow_download boolean NOT NULL DEFAULT true,174 seo_title text,175 seo_description text,176 canonical_url text,177 og_asset_id uuid,178 version integer NOT NULL DEFAULT 1,179 created_by uuid REFERENCES users(id) ON DELETE SET NULL,180 created_at timestamptz NOT NULL DEFAULT now(),181 updated_at timestamptz NOT NULL DEFAULT now(),182 deleted_at timestamptz,183 CHECK ((status = 'published') = (publication_id IS NOT NULL))184);185-- A slug is unique among live sheets of the same language.186CREATE UNIQUE INDEX sheets_slug_locale ON sheets (locale, slug) WHERE deleted_at IS NULL;187CREATE UNIQUE INDEX sheets_localization_locale ON sheets (localization_id, locale) WHERE deleted_at IS NULL;188CREATE INDEX sheets_status ON sheets (status, updated_at DESC) WHERE deleted_at IS NULL;189190-- Every saved state of the working copy. Immutable.191CREATE TABLE sheet_revisions (192 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),193 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,194 number integer NOT NULL,195 doc jsonb NOT NULL,196 doc_hash text NOT NULL,197 -- manual: saved from the editor · ai_generation / ai_edit: produced by a Claude job · revert · import · template198 origin text NOT NULL CHECK (origin IN ('manual', 'ai_generation', 'ai_edit', 'revert', 'import', 'template', 'source_check')),199 summary text NOT NULL DEFAULT '',200 -- Structured patch that produced this revision from its parent, when there is one (AI edits).201 patch jsonb,202 parent_id uuid REFERENCES sheet_revisions(id) ON DELETE SET NULL,203 job_id uuid,204 created_by uuid REFERENCES users(id) ON DELETE SET NULL,205 created_at timestamptz NOT NULL DEFAULT now(),206 UNIQUE (sheet_id, number)207);208CREATE INDEX sheet_revisions_sheet ON sheet_revisions (sheet_id, number DESC);209ALTER TABLE sheets ADD CONSTRAINT sheets_current_revision FOREIGN KEY (current_revision_id) REFERENCES sheet_revisions(id) DEFERRABLE INITIALLY DEFERRED;210211-- What the public reads: an immutable copy taken at the moment of publication. Publishing again adds a row.212CREATE TABLE sheet_publications (213 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),214 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,215 revision_id uuid NOT NULL REFERENCES sheet_revisions(id),216 number integer NOT NULL,217 doc jsonb NOT NULL,218 slug text NOT NULL,219 title text NOT NULL,220 summary text NOT NULL DEFAULT '',221 reading_minutes integer NOT NULL DEFAULT 1,222 -- Plain text of the published document; the public search index is built from it and from nothing else.223 search_text text NOT NULL DEFAULT '',224 search_fr tsvector GENERATED ALWAYS AS (setweight(to_tsvector('french', f_unaccent(title)), 'A') || setweight(to_tsvector('french', f_unaccent(summary)), 'B') || setweight(to_tsvector('french', f_unaccent(search_text)), 'C')) STORED,225 search_en tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', f_unaccent(title)), 'A') || setweight(to_tsvector('english', f_unaccent(summary)), 'B') || setweight(to_tsvector('english', f_unaccent(search_text)), 'C')) STORED,226 published_by uuid REFERENCES users(id) ON DELETE SET NULL,227 published_at timestamptz NOT NULL DEFAULT now(),228 unpublished_at timestamptz,229 UNIQUE (sheet_id, number)230);231CREATE INDEX sheet_publications_fr ON sheet_publications USING gin (search_fr);232CREATE INDEX sheet_publications_en ON sheet_publications USING gin (search_en);233CREATE INDEX sheet_publications_title_trgm ON sheet_publications USING gin (f_unaccent(lower(title)) gin_trgm_ops);234ALTER TABLE sheets ADD CONSTRAINT sheets_publication FOREIGN KEY (publication_id) REFERENCES sheet_publications(id) DEFERRABLE INITIALLY DEFERRED;235236-- Old public addresses keep working after a slug change (301).237CREATE TABLE slug_redirects (238 locale text NOT NULL,239 old_slug text NOT NULL,240 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,241 created_at timestamptz NOT NULL DEFAULT now(),242 PRIMARY KEY (locale, old_slug)243);244245CREATE TABLE sheet_topics (246 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,247 topic_id uuid NOT NULL REFERENCES topics(id) ON DELETE CASCADE,248 PRIMARY KEY (sheet_id, topic_id)249);250CREATE INDEX sheet_topics_topic ON sheet_topics (topic_id);251252CREATE TABLE sheet_tags (253 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,254 tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE,255 PRIMARY KEY (sheet_id, tag_id)256);257CREATE INDEX sheet_tags_tag ON sheet_tags (tag_id);258259-- Private preview links: random token (hash stored), short life, never indexable.260CREATE TABLE preview_tokens (261 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),262 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,263 token_hash text NOT NULL UNIQUE,264 expires_at timestamptz NOT NULL,265 created_by uuid REFERENCES users(id) ON DELETE SET NULL,266 created_at timestamptz NOT NULL DEFAULT now(),267 revoked_at timestamptz268);269270-- ── Media, data and sources ──────────────────────────────────────────────────────────────────────────271CREATE TABLE assets (272 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),273 kind text NOT NULL CHECK (kind IN ('image', 'svg', 'pdf', 'text', 'csv', 'json', 'export')),274 filename text NOT NULL,275 mime text NOT NULL,276 bytes bigint NOT NULL,277 sha256 text NOT NULL,278 storage_key text NOT NULL UNIQUE,279 width integer,280 height integer,281 alt text NOT NULL DEFAULT '',282 credit text NOT NULL DEFAULT '',283 license text NOT NULL DEFAULT '',284 -- clean: scanned · unscanned: no scanner configured · infected rows are never stored285 scan_status text NOT NULL DEFAULT 'unscanned' CHECK (scan_status IN ('clean', 'unscanned')),286 -- Public assets (images of published sheets) are served without a session; the others never are.287 is_public boolean NOT NULL DEFAULT false,288 created_by uuid REFERENCES users(id) ON DELETE SET NULL,289 created_at timestamptz NOT NULL DEFAULT now(),290 deleted_at timestamptz291);292CREATE INDEX assets_kind ON assets (kind, created_at DESC) WHERE deleted_at IS NULL;293CREATE INDEX assets_sha ON assets (sha256);294ALTER TABLE sheets ADD CONSTRAINT sheets_og_asset FOREIGN KEY (og_asset_id) REFERENCES assets(id) ON DELETE SET NULL;295296-- Tabular data parsed from an uploaded CSV/JSON asset: what charts are built from.297CREATE TABLE datasets (298 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),299 asset_id uuid NOT NULL UNIQUE REFERENCES assets(id) ON DELETE CASCADE,300 name text NOT NULL,301 columns jsonb NOT NULL, -- [{ name, type: number|string }]302 row_count integer NOT NULL,303 sample jsonb NOT NULL, -- first rows, for the picker304 source_note text NOT NULL DEFAULT '',305 created_at timestamptz NOT NULL DEFAULT now()306);307308-- Library of references across sheets. The copy inside each document stays authoritative for rendering.309CREATE TABLE sources (310 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),311 fingerprint text NOT NULL UNIQUE, -- doi:… | url:… | title:…312 type text NOT NULL,313 title text NOT NULL,314 authors text[] NOT NULL DEFAULT '{}',315 year integer,316 container text,317 doi text,318 url text,319 status text NOT NULL DEFAULT 'unverified' CHECK (status IN ('unverified', 'resolved', 'reviewed', 'unreachable')),320 checked_at timestamptz,321 check_detail jsonb NOT NULL DEFAULT '{}',322 created_at timestamptz NOT NULL DEFAULT now(),323 updated_at timestamptz NOT NULL DEFAULT now()324);325326CREATE TABLE sheet_sources (327 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,328 source_id uuid NOT NULL REFERENCES sources(id) ON DELETE CASCADE,329 local_id text NOT NULL, -- id of the source inside the document330 PRIMARY KEY (sheet_id, source_id)331);332333CREATE TABLE templates (334 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),335 -- Built-in templates live in code (src/templates) and are referenced by key; rows here are the author's own.336 key text NOT NULL UNIQUE,337 title text NOT NULL,338 description text NOT NULL DEFAULT '',339 locale text NOT NULL CHECK (locale IN ('fr', 'en')),340 category text NOT NULL DEFAULT 'custom',341 doc jsonb NOT NULL,342 created_by uuid REFERENCES users(id) ON DELETE SET NULL,343 created_at timestamptz NOT NULL DEFAULT now(),344 updated_at timestamptz NOT NULL DEFAULT now(),345 deleted_at timestamptz346);347348-- ── Claude studio ────────────────────────────────────────────────────────────────────────────────────349CREATE TABLE ai_conversations (350 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),351 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,352 created_by uuid REFERENCES users(id) ON DELETE SET NULL,353 created_at timestamptz NOT NULL DEFAULT now()354);355CREATE UNIQUE INDEX ai_conversations_sheet ON ai_conversations (sheet_id);356357CREATE TABLE ai_messages (358 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),359 conversation_id uuid NOT NULL REFERENCES ai_conversations(id) ON DELETE CASCADE,360 role text NOT NULL CHECK (role IN ('user', 'assistant', 'system')),361 -- What the administrator typed, or the assistant's explanation of its change.362 content text NOT NULL,363 job_id uuid,364 revision_id uuid REFERENCES sheet_revisions(id) ON DELETE SET NULL,365 -- Block-level diff of the change (added/removed/changed ids), for display.366 diff jsonb,367 created_at timestamptz NOT NULL DEFAULT now()368);369CREATE INDEX ai_messages_conversation ON ai_messages (conversation_id, created_at);370371-- Persistent job queue for everything long: outline, generation, AI edit, translation, source check.372-- Workers claim rows with FOR UPDATE SKIP LOCKED; a job survives a restart of either process.373CREATE TABLE generation_jobs (374 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),375 kind text NOT NULL CHECK (kind IN ('outline', 'generate', 'edit', 'translate', 'verify_sources')),376 status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'succeeded', 'failed', 'cancelled')),377 sheet_id uuid REFERENCES sheets(id) ON DELETE CASCADE,378 -- Validated request: brief, instruction, attachment asset ids, base revision…379 input jsonb NOT NULL,380 -- Result: outline, revision id, diff, quality report.381 output jsonb,382 -- Stage shown to the administrator while it runs.383 stage text NOT NULL DEFAULT 'queued',384 progress jsonb NOT NULL DEFAULT '{}',385 error jsonb,386 attempts integer NOT NULL DEFAULT 0,387 max_attempts integer NOT NULL DEFAULT 3,388 run_after timestamptz NOT NULL DEFAULT now(),389 cancel_requested boolean NOT NULL DEFAULT false,390 idempotency_key text UNIQUE,391 model text,392 prompt_version text,393 locked_by text,394 locked_at timestamptz,395 created_by uuid REFERENCES users(id) ON DELETE SET NULL,396 created_at timestamptz NOT NULL DEFAULT now(),397 started_at timestamptz,398 finished_at timestamptz399);400CREATE INDEX generation_jobs_queue ON generation_jobs (run_after) WHERE status = 'queued';401CREATE INDEX generation_jobs_sheet ON generation_jobs (sheet_id, created_at DESC);402403-- One row per model call: the ledger budgets are checked against.404CREATE TABLE generation_usage (405 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,406 job_id uuid REFERENCES generation_jobs(id) ON DELETE SET NULL,407 at timestamptz NOT NULL DEFAULT now(),408 model text NOT NULL,409 stage text NOT NULL,410 input_tokens integer NOT NULL DEFAULT 0,411 output_tokens integer NOT NULL DEFAULT 0,412 cache_read_tokens integer NOT NULL DEFAULT 0,413 cache_write_tokens integer NOT NULL DEFAULT 0,414 cost_usd numeric(12, 6) NOT NULL DEFAULT 0,415 duration_ms integer NOT NULL DEFAULT 0,416 stop_reason text,417 request_id text418);419CREATE INDEX generation_usage_at ON generation_usage (at DESC);420CREATE INDEX generation_usage_job ON generation_usage (job_id);421422CREATE TABLE export_jobs (423 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),424 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,425 revision_id uuid REFERENCES sheet_revisions(id) ON DELETE CASCADE,426 publication_id uuid REFERENCES sheet_publications(id) ON DELETE CASCADE,427 format text NOT NULL CHECK (format IN ('svg', 'png', 'pdf', 'json')),428 options jsonb NOT NULL DEFAULT '{}', -- { scale: 1|2|4, blockId, }429 status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'succeeded', 'failed')),430 asset_id uuid REFERENCES assets(id) ON DELETE SET NULL,431 error text,432 attempts integer NOT NULL DEFAULT 0,433 run_after timestamptz NOT NULL DEFAULT now(),434 locked_by text,435 locked_at timestamptz,436 -- Exports attached to a publication are offered to readers; the others belong to the studio.437 is_public boolean NOT NULL DEFAULT false,438 created_by uuid REFERENCES users(id) ON DELETE SET NULL,439 created_at timestamptz NOT NULL DEFAULT now(),440 finished_at timestamptz441);442CREATE INDEX export_jobs_queue ON export_jobs (run_after) WHERE status = 'queued';443CREATE INDEX export_jobs_sheet ON export_jobs (sheet_id, created_at DESC);444445-- Privacy-preserving view counter: one row per sheet and day. No address, no cookie, no identifier.446CREATE TABLE sheet_views (447 sheet_id uuid NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,448 day date NOT NULL,449 views integer NOT NULL DEFAULT 0,450 PRIMARY KEY (sheet_id, day)451);452