WhatsApp forensics: chat exports, msgstore.db, ChatStorage

WhatsApp evidence reaches an examiner in three shapes: the text file produced by the in-app Export chat feature, the Android message database msgstore.db, and the iOS database ChatStorage.sqlite. They describe the same conversations but carry very different amounts of information, and they encode time in three different ways. Knowing which one you hold decides what you can defend in a report.

Shape 1: the in-app "Export chat" file

Any user can export a single conversation from the chat menu (Android: the three-dot menu, then More, then Export chat; iOS: tap the contact or group name, then Export Chat). The result is a .txt file, or a .zip holding _chat.txt plus media when "Attach media" is chosen. It is often the only thing a witness or client can hand over without a device extraction.

Line formats

The line layout depends on the platform and on the phone's locale settings, not on the WhatsApp version alone. The common variants:

[13/01/2024, 22:15:03] Alice: Are you still at the office?     iOS, day/month, seconds
13/01/2024, 22:15 - Alice: Are you still at the office?        Android, day/month, no seconds
1/13/24, 10:15 PM - Alice: Are you still at the office?        Android, US month/day, 12-hour
[1/13/24, 10:15:03 PM] Alice: Are you still at the office?     iOS, US month/day, 12-hour
13.01.24, 22:15 - Alice: Are you still at the office?          dotted European dates

A message spanning several lines continues on the following lines without a timestamp, so any parser must attach those lines to the previous message. Lines without a Name: part are system events (joins, "Messages and calls are end-to-end encrypted", number changes). iOS also inserts an invisible left-to-right mark (U+200E) at the start of many system and attachment lines, which breaks naive grep patterns.

Media markers

How media shows up depends on the platform and on whether media was attached to the export:

Marker wording is localized, so a French or German phone produces different strings. Treat the file names as leads to the actual media rather than as proof that the media still exists.

The two problems with text exports

No time zone. The timestamps are rendered in the phone's local time at the moment of export, and nothing in the file records which zone that was. If the phone travelled or the user changed the zone, the file cannot tell you. You need the zone from another source (device settings, the custodian's location, a corroborating server-side record) before you can place these messages on a UTC timeline.

Day/month ambiguity. 03/04/2024 is 3 April or 4 March depending on locale. The only reliable signal inside the file is a component greater than 12 somewhere in the conversation. A short chat whose dates all fall on the 1st to the 12th of a month is genuinely ambiguous, and you should record which reading you chose and why.

Also note that exports are a curated view: the user picks the chat, may have deleted messages before exporting, and deleted messages appear only as "This message was deleted" placeholders. Exports have also historically been capped at a maximum number of messages, particularly with media attached, so a long conversation can be truncated without any warning in the file itself.

What the analyzer does with exports

The parser accepts the .txt directly or the ZIP as-is (the chat title is taken from the ZIP name, such as WhatsApp Chat - Alice.zip). Concretely:

• It recognizes the iOS bracketed, Android dash, 12-hour AM/PM, dotted and ISO-style date layouts, and two-digit years.
• Date order is inferred from the whole file: any first component above 12 means day/month, any second component above 12 means month/day; otherwise the presence of AM/PM suggests US month/day, and its absence day/month. The chosen order is stated in a warning so you can check it.
• Times are kept exactly as written and labelled UTC, with a warning and a time-zone: device-local (not recorded) field on each message. The tool does not guess the zone; apply the offset yourself when you know it.
• <attached: …> and … (file attached) markers (including some French, German and Spanish forms) become attachment entries. <Media omitted> and "image omitted" stay in the message text.
• Common "This message was deleted" placeholders are flagged as deleted messages. Lines without a sender become (system) entries.

Shape 2: Android msgstore.db

A full or file-system extraction of an Android device gives you the live database:

/data/data/com.whatsapp/databases/msgstore.db   messages, chats, media references
/data/data/com.whatsapp/databases/wa.db         contacts (wa_contacts)
/data/data/com.whatsapp/files/key               key for local encrypted backups

Copy each database together with any -wal and -shm files next to it. Recent messages may still sit in the write-ahead log. Load msgstore.db and msgstore.db-wal together: the analyzer replays the WAL's committed frames onto an in-memory copy, leaves the originals untouched, and reports in the parser notes how many frames it merged. Load the database once without the WAL as well if you want to see what the log changed.

Encrypted backups

Without root-level access you often find only the local backups on shared storage, such as Android/media/com.whatsapp/WhatsApp/Databases/msgstore.db.crypt14 (older devices use WhatsApp/Databases/ at the storage root). The .crypt14 and .crypt15 files are encrypted. Decrypting them needs the key from /data/data/com.whatsapp/files/key, or the 64-digit key or password if the user enabled end-to-end encrypted backups. The analyzer does not decrypt these files. Decrypt with a dedicated tool first, then load the resulting SQLite database.

Modern schema

Current Android builds normalize messages across several tables. Column names below are the ones commonly seen; they drift between releases, so check PRAGMA table_info on your own copy.

A query that reconstructs a readable timeline:

SELECT datetime(m.timestamp / 1000, 'unixepoch') AS utc,
       cj.raw_string AS chat, c.subject,
       CASE m.from_me WHEN 1 THEN 'owner' ELSE sj.raw_string END AS sender,
       m.message_type, m.text_data, mm.file_path
  FROM message m
  LEFT JOIN chat c     ON c._id = m.chat_row_id
  LEFT JOIN jid cj     ON cj._id = c.jid_row_id
  LEFT JOIN jid sj     ON sj._id = m.sender_jid_row_id
  LEFT JOIN message_media mm ON mm.message_row_id = m._id
 ORDER BY m.timestamp;

timestamp is Unix time in milliseconds, UTC: 1705184103000 is 2024-01-13 22:15:03 UTC. JIDs identify the conversation type: individual chats end in @s.whatsapp.net, groups in @g.us, and status@broadcast holds status updates. Newer builds also use other identifier forms (such as @lid), so do not assume every sender resolves to a phone number.

Legacy schema

Older databases keep everything in one messages table: key_remote_jid (the chat), key_from_me, key_id, data (the text), timestamp (Unix ms), remote_resource (the sender inside a group), and media_* columns. You still meet these in older extractions and in archived cases.

Contact names from wa.db

msgstore.db stores JIDs, not names. wa.db holds the wa_contacts table with jid, display_name (the address book name), wa_name (the name the user set in WhatsApp) and given_name. Keep them distinct in a report: an address book entry reflects what the device owner typed, not what the contact calls themselves.

What the analyzer does with Android databases

• Detects the modern schema (message + jid + chat) and the legacy messages table with key_remote_jid, and parses both.
• Converts timestamp from Unix milliseconds to UTC and skips rows with no timestamp.
• Joins message_media when present and lists file path, MIME type and size as attachment references (the media files themselves are not in the database).
• Maps common message_type values to labels (image, audio, video, document, sticker, location and so on) when there is no text, and flags type 15 as a deleted message. That mapping is based on commonly observed values, not on an official specification.
• If you drop wa.db in the same batch, contacts are loaded first and senders are shown as Name (number). Without it, you get a warning and bare numbers.

Shape 3: iOS ChatStorage.sqlite

On iOS, WhatsApp keeps its database in the shared app group container, not in the app's own sandbox:

/private/var/mobile/Containers/Shared/AppGroup/<UUID>/ChatStorage.sqlite
iTunes/Finder backup: AppDomainGroup-group.net.whatsapp.WhatsApp.shared / ChatStorage.sqlite

It is a Core Data store, which explains the Z-prefixed names:

ZMESSAGEDATE is Cocoa time: seconds since 2001-01-01 00:00:00 UTC, often with a fractional part. Add 978307200 to get Unix seconds:

SELECT datetime(m.ZMESSAGEDATE + 978307200, 'unixepoch') AS utc,
       s.ZPARTNERNAME AS chat, m.ZISFROMME, m.ZFROMJID, g.ZCONTACTNAME, m.ZTEXT
  FROM ZWAMESSAGE m
  LEFT JOIN ZWACHATSESSION s ON s.Z_PK = m.ZCHATSESSION
  LEFT JOIN ZWAGROUPMEMBER g ON g.Z_PK = m.ZGROUPMEMBER
 ORDER BY m.ZMESSAGEDATE;

The same 22:15:03 UTC message from the Android example would appear here as 726876903. Reading it as Unix time puts it in 1993, which is a quick way to spot a tool that picked the wrong epoch.

What the analyzer does with ChatStorage.sqlite

It joins ZWAMESSAGE with ZWACHATSESSION, plus ZWAGROUPMEMBER and ZWAMEDIAITEM when those tables and link columns exist, converts ZMESSAGEDATE from Cocoa time to UTC, and names senders from the group member name, the push name or the chat partner name, in that order. Media appear as local path references. As with Android, load ChatStorage.sqlite-wal alongside the database so its committed frames are merged.

Choosing and combining sources

When you hold both an export and a database for the same chat, use the database for timing and identity, and use the export to confirm what the user saw. Differences between the two, such as messages present in one and missing from the other, are findings in their own right.

Try it on your own evidence

The analyzer runs entirely in your browser: files are hashed with SHA-256 and parsed locally, and nothing is uploaded. Open the analyzer and drop the export ZIP, or msgstore.db together with wa.db, or ChatStorage.sqlite.

Related guides in this series:

• iMessage and chat.db forensics for the other half of most mobile cases
• Discord data package forensics
• What is messaging forensics?