Apple iMessage Databases on macOS and iOS

Version 1.0, compiled 2026-03-27
Covers macOS Tahoe / iOS 26.x and earlier

Reverse Engineering Note: Apple does not publish official documentation for the chat.db/sms.db schema; virtually all information in this report was drawn from public reverse engineering documentation and open source tool implementations.

Note: This report was compiled in part by automation based on curated sample data, including GitHub repository files, secondary web sources, and both synthetic and real-world sample data.

Table of Contents

1. Executive Summary
2. Architecture Overview
3. Table: message
4. Table: chat
5. Table: handle
6. Table: attachment
7. Table: chat_message_join
8. Table: chat_handle_join
9. Table: message_attachment_join
10. Attachment Storage Mechanism
11. Special Encodings
12. iOS sms.db vs macOS chat.db
13. iCloud Sync Effects
14. Reconstructing a Full Conversation (SQL)
15. Tool Comparison Table
16. Tool Details
17. Recommended Archival Approach
18. Key Repositories Summary
19. Confidence Assessment
20. Footnotes & Citations

1. Executive Summary

Apple’s Messages application stores all iMessage, SMS, MMS, and (as of macOS Sequoia/iOS 18+) RCS messages in a SQLite 3 database called chat.db on macOS (~/Library/Messages/chat.db) and sms.db on iOS (/private/var/mobile/Library/SMS/sms.db). Both databases share a nearly identical schema consisting of approximately seven primary tables linked by join tables, with the message table alone carrying 94+ columns as of macOS Tahoe 26.

The primary encoding challenge is the attributedBody BLOB column — a serialized NSMutableAttributedString in Apple’s proprietary typedstream/NSKeyedArchiver format — which since macOS Ventura (13) has replaced the plain-text text column for many message types.

An ecosystem of open-source tools exists for extraction, with ReagentX/imessage-exporter (Rust, 5000+ stars) being the most comprehensive and actively maintained, offering TXT and HTML export with full attachment embedding and media conversion.

caleb531/imessage-conversation-analyzer (Python) provides a high-level analytics and transcript API built on pandas/duckdb. Archival best practice is HTML with embedded or co-located attachments for human readability, plus a parallel JSON/SQLite dump for machine readability.

2. Architecture Overview

File Locations

Note: macOS 10.14+ requires Full Disk Access for reading chat.db. Grant access in System Settings → Privacy & Security → Full Disk Access.¹

Entity-Relationship ASCII Diagram

 ┌──────────────┐        ┌───────────────────────┐        ┌──────────────┐
 │    handle    │        │   chat_handle_join    │        │     chat     │
 │──────────────│        │───────────────────────│        │──────────────│
 │ ROWID (PK)   │◄───────│ handle_id (FK)        │        │ ROWID (PK)   │
 │ id           │        │ chat_id (FK)          │───────►│ guid         │
 │ country      │        └───────────────────────┘        │ style        │
 │ service      │                                         │ chat_ident.  │
 │ uncanonicali.│                                         │ display_name │
 │ person_centr.│                                         │ room_name    │
 └──────┬───────┘                                         │ service_name │
        │                                                 └──────┬───────┘
        │ handle_id                                              │ chat_id
        ▼                                                        ▼
 ┌──────────────────────────────────────────────────────────────────────────┐
 │                           message                                        │
 │──────────────────────────────────────────────────────────────────────────│
 │ ROWID (PK)  guid  text  attributedBody  handle_id  date  date_read       │
 │ date_delivered  is_from_me  service  is_read  is_delivered  is_sent      │
 │ associated_message_guid  associated_message_type  (tapbacks/reactions)   │
 │ reply_to_guid  thread_originator_guid  (threading)                       │
 │ payload_data  balloon_bundle_id  (rich media / app integrations)         │
 │ group_title  cache_roomnames  item_type  (group chat metadata)           │
 │ date_edited  date_retracted  (edit/unsend, macOS Ventura+)               │
 │ expressive_send_style_id  (bubble/screen effects)                        │
 │ ... (94 total columns as of macOS Tahoe 26)                              │
 └──────────────────────────────┬───────────────────────────────────────────┘
                                │
        ┌───────────────────────┴───────────────────────┐
        │                                               │
        ▼                                               ▼
 ┌──────────────────────┐                 ┌──────────────────────────┐
 │  chat_message_join   │                 │  message_attachment_join │
 │──────────────────────│                 │──────────────────────────│
 │ chat_id (FK→chat)    │                 │ message_id (FK→message)  │
 │ message_id (FK→msg)  │                 │ attachment_id (FK→att.)  │
 └──────────────────────┘                 └────────────┬─────────────┘
                                                       │
                                                       ▼
                                          ┌────────────────────────┐
                                          │      attachment        │
                                          │────────────────────────│
                                          │ ROWID (PK)  guid       │
                                          │ filename  mime_type    │
                                          │ uti  total_bytes       │
                                          │ transfer_state         │
                                          │ is_outgoing is_sticker │
                                          │ created_date           │
                                          │ ... (24 columns)       │
                                          └────────────────────────┘

Cardinality:

• handle ↔ chat: many-to-many via chat_handle_join (group chats have N handles)
• chat ↔ message: many-to-many via chat_message_join
• message ↔ attachment: many-to-many via message_attachment_join

3. Table: message

Location: chat.db and sms.db
Description: The central table. Each row is one discrete message unit, including reactions/tapbacks (which are themselves stored as message rows with associated_message_guid set).²

As of macOS Tahoe 26 / iOS 26, the message table has 94 columns. The full schema below is sourced from documentation in ReagentX/imessage-exporter.³

Column Listing

Key Indexes (known):

• Unique index on guid
• Index on handle_id
• Index on date

4. Table: chat

Description: One row per conversation thread. Covers both 1:1 chats and group chats. The style field distinguishes them.⁴

style values:

5. Table: handle

Description: Represents a unique contact identifier (phone number or email). Multiple handles can map to the same real person (e.g., phone + email for the same Apple ID).⁶

Notes on deduplication: The same physical person may have 2–4 handle rows (SMS phone, iMessage phone, iMessage email). Tools like imessage-exporter handle this via person_centric_id or by matching on chat_handle_join.⁷

6. Table: attachment

Description: Metadata for every file attachment. Does not contain the file itself — only a path and metadata. The file lives on disk at the filename path.⁸

7. Table: chat_message_join

Description: Many-to-many join between chat and message. Allows a message to belong to a chat.⁹

CREATE TABLE chat_message_join (
    chat_id    INTEGER REFERENCES chat(ROWID)    ON DELETE CASCADE,
    message_id INTEGER REFERENCES message(ROWID) ON DELETE CASCADE,
    PRIMARY KEY (chat_id, message_id)
);

Index: Both chat_id and message_id are individually indexed for lookup performance.

8. Table: chat_handle_join

Description: Many-to-many join between chat and handle. Defines group membership. For 1:1 chats, there is exactly one handle_id row.¹⁰

CREATE TABLE chat_handle_join (
    chat_id   INTEGER REFERENCES chat(ROWID)   ON DELETE CASCADE,
    handle_id INTEGER REFERENCES handle(ROWID) ON DELETE CASCADE,
    PRIMARY KEY (chat_id, handle_id)
);

9. Table: message_attachment_join

Description: Many-to-many join between message and attachment.¹¹

CREATE TABLE message_attachment_join (
    message_id    INTEGER REFERENCES message(ROWID)    ON DELETE CASCADE,
    attachment_id INTEGER REFERENCES attachment(ROWID) ON DELETE CASCADE,
    PRIMARY KEY (message_id, attachment_id)
);

Note: A single message can have multiple attachments (e.g., a photo burst or a message with an image + a document). The cache_has_attachments flag on message is a performance hint but should not be relied on exclusively.

10. Attachment Storage Mechanism

On-Disk Layout (macOS)

~/Library/Messages/
├── chat.db                    # Main SQLite database
├── chat.db-shm                # WAL shared memory
├── chat.db-wal                # Write-ahead log (uncommitted transactions)
├── NicknameCaches/            # Cached contact photos
└── Attachments/
    ├── 00/
    │   └── 00/
    │       └── <GUID>/
    │           └── image.jpg
    ├── 01/
    │   └── 01/
    │       └── <GUID>/
    │           └── video.mov
    ...
    └── FF/
        └── ...

Attachments are stored in a two-level hex-prefix directory scheme (first two hex digits of a hash form the first directory, second two form the second), then a GUID-named subdirectory containing the actual file.¹²

attachment.filename Format

The filename column stores a tilde-expanded absolute path on macOS:

~/Library/Messages/Attachments/0A/10/B5C6D2E4-FFFF-AAAA-BBBB-123456789ABC/IMG_0042.HEIC

When accessed programmatically, expand ~ with os.path.expanduser() in Python or $HOME in shell.

Format Notes

• Images are commonly stored as HEIC (High Efficiency Image Container), not JPEG. Older attachments may be JPEG/PNG.
• Videos are stored as MOV (QuickTime), sometimes H.265/HEVC encoded.
• Audio messages use CAF (Core Audio Format) or AMR.
• imessage-exporter can optionally convert: HEIC→JPEG, MOV→MP4, CAF/AMR→MP4, HEICS→GIF.¹³

iOS Backup Attachments

In an iTunes/Finder backup, attachment files are also hash-renamed (SHA-1 of domain + relative path), located in:

~/Library/Application Support/MobileSync/Backup/<UUID>/

The Manifest.db SQLite file in the backup root maps the hash filenames to their original paths.¹⁴

iCloud Optimization

When iCloud Messages is enabled with “Optimize Storage”, attachments may be evicted from local disk and replaced with a stub. The attachment.transfer_state will reflect this (state 8 means not locally available). The file referenced by filename will not exist on disk until re-downloaded. The total_bytes field still records the original size.¹⁵

11. Special Encodings

11.1 The date Column — Apple Epoch (Nanoseconds)

All timestamp columns (date, date_read, date_delivered, date_edited, date_retracted, etc.) use the Core Data timestamp format: integer nanoseconds since 2001-01-01 00:00:00 UTC (the “Cocoa epoch” or “Apple epoch”).

Conversion to Unix timestamp:

APPLE_EPOCH_OFFSET = 978_307_200  # seconds from Unix epoch to Apple epoch
unix_timestamp = (apple_ns_timestamp / 1_000_000_000) + APPLE_EPOCH_OFFSET

In SQL (SQLite):

-- Convert to human-readable UTC
datetime(date / 1000000000 + strftime('%s', '2001-01-01'), 'unixepoch') AS readable_date

This exact expression is taken from the SQL queries of caleb531/imessage-conversation-analyzer.¹⁶

Historical note: Prior to macOS Sierra (10.12) / iOS 11, the date column stored seconds (not nanoseconds) since the same epoch. Databases with values < ~1.5×10¹² are in seconds; values ≥ ~1.5×10¹² are in nanoseconds. This epoch is distinct from the Unix epoch (1970-01-01) by exactly 978,307,200 seconds.¹⁷

11.2 The attributedBody BLOB — typedstream / NSKeyedArchiver

What it is: A serialized NSMutableAttributedString — a string with attached ranges of formatting, link metadata, mentions, OTP codes, and more.

Why it matters: Starting with approximately macOS Ventura (13.x), Apple began storing message text only in attributedBody (with text being NULL) for some message types, particularly messages received via iMessage with formatting, stickers, or after the schema migration.¹⁸

Format:

• Pre-Ventura: Uses the legacy typedstream binary format (GNUstep-era Objective-C serialization)
• Ventura+: May use NSKeyedArchiver binary plist, which wraps an NSAttributedString

Decoding approaches:

Heuristic Python decode (from my-other-github-account/imessage_tools):

attributed_body = attributed_body.decode('utf-8', errors='replace')
if "NSNumber" in attributed_body:
    attributed_body = str(attributed_body).split("NSNumber")[0]
    if "NSString" in attributed_body:
        attributed_body = str(attributed_body).split("NSString")[1]
        if "NSDictionary" in attributed_body:
            attributed_body = str(attributed_body).split("NSDictionary")[0]
            attributed_body = attributed_body[6:-12]  # strip framing bytes

This is fragile but works for plain-text extraction in many cases.²²

Inner structure of a decoded attributedBody:

NSMutableAttributedString {
    string: "Hello @Alice, check this out!",
    attributes: [
        (range: 6-11, NSPersonNameComponentsMentionAttribute: <Alice's handle>),
        (range: 13-28, NSLinkAttributeName: "https://example.com")
    ]
}

11.3 Reactions / Tapbacks

Tapbacks are stored as separate message rows with associated_message_guid pointing to the original message’s guid. They are identified by associated_message_type being in the range 2000–2005 (received) or -2000 to -2005 (removed).²³

associated_message_type values:

iOS 18 / macOS Sequoia+: Emoji reactions (any emoji) are stored in associated_message_emoji (column 85) with associated_message_type = 2006 for the custom emoji reaction.²⁴

SQL to find all tapbacks:

SELECT
    m.guid,
    m.associated_message_guid,
    m.associated_message_type,
    m.is_from_me,
    h.id AS sender,
    datetime(m.date / 1000000000 + strftime('%s', '2001-01-01'), 'unixepoch') AS sent_at
FROM message m
LEFT JOIN handle h ON m.handle_id = h.ROWID
WHERE m.associated_message_type BETWEEN 2000 AND 2006;

11.4 payload_data — NSKeyedArchiver Rich Content

The payload_data BLOB is an NSKeyedArchiver-encoded binary plist. It stores structured data for:

• URL Previews: cached title, description, image, canonical URL
• Apple Pay: transaction amount, currency, direction
• App Integrations (balloon_bundle_id): Apple Fitness, SharePlay, Check In, Find My, Polls
• Apple Music: album art, preview stream URL, lyrics
• Apple Maps: Placemark data

Decoding requires either native ObjC/Swift or crabstep (Rust). The imessage-exporter project is the only cross-platform open-source tool currently known to fully decode this.²⁵

11.5 Threaded Replies

Threaded replies (iMessage reply feature, introduced macOS Monterey):

• reply_to_guid: direct parent message GUID
• thread_originator_guid: the root of the thread chain
• thread_originator_part: part index for multi-part root messages

12. iOS sms.db vs macOS chat.db

The schemas are nearly identical. Key differences:

imessage-exporter explicitly notes: “Jailbroken iOS filesystem data uses sms.db, which follows the same schema as macOS chat.db. Resolved as a macOS database with an alternate attachment root.”²⁶

Tables present in both databases:

• message, chat, handle, attachment
• chat_message_join, chat_handle_join, message_attachment_join
• deleted_messages, kvtable, _SqliteDatabaseProperties
• sync_deleted_messages, sync_deleted_chats, sync_deleted_attachments

iOS backup sms.db hash: In an iTunes/Finder backup, sms.db is stored under the SHA-1 hash filename 3d0d7e5fb2ce288813306e4d4636395e047a3d28.²⁷

13. iCloud Sync Effects

When Messages in iCloud is enabled (Settings → [your Apple ID] → iCloud → Messages):

1. All devices share one canonical message history. Deletions on one device propagate to all.
2. The macOS chat.db becomes authoritative — it can contain messages from all your Apple devices.
3. Storage optimization: Attachments may be locally evicted (transfer_state = 8). The database row remains but filename points to a non-existent local file.
4. ck_sync_state and ck_record_id columns track CloudKit synchronization status.
5. Forensic implication: After deletion with iCloud sync on, the message is removed from all devices and backups made after the fact. Pre-deletion backups are the only recovery path.²⁸
6. WAL file: The chat.db-wal (write-ahead log) may contain uncommitted or recently committed data not yet checkpointed into the main database file. This is relevant for forensic acquisition — always capture chat.db, chat.db-shm, and chat.db-wal together.²⁹

14. Reconstructing a Full Conversation (SQL)

The following query reconstructs a complete conversation thread with sender information, timestamps, and identifies reactions. This is the pattern used by tools like caleb531/imessage-conversation-analyzer.³⁰

-- Get all messages in a conversation, with sender identity and human-readable timestamps
SELECT
    m.ROWID,
    m.guid,
    -- Prefer text; fall back to note that attributedBody needs decoding
    COALESCE(m.text, '(attributedBody - requires decoding)') AS body,
    datetime(m.date / 1000000000 + strftime('%s', '2001-01-01'), 'unixepoch') AS sent_at,
    CASE WHEN m.is_from_me = 1 THEN 'Me' ELSE h.id END AS sender,
    m.service,
    m.is_from_me,
    -- Identify tapback reactions
    CASE
        WHEN m.associated_message_type BETWEEN 2000 AND 2005 THEN 'TAPBACK'
        WHEN m.associated_message_type BETWEEN -2005 AND -2000 THEN 'TAPBACK_REMOVED'
        ELSE 'MESSAGE'
    END AS message_kind,
    m.associated_message_guid,
    m.associated_message_type,
    m.cache_has_attachments
FROM message m
LEFT JOIN handle h ON m.handle_id = h.ROWID
WHERE m.ROWID IN (
    SELECT message_id
    FROM chat_message_join
    WHERE chat_id = (
        -- Replace with actual chat_identifier, e.g. '+15551234567' or group UUID
        SELECT ROWID FROM chat WHERE chat_identifier = '<TARGET_IDENTIFIER>' LIMIT 1
    )
)
ORDER BY m.date ASC;

To also get attachments for each message:

-- Attachments query (from caleb531/imessage-conversation-analyzer/ica/queries/attachments.sql)
SELECT
    a.ROWID,
    a.mime_type,
    a.filename,
    maj.message_id,
    datetime(m.date / 1000000000 + strftime('%s', '2001-01-01'), 'unixepoch') AS datetime,
    m.is_from_me,
    h.id AS sender_handle
FROM attachment a
INNER JOIN message_attachment_join maj ON a.ROWID = maj.attachment_id
INNER JOIN message m ON m.ROWID = maj.message_id
LEFT JOIN handle h ON m.handle_id = h.ROWID
WHERE maj.message_id IN (
    SELECT message_id
    FROM chat_message_join
    WHERE chat_id IN (/* your chat_ids */)
);

15. Tool Comparison Table

¹ Star counts as of March 2026 via GitHub API.
² Last commit / push date as of research date.

16. Tool Details

16.1 ReagentX/imessage-exporter

Repository: https://github.com/ReagentX/imessage-exporter
Language: Rust
Stars: ~5,017 (most starred iMessage tool on GitHub)
License: GPL-3.0

Architecture: Two crates:

• imessage-database: library crate providing Rust models of all iMessage tables and a typedstream/NSKeyedArchiver decoder (uses the crabstep and crabapple sub-crates)
• imessage-exporter: binary crate providing CLI with --format txt|html, --db-path, --attachment-manager, --contact-filter, --start-date, --end-date

Supported sources:

• Local macOS chat.db
• Unencrypted iOS backups (resolved normally)
• Encrypted iOS backups (via crabapple)
• Jailbroken iOS sms.db

Supported features as of macOS Tahoe 26.4: iMessage, RCS, SMS, MMS, multi-part messages, replies/threads, formatted text (mentions, hyperlinks, OTP/2FA, unit conversions), expressives (bubble+screen), tapbacks, stickers, Apple Pay, URL previews, App Integrations (Fitness, SharePlay, Check In, Find My, Polls), handwritten messages (SVG), Digital Touch, edited/unsent messages, satellite messages, genmoji.³²

Attachment conversion options:

• HEIC → JPEG
• HEICS → GIF (animated sticker sequences)
• MOV → MP4
• CAF/AMR → MP4

Installation:

cargo install imessage-exporter
# or
brew install imessage-exporter

Usage example:

imessage-exporter --format html --db-path ~/Library/Messages/chat.db \
    --attachment-manager full --output ./export/

16.2 caleb531/imessage-conversation-analyzer

Repository: https://github.com/caleb531/imessage-conversation-analyzer
Language: Python 3.9+
Stars: ~33
License: MIT
Version: 3.1.0
Package: pip install imessage-conversation-analyzer or uv tool install imessage-conversation-analyzer

Key dependencies (from pyproject.toml):³³

• pandas — dataframe operations
• pyarrow — efficient columnar I/O
• tabulate — pretty-print tables
• openpyxl — Excel output
• pytypedstream — attributedBody decoding
• phonenumbers — phone number normalization
• tzlocal — local timezone detection
• emoji — emoji analytics
• duckdb — in-memory SQL on dataframes

Core SQL queries (in ica/queries/):

• messages.sql — joins message, handle, chat_message_join; converts date epoch; selects attributedBody³⁴
• attachments.sql — joins attachment, message_attachment_join, message, handle³⁵
• contact.sql — resolves contacts from macOS Contacts database

Built-in analyzers:

1. message_totals — counts by person, reactions, days messaged
2. attachment_totals — by MIME type, including Spotify/YouTube/Apple Music link detection
3. most_frequent_emojis — top-10 emoji frequency
4. totals_by_day — per-day breakdown
5. transcript — full printable transcript
6. count_phrases — case-insensitive/regex phrase counting
7. from_sql — arbitrary SQL against an in-memory DuckDB populated with the conversation dataframes

Output formats: csv, excel/xlsx, markdown/md, json, default table

16.3 Kadin2048 / ichat_to_eml (upstream)

Repository: https://github.com/kadin2048/ichat_to_eml
Language: Python
License: GPL-3.0
Last active: 2021

Purpose: Convert Apple iChat / Messages archive files (.chat typedstream, .ichat binary plist) into RFC-compliant EML files (with optional HTML part and embedded attachments). The upstream project provides a pure-Python parser for NeXT “typedstream” files and binary PList (.ichat) files, and includes logic to extract NSAttachment/NSFileWrapper serialized attachments.

Evidence (selected code):

• Imports the typedstream parser and bplist decoder: ichat_to_eml.py:28-31³⁶
• Detects .chat vs .ichat and uses typedstream.unarchive_from_file and bplist_decode.bplist_to_conv: ichat_to_eml.py:69-82³⁷
• Handles NSAttachment / NSFileWrapper serialized attachments and decodes via rtfd_decode.decode_rtfd: ichat_to_eml.py:173-186³⁸
• README credits python-typedstream and ccl-bplist: README.md³⁹

Implications:

• Highly relevant to chat.db research for decoding older iChat .chat typedstream logs and newer .ichat binary plists. Use the upstream repo for attribution in any published research.
• Note license: GPL-3.0 — reuse or redistribution must comply with GPL terms.

Data schema exposed to analyzers:

messages dataframe: | Column | Type | Description | |——–|——|————-| | ROWID | int | Primary key | | text | str | Decoded message body | | datetime | datetime | Localized timestamp | | sender_display_name | str | Resolved name or “Me” | | sender_handle | str | Phone/email | | is_from_me | bool | Direction | | is_reaction | bool | Is tapback |

attachments dataframe: | Column | Type | Description | |——–|——|————-| | ROWID | int | Primary key | | filename | str | File path | | mime_type | str | MIME type | | message_id | int | Parent message ROWID | | datetime | datetime | Localized timestamp | | is_from_me | bool | Direction | | sender_handle | str | Phone/email |

16.3 my-other-github-account/imessage_tools

Repository: https://github.com/my-other-github-account/imessage_tools
Language: Python
Stars: ~124

Key function read_messages(db_location, n, self_number, human_readable_date):

• Joins message LEFT JOIN handle
• Reads text column; falls back to binary-parse attributedBody using the heuristic string-split method
• Converts date using: (date + unix_timestamp_of_2001_01_01_in_ns) / 1e9
• Queries chat table for room_name → display_name mapping
• Returns list of dicts with: rowid, date, body, phone_number, is_from_me, cache_roomname, group_chat_name

Also provides send_message(message, phone_number, group_chat=False) — sends via osascript to the Messages app. Writes message to a temp file to handle special characters.⁴⁰

16.4 niftycode/imessage_reader

Repository: https://github.com/niftycode/imessage_reader
Language: Python 3.9+
Stars: ~116
Package: pip install imessage-reader

Description: A forensic-focused tool that reads chat.db. Notably can be used on Linux by specifying a path to a copied chat.db.⁴¹

Output formats: Terminal display, Excel (.xlsx via openpyxl), SQLite database

Fields extracted: user_id (phone/email), message, date/time, service (iMessage/SMS), account, is_from_me

Does not decode attributedBody — relies solely on the text column.

17. Recommended Archival Approach

For Long-Term Human-Readable Archives

Primary: HTML Export via imessage-exporter

# Install
brew install imessage-exporter

# Export all conversations to HTML with embedded attachments
imessage-exporter \
    --format html \
    --db-path ~/Library/Messages/chat.db \
    --attachment-manager full \
    --output ~/iMessage-Archive/

# Or with attachment format conversion for maximum portability
imessage-exporter \
    --format html \
    --db-path ~/Library/Messages/chat.db \
    --attachment-manager clone \
    --output ~/iMessage-Archive/

Produces one HTML file per conversation, with inline images/video/audio, thread rendering, tapbacks, and stickers.⁴²

For Machine-Readable / LLM / Analysis Archives

Secondary: JSON via caleb531/imessage-conversation-analyzer

ica transcript -c 'Contact Name' -f json -o conversation_export.json

Or programmatic:

import ica, json, pandas as pd

dfs = ica.get_dataframes(contacts=["Jane Doe"])
# Export messages as JSON
dfs.messages.to_json("messages.json", orient="records", date_format="iso")
dfs.attachments.to_json("attachments.json", orient="records", date_format="iso")

For Forensic / Database-Level Archives

Tertiary: SQLite Copy

Simply copy the database file (and attachments directory) with the WAL files:

cp ~/Library/Messages/chat.db ~/iMessage-Backup/chat.db
cp ~/Library/Messages/chat.db-shm ~/iMessage-Backup/chat.db-shm
cp ~/Library/Messages/chat.db-wal ~/iMessage-Backup/chat.db-wal
rsync -av ~/Library/Messages/Attachments/ ~/iMessage-Backup/Attachments/

Recommended Archive Checklist

• Export HTML (imessage-exporter) for browsability
• Export JSON/CSV (ICA or custom SQL) for machine analysis
• Copy raw chat.db + WAL files for forensic completeness
• Copy Attachments/ directory preserving directory structure
• Convert HEIC/MOV to JPEG/MP4 if long-term accessibility is critical
• Note the macOS/iOS version at time of export (schema changes between versions)
• Grant Full Disk Access to Terminal before running any tool
• If iCloud Messages is enabled, run export promptly before any deletions propagate

Standard Formats and Interoperability

There is currently no official or widely-adopted standard format for iMessage archives. The community has converged on:

• HTML (imessage-exporter) — most human-readable, self-contained
• JSON — most machine-readable
• SQLite (raw chat.db) — most faithful, requires tools to query

No XML standard (e.g., like Android’s SMS Backup & Restore XML format) exists for iMessage. The LangChain IMessageChatLoader exports to ChatSession Python objects, easily serializable to JSON.⁴³

18. Key Repositories Summary

19. Confidence Assessment

Limitations:

• Apple does not publicly document the chat.db schema; all column semantics are reverse-engineered
• The schema evolves with each major macOS/iOS release; new columns are added (86+ → 94 columns in Tahoe)
• payload_data / attributedBody internal structure is the least-documented aspect

20. Footnotes & Citations

20. Footnotes & Citations