aashari/mail-digest

Mail Digest — Email Summary for Any Time Period

Period: $ARGUMENTS (default: today)

Step 1: Parse the time expression into a Unix timestamp range

Interpret $ARGUMENTS naturally. Map to START and END unix timestamps:

| Expression | START | END | |---|---|---| | "today" / (empty) | midnight today local | now | | "yesterday" | midnight yesterday | midnight today | | "last 2 hours" | now − 7200s | now | | "last N hours" | now − N×3600s | now | | "last N days" | now − N×86400s | now | | "this week" | last Monday midnight | now | | "last week" | Monday of prev week | last Sunday midnight | | "since Monday" | most recent Monday midnight | now | | "YYYY-MM-DD" | that date midnight | that date 23:59:59 | | "catch me up" / "while I was away" | infer from context or ask — default last 5 days |

DB="$HOME/Library/Mail/V10/MailData/Envelope Index"

# Use SQLite date expressions directly — never compute Unix epochs manually (wrong year risk).
# "today"       → >= date('now','localtime')
# "yesterday"   → >= date('now','-1 day','localtime') AND < date('now','localtime')
# "last 48h"    → >= datetime('now','-48 hours')
# "last 7 days" → >= date('now','-7 days','localtime')
# Apply the range in the WHERE clause below using datetime() comparisons.

Step 2: Pull all messages in the window

sqlite3 "$DB" "
SELECT datetime(m.date_received,'unixepoch','localtime') as dt,
       s.subject, a.address as sender, a.comment as name,
       mb.url as mailbox, m.ROWID,
       m.read, m.flagged, m.is_urgent, m.size,
       m.automated_conversation, m.unsubscribe_type
FROM messages m
JOIN subjects  s  ON m.subject = s.ROWID
JOIN addresses a  ON m.sender  = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE datetime(m.date_received,'unixepoch','localtime') >= date('now','localtime')
  -- adjust the expression above based on $ARGUMENTS
  AND m.deleted = 0
  AND mb.url NOT LIKE '%Spam%'
  AND mb.url NOT LIKE '%Trash%'
  AND mb.url NOT LIKE '%Junk%'
  AND mb.url NOT LIKE '%Draft%'
  AND mb.url NOT LIKE '%Sent%'
ORDER BY m.is_urgent DESC, m.date_received DESC;" 2>/dev/null

Step 3: Categorize

Tier 1 — Needs Attention (read these):

• is_urgent = 1
• flagged = 1
• read = 0 AND automated_conversation != 2 AND unsubscribe_type = 0 (real person, unread)
• Subject contains: urgent, action required, ASAP, deadline, critical, review, approve, response needed

Tier 2 — FYI (skim):

• automated_conversation = 1 (transactional — Jira, Slack, bank receipts)
• Reports, summaries, meeting notes, calendar invites
• Security alerts (login, 2FA, password) — even if automated
• Financial: receipts, payment confirmations, invoices

Tier 3 — Noise (count only, don't enumerate):

• automated_conversation = 2 — bulk automated emails
• unsubscribe_type > 0 — newsletter/mailing list emails
• Monitoring subjects: PROBLEM:, OK:, ALARM:, CRIT:
• Break down by type: monitoring alerts, newsletters, marketing

For Tier 3, just report: "X automated emails (Y monitoring, Z CI/CD, W marketing)"

Step 4: Read bodies for Tier 1 items

For Tier 1 emails, get body preview. Check summaries table first (fast, no file I/O):

sqlite3 "$DB" "
SELECT m.ROWID, su.summary
FROM messages m
JOIN summaries su ON m.summary = su.ROWID
WHERE m.ROWID IN (<ROWID1>,<ROWID2>,...)" 2>/dev/null

For any ROWIDs without a summary entry, fall back to emlx parsing:

python3 ~/.claude/skills/_mail-shared/parser.py <ROWID1> <ROWID2> ...

Pull the first ~200 chars of meaningful body content as a preview for each.

Step 5: Surface special categories

Always call out explicitly if found in the window:

• Financial: subjects containing payment, receipt, invoice, transfer, transaction, billing → note count and total if parseable
• Security: login alerts, 2FA changes, password resets → flag every one
• Meetings: calendar invites or reschedules
• Unread from humans: these are the most likely to need action

Output Format

Email Digest — [RESOLVED DATE RANGE] Total: X | Unread: Y | Flagged: Z | Accounts: N

---

Needs Attention (X) | Time | From | Subject | Preview | |---|---|---|---|

FYI (X) | Time | From | Subject | |---|---|---|

Financial (X emails)

• Brief list of transactions if detectable from subjects

Security (X events)

• Brief list

Noise filtered: X total (breakdown by type)

---

If the window has zero emails: say so and suggest a wider range. If the window has >200 emails: summarize by sender domain and ask if they want to drill into a category. Offer at the end: "Want me to read any of these, or dive into a specific category?"