Why chat.db matters for iOS DFIR: Every iMessage and SMS message the device has sent or received sits in the chat.db SQLite database. Attachments live in a parallel Attachment table with binary references to the raw files on disk. The database survives logical acquisition (MobileBackup2) and is fully readable offline. The Sherlock iOS Analyzer parses chat.db directly and surfaces the messages, conversations and attachments in dedicated investigator views.
Where chat.db Actually Lives
The iMessage and SMS message database on iOS is stored at Library/SMS/sms.db within the app data container for com.apple.MobileSMS. On a logical MobileBackup2 acquisition (the universal path that any iPhone or iPad supports without jailbreak) the database is preserved as the file 3d0d7e5fb2ce288813306e4d4636395e047a3d28 in the Manifest.db index (that hex string is the SHA1 of the file path, which is how MobileBackup2 identifies files). Investigators handling the backup output extract the file from the manifest and open it with any SQLite tooling.
The parallel attachment binaries live under Library/SMS/Attachments/ within the same app data container. Each attachment has a UUID-named subdirectory containing the raw attachment file (image, video, audio, document) and metadata. The message table in chat.db references attachments through the Attachment table which stores the mapping between message rows and attachment binaries.
The chat.db Schema Investigators Need to Know
The chat.db schema has evolved across iOS versions but the core tables are stable. The primary tables investigators query:
message holds every message the device has sent or received. Key columns: ROWID (primary key), guid (message identifier), text (the message content), handle_id (foreign key to the handle table for sender attribution), service (iMessage, SMS, RCS), date (message timestamp in Apple epoch nanoseconds since 2001-01-01), date_read, date_delivered, is_from_me (0 for received, 1 for sent), item_type (message type), is_deleted (soft-delete flag).
handle holds the sender or recipient identifier for every message. Key columns: ROWID (primary key), id (the actual identifier like phone number or Apple ID email), service (iMessage or SMS), country. Handles are joined to messages through message.handle_id to produce sender attribution.
chat holds conversation threads. Group chats are represented as a single chat row referencing multiple handles. Individual conversations are single-handle chats. The chat_message_join junction table links messages to their conversation.
attachment holds attachment metadata: filename, MIME type, transfer state, total_bytes and the path to the attachment binary on disk. The message_attachment_join table links messages to attachments.
The date column in message uses Apple epoch nanoseconds (offset from 2001-01-01 UTC in nanoseconds since iOS 11, seconds before). Investigators querying manually must convert this to standard Unix epoch by dividing by 1000000000 and adding 978307200 (the Unix epoch of 2001-01-01). Sherlock iOS Analyzer handles the conversion automatically and displays timestamps in the examiner workstation timezone or UTC on operator preference.
Attribution Timeline Reconstruction Workflow
For an investigation reconstructing an iMessage or SMS conversation timeline the standard query joins message and handle and chat_message_join to produce a per-conversation flat listing. The output includes message content, sender identifier, timestamp and read/delivered acknowledgement markers. Investigators typically bound the query by date range or by conversation identifier or by keyword content match.
Group conversation attribution requires walking the chat_handle_join table to enumerate the participants of each chat row, then joining against handle to resolve each participant's phone number or Apple ID. Group chats with 10 or more participants routinely appear in modern iPhone use and the reconstruction needs to handle the multi-participant identity resolution correctly. Sherlock iOS Analyzer handles group participants automatically in the WhatsApp Groups and Chats and Groups views (both use the same underlying schema-walk methodology).
The message_attachment_join and attachment table joins produce per-message attachment listings. For investigators tracking image or video or document exchange the attachment metadata and the raw binary path on disk together identify the specific files. The attachment total_bytes column allows quick filtering for the large exchanges that matter in an exfil or IP-transfer investigation.
Deleted Message Recovery Through Freed-Page Carving
When a user deletes an iMessage the SQLite row is not immediately overwritten. SQLite marks the row as deleted in the B-tree index and the row content remains in the freed page until SQLite's page-recycling reuses the space. For investigators the freed-page carving methodology recovers deleted rows that the SQLite freelist has not yet overwritten. This is standard forensic SQLite carving applied to the specific chat.db schema.
Recovery yield depends on how much messaging activity has happened since the deletion. Light-usage users may have deleted messages recoverable months after the delete event. Heavy-usage users overwrite the freed pages faster and the recovery window narrows. The Sherlock iOS Analyzer Deleted and Hidden view carves the chat.db freelist automatically and surfaces the recovered rows alongside their metadata for investigator review.
The Read Receipt Detail That Matters in Investigations
The message table's date_read and date_delivered columns provide read-receipt attribution when the recipient device had read receipts enabled. For investigations reconstructing what a specific party knew and when the read receipt timestamps are load-bearing evidence. A message sent at 14:23 with a date_read of 14:24 establishes that the recipient saw the message within one minute. A message sent at 14:23 with date_read of null establishes the recipient never opened it (or had read receipts disabled).
The read receipt state is per-conversation configurable in iOS settings so absence of date_read does not conclusively prove the recipient did not read the message. It proves either the recipient did not read OR the recipient had read receipts disabled for that conversation. Investigators reporting on read receipt state need to note this caveat.
iMessage vs SMS Distinction in the Data
The service column on message distinguishes iMessage (Apple's encrypted messaging service) from SMS (carrier-delivered short message service). iMessage messages ride Apple infrastructure with end-to-end encryption between devices. SMS messages ride the carrier network as plaintext. The forensic implications differ: iMessage messages that survive on the device are the definitive record because carrier records do not capture the content. SMS messages exist both on-device and in the carrier records which allows cross-source corroboration.
For investigations that need to establish authenticity of an iMessage the on-device record is the primary source. Screenshots submitted as evidence are challenged for authenticity routinely; the on-device chat.db record with intact chain of custody is the load-bearing evidence. The Sherlock iOS Analyzer chain of custody documentation records the source device identifier, the acquisition timestamp, the file hash, the examiner identity and the tool version stamp. This documentation package supports court submission of the iMessage record.
The Forensic Acquisition Path That Surfaces chat.db
The universal path for chat.db acquisition is logical MobileBackup2 on the iPhone or iPad. The device must be unlockable (device passcode known) and connectable via USB to the forensic workstation and the operator must be able to tap "Trust This Computer" on the device prompt. When those conditions are met the MobileBackup2 path produces a complete plaintext backup that includes chat.db and all attachment binaries.
Encrypted backup mode is recommended in most cases because encrypted backups contain more data than unencrypted backups (keychain, Health, Wi-Fi passwords). Sherlock iOS Analyzer sets the backup password itself before acquisition and decrypts the backup offline using RFC 3394 keybag unwrap and AES. The examiner does not need to configure a password manually. The tool handles the encryption flip end-to-end.
Sherlock iOS Analyzer positions its iOS forensic capability against the mainstream competitor tools. Magnet AXIOM and Cellebrite Physical Analyzer both handle iOS logical acquisition with comparable feature depth. The Sherlock differentiator is pricing model (one-time $599 Forensic Edition rather than annual subscription in the multi-thousand-dollar range) and single-tool integration of acquisition, parsing, reporting and chain of custody in a Windows workstation form factor.
Where Investigators Should Look for Additional Corroboration
iMessage on-device chat.db is the primary source but not the only source. iCloud Messages backup (when the user has iCloud Messages enabled) preserves messages in Apple's iCloud infrastructure. Legal-process requests to Apple can retrieve the iCloud Messages record; the process requires the specific request format Apple documents in its Law Enforcement Guidelines. iCloud Messages record differs from device chat.db in that it may include messages the user deleted on-device but had synced to iCloud before deletion.
Companion device sync (Mac, iPad, Apple Watch) may also preserve messages. A user with an iMac paired to their iPhone Apple ID has a parallel chat.db on the Mac at /Users/USERNAME/Library/Messages/. The Mac chat.db syncs with the iPhone chat.db and may preserve messages that the iPhone version has since evicted or deleted. Investigators handling multi-device custody should consider all paired devices as potential corroboration sources.
Sherlock iOS Analyzer handles the primary iPhone or iPad acquisition path. For multi-device engagements the same methodology applies to each device separately and the outputs cross-correlate through the shared conversation identifiers. The Sherlock Forensics engagement model documents this multi-device workflow when the case scope requires it.
What This Means for Investigators in 2026
iOS iMessage and SMS reconstruction is one of the most common evidence categories in modern digital forensic engagements. The chat.db SQLite database is the definitive on-device record. The methodology to acquire and parse the database is documented in Heather Mahalik's iOS forensic research and in SANS FOR585 (Advanced Smartphone Forensics). The Sherlock iOS Analyzer operationalizes the acquisition and parsing methodology in a single-tool workflow suitable for Canadian and US investigator use.
For organizations building internal iOS forensic capacity the Sherlock iOS Analyzer Forensic Edition at $599 one-time is the accessible-premium tier that competes with mainstream commercial tools at a fraction of the total-cost-of-ownership. For organizations preferring engagement work rather than internal capacity the Sherlock incident response engagement model handles the iOS acquisition and analysis as part of the standard mobile-forensic scope.