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

01Session plan

Materials: the pack files on each trainee's machine, a printed worksheet from the pack page, and an examination log form. Pack page →

02Answer key

1. Q011 point

   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.

   Answerf54a0199

   The value begins f54a0199 and matches the manifest. The habit matters more than the digits: the WAL is the file most easily changed by opening it, so its hash is recorded before any tool touches the pair.

   Where

   manifest.txt, the chatstore.db-wal line; recompute with the hash tool or sha256sum.

   Usual mistake

   Hashing the WAL after opening the pair in a database tool, which may already have rewritten or removed it.

   Hash calculatorF-04 Examination logSOP-02 Evidence intake and registration

2. Q021 point

   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?

   AnswerB — The database is in WAL mode: committed changes can sit in the -wal file and not yet in the main file.

   Values 1 mean a rollback journal and 2 mean WAL. In WAL mode the main file is changed only when the WAL is checkpointed, so the main file alone can be older than the committed state.

   Where

   chatstore.db, bytes 0x12 and 0x13 (hex view). Source: https://www.sqlite.org/fileformat2.html (database header, offsets 18 and 19).

   Usual mistake

   Reading the main file as the whole database without asking whether a WAL exists.

   Hex viewer and data inspectorSOP-05 Mobile device acquisition

3. Q031 point

   What is the database page size in bytes? It is stored at offsets 16 and 17 of the main file.

   Answer4096

   The two bytes are 10 00, the big-endian value 0x1000 = 4096. Every page in the main file and every frame body in the WAL has this size, so it is the unit for all offsets that follow.

   Where

   chatstore.db, bytes 0x10 and 0x11; the file length 32768 divided by this value gives eight pages.

   Usual mistake

   Reading the two bytes as little-endian (0x0010 = 16).

   Hex viewer and data inspector

4. Q041 point

   When was the extraction made? Give the time of the extraction line in UTC.

   Answer2026-03-09 05:20:11 UTC

   The extraction line carries the examiner station's local time, 08:20:11 at UTC+03:00, which is 05:20:11 UTC. The file times in the listing below it are already UTC. The same log uses two time bases.

   Where

   acquisition-log.txt, the Extraction line; the label says local time and the offset.

   Usual mistake

   Taking 08:20:11 as UTC, or subtracting the offset from the file times that are already UTC.

   Local time to UTCSOP-08 Examination notes and working record

5. Q051 point

   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.

   Answer55

   (226632 − 32) ÷ (24 + 4096) = 55 frames, with no partial frame at the end. The file size says nothing about how many of them still count: that is the next three questions.

   Where

   File size from acquisition-log.txt or from the file system; frame size 4120 from the page size in Q3.

   Usual mistake

   Dividing the file size by 4096 and ignoring the headers.

   SOP-05 Mobile device acquisition

6. Q062 points

   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.

   Answer22

   Frames 1 to 22 carry the header's salts and a valid checksum chain. From frame 23 on, the salts differ: those 33 frames were written in an earlier WAL generation and were never overwritten. A reader stops at the first frame that does not match.

   Where

   chatstore.db-wal: header offsets 16–23 hold the salts; each frame header repeats them at offsets 8–15 of the frame header.

   Usual mistake

   Counting all 55 frames, or counting by page number instead of by salt.

   SOP-05 Mobile device acquisition

7. Q072 points

   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.

   Answer16

   Commit frames are 3, 6, 9, 10, 11, 12, 15 and 16, each recording a database size of 8 pages. Frame 16 is the last one. The committed state of the database is the main file with the WAL read up to and including frame 16.

   Where

   chatstore.db-wal, the 4 bytes at offset 4 of each frame header (frame n starts at 32 + (n − 1) × 4120). Source: https://www.sqlite.org/walformat.html (frame header, validity rule).

   Usual mistake

   Taking frame 22, the last frame with the current salts, as the end of the committed part.

   SOP-05 Mobile device acquisition

8. Q082 points

   How many frames of the current generation lie after the last commit frame? They carry the right salts and checksums but no commit.

   Answer6

   Frames 17 to 22 are valid frames of a transaction whose commit frame never reached the file. They are not part of the database state. Frame 17 holds page 1, frames 18–20 pages 5–7, frame 21 page 9 and frame 22 page 10.

   Where

   chatstore.db-wal, frames 17–22: salts match the header, field at offset 4 is zero.

   Usual mistake

   Counting the 33 older frames as well, or counting none because 'nothing after the commit counts'.

   SOP-05 Mobile device acquisition

9. Q092 points

   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?

   Answer8

   Page 5 appears in frames 1, 4, 7, 10, 11, 12, 13 and 16. Only the newest of them, frame 16, supplies page 5 to a reader; the other seven are earlier committed versions of the same page that the file kept because it was never checkpointed.

   Where

   chatstore.db-wal, the first 4 bytes of each frame header give the page number; chatstore.db page 1 shows the root pages.

   Usual mistake

   Counting frame 18, which holds a later, uncommitted version of page 5, or counting only the newest frame.

   SOP-05 Mobile device acquisition

10. Q101 point

    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.

    Answer46

    46 rows, message numbers 1 to 46, the last sent at 06:52:30 UTC on 6 March. The main file shows the database as of its last checkpoint at 06:58:12 UTC (the file's modification time in the log), not as of the committed state.

    Where

    chatstore.db alone: SELECT COUNT(*) FROM message;

    Usual mistake

    Opening both files together and counting 50, which answers the next question.

    WhatsApp for Android messages (msgstore.db)SOP-05 Mobile device acquisition

11. Q112 points

    How many rows does the table message have in the committed state? Copy both files together into another empty folder and count the rows.

    Answer50

    50 rows. Messages 47 to 50 exist only in committed WAL frames. The stock-list messages that appear only in the frames after frame 16 are not rows of the committed state: the engine ignores them.

    Where

    chatstore.db and chatstore.db-wal together: SELECT COUNT(*) FROM message;

    Usual mistake

    Counting rows by reading raw frames (which would include the uncommitted ones), or stopping at the main file's 46.

    WhatsApp for Android messages (msgstore.db)SOP-05 Mobile device acquisition

12. Q122 points

    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.

    Answer2026-03-06 07:12:44 UTC

    sent_ms is 1772781164000, Unix epoch milliseconds: 2026-03-06 07:12:44 UTC, which is 10:12:44 local time at UTC+03:00. The row exists only in the committed WAL frames: the main file alone does not contain it.

    Where

    Committed state, SELECT sent_ms FROM message WHERE _id = 49;

    Usual mistake

    Dividing by 1000 wrongly, treating the value as seconds, or giving the local time without its offset.

    WhatsApp for Android messages (msgstore.db)Timestamp decoder

13. Q132 points

    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?

    AnswerB — 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.

    Message 43 says the time will follow; no earlier row names 16:00. Option c turns a sent time into a receipt time, option d invents a deletion the files do not show, and option a contradicts the row. Even b is bounded: it describes what this application stored under this phone's clock.

    Where

    Committed state, rows 43 and 49 of message (body, sent_ms).

    Usual mistake

    Reading 'sent' as 'received', or filling a gap in the data with a story about deletion.

    WhatsApp for Android messages (msgstore.db)SOP-09 Reporting and peer review

14. Q142 points

    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?

    AnswerB — 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.

    Frames 7 and 10 are valid committed frames that an older page version left behind, because the WAL was never checkpointed. They are earlier states, not deleted or recovered data, and they exist only while the WAL keeps them: another collection of the same phone could lack them. The wording changes only 'moved to' to 'is'; intent is not in the files.

    Where

    chatstore.db-wal frames 7, 10 and 16 (page 5); committed state, row 49 (body, edited_ms).

    Usual mistake

    Calling superseded frames 'deleted data' or reading a motive into an edit.

    F-04 Examination logSOP-09 Reporting and peer review

15. Q152 points

    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?

    AnswerB — 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.

    The frames after the last commit frame are the end of an unfinished transaction. A reader does not apply them, so the database never held those rows or that flag. Omitting them (c) hides a fact the reviewer needs; counting them (a) or explaining them (d) goes beyond the files. Say what is there, what status it has and what the files cannot tell.

    Where

    chatstore.db-wal frames 17–22 (no commit mark after frame 16); frame 21 is page 9.

    Usual mistake

    Treating a raw page in an uncommitted frame as database content because a hex editor shows it.

    F-04 Examination logSOP-09 Reporting and peer reviewSOP-05 Mobile device acquisition

03Reconstructed timeline

Saturday 14 March 2026, as the correct answers build it. Local time is UTC+3.

04Scoring

Questions 1–11 carry one point each and question 12 carries three: 24 points in all. The finding paragraph is not scored by points; mark it against the four criteria below and discuss it in the debrief.

Finding paragraph: 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.

05Debrief

1. Open nothing before you hash it. A WAL pair is the evidence type that changes by being looked at: an engine that opens it can checkpoint and delete the WAL, and the part that mattered is gone.
2. Three regions, three sentences. Committed state: what the database held. Frames after the last commit: an unfinished transaction that is described, not counted. Older frames of a previous generation: left over, not part of this database state.
3. A superseded frame is an earlier committed state of a page. It is not deleted data, it is not recovered data, and it exists only because the WAL was never checkpointed. Say that, and say another collection could differ.
4. sent_ms is what this application stored under this phone's clock. It is not a receipt time, not a server time and not a statement about the driver's phone. Neither received_ms nor edited_ms says who did what, or why.
5. The copy was a file-system extraction without the -shm file. That is normal: the shared-memory index is rebuilt by the engine and is not evidence of its own. Record its absence in the examination log instead of explaining it away.

06Model finding

The pair chatstore.db and chatstore.db-wal (SHA-256 recorded before examination) was examined on copies. The main file is in WAL mode with 4096-byte pages and contains 46 messages, the last sent at 06:52:30 UTC on 6 March 2026. The WAL holds 55 complete frames: frames 1 to 16 are committed (last commit frame 16, database size 8 pages), frames 17 to 22 are valid frames of a transaction that has no commit frame and are not part of the database state, and frames 23 to 55 carry the salt values of an earlier WAL generation. The committed state contains 50 messages. Message 49, sent from this phone, is stored with sent_ms 07:12:44 UTC (10:12:44 local time, UTC+03:00 as set out in the log) by that phone's clock; the message before it in the same chat that mentions the pickup, message 43 at 09:26:48 local time, states that the time will follow. An edit of message 49 is recorded at 07:14:20 UTC. The files do not show when the driver's phone received or displayed the message, who edited it, or why the later transaction was not completed. Confidence: high for the structure and the stored values; no conclusion is drawn about conduct.

Report anatomy and confidence scale →