SPB Git

Repository Public

23 entries at the root
History

Latest commit docs: legendary README — icon, logo, live spbgit badges, author 4e908cf

NameLast commitUpdated
db Sheet 1.0.0: publishing platform for scientific sheets
docs docs: legendary README — icon, logo, live spbgit badges, author
e2e Sheet 1.0.0: publishing platform for scientific sheets
infra Documentation brought in line with the review fixes; production measurements in the test report
public App icons (hidden by a global ignore rule), deployment and backup drill notes
scripts Quick-sheet PDFs: drawings planned for their panel, full pages, references that flow
src Quick-sheet PDFs: drawings planned for their panel, full pages, references that flow
tests Typeset maths everywhere, house marks, formula sheet and third design pass
.env.example Sheet 1.0.0: publishing platform for scientific sheets
.gitignore Sheet 1.0.0: publishing platform for scientific sheets
.mldignore Staging exclusions
AGENTS.md Sheet 1.0.0: publishing platform for scientific sheets
eslint.config.mjs Sheet 1.0.0: publishing platform for scientific sheets
next.config.ts Sheet 1.0.0: publishing platform for scientific sheets
package.json Sheet 1.0.0: publishing platform for scientific sheets
pnpm-lock.yaml Sheet 1.0.0: publishing platform for scientific sheets
pnpm-workspace.yaml Sheet 1.0.0: publishing platform for scientific sheets
postcss.config.mjs Sheet 1.0.0: publishing platform for scientific sheets
README.md docs: legendary README — icon, logo, live spbgit badges, author
server.ts Sheet 1.0.0: publishing platform for scientific sheets
tsconfig.json Sheet 1.0.0: publishing platform for scientific sheets
vitest.config.ts Sheet 1.0.0: publishing platform for scientific sheets
vitest.unit.config.ts Sheet 1.0.0: publishing platform for scientific sheets
README.md View source
Sheet logo



Sheet icon

# 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

sheet.spboucher.ai version platform languages license author

Next.js React TypeScript Tailwind CSS PostgreSQL postgres.js WebAuthn Anthropic SDK Zod KaTeX dagre d3 Playwright Vitest ESLint Node.js Redis

commits last commit activity language languages size branches tags clones

TypeScript files TypeScript lines API endpoints pages tables block types simulations templates unit tests integration tests browser tests Lighthouse

node port PM2 deploy tunnel


# Table of contents


# 🎯 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 with hreflang alternates, 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, otherwise Accept-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/&lt;id&gt; · preview/&lt;token&gt; · render/&lt;token&gt;"]
  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/&lt;token&gt;" --> 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.yaml also carries the pnpm 12 build allow-list).
  • PostgreSQL 17 with the citext, pg_trgm and unaccent extensions available (they ship with the standard contrib package).
  • For exports: a Chromium binary usable by playwright-core.
  • For backups: pg_dump/pg_restore matching 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); a clamd socket (CLAMD_SOCKET).

# 🚀 Local install

bash
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 exports

For 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-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).
  • 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_review and archived are labels with no workflow. There is no scheduled publication.
  • deleteSheet() is a soft delete, refused while published.
  • A slug change is recorded in slug_redirects if the sheet has ever been public; public lookups redirect from the old address.
  • A French and an English sheet are two sheets rows sharing a localization_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: 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.
  • 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 are noindex; 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):

bash
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/health

Rollback: 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
sql
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)
  1. The CSP allows inline scripts; the protection against XSS is the schema-only document model and React escaping, not the CSP.
  2. A single administrator, no roles, no second approver for publication.
  3. No attestation verification.
  4. A stolen session cookie works until the session ends (14 days at most, 7 days without use) or is revoked.
  5. 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.
  6. Lock-out is per address.
  7. ClamAV is optional; PDFs are sent to the Anthropic API when attached to a brief.
  8. Request bodies are held in memory while they are read (up to the cap).
  9. Public flags are withdrawn on unpublish only.
  10. /render/<token> is reachable with a valid short-lived token; it is not restricted to loopback.
  11. Preview links are bearer tokens, valid up to 24 hours.
  12. The view counter is unauthenticated.
  13. Backups are written to a local directory, and two of their steps expose a secret in the process list while they run.
  14. The audit trail is in the same database and is not tamper-evident.
  15. Asset deletion does not look at old revisions.
  16. Locks protect a block and the tab group around it, not the reverse.
  17. No error-reporting or intrusion-detection service is integrated.
  18. Model-written content can be wrong: the quality report is mechanical.
  19. One worker process is assumed.

# 📚 Documentation


# 👤 Author

Simon-Pierre Boucher — design, development, operations

www.spboucher.ai · git.spboucher.ai · contact@spboucher.ai

# 📄 License

All rights reserved © 2026 Simon-Pierre Boucher.

Clone sheet

Anonymous clone and fetch. Pushing requires the owner's personal access token.

Snapshot of main

Files main
Menu

Theme