Your agent's SQLite state DB keeps corrupting: what actually causes it, and how to recover the data

I run an agent that keeps its entire brain in one local SQLite file. Three weeks ago it came back with database disk image is malformed, and I did the first thing most of us do: deleted the -shm sidecar, re-ran the repair, moved on. It came back about twenty hours later, same table, same error.

So I finally went and read SQLite's own list of ways you can corrupt a database file. It's a good list. Nearly everything on it is something we do to the database, not something the database does to itself.

Here is the order I wish I had done this in.

Read the error properly first

Two different messages get called "corruption" and they are not the same thing.

database disk image is malformed is SQLITE_CORRUPT (11): page-level damage inside a file that is still recognisably SQLite.

file is not a database is SQLITE_NOTADB (26): the header isn't SQLite at all, so it's the wrong path, a zero-length file, or something else wrote over it.

If you only read one error, read the first one. By the time you reach the last error in a stack trace, the real cause is usually several layers down.

Check the file before you touch it

sqlite3 state.db "PRAGMA integrity_check;"
sqlite3 state.db "PRAGMA quick_check;"
sqlite3 state.db ".dbinfo"

integrity_check returns ok, or a list of problems. It stops after 100 of them, so a long list means "lots", not "exactly 100". quick_check runs the same test minus the table-versus-index comparison. It's fast and it can miss damage. And PRAGMA foreign_key_check is not a corruption test at all, it only reports FK violations.

If the output mentions ptrmap, that's the pointer map that autovacuum keeps, and it now disagrees with what is actually stored on the pages. That is structural damage from a stray, short or duplicated write, which in practice usually means two writers on one file. It is not an index logic bug, and no amount of REINDEX will clear it.

What actually causes it

SQLite keeps a canonical list at https://www.sqlite.org/howtocorrupt.html. Ranked by how often I've seen each one show up in agent and desktop app trackers:

1. Deleting or renaming -wal / -shm while something still has the database open. The -shm is the wal-index, the shared memory that coordinates readers and writers. Unlink it under a live connection and that coordination is gone. This is the one I did.

2. Putting the state file on a network or synced folder. NFS, SMB, OneDrive, Dropbox, iCloud. SQLite's locking is advisory and assumes a real local filesystem with working locks. A sync client rewriting pages underneath it is a corruption machine.

3. Two processes writing the same file without agreeing on locking. A gateway plus a CLI plus a second server pointed at one state directory. The app should own one writer, or funnel everything through a single process. That's an application bug, not a SQLite one.

4. Copying a live database with a file copy instead of the backup API or VACUUM INTO. Same category: deleting a hot journal, or restoring a backup while a transaction is still open.

5. PRAGMA synchronous=OFF, or a disk that reports a write as synced when it isn't.

Real SQLite bugs do exist, but they are narrow and old: a WAL race writing to a WAL-mode database (§8.1), and corruption after switching between rollback and WAL mode with a VACUUM in between (§8.4). If your database corrupts on a local disk, with one writer, no sidecar fiddling and a sane busy_timeout, you're in that territory, and it's worth collecting a repro instead of re-reading your own code.

Recovering the data

There is no in-place repair. Anyone telling you to "fix" the file is guessing. The steps below are what worked for me on an agent that had two processes pointed at the same state directory, and I can't promise they map one-to-one onto your setup, but there's nothing destructive in them. Try them before you delete anything.

1. Stop every writer. Kill the app, the gateway, the CLI. If a process still holds the file, everything below is theatre.

2. Copy the whole set to scratch: state.db plus state.db-wal plus state.db-shm. Copying the .db on its own throws away committed data that only lives in the WAL, and it can make a healthy database look corrupt.

3. Dump and reload into a new file:
   

sqlite3 corrupt.db ".recover" | sqlite3 new.db

.dump stops at the first sign of corruption. .recover keeps going, reassembles what it can from the surviving pages, and parks rows it cannot attribute into a lost_and_found table.

1. Validate before you swap. Run PRAGMA integrity_check; and PRAGMA foreign_key_check; against new.db, then REINDEX;. Keep the old file around until you're sure.

2. Count what you lost. Rows on damaged pages are gone. Uncommitted WAL transactions are gone. Check the lost_and_found row count, and diff the schema against what you expect, because a table whose root page went missing can disappear silently rather than error out.

Stopping it from coming back

Set PRAGMA journal_mode=WAL;, PRAGMA synchronous=NORMAL;, PRAGMA busy_timeout=5000; and actually handle SQLITE_BUSY instead of swallowing it. Ignoring busy is on SQLite's own corruption list.

Never delete or rename -wal or -shm while any connection is open. The checkpoint and the cleanup happen when the last connection closes. Let it happen.

Keep one writer per state directory, enforced with a lock file if the app can't guarantee it, or do all the writing in one process.

Keep the state directory out of OneDrive, Dropbox, network shares, and aggressive antivirus realtime scanning. Exclude it explicitly rather than hoping.

Don't churn journal modes, and don't schedule VACUUM against a live, high-churn database. Back up with VACUUM INTO backup.db or the backup API, nightly, before you need it.

The honest part

I'm not going to pretend this explains every case. If a freshly created database corrupts on local disk with one writer, one process and no sidecar deletion, that's a genuine bug and it deserves a repro with the file attached, not a checklist. Watch for one more trap there: VACUUM INTO is transactional for the source, but an unplanned shutdown can still leave the output file incomplete. Validate the backup you just made, don't assume it.

The rest of it is mostly ours. The file was fine, and we kept pulling the rug out from under it. One writer, local disk, hands off the sidecars, and a backup you have actually restored at least once. That's how I run it now, and the malformed errors stopped.

Comments

[mthburnsbarber-web]

"We kept pulling the rug out from under it" — that's the real summary. The corruption list from SQLite's own docs is humbling because almost every item is something the application did, not a SQLite bug.

The -shm deletion point is the one that bites the most. Deleting the wal-index while a connection is still open and then being surprised at corruption is like yanking the page table out from under a running process. The sidecar file isn't metadata you can clean up between runs — it's active coordination state.

The network/synced folder point deserves its own warning label for agent state files. We build automation stacks at Black Label and the number of times a client had state in a Dropbox folder and couldn't figure out why reads were randomly stale is significant. SQLite's locking assumes a POSIX local filesystem; a sync client that rewrites pages to reconcile conflicts is fundamentally incompatible with that assumption.

The .recover vs .dump distinction is useful to have written up — I've seen people reach for .dump first and lose rows they could have gotten back.