Mail and Messages contact touches
Use this skill on a Mac when your CRM or contact sheet doesn't know who you last emailed or texted. Produces dated touch records (date, contact, channel, direction, email subject) from Apple Mail and Messages, without storing message bodies. "Log my emails and texts to the CRM", "when did I last talk to them", "backfill contact history".
R. Hunter HarrisFounder, Hunter Software ConsultingApplies when relationship history lives in your inbox and phone, not your CRM. Produces one dated touch per real one-to-one email or text, matched to a contact, ready to append.
Runs locally on a Mac. Both databases are opened read-only; raw records stay on the machine, and only the derived fields reach the CRM.
The play
- Build an index from your contact list: lowercase emails, and phones reduced to digits.
- Open
~/Library/Messages/chat.dband the newest~/Library/Mail/V*/MailData/Envelope Indexas read-only SQLite (file:...?mode=ro). The terminal or interpreter needs Full Disk Access. If the open fails, stop and say so; don't report "no touches". - Texts: skip reactions, convert Apple's nanosecond dates, and match the handle. Group chats are context, never a direct touch.
- Email: read sender, To and Cc, date, subject and mailbox. Skip drafts, spam, trash, junk and bulk mail. It is outbound only when the From address is one of yours.
- Match: exact email, or exact phone; fall back to the last 10 digits only for North American numbers. Anything matching more than one contact is skipped and counted, not guessed.
- Emit date, contact, channel, direction, a summary (the email subject, or just "text message") and a source ID.
- Dry run first and show the counts by channel. Write only after the human approves. De-duplicate on the source ID so a re-run adds nothing.
- Start with an 8-day window. Backfill full history once, on purpose.
The field-level traps (Mail's automated flag, RCS sends, To versus Cc) are in references/apple-data-traps.md. Read it before writing the query, and check the flag meanings against a few messages you know before importing.
What good looks like
- First check: your own sent mail shows up as outbound. If it doesn't, the automated-mail filter is eating it.
- The mediocre version treats every group text and Cc as a conversation, and the CRM fills with "touches" from threads where you were one of twelve.
- The output is right when a contact you emailed yesterday shows yesterday, a backfill is spot-checked against the inbox for five contacts, and a second run adds zero rows.
Rules
- MUST open both databases read-only, and never copy them out of the machine.
- MUST store subjects at most, never message bodies or attachments.
- MUST dry-run and get approval before writing to the CRM.
- NEVER guess between two contacts that share an email or number.
- NEVER report an empty result when Full Disk Access was refused.
references/apple-data-traps.md
Apple data traps
Each trap below put wrong touches in a CRM, or dropped real ones, before it was fixed.
Mail (Envelope Index)
- The
automated_conversationflag marks your own sends. Treating flag 2 as bulk silently dropped a real one-to-one email. Rule that held: flag 1 is bulk unless you sent it; flag 2 is bulk unless you sent it or wrote in that thread. List or unsubscribe headers always mean bulk. - Recipient type 0 is To, 1 is Cc. Reading 1 as To invented group conversations. A multi-address To or any Cc is context, not a reply.
- Mailboxes. Skip any mailbox whose name contains draft, spam, trash or junk.
- Direction. Outbound only when From is one of your verified addresses. List them explicitly; aliases and old domains count.
- Second addresses. A contact's other address can be added when a name matches exactly one contact and mail went both ways. Record it as a confirmed identifier; count and skip anything ambiguous.
- No index. Some machines have no
Envelope Index; the fallback is the.emlxfiles, which is slower.
Messages (chat.db)
- Your RCS sends have
handle_id0. They carry no number. Take the handle from the one-to-one chat they belong to, never from a group chat. - Group chats are
chat.style43. Record them as "group text message" context at most. Drop your own group sends. - Reactions have an
associated_message_type; skip them. - Dates are nanoseconds since 2001-01-01 UTC. Convert to local time before comparing with date-only CRM fields.
- Phones. Saved contacts often lack
+1. Match the full digits first, then the last 10 for North American numbers only.
Full Disk Access
Without it, the open fails or returns nothing, and a naive script reports "no new touches". Treat a failed or read-only-refused open as an error that names the fix: grant Full Disk Access to the terminal or interpreter in System Settings, under Privacy & Security.
What to store
| Field | Example |
|---|---|
| date | local date and time |
| contact | your contact's ID |
| channel | email, text |
| direction | in, out |
| summary | email: <subject>, capped at 160 characters, or text message |
| source | message GUID or Mail's global message ID, for de-duplication |
Never store bodies. A text's body is read only to tell whether it had text or just an attachment.
Scale, for orientation
A full-history backfill against about 100 active contacts produced roughly 735 touches: three quarters direct email, a fifth group or Cc email, and a handful of texts. Expect most of a backfill to be email.