sqlite

A rebuild turned 20 healthy ledger rows into 77 UNKNOWN. The fix: a recovery-only contract that fills gaps and never rewrites healthy rows.

TL;DR: A rebuild tool corrupted a court-rulings ledger, inflating twenty healthy rows to seventy-seven, mostly tagged UNKNOWN. The cause was structural: per-tab raw files re-parsed as new records, proving raw-is-truth doesn't make re-parses safe. The redesign makes rebuild recovery-only: healthy rows stay verbatim, only missing cases are appended, sub-tab files are skipped, and any UNKNOWN aborts all writes.

Two local guards for scraping pipelines: an AGENTS.md rulebook for coding agents and a per-target SQLite audit log made idempotent with UPSERT.

TL;DR: When a harvest run died, git couldn't trace which commit produced the data—it records file changes, not execution context. The fix was two guards: an AGENTS.md rulebook forbidding writes to harvested data and secrets in commits, plus a per-target SQLite audit log. UPSERT on a unique commit-case-tab key keeps re-runs idempotent, turning provenance recovery into a single query.

A backfilled next_review went stale in three days. The fix: compute derived fields at read time and keep manual overrides.

TL;DR: A hand-run backfill for next_review went stale the moment one new file hit the index, exposing it as a derived field that shouldn't be stored. The real fix computes it at read time, only filling blanks so manual overrides survive. SQLite and PostgreSQL settled this long ago: derive on read, store only for overrides or heavy read performance.

Copying an active SQLite file is not a backup. Lessons from a light backup script: hot backup, verified atomic tar, and layered pruning.

TL;DR: The script backs up SQLite databases using the official .backup API instead of raw cp, since copying active WAL files can yield corrupt snapshots. Archives are staged, verified with tar, then atomically renamed, with flock preventing overlap. Pruning keeps 14 days plus a floor of 3 newest archives, so failures can't silently destroy valid backups.