SPB Git

Repository Public

001_init.sql 21.6 KB 452 lines sql

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

Theme