BilboConsult

Architecture Notes

A database in a day — and seventeen days to keep it fixed

· 5 min read · Martin Kallenbach

Claude Code moved my ledger into a database in an afternoon. Keeping the data correct took until 2 July, when the rules that protect it moved into the database itself.

On the afternoon of 14 June I started a Claude Code session whose prompt included: “Work phase by phase; do not skip ahead. Treat correctness of financial data as the top priority.” Claude had written it earlier that day, with a build runbook (a step-by-step plan with checks). The first phase was committed at 15:45, the last at 17:56. By evening the household and business ledger lived in PostgreSQL, a relational database, and its spreadsheet was frozen.

Why a database

The spreadsheet could not say where a row came from or take two writers safely, and my new file service gave scripts no way to edit it.

On 12 June Claude had audited the ledger against the bank files and reported about 75 recurring payments missing. My journal records that I challenged it: the payments were in the raw bank files, every month. Claude’s own script had removed duplicates on the reference the bank gives every monthly debit of a standing order, so about 101 real bank rows vanished, and Claude retracted the findings the same day.

Built in an afternoon

The runbook had nine phases and four stops where Claude Code had to show proof and wait: a backup restored into a scratch database, the 2,679 copied rows checked against the spreadsheet, the first new rows, and the plan before the switch-over. It stopped at each.

Seventeen days of quiet regressions

Then the data quietly un-fixed itself. On 2 July Claude reconstructed 15 June to 1 July from the audit log, git and transcripts. One row’s receipt link had been rewritten ten times by at least six writers. On 1 July alone, 32 writers — scripts, jobs and Claude sessions, several at once — made 6,634 edits. One session cleared a link another had just repaired; 3,249 audit entries carried no name.

Built in an afternoon, fixed over seventeen days: the counts come from the reconstruction of 2 July.
Built in an afternoon, fixed over seventeen days: the counts come from the reconstruction of 2 July.

The reconstruction found six causes, among them the same fact stored in several places, bulk clean-ups on weak guesses, and fixes committed but not deployed. On 1 July bank import and receipt filing had each created a row for one purchase.

The rules move into the database

On 1 July I decided that only bank import may create a row; receipts attach to a bank row or wait. On 2 July the rest went into the database and the one write path every writer uses. The database rejects a write without the writer’s name and locks confirmed fields against automated writes; the write path grants a lease (a time-limited right to write) to one writer at a time and refuses a stale spreadsheet export. A daily integrity audit followed; its first run found real errors, cleared the same day.

Who may create a row: before 1 July, and from 2 July.
Who may create a row: before 1 July, and from 2 July.

The old spreadsheet was not quite frozen. On 13 July a session missing the database setting silently fell back to it and added nine rows. It caught this itself; the default changed that day. Since 2 September a guard in Claude Code refuses any write to the old sheet.

I had seen this before

On 19 April Claude planned moving my tasks from dashboard tables to one note per task, and I decided: big-bang, tonight. The overnight run reported success, but the rewritten task scanner existed only in the vault’s copy of the scripts; the deployed copy still read the old tables, and my task list stayed empty until 30 April.

The pattern

One system of record per kind of data, and the rules that protect it live in the database. The first half entered my decision log on build day; the second took until 2 July. A rule in a prompt holds only while every session reads it; a constraint in the database holds for every writer. When two automations can do the same job, give one the exclusive right to create. A migration is finished when every consumer has moved, not when the data has.

What is not solved: only the ledger refuses a bad write at the door; my tasks and notes are files, checked only afterwards.

Part 5 of twelve in the series on how I built my AI OS. Start with the preface.

Next: the green tick over nothing — checks that reported success while nothing was being checked.

Appendix — for builders

  • Write the build as a runbook with gates. Let the agent run routine phases; make it stop and show proof at irreversible ones, starting with a backup restored into a scratch database before the first write.
  • Freeze the old store in code, not with a banner. Point every default at the new store and fail when the setting is missing; on 13 July a banner did not stop a script.
  • Make every write carry a name and lock what a person confirmed, enforced by the database; let one writer in at a time. You cannot reconstruct what you cannot attribute.
  • Schedule an integrity audit, with a few “canary” rows (rows that broke before, expected state pinned) that fail it loudly.
  • Audit the audit tool. A de-duplication key is a claim about your data; check it against the source before acting.
  • What I considered instead: keeping the spreadsheet files and syncing their copies; a live link from the office suite to the database (deferred while a generated spreadsheet with my edits applied back suffices); moving health and building records too (deferred). I chose a database for the ledger alone because ledger, accounts, assets and budgets join, and because a database can refuse a bad write.

Also published on Kalli Family.