# Operations runbook — Note SPBoucher AI

Data ownership, import/export, backups and restore. Node: M4M64c (~/apps/note-spboucher-ai, PostgreSQL 17 via Homebrew /opt/homebrew/opt/postgresql@17/bin, uploads in $DATA_DIR/uploads), cold copy on M3U96b (~/backups/note-spboucher-ai, reached through the m3u96b-backup SSH alias configured on M4M64c).

# 1. Backups

# Manual backup

# on M4M64c (same environment the worker uses)
cd ~/apps/note-spboucher-ai && set -a && source .env && set +a
bash scripts/backup.sh                # full run: archive, sha256, prune, rsync to M3U96b, remote sha256 check
bash scripts/backup.sh --dry-run      # tools/env/database/offsite reachability only, no archive
bash scripts/backup.sh --no-offsite   # keep it local (e.g. M3U96b unreachable)

The script prints one JSON line on stdout ({"path","size","sha256","offsite","durationSec","warnings":[]}) and logs on stderr; exit status ≠ 0 on any failure (pg_dump, tar, openssl, rsync or remote checksum). From the app: Settings → Data → Run backup now (enqueues the same script through the worker). POST /api/backups {"action":"verify","id":…} re-hashes the local archive and compares it with the recorded sha256 and the .sha256 sidecar.

From the laptop (tunnel 127.0.0.1:15432, Homebrew pg_dump 17): BACKUP_PASSPHRASE=… BACKUP_DIR=/tmp/note-backups DATA_DIR=./data bash scripts/backup.sh — fine for a one-off copy of the database, but uploads are only complete on the node.

# Restore

scripts/restore.sh takes an .enc archive and the passphrase ($BACKUP_PASSPHRASE, or --passphrase-env NAME), checks the .sha256 sidecar, decrypts, unpacks, then:

cd ~/apps/note-spboucher-ai && set -a && source .env && set +a
pm2 stop note-web note-worker                     # nothing must write during the restore
bash scripts/restore.sh --latest --yes           # pg_restore --clean --if-exists into $DATABASE_URL + uploads into $DATA_DIR
bash scripts/restore.sh ~/backups/note-spboucher-ai/note-20261101T083000Z.tar.enc --yes --no-uploads   # database only
bash scripts/restore.sh <file.enc> --target-db postgres://note:…@127.0.0.1:5432/note_scratch            # into another database
pm2 start note-web note-worker

Flags: --latest (newest in $BACKUP_DIR), --no-db, --no-uploads, --data-dir DIR, --dry-run (integrity check only). Without --yes (and without --target-db) the script asks you to type RESTORE. pg_restore errors are counted and printed (restoreErrors in the JSON summary). After a restore, search chunks/embeddings are part of the dump; if the archive is older than the uploads on disk, nothing is deleted from data/uploads (files are content-addressed by sha256).

Recovering on a fresh Mac: install postgresql@17 + pgvector (Homebrew), create role note + database note_spboucher (CREATE EXTENSION vector, pg_trgm, unaccent), clone the app, copy .env from the secrets folder, pnpm install, scripts/restore.sh <file.enc> --yes, pnpm db:migrate (no-op if the dump already carries the schema), pm2 start.

# Restore test (do this monthly and after any PostgreSQL upgrade)

bash scripts/restore.sh --latest --verify-only
# → {"verified":true,"file":"…","pages":N,"tables":35,"attachments":M,"uploadFiles":K,"restoreErrors":0}

--verify-only restores into a scratch database note_restore_test, counts pages/tables/attachment rows, lists the uploads in the tar, then drops the scratch database — production is never touched. The scratch database needs an admin connection: $RESTORE_ADMIN_URL if set, else $DATABASE_URL with database postgres (the note role has no CREATEDB, so this usually fails), else the local Unix socket (the Homebrew superuser = the macOS user; this is what works on M4M64c and on the laptop). Verified on 2026-11-01 on both the laptop (through the tunnel + local Homebrew PG) and M4M64c: 35 tables, 2 pages, 0 errors.

# Monitoring

• Settings → Data → Backups: last successful run, size, off-site badge, errors. A failed nightly run leaves a failed row with the script's last stderr lines.
• psql "$DATABASE_URL" -c "select status, started_at, size_bytes, offsite, left(error,80) from backup_jobs order by started_at desc limit 7"
• Off-site listing: ssh M3U96b 'ls -la ~/backups/note-spboucher-ai | tail'
• Worker log: pm2 logs note-worker (lines backup <id> ok … / FAIL …).

# 2. Export (Settings → Data, page menu, or GET /api/export?format=…)

# 3. Import (Settings → Data dropzone, or POST /api/import multipart file, optional kind, parentId, sync=1)

Kind is detected from the extension/MIME. Files ≤ 2 MB of kind markdown/csv/bibtex/docx/pdf are imported in the request (201 { done:true, pageId, stats }); everything else (ZIPs, JSON backups, big files) is written to $DATA_DIR/imports/<jobId>-<name> and queued (202 { jobId }) for the worker; GET /api/import?jobId= returns { status: queued|running|done|error, progress, message, pageId, stats, error } (stored in settings under import.<jobId>). The UI polls every 1.5 s.

Limits: 500 MB per upload (/api/import), 200 MB per attachment elsewhere. Everything imported is audited (import, import.queued) and indexed asynchronously by the index-source queue.

# 4. Where things live