Query macOS iMessage chat.db via SQLite. Decode NSAttributedString attributedBody messages, handle tapbacks, search conversations and build timelines.
From productivity-toolsnpx claudepluginhub terrylica/cc-skills --plugin productivity-toolsThis skill is limited to using the following tools:
references/cross-repo-analysis.mdreferences/evolution-log.mdreferences/known-pitfalls.mdreferences/query-patterns.mdreferences/schema-reference.mdscripts/decode_attributed_body.pyExecutes pre-written implementation plans: critically reviews, follows bite-sized steps exactly, runs verifications, tracks progress with checkpoints, uses git worktrees, stops on blockers.
Guides idea refinement into designs: explores context, asks questions one-by-one, proposes approaches, presents sections for approval, writes/review specs before coding.
Dispatches parallel agents to independently tackle 2+ tasks like separate test failures or subsystems without shared state or dependencies.
Query the macOS iMessage SQLite database (~/Library/Messages/chat.db) to retrieve conversation history, decode messages stored in binary format, and build sourced timelines with precise timestamps.
Self-Evolving Skill: This skill improves through use. If instructions are wrong, parameters drifted, or a workaround was needed — fix this file immediately, don't defer. Only update for real, reproducible issues.
attributedBody formatchat.db is a macOS-specific databasechat.db. Always use read-only SQLite access.pip install pytypedstream — Enables tier 1 decoder (proper typedstream deserialization). Script works without it (falls through to pure-binary tiers 2/3).text vs attributedBody ProblemIMPORTANT: Many iMessage messages have a NULL or empty text column but contain valid, recoverable text in the attributedBody column. This is NOT because they are voice messages — iOS stores dictated messages, messages with rich formatting, and some regular messages in attributedBody as an NSAttributedString binary blob.
-- Messages with attributedBody but no text (these are NOT necessarily voice messages)
SELECT COUNT(*) as hidden_messages
FROM message m
JOIN chat_message_join cmj ON m.ROWID = cmj.message_id
JOIN chat c ON cmj.chat_id = c.ROWID
WHERE c.chat_identifier = '<CHAT_IDENTIFIER>'
AND (m.text IS NULL OR length(m.text) = 0)
AND m.attributedBody IS NOT NULL
AND length(m.attributedBody) > 100
AND m.associated_message_type = 0
AND m.cache_has_attachments = 0;
text is NULLcache_has_attachments | attributedBody length | Likely type |
|---|---|---|
| 0 | > 100 bytes | Dictated/rich text — recoverable via decode script |
| 1 | any | Attachment (image, file, voice memo) — text may be in attributedBody too |
| 0 | < 50 bytes | Tapback reaction or system message — usually noise |
Use the bundled decode script for reliable extraction (v4 — 3-tier decoder + native pitfall protections):
python3 <skill-path>/scripts/decode_attributed_body.py --chat "<CHAT_IDENTIFIER>" --limit 50
The decoder uses a 3-tier strategy:
pytypedstream Unarchiver — proper Apple typedstream deserialization (requires pip install pytypedstream)Falls through tiers on failure. Works without pytypedstream installed (skips tier 1). See Cross-Repo Analysis for decoder comparison.
iMessage stores dates as nanoseconds since Apple epoch (2001-01-01 00:00:00 UTC).
datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime') as timestamp
m.date / 1000000000 — Convert nanoseconds to seconds+ 978307200 — Add offset from Unix epoch (1970) to Apple epoch (2001)'unixepoch' — Tell SQLite this is a Unix timestamp'localtime' — Convert to local timezone (CRITICAL — omitting this gives UTC)sqlite3 ~/Library/Messages/chat.db \
"SELECT c.chat_identifier, c.display_name, COUNT(cmj.message_id) as msg_count
FROM chat c
JOIN chat_message_join cmj ON c.ROWID = cmj.chat_id
GROUP BY c.ROWID
ORDER BY msg_count DESC
LIMIT 20"
sqlite3 ~/Library/Messages/chat.db \
"SELECT datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime') as ts,
CASE WHEN m.is_from_me = 1 THEN 'Me' ELSE 'Them' END as sender,
m.text
FROM message m
JOIN chat_message_join cmj ON m.ROWID = cmj.message_id
JOIN chat c ON cmj.chat_id = c.ROWID
WHERE c.chat_identifier = '<CHAT_IDENTIFIER>'
AND length(m.text) > 0
AND m.associated_message_type = 0
ORDER BY m.date DESC
LIMIT 50"
python3 <skill-path>/scripts/decode_attributed_body.py \
--chat "<CHAT_IDENTIFIER>" \
--after "2026-01-01" \
--limit 100
Tapback reactions (likes, loves, emphasis, etc.) are stored as separate message rows with associated_message_type != 0. Always filter:
AND m.associated_message_type = 0
The != operator can cause issues in zsh. Use positive assertions instead:
-- BAD (breaks in zsh)
AND m.text != ''
-- GOOD (works everywhere)
AND length(m.text) > 0
The bundled decode_attributed_body.py handles all edge cases:
# Basic usage - get last 50 messages from a contact
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" --limit 50
# Search for keyword
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" --search "meeting"
# Search with surrounding context (3 messages before and after each match)
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" --search "meeting" --context 3
# Date range
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" --after "2026-01-01" --before "2026-02-01"
# Only messages from the other party
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" --sender them
# Only messages from me
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" --sender me
# Export conversation to NDJSON for offline analysis
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" --after "2026-02-01" --export thread.jsonl
Output format: timestamp|sender|text (pipe-delimited, one message per line)
--context N)When --search is combined with --context N, the script shows N messages before and after each match:
[match]--- context -----export)Exports messages to a NDJSON (.jsonl) file for offline analysis:
{
"ts": "2026-02-13 18:30:17",
"sender": "them",
"is_from_me": false,
"text": "Message text here",
"decoded": true,
"type": "text",
"edited": true,
"service": "SMS",
"effect": "slam",
"reply_to": {
"ts": "2026-02-13 18:00:00",
"sender": "me",
"text": "Original message..."
}
}
Fields edited, service, effect, reply_to are optional — only present when applicable. The type field is always present ("text", "audio", or "attachment").
Retracted messages are NEVER exported — they are deterministically excluded (see Native Protections below).
Export-first workflow (recommended for multi-query analysis):
# Step 1: Export once
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" \
--after "2026-02-01" --export thread.jsonl
# Step 2: Analyze many times without re-querying SQLite
grep -i "keyword" thread.jsonl
jq 'select(.text | test("reference"; "i"))' thread.jsonl
jq 'select(.sender == "them")' thread.jsonl
The decode script natively handles these pitfalls — no manual SQL workarounds needed:
| Protection | Column Used | Behavior |
|---|---|---|
| Retracted messages (Undo Send) | date_retracted, date_edited | Excluded from output — content wiped by iOS, not admissible |
| Edited messages | date_edited | Flagged with [edited] / "edited": true |
| Audio/voice messages | is_audio_message | Identified as [audio message] — not misclassified as empty |
| Inline quotes (swipe-to-reply) | thread_originator_guid | Resolved to quoted message text via GUID index |
| Attachments without text | cache_has_attachments, attachment table | Surfaced as [attachment: filename] instead of silently dropped |
| Message effects | expressive_send_style_id | Decoded to human-readable names (slam, loud, gentle, invisible_ink) |
| Service type | service | Flagged when SMS instead of iMessage |
| Tapback reactions | associated_message_type | Filtered (only = 0 included) |
--stats first to confirm the right chat identifier has messages in the expected date range--context 5 (or more) with --search to understand conversational meaning around matchesNote: Replace <skill-path> with the actual installed skill path. To find it:
find ~/.claude -path "*/imessage-query/scripts/decode_attributed_body.py" 2>/dev/null
1. Identify chat_identifier for the contact (phone number or email)
2. Run decode script with --chat and appropriate date range
3. Review output for attributedBody-decoded messages (marked with [decoded])
4. If searching for specific topic, add --search flag
5. Format results as needed for the task
1. Query messages where text IS NULL but attributedBody IS NOT NULL
2. Check cache_has_attachments to distinguish voice/file from dictated text
3. Run decode script to extract hidden text content
4. Verify decoded content makes sense in conversation context
5. Document any new decode patterns in known-pitfalls.md
1. Identify all relevant chat_identifiers
2. Run decode script for each contact with date range
3. Merge and sort by timestamp
4. Format as sourced quotes with timestamps for documentation
5. Verify no messages were missed (compare total count vs decoded count)
1. Run --stats to confirm chat_identifier and date range
2. Export full date range to NDJSON: --export thread.jsonl
3. Use grep/jq on the NDJSON file for all keyword searches
4. Use --search with --context 5 for contextual understanding of specific matches
5. All subsequent analysis reads from the NDJSON file (no more SQLite queries)
After modifying this skill:
python3 (pytypedstream optional, tiers 2/3 are stdlib-only)After this skill completes, check before closing:
Only update if the issue is real and reproducible — not speculative.