Turning 47,000 iMessages Into a Personal CRM
Everyone I've ever texted is in a SQLite database on my Mac. Apple stores your
entire Messages history in chat.db, quietly, going back years. I decided
to mine it — not for nostalgia, but to answer a question that had been bugging me:
who am I losing touch with?
The build
A Python script reads chat.db (47,000 messages, in my case), joins it
against the macOS address book, and generates a markdown wiki: one page per person —
365 people, it turns out — with contact info, message counts, and when we last
talked. The index sorts everyone by last contact, which makes the whole thing a
gentle guilt engine: the people sliding toward the bottom are the friendships going
quiet.
The ugliest part: modern iMessages don't store text as plain text. The body lives
in a serialized Apple NSAttributedString blob (typedstream format), so a
chunk of the project was writing a decoder to get human-readable text back out.
There's something funny about needing a binary format archaeologist to read your own
text messages.
Why bother?
I got the idea from the "weak ties" research that people like Nick Gray talk about: most of the good surprises in life — opportunities, referrals, new friends — come through loose connections, and loose connections are exactly the ones that decay silently. My contacts app knows who I know. The CRM knows who I'm neglecting.
It's already earning its keep: I'm using the index to build the recipient list for a friends-and-family newsletter. Instead of staring at a blank page trying to remember everyone I like, I scroll a list sorted by exactly that.
Notes for anyone trying this
- Everything runs locally and the output is a private vault. None of this — the data or the output — belongs anywhere near a cloud service.
- Your terminal app needs Full Disk Access to read
chat.db. - Obsidian-compatible markdown is a great output format: links between people pages for free, and no app lock-in — it's just files.