Skip to content

Communications

sms.db: iMessage and SMS Database on iOS

Where iOS stores iMessage, SMS and MMS in sms.db, which backup reaches it, how to decode attributedBody and nanosecond Cocoa time, and edits and deletes.

Location
/private/var/mobile/Library/SMS/sms.db (backup: HomeDomain, Library/SMS/sms.db, fileID 3d0d7e5fb2ce288813306e4d4636395e047a3d28)
Proves
Who exchanged which messages and attachments with the device owner, over which service (iMessage, SMS, and RCS on iOS 18+), and when they were sent, delivered and read
Timestamps
Cocoa time in nanoseconds (divide by 1,000,000,000, then add 978307200 for Unix time), UTC
Acquisition
Unencrypted backup is enough (HomeDomain). A full file system extraction adds the -wal file, freelist pages and the Attachments tree without backup filtering
Retention
Until deleted by the user or by the Keep Messages setting (30 days, 1 year or forever). Since iOS 16, deleted messages sit in Recently Deleted for about 30 days
Collection
idevicebackup2, iMazing, Full file system extraction

What it is

sms.db is the SQLite database behind the Messages app. It stores iMessage conversations, carrier SMS and MMS, and on iOS 18 and later RCS messages, together with group chats, reactions, delivery and read receipts, and pointers to attachments on disk. It is usually the first database opened in an iPhone case because it reaches a standard backup and answers the core question: who talked to whom, and when.

The same schema is used by chat.db on macOS, so tooling and SQL carry over between the two, but the paths, the backup layout and some columns differ.

Where it lives

SourcePathNotes
Full file system/private/var/mobile/Library/SMS/sms.db (+ -wal, -shm)Collect all three files together
Attachments/private/var/mobile/Library/SMS/Attachments/xx/yy/<GUID>/Paths referenced by attachment.filename with a ~/ prefix
iTunes / Finder backupHomeDomain, Library/SMS/sms.dbStored as 3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28
Backup attachmentsMediaDomain, Library/SMS/Attachments/...One hashed file per attachment, resolved through Manifest.db

When Messages in iCloud is enabled, the local database can be a partial cache: older threads may only exist in iCloud, and deletions on one device propagate to the others. Record the setting (it appears in the device's Apple Account data and in the user's statement) before interpreting gaps.

What it proves

  • Sent and received messages per conversation, with the sender handle (phone number or email address) and the service used.
  • Group membership and group names, including renames over time.
  • Delivery and read state, where the sender's device received receipts.
  • Attachments: original file name, MIME type, size and on-disk path.
  • Tapbacks, replies and, from iOS 16, edited and unsent messages (see Key fields).

It does not prove who was holding the phone. A message "from me" was sent from this Apple Account, which may be signed in on a Mac or iPad as well. Check the account and service columns and the owner's other devices.

Key fields

Table / columnMeaning
message.textPlain text body. Often NULL on recent iOS, see attributedBody
message.attributedBodyTypedstream (NSArchiver) blob holding the formatted text; the body lives here when text is empty
message.handle_idForeign key to handle.ROWID (the other party)
message.is_from_me1 = sent by the device owner
message.serviceiMessage, SMS; RCS observed on iOS 18+
message.date, date_read, date_deliveredEvent times (see Timestamps)
message.associated_message_guid, associated_message_typeTapbacks and replies point at another message GUID
message.date_edited, date_retractedAdded with edit and unsend support (iOS 16); verify the exact columns on your build
message.message_summary_infoBlob that can hold edit history on iOS 16+
handle.idPhone number (E.164) or email address
chat.chat_identifier, display_nameConversation ID and group name
chat_message_join, chat_handle_joinLink messages and participants to chats
attachment.filename, mime_type, transfer_nameAttachment path and original name

Columns are added with most major releases. Run .schema message on your copy before trusting a canned query.

Timestamps

message.date and the other date columns hold Cocoa time in nanoseconds on every release this site covers (older iOS versions used seconds). Convert to Unix seconds by dividing by 10^9 and adding 978307200:

SELECT m.ROWID,
       datetime(m.date / 1000000000 + 978307200, 'unixepoch') AS sent_utc,
       CASE WHEN m.date_read > 0
            THEN datetime(m.date_read / 1000000000 + 978307200, 'unixepoch') END AS read_utc,
       h.id AS handle, m.service, m.is_from_me, m.text
FROM message m
LEFT JOIN handle h ON m.handle_id = h.ROWID
ORDER BY m.date;

All values are UTC. A date_read of 0 means "not read" or "no receipt", not 1 January 2001.

Retention

Messages stay until the user deletes them or the Keep Messages setting (30 days, 1 year, forever) purges them. From iOS 16, a deleted message moves to Recently Deleted for about 30 days before final removal, tracked through tables such as chat_recoverable_message_join. After that, SQLite freelist pages and the -wal file can still hold fragments, which is one reason a full file system extraction beats a backup for deleted content.

Collection

  • Backup: an unencrypted iTunes or Finder backup contains sms.db and the attachments. With libimobiledevice: idevicebackup2 backup --full ./case/backup. Then resolve the file through Manifest.db or copy 3d/3d0d7e5f... directly and rename it.
  • Full file system: collect sms.db, sms.db-wal, sms.db-shm and the Attachments tree. Never open the original: SQLite may checkpoint the WAL and change the evidence.
  • Hash the files immediately after extraction and record the extraction method and the iOS version.

Parsing

  • Messaging Forensics reads sms.db in the browser, including bodies that only exist in attributedBody, and merges it with WhatsApp ChatStorage.sqlite into one timeline.
  • iLEAPP parses messages and attachments from a backup or a full file system extraction.
  • imessage-exporter exports conversations to HTML or text and decodes typedstream bodies.
  • For raw SQL, join message to chat through chat_message_join, and treat attributedBody as binary: extracting its string requires a typedstream decoder, not a CAST.

Investigator tips

  • An empty text column is normal on current iOS. Do not report "no content" before decoding attributedBody.
  • Match handles to names with AddressBook after normalising phone numbers to E.164.
  • Correlate with CallHistory for FaceTime and phone calls to the same handle, and with KnowledgeC and Biome to see whether Messages was actually in focus when a message was read.
  • A message edited or unsent on iOS 16+ can keep traces of the original in message_summary_info and in the WAL. Report what is recoverable, and say which extraction level you had.
  • SMS sent while the device had no service can show a date long before date_delivered: read both before building a sequence.

See also