Apple Mail keeps a full SQLite database of your mailbox metadata. It sits at
~/Library/Mail/V10/MailData/Envelope Index (the V number tracks the Mail
version), and it is the reason Mail can search 300,000 messages instantly on a
laptop. Almost nobody automates against it. Everybody automates against
AppleScript instead, which is how you end up with 10 second waits for a search
that the database answers in a fraction of a millisecond.
I spent the last months building an MCP server on top of this database, so here
is the tour I wish someone had written for me.
The shape of it
The schema is normalized in an old-school, disciplined way. The tables you care
about:
messages is the spine. One row per message across all accounts. It carries
the ROWID that every other table points at, plus date_sent, date_received,
read, flagged, deleted, a mailbox foreign key, a subject foreign key
and a sender foreign key. Note what it does not carry: the subject text and
the sender text. Those are deduplicated into their own tables.
subjects holds each distinct subject string exactly once. A thread of 40
replies with the same subject is 40 rows in messages pointing at one row
here. On my store the ratio is 3.9 to 1: 301,024 messages share 77,343 distinct subjects.
addresses does the same for people: one row per distinct address and
display name pair. Your top correspondent appears once here and thousands of
times in messages.sender.
recipients is the join table for To and Cc: message ROWID, address ROWID,
a type column distinguishing to from cc, and a position. This is where
"find every mail where X was in Cc" becomes a two-join query instead of an
AppleScript loop over every message.
mailboxes maps mailbox ROWIDs to account and folder URLs. The URL encodes
the account UUID, which is how you tell your CERN inbox from your Gmail inbox.
attachments carries filename and MIME type per message, so "find the PDF
Beniada sent me in March" never has to open a single emlx file.
What is deliberately not in there
Message bodies. The Envelope Index is metadata only. Bodies live as individual
.emlx files next to the database, one file per message, named by ROWID. So
the fast pattern is: answer the question from SQLite, then open only the two
or three emlx files you actually need. The slow pattern, which is what
AppleScript does for you, is asking Mail.app to materialize everything.
There is a second database next to it worth knowing about: the full text
search index that Spotlight style search uses. You can get most practical
search quality from the Envelope Index alone plus targeted emlx reads, and you
avoid coupling to a less stable schema.
The rules for touching it
Mail.app owns this database and holds it open with WAL journaling. The rules
that kept me out of trouble:
- Open read only, always:
sqlite3.connect("file:...?mode=ro", uri=True). - Never write. Sends, flags and moves go through Mail itself, then you verify by reading the database again.
- Expect schema drift across macOS versions and feature-detect columns instead of assuming them.
- Full Disk Access is required. There is no way around it and that is correct: this file is your entire mail history.
Why bother
One number: a subject search across 300k messages that took 10 seconds through
scripting interfaces answers in about 0.1 milliseconds as an indexed SQLite
query. That is not an optimization, it is a different category of tool. It is
the difference between an AI assistant that can afford to look at your mailbox
once per question and one that can afford to look fifty times.
The server that came out of this is open source (MIT):
https://github.com/parasxos/apple-mail-mcp
