imessage-query

iMessage データベースのクエリ

macOS の iMessage SQLite データベース（~/Library/Messages/chat.db）にクエリを実行して、会話履歴を取得し、バイナリ形式で保存されたメッセージをデコードして、正確なタイムスタンプ付きの出典に基づくタイムラインを作成します。

自己進化型スキル：このスキルは使用を通じて改善されます。手順に誤りがある場合、パラメーターが変わっている場合、または回避策が必要だった場合は、先延ばしにせず、このファイルをただちに修正してください。実際に再現できる問題に限って更新してください。

使用するタイミング

• 特定の連絡先との iMessage 会話履歴を取得する場合
• テキストメッセージのタイムスタンプを含む、出典に基づくタイムラインを作成する場合
• すべての会話からキーワードを検索する場合
• 空に見えるものの、復元可能なテキストを含むメッセージをデバッグする場合
• iOS がバイナリ形式の attributedBody に保存したメッセージ内容を抽出する場合

前提条件

1. macOS のみ — chat.db は macOS 固有のデータベースです
2. フルディスクアクセス — Claude Code を実行するターミナルに、システム設定 > プライバシーとセキュリティ > フルディスクアクセスで FDA を許可する必要があります
3. 読み取り専用 — chat.db には書き込まないでください。必ず読み取り専用の SQLite アクセスを使用してください。
4. 任意：pip install pytypedstream — ティア 1 デコーダー（適切な typedstream のデシリアライズ）が有効になります。インストールしなくてもスクリプトは動作し、純粋なバイナリ処理のティア 2／3 に進みます。

重要な知識 - text と attributedBody の問題

重要：多くの iMessage メッセージでは、text 列が NULL または空ですが、attributedBody 列には有効で復元可能なテキストが含まれています。これは音声メッセージだからではありません。iOS は、音声入力されたメッセージ、リッチテキスト形式のメッセージ、一部の通常のメッセージを、NSAttributedString のバイナリデータとして attributedBody に保存します。

検出方法

-- 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 が NULL の場合のメッセージタイプの見分け方

cache_has_attachments	attributedBody の長さ	想定されるタイプ
0	> 100 bytes	音声入力／リッチテキスト — デコードスクリプトで復元可能
1	任意	添付ファイル（画像、ファイル、ボイスメモ） — attributedBody にもテキストが含まれる場合があります
0	< 50 bytes	Tapback リアクションまたはシステムメッセージ — 通常はノイズ

デコード方法

確実に抽出するには、同梱のデコードスクリプトを使用してください（v4 — 3 層デコーダー＋ネイティブ環境の落とし穴への対策）：

python3 <skill-path>/scripts/decode_attributed_body.py --chat "<CHAT_IDENTIFIER>" --limit 50

デコーダーは 3 層の戦略を使用します。

1. ティア 1：pytypedstream Unarchiver — Apple typedstream の適切なデシリアライズ（pip install pytypedstream が必要）
2. ティア 2：マルチ形式バイナリ — 0x2B／0x4F／0x49 の長さプレフィックス解析（依存関係なし、macos-messages から移植）
3. ティア 3：NSString マーカー＋長さプレフィックス — v2 のレガシー方式（依存関係なし、最終手段）

失敗した場合は次のティアに進みます。pytypedstream がインストールされていなくても動作します（ティア 1 をスキップします）。デコーダーの比較については、リポジトリ横断分析を参照してください。

日付の計算式

iMessage は日付を Apple エポック（2001-01-01 00:00:00 UTC）からのナノ秒数として保存します。

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

• m.date / 1000000000 — ナノ秒を秒に変換します
• + 978307200 — Unix エポック（1970）から Apple エポック（2001）までのオフセットを加算します
• 'unixepoch' — これが Unix タイムスタンプであることを SQLite に伝えます
• 'localtime' — ローカルタイムゾーンに変換します（重要 — 省略すると UTC になります）

クイックスタートクエリ

1. すべての会話を一覧表示する

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. 会話スレッドを取得する（text 列のみ）

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. attributedBody を含むすべてのメッセージを取得する（デコードスクリプトを使用）

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

ノイズの除外

Tapback リアクション

Tapback リアクション（いいね、ラブ、強調など）は、associated_message_type != 0 の別個のメッセージ行として保存されます。必ず次の条件で除外してください。

AND m.associated_message_type = 0

zsh でのシェルエスケープ

!= 演算子は zsh で問題を引き起こすことがあります。代わりに肯定形のアサーションを使用してください。

-- BAD (breaks in zsh)
AND m.text != ''

-- GOOD (works everywhere)
AND length(m.text) > 0

Decode スクリプトの使用

同梱の decode_attributed_body.py は、すべてのエッジケースに対応します。

# 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

出力形式：timestamp|sender|text（パイプ区切り、1 行につき 1 メッセージ）

コンテキスト検索（--context N）

--search と --context N を組み合わせると、スクリプトは各検索結果の前後 N 件のメッセージを表示します。

• 検索結果には [match] が付加されます
• 連続しないコンテキストグループは --- context --- で区切られます
• 重複するコンテキスト範囲は重複排除されます

NDJSON エクスポート（--export）

オフライン分析用にメッセージを NDJSON（.jsonl）ファイルにエクスポートします。

{
  "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..."
  }
}

edited、service、effect、reply_to フィールドは省略可能で、該当する場合にのみ含まれます。type フィールドは常に含まれます（"text"、"audio"、または "attachment"）。

取り消されたメッセージはエクスポートされません — 確実に除外されます（以下の「ネイティブ保護」を参照）。

エクスポートを最初に行うワークフロー（複数のクエリを分析する場合に推奨）：

# 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

ネイティブ保護（v4）

decode スクリプトは、以下の落とし穴にネイティブで対応するため、手動の SQL 回避策は必要ありません。

保護機能	使用する列	動作
取り消されたメッセージ（送信取り消し）	date_retracted、date_edited	出力から除外 — コンテンツは iOS によって消去されるため、使用できません
編集されたメッセージ	date_edited	[edited] / "edited": true としてフラグ付け
音声メッセージ	is_audio_message	[audio message] として識別 — 空として誤分類されません
インライン引用（スワイプして返信）	thread_originator_guid	GUID インデックスを介して引用元メッセージのテキストを解決
テキストのない添付ファイル	cache_has_attachments、attachment テーブル	サイレントに破棄せず、[attachment: filename] として表示
メッセージエフェクト	expressive_send_style_id	人が読める名前（slam、loud、gentle、invisible_ink）にデコード
サービスタイプ	service	iMessage ではなく SMS の場合にフラグ付け
Tapback リアクション	associated_message_type	フィルタリング（= 0 のみを含める）

避けるべきアンチパターン

1. 複数のチャット識別子を無闇に検索する — 最初に必ず --stats を実行し、想定する日付範囲にメッセージがある正しいチャット識別子を確認してください
2. コンテキストなしでキーワードを検索する — 検索結果の前後の会話の意味を理解するため、--search には必ず --context 5（またはそれ以上）を使用してください
3. 狭い範囲の SQLite クエリを繰り返す — まず日付範囲全体を NDJSON にエクスポートし、その後の分析ではすべて、ファイルに対して grep/jq を実行してください

注：<skill-path> は実際にインストールされたスキルのパスに置き換えてください。確認するには、次を実行します。

find ~/.claude -path "*/imessage-query/scripts/decode_attributed_body.py" 2>/dev/null

リファレンスドキュメント

• スキーマリファレンス — テーブル、列、リレーションシップ
• クエリパターン — 一般的な操作に再利用できる SQL テンプレート
• 既知の落とし穴 — 発見されたすべての注意点と対処方法
• リポジトリ横断分析 — 5 つの OSS デコーダー実装の比較と、採用した内容

---

TodoWrite タスクテンプレート

テンプレート A - 会話スレッドの取得

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

テンプレート B - 空のメッセージのデバッグ

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

テンプレート C - ソース付きタイムラインの作成

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)

テンプレート D - エクスポート優先の詳細分析

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)

---

変更後のチェックリスト

このスキルを変更した後は、次の項目を確認してください。

1. YAML のフロントマターが有効である（名前、トリガーを含む説明）
2. どのファイルにも個人情報（電話番号、名前、メールアドレス）が含まれていない
3. すべての SQL でパラメーター化されたプレースホルダーを使用している
4. デコードスクリプトが python3 で動作する（pytypedstream は任意、ティア 2／3 は標準ライブラリのみ）
5. すべての参照リンクが相対パスである
6. 変更内容を evolution-log.md に追記する

実行後の振り返り

このスキルの実行が完了したら、終了前に次の点を確認してください。

1. コマンドは成功しましたか？ — 成功しなかった場合は、失敗の原因となった指示またはエラーテーブルを修正してください。
2. パラメーターまたは出力に変更はありましたか？ — 基盤となるツールのインターフェースに変更があった場合は、使用例とパラメーターテーブルを更新して、実際の内容に合わせてください。
3. 回避策が必要でしたか？ — 即興の対応（異なるフラグ、追加の手順など）が必要だった場合は、次回の実行で同じ回避策が不要になるよう、この SKILL.md を更新してください。

問題が実在し、再現可能な場合にのみ更新してください。推測に基づく更新はしないでください。