EmDash's search index corrupts itself quietly. Here is the trigger and the repair.

On August 29, while making bulk edits to articles on sourcedpr.com, an ordinary UPDATE failed with this:

database disk image is malformed: SQLITE_CORRUPT (extended: SQLITE_CORRUPT_VTAB)

That is an alarming message. The data was fine. A SELECT returned all 29 articles, and the live site served 200 the whole time. The damage was confined to the search index.

How EmDash indexes content

EmDash stores content in Cloudflare D1, which is SQLite. For search it uses an FTS5 virtual table in external-content mode. The index does not keep its own copy of the text. When it needs to know what a row said, it reads the content table.

Triggers keep the index in sync. When an article changes, the update trigger is supposed to remove the old terms and add the new ones.

The trigger, and why it is wrong

The shipped update trigger runs AFTER UPDATE, and the first thing it does is this:

DELETE FROM fts WHERE rowid = OLD.rowid

With external content, FTS5 works out which terms to remove by re-reading the row from the content table. But the trigger fires after the update, so the content table already holds the new text. FTS5 removes terms based on the new text and leaves the old terms in place.

Nothing errors at that moment. The index drifts a little with every edit. Stale words keep matching articles that no longer contain them, and new text goes missing from results. Eventually a delete targets a term that is not there, and only then does SQLite throw. The error shows up long after the edits that caused it, on an unrelated write.

The correct form for external-content FTS5 passes the old values explicitly: insert the special 'delete' command with the OLD values, then insert the NEW values. The delete trigger has the same defect and needs the same fix.

The repair

Because these indexes are external-content, FTS5’s rebuild command can regenerate them from the content table safely:

INSERT INTO fts(fts) VALUES('rebuild')

We ran it on the three indexed tables. The articles rebuild reported 136 changes, and the database shrank from 1,945,600 to 1,765,376 bytes. That is about 180 KB of orphaned index terms, on a site with 29 articles. The blocked update then went through.

One caution if you try this yourself: rebuild is only safe because the tables are external-content. On a contentless index it is destructive. Check which kind you have before you run it.

The check that did not help

SQLite’s integrity-check ran clean immediately before that rebuild removed 180 KB of junk. So it cannot be the detector for this problem. If your only monitor is the database’s own health check, this bug is invisible to you.

Why this is bigger than one site

This was not caused by bulk SQL. Ordinary saves in the EmDash admin walk the same path, so any author editing an article degrades search a little. And every EmDash site has these triggers.

Our plan has three parts. Patch the triggers in the patch file the site already carries, because a fix applied only to the live database would be undone by the next EmDash migration. Report the bug upstream to the EmDash project. And add a cheap check: after any content write, the number of rows in the search index should equal the number of non-deleted content rows.

A side finding about that patch file

The patch file, patches/emdash@0.10.0.patch, had been labelled “obsolete” in our notes. It is not. It fixes cursor pagination and Astro v6 OAuth, and the OAuth fix is what makes Google sign-in to the admin work. It is load-bearing, and the record now says so. Labels in notes are claims too.

What this means if you hire us

When we build on EmDash and Cloudflare Workers, we treat the platform’s defaults as things to verify, not assume. A site that runs itself has to keep its own search accurate without anyone watching, which is why we count rows instead of trusting a health check. How it works shows where checks like this fit into an Atomic Wax build.