Seventeen years of archives, seven formats, one queryable corpus

Context

Data-portability rules mean you can request an export from almost any platform. What arrives is seven different answers to the same question: a folder of plain-text files, several flavours of JSON with incompatible schemas, and a mail archive in a format designed in the 1980s. Each has its own idea of what a timestamp is, what a conversation is, and who a participant is.

I wanted my own archive queryable as one thing rather than seven. The data is entirely my own, it never leaves the machine, and the outputs stay private — so the interesting part of this project, and the only part described here, is the pipeline.

What I built

• A parser per source format, each normalising to one message schema: plain-text chat exports, two different platform JSON dialects, mbox mail archives, and a social-platform archive with its own structure again.
• Around forty small Python programs rather than one large one, each answering a single question and each independently re-runnable, so a fault in one analysis never forces a full re-ingest.
• A storage split matched to access pattern: newline-delimited JSON for the corpora that get streamed and filtered, and a SQLite database for the mail archive, where attachment indexing and joins made queries the dominant cost.
• Cross-platform relationship linking, which is the genuinely hard part — the same person appears under a display name here, a phone number there, and an email address somewhere else, with no shared key anywhere.
• A runbook written for a stranger: ingest order, what each script consumes and produces, the pitfalls, and what to do when a new export arrives. It exists because I have been on the wrong end of a pipeline whose only documentation was the person who wrote it.

The bug worth writing down

One platform serves UTF-8 content labelled as Latin-1. Parse the export the way it describes itself and every Latin character survives — so the import looks successful, the message counts are right, and nothing errors. Every Arabic character is silently replaced with mojibake.

Roughly half my archive is Arabic. Had that gone unnoticed, the corpus would have been quietly half-destroyed while reporting a clean ingest, and every downstream analysis would have been computed on the surviving half without ever saying so. The fix is three lines. Finding it required not trusting a successful-looking run — the same failure mode I now build gates for by default.

Auditing my own conclusions

Once the corpus existed, it was easy to produce confident-sounding findings from it. So I ran a deliberate second pass whose only job was to attack the first: challenge the method, name what the data could not support, and separate what was measured from what was inferred. It forced the retraction of a conclusion I had been fairly attached to.

A dataset large enough to be interesting is large enough to confirm almost anything you go in believing. Building the adversarial pass as a deliverable, rather than relying on being rigorous in the moment, is the only version of this I trust.

Where this transfers

Consolidating messy multi-source data into one usable store is a substantial share of real operations and analytics work: systems that disagree on identifiers, exports with undocumented quirks, and the standing risk of a migration that appears to succeed while dropping a category of record nobody happened to check. I have now done that end to end, including the part where the encoding was wrong and nothing complained.