Messages chat.db: macOS iMessage and SMS History

What it is

The Messages app stores its conversation history in chat.db, a SQLite database in WAL mode. It holds iMessage, SMS and RCS messages (SMS and RCS relayed from a paired iPhone), group and one-to-one chats, participant handles (phone numbers and email addresses), attachment metadata and, on recent releases, edit, unsend and "Recently Deleted" information. Attachment files themselves sit in a folder next to the database.

With Messages in iCloud enabled, the same history is synchronised across the user's devices, so the Mac can contain messages that were sent or received on an iPhone.

Where it lives

The whole ~/Library/Messages folder is covered by macOS privacy protection: Terminal or the collection tool needs Full Disk Access, even as root.

What it proves

• Message content, direction (is_from_me), sender or recipient handle, service (iMessage, SMS, RCS) and chat.
• When a message was sent or received (date), delivered (date_delivered) and read (date_read).
• Group chat membership and names (chat.display_name, chat_handle_join).
• Files exchanged: original name, MIME type, size and on-disk path.
• Edits (date_edited, message_summary_info) and messages placed in Recently Deleted (chat_recoverable_message_join).

It does not prove which physical device sent a message when Messages in iCloud is on, and a handle is an identifier, not a verified identity.

Key fields

Since Ventura, many rows have text set to NULL and keep the content only in attributedBody, a typedstream-archived NSAttributedString. Queries that read only text miss those messages.

Timestamps

On High Sierra (10.13) and later, date, date_read, date_delivered, date_edited and delete_date are nanoseconds since 2001-01-01 00:00:00 UTC. Older databases (and some rows carried over after upgrades) store seconds. A value around 10^17 to 10^18 is nanoseconds; around 10^8 to 10^9 is seconds. 0 means "not set" (for example never read).

SELECT datetime(date/1000000000 + 978307200, 'unixepoch') AS utc,
       CASE WHEN date_read > 0
            THEN datetime(date_read/1000000000 + 978307200, 'unixepoch') END AS read_utc
FROM message ORDER BY date DESC LIMIT 10;

Retention

Messages are kept until deleted, unless the user set Messages > Settings > General > "Keep messages" to a period other than Forever, in which case older conversations and their attachments are removed automatically. On Ventura and later, deleted messages go to Recently Deleted for up to 30 days, per Apple. Deleted rows can survive in the WAL, in free pages of the database and in notification records.

Collection

# Terminal with Full Disk Access; copy DB, WAL and SHM together
mkdir -p case/messages
ditto ~/Library/Messages case/messages/Messages
shasum -a 256 case/messages/Messages/chat.db*

• UAC messages artifact collects chat.db* and Attachments per user.
• mac_apt's IMESSAGE plugin reads chat.db and attachment paths from an image.
• Never open the live database: SQLite may checkpoint the WAL and destroy deleted-record traces.

Parsing

• imessage-exporter decodes attributedBody, edits, unsends and Recently Deleted, and exports to HTML or text:

imessage-exporter -f html -p case/messages/Messages/chat.db -o out/

• mac_apt IMESSAGE plugin and the Plaso imessage SQLite plugin.
• SQL overview (text column only; use a decoder for attributedBody):

SELECT datetime(m.date/1000000000 + 978307200, 'unixepoch') AS utc,
       c.chat_identifier, h.id AS handle, m.service,
       m.is_from_me, m.text, a.transfer_name, a.filename
FROM message m
LEFT JOIN chat_message_join cmj ON cmj.message_id = m.ROWID
LEFT JOIN chat c   ON c.ROWID = cmj.chat_id
LEFT JOIN handle h ON h.ROWID = m.handle_id
LEFT JOIN message_attachment_join maj ON maj.message_id = m.ROWID
LEFT JOIN attachment a ON a.ROWID = maj.attachment_id
ORDER BY m.date;

Investigator tips

• Count rows where text IS NULL AND attributedBody IS NOT NULL: if that number is high and your tool ignores attributedBody, your export is incomplete.
• Rows in chat_recoverable_message_join were deleted by the user but not yet purged; the delete_date itself is evidence of deletion activity.
• Gaps in ROWID sequences can indicate deleted messages. Carve the WAL and free pages before concluding nothing was deleted.
• Attachments referenced in attachment.filename but missing on disk may have been removed or offloaded; check iCloud Drive and CloudDocs activity and the file system.
• Compare with the notification database, which can hold message previews after the message was deleted, and with Mail for cross-channel communication.
• is_from_me = 1 with Messages in iCloud may have been sent from the user's iPhone; look for supporting activity on the Mac, such as Biome app focus records for the Messages app.