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.
database disk image is malformed is SQLITECORRUPT (11): page-level damage inside a file that is still recognisably SQLite.
file is not a database is SQLITENOTADB (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.
integritycheck returns ok, or a list of problems. It stops after 100 of them, so a long list means "lots", not "exactly 100". quickcheck runs the same test minus the table-versus-index comparison. It's fast and it can miss damage. And PRAGMA foreignkeycheck 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.
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: 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. 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. 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. 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. 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 busytimeout, you're in that territory, and it's worth collecting a repro instead of re-reading your own code.
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. Stop every writer. Kill the app, the gateway, the CLI. If a process still holds the file, everything below is theatre. 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. Dump and reload into a new file:
.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 lostandfound table. Validate before you swap. Run PRAGMA integritycheck; and PRAGMA foreignkeycheck; against new.db, then REINDEX;. Keep the old file around until you're sure. Count what you lost. Rows on damaged pages are gone. Uncommitted WAL transactions are gone. Check the lostandfound 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.
Set PRAGMA journalmode=WAL;, PRAGMA synchronous=NORMAL;, PRAGMA busytimeout=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.
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.