Deleted, But Not in the WAL: How SQLite Handles a Deleted Message

An examiner recently came to me confused. Their forensic tool was showing a text message as "deleted," but the message was sitting in the main database file, not in the WAL. If it had been deleted, why wasn't it in the WAL?

It's a fair question, and the tool wasn't necessarily wrong. The answer comes down to how SQLite, the database behind most phone apps, saves and removes data. Once you understand three files and one process, this question answers itself, and you can explain it on the stand.

1. Three files, one database

Most messaging apps on iOS and Android store their data in SQLite databases. On iPhone, text messages and iMessages live in sms.db. When the database runs in WAL (write-ahead log) mode, which most phone apps use, you will usually find three files side by side:

• The database file (sms.db). The main record. Think of it as the notebook.
• The WAL file (sms.db-wal). New changes are written here first instead of directly into the notebook. Think of it as a stack of sticky notes.
• The shared-memory file (sms.db-shm). An index that tracks which sticky notes are current and which ones have already been copied into the notebook.

If you only extract or examine the main database file, you can miss everything still sitting in the WAL. That's one reason a full file system extraction matters.

2. What happens when a message is deleted

Deleting a message doesn't wipe it from the phone right away. In simple terms:

1. The change goes to the WAL first. SQLite writes a new copy of the affected database page into the WAL, with the message removed from the active records.
2. The old data is usually marked free, not erased. Inside that page, the space the message used is flagged as available. The bytes often stay there until something new overwrites them.
3. A checkpoint copies the WAL into the main database. Every so often, SQLite copies the pages from the WAL back into the main file. This is called a checkpoint. After it runs, the main database holds the updated page, including any leftover deleted bytes.
4. The WAL starts over. After a checkpoint, SQLite reuses the WAL from the beginning. It doesn't shrink the file. New changes simply overwrite the old ones, starting at the top.

3. So where did the "deleted" message come from?

If the message isn't in the WAL, the most likely explanation is that a checkpoint already happened. The page holding the deleted message was copied from the WAL into the main database, and the tool recovered the leftover bytes from free space inside sms.db itself. The WAL was then reused, and the copy that used to be there was overwritten.

There are a few other possibilities worth ruling out:

• The message was never fully deleted. Since iOS 16, deleted messages first go to a Recently Deleted folder, where they can be recovered for about 30 days. During that time they are still active records in the database. Some tools label them "deleted" because the user deleted them, not because they were carved from free space.
• The tool found it in an older WAL cycle. Because the WAL is overwritten from the top, older entries can survive near the end of the file. Tools that parse those stale entries may report a record that isn't part of the current WAL.
• The tool's label is an interpretation. Different tools use "deleted" to mean different things. The label is where your analysis starts, not where it ends.

The fix is the same in every case: go to the source. Every good tool can show you which file and offset a record came from. If it came from sms.db and not sms.db-wal, you have your answer.

4. How we worked through it

This question didn't come up in a classroom. It came from a real case, and the way the examiner worked it out is a good model for anyone who runs into the same thing.

• Two tools, two answers. One commercial tool showed the messages as deleted. A second commercial tool, parsing the same extraction, didn't show them at all. That alone doesn't mean either tool is wrong. Tools differ in whether they parse the WAL, whether they recover records from free space, and how they label what they find.
• With the WAL, then without it. We parsed the database twice: once with the WAL and shm files alongside it, and once with the main database file by itself. The messages showed up even without the WAL. That told us they were coming from the main database itself, not from pending changes in the WAL.
• Back to the phone. The examiner then did a manual validation, scrolling through the conversation on the physical phone. The messages weren't there. That confirmed the user could no longer see them, which is consistent with a record that was deleted and recovered from leftover data, not one that was still active.

No single step proved the answer. Together, they let the examiner explain exactly where the messages came from and why one tool reported them and the other didn't. When you do a manual check like this on an iPhone, look in the Recently Deleted folder too, and photograph or record what you see.

5. Use the shm file to prove a checkpoint happened

The shm file is small, but it tells you how far the checkpoint got. Two numbers matter most:

• mxFrame: the number of valid entries currently in the WAL.
• nBackfill: how many of those entries have already been copied into the main database.

According to the SQLite specification, when nBackfill equals mxFrame, all of the WAL's content has been copied into the database. That is objective support for the explanation above, and it comes from the published format, not from a tool's label.

Where to look

You don't need to work out a new pattern for every shm file. The layout is fixed and published by SQLite in its WAL-mode file format documentation. These are the fields most examiners need:

• Offset 0 (4 bytes): version number, always 3007000 (0x002DE218). If the bytes read 18 E2 2D 00, the file is little-endian, which is normal for phones and PCs. Read every other number in the file that way.
• Offset 14 (2 bytes): page size.
• Offset 16 (4 bytes): mxFrame.
• Offset 32 (8 bytes): the salt values, which should match the WAL's header at offset 16.
• Offset 96 (4 bytes): nBackfill.
• Offset 128 (4 bytes): nBackfillAttempted.

Two quick checks confirm you are reading the right shm for the right WAL. First, bytes 0 to 47 should be identical to bytes 48 to 95, because SQLite keeps two copies of the header. Second, the salt and page size should match the WAL file's own header.

6. Verify it yourself before you testify

• Cite the spec. Reference SQLite's own documentation and source code in your report. "The published format says" is much stronger on the stand than "a tool told me."
• Use two methods. Decode the key values in a hex editor and with a second tool or a short script. If both agree, you verified the result instead of just reporting it.
• Test on a device you control. On a test phone, send a few messages, delete one, and extract right away. Then let the phone sit long enough to checkpoint and extract again. Comparing the two sets of files shows you exactly what happened, and it is validation work you can point to in court.

The takeaway

The views in this article are my own and do not necessarily represent the Richland County Sheriff's Department.

Sources

• SQLite, WAL-mode File Format
• SQLite, Write-Ahead Logging
• SQLite source, wal.c