Sheet — by Simon-Pierre Boucher
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.
Author: Simon-Pierre Boucher
Table of contents
- Overview
- Features
- Architecture
- Stack
- Repository layout
- Requirements
- Local install
- Commands
- Configuration
- Routes
- API
- Document format
- Publication model
- Data model
- Claude studio pipeline
- Security and privacy
- Testing
- Deployment
- Operations and troubleshooting
- Status and known limitations
- Documentation
- Author
- License
🎯 Overview
Sheet 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.
The 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.
Production: https://sheet.spboucher.ai — deployed on 2026-11-17 on the MacLustr cluster (node M2M32c, port 8700).
Sheet is a standalone project of the spboucher.ai application family: it has no native app and no sister repository.
✨ Features
Public site (src/app/(site), src/app/api/public)
- Home, topic pages, search (PostgreSQL full-text + trigram, French and English, accent-insensitive), sheet pages at
/s/<slug>, an about page. - RSS (
/rss.xml), JSON Feed (/api/public/feed), sitemap withhreflangalternates,robots.txt, JSON-LD, a generated Open Graph image per sheet. - A read-only JSON API over published sheets (
/api/public/sheets,/search,/topics). - Interface in French or English (cookie
sheet_locale, otherwiseAccept-Language), light/dark mode stored in the browser. - A per-sheet, per-day view counter that stores no visitor data (can be switched off).
- A small service worker: caches hashed static files, shows an offline page; pages and API calls always go to the network.
Studio (src/app/admin, src/components/admin, src/components/editor)
- Passkey-only sign-in (WebAuthn), recovery codes, passkey and session management, audit log.
- 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.
- 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.
- 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.
- 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).
- Uploads: images, SVG (sanitised), PDF, text, CSV/JSON datasets; type decided from the bytes; quota; optional ClamAV scan.
- Dashboard with service health, queue state and AI spend against budgets.
What is not implemented is listed in docs/ROADMAP.md.
The 13 built-in simulations (src/sim/kinds/)
attention · confidence-interval · correlation · distribution · event-study · function-plot · garch · impulse-response · kv-cache · moe-routing · neural-forward · random-walk · regression
Each 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.
The 12 built-in templates (src/templates/, French and English)
| Family | Templates |
|---|---|
AI (src/templates/ai/) |
transformer-architecture · neural-network-backprop · rag-pipeline · kv-cache · mixture-of-experts · finetuning-vs-rag-vs-prompting |
Quant and systems (src/templates/quant/) |
garch-volatility · hf-event-study · var-impulse-response · three-methods-comparison · data-pipeline-etl · compute-cluster-architecture |
Each build(locale) result is passed through mustParseSheet; changing a built-in template needs a deployment.
Measured on production
Lighthouse 13, mobile profile, lab data only (one run per page, not field data), on 2026-11-17 (docs/TESTING.md):
| Page | Performance | Accessibility | Best practices | SEO | LCP | CLS |
|---|---|---|---|---|---|---|
/ |
100 | 100 | 100 | 100 | 1.0 s | 0.004 |
/s/melange-experts-routage-top-k |
99 | 100 | 96 | 100 | 1.1 s | 0 |
/search?q=garch |
100 | 100 | 100 | 63 | 0.8 s | 0 |
The 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.
🏗 Architecture
One 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.
flowchart TB
B["Browser<br/>reader / administrator"] -- HTTPS --> RP["Reverse proxy<br/>TLS, X-Forwarded-For, body limit, compression"]
RP -- "loopback / private network" --> WEB
subgraph WEB["web process — server.ts (node:http → Next.js 16 App Router)"]
P1["(site)/… public pages"]
P2["admin/… studio pages"]
A1["api/public/* anonymous JSON API"]
A2["api/auth/* WebAuthn, sessions"]
A3["api/admin/* studio API"]
M["media/<id> · preview/<token> · render/<token>"]
end
WEB --> PG[("PostgreSQL 17<br/>documents, queue, sessions,<br/>limits, audit, ledger")]
WEB --> ST[["STORAGE_ROOT<br/>uploads, exports"]]
subgraph WK["worker process — src/worker/index.ts"]
J1["AI jobs (2 at a time)"]
J2["verify_sources"]
J3["exports (1 at a time)"]
end
WK -- "FOR UPDATE SKIP LOCKED" --> PG
WK --> ST
J1 --> AN["api.anthropic.com"]
J2 --> CR["Crossref · doi.org · source URLs"]
J3 --> CH["headless Chromium"]
CH -- "INTERNAL_URL/render/<token>" --> WEB
BK["scripts/backup.sh (scheduled)"] --> BD[["BACKUP_DIR<br/>encrypted dump + storage archive"]]
PG -.-> BK
ST -.-> BK
The 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.
Module map
flowchart RL renderer["src/renderer"] --> schema["src/schema"] sim["src/sim"] --> schema server["src/server"] --> schema server --> renderer ai["src/ai"] --> server worker["src/worker"] --> ai api["src/lib/api (route tables)"] --> server api --> ai app["src/app (routes)"] --> api components["src/components (UI)"] --> renderer components --> sim
| Module | Responsibility |
|---|---|
src/schema |
Sheet Document Schema v1 (Zod), parseSheet, migrateSheet, stableStringify, traversal, applyPatch, diffSheets, slugify — imports zod only; runs in the browser too |
src/renderer |
block components, SheetView, diagram layout, chart drawing, inline parser, KaTeX wrapper, SVG sanitiser, theme palettes, standalone SVG export |
src/sim |
the 13 simulations: pure compute, a View, shared numeric core, the expression parser |
src/templates |
12 built-in templates as code |
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 |
src/server |
all writes to sheets, assets and scanning, storage driver, job queue API, export requests, quality report, settings, render tokens |
src/ai |
client.ts (the only caller of the Anthropic API), prompts.ts, pipeline.ts, net.ts, sources.ts, pricing.ts |
src/worker |
queue loop, job state transitions, export rendering — not part of the Next.js build |
src/components |
site chrome, studio pages, editor — types only from server and ai |
src/app |
routes; thin wrappers over server functions and the route tables |
The import direction is a convention; there is no lint rule that enforces it.
A Claude generation job
sequenceDiagram
autonumber
participant B as Studio (browser)
participant W as web process
participant DB as PostgreSQL
participant K as worker
participant C as api.anthropic.com
B->>W: POST /api/admin/generations (kind generate, brief, outline, idempotencyKey)
W->>W: requireAdmin, schemas, key present, attachments exist
W->>DB: INSERT generation_jobs (queued) + pg_notify('sheet_jobs')
W-->>B: job view
B->>W: EventSource /api/admin/generations/:id/events (row re-read every 500 ms)
K->>DB: LISTEN sheet_jobs + 2 s poll, claim with FOR UPDATE SKIP LOCKED
K->>K: extractMaterial (attachments, URLs through safeFetch)
K->>C: callClaude — budget check, stream, ledger row
C-->>K: text
K->>K: extractJson → normalizeModelDoc → parseSheet (up to 2 repair rounds)
K->>DB: createSheet: sheets row + revision 1 (origin ai_generation) — a draft
K->>DB: UPDATE generation_jobs SET status = 'succeeded'
W-->>B: event "succeeded" → /admin/sheets/:sheetId/edit
A save in the editor
sequenceDiagram
autonumber
participant E as Editor (client)
participant W as web process
participant DB as PostgreSQL
E->>E: change → autosave timer (2.5 s) or ⌘S, parseSheet(doc) in the browser
E->>W: PATCH /api/admin/sheets/:id { doc, expectedVersion } + X-CSRF-Token
W->>W: requireAdmin → body under a 2 MB cap → Zod → validDoc (422 with issues if invalid)
W->>DB: SELECT … FOR UPDATE (one transaction)
alt version ≠ expectedVersion
W-->>E: 409 version_conflict (editor stops autosave, offers a reload)
else same doc_hash as current
W-->>E: { changed: false } — no revision
else
W->>DB: INSERT sheet_revisions (origin manual), UPDATE sheets, syncTaxonomy
W-->>E: { version, revisionId, revisionNumber, diff, quality }
end
Exports 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.
Technical trade-offs (from docs/ARCHITECTURE.md §7)
| Decision | Reason | Cost |
|---|---|---|
| One package with modular folders, not a monorepo | One deployable; a workspace would add tooling for no isolation gain | Module boundaries rely on discipline |
| 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 |
| 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 |
| A separate worker process | Model calls last minutes and exports need a browser | Two processes to supervise; they must share STORAGE_ROOT |
| 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 |
| 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 |
| A custom Node server in front of Next.js | Explicit timeouts and graceful stop | tsx is a runtime dependency |
Static CSP with 'unsafe-inline' |
Next's inline bootstrap scripts would otherwise need a per-request nonce | Script injection is not blocked by CSP |
Gateway compresses, app does not (compress: false) |
Avoids double work behind a proxy | A proxy without compression serves uncompressed responses |
| Built-in templates live in code | Versioned and tested against the schema | Changing one needs a deployment |
Only the local storage driver |
A directory or NAS mount is enough for one node | No S3 |
| Undo history as whole documents (100 steps) | Documents are small JSON values | Memory grows with very large sheets |
| Published documents are migrated when read, never rewritten | Publications stay immutable across schema versions | Every read pays the migration once SCHEMA_VERSION moves |
🧱 Stack
| Layer | Technology | Version (from package.json) |
Role |
|---|---|---|---|
| Framework | Next.js (App Router) behind a custom node:http server (server.ts) |
^16.4.6 | pages, catch-all API routes |
| UI | React · React DOM | ^19.3.1 | site, studio, editor |
| Language | TypeScript · tsx (runtime dependency) | ^6.0.3 · ^4.23.15 | both processes run TypeScript directly |
| Styling | Tailwind CSS (@tailwindcss/postcss) |
^4.3.5 | site and studio chrome (--ui-* tokens); sheets use --sh-* tokens in src/renderer/sheet.css |
| Database | PostgreSQL + citext, pg_trgm, unaccent |
17 | documents, queue, sessions, limits, audit, ledger, full-text search |
| Driver | postgres (postgres.js) | ^4.1.1 | tagged-template SQL, no ORM |
| Auth | @simplewebauthn/server · @simplewebauthn/browser |
14.1.0 · ^14.0.0 | passkeys |
| AI | @anthropic-ai/sdk |
^1.8.0 | Claude studio (default model claude-opus-5-5) |
| Validation | Zod | ^4.6.7 | Sheet Document Schema, request bodies, settings |
| Maths | KaTeX | ^0.19.1 | equations |
| Diagrams | @dagrejs/dagre |
^3.1.3 | diagram layout |
| Charts | d3-array · d3-scale · d3-shape | ^3.2.5 · ^4.0.3 · ^3.2.1 | scales and shapes for the SVG charts |
| SVG sanitising | htmlparser2 | ^12.0.0 | uploaded and pasted SVG |
| Fonts | Fraunces · Inter · JetBrains Mono (@fontsource-variable) |
^5.3.0 | self-hosted |
| Exports | playwright-core (Chromium headless shell) | ^1.65.1 | PDF and PNG |
| Tests | Vitest · @playwright/test |
^5.1.0 · ^1.65.1 | unit · browser suites |
| Lint | ESLint · eslint-config-next | ^10.15.0 · ^16.4.6 | static checks |
| Process manager | PM2 (production) | — | sheet-web, sheet-worker, sheet-backup |
🗂 Repository layout
268 tracked files; 223 TypeScript files, 26 963 lines (tests and scripts included).
| Path | Content |
|---|---|
server.ts |
web process entry point |
src/schema/ |
Sheet Document Schema v1, parsing, migration, patches, diff (pure) |
src/renderer/ |
block components, diagram layout, charts, KaTeX, SVG sanitiser, themes (sheet.css), standalone SVG export |
src/sim/ |
the 13 built-in simulations and their numeric core |
src/templates/ |
12 built-in templates (French and English) |
src/ai/ |
Anthropic client, prompts, pipeline, outbound-request guard, reference checks, price table |
src/server/ |
sheets, publication, assets, storage, jobs, exports, quality report, settings |
src/worker/ |
worker loop and export rendering |
src/lib/ |
env, database client, auth, sessions, rate limits, audit, HTTP helpers, API route tables |
src/components/ |
site chrome, studio pages, editor |
src/app/ |
Next.js routes |
src/i18n/ |
interface dictionaries (French, English) |
db/migrations/ |
SQL migrations (001_init.sql) |
scripts/ |
migrate, bootstrap, seed, recovery, backup, restore, tests, secret scan, icon and PDF helpers |
tests/ |
unit tests (tests/unit, 17 files), HTTP integration tests, the Anthropic stand-in, a software authenticator |
e2e/ |
Playwright configuration and specs (studio, studio-phone, public) |
infra/ |
Docker Compose alternative and reverse-proxy examples (written, not run — see infra/README.md) |
public/ |
PWA manifest, service worker, offline page, icons |
docs/ |
ten documents (see Documentation) and brand/ |
Screenshots 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.
📋 Requirements
- Node.js with
pnpm(developed on Node 26 and pnpm 11;pnpm-workspace.yamlalso carries the pnpm 12 build allow-list). - PostgreSQL 17 with the
citext,pg_trgmandunaccentextensions available (they ship with the standard contrib package). - For exports: a Chromium binary usable by
playwright-core. - For backups:
pg_dump/pg_restorematching the server version,openssl,tar. - For passkeys in production: HTTPS on the exact origin given in
APP_URL. - Optional: an Anthropic API key (
ANTHROPIC_API_KEY); aclamdsocket (CLAMD_SOCKET).
🚀 Local install
pnpm install
cp .env.example .env && chmod 600 .env # then fill it in; see the comments in the file
createdb sheet # the role in DATABASE_URL must be able to CREATE EXTENSION
pnpm migrate # applies db/migrations/*.sql once each (checksummed)
pnpm bootstrap # creates the administrator, writes the one-time enrolment link
pnpm seed # topics + three demonstration sheets as drafts (--publish-examples to publish)
node_modules/.bin/playwright-core install chromium-headless-shell # browser for exports (see docs/DEPLOYMENT.md)
pnpm dev # web process on PORT (default 8700), development mode
pnpm worker # second terminal: jobs and exportsFor 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.
🧰 Commands
| Command | What it runs |
|---|---|
pnpm dev |
tsx server.ts — Next.js in development mode behind the Node server |
pnpm build |
next build |
pnpm start |
NODE_ENV=production tsx server.ts — the web process |
pnpm worker |
tsx src/worker/index.ts — the worker process |
pnpm migrate |
scripts/migrate.ts |
pnpm bootstrap |
scripts/bootstrap.ts (--renew issues a fresh enrolment link while no passkey exists) |
pnpm seed |
scripts/seed.ts (--publish-examples) |
tsx scripts/setup-token.ts [--recovery] |
recovery link from the server's shell (interactive terminal only) |
bash scripts/backup.sh / bash scripts/restore.sh |
encrypted backup (with an HMAC per archive) and verified restore |
pnpm typecheck / pnpm lint |
tsc --noEmit / ESLint (src scripts tests e2e server.ts) |
pnpm test:unit |
vitest, pure functions and server-rendered components, no database |
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 |
pnpm secrets:scan |
scripts/secrets-scan.ts |
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 |
⚙️ Configuration
Names 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.
| Variable | Required | Meaning |
|---|---|---|
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 |
WEBAUTHN_RP_ID |
no | Defaults to the host of APP_URL. Changing it later invalidates every passkey |
APP_SECRET |
yes | ≥ 32 characters. Signs preview and render tokens |
PORT, HOST |
no | Default 8700, 0.0.0.0. Prefer a loopback HOST when the proxy is on the same machine |
ADMIN_EMAIL, ADMIN_NAME |
first run | The administrator created by bootstrap (there is no password) |
SETUP_URL_FILE |
recommended | Where the one-time enrolment link is written (mode 0600) |
DATABASE_URL |
yes | PostgreSQL connection string |
ANTHROPIC_API_KEY |
no | Without it the studio is a manual editor and says that AI generation is not configured |
ANTHROPIC_MODEL |
no | Default model until one is chosen in the settings |
STORAGE_DRIVER |
no | Only local is implemented |
STORAGE_ROOT |
yes | Directory for uploads and exports, outside the application directory |
UPLOAD_MAX_MB, STORAGE_QUOTA_GB |
no | Default 20 and 5 |
CLAMD_SOCKET |
no | Unix socket path or host:port. Unset = uploads stored unscanned |
INTERNAL_URL |
no | Address of the web process as seen from the worker (default: loopback on PORT) |
CHROMIUM_PATH |
no | Chromium binary for exports; unset = the one playwright-core reports |
BACKUP_DIR, BACKUP_PASSPHRASE_FILE, BACKUP_KEEP_DAYS |
for backups | See docs/BACKUP_RESTORE.md |
LOG_LEVEL |
no | debug, info (default), warn, error |
REAL_AI |
tests only | REAL_AI=1 scripts/test.sh adds a run against the real Anthropic API (costs money) |
Non-secret settings (model, efforts, budgets, view counter…) live in the settings table and are edited in /admin/settings.
🖥 Routes
17 pages and 9 route handlers under src/app/.
| Path | Kind | Purpose |
|---|---|---|
/ |
page | home |
/s/[slug] |
page | a published sheet (+ generated Open Graph image) |
/topics · /topics/[slug] |
pages | topics |
/search |
page | search (noindex) |
/about |
page | about |
/admin |
page | dashboard: service health, queue state, AI spend |
/admin/login · /admin/setup |
pages | passkey sign-in · one-time enrolment |
/admin/sheets/new |
page | creation assistant |
/admin/sheets/[id]/edit |
page | editor |
/admin/assets |
page | media and data library |
/admin/templates |
page | templates |
/admin/revisions |
page | revisions |
/admin/settings |
page | settings: passkeys, sessions, Claude, topics, audit |
/preview/[token] |
page | private draft preview (24 h, revocable, noindex) + /preview/[token]/asset/[id] |
/render/[token] |
page | bare page for exports + /render/[token]/asset/[id] |
/media/[id] |
route | public files (only assets flagged is_public) |
/rss.xml · /sitemap.xml · robots.txt |
routes | feeds and crawlers |
/api/health |
route | liveness: 200 {"ok":true} when the web process answers and SELECT 1 works, 503 {"ok":false} otherwise |
/api/public/[...path] · /api/auth/[...path] · /api/admin/[...path] |
routes | the three API surfaces |
🔌 API
66 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.
| Surface | Table | Endpoints | Guard |
|---|---|---|---|
/api/public/* |
src/lib/api/publicroutes.ts |
6 | none (rate limit per client address inside each handler) |
/api/auth/* |
src/lib/api/authroutes.ts |
15 | per route: same-origin check, rate limit, session + CSRF where relevant |
/api/admin/* |
src/lib/api/adminroutes.ts |
45 | requireAdmin: passkey session, CSRF header on non-GET |
Public API — read-only, anonymous, cacheable; reads published snapshots only
| Method | Path | Purpose |
|---|---|---|
| GET | /api/public/sheets |
list of published sheets (locale, level, kind, topic, tag, q, limit ≤ 50, offset) |
| GET | /api/public/search |
search (q required) |
| GET | /api/public/sheets/:slug |
one published sheet with its document; 301 from an old slug |
| GET | /api/public/topics |
topics |
| GET | /api/public/feed |
JSON Feed |
| POST | /api/public/views |
view beacon (per sheet, per day; no visitor data) |
Authentication API
| Method | Path | Purpose |
|---|---|---|
| GET | /api/auth/session |
current session |
| POST | /api/auth/setup/info |
state of a one-time enrolment link |
| POST | /api/auth/register/options · /api/auth/register/verify |
passkey enrolment ceremony |
| POST | /api/auth/login/options · /api/auth/login/verify |
passkey sign-in ceremony |
| POST | /api/auth/recovery |
sign-in with a recovery code (session limited to enrolling a passkey) |
| POST | /api/auth/reauth/options · /api/auth/reauth/verify |
fresh verification for sensitive actions |
| GET | /api/auth/passkeys |
list of passkeys |
| PATCH · DELETE | /api/auth/passkeys/:id |
rename · remove (the last passkey cannot be removed) |
| POST | /api/auth/recovery-codes |
new set of ten codes |
| DELETE | /api/auth/sessions/:id |
revoke a session |
| POST | /api/auth/logout |
sign out |
Studio API (45 endpoints)
| Area | Endpoints |
|---|---|
| Dashboard | GET dashboard |
| Sheets | GET sheets · POST sheets · GET sheets/:id · PATCH sheets/:id · DELETE sheets/:id |
| Publication | POST sheets/:id/publish · POST sheets/:id/unpublish · POST sheets/:id/preview-link · DELETE sheets/:id/preview-link |
| Revisions | GET sheets/:id/revisions · GET sheets/:id/revisions/:rid · POST sheets/:id/revert |
| Quality | POST quality · POST validate |
| 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 |
| Exports | POST exports · GET exports/:id · GET sheets/:id/exports · GET sheets/:id/export.json |
| Assets | GET assets · POST assets · PATCH assets/:id · DELETE assets/:id · GET assets/:id/dataset · GET assets/:id/file · POST svg/sanitize |
| Templates | GET templates · GET templates/:key/preview · DELETE templates/:id · POST sheets/:id/save-as-template |
| Topics | GET topics · PATCH topics/:id · DELETE topics/:id |
| Settings and audit | GET settings · PATCH settings · GET audit |
All paths are relative to /api/admin/.
📐 Document format
A 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.
The 23 block types of Sheet Document Schema v1 (src/schema/sheet.ts):
| Text | Structure | Visual | Interactive |
|---|---|---|---|
heading · paragraph · list · equation · callout · code · references |
table · cards · steps · legend · comparison · divider |
diagram · timeline · matrix · chart · svg · image |
accordion · quiz · simulation · tabs |
- Inline syntax without HTML (
src/renderer/inline.tsx); equations with KaTeX. - Charts: line, area, bar, scatter, histogram — optionally from an uploaded dataset (
datasetRef). - Diagrams: layout engine with routing and groups (
@dagrejs/dagre), animation steps with a player, zoom, pan and full screen for readers. - Themes:
scientific-light,scientific-dark,editorial,blueprint,finance. Documents never carry colours, only a preset, an accent tone and per-element tones (8 tones). - Fixed formats:
a4,slide-16-9,square,portrait-4-5keep their pixel size; the responsive layout is a 12-column grid that responds to the width of the sheet itself (CSS container query). - Patch operations and block diff (
applyPatch,diffSheets): every Claude edit is a patch and an undoable revision. - Exports: PDF, PNG ×1/×2/×4, per-block SVG, portable JSON (
GET /api/admin/sheets/<id>/export.json).
📰 Publication model
Four things, three of them immutable.
| Thing | Table / column | Mutable? | Meaning |
|---|---|---|---|
| Working copy | sheets.doc |
yes | What the studio shows. Always equal to the document of sheets.current_revision_id |
| 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 |
| Publication | sheet_publications |
no | A copy of one revision taken at publication time. Publishing again adds a row |
| Pointer | sheets.publication_id |
yes | The one publication the public sees. NULL when not published |
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.
- Statuses are
draft,in_review,published,archived;in_reviewandarchivedare labels with no workflow. There is no scheduled publication. deleteSheet()is a soft delete, refused while published.- A slug change is recorded in
slug_redirectsif the sheet has ever been public; public lookups redirect from the old address. - A French and an English sheet are two
sheetsrows sharing alocalization_id. A sheet's locale cannot change; translation creates the sibling.
🗄 Data model
31 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.
| Group | Tables |
|---|---|
| Administrator and authentication | users · passkey_credentials · setup_tokens · recovery_codes · webauthn_challenges · sessions · rate_limits · audit_events · settings · worker_heartbeat |
| Content | topics · tags · sheet_localizations · sheets · sheet_revisions · sheet_publications · slug_redirects · sheet_topics · sheet_tags · preview_tokens · templates · sheet_views |
| Media, data, sources | assets · datasets · sources · sheet_sources |
| Claude studio and exports | ai_conversations · ai_messages · generation_jobs · generation_usage · export_jobs |
erDiagram
sheet_localizations ||--o{ sheets : "language versions"
sheets ||--o{ sheet_revisions : "immutable saved states"
sheets ||--o{ sheet_publications : "immutable public snapshots"
sheet_revisions ||--o{ sheet_publications : "copied at publication"
sheets ||--o{ sheet_topics : has
topics ||--o{ sheet_topics : groups
sheets ||--o{ sheet_tags : has
tags ||--o{ sheet_tags : labels
sheets ||--o{ preview_tokens : "private links"
sheets ||--o{ sheet_views : "per-day counter"
sheets ||--o{ sheet_sources : cites
sources ||--o{ sheet_sources : "library"
assets ||--o| datasets : "parsed CSV / JSON"
sheets ||--o{ ai_conversations : has
ai_conversations ||--o{ ai_messages : contains
generation_jobs ||--o{ generation_usage : "ledger rows"
sheets ||--o{ export_jobs : exports
users ||--o{ passkey_credentials : owns
users ||--o{ sessions : opens
Search 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 §5.
🤖 Claude studio pipeline
Reference: docs/AI_PIPELINE.md. AI routes answer 503 ai_not_configured when ANTHROPIC_API_KEY is unset; verify_sources works without a key.
| Job kind | Started by | Result |
|---|---|---|
outline |
POST /api/admin/generations (kind: "outline") |
an outline for the author to correct; nothing is stored besides the job output |
generate |
POST /api/admin/generations (kind: "generate") |
a new sheet (draft) with revision 1, origin ai_generation |
edit |
POST /api/admin/sheets/<id>/ai-edit |
a new revision, origin ai_edit, with the patch stored; or no change |
translate |
POST /api/admin/sheets/<id>/translate |
a new linked sheet (draft) in the other language |
verify_sources |
POST /api/admin/sheets/<id>/verify-sources |
a new revision, origin source_check, with updated source statuses. No model call |
| Setting | Default |
|---|---|
Model (settings.aiModel, chosen from the live Models API list; empty → ANTHROPIC_MODEL) |
claude-opus-5-5 |
| Effort for generation · for edits and translation · for outlines | high · medium · medium (fixed in code) |
Output cap per call (settings.aiMaxOutputTokens, 4 000–128 000) |
48 000 |
| Budgets in US dollars, 0 = no limit: per job · daily · monthly | 5 · 25 · 200 |
aiJobsPerHour · aiJobTimeoutMinutes |
40 · 20 |
- 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. - Output contract:
extractJson→normalizeModelDoc→parseSheet, with up to 2 repair rounds; a clear failure when the output never validates or the model declines. - Source material (uploads and links) is wrapped as data; URLs go through
safeFetch, which refuses private addresses. - Budgets:
assertBudgetruns before each model call against thegeneration_usageledger, so a limit can be exceeded by the cost of one call.src/ai/pricing.tsholds 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. - Not done, by design: tool use / agents, automatic publication. Not done: a web research provider.
Real 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.
🛡 Security and privacy
Threat model, controls and limits: docs/SECURITY.md.
| Topic | Control |
|---|---|
| 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 |
| Bootstrap | one administrator row created by scripts/bootstrap.ts; a 24-hour, one-use enrolment link written to a 0600 file |
| 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 |
| Sessions | server-side, rotated at each sign-in, revocable, listed in the settings: 14 days absolute, 7 days without use |
| CSRF | header token on every state change (requireAdmin) + same-origin check |
| Re-authentication | fresh passkey verification to add or remove a passkey and to issue recovery codes |
| 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 |
| Lock-out | per client address, from failed sign-ins of the last hour: 5 → 1 min, 10 → 5 min, 20 → 1 h |
| 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 |
| 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 |
| XSS | schema-only documents and React escaping: <script>, onerror= and javascript: links typed into a block are shown as text |
| Outbound requests | safeFetch (src/ai/net.ts) for source material and reference checks; private addresses refused |
| Model output | crosses into the database only through extractJson → normalizeModelDoc → parseSheet (or applyPatch) |
| Export rendering | HMAC token naming one document; the worker's browser aborts every request to another origin |
| 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 |
| Audit | append-only audit_events: sign-ins, failures, recovery, revocations, publications, uploads, settings and jobs |
| Privacy | the view counter is per sheet and per day and stores no visitor data; it can be switched off |
✅ Testing
Four suites (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.
| Suite | Command | Result of the run of 2026-11-17 |
|---|---|---|
| Unit | pnpm test:unit |
711 passed, 0 failed (17 files) |
Integration — tests/api.test.ts |
pnpm test |
53 passed, 0 failed |
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 |
| Secret scan | pnpm test / pnpm secrets:scan |
clean on 255 files; bundle and logs free of the API key and APP_SECRET |
| 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 |
| Browser | pnpm test:e2e |
27 passed (studio 13, studio on a phone 2, public pages 6 × 2 browsers) |
| Real API | REAL_AI=1 scripts/test.sh |
2 passed (one generation, one edit) |
| Lint, types | pnpm lint · pnpm typecheck |
clean |
| Production | public browser suite against https://sheet.spboucher.ai | 12 passed (6 in Chromium at seven widths, 6 in WebKit with an iPhone profile) |
What the integration suite asserts
- Anonymous access: 401 on every
/api/admin/*and credential route (18 GET, 14 write routes); studio pages redirect to sign-in and arenoindex; security headers present. - 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.
- 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.
- 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.
- 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.
- Draft isolation, injected markup, uploads (hostile SVG stored without script, handlers, foreign content or external references).
- 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.
- 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.
- Audit: no secret appears in the trail.
Not covered by automated tests
- 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.
- WebKit for the studio: WebKit has no virtual authenticator, so only the public pages run in WebKit.
- VoiceOver: accessibility is checked structurally, not with a screen reader.
- Field Core Web Vitals: only lab measurements exist.
- ClamAV: the scanner protocol code is not exercised.
- Reference checking against the network is not part of the suites; it was run by hand on 2026-11-17.
- Docker Compose files in
infra/were not built or run. - Pinch-zoom and drag gestures in the diagram viewer and the visual diagram editor.
🚢 Deployment
Generic self-hosted installation, reverse proxy, upgrade and rollback: docs/DEPLOYMENT.md. The infra/ directory holds a Docker Compose alternative and Caddy / nginx examples that the production installation does not use.
This installation
Deployed on 2026-11-17 on the MacLustr cluster, through its orchestrator (mld), not with Docker.
| Item | Value |
|---|---|
| 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) |
| Node | M2M32c (Mac Studio, Apple silicon), chosen by mld pick; application directory ~/apps/sheet — a git clone, origin gitsrv:srv/git/sheet.git |
| Processes (PM2) | sheet-web (port 8700), sheet-worker, sheet-backup (cron 03:25 every day) |
| Database | PostgreSQL 17 on the node, database sheet, role sheet_app; extensions citext, pg_trgm, unaccent created once by the superuser |
| Storage | ~/data/sheet/storage |
| Backups | ~/backups/sheet (same disk — see docs/BACKUP_RESTORE.md) |
| Secrets | ~/apps/sheet/.env (mode 600), never in git |
| Manifest | M1M32:~/dispatch/apps/sheet.json (processes, hooks, tunnel route) |
| Headless browser | chromium-headless-shell, installed by the deployment hook (playwright-core install) |
Deployment 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.
Update procedure (the node's clone is the source of truth):
ssh M2M32c
cd ~/apps/sheet && git pull --ff-only
pnpm install --frozen-lockfile
node_modules/.bin/tsx scripts/migrate.ts
node_modules/.bin/next build
pm2 restart sheet-web sheet-worker
curl -fsS http://127.0.0.1:8700/api/healthRollback: 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.
Verified 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.
Not verified on this installation: enrolment and sign-in with a real passkey, and therefore a Claude generation started from the production studio.
Backups
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.
🩺 Operations and troubleshooting
Runbook: docs/OPERATIONS.md — processes, logs (JSON lines in the worker), health, the job queue (states, inspect, cancel, re-queue), incidents, housekeeping, dependency upgrades, rollback.
| Check | How |
|---|---|
| Liveness | GET /api/health |
| Dashboard tiles | /admin: database, Claude API (spends no tokens), job queue and worker, storage (write probe), antivirus |
| Worker heartbeat | worker_heartbeat row, updated every 15 s; the dashboard calls the worker alive when the last beat is under 45 s old |
| Spend | dashboard, from generation_usage: today and this month against the budgets |
SELECT name, now() - beat_at AS age, info FROM worker_heartbeat;| Symptom | Cause | Action |
|---|---|---|
Jobs fail with code auth |
the Anthropic API key was refused | check ANTHROPIC_API_KEY in the environment of both processes, restart them |
AI routes answer 503 ai_not_configured |
no key at all | the studio works as a manual editor; source checks still work |
Jobs fail with code budget |
daily, monthly or per-job limit reached | wait, or raise the budget in Settings → Claude (0 = no limit) |
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 |
422 whole_sheet_only |
PDF requested for a single block | export one block as PNG or SVG |
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 |
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 |
| 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 |
409 version_conflict; "The sheet was changed elsewhere … Reload" |
two writers on one sheet | reload the tab that lost (copy anything important first) |
| "Invalid document — not saved" | the working copy does not pass the schema; autosave is suspended | fix the issue named in the banner, or undo |
| 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 |
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 |
| 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 |
| A published sheet has broken images | the public flag was withdrawn on unpublish | publish again |
| A reader PDF is missing | export job failed or never queued | publish again, or insert the export_jobs row (SQL in the runbook) |
🚧 Status and known limitations
Status per phase and everything that is not implemented: docs/ROADMAP.md.
| Not done or partial | Note |
|---|---|
| CI pipeline | no CI configuration in the repository |
| Multiple users or roles | single administrator |
| Attestation verification | attestationType: "none" |
| Scheduled publication | no schedule column, no scheduler |
| S3 / MinIO storage driver | interface only; STORAGE_DRIVER refuses anything but local |
| Whole-sheet SVG export | SVG is per vector block; a whole sheet is PDF or PNG |
| PDF of a single block | refused with 422 whole_sheet_only |
| Cross-block interactions | interactions must be empty in v1 |
| Sharing image chosen per sheet | accepted by the API, no field in the editor |
| Cost estimate before generation | heuristic from the outline |
| Web research provider | the model works from supplied material or memory |
| Author-defined simulations or custom code | by design: documents never carry code |
Residual security risks (from docs/SECURITY.md §17)
- The CSP allows inline scripts; the protection against XSS is the schema-only document model and React escaping, not the CSP.
- A single administrator, no roles, no second approver for publication.
- No attestation verification.
- A stolen session cookie works until the session ends (14 days at most, 7 days without use) or is revoked.
- The client address is the last
X-Forwarded-Forvalue: if the web port is reachable without the proxy, rate limits, lock-out and audit addresses can be spoofed. - Lock-out is per address.
- ClamAV is optional; PDFs are sent to the Anthropic API when attached to a brief.
- Request bodies are held in memory while they are read (up to the cap).
- Public flags are withdrawn on unpublish only.
/render/<token>is reachable with a valid short-lived token; it is not restricted to loopback.- Preview links are bearer tokens, valid up to 24 hours.
- The view counter is unauthenticated.
- Backups are written to a local directory, and two of their steps expose a secret in the process list while they run.
- The audit trail is in the same database and is not tamper-evident.
- Asset deletion does not look at old revisions.
- Locks protect a block and the tab group around it, not the reverse.
- No error-reporting or intrusion-detection service is integrated.
- Model-written content can be wrong: the quality report is mechanical.
- One worker process is assumed.
📚 Documentation
- docs/ARCHITECTURE.md — components, data flows, publication model, data model, design system, trade-offs
- docs/SECURITY.md — threat model and controls, known limits
- docs/AI_PIPELINE.md — the Claude studio pipeline
- docs/SHEET_SCHEMA.md — the document format and the simulation catalogue
- docs/EDITOR.md — the studio, screen by screen
- docs/DEPLOYMENT.md — installation, reverse proxy, upgrade, rollback
- docs/BACKUP_RESTORE.md — backups and the restore drill
- docs/OPERATIONS.md — running it: logs, health, queue, incidents
- docs/ROADMAP.md — status per phase and what is not implemented
- docs/TESTING.md — the four suites, with measured results
- infra/README.md — the Docker Compose alternative
👤 Author
Simon-Pierre Boucher — design, development, operations
📄 License
All rights reserved © 2026 Simon-Pierre Boucher.