fruitmail — Apple Mail Search

Fast Apple Mail search via SQLite on macOS. Search emails by subject, sender, date, body, threads, attachments — results in ~50ms vs 8+ minutes with AppleScr...

Security Scan

The skill does what it says (fast local Apple Mail search) and asks for reasonable local tools, but the provided shell script interpolates unvalidated user input into SQL and shell commands (and can be pointed at the live DB with --no-copy), which creates a non-trivial risk; review and harden before installing or automating.

Purpose & Capability

Name/description match the actual behavior: the instructions and bundled shell script directly query Mail.app's Envelope Index via sqlite3 and read .emlx files. Required binaries (sqlite3, python3) and macOS-only restriction are appropriate and proportional.

Instruction Scope

The SKILL.md and script legitimately read ~/Library/Mail and .emlx files and ask for Full Disk Access — that's expected. However the script builds SQL queries and command lines by interpolating user-supplied values (e.g., SEARCH and MSG_ID) directly into SQL and shell commands without robust validation or parameterization. Examples: WHERE ... LIKE '%${SEARCH//\'/'\'}%' and WHERE ROWID = $MSG_ID; the body path is embedded into a python -c string as '$EMLX'. These patterns leave room for SQL injection or shell/argument injection, and if the user uses --no-copy the live DB could be affected. The script attempts a simplistic quote replacement for SEARCH but does not enforce numeric checks for MSG_ID or otherwise sanitize inputs fully.

Install Mechanism

Instruction-only skill with no automated installer; install instructions simply copy a script into ~/bin. No network downloads or arbitrary code fetches are performed by the skill itself, so install risk is low — but the user is explicitly asked to place the script on PATH, so they should review it before doing so.

Credentials

No credentials, environment secrets, or unrelated config paths are requested. The only privileged access required is Full Disk Access to read ~/Library/Mail, which is necessary to accomplish the stated purpose.

Persistence & Privilege

The skill is not force-enabled (always: false) and does not request persistent system-wide configuration changes. It is user-invocable only; autonomous invocation is allowed by default but not combined with additional privileges here.

What to consider before installing

This skill is coherent with its purpose (fast local search of Apple Mail), but the shipped shell script performs unescaped string interpolation into SQL and shell contexts. Before installing or automating it: 1) Review the script line-by-line (especially the SQL construction and the body/open commands). 2) Prefer the default behavior (copying the DB) and avoid --no-copy unless you trust inputs — querying the live DB plus injection could modify mail data. 3) If you will expose this to other actors or automate it, harden the script: validate that MSG_ID is an integer, avoid injecting user strings directly into SQL (use parameterized queries or ensure proper escaping), and safely quote filenames passed to python. 4) Only grant Full Disk Access to Terminal/shell if you trust the script. 5) If unsure, run the script against a copied/mounted test mailbox first or consider using a vetted third-party tool. If you want, I can point out the exact lines to change to reduce injection risk or provide a hardened version of the script.

Apple Mail Search

Search Apple Mail.app emails instantly via SQLite. ~50ms vs 8+ minutes with AppleScript.

Why This Exists

Apple Mail's AppleScript bridge enumerates every message object in memory before doing anything. At 100K+ emails, it just hangs — indefinitely. This has been broken for years and Apple hasn't fixed it.

Method	Time for 110K emails
AppleScript iteration	Hangs forever
Spotlight/mdfind	Broken since Big Sur (emlx importer removed)
SQLite (this tool)	~50ms

Setup

Follow the instructions in references/install.md to install the mail-search script to your PATH. The script source is embedded there for portability.

Requires Full Disk Access for Terminal/shell to read ~/Library/Mail/.

Usage

mail-search subject "invoice"              # Search subjects
mail-search sender "@amazon.com"           # Search by sender email
mail-search from "John"                    # Search by sender display name
mail-search to "[e-mail]"     # Search sent mail
mail-search unread                         # List unread emails
mail-search recent 7                       # Last 7 days
mail-search date-range 2025-01-01 2025-01-31  # Date range
mail-search attachments                    # Emails with attachments
mail-search thread 12345                   # Full conversation thread
mail-search body 12345                     # Read email body text
mail-search open 12345                     # Open email in Mail.app
mail-search stats                          # Database statistics

Options

-n, --limit N    Max results (default: 20)
-j, --json       Output as JSON
-c, --csv        Output as CSV
-q, --quiet      No headers
--db PATH        Override database path
--no-copy        Query live DB (faster, slight risk if Mail.app writes simultaneously)

Examples

# Morning inbox check — unread as JSON for cron processing
mail-search unread --json | jq '.[].subject'

# Find supplier emails
mail-search sender "@example.com" -n 50

# Read the actual email body
mail-search body 116519

# Thread view — see full conversation
mail-search thread 116519

# Export last month to CSV
mail-search date-range 2026-02-01 2026-02-28 --csv > feb_emails.csv

# Quick stats
mail-search stats

How It Works

Queries Mail.app's internal Envelope Index SQLite database directly at:

~/Library/Mail/V{9,10,11}/MailData/Envelope Index

Safety: By default, copies the DB to a temp file before querying so there's no risk of corruption while Mail.app is running. Use --no-copy to skip this if you need raw speed.

Epoch detection: Auto-detects whether your DB uses Unix epoch or Apple CoreData epoch (offset by 978307200 seconds). Works correctly on both.

Body reading: The body command finds the .emlx file on disk and extracts plain text (falls back to stripped HTML). Requires python3.

Key Tables

• messages — Email metadata (dates, flags, read status, foreign keys)
• subjects — Subject lines
• addresses — Email addresses and display names
• recipients — TO/CC/BCC mappings
• attachments — Attachment filenames and types
• mailboxes — Folder/mailbox structure

Limitations

• Read-only — cannot compose or send (use AppleScript for that; single-message sends work fine)
• Metadata + body — bodies require the .emlx file to exist on disk (may be purged by Mail.app for old messages)
• Apple Mail only — doesn't read Outlook, Spark, etc.
• macOS only — requires ~/Library/Mail/ directory structure

Advanced: Raw SQL

For custom queries beyond what the CLI offers:

sqlite3 -header -column ~/Library/Mail/V10/MailData/Envelope\ Index "
SELECT m.ROWID, s.subject, a.address,
       datetime(m.date_sent, 'unixepoch') as date
FROM messages m
JOIN subjects s ON m.subject = s.ROWID
LEFT JOIN addresses a ON m.sender = a.ROWID
WHERE s.subject LIKE '%your query%'
ORDER BY m.date_sent DESC
LIMIT 20;
"

Credits

Inspired by steipete's original apple-mail-search concept and tyler6204's safe-copy approach. This version adds body reading, thread support, epoch auto-detection, sent mail search, and bundles the actual executable script.

License

MIT