Portal mutable state, from JSON files to SQLite (Phase 3a)

2026-04-07

​

Phase 3 of honest-cam is about getting the portal off a single long-lived Linux box and onto hardware I actually own — a Mac mini on my desk, backed up to object storage, so I can run local RAG against the same data the portal reads. None of that works while the portal's mutable state lives in plain JSON files pinned to one filesystem. Phase 3a is the unglamorous half: swap the sticky notes for a database without breaking the apply_events engine or the public loader surface above it.

What changed

A new packages/py/src/honestcam/db/ module, small and boring on purpose:

  • connection.py — per-path connection cache, opens SQLite with journal_mode=WAL and foreign_keys=ON, reuses the same connection across a request.
  • schema.py + migrations/property/*.sql + migrations/study/*.sql — numbered SQL migrations, applied via sqlite3.executescript() so each file commits atomically. No ORM, no Alembic.
  • compliance_repo.py — append-only writes to compliance_events. No updates, no deletes. Matches the event-sourced shape that apply_events already assumed.
  • study_repo.py — full-overwrite semantics per user. The standalone honestcam study CLI still writes to ~/.honestcam/cam-study-progress.json and is out of scope.
  • migrate_json.py — idempotent importer from legacy JSON to SQLite, archives sources to _legacy/{ts}/.
  • CLI: honestcam db-init <slug> (fresh) and honestcam db-migrate <slug> (import + archive). Both accept --dry-run and --execute.

The public surface held: compliance/loader.py still exposes load_compliance_log / save_compliance_log, so compliance_routes.py needed zero changes. The apply_events engine and its _apply_* helpers are 100% untouched.

Schema shape

Two databases, not one:

  • Per-property: data/{slug}/honestcam.db, owns compliance_events for that property. Append-only. Deleting a property is rm -rf data/{slug}/.
  • Global: <data_dir>/honestcam-study.db, owns study_users and study_progress across all properties. Full-overwrite on save because that's how progress worked under the JSON files — small blobs, last write wins.

Both run in WAL mode. The portal serves concurrent reads under an active writer without blocking, and a crash mid-write leaves a recoverable journal instead of a truncated file. That last property is load-bearing: my previous "oops the power blipped" recovery plan for the JSON files was "hope fcntl did its job."

The migration playbook

Bamboo House went first on 2026-04-07. The playbook I ran, end to end:

# 1. Dry run to see what would move.
HONESTCAM_DATA_DIR=… honestcam db-migrate bamboo-house --dry-run
 
# 2. Execute. Writes SQLite, archives JSON to _legacy/{ts}/.
HONESTCAM_DATA_DIR=… honestcam db-migrate bamboo-house --execute
 
# 3. Re-run to prove idempotency. This must be a no-op.
HONESTCAM_DATA_DIR=… honestcam db-migrate bamboo-house --execute

Counts: 3 study users, 3 progress files, 0 compliance events (portal hadn't mutated Bamboo compliance yet — expected, since that property is still in the ingest-only phase). Legacy JSON archived to _legacy/20260407T143650/. Second --execute reported zero work, which is the only acceptable outcome for a migration command you might ever run twice.

The rollback drill

I don't trust a migration I haven't reversed:

  1. Move the new honestcam.db aside.
  2. Restore the JSON from _legacy/20260407T143650/.
  3. Hit the portal and let quiz.load_progress read the restored JSON.

All three steps round-tripped cleanly. load_progress still knows how to read the JSON shape because that reader was never removed — only the writer path now goes through study_repo. That's the backout plan if SQLite surfaces a bug I didn't catch: move the DB aside, drop the JSON back, restart the portal, nothing else.

Verification

  • pytest — 660/660 pass (was 647, +13 new repo tests).
  • ruff check . — clean.
  • db-migrate bamboo-house --dry-run then --execute reported the correct counts.
  • Portal smoke test under SQLite: GET /study/, GET /compliance/, GET /study/admin?key=… all returned 200; a POST /compliance/actions/ACT-001/status landed a row in compliance_events and survived a restart.
  • Idempotency: a second --execute was a no-op.
  • Rollback drill: SQLite moved aside, JSON restored, quiz.load_progress read them without fuss.

What this unblocks

With mutable state in portable files, Phase 3b (/api/* split away from the Astro render pipeline) and Phase 3c (Mac mini + Cloudflare Tunnel + Access, with Litestream continuously replicating the SQLite WAL to Cloudflare R2) can both land in their own PRs. Phase 3c is the part that actually matters — the whole reason I'm doing any of this is to host the portal on my own hardware so the second-brain RAG work can run against the same data on the same box. More on that in the next post.

PR: https://github.com/StevieIsmagic/honest-cam/pull/16