A backup you have not reopened and counted is not a backup

I run a database of Vietnamese bank interest rates that updates every day. The most important table in it is the daily rate schedule table. It records each bank's rate schedule for each day, append only, history untouched. Yesterday's schedule has already left every bank's own site. Lose that table and it is gone for good; no amount of re-scraping brings it back. So the backup is the main job. And I broke it in the quietest way available.

Three nights of logs saying done, with the real database left out

On 9 August 2026 I moved the database from a local SQLite file to Turso. The config variable for the local replica was left blank, so the old database file froze that same day. The nightly job kept its old line: copy that very file into the backup folder, then log "database backed up".

Three nights running it copied the same dead file. Measured on 11 August 2026:

Table	Live database in the cloud	The file being "backed up"
Daily rate schedules	12,282	9,027
Social signals	2,449	2,029

There was a log, there was a file, there was a feeling of safety. The real database was outside all three. I call this a false success: every command ran to the end, the log was clean, the result was wrong.

The day before, on 10 August 2026, the file sync service on my machine replaced the database: a 5.5 MB file came back as 53 KB with every table empty. It was saved by an accidental copy taken at 01:05 plus 88 raw article files. A mechanism had to replace the luck. Two incidents in three days was enough for me to rewrite the backup program from scratch, with three blocking gates. Each gate comes from something that actually happened.

Gate 1: read the live database directly

The new program drops the file copy step entirely. It opens a connection to whatever database is live, Turso or local SQLite, reads each table, and writes a fresh SQLite file. The manifest that travels with it records which kind of database was read, so whoever opens the backup knows where it came from.

Turso returns results over the network in chunks. Reading a whole large table in a single query breaks the stream partway through. On the night of 21 August 2026 the backup died exactly that way, and the most recent good copy was 5 days old by then. The failure gets worse as the database grows, so the cure had to sit in how it reads. I switched to batches of 2,000 rows by row number, retrying a broken batch once.

Gate 2: if the database shrank, stop

Three tables in the database are append only: daily rate schedules, social signals, and the scrape run log. Their row counts can only go up. Before writing a new backup, the program reads the previous manifest and compares table by table. If the new copy has fewer rows in any of those three, it prints which table lost how many rows and stops before writing.

A shrinking database has three explanations: the database was replaced, as on 10 August; somebody deleted rows by mistake; or the program is reading a different database from yesterday's. All three need a person to look before the old copy is overwritten. If the shrink really is intended, rerun with a flag saying I know and I still want to write. That flag exists so that skipping the gate is always a decision with a signature on it.

Because the gate counts rows, everything else in the program has to leave row counts intact. Any copy leaving the machine has to drop personal data, so I mask columns and keep every row. Deleting rows there would blindfold this gate with my own hands.

Gate 3: after writing, reopen and count

After compressing and writing the file, the program reopens the copy it just wrote and does five things: the manifest must exist; the file's sha256 must match the manifest; unpack into a temp folder; run SQLite's integrity check; count every table and compare against the manifest. One row off and it deletes both the file and the manifest, then reports an error. A backup is a file that has been reopened.

This gate caught its first bug inside the program itself. The helper I used to derive the manifest name replaced only the last file extension, so it went looking for a name that did not exist. The first backup destroyed itself right after being written, for want of a manifest. Annoying, and correct: the gate did its job before the program did its own.

The manifest records the timestamp, the database type, per-table row counts, the compressed size and the checksum. A separate flag reruns that same verification against the most recent copy at any time. The program keeps the 14 most recent copies plus the oldest one, because a fault found late needs a reference point further back than two weeks.

The daily run calls the backup before it writes anything to the database, in the mode that carries no personal data: drop the table holding phone numbers readers left, and mask the source link column with a string deliberately shaped not to look like a web address. Masked with a string rather than left blank, because that column is declared NOT NULL and an extract is only a backup if it loads back in. The upload step accepts masked files only; if no matching file is there, that step has to go red. The handbook says keep for 90 days, the actual config is set to 30, with a note: the published policy promises to delete personal data after 90 days, so holding the extract for a full 90 days would set the copy's expiry equal to the original's.

A second false success: the site served 16 banks while the database held 29

The system went green and wrong in one more place. The daily run has step 6, export per-page data out of the database, and step 7, build the static site. Step 7 reads the files just exported instead of reading the database directly. Skip step 6 and you rebuild the site on stale data, with everything still reporting success. It happened: the site served a 16 bank version while the database already held 29.

Same family as the backup. Every step went green on its own test. The question that mattered stayed unasked: does what we just produced match the database. The cure is from the same family too: write the required order into the operations handbook, and put a counting step on the output instead of trusting exit codes.

What I took from it. A log line saying done proves only that the command ran to the end; whether the work is done has to be measured at the output. Put the gate on the output and compare against the source of truth, not against the thing that just produced it. Tie every gate to an incident with a date, because a gate with an incident behind it survives the day somebody wants to remove it. And a backup worth trusting is one that has been reopened and counted.

If you are running a nightly system whose backups have never been reopened, tell me the context.