WhatsApp Desktop — Deleted Message Recovery

Overview

When a user deletes a message in WhatsApp Desktop, the corresponding row is removed from the ZWAMESSAGE table in ChatStorage.sqlite. However, SQLite does not immediately overwrite the deleted data on disk. Instead, the database page that contained the row is added to SQLite's free page list — a linked list of pages available for reuse. The original data remains in the free page until SQLite reuses that page for new data.

This creates a forensic recovery opportunity: by reading the raw ChatStorage.sqlite binary file and scanning its free pages, investigators can recover fragments of deleted messages — including message text, sender JIDs, and timestamps — that the user believed were permanently removed.

How SQLite Free Pages Work

SQLite databases are divided into fixed-size pages (typically 4096 bytes). When rows are deleted, their pages may become free pages rather than being zeroed out.

SQLite Header Fields

The first 100 bytes of the database file contain the header. The relevant fields for free page recovery are:

Offset	Size	Field	Description
16	2 bytes	Page size	Database page size in bytes (big-endian). If value is 1, actual size is 65536.
32	4 bytes	First free page	Page number of the first free page (big-endian). 0 means no free pages.
36	4 bytes	Free page count	Total number of free pages (big-endian).

Free Page Linked List

Free pages form a linked list. The first 4 bytes of each free page contain the page number of the next free page in the chain (0 = end of list). The remaining bytes of the page retain their original content from before the page was freed.

Page Types

When scanning free pages, look for pages that were originally leaf table b-tree pages (page type byte 0x0D at offset 0). These contain the actual row data (cell content) that held message records.

Page type bytes:
0x02 - Interior index b-tree page
0x05 - Interior table b-tree page
0x0A - Leaf index b-tree page
0x0D - Leaf table b-tree page  <-- contains row data

Recovery Approach

macfor uses a pattern-matching approach to recover message fragments from free pages:

Step 1: Parse the SQLite Header

Read the page size from offset 16 and the first free page number from offset 32. If the first free page is 0, there are no free pages and no recovery is possible.

Step 2: Walk the Free Page List

Starting from the first free page, follow the linked list by reading the first 4 bytes of each page to get the next page number. For each page in the chain, examine its contents.

Step 3: Scan for WhatsApp Identifiers

Search each free page's data for WhatsApp JID patterns using regular expressions:

Individual: \d+@s\.whatsapp\.net
Group:      \d+@g\.us

The presence of a JID on a free page strongly indicates the page previously held a ZWAMESSAGE or ZWACHATSESSION row.

Step 4: Extract Surrounding Content

When a JID is found, examine the nearby bytes for:

Text content: Look for runs of printable UTF-8 characters near the JID. Message body text is typically stored close to the sender JID within the same cell.
Timestamps: Look for 8-byte IEEE 754 float64 values in the Core Data timestamp range (approximately 631,000,000 to 820,000,000, corresponding to 2021-2027). Try both little-endian and big-endian byte orders.
Additional JIDs: Multiple JIDs on the same page may indicate a conversation thread with sender and recipient identifiers.

Step 5: Emit Recovery Records

Each recovered fragment is emitted with metadata indicating:

Field	Description
`recovery_source`	Always `"freepage"`
`page_number`	The SQLite page number where the fragment was found
`cell_offset`	Byte offset within the page
`text`	Recovered message text (if found)
`sender_id`	WhatsApp JID of the sender (if found)
`chat_id`	Conversation JID (if found)
`timestamp`	Recovered Core Data timestamp converted to UTC (if found)
`is_partial`	`true` if not all fields could be recovered

Limitations

Deleted message recovery is best-effort with several important limitations:

No guarantee of recovery: Free pages are reused by SQLite as the database grows. Once a page is reused, its previous content is permanently lost.
Partial records: Recovered fragments may contain only some fields (e.g., a JID without text, or text without a timestamp). These are still emitted with is_partial: true.
False positives: The pattern-matching approach may occasionally match non-message data that happens to contain JID-like patterns.
No reconstruction of conversation order: Recovered fragments are individual records without the context of their original conversation thread. The chat_id field, when present, provides some grouping capability.
WAL complication: If ChatStorage.sqlite has an active WAL (Write-Ahead Log) file, recently deleted messages may still be in the WAL rather than in free pages. macfor's WAL-safe copy captures the WAL contents, but free page analysis operates on the main database file only.
VACUUM destroys free pages: If WhatsApp or SQLite has performed a VACUUM operation on the database, all free pages are eliminated and their content is permanently lost.

Complementary Evidence Sources

Deleted message recovery from free pages should be combined with other evidence sources:

ChatSearch.sqlite (Search Index)

The FTS (Full-Text Search) index in ChatSearch.sqlite may retain indexed text from messages that have been deleted from ChatStorage.sqlite. When a message is deleted from the main database, the search index is not always updated synchronously. See the Chat Database page for details on querying the search index.

iCloud Backup

The iCloud backup may contain an older version of ChatStorage.sqlite.enc that predates the deletion. If the backup encryption key is available, this can provide access to messages that were subsequently deleted from the live database.

FSEvents

The FSEvents journal may record file modification events for ChatStorage.sqlite, providing a timeline of when the database was modified (which may correlate with message deletion activity).

Forensic Analysis Notes

Always collect free page results alongside live messages for a complete picture. A message appearing only in free page recovery (not in the active ZWAMESSAGE table) confirms it was deleted.
Timestamp correlation: When a recovered timestamp falls between known active messages in the same conversation, it strengthens the evidence that the recovered fragment belongs to that conversation.
JID attribution: The sender JID in a recovered fragment directly encodes the phone number (14155551234@s.whatsapp.net = +1-415-555-1234), providing identity attribution even for deleted messages.
Large databases yield more recoveries: Heavy WhatsApp users with large databases tend to have more free pages, increasing recovery potential. Conversely, small databases with active growth may have few or no free pages.
The SkipDeletedRecovery option in macfor disables free page analysis. It is enabled by default because the forensic value justifies the additional processing time.

Tool Support

Tool	Support Level
macfor	Automated free page scanning with JID pattern matching
sqlite3 CLI	Manual inspection via `.dbinfo` (shows free page count)
Belkasoft X	SQLite free page recovery
Oxygen Forensic Detective	SQLite deleted record recovery
FTK Imager	Hex-level database inspection