Practice data only. Every message, number and account in this lab is fictional, and the phone numbers use the 555-01xx range reserved for fiction. We built the database with a reduced sms.db-style schema so you can practice WAL analysis. It is not an extraction from a real device, but the SQLite behavior it shows is real.
The scenario
A witness says the phone's owner traded messages with +1 (803) 555-0187 on the morning of October 14, 2026. The owner says no such conversation exists. Your tool's message report shows nothing with that number, either.
You have the sms.db, sms.db-wal and sms.db-shm files from a full file system extraction. The phone was set to Eastern Daylight Time (UTC-4). Before anything goes into a report, validate what the database actually holds.
Download
Download the lab files (ZIP, 8 KB)
| File | SHA-256 |
|---|---|
sms.db | c6c57f92758d9e7bb540efdda509c5e620e96c7f9ba8ea1e759eb7601b7b2538 |
sms.db-wal | 3580ed64d6e8732291654fdfb08377e6adcf7e44c3ad776a9eab16babb48c2ed |
sms.db-shm | f8f1d8660479adc9e2e05393453a9c65b9851ebb92c7e0f5ef1e1c766d73a35a |
validate-this-01.zip | 212d19db5af5aacd1c6e63b8bd75515e3020be246eb5c7528e4d3a821dcd6f91 |
Work only on copies. Most SQLite tools apply the WAL when you open sms.db with sms.db-wal beside it, and many checkpoint it into sms.db when they close. That changes your files. If your hashes stop matching, unzip a fresh set. That lesson is part of the lab.
Nothing to install: open all three files in our browser-based Lab Tools. The SQLite viewer reads the database with and without the WAL, compares the two, and decodes the WAL and shm headers; the hex viewer handles the bonus question. Prefer your own tools? DB Browser for SQLite or the sqlite3 command-line program work too. The walkthrough below uses sqlite3.
The challenge
- How many rows are in the
messagetable when you readsms.dbby itself? How many when the WAL is applied? - Which
ROWIDs are missing from the WAL-applied view, and which are new? - Was there a conversation with 555-0187? Give the number of messages sent and received and the first and last times (EDT).
- Besides the messages, what else was removed? Would comparing row counts in the
handleandchattables have caught it? - Were these messages simply in iOS's Recently Deleted folder? How do you know?
- Has the delete been checkpointed into
sms.db? Cite the evidence. - What can you say about when the delete happened?
- Bonus: Does any text from the deleted messages appear in
sms.db-wal? If so, is it a live record?
Hints
Hint 1: reading the database two ways
Make two folders. Put a copy of sms.db alone in the first. Put copies of all three files in the second. The first shows the main file as it was last checkpointed. The second shows the database the way the phone saw it.
Hint 2: comparing the two
In sqlite3, open the main-file-only copy and ATTACH the WAL-applied copy. Then look for ROWIDs in one message table that aren't in the other, in both directions.
Hint 3: the timestamps
The date values are 18 digits: Mac Absolute Time in nanoseconds. Divide by 1,000,000,000, add 978,307,200 to get Unix time, then shift to UTC-4.
Hint 4: was it checkpointed?
The WAL header is 32 bytes. Each frame is a 24-byte header plus one 4,096-byte page. A frame whose "database size" field isn't zero ends a transaction. In the -shm file, the 32-bit value at offset 96 (nBackfill) counts the frames already copied into the main file.
Walkthrough
Show the full solution
1. Read it two ways
mkdir nowal wal
cp sms.db nowal/
cp sms.db sms.db-wal sms.db-shm wal/
sqlite3 nowal/sms.db "SELECT count(*) FROM message;" -- 16
sqlite3 wal/sms.db "SELECT count(*) FROM message;" -- 7
The main file alone holds 16 messages. With the WAL applied, only 7 remain. (After you close sqlite3, re-copy the wal folder. Closing the last connection checkpoints the WAL, so that copy no longer matches its hashes.)
2. Find what changed
sqlite3 -header -column nowal/sms.db
ATTACH 'file:wal/sms.db?mode=ro' AS w;
-- in the main file, gone once the WAL is applied
SELECT m.ROWID, h.id AS handle, m.is_from_me,
datetime(m.date/1000000000 + 978307200, 'unixepoch', '-4 hours') AS edt, m.text
FROM main.message m LEFT JOIN main.handle h ON h.ROWID = m.handle_id
WHERE m.ROWID NOT IN (SELECT ROWID FROM w.message);
-- added in the WAL
SELECT ROWID, handle_id, text FROM w.message
WHERE ROWID NOT IN (SELECT ROWID FROM main.message);
ROWIDs 4–11 and 14–16 are in the main file but gone with the WAL applied. ROWIDs 17 and 18 exist only in the WAL. Even without the main file, the gaps in the WAL-applied ROWIDs (1–3, 12–13, 17–18) are the first clue that something is missing.
3. The conversation
All 11 missing messages are with +18035550187: 6 received and 5 sent (is_from_me = 1). They run from 07:41:09 to 19:02:14 EDT on October 14, 2026. They include a change of meeting place, a time of 6:30, "I only have 400 right now," and the last received message, "We good. Delete these." The two new messages, at 21:03:00 and 21:05:26, are with a different number, +18035550111, which starts with "Hey this is my new number, save it."
4. What else went with it
The handle row for 555-0187, its chat row and its chat_handle_join row are all in the main file and gone with the WAL. The whole conversation was deleted, not just the messages.
Row counts would not have caught it. handle and chat each hold 2 rows both ways, because a new handle and chat for 555-0111 were added after the delete. Compare the actual rows, not the totals.
5. Not Recently Deleted
Since iOS 16, deleted messages first sit in Recently Deleted, where they are still live rows listed in chat_recoverable_message_join. Here that table is empty both ways, and the message rows themselves are gone once the WAL is applied. These messages were removed from the database, not parked in Recently Deleted.
6. Not checkpointed
- The WAL: 25 frames of 4,096-byte pages, in 2 committed transactions. Every frame carries the same salt values as the WAL header (
0x7f431034and0xa4f4543c), so all of them belong to the current WAL cycle. - Transaction order: the first transaction (frames 1–12) removes the conversation. The second (frames 13–25) adds the 555-0111 conversation. You can show this by applying only the first 12 frames: copy the WAL's first 32 + 12 × 4,120 = 49,472 bytes next to a fresh
sms.db. That view has 5 messages and only the 555-0142 handle. - The shm:
mxFrame(offset 16) is 25 andnBackfill(offset 96) is 0. None of the WAL had been copied into the main file. - The main file: it still holds all 11 messages as complete, live rows.
7. When was it deleted?
The database doesn't record the moment of a delete. What you can say is bounded. The delete came after the last deleted message (19:02:14), and it was committed before the transaction that added ROWID 17, dated 21:03:00. Stop there. Don't name a single time for the delete unless another artifact, such as a log, supports it.
Bonus: text in the WAL
Search sms.db-wal for "laundromat" and you'll find it. When SQLite rewrites a page to remove a row, the old bytes often stay behind as free space in the new copy of the page. Those bytes are a remnant, not a live record. Report them that way, and say which file, frame and page they came from.
Report wording
Eleven iMessage records (ROWIDs 4–11 and 14–16) exchanged with +1 (803) 555-0187 between 07:41:09 and 19:02:14 EDT on October 14, 2026 are present as complete records in the main sms.db file. They are not present when the database is read with its write-ahead log (sms.db-wal) applied. The WAL contains a committed transaction, not yet written into the main file at the time of extraction, that removes these records along with the associated chat and handle records. The deletion occurred after 19:02:14 and before a later transaction that added a message dated 21:03:00 EDT.
Notice what the wording leaves out. It doesn't say who deleted the conversation, and it doesn't treat "Delete these" as proof of intent. Those are questions for the investigation, not the database.
Go further
This lab is the hands-on version of Deleted, But Still in the Database, which walks through a real case with the same pattern. SQLite, WAL analysis and timestamp validation are covered in depth, with graded labs, in our examiner course, Mobile Forensics: A Bit of Knowledge.