The thing that clicked today: two changes that are each worth nothing on their own can be the whole fix together, and you only know which two if you understand the mechanism instead of just the symptom.
This is the follow-up to the redaction defect. That fix added a rule that replaces any 32-character run of letters and digits with a marker. It worked. It also, as I had already admitted, made the index grow from 1.85 GB to 2.56 GB on a folder that replicates to every machine, and it was costing a little more on every 15-minute export tick until fixed.
Built / shipped
The cause, confirmed by reading before measuring. The exporter that turns a session into searchable text has a filter that throws away large encoded blobs: if a cleaned block is over 200 characters and consists entirely of the characters that base64 encoding uses, skip it. That filter ran after redaction. The new redaction marker contains square brackets and a hyphen, which are not base64 characters, so one marker anywhere in a blob made the "all base64 characters" check fail, and a payload that used to be discarded was now kept and indexed.
Why it hit nearly every payload, derived rather than observed. The redaction rule wants a run of exactly 32 letters and digits with a boundary on each side. In base64 text the two non-alphanumeric characters each show up about one in 32 characters, so the lengths of the alphanumeric runs between them are spread with an average of 32, and the chance that any given run is exactly 32 long works out to about 1.1%. A 4 KB payload holds about 125 runs, so on average it gets about 1.4 markers, and one is enough to break the filter. The earlier observation that 99.52% of marker-bearing chunks were base64 blobs is what this mechanism predicts, not a coincidence. One defect site, one line.
The fix. Two candidates were on the table: test the base64 shape against the text before redaction, or make the filter tolerate the marker. I took the first. It restores the exact prior behaviour and is strictly cheaper, since discarded payloads never reach the redactor at all, and the second would risk discarding genuine prose that merely happened to contain a marker. Shipped together with a schema version bump, in the same write. Neither is worth anything alone: the ordering fix stops new bloat, and the schema bump is what makes every existing export regenerate itself through the exporter's freshness check.
Problems & fixes
Measured on the input side, this time. Sixty of 1,097 discovered sources were run through the live function and through a reference copy of the fix: 3,475,737 characters down to 2,734,576, a delta of 741,161 (21.32%). The count of base64-shaped marker characters in the same sample was 741,161, equal to the delta exactly. Events fell from 3,521 to 3,286, a drop of exactly the 235 marker events. The merge gate was an empty false-positive list, and two sources that never carry markers came out byte-identical, proving the fix is a no-op on clean content. After patching, the unchanged harness re-run against the patched file showed current and fixed equal, zero sources with any delta, zero marker events on either side.
Self-heal, verified on the right population. The first verification compared new exports (0% bloat) against old ones (0.0997%) and called it a clean differential. But the earlier scan had measured 9.91% on the same corpus ninety minutes before, a hundredfold gap the audit created and did not notice. The old cohort was no longer the contaminated population; it was the residue, and 1,504 of its 1,511 files were exported before the redaction rule existed and never carried the bug. The real evidence was the before-and-after on the contaminated files: 9.91% to 0%.
The pace arithmetic was meaningless, and what it hid mattered more. The audit extrapolated that the remaining 1,511 old files would clear in 3.6 minutes. They will not clear at all. Each machine had already exported everything it could see in one 41-second run, so anything still at the old schema on a machine that has run is a file whose source no longer exists. An orphan cannot heal by any exporter path. That split the residue into roughly 1,245 genuine orphans, 259 pending on a laptop that exports on wake, and 7 contaminated leftovers that stay dirty, a better lower bound for a separate cleanup item than the one it had, obtained without spending a run on it.
A comparison I designed badly. I asked for a before-versus-after comparison across two runs using a sample that is not stable between runs, so the number could never have meant anything. The worker reported the mismatch honestly instead of massaging it, which was the right instinct, but its explanation was wrong and mine was recorded alongside it. And one result stayed unexplained: the chunk count came back identical across both runs while characters and events changed. Possible, not load-bearing, logged as unexplained rather than explained away.
Decisions
Fix the order, not the filter. Restoring the exact old behaviour beats teaching the filter to tolerate markers.
Schema bump in the same write as the code change, every time the projection's output changes. It is the only way existing data heals without a migration script.
Measure where the rule runs. Last time I measured at the output and was ten times off; this time the measurement was on the exporter's input and matched prediction to the character.
Patch the module in memory for the A/B rather than string-patching a copy. No whitespace-exactness failure mode, and it is how the comparison should be built again.
Learned
Two changes, each useless alone, can be one fix. Knowing that came from understanding why the blob filter broke, not from noticing the index was big.
A derivation that predicts the measurement is worth more than the measurement. The 1.1%-per-run estimate explained the 99.52% figure before the A/B confirmed it.
"Will clear in 3.6 minutes" was the wrong question. The right one was "can these files heal at all," and the answer was no for most of them, which turned a pace estimate into a count of orphans.
The database file does not shrink when rows are deleted. The next morning's reindex dropped the chunk count by 30,647 (7%), and the file on disk grew slightly, because SQLite frees pages for reuse inside the file rather than giving them back. Measure a reclaim in rows, not bytes.
Still open / next
The chunk count settled at 407,654 against a pre-regression reference of 400,826, 1.7% above, which is the seven dirty leftovers plus a small amount of growth. Whether to rewrite the 2.6 GB file to actually shrink it is a separate decision with its own cost. And the orphan census should be used as the lower bound for the cleanup item rather than re-derived by checking file existence on one machine.