imessage-query

iMessage Database Query

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.

When to Use

• Retrieving iMessage conversation history for a specific contact
• Building sourced timelines with timestamps from text messages
• Searching for keywords across all conversations
• Debugging messages that appear empty but contain recoverable text
• Extracting message content that iOS stored in binary attributedBody format

Prerequisites

1. macOS only — chat.db is a macOS-specific database
2. Full Disk Access — The terminal running Claude Code must have FDA granted in System Settings > Privacy & Security > Full Disk Access
3. Read-only — Never write to chat.db. Always use read-only SQLite access.
4. Optional: pip install pytypedstream — Enables tier 1 decoder (proper typedstream deserialization). Script works without it (falls through to pure-binary tiers 2/3).

Critical Knowledge - The text vs attributedBody Problem

IMPORTANT: 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.

How to detect

-- 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;