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;

How to distinguish message types when text is NULL

How to decode

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:

1. Tier 1: pytypedstream Unarchiver — proper Apple typedstream deserialization (requires pip install pytypedstream) 2. Tier 2: Multi-format binary — 0x2B/0x4F/0x49 length-prefix parsing (zero deps, ported from macos-messages) 3. Tier 3: NSString marker + length-prefix — v2 legacy approach (zero deps, last resort)

Falls through tiers on failure. Works without pytypedstream installed (skips tier 1). See Cross-Repo Analysis for decoder comparison.

Date Formula

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)

Quick Start Queries

1. List all conversations

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"

2. Get conversation thread (text column only)

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"

3. Get ALL messages including attributedBody (use decode script)

python3 <skill-path>/scripts/decode_attributed_body.py \
  --chat "<CHAT_IDENTIFIER>" \
  --after "2026-01-01" \
  --limit 100

Filtering Noise

Tapback reactions

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

Shell escaping in zsh

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

Using the Decode Script

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 Search (--context N)

When --search is combined with --context N, the script shows N messages before and after each match:

• Matches are prefixed with [match]
• Non-contiguous context groups are separated by --- context ---
• Overlapping context windows are deduplicated

NDJSON Export (--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

Native Protections (v4)

The decode script natively handles these pitfalls — no manual SQL workarounds needed:

Anti-Patterns to Avoid

1. Searching multiple chat identifiers blindly — Always run --stats first to confirm the right chat identifier has messages in the expected date range 2. Keyword search without context — Always use --context 5 (or more) with --search to understand conversational meaning around matches 3. Repeated narrow-window SQLite queries — Export the full date range to NDJSON first, then grep/jq the file for all subsequent analysis

Note: 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

Reference Documentation

• Schema Reference — Tables, columns, relationships
• Query Patterns — Reusable SQL templates for common operations
• Known Pitfalls — Every gotcha discovered and how to handle it
• Cross-Repo Analysis — Comparison of 5 OSS decoder implementations and what we adopted

---

TodoWrite Task Templates

Template A - Retrieve Conversation Thread

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

Template B - Debug Empty Messages

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

Template C - Build Sourced Timeline

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)

Template D - Export-First Deep Analysis

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)

---

Post-Change Checklist

After modifying this skill:

1. [ ] YAML frontmatter valid (name, description with triggers) 2. [ ] No private data (phone numbers, names, emails) in any file 3. [ ] All SQL uses parameterized placeholders 4. [ ] Decode script works with python3 (pytypedstream optional, tiers 2/3 are stdlib-only) 5. [ ] All reference links are relative paths 6. [ ] Append changes to evolution-log.md

Post-Execution Reflection

After this skill completes, check before closing:

1. Did the command succeed? — If not, fix the instruction or error table that caused the failure. 2. Did parameters or output change? — If the underlying tool's interface drifted, update Usage examples and Parameters table to match. 3. Was a workaround needed? — If you had to improvise (different flags, extra steps), update this SKILL.md so the next invocation doesn't need the same workaround.

Only update if the issue is real and reproducible — not speculative.

Skill: iMessage Query

Cross-Repository Analysis: iMessage Decoder Implementations

Comparative analysis of 5 open-source iMessage attributedBody decoders, conducted 2026-02-14. Used to inform the v3 decoder upgrade for this skill.

---

Repositories Studied

All repositories forked to ~/fork-tools/ under terrylica for analysis.

1. imessage-exporter (Rust)

Decoder approach: Uses the crabstep crate for native Apple typedstream deserialization. Has a legacy regex-based fallback for older message formats. The Rust type system provides strong guarantees on binary parsing correctness.

What we learned: Confirmed that proper typedstream deserialization is the gold standard approach. The dual-strategy pattern (proper parser + legacy fallback) influenced our 3-tier design.

What we adopted: The philosophy of having a proper deserializer as tier 1 with fallbacks for edge cases.

What we did NOT adopt: The Rust implementation itself (wrong language for our Python skill). The crabstep crate is Rust-only with no Python bindings.

---

2. macos-messages (Python)

Decoder approach: The most thorough pure-binary parser found in any Python repo. The _extract_text_from_attributed_body() function in src/messages/db.py handles 4 distinct binary encoding formats:

1. 0x2B (+) marker — Variable-length encoding:

• < 0x80: 1-byte length (direct)
• 0x81: 2-byte little-endian length
• 0x82: 3-byte little-endian length
• 0x83: 4-byte little-endian length

2. 0x4F marker — Extended encoding with size markers:

• 0x10: 1-byte length
• 0x11: 2-byte big-endian length
• 0x12: 4-byte big-endian length

3. 0x49 (I) marker — Legacy 4-byte big-endian length 4. Heuristic fallback — Regex for readable text sequences after NSString marker

What we adopted: The entire multi-format binary decoder was ported as tier 2 in our v3 decoder (_decode_via_multiformat()). This provides zero-dependency decoding for messages that pytypedstream can't handle, covering format variants we'd never encountered in our own testing.

What we did NOT adopt: Their overall architecture (AI-first analysis pipeline, conversation grouping, sentiment analysis). Out of scope for a decode-focused skill.

---

3. imessage-conversation-analyzer (Python)

Decoder approach: Uses the pytypedstream package for proper Apple typedstream binary deserialization. The decode function at ica/core.py:111-124 is only 6 lines:

from typedstream import Unarchiver

def decode_message_attributedbody(blob):
    result = Unarchiver.from_data(blob).decode_all()
    # Navigate result → GenericArchivedObject → contents → NSMutableString.value

This is the most reliable approach because it uses the actual Apple binary format specification rather than pattern matching on byte sequences.

What we adopted: The pytypedstream dependency and Unarchiver.from_data().decode_all() approach as tier 1 in our v3 decoder (_decode_via_typedstream()). We expanded the text extraction to handle more object graph variations (both val.value for NSMutableString wrappers and direct str values).

What we did NOT adopt: Their analytics pipeline (pandas, DuckDB, word clouds, sentiment analysis). Different purpose than our decode-and-search skill. Also did not adopt their TypedStreamReader — that class doesn't exist in the current pytypedstream API; the correct entry point is Unarchiver.

API discovery note: The pip package is pytypedstream but it installs as the typedstream module. The Unarchiver class is the correct entry point, not TypedStreamReader (which appears in some older documentation but doesn't exist).

---

4. imessage_tools (Python)

Decoder approach: content[6:-12].decode("utf-8") — a fixed-offset slice that works only for messages of a specific length range. No length-prefix parsing, no format detection, no error handling.

Why we skipped this: The [6:-12] slice is brittle — it silently truncates or corrupts messages of unusual lengths. Bare except: clauses mask errors. No tests, no maintenance. This is the anti-pattern our v1 decoder was already better than.

What we learned: Confirmed that hardcoded byte offsets are the worst possible approach to typedstream decoding. Any format variation (longer messages, emoji, different iOS versions) breaks silently.

---

5. pymessage-lite (Python)

Why we skipped this: No attributedBody decoding at all. Uses string formatting for SQL queries (injection risk). Python 2 style code. Nothing to learn from this repo beyond confirming what not to do.

---

Capability Comparison Matrix

---

Selection Criteria

What we optimized for

1. Decode reliability — Must decode all messages including short ones, emoji, and rich formatting 2. Graceful degradation — Must work even if pytypedstream is not installed (falls through to pure-binary tiers) 3. Zero-config for basic use — Tiers 2 and 3 are stdlib-only, tier 1 requires one pip install 4. Search-first workflow — --search with --context is unique to our skill and critical for investigation work 5. Minimal scope — We're a decode-and-search tool, not an analytics platform

What we adopted and why

What we kept from our own implementation

What we did NOT adopt and why

---

Complementary Tool Recommendations

These tools serve different purposes and can be used alongside our skill:

Our skill remains the best choice for:

• Quick keyword searches with conversational context (--search + --context)
• Building sourced timelines with precise timestamps
• Export-once-analyze-many workflows with NDJSON
• Claude Code integration (pipe-delimited output, stderr summaries)

---

Technical Notes

pytypedstream API

# Correct usage (verified 2026-02-14):
from typedstream import Unarchiver

result = Unarchiver.from_data(blob).decode_all()
# Returns: list[TypedValue]
# Navigate: TypedValue.value → GenericArchivedObject.contents → NSMutableString.value → str

Gotchas:

• Package name: pip install pytypedstream (PyPI)
• Module name: import typedstream (NOT pytypedstream)
• Entry point: Unarchiver (NOT TypedStreamReader)
• Method: .decode_all() returns a list (Unarchiver itself is NOT iterable)

Multi-format binary encoding (from macos-messages)

The attributedBody binary format uses different length-encoding schemes depending on iOS version and message content:

0x2B (+) marker:
  byte < 0x80  → 1-byte length (direct value)
  byte == 0x81 → next 2 bytes = length (little-endian)
  byte == 0x82 → next 3 bytes = length (little-endian)
  byte == 0x83 → next 4 bytes = length (little-endian)

0x4F marker:
  0x10 → next 1 byte = length
  0x11 → next 2 bytes = length (big-endian)
  0x12 → next 4 bytes = length (big-endian)

0x49 (I) marker:
  next 4 bytes = length (big-endian)

All extraction follows the pattern: split on NSString, split on NSDictionary, find marker, read length, extract UTF-8 text.

Skill: iMessage Query

Evolution Log

Reverse chronological record of changes to this skill.

---

2026-02-15 — v4 Native Pitfall Protections + Full Metadata Extraction

Context: After correcting the "voice message" misconception (actually retracted messages — pitfall #14) and discovering that thread_originator_guid provides inline quote context for 291 messages in the Phoebe chat, we upgraded the script to natively handle every known pitfall deterministically rather than relying on contextual documentation alone. Cross-referenced all 5 forked OSS repos and the full 92-column message schema to identify missing attributes.

Changes:

1. Retracted message exclusion (pitfall #14) — Messages with date_retracted > 0 are deterministically excluded from both stdout and NDJSON export. Also handles older iOS pattern where Undo Send set date_edited instead of date_retracted (detected when date_edited > 0 + empty text/attributedBody).

2. Edited message flagging — Messages with date_edited > 0 that still have content are included but flagged with [edited] in stdout and "edited": true in NDJSON.

3. Audio message identification (pitfall #9 correction) — Uses is_audio_message column (definitive) instead of heuristic guessing from NULL text + attachments. Audio messages emit [audio message] placeholder instead of being silently dropped.

4. Inline quote resolution — Builds GUID→message index via _build_guid_index(). Messages with thread_originator_guid get their reply_to field populated with the quoted message's {ts, sender, text}. Shown as [replying to sender: "quoted text"] in stdout and "reply_to": {...} in NDJSON.

5. Attachment surfacing — Messages with no text but cache_has_attachments = 1 now emit [attachment: filename] or [attachment: mime_type] instead of being silently dropped. LEFT JOINs to attachment table via message_attachment_join.

6. Message effects — expressive_send_style_id decoded to human-readable names (slam, loud, gentle, invisible_ink). Shown as [slam] in stdout and "effect": "slam" in NDJSON.

7. Service type — service column distinguishes iMessage from SMS. Non-iMessage messages flagged with [SMS] in stdout and "service": "SMS" in NDJSON.

8. Enhanced stats — --stats now shows retracted, edited, audio, threaded replies, SMS counts, and adjusted coverage (excluding retracted).

9. Row deduplication — LEFT JOIN to attachment table can produce duplicate rows for multi-attachment messages. Dedup key (ts, is_from_me, text) prevents duplicates in output.

SQL query changes:

• Added columns: m.date_retracted, m.date_edited, m.is_audio_message, m.service, m.expressive_send_style_id, m.cache_has_attachments, a.transfer_name, a.mime_type
• Added JOINs: LEFT JOIN message_attachment_join, LEFT JOIN attachment
• New function: _build_guid_index() for GUID→message resolution

NDJSON schema v2:

{
  "ts": "2026-02-14 07:16:22",
  "sender": "them",
  "is_from_me": false,
  "text": "I was not trying to loop around you...",
  "decoded": false,
  "type": "text",
  "edited": true,
  "service": "SMS",
  "effect": "slam",
  "reply_to": {
    "ts": "2026-02-13 23:30:00",
    "sender": "me",
    "text": "Original message..."
  }
}

Fields edited, service, effect, reply_to are optional — only present when applicable.

Informed by: macos-messages (columns: date_edited, date_retracted, expressive_send_style_id, thread_originator_guid), imessage-exporter (attachment handling), full PRAGMA table_info(message) audit (92 columns).

---

2026-02-14 — v3 Decoder: 3-Tier with pytypedstream + Cross-Repo Analysis

Context: After the v2 fix (NSString marker + length-prefix), conducted a cross-repo analysis of 5 open-source iMessage decoder implementations to find the best-in-class approaches. Forked and studied: imessage-exporter (Rust), macos-messages (Python), imessage-conversation-analyzer (Python), imessage_tools (Python), pymessage-lite (Python). Full analysis in cross-repo-analysis.md.

Verdict: Learn from the best, keep our unique features (--search, --context, --export), upgrade the decoder with external dependency (pytypedstream).

Changes:

1. 3-tier decoder architecture — Replaced single-function decoder with tiered fallback:

• Tier 1: _decode_via_typedstream() — pytypedstream Unarchiver (proper Apple typedstream deserialization). Adopted from imessage-conversation-analyzer. Handles all message lengths, emoji, rich formatting.
• Tier 2: _decode_via_multiformat() — Multi-format binary parser with 0x2B/0x4F/0x49 length-prefix variants + heuristic fallback. Ported from macos-messages _extract_text_from_attributed_body(). Zero external deps.
• Tier 3: _decode_via_nsstring_marker() — v2 legacy NSString split + length-prefix (LangChain approach). Kept as last resort.

2. Graceful degradation — _HAS_TYPEDSTREAM flag allows script to work without pytypedstream installed (skips tier 1, falls through to tiers 2/3).

3. PEP 723 inline script metadata — Added # dependencies = ["pytypedstream"] for tools that support it (e.g., uv run --script).

Additions:

• re import (for heuristic fallback in tier 2)
• from typedstream import Unarchiver (conditional, with graceful ImportError handling)
• 3 new decode functions (_decode_via_typedstream, _decode_via_multiformat, _decode_via_nsstring_marker)

API discovery (pytypedstream):

• Package: pip install pytypedstream (PyPI name)
• Module: import typedstream (NOT pytypedstream)
• Entry point: Unarchiver.from_data(blob).decode_all() (NOT TypedStreamReader)
• Unarchiver is NOT iterable — must call .decode_all() first

New reference: cross-repo-analysis.md — Full comparison of all 5 repos with selection criteria, adoption decisions, and technical notes.

---

2026-02-14 — v2 Decoder + Context & Export Features

Context: During analysis of the Tiemar recruitment case, the v1 decoder (null-byte split + NS framework class filter) failed to extract 23+ messages including critical evidence like "The current office gave her a glaring reference" and "She gave me these references" + phone numbers. This caused multiple wasted search attempts and missed evidence that took hours to find manually via screenshots.

Root cause: The v1 any(cls in chunk for cls in NS_FRAMEWORK_CLASSES) filter discards chunks containing both message text AND framework class names. For short messages, the actual text and NSString/NSDictionary markers always land in the same null-delimited chunk — so the filter throws out the message with the metadata.

Changes:

1. Replaced `decode_attributed_body()` with NSString marker + length-prefix algorithm — Same approach as LangChain's iMessage loader. Splits on b"NSString", skips 5-byte preamble, reads length-prefix (single byte or 0x81 + 2-byte little-endian), extracts exact text. No filtering needed.

2. Added `--context N` flag — When used with --search, shows N messages before and after each match. Solves the "isolated keyword match loses conversational meaning" problem. Uses --- context --- separators between non-contiguous groups and [match] markers on actual matches.

3. Added `--export <path.jsonl>` flag — Exports conversation to NDJSON file for offline analysis. Format: {"ts", "sender", "is_from_me", "text", "decoded"} per line. Enables export-once-analyze-many workflow instead of repeated SQLite queries.

Removals:

• NS_FRAMEWORK_CLASSES frozenset (no longer needed)
• re import (no longer needed)
• Null-byte split logic
• iI cleanup regex (length-prefix extraction doesn't include trailing artifacts)
• +. cleanup regex (same reason)

Anti-patterns documented:

1. Searching multiple chat identifiers for the same person without first checking --stats 2. Keyword search without context — always use --context 5 with --search 3. Repeated narrow-window SQLite queries — export first, then grep

---

2026-02-07 — Initial Creation

Context: During iMessage retrieval work, discovered that 20-60% of messages in real conversations have NULL text columns but contain valid, recoverable text in attributedBody (NSAttributedString binary blobs). Without documented knowledge of this pattern, every future session would rediscover the same workaround from scratch.

Created:

• SKILL.md — Main skill with YAML frontmatter, workflow instructions, quick start queries
• scripts/decode_attributed_body.py — Python script (stdlib only) for decoding NSAttributedString binary blobs from attributedBody column
• references/schema-reference.md — Core table documentation (message, chat, handle, attachment, joins)
• references/query-patterns.md — 8 reusable SQL templates for common operations
• references/known-pitfalls.md — 10 documented pitfalls with symptoms and solutions
• references/evolution-log.md — This file

Key discoveries codified:

1. text vs attributedBody problem (critical — causes messages to appear empty) 2. NSAttributedString binary decode technique (null-byte split, framework class filtering) 3. Tapback reaction filtering (associated_message_type = 0) 4. Apple epoch date formula with localtime conversion 5. zsh shell escaping for != operator 6. Voice message vs dictated text differentiation (cache_has_attachments flag)

Skill: iMessage Query

Known Pitfalls

Every gotcha discovered when working with the macOS iMessage database, with symptoms and solutions.

---

Critical: The text vs attributedBody Problem

Pitfall: Many messages have a NULL or empty text column but contain valid, recoverable text in attributedBody.

Symptom: Messages appear empty or are mistakenly classified as "voice messages" when they are actually dictated text or rich-formatted messages.

Root cause: iOS stores messages typed via dictation, messages with rich formatting (links, styled text), and some regular messages in attributedBody as an NSAttributedString binary blob instead of (or in addition to) the text column.

Solution: Always check attributedBody when text is NULL. Use the decode script or the inline Python technique below.

Scale: In real-world conversations, 20-60% of one party's messages may be stored exclusively in attributedBody, especially if they use dictation frequently.

---

Pitfall Reference Table

---

Detailed Explanations

1. Decoding NSAttributedString Binary

The attributedBody column contains a serialized NSAttributedString object. The binary format includes:

• A streamtyped header
• The actual text content (after a b"NSString" marker + 5-byte preamble + length prefix)
• Attribute dictionaries (font, color, paragraph style)
• Apple framework class names as markers

Decode strategy v3 (used by the bundled script — 3-tier with pytypedstream):

# Tier 1: pytypedstream (proper deserialization, most reliable)
from typedstream import Unarchiver
result = Unarchiver.from_data(attr_body).decode_all()
# Navigate: TypedValue.value → GenericArchivedObject.contents → NSMutableString.value → str

# Tier 2: Multi-format binary (0x2B/0x4F/0x49 markers with variable-length encoding)
# Ported from macos-messages — handles 4 encoding formats, zero external deps

# Tier 3: NSString marker + length-prefix (v2 legacy, LangChain approach)
# Split on b"NSString", skip 5-byte preamble, read length — simplest last resort

Why v3? The v2 approach (NSString marker only) fixed the v1 short-message bug but only handles one encoding format. The v3 3-tier strategy handles all known formats via pytypedstream (tier 1), falls through to multi-format binary parsing (tier 2), and keeps the v2 approach as a last resort (tier 3). See cross-repo-analysis.md for full comparison.

Why v2 over v1? The original v1 approach (null-byte split + framework class name filtering) silently dropped short messages where the text and NS class names coexisted in the same chunk. See pitfall #11.

2. Tapback Reactions

When a user "likes" or "loves" a message, iOS creates a NEW message row with:

• associated_message_type set to 2000-2005 (or 3000-3005 for removal)
• text often NULL
• attributedBody containing metadata about the reaction

Always filter: AND m.associated_message_type = 0

Without this filter, conversations appear to have many duplicate or garbage messages.

3. Shell Escaping in zsh

macOS default shell is zsh. The != operator in SQL strings can be misinterpreted:

# BAD — zsh may choke on !=
sqlite3 db.db "SELECT * FROM message WHERE text != ''"

# GOOD — works in all shells
sqlite3 db.db "SELECT * FROM message WHERE length(text) > 0"

4. Date Conversion Gotchas

Forgetting `'localtime'`:

-- Returns UTC (wrong for display)
datetime(m.date/1000000000 + 978307200, 'unixepoch')

-- Returns local time (correct)
datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime')

Comparing dates: Always compare in the datetime() domain, not raw integers:

-- GOOD
WHERE datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime') >= '2026-01-01'

-- BAD (raw integer comparison is error-prone)
WHERE m.date >= 788918400000000000

5. Group Chats vs 1:1

Group chats have a different chat_identifier format (often chat + number). To find group chats:

SELECT chat_identifier, display_name
FROM chat
WHERE chat_identifier LIKE 'chat%'

Group chat messages may have different handle_id values for each participant. Use the handle table to resolve sender identity in group chats.

6. Database Locking

chat.db is actively used by Messages.app. Always open read-only:

# Python — read-only URI mode
conn = sqlite3.connect("file:path/to/chat.db?mode=ro", uri=True)

# sqlite3 CLI — inherently read-only for SELECT queries
sqlite3 ~/Library/Messages/chat.db "SELECT ..."

Never attempt to write to chat.db — it will corrupt the database.

7. Short Messages Invisible to v1 Decoder (FIXED)

Pitfall #11 — Messages under ~50 characters returned None from the original v1 decode function. Searching for known keywords returned "No messages found" despite messages existing in the DB.

Root cause: The v1 null-byte split approach puts actual message text in the same chunk as NSString/NSDictionary class markers. The framework class filter (any(cls in chunk for cls in NS_FRAMEWORK_CLASSES)) then discards the entire chunk — throwing out the message text along with the metadata.

Affected messages (discovered during Tiemar recruitment case, 2026-02-13):

• "She gave me these references" + phone numbers
• "The current office gave her a glaring reference"
• "Yes", "Cool.", "Never", and other short messages

Solution: Replaced with NSString marker + length-prefix extraction (v2, 2026-02-14), then upgraded to 3-tier decoder with pytypedstream (v3, 2026-02-14). No filtering needed — pytypedstream does proper deserialization, and the length-prefix fallbacks give exact text boundaries.

Anti-pattern: Never filter decoded chunks by checking if they _contain_ framework class names. The message text and class names coexist in the same binary region for short messages.

8. pytypedstream Module Name Mismatch

Pitfall #12 — from pytypedstream import Unarchiver raises ImportError. The PyPI package name (pytypedstream) differs from the installed module name (typedstream).

Solution: from typedstream import Unarchiver — always import from typedstream, not pytypedstream.

9. Unarchiver Object Not Iterable

Pitfall #13 — for value in Unarchiver.from_data(blob): raises TypeError. The Unarchiver object is not directly iterable.

Solution: Call .decode_all() first: Unarchiver.from_data(blob).decode_all() returns a list[TypedValue] that you can iterate.

10. Retracted Messages Mistaken for Voice Messages or Missing Content

Pitfall #14 — Messages with NULL text, NULL/empty attributedBody, and cache_has_attachments = 0 look identical to the "voice message" pattern from pitfall #9. Previous sessions incorrectly classified these as voice messages, inflating the "undecodable" count.

Root cause: When a sender uses "Undo Send" (iOS 16+), the message row stays in the database but both text and attributedBody are wiped. The date_edited field is set to the retraction timestamp (typically 3–64 seconds after date).

How to detect:

-- Retracted messages (NOT voice, NOT missing content — intentionally unsent)
SELECT datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime') as sent,
       datetime(m.date_edited/1000000000 + 978307200, 'unixepoch', 'localtime') as retracted,
       (m.date_edited - m.date) / 1000000000 as delay_secs
FROM message m
WHERE m.date_edited > 0
AND (m.text IS NULL OR length(m.text) = 0)
AND (m.attributedBody IS NULL OR length(m.attributedBody) < 50)

Original text length: The message_summary_info blob (a plist) contains otr.0.le which records the original text length before retraction. This confirms the message had real content, but that content is unrecoverable.

Admissibility: Retracted messages are NOT admissible in conversation logs or NDJSON exports. Both parties saw the retraction notification. The decode script correctly excludes them (no text or attributedBody means _resolve_message() returns None).

Anti-pattern: Never assume that NULL text + NULL attributedBody = voice message. Always check date_edited first — if set, it's a retracted message, not a voice message.

Skill: iMessage Query

Reusable SQL Query Patterns

All queries use parameterized placeholders. Replace <CHAT_IDENTIFIER> with actual phone number or email.

Database: ~/Library/Messages/chat.db Tool: sqlite3 (pre-installed on macOS)

---

1. List All Conversations

Find chat identifiers for all conversations, sorted by message count.

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 30"

2. Get Conversation Thread (text column only)

Simple retrieval — misses messages stored in attributedBody.

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 ASC"

3. Keyword Search Across All Chats

Search for a keyword in all conversations.

sqlite3 ~/Library/Messages/chat.db \
  "SELECT c.chat_identifier,
          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 m.text LIKE '%<KEYWORD>%'
   AND m.associated_message_type = 0
   ORDER BY m.date DESC
   LIMIT 50"

Note: This only searches the text column. To search attributedBody content, use the decode script with --search.

4. Message Statistics

Get counts by sender, message type, and date range for a conversation.

sqlite3 ~/Library/Messages/chat.db \
  "SELECT
     COUNT(*) as total,
     SUM(CASE WHEN m.is_from_me = 1 THEN 1 ELSE 0 END) as sent,
     SUM(CASE WHEN m.is_from_me = 0 THEN 1 ELSE 0 END) as received,
     SUM(CASE WHEN m.text IS NOT NULL AND length(m.text) > 0 THEN 1 ELSE 0 END) as has_text,
     SUM(CASE WHEN (m.text IS NULL OR length(m.text) = 0)
               AND m.attributedBody IS NOT NULL
               AND length(m.attributedBody) > 100
               AND m.cache_has_attachments = 0 THEN 1 ELSE 0 END) as hidden_in_attributed_body,
     SUM(CASE WHEN m.cache_has_attachments = 1 THEN 1 ELSE 0 END) as with_attachments,
     MIN(datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime')) as first_msg,
     MAX(datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime')) as last_msg
   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.associated_message_type = 0"

The hidden_in_attributed_body count shows how many messages would be missed without the decode script.

5. Find Messages with Attachments

List messages that have file attachments (images, voice memos, documents).

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,
          a.mime_type,
          a.transfer_name,
          a.total_bytes,
          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
   JOIN message_attachment_join maj ON m.ROWID = maj.message_id
   JOIN attachment a ON maj.attachment_id = a.ROWID
   WHERE c.chat_identifier = '<CHAT_IDENTIFIER>'
   AND m.associated_message_type = 0
   ORDER BY m.date DESC
   LIMIT 30"

6. Identify Messages Needing Decode

Find messages where text is NULL but attributedBody contains recoverable content.

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,
          length(m.attributedBody) as attr_len,
          m.cache_has_attachments
   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
   ORDER BY m.date DESC
   LIMIT 50"

These are the messages that require the Python decode script to extract text.

7. Conversation Window (Time Range)

Get messages in a specific time window.

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 datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime')
       BETWEEN '<START_DATE>' AND '<END_DATE>'
   AND length(m.text) > 0
   AND m.associated_message_type = 0
   ORDER BY m.date ASC"

Date format: YYYY-MM-DD or YYYY-MM-DD HH:MM:SS

8. All Messages Including Both Parties (Full Thread with Context)

Get a complete interleaved thread showing both sides, including empty-text markers.

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,
          CASE
            WHEN length(m.text) > 0 THEN m.text
            WHEN m.cache_has_attachments = 1 THEN '[attachment]'
            WHEN m.attributedBody IS NOT NULL AND length(m.attributedBody) > 100 THEN '[needs decode]'
            ELSE '[empty]'
          END as content
   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.associated_message_type = 0
   ORDER BY m.date ASC"

Messages showing [needs decode] should be processed with the Python decode script.

Skill: iMessage Query

iMessage Database Schema Reference

The macOS iMessage database is located at ~/Library/Messages/chat.db (SQLite3). Requires Full Disk Access.

Core Tables

message

The primary table. One row per message (including tapback reactions as separate rows).

chat

One row per conversation (1:1 or group).

chat_message_join

Many-to-many join between chats and messages.

handle

Contact identifiers (phone numbers, emails).

attachment

File attachments (images, voice memos, documents).

message_attachment_join

Many-to-many join between messages and attachments.

---

Date Formula

iMessage uses nanoseconds since Apple epoch (2001-01-01 00:00:00 UTC).

Convert to human-readable local time

datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime')

Breakdown

The magic number 978307200

from datetime import datetime
# Seconds between 1970-01-01 and 2001-01-01
(datetime(2001, 1, 1) - datetime(1970, 1, 1)).total_seconds()
# = 978307200.0

Filter by date

To find messages after a specific date, compare in the datetime domain:

WHERE datetime(m.date/1000000000 + 978307200, 'unixepoch', 'localtime') >= '2026-01-01'

---

associated_message_type Values

Always filter `associated_message_type = 0` to get only actual messages.