SPB Git

Repository Public

README.md 58.0 KB 878 lines markdown

1<div align="center">23<img src="docs/brand/logo.svg" alt="Sheet logo" width="560">45<br><br>67<img src="docs/brand/icon.png" alt="Sheet icon" width="112" height="112">89# Sheet — by Simon-Pierre Boucher1011**A self-hosted publishing platform for scientific and technical explainer sheets: one idea per page, carried by a vector diagram, defined equations, charts, a quiz or an interactive simulation, with cited sources — and a passkey-only studio with a Claude-powered assistant that never publishes by itself.**1213**Author: [Simon-Pierre Boucher](https://www.spboucher.ai)**1415[![sheet.spboucher.ai](https://img.shields.io/website?url=https%3A%2F%2Fsheet.spboucher.ai&label=sheet.spboucher.ai&style=for-the-badge&up_message=online&down_message=offline)](https://sheet.spboucher.ai)16![version](https://img.shields.io/badge/version-1.0.0-2f4bff?style=for-the-badge)17![platform](https://img.shields.io/badge/platform-web%20%C2%B7%20PWA-0e1726?style=for-the-badge)18![languages](https://img.shields.io/badge/languages-FR%20%C2%B7%20EN-e8452c?style=for-the-badge)19![license](https://img.shields.io/badge/license-all%20rights%20reserved-lightgrey?style=for-the-badge)20![author](https://img.shields.io/badge/author-Simon--Pierre%20Boucher-0A66C2?style=for-the-badge)2122![Next.js](https://img.shields.io/badge/Next.js-16.4-000000?style=flat-square&logo=nextdotjs&logoColor=white)23![React](https://img.shields.io/badge/React-19.3-20232A?style=flat-square&logo=react&logoColor=white)24![TypeScript](https://img.shields.io/badge/TypeScript-6.0-3178C6?style=flat-square&logo=typescript&logoColor=white)25![Tailwind CSS](https://img.shields.io/badge/Tailwind%20CSS-4.3-06B6D4?style=flat-square&logo=tailwindcss&logoColor=white)26![PostgreSQL](https://img.shields.io/badge/PostgreSQL-17%20%C2%B7%20citext%20%C2%B7%20pg__trgm%20%C2%B7%20unaccent-4169E1?style=flat-square&logo=postgresql&logoColor=white)27![postgres.js](https://img.shields.io/badge/postgres.js-4.1-336791?style=flat-square)28![WebAuthn](https://img.shields.io/badge/WebAuthn-SimpleWebAuthn%2014.1.0-3423A6?style=flat-square&logo=webauthn&logoColor=white)29![Anthropic SDK](https://img.shields.io/badge/%40anthropic--ai%2Fsdk-1.8-D97757?style=flat-square&logo=anthropic&logoColor=white)30![Zod](https://img.shields.io/badge/Zod-4.6-3E67B1?style=flat-square&logo=zod&logoColor=white)31![KaTeX](https://img.shields.io/badge/KaTeX-0.19-329894?style=flat-square)32![dagre](https://img.shields.io/badge/dagre-3.1-555555?style=flat-square)33![d3](https://img.shields.io/badge/d3-array%20%C2%B7%20scale%20%C2%B7%20shape-F9A03C?style=flat-square&logo=d3&logoColor=white)34![Playwright](https://img.shields.io/badge/Playwright-1.65-2EAD33?style=flat-square&logo=playwright&logoColor=white)35![Vitest](https://img.shields.io/badge/Vitest-5.1-6E9F18?style=flat-square&logo=vitest&logoColor=white)36![ESLint](https://img.shields.io/badge/ESLint-10-4B32C3?style=flat-square&logo=eslint&logoColor=white)37![Node.js](https://img.shields.io/badge/Node.js-26%20%C2%B7%20tsx-5FA04E?style=flat-square&logo=nodedotjs&logoColor=white)38![Redis](https://img.shields.io/badge/Redis-none%20%C2%B7%20PostgreSQL%20queue-lightgrey?style=flat-square&logo=redis&logoColor=white)3940[![commits](https://git.spboucher.ai/badge/sheet/commits.svg?style=flat-square)](https://git.spboucher.ai/sheet/commits)41[![last commit](https://git.spboucher.ai/badge/sheet/last-commit.svg?style=flat-square)](https://git.spboucher.ai/sheet/commits)42[![activity](https://git.spboucher.ai/badge/sheet/activity.svg?style=flat-square)](https://git.spboucher.ai/sheet/insights)43[![language](https://git.spboucher.ai/badge/sheet/language.svg?style=flat-square)](https://git.spboucher.ai/sheet)44[![languages](https://git.spboucher.ai/badge/sheet/languages.svg?style=flat-square)](https://git.spboucher.ai/sheet/insights)45[![size](https://git.spboucher.ai/badge/sheet/size.svg?style=flat-square)](https://git.spboucher.ai/sheet)46[![branches](https://git.spboucher.ai/badge/sheet/branches.svg?style=flat-square)](https://git.spboucher.ai/sheet/branches)47[![tags](https://git.spboucher.ai/badge/sheet/tags.svg?style=flat-square)](https://git.spboucher.ai/sheet/tags)48[![clones](https://git.spboucher.ai/badge/sheet/clones.svg?style=flat-square)](https://git.spboucher.ai/sheet)4950![TypeScript files](https://img.shields.io/badge/TypeScript%20files-223-informational?style=flat-square)51![TypeScript lines](https://img.shields.io/badge/TypeScript%20lines-26%20963-informational?style=flat-square)52![API endpoints](https://img.shields.io/badge/API%20endpoints-66-blue?style=flat-square)53![pages](https://img.shields.io/badge/pages-17-blue?style=flat-square)54![tables](https://img.shields.io/badge/tables-31-blue?style=flat-square)55![block types](https://img.shields.io/badge/block%20types-23-blue?style=flat-square)56![simulations](https://img.shields.io/badge/simulations-13-blue?style=flat-square)57![templates](https://img.shields.io/badge/built--in%20templates-12-blue?style=flat-square)58![unit tests](https://img.shields.io/badge/unit%20tests-711-brightgreen?style=flat-square)59![integration tests](https://img.shields.io/badge/integration%20tests-53%20%2B%204-brightgreen?style=flat-square)60![browser tests](https://img.shields.io/badge/browser%20tests-27-brightgreen?style=flat-square)61![Lighthouse](https://img.shields.io/badge/Lighthouse%20home-100%20%C2%B7%20100%20%C2%B7%20100%20%C2%B7%20100-brightgreen?style=flat-square&logo=lighthouse&logoColor=white)6263![node](https://img.shields.io/badge/node-M2M32c-8A2BE2?style=flat-square&logo=apple&logoColor=white)64![port](https://img.shields.io/badge/port-8700-informational?style=flat-square)65![PM2](https://img.shields.io/badge/PM2-sheet--web%20%C2%B7%20sheet--worker%20%C2%B7%20sheet--backup-2B037A?style=flat-square&logo=pm2&logoColor=white)66![deploy](https://img.shields.io/badge/deploy-mld-orange?style=flat-square)67![tunnel](https://img.shields.io/badge/tunnel-MacLustr%20Tunnel%20%C2%B7%20Caddy%20%2B%20WireGuard-1F8B4C?style=flat-square&logo=wireguard&logoColor=white)6869</div>7071---7273## Table of contents7475- [Overview](#-overview)76- [Features](#-features)77- [Architecture](#-architecture)78- [Stack](#-stack)79- [Repository layout](#-repository-layout)80- [Requirements](#-requirements)81- [Local install](#-local-install)82- [Commands](#-commands)83- [Configuration](#-configuration)84- [Routes](#-routes)85- [API](#-api)86- [Document format](#-document-format)87- [Publication model](#-publication-model)88- [Data model](#-data-model)89- [Claude studio pipeline](#-claude-studio-pipeline)90- [Security and privacy](#-security-and-privacy)91- [Testing](#-testing)92- [Deployment](#-deployment)93- [Operations and troubleshooting](#-operations-and-troubleshooting)94- [Status and known limitations](#-status-and-known-limitations)95- [Documentation](#-documentation)96- [Author](#-author)97- [License](#-license)9899---100101## 🎯 Overview102103Sheet is a self-hosted publishing platform for scientific and technical explainer sheets: one idea per page, carried by a vector diagram, defined equations, charts, a quiz or an interactive simulation, with cited sources. Readers need no account. A sheet is a JSON document that follows a strict schema (`src/schema`); the same renderer draws it on the public site, in the studio, in private previews and in the PDF/PNG/SVG exports.104105The studio at `/admin` is for a single administrator who signs in with a passkey. It contains a block editor, a visual diagram editor, an asset library, revision history, and a Claude-powered assistant that plans a sheet, drafts it, edits it through structured patches and translates it between French and English. Nothing the model produces is published automatically: every generation lands as a draft revision, and publication is an explicit act that is refused while the quality report has blocking errors.106107Production: **https://sheet.spboucher.ai** — deployed on 2026-11-17 on the MacLustr cluster (node `M2M32c`, port 8700).108109Sheet is a standalone project of the [spboucher.ai](https://www.spboucher.ai) application family: it has no native app and no sister repository.110111---112113## ✨ Features114115### Public site (`src/app/(site)`, `src/app/api/public`)116117- Home, topic pages, search (PostgreSQL full-text + trigram, French and English, accent-insensitive), sheet pages at `/s/<slug>`, an about page.118- RSS (`/rss.xml`), JSON Feed (`/api/public/feed`), sitemap with `hreflang` alternates, `robots.txt`, JSON-LD, a generated Open Graph image per sheet.119- A read-only JSON API over published sheets (`/api/public/sheets`, `/search`, `/topics`).120- Interface in French or English (cookie `sheet_locale`, otherwise `Accept-Language`), light/dark mode stored in the browser.121- A per-sheet, per-day view counter that stores no visitor data (can be switched off).122- A small service worker: caches hashed static files, shows an offline page; pages and API calls always go to the network.123124### Studio (`src/app/admin`, `src/components/admin`, `src/components/editor`)125126- Passkey-only sign-in (WebAuthn), recovery codes, passkey and session management, audit log.127- Creation assistant: brief → outline you can correct → generated draft; or start from one of 12 built-in templates, your own templates, an imported JSON, or an empty sheet.128- Editor: block library (23 block types), canvas with selection toolbar, property forms validated against the schema, undo/redo, autosave with optimistic locking, device-width preview, visual diagram editor.129- Claude panel (targeted edits as patches, each an undoable revision), quality report, sources panel with server-side reference checks (Crossref / doi.org / URL fetch), translation into a linked sheet.130- Publication as immutable snapshots, slug redirects, private preview links (24 h, revocable), exports (PDF, PNG ×1/×2/×4, SVG of one vector block, portable JSON).131- Uploads: images, SVG (sanitised), PDF, text, CSV/JSON datasets; type decided from the bytes; quota; optional ClamAV scan.132- Dashboard with service health, queue state and AI spend against budgets.133134What is not implemented is listed in [docs/ROADMAP.md](docs/ROADMAP.md).135136<details>137<summary><b>The 13 built-in simulations</b> (<code>src/sim/kinds/</code>)</summary>138139`attention` · `confidence-interval` · `correlation` · `distribution` · `event-study` · `function-plot` · `garch` · `impulse-response` · `kv-cache` · `moe-routing` · `neural-forward` · `random-walk` · `regression`140141Each one is a pure, seeded `compute` plus a `View`, on a shared numeric core; `function-plot` uses a safe expression parser (`src/sim/core/expr.ts`). Documents never carry code. Catalogue: [docs/SHEET_SCHEMA.md](docs/SHEET_SCHEMA.md).142143</details>144145<details>146<summary><b>The 12 built-in templates</b> (<code>src/templates/</code>, French and English)</summary>147148| Family | Templates |149|---|---|150| AI (`src/templates/ai/`) | `transformer-architecture` · `neural-network-backprop` · `rag-pipeline` · `kv-cache` · `mixture-of-experts` · `finetuning-vs-rag-vs-prompting` |151| Quant and systems (`src/templates/quant/`) | `garch-volatility` · `hf-event-study` · `var-impulse-response` · `three-methods-comparison` · `data-pipeline-etl` · `compute-cluster-architecture` |152153Each `build(locale)` result is passed through `mustParseSheet`; changing a built-in template needs a deployment.154155</details>156157### Measured on production158159Lighthouse 13, mobile profile, lab data only (one run per page, not field data), on 2026-11-17 ([docs/TESTING.md](docs/TESTING.md)):160161| Page | Performance | Accessibility | Best practices | SEO | LCP | CLS |162|---|---|---|---|---|---|---|163| `/` | 100 | 100 | 100 | 100 | 1.0 s | 0.004 |164| `/s/melange-experts-routage-top-k` | 99 | 100 | 96 | 100 | 1.1 s | 0 |165| `/search?q=garch` | 100 | 100 | 100 | 63 | 0.8 s | 0 |166167The search page is `noindex` on purpose, which is what lowers its SEO score. The first measurement (home 100 / 96 / 100 / 100, a sheet page 100 / 97 / 96 / 100, LCP 0.9–1.3 s, CLS 0) found two accessibility defects and a heading-order gap; all three were fixed and re-measured.168169---170171## 🏗 Architecture172173One Node package, two long-running processes (web, worker) and a scheduled backup. There is no Redis, no message broker and no object store: PostgreSQL holds documents, the job queues, sessions, rate-limit counters, the audit trail and the AI usage ledger. Details: [docs/ARCHITECTURE.md](docs/ARCHITECTURE.md).174175```mermaid176flowchart TB177  B["Browser<br/>reader / administrator"] -- HTTPS --> RP["Reverse proxy<br/>TLS, X-Forwarded-For, body limit, compression"]178  RP -- "loopback / private network" --> WEB179  subgraph WEB["web process — server.ts (node:http → Next.js 16 App Router)"]180    P1["(site)/… public pages"]181    P2["admin/… studio pages"]182    A1["api/public/* anonymous JSON API"]183    A2["api/auth/* WebAuthn, sessions"]184    A3["api/admin/* studio API"]185    M["media/&lt;id&gt; · preview/&lt;token&gt; · render/&lt;token&gt;"]186  end187  WEB --> PG[("PostgreSQL 17<br/>documents, queue, sessions,<br/>limits, audit, ledger")]188  WEB --> ST[["STORAGE_ROOT<br/>uploads, exports"]]189  subgraph WK["worker process — src/worker/index.ts"]190    J1["AI jobs (2 at a time)"]191    J2["verify_sources"]192    J3["exports (1 at a time)"]193  end194  WK -- "FOR UPDATE SKIP LOCKED" --> PG195  WK --> ST196  J1 --> AN["api.anthropic.com"]197  J2 --> CR["Crossref · doi.org · source URLs"]198  J3 --> CH["headless Chromium"]199  CH -- "INTERNAL_URL/render/&lt;token&gt;" --> WEB200  BK["scripts/backup.sh (scheduled)"] --> BD[["BACKUP_DIR<br/>encrypted dump + storage archive"]]201  PG -.-> BK202  ST -.-> BK203```204205The web process is `server.ts`: a plain `node:http` server that hands every request to Next.js, sets `requestTimeout` to 5 minutes, `headersTimeout` to 60 s, `keepAliveTimeout` to 65 s, and closes on `SIGTERM`/`SIGINT` (forced exit after 8 s). The worker sends a heartbeat every 15 s and does housekeeping hourly.206207### Module map208209```mermaid210flowchart RL211  renderer["src/renderer"] --> schema["src/schema"]212  sim["src/sim"] --> schema213  server["src/server"] --> schema214  server --> renderer215  ai["src/ai"] --> server216  worker["src/worker"] --> ai217  api["src/lib/api (route tables)"] --> server218  api --> ai219  app["src/app (routes)"] --> api220  components["src/components (UI)"] --> renderer221  components --> sim222```223224| Module | Responsibility |225|---|---|226| `src/schema` | Sheet Document Schema v1 (Zod), `parseSheet`, `migrateSheet`, `stableStringify`, traversal, `applyPatch`, `diffSheets`, `slugify` — imports `zod` only; runs in the browser too |227| `src/renderer` | block components, `SheetView`, diagram layout, chart drawing, inline parser, KaTeX wrapper, SVG sanitiser, theme palettes, standalone SVG export |228| `src/sim` | the 13 simulations: pure `compute`, a `View`, shared numeric core, the expression parser |229| `src/templates` | 12 built-in templates as code |230| `src/lib` | `env`, `db` (one `postgres` client, pool of 10), `auth`, `session-store`, `ratelimit`, `audit`, `http`, `log`, `crypto`, client fetch helper, the three API route tables |231| `src/server` | all writes to sheets, assets and scanning, storage driver, job queue API, export requests, quality report, settings, render tokens |232| `src/ai` | `client.ts` (the only caller of the Anthropic API), `prompts.ts`, `pipeline.ts`, `net.ts`, `sources.ts`, `pricing.ts` |233| `src/worker` | queue loop, job state transitions, export rendering — not part of the Next.js build |234| `src/components` | site chrome, studio pages, editor — types only from `server` and `ai` |235| `src/app` | routes; thin wrappers over `server` functions and the route tables |236237The import direction is a convention; there is no lint rule that enforces it.238239### A Claude generation job240241```mermaid242sequenceDiagram243  autonumber244  participant B as Studio (browser)245  participant W as web process246  participant DB as PostgreSQL247  participant K as worker248  participant C as api.anthropic.com249  B->>W: POST /api/admin/generations (kind generate, brief, outline, idempotencyKey)250  W->>W: requireAdmin, schemas, key present, attachments exist251  W->>DB: INSERT generation_jobs (queued) + pg_notify('sheet_jobs')252  W-->>B: job view253  B->>W: EventSource /api/admin/generations/:id/events (row re-read every 500 ms)254  K->>DB: LISTEN sheet_jobs + 2 s poll, claim with FOR UPDATE SKIP LOCKED255  K->>K: extractMaterial (attachments, URLs through safeFetch)256  K->>C: callClaude — budget check, stream, ledger row257  C-->>K: text258  K->>K: extractJson → normalizeModelDoc → parseSheet (up to 2 repair rounds)259  K->>DB: createSheet: sheets row + revision 1 (origin ai_generation) — a draft260  K->>DB: UPDATE generation_jobs SET status = 'succeeded'261  W-->>B: event "succeeded" → /admin/sheets/:sheetId/edit262```263264### A save in the editor265266```mermaid267sequenceDiagram268  autonumber269  participant E as Editor (client)270  participant W as web process271  participant DB as PostgreSQL272  E->>E: change → autosave timer (2.5 s) or ⌘S, parseSheet(doc) in the browser273  E->>W: PATCH /api/admin/sheets/:id { doc, expectedVersion } + X-CSRF-Token274  W->>W: requireAdmin → body under a 2 MB cap → Zod → validDoc (422 with issues if invalid)275  W->>DB: SELECT … FOR UPDATE (one transaction)276  alt version ≠ expectedVersion277    W-->>E: 409 version_conflict (editor stops autosave, offers a reload)278  else same doc_hash as current279    W-->>E: { changed: false } — no revision280  else281    W->>DB: INSERT sheet_revisions (origin manual), UPDATE sheets, syncTaxonomy282    W-->>E: { version, revisionId, revisionNumber, diff, quality }283  end284```285286Exports go through the same page as readers see: the worker opens `INTERNAL_URL/render/<token>` (HMAC-SHA256 token signed with `APP_SECRET`, 600 s) in headless Chromium, aborts every request to another origin, and writes a PDF or a PNG; one vector block is exported as a standalone SVG with `react-dom/server`, theme tokens resolved to colours and fonts embedded.287288<details>289<summary><b>Technical trade-offs</b> (from <code>docs/ARCHITECTURE.md</code> §7)</summary>290291| Decision | Reason | Cost |292|---|---|---|293| One package with modular folders, not a monorepo | One deployable; a workspace would add tooling for no isolation gain | Module boundaries rely on discipline |294| SQL migrations with checksums, no ORM | The queries that matter (queue claim, full-text ranking, generated tsvector columns) are plain SQL | Row types are written by hand next to each query |295| No Redis: queue, rate limits and sessions in PostgreSQL | One fewer service; jobs and sessions survive restarts; `FOR UPDATE SKIP LOCKED` gives exactly-one claim | Rate limits cost a write per request; the progress stream polls the job row |296| A separate worker process | Model calls last minutes and exports need a browser | Two processes to supervise; they must share `STORAGE_ROOT` |297| Documents are data, not HTML | The model, the editor and imports can only produce what the schema allows | New visual ideas need a schema and renderer change |298| Exports through headless Chromium on the real page | PDF and PNG use the same fonts, KaTeX output and CSS as the site | A browser binary must be installed next to the worker |299| A custom Node server in front of Next.js | Explicit timeouts and graceful stop | `tsx` is a runtime dependency |300| Static CSP with `'unsafe-inline'` | Next's inline bootstrap scripts would otherwise need a per-request nonce | Script injection is not blocked by CSP |301| Gateway compresses, app does not (`compress: false`) | Avoids double work behind a proxy | A proxy without compression serves uncompressed responses |302| Built-in templates live in code | Versioned and tested against the schema | Changing one needs a deployment |303| Only the `local` storage driver | A directory or NAS mount is enough for one node | No S3 |304| Undo history as whole documents (100 steps) | Documents are small JSON values | Memory grows with very large sheets |305| Published documents are migrated when read, never rewritten | Publications stay immutable across schema versions | Every read pays the migration once `SCHEMA_VERSION` moves |306307</details>308309---310311## 🧱 Stack312313| Layer | Technology | Version (from `package.json`) | Role |314|---|---|---|---|315| Framework | Next.js (App Router) behind a custom `node:http` server (`server.ts`) | ^16.4.6 | pages, catch-all API routes |316| UI | React · React DOM | ^19.3.1 | site, studio, editor |317| Language | TypeScript · tsx (runtime dependency) | ^6.0.3 · ^4.23.15 | both processes run TypeScript directly |318| Styling | Tailwind CSS (`@tailwindcss/postcss`) | ^4.3.5 | site and studio chrome (`--ui-*` tokens); sheets use `--sh-*` tokens in `src/renderer/sheet.css` |319| Database | PostgreSQL + `citext`, `pg_trgm`, `unaccent` | 17 | documents, queue, sessions, limits, audit, ledger, full-text search |320| Driver | postgres (postgres.js) | ^4.1.1 | tagged-template SQL, no ORM |321| Auth | `@simplewebauthn/server` · `@simplewebauthn/browser` | 14.1.0 · ^14.0.0 | passkeys |322| AI | `@anthropic-ai/sdk` | ^1.8.0 | Claude studio (default model `claude-opus-5-5`) |323| Validation | Zod | ^4.6.7 | Sheet Document Schema, request bodies, settings |324| Maths | KaTeX | ^0.19.1 | equations |325| Diagrams | `@dagrejs/dagre` | ^3.1.3 | diagram layout |326| Charts | d3-array · d3-scale · d3-shape | ^3.2.5 · ^4.0.3 · ^3.2.1 | scales and shapes for the SVG charts |327| SVG sanitising | htmlparser2 | ^12.0.0 | uploaded and pasted SVG |328| Fonts | Fraunces · Inter · JetBrains Mono (`@fontsource-variable`) | ^5.3.0 | self-hosted |329| Exports | playwright-core (Chromium headless shell) | ^1.65.1 | PDF and PNG |330| Tests | Vitest · `@playwright/test` | ^5.1.0 · ^1.65.1 | unit · browser suites |331| Lint | ESLint · eslint-config-next | ^10.15.0 · ^16.4.6 | static checks |332| Process manager | PM2 (production) | — | `sheet-web`, `sheet-worker`, `sheet-backup` |333334---335336## 🗂 Repository layout337338268 tracked files; 223 TypeScript files, 26 963 lines (tests and scripts included).339340| Path | Content |341|---|---|342| `server.ts` | web process entry point |343| `src/schema/` | Sheet Document Schema v1, parsing, migration, patches, diff (pure) |344| `src/renderer/` | block components, diagram layout, charts, KaTeX, SVG sanitiser, themes (`sheet.css`), standalone SVG export |345| `src/sim/` | the 13 built-in simulations and their numeric core |346| `src/templates/` | 12 built-in templates (French and English) |347| `src/ai/` | Anthropic client, prompts, pipeline, outbound-request guard, reference checks, price table |348| `src/server/` | sheets, publication, assets, storage, jobs, exports, quality report, settings |349| `src/worker/` | worker loop and export rendering |350| `src/lib/` | env, database client, auth, sessions, rate limits, audit, HTTP helpers, API route tables |351| `src/components/` | site chrome, studio pages, editor |352| `src/app/` | Next.js routes |353| `src/i18n/` | interface dictionaries (French, English) |354| `db/migrations/` | SQL migrations (`001_init.sql`) |355| `scripts/` | migrate, bootstrap, seed, recovery, backup, restore, tests, secret scan, icon and PDF helpers |356| `tests/` | unit tests (`tests/unit`, 17 files), HTTP integration tests, the Anthropic stand-in, a software authenticator |357| `e2e/` | Playwright configuration and specs (`studio`, `studio-phone`, `public`) |358| `infra/` | Docker Compose alternative and reverse-proxy examples (written, not run — see [infra/README.md](infra/README.md)) |359| `public/` | PWA manifest, service worker, offline page, icons |360| `docs/` | ten documents (see [Documentation](#-documentation)) and `brand/` |361362Screenshots are not committed. The end-to-end run writes them to `e2e/screenshots/` (git-ignored): setup, dashboard, sign-in, plan, editor, diagram editor, phone views of the studio, and the public home and sheet pages per browser.363364---365366## 📋 Requirements367368- Node.js with `pnpm` (developed on Node 26 and pnpm 11; `pnpm-workspace.yaml` also carries the pnpm 12 build allow-list).369- PostgreSQL 17 with the `citext`, `pg_trgm` and `unaccent` extensions available (they ship with the standard contrib package).370- For exports: a Chromium binary usable by `playwright-core`.371- For backups: `pg_dump`/`pg_restore` matching the server version, `openssl`, `tar`.372- For passkeys in production: HTTPS on the exact origin given in `APP_URL`.373- Optional: an Anthropic API key (`ANTHROPIC_API_KEY`); a `clamd` socket (`CLAMD_SOCKET`).374375---376377## 🚀 Local install378379```bash380pnpm install381cp .env.example .env && chmod 600 .env     # then fill it in; see the comments in the file382createdb sheet                              # the role in DATABASE_URL must be able to CREATE EXTENSION383pnpm migrate                                # applies db/migrations/*.sql once each (checksummed)384pnpm bootstrap                              # creates the administrator, writes the one-time enrolment link385pnpm seed                                   # topics + three demonstration sheets as drafts (--publish-examples to publish)386node_modules/.bin/playwright-core install chromium-headless-shell   # browser for exports (see docs/DEPLOYMENT.md)387pnpm dev                                    # web process on PORT (default 8700), development mode388pnpm worker                                 # second terminal: jobs and exports389```390391For local development set `APP_URL=http://localhost:8700` (WebAuthn accepts `localhost` without TLS). The enrolment link is written to `SETUP_URL_FILE` (mode 0600), or printed when `SETUP_URL_FILE` is unset and the command runs in a terminal.392393---394395## 🧰 Commands396397| Command | What it runs |398|---|---|399| `pnpm dev` | `tsx server.ts` — Next.js in development mode behind the Node server |400| `pnpm build` | `next build` |401| `pnpm start` | `NODE_ENV=production tsx server.ts` — the web process |402| `pnpm worker` | `tsx src/worker/index.ts` — the worker process |403| `pnpm migrate` | `scripts/migrate.ts` |404| `pnpm bootstrap` | `scripts/bootstrap.ts` (`--renew` issues a fresh enrolment link while no passkey exists) |405| `pnpm seed` | `scripts/seed.ts` (`--publish-examples`) |406| `tsx scripts/setup-token.ts [--recovery]` | recovery link from the server's shell (interactive terminal only) |407| `bash scripts/backup.sh` / `bash scripts/restore.sh` | encrypted backup (with an HMAC per archive) and verified restore |408| `pnpm typecheck` / `pnpm lint` | `tsc --noEmit` / ESLint (`src scripts tests e2e server.ts`) |409| `pnpm test:unit` | vitest, pure functions and server-rendered components, no database |410| `pnpm test` | `scripts/test.sh`: integration tests over HTTP against a production build with a local stand-in for the Anthropic API, restart, secret scan, backup → restore. `REAL_AI=1` adds a run against the real API |411| `pnpm secrets:scan` | `scripts/secrets-scan.ts` |412| `pnpm test:e2e` | `scripts/e2e.sh`: Playwright browser tests (`e2e/`) against a production build on a fresh database, with the Anthropic stand-in — the studio journey in Chromium with a virtual passkey authenticator, the studio at phone size, and the public pages in Chromium and in WebKit with an iPhone profile |413414---415416## ⚙️ Configuration417418Names only. `.env` (never committed, mode 600) is read by Next.js and by `scripts/_env.ts` (scripts, worker, `server.ts`); variables already present in the environment win over the file. No secret belongs in the database or in the repository.419420| Variable | Required | Meaning |421|---|---|---|422| `APP_URL` | yes | Public origin exactly as typed in the browser, without a trailing slash. WebAuthn compares it byte for byte. Also used for canonical URLs, feeds and `robots.txt` |423| `WEBAUTHN_RP_ID` | no | Defaults to the host of `APP_URL`. Changing it later invalidates every passkey |424| `APP_SECRET` | yes | ≥ 32 characters. Signs preview and render tokens |425| `PORT`, `HOST` | no | Default `8700`, `0.0.0.0`. Prefer a loopback `HOST` when the proxy is on the same machine |426| `ADMIN_EMAIL`, `ADMIN_NAME` | first run | The administrator created by `bootstrap` (there is no password) |427| `SETUP_URL_FILE` | recommended | Where the one-time enrolment link is written (mode 0600) |428| `DATABASE_URL` | yes | PostgreSQL connection string |429| `ANTHROPIC_API_KEY` | no | Without it the studio is a manual editor and says that AI generation is not configured |430| `ANTHROPIC_MODEL` | no | Default model until one is chosen in the settings |431| `STORAGE_DRIVER` | no | Only `local` is implemented |432| `STORAGE_ROOT` | yes | Directory for uploads and exports, outside the application directory |433| `UPLOAD_MAX_MB`, `STORAGE_QUOTA_GB` | no | Default 20 and 5 |434| `CLAMD_SOCKET` | no | Unix socket path or `host:port`. Unset = uploads stored unscanned |435| `INTERNAL_URL` | no | Address of the web process as seen from the worker (default: loopback on `PORT`) |436| `CHROMIUM_PATH` | no | Chromium binary for exports; unset = the one `playwright-core` reports |437| `BACKUP_DIR`, `BACKUP_PASSPHRASE_FILE`, `BACKUP_KEEP_DAYS` | for backups | See [docs/BACKUP_RESTORE.md](docs/BACKUP_RESTORE.md) |438| `LOG_LEVEL` | no | `debug`, `info` (default), `warn`, `error` |439| `REAL_AI` | tests only | `REAL_AI=1 scripts/test.sh` adds a run against the real Anthropic API (costs money) |440441Non-secret settings (model, efforts, budgets, view counter…) live in the `settings` table and are edited in `/admin/settings`.442443---444445## 🖥 Routes44644717 pages and 9 route handlers under `src/app/`.448449| Path | Kind | Purpose |450|---|---|---|451| `/` | page | home |452| `/s/[slug]` | page | a published sheet (+ generated Open Graph image) |453| `/topics` · `/topics/[slug]` | pages | topics |454| `/search` | page | search (`noindex`) |455| `/about` | page | about |456| `/admin` | page | dashboard: service health, queue state, AI spend |457| `/admin/login` · `/admin/setup` | pages | passkey sign-in · one-time enrolment |458| `/admin/sheets/new` | page | creation assistant |459| `/admin/sheets/[id]/edit` | page | editor |460| `/admin/assets` | page | media and data library |461| `/admin/templates` | page | templates |462| `/admin/revisions` | page | revisions |463| `/admin/settings` | page | settings: passkeys, sessions, Claude, topics, audit |464| `/preview/[token]` | page | private draft preview (24 h, revocable, `noindex`) + `/preview/[token]/asset/[id]` |465| `/render/[token]` | page | bare page for exports + `/render/[token]/asset/[id]` |466| `/media/[id]` | route | public files (only assets flagged `is_public`) |467| `/rss.xml` · `/sitemap.xml` · `robots.txt` | routes | feeds and crawlers |468| `/api/health` | route | liveness: `200 {"ok":true}` when the web process answers and `SELECT 1` works, `503 {"ok":false}` otherwise |469| `/api/public/[...path]` · `/api/auth/[...path]` · `/api/admin/[...path]` | routes | the three API surfaces |470471---472473## 🔌 API47447566 endpoints in three route tables. Each surface is one catch-all route whose guard runs before a handler is looked up (`src/lib/api/router.ts`), so a handler cannot be reached without its guard.476477| Surface | Table | Endpoints | Guard |478|---|---|---|---|479| `/api/public/*` | `src/lib/api/publicroutes.ts` | 6 | none (rate limit per client address inside each handler) |480| `/api/auth/*` | `src/lib/api/authroutes.ts` | 15 | per route: same-origin check, rate limit, session + CSRF where relevant |481| `/api/admin/*` | `src/lib/api/adminroutes.ts` | 45 | `requireAdmin`: passkey session, CSRF header on non-GET |482483<details open>484<summary><b>Public API</b> — read-only, anonymous, cacheable; reads published snapshots only</summary>485486| Method | Path | Purpose |487|---|---|---|488| GET | `/api/public/sheets` | list of published sheets (`locale`, `level`, `kind`, `topic`, `tag`, `q`, `limit` ≤ 50, `offset`) |489| GET | `/api/public/search` | search (`q` required) |490| GET | `/api/public/sheets/:slug` | one published sheet with its document; 301 from an old slug |491| GET | `/api/public/topics` | topics |492| GET | `/api/public/feed` | JSON Feed |493| POST | `/api/public/views` | view beacon (per sheet, per day; no visitor data) |494495</details>496497<details>498<summary><b>Authentication API</b></summary>499500| Method | Path | Purpose |501|---|---|---|502| GET | `/api/auth/session` | current session |503| POST | `/api/auth/setup/info` | state of a one-time enrolment link |504| POST | `/api/auth/register/options` · `/api/auth/register/verify` | passkey enrolment ceremony |505| POST | `/api/auth/login/options` · `/api/auth/login/verify` | passkey sign-in ceremony |506| POST | `/api/auth/recovery` | sign-in with a recovery code (session limited to enrolling a passkey) |507| POST | `/api/auth/reauth/options` · `/api/auth/reauth/verify` | fresh verification for sensitive actions |508| GET | `/api/auth/passkeys` | list of passkeys |509| PATCH · DELETE | `/api/auth/passkeys/:id` | rename · remove (the last passkey cannot be removed) |510| POST | `/api/auth/recovery-codes` | new set of ten codes |511| DELETE | `/api/auth/sessions/:id` | revoke a session |512| POST | `/api/auth/logout` | sign out |513514</details>515516<details>517<summary><b>Studio API</b> (45 endpoints)</summary>518519| Area | Endpoints |520|---|---|521| Dashboard | `GET dashboard` |522| Sheets | `GET sheets` · `POST sheets` · `GET sheets/:id` · `PATCH sheets/:id` · `DELETE sheets/:id` |523| Publication | `POST sheets/:id/publish` · `POST sheets/:id/unpublish` · `POST sheets/:id/preview-link` · `DELETE sheets/:id/preview-link` |524| Revisions | `GET sheets/:id/revisions` · `GET sheets/:id/revisions/:rid` · `POST sheets/:id/revert` |525| Quality | `POST quality` · `POST validate` |526| Claude | `POST generations` · `GET generations` · `GET generations/:id` · `POST generations/:id/cancel` · `GET generations/:id/events` · `POST sheets/:id/ai-edit` · `POST sheets/:id/translate` · `POST sheets/:id/verify-sources` · `GET sheets/:id/conversation` |527| Exports | `POST exports` · `GET exports/:id` · `GET sheets/:id/exports` · `GET sheets/:id/export.json` |528| Assets | `GET assets` · `POST assets` · `PATCH assets/:id` · `DELETE assets/:id` · `GET assets/:id/dataset` · `GET assets/:id/file` · `POST svg/sanitize` |529| Templates | `GET templates` · `GET templates/:key/preview` · `DELETE templates/:id` · `POST sheets/:id/save-as-template` |530| Topics | `GET topics` · `PATCH topics/:id` · `DELETE topics/:id` |531| Settings and audit | `GET settings` · `PATCH settings` · `GET audit` |532533All paths are relative to `/api/admin/`.534535</details>536537---538539## 📐 Document format540541A sheet is data, not HTML: the model, the editor and imports can only produce what the schema allows, and the renderer never interprets markup. Reference: [docs/SHEET_SCHEMA.md](docs/SHEET_SCHEMA.md).542543The 23 block types of Sheet Document Schema v1 (`src/schema/sheet.ts`):544545| Text | Structure | Visual | Interactive |546|---|---|---|---|547| `heading` · `paragraph` · `list` · `equation` · `callout` · `code` · `references` | `table` · `cards` · `steps` · `legend` · `comparison` · `divider` | `diagram` · `timeline` · `matrix` · `chart` · `svg` · `image` | `accordion` · `quiz` · `simulation` · `tabs` |548549- **Inline syntax without HTML** (`src/renderer/inline.tsx`); equations with KaTeX.550- **Charts**: line, area, bar, scatter, histogram — optionally from an uploaded dataset (`datasetRef`).551- **Diagrams**: layout engine with routing and groups (`@dagrejs/dagre`), animation steps with a player, zoom, pan and full screen for readers.552- **Themes**: `scientific-light`, `scientific-dark`, `editorial`, `blueprint`, `finance`. Documents never carry colours, only a preset, an accent tone and per-element tones (8 tones).553- **Fixed formats**: `a4`, `slide-16-9`, `square`, `portrait-4-5` keep their pixel size; the responsive layout is a 12-column grid that responds to the width of the sheet itself (CSS container query).554- **Patch operations and block diff** (`applyPatch`, `diffSheets`): every Claude edit is a patch and an undoable revision.555- **Exports**: PDF, PNG ×1/×2/×4, per-block SVG, portable JSON (`GET /api/admin/sheets/<id>/export.json`).556557---558559## 📰 Publication model560561Four things, three of them immutable.562563| Thing | Table / column | Mutable? | Meaning |564|---|---|---|---|565| Working copy | `sheets.doc` | yes | What the studio shows. Always equal to the document of `sheets.current_revision_id` |566| Revision | `sheet_revisions` | no | Every saved state, numbered per sheet, with origin (`manual`, `ai_generation`, `ai_edit`, `revert`, `import`, `template`, `source_check`). Reverting writes a new revision |567| Publication | `sheet_publications` | no | A copy of one revision taken at publication time. Publishing again adds a row |568| Pointer | `sheets.publication_id` | yes | The one publication the public sees. `NULL` when not published |569570`publish()` runs in one transaction: lock the sheet, check `expectedVersion`, run the quality report and refuse on errors, mark the previous publication `unpublished_at`, insert the new publication, move the pointer, flag the document's image assets `is_public`, queue the reader PDF, write the audit event. Readers see the old publication or the new one, never a mixture.571572- Statuses are `draft`, `in_review`, `published`, `archived`; `in_review` and `archived` are labels with no workflow. There is no scheduled publication.573- `deleteSheet()` is a soft delete, refused while published.574- A slug change is recorded in `slug_redirects` if the sheet has ever been public; public lookups redirect from the old address.575- A French and an English sheet are two `sheets` rows sharing a `localization_id`. A sheet's locale cannot change; translation creates the sibling.576577---578579## 🗄 Data model58058131 tables created by `db/migrations/001_init.sql` (UUID keys, `timestamptz`), plus `schema_migrations (name, checksum, applied_at)` added by `scripts/migrate.ts`, which refuses to run if an applied file was modified.582583| Group | Tables |584|---|---|585| Administrator and authentication | `users` · `passkey_credentials` · `setup_tokens` · `recovery_codes` · `webauthn_challenges` · `sessions` · `rate_limits` · `audit_events` · `settings` · `worker_heartbeat` |586| Content | `topics` · `tags` · `sheet_localizations` · `sheets` · `sheet_revisions` · `sheet_publications` · `slug_redirects` · `sheet_topics` · `sheet_tags` · `preview_tokens` · `templates` · `sheet_views` |587| Media, data, sources | `assets` · `datasets` · `sources` · `sheet_sources` |588| Claude studio and exports | `ai_conversations` · `ai_messages` · `generation_jobs` · `generation_usage` · `export_jobs` |589590```mermaid591erDiagram592  sheet_localizations ||--o{ sheets : "language versions"593  sheets ||--o{ sheet_revisions : "immutable saved states"594  sheets ||--o{ sheet_publications : "immutable public snapshots"595  sheet_revisions ||--o{ sheet_publications : "copied at publication"596  sheets ||--o{ sheet_topics : has597  topics ||--o{ sheet_topics : groups598  sheets ||--o{ sheet_tags : has599  tags ||--o{ sheet_tags : labels600  sheets ||--o{ preview_tokens : "private links"601  sheets ||--o{ sheet_views : "per-day counter"602  sheets ||--o{ sheet_sources : cites603  sources ||--o{ sheet_sources : "library"604  assets ||--o| datasets : "parsed CSV / JSON"605  sheets ||--o{ ai_conversations : has606  ai_conversations ||--o{ ai_messages : contains607  generation_jobs ||--o{ generation_usage : "ledger rows"608  sheets ||--o{ export_jobs : exports609  users ||--o{ passkey_credentials : owns610  users ||--o{ sessions : opens611```612613Search uses generated `search_fr` / `search_en` tsvector columns (GIN) and a trigram index on the title of `sheet_publications`. Table by table: [docs/ARCHITECTURE.md](docs/ARCHITECTURE.md) §5.614615---616617## 🤖 Claude studio pipeline618619Reference: [docs/AI_PIPELINE.md](docs/AI_PIPELINE.md). AI routes answer `503 ai_not_configured` when `ANTHROPIC_API_KEY` is unset; `verify_sources` works without a key.620621| Job kind | Started by | Result |622|---|---|---|623| `outline` | `POST /api/admin/generations` (`kind: "outline"`) | an outline for the author to correct; nothing is stored besides the job output |624| `generate` | `POST /api/admin/generations` (`kind: "generate"`) | a new sheet (draft) with revision 1, origin `ai_generation` |625| `edit` | `POST /api/admin/sheets/<id>/ai-edit` | a new revision, origin `ai_edit`, with the patch stored; or no change |626| `translate` | `POST /api/admin/sheets/<id>/translate` | a new linked sheet (draft) in the other language |627| `verify_sources` | `POST /api/admin/sheets/<id>/verify-sources` | a new revision, origin `source_check`, with updated source statuses. **No model call** |628629| Setting | Default |630|---|---|631| Model (`settings.aiModel`, chosen from the live Models API list; empty → `ANTHROPIC_MODEL`) | `claude-opus-5-5` |632| Effort for generation · for edits and translation · for outlines | `high` · `medium` · `medium` (fixed in code) |633| Output cap per call (`settings.aiMaxOutputTokens`, 4 000–128 000) | 48 000 |634| Budgets in US dollars, 0 = no limit: per job · daily · monthly | 5 · 25 · 200 |635| `aiJobsPerHour` · `aiJobTimeoutMinutes` | 40 · 20 |636637- **What the model may not decide**: author, locale and source status are forced by the server; image blocks and assets from the model are dropped; every source in a generated sheet is stored as `unverified`.638- **Output contract**: `extractJson` → `normalizeModelDoc` → `parseSheet`, with up to 2 repair rounds; a clear failure when the output never validates or the model declines.639- **Source material** (uploads and links) is wrapped as data; URLs go through `safeFetch`, which refuses private addresses.640- **Budgets**: `assertBudget` runs before each model call against the `generation_usage` ledger, so a limit can be exceeded by the cost of one call. `src/ai/pricing.ts` holds list prices for fourteen model ids, maintained by hand: an estimate for budgeting, not an invoice. A model missing from the table is costed at the most expensive rate.641- **Not done, by design**: tool use / agents, automatic publication. Not done: a web research provider.642643Real run of 2026-11-17 (model `claude-opus-5-5`, effort `high`): a generation accepted on the first answer — 12 blocks, 83 s, 9 885 output tokens, **0.34 USD**; an edit adding one block and touching nothing else — 4.7 s, **0.04 USD**.644645---646647## 🛡 Security and privacy648649Threat model, controls and limits: [docs/SECURITY.md](docs/SECURITY.md).650651| Topic | Control |652|---|---|653| Authentication | WebAuthn passkeys only: no password anywhere and no sign-up route. `residentKey: "required"`, `userVerification: "required"` for registration, sign-in and re-authentication; algorithms ES256, EdDSA, RS256; sign-in sends no `allowCredentials` |654| Bootstrap | one administrator row created by `scripts/bootstrap.ts`; a 24-hour, one-use enrolment link written to a 0600 file |655| Challenges | stored server-side with a 5-minute expiry, bound to the browser (hash of a `HttpOnly`, `SameSite=Strict` cookie) or to the session, single use |656| Sessions | server-side, rotated at each sign-in, revocable, listed in the settings: 14 days absolute, 7 days without use |657| CSRF | header token on every state change (`requireAdmin`) + same-origin check |658| Re-authentication | fresh passkey verification to add or remove a passkey and to issue recovery codes |659| Recovery | ten one-use codes stored hashed; a code opens a session that can only enrol a passkey, and doing so revokes earlier passkeys, sessions and codes; CLI recovery with `scripts/setup-token.ts --recovery` |660| Lock-out | per client address, from failed sign-ins of the last hour: 5 → 1 min, 10 → 5 min, 20 → 1 h |661| Draft isolation | public pages and `/api/public` read publication snapshots only; no draft slug appears in the API, search, sitemap, RSS, JSON feed or home page |662| Uploads | type decided from the bytes; HTML, executables, broken JSON and oversized files refused; SVG sanitised and served as a sandboxed attachment; quota; optional ClamAV |663| XSS | schema-only documents and React escaping: `<script>`, `onerror=` and `javascript:` links typed into a block are shown as text |664| Outbound requests | `safeFetch` (`src/ai/net.ts`) for source material and reference checks; private addresses refused |665| Model output | crosses into the database only through `extractJson` → `normalizeModelDoc` → `parseSheet` (or `applyPatch`) |666| Export rendering | HMAC token naming one document; the worker's browser aborts every request to another origin |667| Secrets | environment only; `scripts/secrets-scan.ts`; the test run checks that the client bundle and both process logs contain neither the API key nor `APP_SECRET` |668| Audit | append-only `audit_events`: sign-ins, failures, recovery, revocations, publications, uploads, settings and jobs |669| Privacy | the view counter is per sheet and per day and stores no visitor data; it can be switched off |670671---672673## ✅ Testing674675Four suites ([docs/TESTING.md](docs/TESTING.md)). In the integration and browser suites the Anthropic API is replaced by `tests/fake-anthropic.ts`, a local server that speaks the streaming protocol and returns deterministic JSON (including deliberately wrong answers). Nothing else is mocked: the database, the worker, the queue, the headless browser and the file storage are the real ones.676677| Suite | Command | Result of the run of 2026-11-17 |678|---|---|---|679| Unit | `pnpm test:unit` | **711 passed**, 0 failed (17 files) |680| Integration — `tests/api.test.ts` | `pnpm test` | **53 passed**, 0 failed |681| Integration — restart (`tests/restart.test.ts`) | `pnpm test` | **4 passed**: publications served, sessions kept, a job left "running" by a dead worker re-run to completion, worker heartbeat |682| Secret scan | `pnpm test` / `pnpm secrets:scan` | clean on 255 files; bundle and logs free of the API key and `APP_SECRET` |683| Backup → restore | `pnpm test` | encrypted dump and storage archive restored into an empty database and directory; row counts equal on six tables; every file matches its SHA-256; a tampered archive is refused |684| Browser | `pnpm test:e2e` | **27 passed** (studio 13, studio on a phone 2, public pages 6 × 2 browsers) |685| Real API | `REAL_AI=1 scripts/test.sh` | **2 passed** (one generation, one edit) |686| Lint, types | `pnpm lint` · `pnpm typecheck` | clean |687| Production | public browser suite against https://sheet.spboucher.ai | 12 passed (6 in Chromium at seven widths, 6 in WebKit with an iPhone profile) |688689<details>690<summary><b>What the integration suite asserts</b></summary>691692- **Anonymous access**: 401 on every `/api/admin/*` and credential route (18 GET, 14 write routes); studio pages redirect to sign-in and are `noindex`; security headers present.693- **Setup**: registration refused from another origin, another RP ID or without user verification; a challenge cannot be used by another browser; replay refused; the setup token is single-use; bootstrap closed afterwards.694- **Sign-in**: bad signature, unknown credential, wrong origin, missing user verification and replay all refused; session rotated at each sign-in and revoked at sign-out; progressive lock-out per address.695- **Passkey management and recovery**: fresh verification required to add or remove; the last passkey cannot be removed; a recovery code opens a session that can only enrol a passkey.696- **CMS**: optimistic locking (409), schema validation (422), revisions and revert, publication refused on quality errors, atomic publish, slug change answers 308 from the old address, unpublish removes the sheet from page, API, sitemap and feeds.697- **Draft isolation**, **injected markup**, **uploads** (hostile SVG stored without script, handlers, foreign content or external references).698- **Claude pipeline**: brief validation, attachment handed over as data with its embedded instructions reported, idempotent job creation, repair round, cancellation, patch-based edit with byte-identical untouched blocks, selection scope and locked blocks, translation with identical skeleton and sources, budget stop, usage ledger amounts.699- **Exports**: PDF with embedded fonts; PNG at 1× and 2×; standalone SVG with embedded fonts and no unresolved CSS variable; JSON export re-imported without loss.700- **Audit**: no secret appears in the trail.701702</details>703704<details>705<summary><b>Not covered by automated tests</b></summary>706707- **Face ID / Touch ID on real devices**: the browser suite uses Chromium's virtual platform authenticator; enrolment and sign-in on a real iPhone and a real Mac must be done by hand once after deployment; Firefox was not run.708- **WebKit for the studio**: WebKit has no virtual authenticator, so only the public pages run in WebKit.709- **VoiceOver**: accessibility is checked structurally, not with a screen reader.710- **Field Core Web Vitals**: only lab measurements exist.711- **ClamAV**: the scanner protocol code is not exercised.712- **Reference checking against the network** is not part of the suites; it was run by hand on 2026-11-17.713- **Docker Compose files** in `infra/` were not built or run.714- **Pinch-zoom and drag gestures** in the diagram viewer and the visual diagram editor.715716</details>717718---719720## 🚢 Deployment721722Generic self-hosted installation, reverse proxy, upgrade and rollback: [docs/DEPLOYMENT.md](docs/DEPLOYMENT.md). The `infra/` directory holds a Docker Compose alternative and Caddy / nginx examples that the production installation does not use.723724### This installation725726Deployed on 2026-11-17 on the MacLustr cluster, through its orchestrator (`mld`), not with Docker.727728| Item | Value |729|---|---|730| Public address | https://sheet.spboucher.ai (DNS `A` → the tunnel gateway; TLS issued automatically by the gateway's Caddy; the node is reached over WireGuard) |731| Node | `M2M32c` (Mac Studio, Apple silicon), chosen by `mld pick`; application directory `~/apps/sheet` — a git clone, origin `gitsrv:srv/git/sheet.git` |732| Processes (PM2) | `sheet-web` (port 8700), `sheet-worker`, `sheet-backup` (cron 03:25 every day) |733| Database | PostgreSQL 17 on the node, database `sheet`, role `sheet_app`; extensions `citext`, `pg_trgm`, `unaccent` created once by the superuser |734| Storage | `~/data/sheet/storage` |735| Backups | `~/backups/sheet` (same disk — see [docs/BACKUP_RESTORE.md](docs/BACKUP_RESTORE.md)) |736| Secrets | `~/apps/sheet/.env` (mode 600), never in git |737| Manifest | `M1M32:~/dispatch/apps/sheet.json` (processes, hooks, tunnel route) |738| Headless browser | `chromium-headless-shell`, installed by the deployment hook (`playwright-core install`) |739740Deployment hooks run, in order: check secrets and directories → `pnpm install --frozen-lockfile` → install the headless browser → `migrate` → `bootstrap` → `seed --publish-examples` → `next build`; then PM2 starts the processes, the local health check is read, and the gateway route `https://sheet.spboucher.ai → M2M32c:8700` is set.741742Update procedure (the node's clone is the source of truth):743744```bash745ssh M2M32c746cd ~/apps/sheet && git pull --ff-only747pnpm install --frozen-lockfile748node_modules/.bin/tsx scripts/migrate.ts749node_modules/.bin/next build750pm2 restart sheet-web sheet-worker751curl -fsS http://127.0.0.1:8700/api/health752```753754Rollback: `git checkout <previous commit>`, rebuild, restart. Migrations are additive; a release that needs a destructive migration must say so and ship its own rollback notes. A document saved by a newer version with a higher `schemaVersion` is refused by older code: after a schema bump, rolling back the code requires rolling back the data too.755756Verified after deployment (2026-11-17): `/api/health` 200; home, two example sheets, topics, about, RSS, sitemap and robots 200 over HTTPS; `/admin` redirects to sign-in; `/api/admin/dashboard` 401; security headers present; the worker rendered the six reader PDFs; the Anthropic API key is accepted from the node and the configured model is available.757758Not verified on this installation: enrolment and sign-in with a real passkey, and therefore a Claude generation started from the production studio.759760### Backups761762`scripts/backup.sh` writes an encrypted database dump and a storage archive to `BACKUP_DIR`, each with an HMAC that `scripts/restore.sh` verifies. Two drills were run on 2026-11-17: in the test environment, and on the production node (6 sheets / 6 publications / 6 files on both sides, 0 checksum mismatches). Still to do by the operator: copy the backups (and, separately, the passphrase) to another machine on a schedule — they currently sit on the same disk as the data.763764---765766## 🩺 Operations and troubleshooting767768Runbook: [docs/OPERATIONS.md](docs/OPERATIONS.md) — processes, logs (JSON lines in the worker), health, the job queue (states, inspect, cancel, re-queue), incidents, housekeeping, dependency upgrades, rollback.769770| Check | How |771|---|---|772| Liveness | `GET /api/health` |773| Dashboard tiles | `/admin`: database, Claude API (spends no tokens), job queue and worker, storage (write probe), antivirus |774| Worker heartbeat | `worker_heartbeat` row, updated every 15 s; the dashboard calls the worker alive when the last beat is under 45 s old |775| Spend | dashboard, from `generation_usage`: today and this month against the budgets |776777```sql778SELECT name, now() - beat_at AS age, info FROM worker_heartbeat;779```780781| Symptom | Cause | Action |782|---|---|---|783| Jobs fail with code `auth` | the Anthropic API key was refused | check `ANTHROPIC_API_KEY` in the environment of **both** processes, restart them |784| AI routes answer `503 ai_not_configured` | no key at all | the studio works as a manual editor; source checks still work |785| Jobs fail with code `budget` | daily, monthly or per-job limit reached | wait, or raise the budget in Settings → Claude (0 = no limit) |786| `worker.chromium_missing` at start-up; published sheets have no PDF button | Chromium is missing | `node_modules/.bin/playwright-core install chromium-headless-shell` (or set `CHROMIUM_PATH`), restart the worker — the check runs only at start-up |787| `422 whole_sheet_only` | PDF requested for a single block | export one block as PNG or SVG |788| `Render page answered HTTP …` | the worker's browser cannot load `INTERNAL_URL/render/<token>` | check `INTERNAL_URL` and that `APP_SECRET` is the same for both processes |789| Dashboard "Worker stopped"; jobs stay `queued` | the worker is down | read `worker.fatal` in its log, fix, restart — queued jobs wait, running ones are re-queued at start-up |790| Uploads and exports fail; storage tile fails | disk full | lower `BACKUP_KEEP_DAYS` or move `BACKUP_DIR`, rotate logs, delete unused files in Media & data — never delete files under `STORAGE_ROOT` by hand |791| `409 version_conflict`; "The sheet was changed elsewhere … Reload" | two writers on one sheet | reload the tab that lost (copy anything important first) |792| "Invalid document — not saved" | the working copy does not pass the schema; autosave is suspended | fix the issue named in the banner, or undo |793| Lost passkeys | — | another enrolled device → remove the lost passkey; else a recovery code; else `node_modules/.bin/tsx scripts/setup-token.ts --recovery` on the server |794| `429 locked_out` | failed attempts from one address | clears by itself; if every client shares one address, the proxy is not sending `X-Forwarded-For` |795| Passkeys stop working after a domain change | passkeys are bound to the RP ID | set `WEBAUTHN_RP_ID` to the old value where possible, or re-enrol with `scripts/setup-token.ts --recovery` |796| A published sheet has broken images | the public flag was withdrawn on unpublish | publish again |797| A reader PDF is missing | export job failed or never queued | publish again, or insert the `export_jobs` row (SQL in the runbook) |798799---800801## 🚧 Status and known limitations802803Status per phase and everything that is not implemented: [docs/ROADMAP.md](docs/ROADMAP.md).804805| Not done or partial | Note |806|---|---|807| CI pipeline | no CI configuration in the repository |808| Multiple users or roles | single administrator |809| Attestation verification | `attestationType: "none"` |810| Scheduled publication | no schedule column, no scheduler |811| S3 / MinIO storage driver | interface only; `STORAGE_DRIVER` refuses anything but `local` |812| Whole-sheet SVG export | SVG is per vector block; a whole sheet is PDF or PNG |813| PDF of a single block | refused with `422 whole_sheet_only` |814| Cross-block interactions | `interactions` must be empty in v1 |815| Sharing image chosen per sheet | accepted by the API, no field in the editor |816| Cost estimate before generation | heuristic from the outline |817| Web research provider | the model works from supplied material or memory |818| Author-defined simulations or custom code | by design: documents never carry code |819820<details>821<summary><b>Residual security risks</b> (from <code>docs/SECURITY.md</code> §17)</summary>8228231. The CSP allows inline scripts; the protection against XSS is the schema-only document model and React escaping, not the CSP.8242. A single administrator, no roles, no second approver for publication.8253. No attestation verification.8264. A stolen session cookie works until the session ends (14 days at most, 7 days without use) or is revoked.8275. The client address is the last `X-Forwarded-For` value: if the web port is reachable without the proxy, rate limits, lock-out and audit addresses can be spoofed.8286. Lock-out is per address.8297. ClamAV is optional; PDFs are sent to the Anthropic API when attached to a brief.8308. Request bodies are held in memory while they are read (up to the cap).8319. Public flags are withdrawn on unpublish only.83210. `/render/<token>` is reachable with a valid short-lived token; it is not restricted to loopback.83311. Preview links are bearer tokens, valid up to 24 hours.83412. The view counter is unauthenticated.83513. Backups are written to a local directory, and two of their steps expose a secret in the process list while they run.83614. The audit trail is in the same database and is not tamper-evident.83715. Asset deletion does not look at old revisions.83816. Locks protect a block and the tab group around it, not the reverse.83917. No error-reporting or intrusion-detection service is integrated.84018. Model-written content can be wrong: the quality report is mechanical.84119. One worker process is assumed.842843</details>844845---846847## 📚 Documentation848849- [docs/ARCHITECTURE.md](docs/ARCHITECTURE.md) — components, data flows, publication model, data model, design system, trade-offs850- [docs/SECURITY.md](docs/SECURITY.md) — threat model and controls, known limits851- [docs/AI_PIPELINE.md](docs/AI_PIPELINE.md) — the Claude studio pipeline852- [docs/SHEET_SCHEMA.md](docs/SHEET_SCHEMA.md) — the document format and the simulation catalogue853- [docs/EDITOR.md](docs/EDITOR.md) — the studio, screen by screen854- [docs/DEPLOYMENT.md](docs/DEPLOYMENT.md) — installation, reverse proxy, upgrade, rollback855- [docs/BACKUP_RESTORE.md](docs/BACKUP_RESTORE.md) — backups and the restore drill856- [docs/OPERATIONS.md](docs/OPERATIONS.md) — running it: logs, health, queue, incidents857- [docs/ROADMAP.md](docs/ROADMAP.md) — status per phase and what is not implemented858- [docs/TESTING.md](docs/TESTING.md) — the four suites, with measured results859- [infra/README.md](infra/README.md) — the Docker Compose alternative860861---862863## 👤 Author864865<div align="center">866867<img src="docs/brand/icon.png" width="64" height="64" alt="">868869**Simon-Pierre Boucher** — design, development, operations870871[www.spboucher.ai](https://www.spboucher.ai) · [git.spboucher.ai](https://git.spboucher.ai) · contact@spboucher.ai872873</div>874875## 📄 License876877All rights reserved © 2026 Simon-Pierre Boucher.878
Files main
Menu

Theme