It Printed Output Every Single Run — That's Why Nobody Noticed It Was Missing 87%

An export script ran for two months. Every run produced output. Every output looked fine. It was missing 87% of the data.

I found it by accident. I was building a merged timeline of phone calls and text messages, which meant pulling message bodies out of macOS's chat.db. On the way I glanced at the SQL the old export script was using:

WHERE m.text IS NOT NULL AND length(m.text) > 0

Looks fine. Runs fine — prints the five most recent messages, full content, Chinese rendering correct.

But here's the truth: for the entire year 2023, the number of messages with a non-empty text column is zero.

Modern macOS writes message bodies into a NeXTSTEP binary column called attributedBody, not text. Only more recent messages still happen to carry text. And that script sorted descending by time and printed only the top 5 — landing precisely on the small subset that still had text.

Of 10,252 messages with a body, it could see 1,404. Missing 86.3%. Two months. Not one error.

This post is about the cleanup that started from that bug: cutting 12 dead data domains, deleting 7.5 GB, and auditing 6,900 markdown files from scratch. And one lesson that kept recurring — the default criterion is almost always wrong. You have to build the criterion before you cut.

---

1. The starting point: I didn't know what these things were for either

It started as a challenge: what are these projects even for? I have no idea what you're doing.

I had built 19 "personal data read-only windows" under ~/Apps/data/ — calendar, reminders, notes, contacts, photos, messages, mail, Safari history, music, podcasts, maps, stickies, health. One directory per domain, one dump script, one data/ output folder.

The audit was ugly:

Of the 19 domains, 12 had exactly one file in data/, dated the day the scaffold was built. Zero output in the two months since. Nothing — no skill, no cron, no script — ever called them.

Why? Because they lose to just opening the app.

"What have I been listening to lately" — open Music, one second. Writing a script to read its SQLite store is slower, more error-prone, and needs maintenance. The health domain has no database at all; stickies has two notes; maps has eight records; podcasts has three subscriptions.

There is exactly one situation where a script beats the app:

Cross-source + cross-year + structured question.

Something like "how many times has this person and I been in contact over the past three years, calls and texts combined." Calls live in the Phone app, texts in Messages; the two never join, and both can only be scrolled, not queried. The app can't do it. A script can.

So the direction wasn't to improve those 19 windows. It was to cut down to only what can answer cross-source questions, and turn those into indexes.

---

2. What got built

Two indexes and one shared foundation.

Cross-repo markdown index (md_index.py). My knowledge doesn't live in any note-taking app — zero Obsidian vaults on this machine; the only two real notebooks had been dormant for one to two years and are now deleted. The actual knowledge is scattered across 6,900 md files in 124 repos across 7 workspaces: proposals, PRDs, session handoffs, project docs, bid technical documents. SQLite FTS5 full-text index, 40-second full rebuild, search returns file:line plus the matching line verbatim.

Cross-source contact timeline (touchpoints.py). Call history + SMS/iMessage, normalized by phone number into a single timeline, then joined against the address book for names and organizations. 12,713 touchpoints spanning June 2023 to now.

iMessage body decoder (_imessage_body.py). The fix for the bug above. Extracted into a single implementation, because two consumers need it and two copies would drift. It ships with a 1,438-message ground-truth fixture — messages that have both columns populated — and passes 1,438/1,438.

One number I didn't expect: of 677 people in my address book, only 97 have ever appeared in a call or text. Everything else went through WeChat, whose database isn't in this pipeline. So this timeline is a "carrier-channel contact ledger," not a complete social record. Knowing where the boundary is matters more than the number itself.

---

3. Deleting an entire domain: measure first, then cut

During the cleanup I noticed PastePal (a clipboard history tool) had stopped running 26 hours earlier — no process, not in login items, source database last written the previous morning. It wasn't producing anymore.

Should the nine months of accumulated history go too? That's not a question to answer by feel. Measure it:

Half is noise, only 5.3% is irreplaceable, it's continuously accumulating secrets — and the source had already stopped.

Delete. The PastePal app, its 7.3 GB container, and its Application Scripts all went to the trash; a full-disk mdfind confirmed no residue. The 129 MB index domain went with it. 7.4 GB + 129 MB, all reversible.

Those 47 keys were an incidental find — unrelated to whether the index stays. They were sitting in the source database all along; nobody had ever looked.

---

4. Auditing 6,900 md files: the main output of dedup isn't finding duplicates

This is the counterintuitive part.

The request was simple: merge what overlaps, archive what's finished.

The first implementation took five lines — normalize the body, take SHA-256, identical hash means duplicate. Result:

459 groups, 1,134 files, 675 removable.

That number is toxic. Because for most of them, deletion is the damage:

• plugins/cache/claude-hud/{0.4.0 … 0.5.1}/TESTING.md — the package manager stores these per version; delete them and they come back on next install
• domain-skills/*/SKILL.md distributed into nine projects' .claude/skills/ — the copies are derived artifacts; you change the source
• The same official letter appearing under "Protection Zone I," "Protection Zone II," and "Phosphate Detergent Use" — this is a delivery-structure requirement of the drinking-water-source compliance assessment. Every checklist item must carry its supporting evidence. Delete them and the deliverable fails review.

So I rewrote the tool to be layered. Criteria applied top-down, first match wins:

Cross-repo double-writes in the live layer dropped from 156 groups to 4.

The tool's value isn't finding duplicates — that's five lines of code — it's keeping the untouchable ones out.

---

5. The interesting tier: duplicates that aren't byte-identical

Once byte-identical dedup hit zero, the follow-up came: "mostly it's about merging, or archiving some of them."

That's when I realized everything so far had been the surface layer. The same thing written two or three times, each edited a bit but substantially overlapping — hashing is completely blind to this, since one changed character makes them entirely different.

So I added near-duplicate detection. The first version used classic 128-permutation MinHash and didn't finish in 120 seconds — among 3,000 documents there are dozens over 100,000 characters, and each shingle needed 128 XOR-and-min operations. I switched to a bottom-k sketch (hash once, keep the K smallest), which estimates Jaccard just as well and runs two orders of magnitude faster. 40 seconds.

It found 238 highly-overlapping pairs (Jaccard ≥ 0.55). After verifying each one:

Confirmed legitimate, must not touch:

What did get archived: one-time migration intermediates (44 files), four processing stages of a completed bid (38 files, 95–99% overlapping), check reports from four runs within two hours (kept the latest), and a copy 2 directory left behind by a Finder slip.

All moved into an _archive/ inside their own project, with a README documenting the restore command — nothing left its project, nothing was deleted.

One design decision that matters: the whitelist is reported as its own section with counts, not silently filtered. Silent filtering makes the next person think detection is broken.

---

6. Three mistakes I made

This is what the post is really about. Everything above sounds confident. Along the way I was wrong three times, and each time it was the same class of error: I used an unverified ruler, then believed what it measured.

Mistake 1: the reconciliation script itself was wrong

To check the FTS5 index, I wrote a script using LIKE as a control. LIKE '%llm_client%' counted 224; FTS counted 221. I concluded FTS had a bug.

The bug was in my reconciliation script — in SQL, _ is a single-character wildcard, so llm-client and llm.client were being counted too. After escaping, six terms matched six for six.

If the ruler you're using as ground truth hasn't itself been verified, a mismatch only tells you one of the two sides is wrong. It does not tell you which.

Mistake 2: my own indexer was manufacturing fake duplicates

The audit reported 156 groups of "cross-repo double writes." One family was stations/docs/knowledge/session-retro-*.md matching Work/projects/*/docs/retros/ byte for byte. I wrote it up as real debt.

Those were symlinks.

os.walk(followlinks=False) only blocks symlinked directories. Symlinked files are still listed, and open() still follows them to the target's content. 157 files on this machine were exactly that, each becoming a phantom entry "byte-identical to its target."

Fixed by deduplicating on realpath, which also catches broken links. Both counts are printed in the build stats line — not silently swallowed. Index went from 6,899 to 6,742.

Two of the other "real debts" in that same batch were also false: one was a site's content/ directory that a prebuild hook syncs from the SSOT anyway; the other was the wiki's memory mirror, which the builder's own source code labels canonical mirror in plain text.

Of 156 groups, exactly 4 were real. My first reported number was 97% wrong.

Mistake 3: I nearly archived two real deliverables

Among the near-duplicates were three copies of a riverbank planning atlas, 93–95% overlapping. I judged two of them conversion leftovers and moved them into _archive/.

Before archiving, procedure says grep for references. The project's CLAUDE.md said:

| Planning atlas | 钱塘江/钱塘江岸线规划图册.{md,pdf} | md in git · pdf local-only |
| Report draft   | 钱塘江/qjtbg.{md,docx,pdf}       | md in git · docx/pdf local-only |

They are formal documents with companion docx/pdf files, not leftovers. Moved back immediately.

The criterion was hardened and written into the code comments: an .md with a same-named .docx/.pdf sibling is not a leftover — it's the markdown face of a real deliverable. Always grep for references before archiving.

---

7. What generalizes

Four things worth taking away.

① Output being non-empty is not the same as output being correct.

This is the thread running through everything. The messages bug missing 87%; ZANSWERED labeling 792 connected outgoing calls as "not connected" (the field asks "did I answer," which is always 0 when I'm the caller); 87 fake md files containing NUL bytes polluting the index (85 of them Neovim undo history). Three bugs, one shape: every run produced output, so nobody was suspicious.

The acceptance criterion has to be "test it with a window that should show you something": the earliest 400 records in the database, a two-column ground-truth fixture, term-by-term count reconciliation. Not "did it print anything."

How was the 87% bug confirmed? Take the earliest 400 messages in the database — old criterion shows 0, new criterion shows 395.

② Guards must fail closed, and you verify them in reverse the moment you write them.

When adding the sync step for the wiki mirror, I wrote two guards: source directory missing → error out; source directory present but zero md files → error out and never wipe the mirror.

Then immediately verified in reverse: point the source at a nonexistent path, point it at an empty directory, run both, confirm the real exit code is 2 and the mirror's 138 files are untouched.

A guard you haven't reverse-verified doesn't count — a silently broken guard belongs to the same category as the bug it was meant to catch.

The final "cross-repo double writes: 0 groups" claim got the same treatment: plant an identical file in two workspaces, confirm it immediately reports 1 group; remove them, confirm it returns to zero.

③ A classifier's value is in what it excludes, not what it finds.

Dedup is five lines. The hard part is deciding which duplicates are correct. And that decision cannot be made by feel — every whitelist entry needs evidence: read package.json to confirm prebuild syncs, read the builder source to confirm memory is a canonical mirror, read the project's CLAUDE.md to confirm those two md files have docx companions.

④ Criteria worth keeping go into the code, not into your head.

Every stumble above ended up as one regex plus a comment in the tool, explaining why this thing that looks like a duplicate isn't one. The next person to run it — including me in three months — doesn't have to re-derive it.

---

8. What it's for going forward

Directly: md_index.py turned 6,900 md files from "guess which repo, then grep" into one command that returns a file and a line number. It's the one output that saves time every day.

Methodologically: md_audit.py is now a re-runnable checkup — layering, near-duplicate detection, and the legitimate-family whitelist are all frozen into code. Six months from now, when the knowledge base has grown again, one command says what actually needs merging.

More generally: the valuable part of this round wasn't building. It was cutting and verifying.

• Built: two indexes
• Cut: 12 dead domains, one entire domain, 7.5 GB
• Verified: three silently-wrong data pipelines, one of which had been missing 87% for two months unnoticed

Of those three, verification paid the most — because a pipeline that looks green while being wrong is far more dangerous than one that's visibly broken. The broken one you fix. The green one you make decisions with.

If you take away one sentence:

Producing output every run doesn't mean it's right. Find a window that should show you something, and test it there.