VP-02 · Mobile device forensics

Android messaging database and its WAL: a pickup message under dispute

A SQLite database and its write-ahead log from a messaging app, copied from a phone that was found switched off, plus the extraction log. Fifteen questions lead from file integrity and the WAL structure to what the committed state shows about one message and what it cannot show.

Synthetic training data. The organisation, people, devices, serial numbers and times are invented.

01Brief

Tern Freight is a parcel courier. On Friday 6 March 2026 a pickup at Dock 4 was moved to the afternoon. The driver says the instruction reached him only after 10:00 local time; the dispatcher says he sent it earlier. The dispatcher's phone was found switched off. On Monday 9 March 2026 a file-system extraction copied the messaging application's database, chatstore.db, together with its write-ahead log, chatstore.db-wal. You received the two files and the extraction log.

Internal audit asks:

Are the files intact and what are they: journal mode, page size, time of extraction?
How is the WAL built: how many frames, how many belong to the current generation, where does the committed part end?
What does the committed state show about the pickup message, and how should the rest of the WAL be reported?

You are asked about the files only: what the pair contains, which part is committed database state, and what can and cannot be said about the pickup message. Do not infer who intended what.

02Evidence files

File	Contents	SHA-256
README.txtScenario and file list	The brief in English and Turkish, handling notes and how the pair was made.	932c132542be4ea326f32a7dea072af505c4d74b62aeddb7e2c050afbdd54fbf
acquisition-log.txtExtraction log	Source directory, directory listing with sizes and file-system times, and the extraction time on the examiner's clock.	a528760d5bd581619856552be654e3d70dae187b696f07ecd529778a9611133d
chatstore.dbSQLite main database file	The main file of the messaging application's database: eight pages of 4096 bytes. WhatsApp for Android messages (msgstore.db) →	b534c5f2faedeff403a8bb282a120a5988fa1c19f290a29e5954e2e43b72da3d
chatstore.db-walSQLite write-ahead log	The WAL that belongs to the main file, copied as found: committed frames, the end of an unfinished transaction and older frames left over from a previous WAL generation.	f54a0199f87b858ae4359b0b09acb8db78453030eb3a4cf04eb55c8903b79d26
manifest.txtHash manifest	SHA-256 of every file above. Verify before you start.	(the manifest itself)

The hashes above are copied from manifest.txt. Recompute them yourself, for example with the hash tool or sha256sum.

03Before you start

Verify every file against manifest.txt and write the result in your examination log before you read anything else.
Work on copies, and never open the original pair with a database tool: an engine that opens a main file together with its WAL can run recovery and change or delete the WAL.
To see the main file alone, copy only chatstore.db to an empty folder. To see the committed state, copy both files together into another empty folder.
Message times in the database are Unix epoch milliseconds (UTC); the extraction log mixes time bases. Read the basis of every time before you use it.
The files show what the application stored, not what a person did or saw. Write each answer so that it stays true if the phone's clock was wrong.
Note which file, page or frame supports each answer; the instructor will ask.

Keep your notes in F-02 Evidence intake form and F-04 Examination log.

04Questions

Answer from the files only. Each answer is checked in this browser; nothing is sent anywhere, and your answers are kept on this device until you clear them. 15 questions, 24 points.

Score 0 / 24

05Your finding

Last step: write one paragraph that answers the three audit questions as a finding. Your instructor marks it against four criteria:

The report separates the main file, the committed state and the frames after the last commit frame.
Every time carries its basis: Unix epoch milliseconds, UTC, or local time with its offset.
The wording describes what the application stored, not what a person did, intended or saw.
No sentence calls anything 'deleted' or 'recovered' that the files only show as superseded or uncommitted.

How findings are phrased →

VP-02 · Android messaging database and its WAL: a pickup message under dispute

Name: ______________________ Date: ____________ Start / end (UTC): ________ / ________

Synthetic training data. Answer from the files; for each answer write the file and row that supports it.

Q01 (1 pt) Compute the SHA-256 of chatstore.db-wal and compare it with manifest.txt. Enter the first eight hex characters of the value you computed.
Answer: ____________________________________________
Source (file, row): ________________________________
Q02 (1 pt) The bytes at offsets 18 and 19 of chatstore.db (file format write version and read version) are both 02. What does that tell you?
- ☐ A. The database uses a rollback journal and has no WAL.
- ☐ B. The database is in WAL mode: committed changes can sit in the -wal file and not yet in the main file.
- ☐ C. The database is encrypted.
- ☐ D. The file is damaged.
Source (file, row): ________________________________
Q03 (1 pt) What is the database page size in bytes? It is stored at offsets 16 and 17 of the main file.
Answer: ____________________________________________
Source (file, row): ________________________________
Q04 (1 pt) When was the extraction made? Give the time of the extraction line in UTC.
Answer: ____________________________________________
Source (file, row): ________________________________
Q05 (1 pt) How many complete frames does chatstore.db-wal physically contain? A WAL has a 32-byte header; each frame is a 24-byte frame header followed by one page.
Answer: ____________________________________________
Source (file, row): ________________________________
Q06 (2 pt) How many frames, counted from the start of the file, belong to the current WAL generation? Those are the frames whose two salt values equal the salt values in the WAL header and whose checksums chain from the header.
Answer: ____________________________________________
Source (file, row): ________________________________
Q07 (2 pt) What is the number of the last commit frame of the current generation? A commit frame is a frame whose header field 'database size in pages after commit' (frame header offset 4) is not zero.
Answer: ____________________________________________
Source (file, row): ________________________________
Q08 (2 pt) How many frames of the current generation lie after the last commit frame? They carry the right salts and checksums but no commit.
Answer: ____________________________________________
Source (file, row): ________________________________
Q09 (2 pt) The table message has its root on page 5 (see sqlite_schema on page 1 of the main file). In how many committed frames of the WAL (frames 1 to the last commit frame) does page 5 appear?
Answer: ____________________________________________
Source (file, row): ________________________________
Q10 (1 pt) How many rows does the table message have in the main file alone? Copy chatstore.db to an empty folder without the WAL and count the rows.
Answer: ____________________________________________
Source (file, row): ________________________________
Q11 (2 pt) How many rows does the table message have in the committed state? Copy both files together into another empty folder and count the rows.
Answer: ____________________________________________
Source (file, row): ________________________________
Q12 (2 pt) The pickup message is row 49 of message (chat 1, sent from this phone). When does the database say it was sent? Convert sent_ms to UTC.
Answer: ____________________________________________
Source (file, row): ________________________________
Q13 (2 pt) The driver says he got no pickup time before 10:00 local time. Message 43, sent at 06:26:48 UTC, says 'Friday: pickup at Dock 4 later today. Time follows.' Which statement can the report support from the committed state?
- ☐ A. The 16:00 time was sent to the driver before 10:00 local time, because message 43 mentions the pickup.
- ☐ B. The application stored the first message that names 16:00 as sent at 10:12:44 local time (07:12:44 UTC) by this phone's clock; the file does not show when the driver saw it.
- ☐ C. The driver's phone received the message at 07:12:44 UTC.
- ☐ D. The dispatcher deleted an earlier message that contained the time.
Source (file, row): ________________________________
Q14 (2 pt) Message 49 reads 'Pickup is 16:00 at Dock 4. Please confirm.' and has edited_ms 2026-03-06 07:14:20 UTC. A text search of the WAL also finds 'Pickup moved to 16:00 at Dock 4. Please confirm.' in frames 7 and 10. Which statement can the report support?
- ☐ A. The sender changed the wording on purpose to mislead the driver.
- ☐ B. The file holds two committed versions of the page: the first wording in superseded frames and the current wording in frame 16. With edited_ms this is consistent with an edit recorded by the application; the files do not show who made it, why, or what the driver saw.
- ☐ C. The first wording was deleted and has been recovered from the WAL.
- ☐ D. Frames 7 and 10 are corrupt because their text differs from the current page.
Source (file, row): ________________________________
Q15 (2 pt) A text search of chatstore.db-wal finds 'Stock list part 1' in frames 18 and 21, but a query of the committed state does not return it. Frame 21 also shows row 49 with its revoked column set to 1, while the committed state has revoked = 0. How should the report treat this?
- ☐ A. List the stock-list messages as messages stored in the database and report message 49 as revoked.
- ☐ B. State that frames 17 to 22 belong to a transaction that was not committed and are not part of the database state; describe them without counting them as stored messages or as a revocation, and note that the files do not show why the transaction was not completed.
- ☐ C. Leave them out of the report, because anything after the last commit is irrelevant.
- ☐ D. State that the sender deleted the stock-list messages and tried to revoke message 49.
Source (file, row): ________________________________

Finding paragraph

______________________________________________________________________