Practice data only. Every note, name and detail in this lab is fictional, and "Jotter" is a made-up notes app. We built jotter.db to practice carving a deleted SQLite record by hand. It is not an extraction from a real device, but the SQLite structures it shows are real.
The scenario
The same storage-unit case. The person of interest's phone has a notes app, Jotter, and your tool reports five notes. The IDs run 1, 2, 3, 5, 6. Note 4 is missing, and the detective wants to know what it said.
Jotter keeps its database in rollback-journal mode, so there's no WAL to fall back on. If note 4 still exists, it's in the free space of the page that held it. Your tool's "recovered records" view shows nothing. Time to read the bytes yourself.
Download
Download the lab file (ZIP, 1 KB)
| File | SHA-256 |
|---|---|
jotter.db | 89f6d9ac3479be04669311dcaac908d540141cbfc27d05d833a3eebccd678551 |
validate-this-04.zip | bbafaf9f5494a03695ab435bd32f05bd236040aa61c278ace618b4c1a4605cc4 |
Nothing to install: open jotter.db in the Lab Tools SQLite viewer to see the live notes and the schema, then open it in the Hex Viewer, which can jump to any offset and reads 2-byte values for you. Keep a copy of the SQLite file format documentation open for the record format.
The challenge
- How many live notes are there, and which ID is missing? Does a missing ID prove a note was deleted?
- What is the page size, and which page is the root of the
notetable? At what file offset does that page start? - Read the page's 8-byte header: page type, first freeblock, number of cells, start of the cell content area. Is the freeblock's offset in the cell pointer array?
- Go to the freeblock. What are its "next freeblock" and "size" values, and where does it end?
- Compare the freeblock with a live cell. Which of the original cell's bytes did the freeblock header overwrite, and what did they hold?
- Decode the serial types that survived. What type and length is each column?
- Recover the note: its created time (Eastern), title, body and folder.
- What can't you recover or prove? Write the finding without overstating it.
Check your answers
Short answers to the key facts. Answers are checked in your browser against hashed values, so the page source won't give them away. Your entries are saved on this device only.
note tableHints
Hint 1: finding the page
Run SELECT name, rootpage FROM sqlite_master; in the SQL tab. The page size is the 2-byte big-endian value at offset 16 of the file. Page n starts at offset (n − 1) × page size. Page 1 is the only page whose header comes after the 100-byte file header.
Hint 2: the page header
On a table leaf page, byte 0 is 0D. Bytes 1–2 give the offset of the first freeblock, bytes 3–4 the number of cells, and bytes 5–6 the start of the cell content area. All offsets are from the start of the page, not the file. The cell pointer array (2 bytes per live cell) follows at byte 8.
Hint 3: reading a record
A table leaf cell is: payload length (varint), rowid (varint), then the record. The record starts with its header length (varint), followed by one serial type per column. Serial type 0 is NULL, 1 is a 1-byte integer, 5 is a 6-byte integer, 9 is the constant 1, and an odd number 13 or above is text of (N − 13) ÷ 2 bytes. A varint byte of 0x80 or more means "more bytes follow".
Hint 4: the missing bytes
When SQLite frees a cell, it writes a 4-byte freeblock header (2 bytes next-pointer, 2 bytes size) over the first 4 bytes of the cell. Decode a live cell first, such as note 5's, to see what those first bytes normally hold. Then use the schema and the freeblock size to work out what was lost.
Walkthrough
Show the full solution
1. The live notes
Five live notes, IDs 1, 2, 3, 5 and 6. id is an INTEGER PRIMARY KEY, so SQLite normally assigns one more than the largest ID in use. A gap in the middle strongly suggests that a note 4 existed and was deleted, but it isn't proof by itself. An app can also assign IDs on its own. Keep looking.
2. The page
Offset 16 holds 10 00: the page size is 4,096 bytes. sqlite_master lists note with root page 3, which starts at (3 − 1) × 4,096 = 8,192 = 0x2000. The table is small enough to fit on that one leaf page.
3. The page header
0x2000: 0D 0E D6 00 05 0E 6C 00 | 0F C8 0F 82 0F 4D 0E 9E 0E 6C
type freeblk cells content frag cell pointer array (5 cells)
0D: table leaf page.0E D6: first freeblock at page offset0xED6(file offset0x2ED6).00 05: 5 live cells.0E 6C: cell content starts at0xE6C.- The five cell pointers are
0xFC8,0xF82,0xF4D,0xE9Eand0xE6C.0xED6is not among them: no live record points there.
4. The freeblock
0x2ED6: 00 00 00 77 05 1B 81 4D 01 01 A1 B4 1E 16 F1 73 74 6F 72 61 67 65 67 61 74 65 ... 02
next size serial types created (6 bytes) "storage" "gate ..." folder
Next freeblock is 00 00 (this is the only one), and the size is 0x0077 = 119 bytes, so it runs from 0x2ED6 to 0x2F4C. The next byte, 0x2F4D, is the start of note 3's cell, which is in the pointer array.
5. What the freeblock header overwrote
Decode a live cell first. Note 5 starts at page offset 0xE9E: 36 05 06 00 05 1B 53 09. That's a payload of 54 bytes (0x36), rowid 5, a 6-byte record header, then the serial types 00 05 1B 53 09 for the five columns. The id column's serial type is 00 (NULL) because an INTEGER PRIMARY KEY is stored as the rowid, not in the record.
So the first 4 bytes of the deleted cell held the payload length, the rowid, the header length, and the id column's 00. The freeblock header replaced all four. What survives starts with the serial type for created.
6. The surviving serial types
| Bytes | Serial type | Column | Meaning |
|---|---|---|---|
05 | 5 | created | 6-byte integer |
1B | 27 | title | text, (27 − 13) ÷ 2 = 7 bytes |
81 4D | 205 | body | 2-byte varint: (0x01 × 128) + 0x4D = 205; text of (205 − 13) ÷ 2 = 96 bytes |
01 | 1 | folder_id | 1-byte integer |
Check the math. The record header would have been 7 bytes (its length byte, 00, 05, 1B, 81 4D, 01). The payload is 7 + 6 + 7 + 96 + 1 = 117 bytes, which fits in a 1-byte payload-length varint. Add 1 byte for a small rowid and the cell is 1 + 1 + 117 = 119 bytes, exactly the freeblock's size. That agreement is what makes the interpretation defensible.
7. The recovered note
- created:
01 A1 B4 1E 16 F1= 1,794,023,233,265. As Unix milliseconds, that's 2026-11-07 03:47:13 UTC, or 10:47:13 PM EST on November 6, 2026, one day before the break-in. - title: "storage"
- body: "gate code 4471#. unit 214, row C. cameras face the office side. go after 10 when the gate closes"
- folder_id:
02= 2, which thefoldertable names Private.
8. Limits, and the finding
The rowid was overwritten, so you cannot show that this record was note 4. The ID gap and the record's position on the page, between notes 3 and 5, are consistent with it, and nothing more. You also can't say when it was deleted; the database doesn't record that. And treat created as what the column is named until you have validated what Jotter actually writes there, using test data.
A deleted record was recovered from a freeblock at file offset 0x2ED6 (page 3, the root page of the note table) in jotter.db. The record's first four bytes, which held its payload length, rowid and record header length, had been overwritten by SQLite when the space was freed; the remaining fields were decoded manually and were consistent with the note table's schema and with the freeblock's 119-byte size. The record contains a created value of 1794023233265 (2026-11-06 22:47:13 EST, interpreted as Unix milliseconds), the title "storage", a body beginning "gate code 4471#. unit 214, row C", and folder_id 2 ("Private"). Its original ID could not be recovered; the gap between notes 3 and 5 is consistent with ID 4.
Go further
This lab shows why "the tool didn't recover anything" isn't the end of the exam. SQLite internals, record carving and timestamp validation are covered in depth, with graded labs, in our examiner course, Mobile Forensics: A Bit of Knowledge.