imessage-query

What this skill does

# 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

```sql
-- 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

| `cache_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                         |

### How to decode

Use the bundled decode script for reliable extraction (v4 — 3-tier decoder + native pitfall protections):

```bash
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](https://github.com/bettercallsean/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](./references/cross-repo-analysis.md) for decoder comparison.

## Date Formula

iMessage stores dates as **nanoseconds since Apple epoch (2001-01-01 00:00:00 UTC)**.

```sql
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

```sql
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)

```sql
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)

```bash
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:

```sql
AND m.associated_message_type = 0
```

### Shell escaping in zsh

The `!=` operator can cause issues in zsh. Use positive assertions instead:

```sql
-- 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:

```bash
# 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:

```json
{
  "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):

```bash
# Step 1: Export once
python3 <skill-path>/scripts/decode_attributed_body.py --chat "+1234567890" \
  --