Chat Database (chat.db)

Overview

The chat.db SQLite database is the primary data store for Apple Messages.app. It contains every message sent and received through iMessage, SMS, MMS, and RCS, along with conversation metadata, contact handles, and attachment references. The database uses a relational model with join tables linking messages to chats and attachments.

File Locations

Always collect all three files together to ensure database consistency. The WAL file may contain the most recent messages not yet checkpointed into the main database.

Database Schema

message Table

The message table is the core table containing every message. On macOS 15 Sequoia, this table has approximately 60 columns.

CREATE TABLE message (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    guid TEXT UNIQUE NOT NULL,
    text TEXT,                              -- Plain text content (pre-Ventura)
    attributedBody BLOB,                    -- NSKeyedArchiver content (Ventura+)
    replace INTEGER DEFAULT 0,
    service_center TEXT,
    handle_id INTEGER DEFAULT 0,            -- FK to handle.ROWID
    subject TEXT,
    country TEXT,
    version INTEGER DEFAULT 0,
    type INTEGER DEFAULT 0,
    service TEXT,                           -- 'iMessage', 'SMS', 'MMS', 'RCS'
    account TEXT,
    account_guid TEXT,
    error INTEGER DEFAULT 0,
    date INTEGER,                           -- Core Data nanoseconds since 2001
    date_read INTEGER DEFAULT 0,
    date_delivered INTEGER DEFAULT 0,
    is_delivered INTEGER DEFAULT 0,
    is_finished INTEGER DEFAULT 0,
    is_from_me INTEGER DEFAULT 0,           -- 1 = sent, 0 = received
    is_empty INTEGER DEFAULT 0,
    is_delayed INTEGER DEFAULT 0,
    is_auto_reply INTEGER DEFAULT 0,
    is_prepared INTEGER DEFAULT 0,
    is_read INTEGER DEFAULT 0,
    is_system_message INTEGER DEFAULT 0,
    is_sent INTEGER DEFAULT 0,
    has_dd_results INTEGER DEFAULT 0,
    is_service_message INTEGER DEFAULT 0,
    is_forward INTEGER DEFAULT 0,
    was_downgraded INTEGER DEFAULT 0,
    is_archive INTEGER DEFAULT 0,
    cache_has_attachments INTEGER DEFAULT 0,
    cache_roomnames TEXT,
    was_data_detected INTEGER DEFAULT 0,
    was_deduplicated INTEGER DEFAULT 0,
    is_audio_message INTEGER DEFAULT 0,
    is_played INTEGER DEFAULT 0,
    date_played INTEGER DEFAULT 0,
    item_type INTEGER DEFAULT 0,
    other_handle INTEGER DEFAULT -1,
    group_title TEXT,
    group_action_type INTEGER DEFAULT 0,
    share_status INTEGER DEFAULT 0,
    share_direction INTEGER DEFAULT 0,
    is_expirable INTEGER DEFAULT 0,
    expire_state INTEGER DEFAULT 0,
    message_action_type INTEGER DEFAULT 0,
    message_source INTEGER DEFAULT 0,
    associated_message_guid TEXT,           -- For tapbacks and replies
    associated_message_type INTEGER DEFAULT 0, -- Reaction code (2000-3005)
    balloon_bundle_id TEXT,
    payload_data BLOB,
    expressive_send_style_id TEXT,
    associated_message_range_location INTEGER DEFAULT 0,
    associated_message_range_length INTEGER DEFAULT 0,
    time_expressive_send_played INTEGER DEFAULT 0,
    message_summary_info BLOB,
    ck_sync_state INTEGER DEFAULT 0,        -- CloudKit sync status
    ck_record_id TEXT,
    ck_record_change_tag TEXT,
    destination_caller_id TEXT,
    sr_ck_sync_state INTEGER DEFAULT 0,
    is_corrupt INTEGER DEFAULT 0,
    reply_to_guid TEXT,
    sort_id INTEGER,
    is_spam INTEGER DEFAULT 0,
    has_unseen_mention INTEGER DEFAULT 0,
    thread_originator_guid TEXT,            -- Inline reply parent (Big Sur+)
    thread_originator_part TEXT,
    syndication_ranges TEXT,
    was_delivered_quietly INTEGER DEFAULT 0,
    did_notify_recipient INTEGER DEFAULT 0,
    synced_syndication_ranges TEXT,
    fallback_hash TEXT
);

handle Table

Handles represent contact identifiers -- phone numbers or email addresses associated with a messaging service.

CREATE TABLE handle (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    id TEXT NOT NULL,                       -- Phone number or email address
    country TEXT,
    service TEXT NOT NULL,                  -- 'iMessage' or 'SMS'
    uncanonicalized_id TEXT,
    person_centric_id TEXT,                 -- Cross-device person identifier
    UNIQUE(id, service)
);

chat Table

Chats represent conversations. Each chat has a style that distinguishes individual from group conversations.

CREATE TABLE chat (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    guid TEXT UNIQUE NOT NULL,              -- e.g., 'iMessage;-;+15551234567'
    style INTEGER,                          -- 43 = group, 45 = individual
    state INTEGER,
    account_id TEXT,
    properties BLOB,
    chat_identifier TEXT,                   -- Contact ID or group ID
    service_name TEXT,
    room_name TEXT,
    account_login TEXT,
    is_archived INTEGER DEFAULT 0,
    last_addressed_handle TEXT,
    display_name TEXT,                      -- User-assigned group name
    group_id TEXT,
    is_filtered INTEGER DEFAULT 0,
    successful_query INTEGER DEFAULT 1,
    engram_id TEXT,
    server_change_token TEXT,
    ck_sync_state INTEGER DEFAULT 0,
    original_group_id TEXT,
    last_read_message_timestamp INTEGER DEFAULT 0,
    sr_server_change_token TEXT,
    sr_ck_sync_state INTEGER DEFAULT 0,
    cloudkit_record_id TEXT,
    sr_cloudkit_record_id TEXT,
    last_addressed_sim_id TEXT,
    is_blackholed INTEGER DEFAULT 0,
    syndication_date INTEGER DEFAULT 0,
    syndication_type INTEGER DEFAULT 0
);

Chat GUID format:

Join Tables

-- Links handles (participants) to chats
CREATE TABLE chat_handle_join (
    chat_id INTEGER REFERENCES chat(ROWID) ON DELETE CASCADE,
    handle_id INTEGER REFERENCES handle(ROWID) ON DELETE CASCADE,
    UNIQUE(chat_id, handle_id)
);

-- Links messages to chats
CREATE TABLE chat_message_join (
    chat_id INTEGER REFERENCES chat(ROWID) ON DELETE CASCADE,
    message_id INTEGER REFERENCES message(ROWID) ON DELETE CASCADE,
    message_date INTEGER DEFAULT 0,
    UNIQUE(chat_id, message_id)
);

-- Links attachments to messages
CREATE TABLE message_attachment_join (
    message_id INTEGER REFERENCES message(ROWID) ON DELETE CASCADE,
    attachment_id INTEGER REFERENCES attachment(ROWID) ON DELETE CASCADE,
    UNIQUE(message_id, attachment_id)
);

Key Fields for Analysis

Message Content Resolution

Starting with macOS 13 Ventura, the text column may be NULL and the content is stored in attributedBody as an NSKeyedArchiver (typedstream) blob. The parsing algorithm:

1. Locate the NSString marker in the blob bytes.
2. Skip a 5-byte preamble after the marker.
3. Read a length field: if the first byte is 0x81, read a 2-byte little-endian length; otherwise the single byte is the length.
4. Extract that many bytes as UTF-8 text.

Always check the text column first, then fall back to attributedBody.

Service Types

Key Boolean Columns

Useful Forensic Queries

-- All messages with sender info, ordered by date
SELECT
    datetime(m.date / 1000000000 + 978307200, 'unixepoch') AS date_utc,
    CASE WHEN m.is_from_me = 1 THEN 'SENT' ELSE 'RECEIVED' END AS direction,
    h.id AS contact,
    m.service,
    m.text,
    c.display_name AS group_name
FROM message m
LEFT JOIN handle h ON m.handle_id = h.ROWID
LEFT JOIN chat_message_join cmj ON m.ROWID = cmj.message_id
LEFT JOIN chat c ON cmj.chat_id = c.ROWID
WHERE m.text IS NOT NULL
ORDER BY m.date;

-- Count messages per contact
SELECT h.id, h.service, COUNT(*) AS msg_count
FROM message m
JOIN handle h ON m.handle_id = h.ROWID
GROUP BY h.id, h.service
ORDER BY msg_count DESC;

Timestamps

All date columns use Core Data nanosecond timestamps (nanoseconds since 2001-01-01 00:00:00 UTC).

Conversion:

UTC time = datetime((nanoseconds / 1,000,000,000) + 978307200, 'unixepoch')

Analysis Notes

• The chat_message_join table is essential for associating messages with their conversations. Without it, you cannot determine which chat a message belongs to.
• The person_centric_id column in the handle table links different service identifiers (phone and email) belonging to the same person across devices.
• A message with associated_message_type between 2000 and 3005 is a tapback reaction, not a standalone message. See Reactions.
• The cache_roomnames column in the message table may contain the group chat room name even when the chat table's display_name is NULL.

Version Differences

Tool Support

References

• Searching Your iMessage Database with SQL
• BlueBubbles - IMCore Documentation
• imessage_tools - AttributedBody Parser