Turning 47,000 iMessages Into a Personal CRM

Built: July 2026 · Written: July 2026

Everyone I have ever texted is in a SQLite database on my Mac. Apple stores your entire message history in a file called chat.db, quietly, going back years. I decided to mine it, not for nostalgia, but to fix a problem that had been bugging me: who am I losing touch with? What are the ties that bind? The little things that make us people worth knowing.

Why bother?

My contacts app knows who I know. The CRM knows who I'm neglecting.

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. When I look back at the friendships I've let go quiet, I regret it.

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've crossed paths with, I scroll a list sorted by exactly that: last contact.

The build

I wrote a Python script that reads chat.db (47,000 messages, in my case), cross-references it against my address book, and shoves everything into a markdown wiki: one page per person. It turns out I've texted 365 people over the years, and each page holds their contact info, message counts, the messages themselves, and when we last talked.

The index is sorted by last contact, which makes the whole thing a gentle guilt engine: the people sliding toward the bottom are the friendships going quiet.

The rough part: modern iMessages aren't stored as plain text. The message body lives in a serialized Apple NSAttributedString blob (typedstream format), and writing a decoder to get human-readable text back out became a real chunk of the project. There's something ironic about needing a binary-format decoder to read your own text messages. I felt like a digital archaeologist.

Things I learned

• Your terminal needs Full Disk Access to read chat.db. macOS guards it, as it should.
• Obsidian-compatible markdown is a great output format. Links between people pages for free, and no app lock-in; it's just files.
• Keep it local. Everything runs on my Mac, because that's where chat.db lives, and the vault sits on its FileVault-encrypted drive. None of this data or output belongs anywhere near a cloud service.
• Linux is still your friend. The Mac is just where the data is. The rest of my household's services run on NERV, my Linux home server, and even the Windows gaming PC does its AI work through WSL. One way or another, it's Linux underneath.

What's next?

• Mine the messages themselves. Once I get Ollama running better on my RTX 4070 Super (through WSL), I'm pointing it at the data store to pull out birthdays, the people in my friends' lives, and the other little details I should probably remember better.
• Make it visual. Move the wiki from plain markdown to markdown with generated HTML pages: a relationship web, a contact heatmap, maybe all of the above. I don't know yet. Just a visual way of seeing the people in my life in a meaningful way.
• Close the loop. A script that creates calendar reminders, so touching base with people happens on purpose instead of by accident.

← All projects