mail-subscriptions

name	mail-subscriptions
description	Find and list all active subscriptions and recurring charges from email history. Show renewal dates, amounts, and services. Use when user asks about subscriptions, recurring charges, what they're paying for monthly, or wants to audit their subscriptions. Arguments: optional time range to search (default: last 13 months to catch all annual subs).
disable-model-invocation	false
user-invocable	true
allowed-tools	Bash
metadata	{"openclaw":{"requires":{"bins":"[Truncated]"}}}

Mail Subscriptions — Recurring Charge Audit

Scanning: $ARGUMENTS (default: last 13 months)

Step 1: Find all subscription/renewal emails

DB="$HOME/Library/Mail/V10/MailData/Envelope Index"
SINCE=$(($(date +%s) - 34214400))  # 13 months = 396 days

sqlite3 "$DB" "
SELECT datetime(m.date_received,'unixepoch','localtime') as dt,
       s.subject, a.address as sender, a.comment as name,
       mb.url, m.ROWID
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 m.date_received >= ${SINCE}
  AND m.deleted = 0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Trash%'
  AND (
    s.subject LIKE '%subscription%'
    OR s.subject LIKE '%renewal%'
    OR s.subject LIKE '%renew%'
    OR s.subject LIKE '%auto-renew%'
    OR s.subject LIKE '%billing%'
    OR s.subject LIKE '%monthly%'
    OR s.subject LIKE '%annual%'
    OR s.subject LIKE '%your plan%'
    OR s.subject LIKE '%membership%'
    OR s.subject LIKE '%your invoice%'
    OR s.subject LIKE '%receipt for%'
    OR s.subject LIKE '%charged%'
    OR s.subject LIKE '%continued%'
    OR s.subject LIKE '%successfully renewed%'
  )
ORDER BY m.date_received DESC;" 2>/dev/null

Step 2: Group by sender/service to find recurring patterns

sqlite3 "$DB" "
SELECT a.address, a.comment, COUNT(*) as occurrences,
       MIN(datetime(m.date_received,'unixepoch','localtime')) as first_charge,
       MAX(datetime(m.date_received,'unixepoch','localtime')) as last_charge
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 m.date_received >= ${SINCE}
  AND m.deleted = 0
  AND (s.subject LIKE '%subscription%' OR s.subject LIKE '%renewal%'
       OR s.subject LIKE '%receipt%' OR s.subject LIKE '%invoice%'
       OR s.subject LIKE '%billing%' OR s.subject LIKE '%charged%')
GROUP BY a.address
HAVING COUNT(*) >= 1
ORDER BY occurrences DESC;" 2>/dev/null

Step 3: Parse amounts and cycle from email bodies

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

From bodies, extract:

• Amount (and currency)
• Next renewal date if mentioned
• Plan name/tier
• Cancel URL if present

Step 4: Infer billing cycle

From occurrence count and date spread:

• 1 charge in 13 months → annual
• ~12 charges in 13 months → monthly
• ~3-4 charges → quarterly

Output Format

Your Subscriptions (inferred from email)

Service	Cycle	Amount	Last Charged	Next Due

Group by: Monthly | Annual | Unknown cycle

Total monthly spend (annualize annual charges for comparison). Flag: subscriptions not charged in >14 months (possibly cancelled). Flag: any subscription with price changes between charges. Offer to find cancel links for any service.