PRAGMA quick_check reported FTS5 corruption on a healthy file

If your agent keeps its memory in SQLite, you probably have a messages table, an FTS5 index beside it, sync triggers between the two, and a health check that runs PRAGMA quick_check on a timer. Mine did. Starting 2026-08-04 that check kept printing fts5: corruption found reading blob N, and I believed it for a month. Some of it was real. Most of it was the check tripping over its own snapshot.

The real part: the database was damaged four times on my dev machine. On 09-04 the base table was intact, all 78,017 rows readable, while the indexes and FTS5 shadow tables showed 57 “2nd reference to page” errors. The hard part was that the loud signal and the real damage came from two different mechanisms.

fts-audit.ts logs the statement, not the afternoon

What I shipped is small. In Vodou, the web gateway stores chat in a gateway_messages table with an external-content FTS5 index. MCP-servers/Vodou-Console/src/fts-audit.ts adds one call at every site that updates or deletes those rows. Trimmed:

export function auditFtsMutation(site: string, op: 'update' | 'delete', changes: number, db?: DbLike): void {
  if (!Number.isFinite(changes) || changes <= 0) return; // matched nothing, sent no 'delete'
  console.error(`[${stamp()}] [fts-audit] ${op} site=${site} rows=${changes}`);
  if (!checkAfterEachMutation || !db) return;            // opt-in, off by default
  db.prepare("INSERT INTO gateway_messages_fts(gateway_messages_fts) VALUES('integrity-check')").run();
  // on throw: "INDEX WENT BAD IMMEDIATELY AFTER: site=... op=... rows=..."
}

It logs the row count because that count is the exposure. An external-content index requires that the 'delete' command be handed the exact bytes that were indexed for that rowid, and one UPDATE over 3,000 rows sends 3,000 of those. With the opt-in check on, FTS5’s own integrity-check runs after every mutation, so “the index broke sometime today” becomes “this statement broke it.” That costs a full index scan per mutation, so it is only for reproduction runs.

Wiring it in exposed something I had missed. The update trigger fires on every UPDATE, including ones that only flip excluded_from_context or dedupe_key, and one function runs two unbounded UPDATEs that can touch thousands of rows at once, some older than the index.

Node 22 took the blame, then Node 24 still failed every ten minutes

My first diagnosis was the runtime. The gateway was running Node 22.22.3 although package.json required 24. The timeline ruled that out: two months of FTS5 on Node 22 with no corruption, then the first DELETE path on 07-27, the first UPDATE on 07-31, and the first corruption on 08-04. That made delete/update traffic the prime suspect.

On 09-04 at 19:15 I added a guard that allows only Node 24, and then nobody checked for twenty hours. When I looked: no file corruption, a clean full integrity_check, and 22 new handle-local failures, bad on the gateway’s connection and clean on a fresh one. Both Nodes bundle SQLite 3.51.3, so a version difference couldn’t explain it.

The failures came in bursts. From 01:49Z they were exactly ten minutes apart, which is the quick_check interval, so every tick failed. Then came 70 clean ticks in a row, then more failures. pmset ruled out sleep. Docker Desktop showed up in lsof holding the database, so I blamed Docker. I quit it, and failure #22 fired three minutes later. I took that one back.

4,441 mutations, and the prime suspect came back clean

Next I tried to reproduce it on throwaway databases, never the real file. I used the shipped schema and triggers, node:sqlite and WAL, and seeded rows before creating and rebuilding the FTS table, because that matches the real history. The traffic covered the six audited sites plus the ON DELETE CASCADE from the conversations table, which fires the delete trigger in bulk and wasn’t on my list.

That was 40 rounds and 4,441 mutations on each Node. quick_check, full integrity_check and FTS5 integrity-check came back ok every round, with 7,169 MATCH hits at the end on both. The two runs were identical line for line.

Then I forced the desync: drop the update trigger, rewrite every 7th row, restore the trigger, rerun. FTS5’s integrity-check went bad at once and stayed bad. quick_check stayed ok, and a fresh connection saw the damage too. That is the opposite of my symptom. A real desync is on disk, where every connection sees it. Mine existed on one handle.

47 of 57 ticks failed on a file with nothing wrong

The run that explained it used one long-lived connection running quick_check every 150ms while three separate processes wrote to the file. 47 of 57 ticks failed with the exact live message, across 33 distinct blob ids, bursts included. On that same handle, FTS5’s integrity-check and MATCH never failed once, and afterwards a fresh connection passed every check. The control ran the same traffic without an FTS5 table: 184 ticks, zero failures.

quick_check on a long-lived handle walks the FTS5 shadow b-trees while other connections are rewriting them. It reads a blob id its snapshot no longer describes and reports corruption on a healthy file. The problem is in the check, not the file, and the bursts line up with the hours when writers are busy.

The real damage came from two processes that exited cleanly

The file damage had another cause, one I had measured on 09-04 and never written down. Three live processes held -wal/-shm inodes 146979904/146979905, but the files on disk were 150851813/150851814. In WAL mode those sidecar files are only removed when the last connection closes, and none of those connections had closed. Something deleted and recreated the files underneath them. The live handle counted 803 freelist pages against the file’s 742: two separate WALs over one main file.

At that point the file was still clean. Fifteen minutes later, two of those processes exited normally and checkpointed their orphaned WAL into a main file that had moved on. integrity_check went from ok to 100 problem lines in that one step. The clean shutdown is what did the damage. A stranded process has to be killed with kill -9.

Three checks: the trigger, the sidecar inode, the second connection

Each of these is a property that is either true or false of your code. First: an external-content FTS5 update trigger fires only when an indexed column changed.

SELECT name, sql FROM sqlite_master
WHERE type = 'trigger' AND sql LIKE '%''delete''%';

Passing looks like AFTER UPDATE OF content ON messages, or a WHEN old.content IS NOT new.content clause. Failing looks like a bare AFTER UPDATE ON messages, which re-deletes every row your metadata jobs touch.

Second: every process that holds the -wal holds the inode that is on disk.

DB=/path/to/app.db
echo "on disk: $(stat -f %i "$DB-wal" 2>/dev/null || stat -c %i "$DB-wal")"
lsof -nP 2>/dev/null | awk -v w="$DB-wal" 'index($0, w) {print $1, "pid", $2, "inode", $8}'

Passing means every inode matches the one on disk. Failing means a process holds a different inode, or one marked (deleted) on Linux. Kill that process with -9. Do not send SIGTERM.

Third: a corruption report from a long-lived handle doesn’t count until a fresh connection agrees.

import sqlite3

def confirm(db_path, live_result):
    if live_result == "ok":
        return "ok"
    fresh = sqlite3.connect(db_path).execute("PRAGMA quick_check").fetchone()[0]
    return "corrupt" if fresh != "ok" else "handle-local (log it, do not alarm)"

If your health check pages someone on the first fts5: corruption found reading blob, it fails this check.

Agent memory write-ups assume the checker is honest

SQLite with WAL and FTS5 is a common local store for agent memory. The TencentDB agent memory design pairs it with sqlite-vec. Published FTS failure reports focus on write atomicity: Hermes Agent issue #72716 lost search permanently because executescript() issued an implicit COMMIT partway through a demote. mnema uses a contentless-delete FTS5 table, which avoids the byte-for-byte delete contract altogether.

None of that covers an instrument that is wrong, or sidecars pulled out from under a live connection. The New Stack notes that a hallucination often means the source never reached the context. When a keyword index silently loses postings, users see a model making things up, not an index problem.

Still open: what deletes -wal under a live connection

I don’t know what deleted those sidecars. The four historical corruptions happened before any instrumentation existed. And 4,441 clean mutations is absence of evidence, not proof the pattern is safe at 78,000 rows over months.

Your memory is a SQLite file on your machine, so its health check has to be honest

I tracked this bug so closely because the database it hit is where Vodou’s memory lives. Vodou is local-first: facts pulled from your chats and coding sessions go into a database on your own disk, and nothing leaves until you send it. That design also makes you the one who finds out when the file breaks. So after this incident, the gateway shows a banner that says outright when the database reports damage and new messages may not be saved. Before it, lost messages simply weren’t there. Handle-local artifacts are counted apart from real damage, so the warning still means something.

Keyword search is half of retrieval here. Vodou fuses vector and FTS results, runs a cross-encoder reranker over them, and applies a precision floor that injects nothing rather than noise. If postings quietly go missing, that pipeline gives your assistant less than you actually told it. That’s why this index gets a named-statement audit instead of a guess.

You never re-explain yourself, wherever you work. The same memory reaches Claude Code, Cursor and VS Code through MCP and hooks, and ChatGPT, Claude and Gemini in the browser through the Vodou Bridge extension. Proactive loops flag it when extraction or capture goes quiet, so a silent failure like this one reaches you before you notice the missing context. The MCP servers, skills and extension are open source, including fts-audit.ts, so you can read exactly what the instrument does and change it.

It also runs work while you’re away. This post went through that path. The notes behind it were mined from the memory of the build, then the draft was written, graded against a rubric, checked by a redaction gate so nothing private or proprietary gets out, and deployed on Vodou’s own scheduler. The investigation followed the same pattern: the next corruption, if one comes, will leave a named statement in a log instead of a month of guesses.

Vodou is for engineers who want their assistant’s memory in a file they own, with the failure modes visible. It runs on your machine, and every skill, server and schedule is yours to extend.

If you’re standing up a local memory store and want the index, the WAL and the health check already checked against each other, start at vodou.ai.