The empty database that looked perfectly healthy

Greenhouse, my creative-project manager, keeps its entire state in one SQLite file - state.db. Every project, every touch, every timestamp lives there. This week I gave it a backup system and a corruption-recovery screen, and along the way found a bug in my own fix that would have made the “recovery” feature actively dangerous.

The setup: no story for a corrupted database

The app already handled a corrupt settings.json (fall back to onboarding) and a vault folder that goes missing (a disconnected drive, say). But nothing covered state.db itself getting damaged - a power loss mid-write, or a cloud-sync client catching the file mid-transfer. Worst case: SQLite just opens the damaged file as if it were a fresh, empty database, and the app happily renders an empty dashboard. From the user’s side, that reads as “all my history is gone,” with no explanation and nothing offered to fix it.

The plan was straightforward: back up state.db once a day via VACUUM INTO (a built-in SQLite command that writes a consistent snapshot, safer than a raw file copy), run PRAGMA quick_check at vault activation, and route a failing check to a new recovery screen offering restore-from-backup.

The bug I almost shipped

I built the whole pipeline, wrote unit tests for every piece, watched 186 tests pass, and was ready to call it done. Then I ran a second-opinion review pass before wrapping up, out of habit more than suspicion.

The question that came back: what happens when state.db is truncated to exactly zero bytes?

I hadn’t tested that specific case. So I traced it by hand. SQLite treats an empty file as a brand-new, valid database - no corruption there as far as SQLite is concerned. My code would open it, write a fresh schema into it (the same migration step that runs on every legitimate first launch), and then run quick_check against that fresh schema. Which comes back clean. Every check I’d built would say “this vault is fine” - while quietly showing the user a wiped project history as if it were a fresh install.

And it gets worse: my code would then dutifully back up that empty database as today’s snapshot. Within a week, the pruning logic that keeps only the last 7 backups would have deleted every good backup that actually had the user’s data in it.

The fix: cross-reference something outside the database itself

An empty database can’t tell you, on its own, whether it’s supposed to be empty. The only way to know is to check something external. My fix: compare the live database’s item count against the most recent backup’s item count. If the backup has real projects in it but the live database has none, that’s not a fresh vault - that’s data that went missing. Route to the damaged-vault screen instead of the dashboard.

I wrote a test that reproduces the exact scenario - seed a vault, back it up, truncate the live file to zero bytes - and confirmed the fix catches it. Then, to make sure the test wasn’t just decorative, I temporarily deleted the fix and reran the test. It failed, exactly as it should. Then I put the fix back. That one habit - break the fix on purpose and watch the test catch it - is the only way I know to trust that a regression test is actually testing anything.

A tooling limitation that turned into a bigger lesson

Greenhouse has a WebDriver setup for driving the real, built app - genuinely useful for proving a fix works outside of mocks. I went to use it here and hit a wall: it boots straight past onboarding using a debug-only environment variable, and that shortcut skips the entire activation path I’d just built, corruption check and all. There was no way to reach the new recovery screen through it.

The tempting workaround was to point a real app instance at my machine’s actual saved settings file instead of the throwaway test vault. I got as far as writing that script before my own safety tooling stopped me - correctly. That path would have meant a live app instance pointed at my real vault, while a script sitting right next to it was busy truncating and corrupting database files to test recovery. One bug in my own restore-the-original-file logic, or a script crash at the wrong moment, and I’d be the one who needed the recovery screen I was building.

So I didn’t route around it. I ran a plain regression check instead - confirmed the ordinary capture flow still works after the changes - and leaned harder on the unit tests, which do exercise the real production functions directly, not a mock standing in for them. Then I wrote down exactly why the automation can’t reach this feature, so the next person (or the next me) doesn’t waste an afternoon rediscovering it.

What shipped

Daily backups via VACUUM INTO, named by date so a second launch the same day is automatically a no-op, keeping the last 7. A structural health check at vault activation, plus the cross-reference check for the empty-database case. A new recovery screen offering restore-from-backup, mirroring the app’s existing “vault went missing” screen. And the corrupted file is never deleted, even during a restore - it gets renamed aside instead, in keeping with the app’s whole “nothing ever gets deleted” philosophy.

The instinct to trust “all green” and move on is strong, especially after a long implementation session. The value of a second look, even briefly, before calling something finished keeps paying for itself.