SQLite

Deleted, but Still in the Database: Recovering a Message the WAL Never Checkpointed

Validated4n6·

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 wrong. The delete had been saved, but only to the WAL. It had never been copied into the main database, so the main file still held the message. A deleted message usually won't be in the WAL. What goes into the WAL is the change: a new version of the page without the message. 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 notepad.
  • The shared-memory file (sms.db-shm). An index that tracks which notepad entries are current and how far the phone has gotten in copying them into the notebook.

If you only extract or examine the main database file, you can miss everything still sitting in the WAL.

2. What happens when a message is deleted

SQLite doesn't "mark" a message for deletion. It stores data in fixed-size pages, and a delete works like this:

  1. The change goes to the WAL first. SQLite writes a new copy of the affected page into the WAL, without the message. The original page in the main database is not touched.
  2. The phone reads the newest version. When the phone opens the database, it uses the WAL's copy of any page that has one. The message disappears from the screen right away.
  3. A checkpoint copies the WAL into the main database. Every so often, SQLite copies the pages from the WAL over the originals in the main file. This is called a checkpoint. Until it runs, the main file still holds the old page, and on that page the message is an ordinary, intact record.
  4. After the checkpoint, only leftovers remain. Once the new page has replaced the old one, the message is gone from the active records. Its bytes may survive in free space inside the page until something overwrites them. SQLite then reuses the WAL from the top.

3. So why was the "deleted" message in the main database?

Because the delete hadn't been checkpointed yet. That gives you two very different situations to tell apart:

  • The delete is still only in the WAL. The message is a complete, live row in the main database file. Read the main file by itself and the message is there. Apply the WAL and it disappears. That is what happened in this case.
  • The delete has already been checkpointed. The message is no longer a row anywhere. At most, pieces of it can be carved from free space in the main file, and often nothing is left.

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, listed in the chat_recoverable_message_join table. Some tools label them "deleted" because the user deleted them, not because they were recovered.
  • The tool found it in an older WAL cycle. Because the WAL is reused from the top, entries from an earlier cycle can survive near the end of the file. Each WAL frame carries the salt values from the WAL header. Frames whose salt doesn't match belong to an older cycle and aren't part of the current database.
  • 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. Find out which file and which page the record came from, and whether it is a live row or a remnant in free space.

4. The case: how we worked through it

This question didn't come up in a classroom. It came from a real case, and the way it was worked out is a good model for anyone who runs into the same thing. It also shows how a question can grow once you start pulling on it.

It started with a few missing numbers

Every message in sms.db gets a ROWID, a number that goes up by one with each new row. The examiner was looking at a short exchange from the previous morning and noticed gaps in the ROWIDs around it: rows that should have been there and weren't. One commercial tool showed those messages as deleted. A second commercial tool, parsing the same extraction, didn't show them at all. And the messages weren't on the phone.

Neither tool was wrong. The second one applied the WAL, so it showed the database the way the phone saw it. The first one also read the older pages in the main file and flagged the difference.

With the WAL, then without it

We worked on copies and read the database two ways: the main file by itself, opened read-only so the WAL couldn't be applied, and then the main file with the WAL applied. The messages were in the main file alone. With the WAL applied, they were gone. The delete was sitting in the WAL and had never been copied into the main database.

The WAL held 934 frames in 118 committed transactions, all from the same WAL cycle. None were left over from an earlier one, and nothing was uncommitted. The shm (section 5) showed that none of those frames had been recorded as copied into the main file.

Page by page

Next we found the pages in the main file that held the messages and compared them with every version of those pages in the WAL. The main file still had the pre-delete versions. The WAL's newer versions didn't contain the messages. The checkpoint had never overwritten them.

Three messages became eighty-nine

The first pass focused on the three messages the examiner was asking about. When the whole database was compared both ways, 89 messages turned out to be in the main file but gone once the WAL was applied. All 89 were the same conversation: 50 sent and 39 received, covering about 34 hours. The entire conversation had been deleted, along with the conversation record itself, the contact's handle, and one attachment. Every message was still intact in the main file.

The counts tie out. The main file alone held 84,925 messages. With the WAL applied, there were 84,842: the 89 deleted messages gone, and 6 new messages added afterward.

It's also worth knowing what this method can't recover. The database had more than 18,000 other ROWID gaps. Those messages were deleted long before and had already been checkpointed, so they weren't sitting intact in the main file. Only the 89 deleted after the last checkpoint could be recovered this way.

Deleted in two steps, on the device

The WAL showed the delete happened in two transactions, one right after the other:

  1. Moved to Recently Deleted. One transaction moved all 89 messages to Recently Deleted. The phone recorded the time in the delete_date column of chat_recoverable_message_join.
  2. Deleted for good. The very next transaction removed them from Recently Deleted and deleted the rows, the conversation, the handle, and the attachment. It also wrote 89 entries to deleted_messages and 89 to sync_deleted_messages, which the phone uses to tell iCloud that a message was deleted.

Restoring from Recently Deleted would have kept the messages. These were deleted, with sync records for iCloud. That is a permanent delete made on the device, not the database cleaning up after itself.

A second method

Finally, the examiner ran the same three files through a tool built to read the main file, the WAL, and the shm together, transaction by transaction. It confirmed the manual result on its own: same 89 messages, same transactions, no checkpoint. It also caught what the first pass had missed: the full scope of the conversation, and the two-step delete with its recorded time. Two methods that agree are much easier to defend than one, and here the second method made the findings better.

No single step proved the answer. Together, they showed exactly where the messages came from, why one tool reported them and the other didn't, and that they were deleted on the device. Notice the wording: on the device. The data shows the messages were deleted on that phone. It doesn't show whose hand was on it. Tie it to a person only if other evidence does.

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. What the shm file shows, and what it doesn't

The shm file is small, but it tells you how far a checkpoint got. Three numbers matter:

  • mxFrame: the last valid frame in the WAL.
  • nBackfill: how many frames SQLite has recorded as copied into the main database.
  • nBackfillAttempted: how many frames the most recent checkpoint set out to copy.

According to the SQLite documentation, nBackfill equal to mxFrame means the WAL has been fully copied into the database. nBackfill of 0 means none of it has been recorded as copied. SQLite sets nBackfillAttempted when a checkpoint starts and updates nBackfill only after the pages have been written and synced. So when nBackfillAttempted is higher than nBackfill, a checkpoint started but never recorded finishing.

That is what we saw in this case: mxFrame was 934, nBackfill was 0, and nBackfillAttempted was 934. A checkpoint covering all 934 frames had begun but hadn't been recorded as complete.

Be careful how far you take this. The shm is a memory file, and what you collect is a snapshot of it at that moment. On its own, it supports the explanation but doesn't prove that no pages reached the main database. The page-by-page comparison in section 4 is what proves it. Use the shm as support, and the pages as proof.

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. When did the deletion happen?

The WAL has no timestamps. What it has is order. SQLite only adds to the end of the WAL: each saved transaction is written as a run of frames after the one before it, and the last frame of the run is flagged as the commit. As long as every frame carries the WAL header's salt, frame order is the order the changes were saved.

To build the timeline, we rebuilt the database one transaction at a time from a copy of the main file, applying the WAL frames in order and querying the result after each commit. Then we lined up every time the phone recorded along the way. All times are the phone's local time.

TimeWhat happenedWhere the time comes from
8:24:39 PMLast message in the conversation (sent)Message date
8:24:40 PMMain database file last modifiedFile system
8:55:51 PMA message arrives from a different contactMessage date
9:03:54 PMThe phone marks that message readRead receipt (date_read)
9:08:44.174 PMAll 89 messages moved to Recently Deleted (transaction 33)delete_date
After 9:08:44, before 9:10:14 PMAll 89 messages, the conversation, the handle, and the attachment deleted for good (transaction 34)WAL order
9:10:14 PMThe user sends a message to a different contact (transaction 38)Message date
9:10 to 9:31 PMSix messages with two other contactsMessage dates

How the window got smaller

Our first pass bracketed the delete between two events the phone timed itself: the read receipt at 9:03:54 PM and the message sent at 9:10:14 PM. That gave a window of about six minutes.

The second method found something better: a time recorded for the delete itself. On iOS 16 and later, moving a message to Recently Deleted writes a delete_date. That put the move at 9:08:44 PM, to the millisecond. The permanent delete has no time of its own, but WAL order puts it after the move and before the 9:10:14 PM message. That's a window of about 90 seconds.

Choose your anchors carefully

Every anchor in the final window was written by the phone's own clock: the delete_date and the time of a message the phone sent. The date on an incoming message can come from the sender's side, which makes it a weaker anchor. State the limits in your report: WAL order proves sequence, not an exact minute, and every time assumes the phone's clock was accurate.

File-system times can back up the timeline, but they don't prove it. Here, the main database file was last modified at 8:24:40 PM, one second after the last message in the conversation. That fits a checkpoint at that moment, with everything after it, including both steps of the delete, written only to the WAL.

7. Verify it yourself before you testify

  • Work on copies. Opening a database normally with its WAL beside it applies the WAL, and closing it can run a checkpoint and change the files. To read the main file by itself, open a copy read-only with SQLite's immutable=1 option, or copy the main file to a folder without its WAL.
  • 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. Anyone can repeat the rebuild in section 6: write each WAL frame's page into a copy of the main file at offset (page number − 1) × page size, and query the copy after each commit frame.
  • 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 both situations from section 3, and it is validation work you can point to in court.

The takeaway

A message that shows as deleted but isn't in the WAL usually isn't a tool error. Look for the delete in the WAL, not the message. If the delete hasn't been checkpointed, the main database still holds the message as a complete record. The Recently Deleted timestamp and the delete's place in the WAL can show when it was deleted, often to within a minute or two. Read the main file with and without the WAL, compare the pages, use the shm as support, and say "deleted on the device" unless other evidence names the person. That is the difference between repeating what a tool reported and explaining what the data shows.

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

Sources