Repository Public
1# Deployment23A generic self-hosted installation, step by step, then the Docker Compose alternative, first sign-in, upgrades and rollback. Everything here is derived from the code and scripts in the repository; nothing in this document reports a deployment that was carried out.45## 1. What runs67| Piece | Command | Notes |8|---|---|---|9| Web process | `NODE_ENV=production node_modules/.bin/tsx server.ts` (`pnpm start`) | listens on `HOST` (default `0.0.0.0`) and `PORT` (default `8700`) |10| Worker process | `node_modules/.bin/tsx src/worker/index.ts` (`pnpm worker`) | one instance only (see [OPERATIONS.md](OPERATIONS.md)) |11| Backup | `bash scripts/backup.sh`, scheduled daily | see [BACKUP_RESTORE.md](BACKUP_RESTORE.md) |12| PostgreSQL 17 | external | one database |13| Reverse proxy | external | terminates TLS |1415Both processes read the same `.env`, the same database and the same `STORAGE_ROOT` directory. If they run on different machines, `STORAGE_ROOT` must be a shared mount and `INTERNAL_URL` must reach the web process from the worker.1617## 2. Requirements1819- Node.js (developed on Node 26) and pnpm (11 or 12).20- PostgreSQL 17 with the contrib extensions `citext`, `pg_trgm`, `unaccent`.21- Chromium for exports, installed through `playwright-core` (step 9).22- `pg_dump` / `pg_restore` 17, `openssl`, `tar`, `bash` for backups.23- A DNS name and a reverse proxy with a TLS certificate.24- Optional: an Anthropic API key; a `clamd` daemon.2526## 3. Database and role2728```sql29CREATE ROLE sheet_app LOGIN PASSWORD '<a long random password>';30CREATE DATABASE sheet OWNER sheet_app;31```3233The first migration runs `CREATE EXTENSION IF NOT EXISTS citext, pg_trgm, unaccent`. These are trusted extensions in PostgreSQL 13 and later, so the database owner can create them. If your installation restricts that, create the three extensions in the `sheet` database as a superuser before migrating.3435Keep the database reachable from the application hosts only.3637## 4. Code and dependencies3839```bash40git clone <repository> sheet && cd sheet41pnpm install --frozen-lockfile42```4344Development dependencies are needed for the build (Tailwind, TypeScript). `tsx` is a runtime dependency: both processes run TypeScript directly.4546## 5. Configuration (`.env`)4748```bash49cp .env.example .env && chmod 600 .env50```5152`.env` is read by Next.js and by `scripts/_env.ts` (scripts, worker, `server.ts`) from the working directory. Variables already present in the environment win over the file.5354| Variable | Required | Meaning |55|---|---|---|56| `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`. |57| `WEBAUTHN_RP_ID` | no | Defaults to the host of `APP_URL`. Changing it later invalidates every passkey. |58| `APP_SECRET` | yes | ≥ 32 characters (`openssl rand -base64 48`). Signs render tokens. |59| `PORT`, `HOST` | no | Default `8700`, `0.0.0.0`. Prefer `HOST=127.0.0.1` when the proxy is on the same machine. |60| `ADMIN_EMAIL`, `ADMIN_NAME` | first run | The administrator created by `bootstrap`. |61| `SETUP_URL_FILE` | recommended | Where the one-time enrolment link is written (mode 0600). |62| `DATABASE_URL` | yes | `postgres://sheet_app:<password>@<host>:5432/sheet` |63| `ANTHROPIC_API_KEY` | no | Without it the studio is a manual editor. |64| `ANTHROPIC_MODEL` | no | Default model until one is chosen in the settings (`claude-opus-5-5`). |65| `STORAGE_DRIVER` | no | Only `local` is implemented. |66| `STORAGE_ROOT` | yes | Directory for uploads and exports, outside the application directory. |67| `UPLOAD_MAX_MB`, `STORAGE_QUOTA_GB` | no | Default 20 and 5. |68| `CLAMD_SOCKET` | no | Unix socket path or `host:port`. Unset = uploads stored unscanned. |69| `INTERNAL_URL` | no | Address of the web process as seen from the worker. Default `http://127.0.0.1:<PORT>`. |70| `CHROMIUM_PATH` | no | Chromium binary for exports; unset = the one `playwright-core` reports. |71| `BACKUP_DIR`, `BACKUP_PASSPHRASE_FILE`, `BACKUP_KEEP_DAYS` | for backups | See [BACKUP_RESTORE.md](BACKUP_RESTORE.md). |72| `LOG_LEVEL` | no | `debug`, `info` (default), `warn`, `error`. |7374No secret belongs in the database or in the repository.7576## 6. Migrate7778```bash79pnpm migrate80```8182Applies `db/migrations/*.sql` in order, each inside a transaction, and records name and checksum in `schema_migrations`. It refuses to continue if an applied file was modified. Safe to run at every deployment.8384## 7. Bootstrap8586```bash87pnpm bootstrap88```8990Checks that `STORAGE_ROOT` is writable, creates the administrator if none exists, and — while the administrator has no passkey — writes a 24-hour, one-use enrolment link to `SETUP_URL_FILE`. Safe to run at every deployment; once a passkey exists it issues nothing. `node_modules/.bin/tsx scripts/bootstrap.ts --renew` replaces a pending link.9192## 8. Seed (optional)9394```bash95pnpm seed # ten topics + three demonstration sheets (FR and EN) as drafts96tsx scripts/seed.ts --publish-examples # also publishes and features them, with the "example" banner97```9899Idempotent. It never re-publishes a sheet the author unpublished.100101## 9. Chromium for exports102103```bash104node_modules/.bin/playwright-core install chromium-headless-shell # macOS105node_modules/.bin/playwright-core install --with-deps chromium-headless-shell # Debian/Ubuntu: also installs system libraries (root)106```107108Two details of the code matter here:109110- The worker's availability check (`chromiumAvailable` in `src/worker/export.ts`) really launches the browser once at start-up, with `CHROMIUM_PATH` if set, otherwise with whatever build Playwright picks. If the launch fails the worker logs `worker.chromium_missing` and the dashboard reports it.111- Browsers are installed per user (`~/.cache/ms-playwright` on Linux, `~/Library/Caches/ms-playwright` on macOS) unless `PLAYWRIGHT_BROWSERS_PATH` is set. Install them as the user that runs the worker, and reinstall after upgrading `playwright-core`.112113Without Chromium everything works except PDF and PNG exports (and the reader PDF produced at publication).114115## 10. Build116117```bash118pnpm build119```120121Build with the production `.env` in place: the modules imported during the build read `DATABASE_URL` (no connection is opened). `robots.txt` is rendered per request (`src/app/robots.ts` is `force-dynamic`), so `APP_URL` is read at run time.122123## 11. Process manager124125Any supervisor that restarts on exit and forwards `SIGTERM` works. The web process stops accepting connections on `SIGTERM` and exits within 8 s; the worker aborts its jobs, re-queues them and exits within about 20 s.126127PM2 example (`ecosystem.config.cjs`, not part of the repository):128129```js130module.exports = {131 apps: [132 { name: "sheet-web", cwd: "/srv/sheet", script: "node_modules/.bin/tsx", args: "server.ts",133 env: { NODE_ENV: "production" }, kill_timeout: 10000 },134 { name: "sheet-worker", cwd: "/srv/sheet", script: "node_modules/.bin/tsx", args: "src/worker/index.ts",135 env: { NODE_ENV: "production" }, instances: 1, kill_timeout: 25000 },136 { name: "sheet-backup", cwd: "/srv/sheet", script: "scripts/backup.sh", interpreter: "bash",137 autorestart: false, cron_restart: "15 3 * * *" },138 ],139};140```141142systemd equivalent for the web unit: `WorkingDirectory=/srv/sheet`, `Environment=NODE_ENV=production`, `ExecStart=/srv/sheet/node_modules/.bin/tsx server.ts`, `Restart=always`, `TimeoutStopSec=10`; the worker unit runs `src/worker/index.ts` with `TimeoutStopSec=25`.143144Run exactly one worker.145146Health check for the supervisor or the proxy: `GET /api/health` → `200 {"ok":true}` when the process answers and reaches the database, `503` otherwise.147148## 12. Reverse proxy149150Requirements, each tied to something in the code:151152| Requirement | Why |153|---|---|154| HTTPS on the exact host of `APP_URL` | WebAuthn needs a secure context and compares the origin; the session cookie is `Secure` in production; HSTS is sent |155| Send `X-Forwarded-For` with the real client address as its **last** value (set it, or append to it — both work) | Rate limits, lock-out and the audit trail use the last value of that header, the one the proxy wrote (`src/lib/http.ts`). With two proxies in a chain, the inner one must be configured to pass the real client address. |156| The web port reachable only through the proxy | Otherwise a direct client can send its own `X-Forwarded-For`. Use `HOST=127.0.0.1` or a firewall. |157| Request body limit ≥ `UPLOAD_MAX_MB` plus multipart overhead (for example 25 MB for the default 20) | The application enforces its own cap while reading the body; the proxy limit must not be lower, and is a useful first barrier |158| No response buffering for `text/event-stream`, read timeout ≥ 30 minutes on `/api/admin/generations/*/events` | Job progress is a server-sent event stream that can stay open for up to 30 minutes; the application sends `X-Accel-Buffering: no` |159| Read timeout ≥ 5 minutes elsewhere | Matches the web server's `requestTimeout` |160| Compression at the proxy | The application sets `compress: false` |161| Pass security headers through unchanged | CSP, HSTS, `X-Frame-Options` and the others come from the application |162| Optional: answer 404 for `/render/` from outside | That path exists for the worker's browser, which connects to `INTERNAL_URL` directly |163164Do not cache HTML at the proxy: pages vary by the `sheet_locale` cookie and `Accept-Language`. `/api/public/*`, `/media/*`, `/rss.xml` and `/sitemap.xml` send their own `Cache-Control`.165166Example blocks: `infra/Caddyfile.example` and `infra/nginx.conf.example`.167168DNS: an `A`/`AAAA` record for the host of `APP_URL` pointing at the proxy. If the certificate comes from ACME, the record must exist before the proxy starts.169170## 13. Docker Compose alternative171172`infra/` contains a `Dockerfile` and a `docker-compose.yml` with three services (`web`, `worker`, `postgres`), an internal network, PostgreSQL not published, and volumes for the database, storage and backups. Read `infra/README.md` first: those files were written from the code and **were not built or run**.173174## 14. First sign-in1751761. Read the link: `cat "$SETUP_URL_FILE"` on the server. It looks like `https://<host>/admin/setup#<token>` and is valid 24 hours, once.1772. Open it **on the device that should hold the passkey**, on the production origin. The token is in the fragment; it is removed from the address bar as the page loads.1783. Name the device and confirm with Touch ID, Face ID, the device PIN or a security key. User verification is required.1794. Ten recovery codes are shown once. Store them offline.1805. You are signed in. Settings → Passkeys → "Add this device" enrols another passkey from a signed-in browser (it asks for a fresh passkey verification first). For a device that cannot sign in with an existing passkey, use `scripts/setup-token.ts` below.1816. Delete the link file; the token is already consumed.182183To add a device that cannot use an existing passkey, or after losing everything: `tsx scripts/setup-token.ts` (adds a passkey) or `tsx scripts/setup-token.ts --recovery` (replaces all passkeys, signs out every device), run interactively on the server. Links from this script are valid 30 minutes.184185Then, in Settings: choose the Claude model, check the budgets, and look at the dashboard health tiles (database, Claude API, queue and worker, storage, antivirus).186187## 15. Upgrade procedure188189```bash190bash scripts/backup.sh # 1. a fresh backup191git pull # 2. new code192pnpm install --frozen-lockfile # 3. dependencies193node_modules/.bin/playwright-core install chromium-headless-shell # 4. only if playwright-core changed194pnpm migrate # 5. new migrations, if any195pnpm bootstrap # 6. idempotent196pnpm build # 7. new build197# 8. restart the web process, then the worker198```199200Notes:201202- `next build` rewrites `.next` in place. Building while the old web process serves from the same directory can produce a short window of mismatched assets; stop the web process during the build, or build in a second checkout and switch.203- Restarting the worker is safe at any time: running jobs go back to the queue without losing an attempt, and exports in flight are re-queued.204- Sessions, queued jobs and rate limits live in the database and survive the restart.205- A change to `src/ai/prompts.ts` should come with a new `PROMPT_VERSION`; the worker reports the version it runs in its heartbeat.206- If `SCHEMA_VERSION` changed, documents are migrated on read; no data step is needed.207208## 16. Rollback209210Code only (no new migration was applied): check out the previous revision, `pnpm install --frozen-lockfile`, `pnpm build`, restart both processes.211212With a migration applied: migrations are forward-only; there are no down scripts. Either the previous code tolerates the new schema (additive changes usually do), or restore the backup taken in step 1 into a fresh database and storage directory ([BACKUP_RESTORE.md](BACKUP_RESTORE.md)) and point `.env` at them. Work done after that backup is lost.213214`scripts/migrate.ts` refuses to run when an applied migration file differs from its recorded checksum; rolling the code back does not trigger it as long as the applied files are unchanged.215216A document saved by a newer version with a higher `schemaVersion` is refused by older code ("newer than this server"). After a schema bump, rolling back the code requires rolling back the data too.217218## This installation219220Deployed on 2026-11-17 on the owner's MacLustr cluster, through its orchestrator (`mld`), not with Docker.221222| Item | Value |223|---|---|224| Public address | https://sheet.spboucher.ai (DNS `A` → the tunnel gateway; TLS issued automatically by the gateway's Caddy) |225| Node | `M2M32c` (Mac Studio, Apple silicon), chosen by `mld pick`; application directory `~/apps/sheet` — a git clone, origin `gitsrv:srv/git/sheet.git` |226| Processes (PM2) | `sheet-web` (port 8700), `sheet-worker`, `sheet-backup` (cron 03:25 every day) |227| Database | PostgreSQL 17 on the node, database `sheet`, role `sheet_app` (password in `.env`); extensions `citext`, `pg_trgm`, `unaccent` created once by the superuser |228| Storage | `~/data/sheet/storage` |229| Backups | `~/backups/sheet` (same disk — see docs/BACKUP_RESTORE.md), passphrase `~/.config/sheet/backup-passphrase` |230| Secrets | `~/apps/sheet/.env` (mode 600), never in git; a private copy is kept by the owner outside the repository |231| Manifest | `M1M32:~/dispatch/apps/sheet.json` (processes, hooks, tunnel route) |232| Headless browser | `chromium-headless-shell`, installed by the deployment hook (`playwright-core install`) |233234Deployment 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.235236Update procedure on this installation (the node's clone is the source of truth):237238```bash239ssh M2M32c240cd ~/apps/sheet && git pull --ff-only241pnpm install --frozen-lockfile242node_modules/.bin/tsx scripts/migrate.ts243node_modules/.bin/next build244pm2 restart sheet-web sheet-worker245curl -fsS http://127.0.0.1:8700/api/health246```247248Rollback: `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. The application port (8700) listens on the node's LAN and tunnel interfaces; only the gateway forwards public traffic to it.249250Verified 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 (`claude-opus-5-5`) is available; the public pages pass the browser suite against the production address (see docs/TESTING.md).251252Not verified on this installation: enrolment and sign-in with a real passkey (the one-time link in `~/data/sheet/setup-url.txt` is for the owner), and therefore a Claude generation started from the production studio.253