iMessage forensics: chat.db, sms.db and Cocoa time

Apple's Messages app keeps iMessage, SMS and RCS conversations in a single SQLite database. On a Mac it is chat.db; on an iPhone the same schema lives in sms.db. The schema is well known, but three details regularly produce wrong results: timestamps that switched from seconds to nanoseconds, message text that moved out of the text column, and reaction rows that look like ordinary messages. This guide covers acquisition, the tables, and those traps.

Acquisition on macOS

The database and its companions live in the user's library:

~/Library/Messages/chat.db
~/Library/Messages/chat.db-wal
~/Library/Messages/chat.db-shm
~/Library/Messages/Attachments/     attachment files, referenced by path

Copy all three database files together. SQLite in WAL mode writes new transactions to chat.db-wal first and folds them into the main file later, so recent messages, edits and deletions may exist only in the WAL. Copying chat.db alone gives you an older state of the conversation.

The folder is protected by macOS privacy controls. A Terminal or acquisition tool needs Full Disk Access (System Settings → Privacy & Security → Full Disk Access) before it can read ~/Library/Messages; without it, cp fails with "Operation not permitted" even as the owner. Quit Messages before copying on a live system to reduce the chance of a checkpoint during acquisition:

mkdir -p ~/case-0421/messages
cp -p ~/Library/Messages/chat.db* ~/case-0421/messages/
shasum -a 256 ~/case-0421/messages/*

If the Mac signs into the same Apple Account as an iPhone with Messages in iCloud enabled, chat.db may contain conversations that originated on the phone. Note this in your report when attributing a message to a device.

Acquisition from iOS

On iOS the database is /private/var/mobile/Library/SMS/sms.db. A full file-system extraction gives it to you directly. In an iTunes or Finder backup it is stored under a hashed name derived from its domain and path:

Domain:     HomeDomain
Path:       Library/SMS/sms.db
Backup file: 3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28

The hash is SHA-1 of HomeDomain-Library/SMS/sms.db, so it is the same on every backup. Encrypted backups must be decrypted first with the backup password. The file is plain SQLite once you have it. Attachments are stored separately under Library/SMS/Attachments/ in the same domain.

The WAL is merged in memory

Load chat.db and chat.db-wal together (drop both files, or the folder that holds them). The analyzer replays every committed WAL frame whose salts match the WAL header onto an in-memory copy of chat.db. That is the state SQLite itself would show when it opens the pair. Frames after the last commit record are ignored, and the parser notes report how many frames were merged and how many were ignored. Your files are never modified, and the evidence table hashes each of them as it was loaded.

To see what the WAL changed, run the analysis twice, once with the WAL and once without, and compare. Rows that differ are recent edits, deletions or messages that had not been checkpointed yet. If you prefer to merge with SQLite itself, do it on a copy:

cp -p ~/case-0421/messages/chat.db* ~/case-0421/work/
sqlite3 ~/case-0421/work/chat.db "PRAGMA wal_checkpoint(TRUNCATE);"

The core tables

Useful message columns: ROWID, guid, text, attributedBody, handle_id, is_from_me, service, date, date_read, date_delivered, date_edited, date_retracted, associated_message_type, associated_message_guid, thread_originator_guid. Column availability depends on the OS version, so check PRAGMA table_info(message) before writing a query.

is_from_me is the direction flag. For outgoing messages, handle_id may point to the recipient or be 0 in group chats, so derive the conversation from chat_message_join, not from the handle. service tells you the transport: iMessage, SMS, or RCS on recent versions. A single conversation can mix services when a message falls back to SMS.

Timestamps: Cocoa time, in two units

date and the other date_* columns count from the Cocoa epoch, 2001-01-01 00:00:00 UTC. The unit changed around macOS High Sierra and iOS 11: older databases store seconds, newer ones store nanoseconds. The magnitude tells you which one you have. Nine-digit values are seconds; 18-digit values are nanoseconds.

726876903                  seconds      → 2024-01-13 22:15:03 UTC
726876903000000000         nanoseconds  → 2024-01-13 22:15:03 UTC

SELECT datetime(
         CASE WHEN date > 1000000000000 THEN date / 1000000000 ELSE date END
         + 978307200, 'unixepoch') AS utc,
       is_from_me, service, text
  FROM message
 ORDER BY date;

A zero in date_read or date_delivered means "not recorded", not 2001-01-01. Filter zeros out before converting.

When text is NULL: attributedBody

On macOS Ventura and later (and matching iOS versions), many rows have text set to NULL while the message body sits in attributedBody, a BLOB in Apple's legacy typedstream format (the file starts with streamtyped). It holds an NSAttributedString; the plain text follows an NSString class marker with a length prefix, and the rest of the stream carries formatting, mentions and link attributes. Any query that reads only text will under-report these conversations, sometimes by most of their content.

Edits and unsends

Since iOS 16 and macOS Ventura, users can edit a sent iMessage for a short window and unsend it for a slightly shorter one:

• date_edited is non-zero when the message was edited. The visible text is the latest version. Earlier versions are kept in other structures (such as message_summary_info) whose format is not documented, so treat reconstruction of the original wording as tool-dependent and verify it.
• date_retracted is non-zero when the message was unsent. The row remains with its metadata, but the content is typically gone.

Both are strong signals in a timeline: an unsend shortly before a relevant event is often a finding by itself.

Tapbacks are rows too

Reactions ("tapbacks") are stored as their own message rows, linked to their target through associated_message_guid (a value like p:0/<target guid>). The type is in associated_message_type:

Newer releases added emoji and sticker reactions with further codes, so treat unknown values in the 2000 and 3000 ranges as reactions to investigate rather than as ordinary messages. Their text is usually a generated summary such as "Liked “See you at 8”". Counting these rows as messages inflates activity figures.

What the analyzer does with chat.db and sms.db

The parser detects the schema by the message, handle and chat_message_join tables, and handles chat.db and sms.db the same way:

• Joins message, handle, chat_message_join and chat; the conversation is named from display_name, then chat_identifier, then the handle.
• Converts date and date_edited from Cocoa time, detecting seconds versus nanoseconds by magnitude.
• Uses is_from_me for direction and shows the owner as (device owner).
• Falls back to attributedBody when text is empty, extracting the first string after the NSString marker. This is a targeted extraction, not a full typedstream decoder, so check unusual rows against the raw BLOB.
• Marks edited messages (date_edited) and flags unsent ones (date_retracted) as deleted.
• Prefixes rows with associated_message_type 2000–3005 with [tapback <code>] so they can be filtered. It does not resolve which message a tapback targets.
• Uses thread_originator_guid as the reply reference, and lists attachments from message_attachment_join with name, MIME type, size and on-disk path. The attachment files themselves are not loaded.
• Separates SMS and RCS rows from iMessage by the service column.

date_read and date_delivered are not shown, so use SQL for delivery and read analysis.

Try it

The analyzer runs in the browser and never uploads your data. Open the analyzer, then drop your merged chat.db or the extracted sms.db.

Related guides:

• WhatsApp forensics, including the iOS ChatStorage.sqlite, which uses the same Cocoa epoch
• Email header forensics
• What is messaging forensics?