# `sqlite-forensic` Test Corpus Catalog

This is the per-repo record of the SQLite test fixtures under the repo-root
`tests/data/` (shared by both workspace members; co-located detail in
`tests/data/README.md`). It mirrors the fleet-wide catalog discipline
(`issen/docs/corpus-catalog.md`); the verbatim generator for each synthetic
fixture is recorded here so the corpus is reproducible. The committed fixtures
**are** in git (only `/target`, `/tools`, and `/tests-oracle-corpus` are
gitignored), but the generators are kept here regardless so anyone can rebuild or
vary them.

All fixtures were built with the system `sqlite3` CLI / Python `sqlite3` module:
`SQLite 3.45.3 2024-04-15` (CLI version string above).

> Follow-up (flagged, NOT done this round): promote these entries into the
> fleet-wide `issen/docs/corpus-catalog.md` and add the missing
> `forensicnomicon::sqlite` constants (B-tree page-type bytes, serial-type rules,
> reserved-space offset 20, in-header DB-size offset 28, freelist-count offset 36,
> WAL salt/checksum offsets). Both are owned by other live repos this round.

## Classification

The corpus spans three provenance classes, labelled per section below:
`REAL-engine`/`SYNTHETIC` (built locally with the real `sqlite3` engine — real
engine, our data: §A–§E, §J, §L, §M, §N, §O); `REAL-ext` (externally-authored real
artifacts — the Nemetz corpus §I, NIST CFReDS §K, SharifCTF §K, the DC3 corpus §G);
and `REAL-device` (genuine device data — the Josh Hickman iOS-17 images §P).
Confidence `✓` throughout (each generator was run and the file inspected, or the
external artifact downloaded and its schema/ground-truth parse confirmed — not just
named).

## §A `tests/data/places.db`  (pre-existing, WS-C spike)

Single-table `moz_places` DB exercising every storage class + the rowid-alias
rule. The verbatim generator is in `tests/data/README.md` (§`places.db`).

- md5 `f07a69d05358f227e2120080370bbb6b`, 8192 bytes (2 pages, 4096-byte page).

## §B `tests/data/overflow.db`  (overflow-page chain)

One `notes` row whose ~12 KB TEXT body spills onto an overflow-page chain, plus
one small row that fits on the leaf. Drives `core/tests/overflow.rs`.

```sh
python3 - <<'PY'
import sqlite3
con = sqlite3.connect('overflow.db')
con.executescript("PRAGMA page_size=4096; PRAGMA auto_vacuum=NONE;")
con.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT)")
big = "OVERFLOW_PAYLOAD_" + ("ABCDEFGHIJ" * 1200)  # 12017 chars
con.execute("INSERT INTO notes VALUES (1, ?)", (big,))
con.execute("INSERT INTO notes VALUES (2, 'small row')")
con.commit(); con.close()
PY
```

- `notes` root page = 2; row id=1 body length = 12017; 4 pages total.
- md5 `1c17320320a173fb5968c598f9df7373`, 16384 bytes.

## §C `tests/data/deleted_places.db`  (deleted-record carving)

`moz_places` with 400 rows inserted, ids 201..=400 `DELETE`d **without VACUUM**,
under `secure_delete=OFF` so the freed leaf pages retain the deleted records.
This is the carving fixture (`forensic/tests/carve.rs`,
`forensic/tests/audit_realdb.rs`) and the freelist fixture
(`core/tests/freelist.rs`).

```sh
python3 - <<'PY'
import sqlite3
con = sqlite3.connect('deleted_places.db')
con.executescript("""
PRAGMA page_size=4096; PRAGMA auto_vacuum=NONE; PRAGMA secure_delete=OFF;
CREATE TABLE moz_places(id INTEGER PRIMARY KEY, url TEXT, title TEXT,
  visit_count INTEGER, last_visit_date INTEGER, frecency REAL);
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 400)
INSERT INTO moz_places SELECT n,
  'https://site-'||n||'.example.com/path/page',
  'Title for record number '||n||' SECRETMARKER',
  n%100, 1700000000000000+n, n*1.5 FROM seq;
DELETE FROM moz_places WHERE id > 200;
""")
con.commit(); con.close()
PY
```

- Ground truth: live rows = 200 (ids 1..=200), deleted ids 201..=400;
  `PRAGMA freelist_count` = 5, `PRAGMA page_count` = 13; `moz_places` root = 2.
- md5 `16682d7df99b1e8a89287a508d95eb47`, 53248 bytes.

> Note: `secure_delete` defaults to **ON** on this build; without the explicit
> `PRAGMA secure_delete=OFF` the deleted content is wiped and nothing is
> carvable. Many real-world browser DBs run with secure_delete off, so this is a
> realistic — not contrived — recovery scenario.

## §D `tests/data/wal_places.db` + `…-wal`  (read-only WAL overlay)

A main DB + persistent `-wal` sidecar captured **mid-transaction**: a held reader
connection blocks the checkpoint so the WAL survives on disk with one committed
COMMIT frame (page 2) that the main file does not yet reflect. Drives
`core/tests/wal.rs` and the WAL branch of `forensic/tests/audit_realdb.rs`.

```sh
python3 - <<'PY'
import sqlite3, shutil
con = sqlite3.connect('wal.db')
con.executescript("""
PRAGMA page_size=4096; PRAGMA journal_mode=WAL; PRAGMA wal_autocheckpoint=0;
CREATE TABLE moz_places(id INTEGER PRIMARY KEY, url TEXT, title TEXT,
  visit_count INTEGER, last_visit_date INTEGER, frecency REAL);
INSERT INTO moz_places VALUES (1,'https://www.rust-lang.org/','Rust',5,1700000000000000,2000.5);
INSERT INTO moz_places VALUES (2,'https://github.com/','GitHub',12,1700000100000000,5500.0);
""")
con.commit()
con.execute("PRAGMA wal_checkpoint(TRUNCATE)"); con.commit()  # baseline → main file
reader = sqlite3.connect('wal.db')                            # hold a read txn
reader.execute("BEGIN"); reader.execute("SELECT count(*) FROM moz_places").fetchone()
con.execute("PRAGMA wal_autocheckpoint=0")
con.execute("UPDATE moz_places SET title='Rust (EDITED IN WAL)', visit_count=777 WHERE id=1")
con.execute("INSERT INTO moz_places VALUES (3,'https://wal-only-row.example/','WAL-ONLY ROW',1,1700000200000000,100.0)")
con.commit()
shutil.copy('wal.db','wal_places.db')          # snapshot while WAL is live
shutil.copy('wal.db-wal','wal_places.db-wal')
PY
```

- Ground truth: main-only view = id=1 title `Rust`, visit_count 5, 2 rows;
  WAL-applied view = id=1 title `Rust (EDITED IN WAL)`, visit_count 777, plus
  id=3 `WAL-ONLY ROW`, 3 rows. WAL = 1 COMMIT frame for page 2.
- md5 `wal_places.db` = `bad96eb068359bcb142533696b6515fc`, 8192 bytes.
- md5 `wal_places.db-wal` = `84b08a77d90914c917d92e60a6c8eeab`, 4152 bytes.

## §E `tests/data/updated_messages.db`  (prior-version / version-aware carving)

A `messages` table where row 7's `body` is **`UPDATE`d twice** (grow then shrink)
under `secure_delete=OFF`, so the intermediate pre-edit version survives in freed
slack with **the same rowid as the live row but different values** — the
edited-message / changed-amount evidence. Drives `forensic/tests/prior_version.rs`
and the prior-version leg of `forensic/tests/oracle_differential.rs`.

```sh
python3 - <<'PY'
import sqlite3
con = sqlite3.connect('updated_messages.db')
con.executescript("""
PRAGMA page_size=4096; PRAGMA auto_vacuum=NONE; PRAGMA secure_delete=OFF;
CREATE TABLE messages(id INTEGER PRIMARY KEY, sender TEXT, body TEXT, amount INTEGER);
""")
con.executemany("INSERT INTO messages VALUES(?,?,?,?)",
                [(n, f"user{n}", f"ORIGINAL message body number {n} ZZZ", 707) for n in range(1, 51)])
con.commit()
# Edit row 7's body twice: grow forces the cell to relocate (freeing the old slot),
# then shrink leaves the intermediate version recoverable in freed space.
con.execute("UPDATE messages SET body=? WHERE id=7",
            ("PRIORVERSION secret message body that was later edited " + ("Q" * 120),))
con.execute("UPDATE messages SET body='EDITED final body' WHERE id=7")
con.commit(); con.close()
PY
```

- Ground truth: 50 live rows (ids 1..=50); live row 7 body = `EDITED final body`.
  The recoverable prior version is rowid 7, body `PRIORVERSION secret …`, amount 707
  — a genuine deleted record whose rowid is still live with different values. The
  full original body (`ORIGINAL message body number 7 ZZZ`) survives nowhere (it was
  overwritten); only the intermediate `PRIORVERSION` version is cleanly carvable.
- md5 `e1edbb56bf37efa6a7c1e738040f1360`, 8192 bytes.

> Note: a same-size in-place `UPDATE` overwrites the cell without freeing the old
> version, so no prior version survives. The grow-then-shrink edit forces a
> relocation (freed old cell) whose prefix survives intact in slack — the realistic
> shape of an edited message in a chat/SQLite store.

## §F Independent oracle tools (VENDORED, not committed)

Four independent reference carvers validate `carve_deleted_records` (differential
methodology in `docs/validation.md`; the head-to-head harness is
`forensic/tests/nemetz_tool_comparison.rs`; the fixture differential is
`forensic/tests/oracle_differential.rs`). `tools/` is gitignored — none of the
tool sources are **committed**; these entries are their provenance record. The
thin normalizing wrappers in `scripts/` (`run-bring2lite.sh`, `run-sqlite-dissect.sh`)
**are** committed and are the stable interface the harness shells out to.

### §F.1 `undark` (C) — test gate `UNDARK_BIN`

- Classification: `VENDORED` (third-party tool), confidence `✓` (built and run).
- Tool: `undark` 0.7.1, Paul L. Daniels.
- Upstream: <https://github.com/inflex/undark>
- Source tarball (master): <https://github.com/inflex/undark/archive/refs/heads/master.tar.gz>
- Source tarball sha256 `c0a9ee7ebd180727deef52fbafe0ef0e2b7c9b43c5604761bfeb86bc9306912a`.
- Build (macOS/clang): hoist the nested `swap64`/`ntohll` out of `decode_row` to
  file scope and rename `ntohll` → `u_ntohll` (collides with the macOS
  `<sys/_endian.h>` macro), then `make`. Patched source kept at
  `tools/undark.c.patched` (gitignored). See `docs/validation.md` for the exact
  recipe.
- CLI: `undark -i <db>` dumps all reconstructable records as CSV
  (`rowid,id,col1,col2,…`); deleted rows = recovered rowids absent from the live
  b-tree.

### §F.2 `fqlite` (Java) — test gate `FQLITE_TAP`

fqlite's CLI was removed at v2.0, but its carving engine (`fqlite.base.Job`) is
plain Java that populates a result list the GUI merely reads. A headless
source-instrumentation tap drives it with no JavaFX UI — so fqlite IS usable as
an oracle, the CLI cancellation was the only blocker.

- Classification: `VENDORED` (third-party tool, source-instrumented), confidence
  `✓` (built and run).
- Tool: `fqlite` 4.22, Dirk Pawlaszczyk.
- Upstream: <https://github.com/pawlaszczyk/fqlite>
- Commit: `26922bd9e3cdc60c93b72dfb1fb2f5972a0af6a6`.
- Build: clone at the commit, null-guard the unguarded `gui.add_table(...)` calls
  in `Job.java`, stub the `rag`/`erm` LLM packages, compile the engine + the
  `HeadlessTap` driver against **OpenJFX 22.0.2 SDK** + `commons-codec-1.17.1` /
  `jspecify-1.0.0` / `antlr4-runtime-4.8` / `sqlite-jdbc-3.51.1.0` (OpenJDK 25,
  `--release 21`, `--add-modules javafx.base,javafx.graphics,javafx.controls`).
  Full recipe in `tools/fqlite/README.md`; engine API map + the JavaFX-coupling
  findings (relevant to a future upstream CLI revival) in
  `tools/fqlite/ENGINE_NOTES.md`. Both gitignored.
- Invocation: `FQLITE_JAVA=<jdk-25>/bin/java tools/fqlite/run-tap.sh <db>` → CSV
  `rowid,col1,col2,…` of recovered DELETED rows (rowid `-1` when the header rowid
  is unrecoverable; the fqlite comparison is keyed by content).
- Paper false-positive run (identical bytes): on `tests/data/paper_fp/f.db` (0F)
  it recovers 11/50 deleted freelist rows with 0 live false positives; on `b.db`
  (0B) it recovers the 5 surviving OLD residue rows with 0 false positives. See
  `docs/competitive-landscape.md`.
- WAL limitation (scenario 10): the `-wal` reader (`WALReader`) is instantiated by
  a JavaFX `ImportDBTask`, and the WAL table wiring in `Job.processDB()` is inside
  `if (gui != null)` blocks, so `Job.run()` headless leaves `job.wal == null` and
  recovers nothing from a WAL-only file. The tap sets `readWAL`/`walpath` and
  drains `job.wal.resultlist`, but the GUI-coupled instantiation is not reachable
  without reconstructing the `ImportDBTask` flow — so the WAL scenario keeps
  FQLite's cited (paper) figure; no measured WAL number is fabricated.

### §F.3 `bring2lite` (Python 3) — test gate `BRING2LITE_CMD`

A freeblock / freelist / unallocated-area carver. Its CLI path imports PyQt5 at
module load (the `Visualizer` is never used in `--gui 0` mode), and the Python-3
source emits `SyntaxWarning`s for `is`-with-literal comparisons.

- Classification: `VENDORED` (third-party tool), confidence `✓` (run on the full
  0C/0D/0E head-to-head scope).
- Tool: `bring2lite` (Bring2lite), Python 3.
- Upstream: <https://github.com/bring2lite/bring2lite>
- Commit: `e876bf28c1ba03fc598d92832374f72794760ca1`.
- Upstream identity sha256: `main.py`
  `5654260c3c9131a70957b6375d6d86ffc6700c95cce0a813e81a7b989984fe94`,
  `classes/gui.py`
  `9273ea13001b96ef53255b084f58d27ebb6b6a69d1153039712bc48660280ea4`.
- Setup recipe (all under the gitignored `tools/bring2lite/`):
  1. `git clone` at the commit; copy the `bring2lite/` package to
     `tools/bring2lite/pkg`.
  2. Replace the `is`/`is not` literal comparisons with `==`/`!=` in
     `classes/{gui,sqlite_parser,journal_parser,visualizer}.py` (clears every
     `SyntaxWarning`; behaviour-preserving).
  3. A headless **PyQt5 shim** (`tools/bring2lite/shim/PyQt5/`) provides inert
     stubs so the top-level `from PyQt5.QtWidgets import …` in `visualizer.py`
     loads on a host without PyQt5. `scripts/run-bring2lite.sh` prepends the shim
     to `PYTHONPATH` **only when a real PyQt5 is absent** (a genuine install
     always wins); no Qt symbol is ever called in CLI mode.
- CLI: `python3 main.py --filename <db> --out <dir> --format CSV`. Output is a tree
  of per-page `.log` files; the carved-deleted records land in `freeblocks/`,
  `freelists/`, and `unalloc-parsing/` (the `regular-page-parsing/` tree is the
  live b-tree, not a recovery claim).
- Invocation (the harness gate): `BRING2LITE_CMD=scripts/run-bring2lite.sh`. The
  wrapper runs the tool into a temp dir and emits one recovered record per line as
  `col0,col1,col2,…` (the same row shape undark emits), suppressing the live
  `regular-page-parsing/` re-dump. Its `(col1,col2)` identity is at CSV fields 1/2.

### §F.4 DC3 SQLite Dissect (`sqlite_dissect`, Python) — test gate `SQLITE_DISSECT_CMD`

A record-level carver from the DoD Cyber Crime Center. SQLite carving is off by
default; with it enabled (`-c -f`) it recovers deleted cells from free blocks and
freelist pages (and the journal/WAL), exporting per-column CSV rows that the
wrapper normalizes to column order so the head-to-head's `(col1,col2)` identity is
at fields 1/2.

- Classification: `VENDORED` (third-party tool), confidence `✓` (run on the full
  0C/0D/0E head-to-head scope).
- Tool: SQLite Dissect 1.0.0, DoD Cyber Crime Center (DC3); see upstream for license.
- Upstream: <https://github.com/dod-cyber-crime-center/sqlite-dissect>; install via
  `pip install sqlite-dissect`.
- Invocation (the harness gate): `SQLITE_DISSECT_CMD=scripts/run-sqlite-dissect.sh`,
  which runs `sqlite_dissect <db> -c -f -e csv` and emits one carved record per
  line. **Measured:** `0C` recall 0.607 / precision 0.689; `0D` 0.895 / 0.857;
  `0E` recall 0.750 but **633 phantoms + 7 live re-reads** (precision 0.005) — its
  aggressive freelist carving (marked "under development" in its own help) is a
  precision contrast to the structural live-row exclusion (see
  `docs/recovery-comparison.md`). Its DC3-authored test databases are separately
  used as independent carving input (§G).

## §G `tests-oracle-corpus/dc3-sqlite-dissect/`  (REAL-ext, not committed)

Independent third-party SQLite databases authored by the Department of Defense
Cyber Crime Center (DC3) as the `sqlite_dissect` project's test corpus. Used as
**independent input** for the differential carving validation: neither the input
DB nor the oracle (`undark`) is ours. `tests-oracle-corpus/` is gitignored — the
DBs are **not committed**; this entry + `tests-oracle-corpus/README.md` are their
provenance record.

- Classification: `REAL-ext` (externally-authored real artifacts), confidence `✓`
  (downloaded and inspected; SQLite magic + schema confirmed per file).
- Source: <https://github.com/dod-cyber-crime-center/sqlite-dissect> →
  `sqlite_dissect/tests/test_files/` (raw base
  <https://raw.githubusercontent.com/dod-cyber-crime-center/sqlite-dissect/master/sqlite_dissect/tests/test_files/>).
- Forensic cases exercised (the load-bearing point — these reach scenarios our
  whole-freed-page fixture cannot): `corpus_01-01.db`/`corpus_01-02.db`,
  `corpus_03-02.db`, `corpus_07-01.db` are **in-page free-block deletions**
  (`freelist_count = 0` — deleted rows live inside still-allocated b-tree pages);
  `corpus_0A-01.db`/`corpus_0A-02.db` are **dropped tables** (no table in
  `sqlite_master`). Our freelist-only carver recovers 0 from all of these — the
  documented scope boundary in `docs/validation.md`.

sha256 (full list in `tests-oracle-corpus/README.md`); the six DBs wired into the
differential test:

| file | sha256 | md5 | bytes |
|---|---|---|---|
| `corpus_01-01.db` | `8438a5533586e7e0f38628330d615aeaa057ebb9698c1103424d8128e417875e` | `4ac52776c7d21f0beb38d456452ca2f6` | 8192 |
| `corpus_01-02.db` | `508fb80ce083bc6ad79d2921b1d35d998724e808a72d05476671010b1265043b` | `57f88570e289df9919bd900f24b7a026` | 8192 |
| `corpus_03-02.db` | `7ea933d7082d3ec0cdc9f5ca3e39624d80c0da495a365d520424a69a1937f138` | `9c0a90eeb78cd24d5b4004c157d8618f` | 12288 |
| `corpus_07-01.db` | `6e110c0663be9500e817ab0d6153f0f1aaa7d8831e7e17a05e2565abbbf9e4da` | `7f8f9e9b4d6aa971b9f0c5d16b6c2419` | 81920 |
| `corpus_0A-01.db` | `c640727d2fe3e269d196e64c25cf896e9fa21c2626d4f6b88398274c4e1691d1` | `a174174a3f98fe7733e4a32e7aab86b7` | 8192 |
| `corpus_0A-02.db` | `030fd0a82fa37707f448e90a21bc178f120b018b009999daaefdc61d04b24d24` | `c1be2eb3388bc294ec0deecb334180b9` | 8192 |

## §I `tests/data/nemetz/`  (REAL-ext, CC0, **committed**)

The **SQLite Forensic Corpus** (Nemetz, Schmitt & Freiling, DFRWS-EU 2018, plus
the anti-forensic extension) — a third-party dataset that ships, per database, an
`.xml` answer key tagging every deleted row with its full decoded content. This is
independent deleted-record **ground truth**: unlike our `deleted_places.db`
fixture (we authored both the deleter and the carver), here a third party authored
the deletions *and* the answer key, so a recall/precision number against it is
real. It drives `forensic/tests/nemetz_metrics.rs` (the per-DB confusion matrix),
the panic-free `forensic/tests/nemetz_robustness.rs` real-data proof, and is the
basis of `docs/recovery-comparison.md`.

- Classification: `REAL-ext` (externally-authored real artifacts), confidence `✓`
  (downloaded, extracted, SQLite magic + schema + answer-key parse confirmed per
  file). **Committed** (CC0 public domain — redistribution unrestricted).
- Authors: Sebastian Nemetz, Sven Schmitt, Felix Freiling (FAU Erlangen-Nuremberg).
- Paper: <https://doi.org/10.1016/j.diin.2018.01.015>.
- Download (v2.0): <https://downloads.digitalcorpora.org/corpora/sql/sqlite_forensic_corpus_v2.0.zip>
  (302 → `digitalcorpora.s3.amazonaws.com`; `curl -L`). Zip md5
  `02aa205efa80757602a2911156db79a6`.
- **Full v2.0 corpus vendored: 141 databases across 23 categories** — the
  14-category standardized corpus (`01`–`0E`) plus the 9-category anti-forensic
  extension (`11`–`19`), as `.db`+`.xml`+`.sql` per fixture. Per-category counts:
  `01`:18 `02`:7 `03`:5 `04`:6 `05`:4 `06`:4 `07`:4 `08`:1 `09`:1 `0A`:5 `0B`:2
  `0C`:10 `0D`:8 `0E`:2 `11`:5 `12`:6 `13`:8 `14`:8 `15`:13 `16`:2 `17`:13 `18`:5
  `19`:4. The eight categories with per-row deleted ground truth
  (`07`,`0A`,`0B`,`0C`,`0D`,`0E`,`17`,`18`) are scored for recall/precision; the
  rest describe only LIVE content and are parse/format fixtures, NOT scored as
  deleted-recall (the answer key has no deleted set to invent one from). The
  full per-file md5 manifest, the 23-category table, and the deleted-vs-parse
  classification live in `tests/data/nemetz/README.md` — the single detailed index
  for this dataset (cross-referenced, not duplicated here).
- **Real robustness finding:** vendoring category `12` (Manipulated Left Child
  Page Pointers) exposed a genuine stack-overflow in the b-tree walkers
  (`collect_rows`/`collect_rowids`/`walk_table_page`), which bounded only total
  page COUNT, not recursion DEPTH — a manipulated child pointer forming a cycle
  recursed ~1M frames deep before stopping. Fixed by a visited page-set (each page
  descended at most once), so the parser degrades gracefully (partial rows) instead
  of aborting. `nemetz_robustness.rs` now runs the full pipeline over all 141 DBs
  panic-free.
- Ground-truth manifest `tests/data/nemetz/nemetz_ground_truth.json` is generated
  from the `.xml` answer keys by the committed
  `tests/data/nemetz/gen_ground_truth.py`; the harness reads the manifest, never
  the `.xml` at test time. The generator's `substrate_recoverable` rule now decides
  the **overflow** class via `chain_followable` (task #73): a deleted overflow row
  counts as recoverable iff its freed overflow chain is followable through freelist
  leaves to a byte-exact reassembly of the expected payload (pure-bytes, independent
  of our carver). Regenerate with `python3 tests/data/nemetz/gen_ground_truth.py`.
- **In-code synthetic fixtures (no committed files)** for chain-aware overflow
  recovery (task #73): `synth_db` / `synth_spilled_prefix` / `synth_clobbered_spill_db`
  in `core/src/lib.rs` (test module) build minimal multi-page images (intact-prefix
  spilled cells, freed leaf/trunk chains, and the freeblock-clobbered-spill case that
  has NO corpus instance — `SYNTHETIC`, unproven-by-corpus). They produce no
  `tests/data/` artifacts; the builders are the generator of record.

## §J `tests/data/wal_carve.db` + `…-wal`  (WAL-frame deleted-residue carving)

A main DB + persistent `-wal` sidecar where the genuinely-different deleted rows
live **only in the uncheckpointed WAL frames**, never on the main file's pages.
A `wal_checkpoint(TRUNCATE)` first flushes a clean baseline (rows 1..=50) to the
main file and empties the WAL; then — with a held reader blocking any further
checkpoint — rows 101..=150 are inserted (COMMIT) and 121..=140 deleted (COMMIT),
with **no checkpoint**. So the freed-cell residue for 121..=140 exists only in the
`-wal` frames; the on-disk pages never held rows 101..=150. Drives the WAL-frame
carving tests in `core/tests/wal.rs` and `forensic/tests/carve_all.rs` (#60).

```sh
python3 - <<'PY'
import sqlite3, shutil, os
for f in ('walcarve.db','walcarve.db-wal','walcarve.db-shm'):
    if os.path.exists(f): os.remove(f)
con = sqlite3.connect('walcarve.db')
con.execute("PRAGMA page_size=4096")
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA wal_autocheckpoint=0")
con.execute("CREATE TABLE msg(id INTEGER PRIMARY KEY, sender TEXT, body TEXT)")
for i in range(1, 51):
    con.execute("INSERT INTO msg VALUES (?,?,?)", (i, f"alice{i}", f"baseline message {i}"))
con.commit()
con.execute("PRAGMA wal_checkpoint(TRUNCATE)"); con.commit()   # baseline → main file, WAL emptied
reader = sqlite3.connect('walcarve.db')                        # hold a read txn (blocks checkpoint)
reader.execute("BEGIN"); reader.execute("SELECT count(*) FROM msg").fetchone()
con.execute("PRAGMA wal_autocheckpoint=0")
for i in range(101, 151):
    con.execute("INSERT INTO msg VALUES (?,?,?)", (i, f"bob{i}", f"secret WAL body {i}"))
con.commit()                                                   # INSERT commit → WAL frame 0
con.execute("DELETE FROM msg WHERE id BETWEEN 121 AND 140"); con.commit()  # DELETE commit → WAL frame 1
shutil.copy('walcarve.db','wal_carve.db')          # snapshot while WAL is live
shutil.copy('walcarve.db-wal','wal_carve.db-wal')
reader.close(); con.close()
PY
```

- Ground truth: on-disk-only carve recovers **0** of the WAL-resident deleted rows
  (their bytes are not in the main file); WAL-frame carve recovers **20/20** rows
  121..=140, each tagged `RecoverySource::WalFrame` with `(salt1, salt2,
  frame_index)` provenance, and re-surfaces **0** surviving (live) rows (101..=120,
  141..=150). WAL = 2 COMMIT frames for page 2 (the INSERT commit, then the DELETE
  commit).
- md5 `wal_carve.db` = `6747389de0fefcc4c23543353a31325a`, 8192 bytes.
- md5 `wal_carve.db-wal` = `598e80ad38536f4b7a6cb51ddaedc767`, 8272 bytes.

## §K `tests/data/cfreds/` + `tests/data/sharifctf/`  (REAL-ext, **committed**)

The **NIST CFReDS / CFTT SQLite test sets** — the authoritative, U.S.-Government
reference data for SQLite forensic tool testing. Authored by NIST (created on real
Android `sqlite 3.19.0` / iOS `sqlite 3.32.3` devices) with published ground truth
and per-file MD5s; **10/10 `.sqlite` MD5s verified against NIST's published
hashes**. Public domain (17 U.S.C. § 105) — committed. Full provenance +
manifest: `tests/data/cfreds/README.md` (the single detailed index for this set).

- Classification `REAL-ext`, confidence `✓` (downloaded, MD5-matched to NIST,
  schema + ground-truth parse confirmed). Drives `core/tests/cfreds_encoding.rs`
  and `forensic/tests/cfreds_recovery.rs`.
- **SFT-01** (encodings): the same `Albums`/`Weekly_Ratings` schema stored as
  UTF-8 (4096 B page), UTF-16BE (1024 B), UTF-16LE (8192 B) on both platforms;
  ground truth = encoding, page size, journal mode, 100 rows. Validates the
  header-encoding decode against real-device data (the independent replacement for
  the self-minted `core/tests/utf16_text_tests.rs` fixtures).
- **SFT-03** (deleted & modified): `invoice_items` (~2240 rows), 100 deletes + 100
  `UPDATE … SET Quantity=200` modifications per variation.
  - *WAL* variation (uncheckpointed): main-only view = 2240 rows, WAL-applied =
    2140; our WAL handling surfaces both. Validated now.
  - *PERSIST* variation (rollback journal): the 100 deletes and 100 modifications
    survive in the `-journal` page images (header zeroed post-commit, bodies
    intact). `carve_rollback_journal` diffs the journal's pre-transaction snapshot
    against the live db and recovers **100/100 deletes + 100/100 modified prior
    values** (`forensic/tests/cfreds_journal_recovery.rs`); `audit_journal` raises
    the RECOVERABLE observation on it (`cfreds_journal_anomaly.rs`). Rollback-journal
    carving is designed in [`design/journal-recovery.md`](design/journal-recovery.md).
    This was the Doer-Checker payoff: real NIST ground truth surfaced a real
    recovery-substrate gap our synthetic fixtures never exercised, now closed.
- **SFT-05** (BLOB / data types): **not committed** — `SFT-05_android.sqlite` /
  `SFT-05_ios.sqlite` are ~206 MB each (gitignored/env-gated). Download both from
  the NIST CFReDS "SQLite Database containing BLOB data" Drive folder (link in
  `tests/data/cfreds/README.md`) into one directory and point
  **`SQLITE_FORENSIC_SFT05`** at it; `core/tests/cfreds_sft05_types.rs` then
  validates native-type + BLOB reading against the `new_students` table
  (`id INT PK, name TEXT, photo BLOB, gpa FLOAT, has_covid_vaccine BOOLEAN,
  year_graduated INT`; 100 rows, the `photo` BLOBs a variety of real graphic
  formats — PNG/JPEG/GIF/TIFF/BMP/ISO-BMFF/PDF, all read intact). Skips cleanly
  when the var is unset.

`tests/data/sharifctf/db0.db` — a real damaged-header SQLite db from SharifCTF 8
("crashed db"); the 100-byte header is overwritten so `Database::open` returns
`Err(BadMagic)`. Robustness artifact (`corrupted_header_fails_typed_not_panicking`).
Upstream write-ups repo has no licence; retained as an 8 KB CTF artifact under
fair-use with attribution (see `tests/data/sharifctf/README.md`).

## §L `tests/data/journal/`  (real-engine artifact / minted input, **committed**)

Four small artifacts the **real SQLite engine** wrote for two minted
rollback-`-journal` scenarios, driving the `audit_journal` anomaly arms in
`forensic/tests/hot_journal_anomaly.rs` against real engine output rather than
hand-encoded bytes. Real-engine / minted-input tier: the recipe is deterministic,
the journal nonce is engine-random, so the committed bytes are what the engine
produced. Public domain (minted with the public-domain SQLite engine, no
third-party content); full provenance + recipes + md5s in
`tests/data/journal/README.md`.

- `hot.db` + `hot.db-journal` — a Tier-A **hot** journal (valid magic, `n_rec=5`,
  5/5 checksum-valid, DML only). Page 1 is journaled but the schema cookie is
  unchanged (1 == 1), so HOT fires and SCHEMA-CHANGE does **not** — the negative
  oracle for the cookie comparison alongside NIST SFT-03 PERSIST.
- `ddl_persist.db` + `ddl_persist.db-journal` — a committed-DDL PERSIST journal
  (`ALTER TABLE … ADD COLUMN`). Live schema cookie (2) advanced past the journal's
  prior page-1 image cookie (1), so SCHEMA-CHANGE fires with both values shown.

## §N `tests/data/drop_recreate/`  (real-engine artifact, **committed**)

Five small real-engine databases (plus two `-journal` sidecars) that exercise the
`table_instance_risk` diagnostic **HINT** — Detector A (AUTOINCREMENT high-water
reconciliation) and Detector B (sidecar `-wal`/`-journal` schema change). The flag
is a hint that names its evidence; it is **not** an assertion that a predecessor
table existed. Construction reference: `docs/design/drop-recreate-attribution.md`;
full fixture table + ground truth in `tests/data/drop_recreate/README.md`.

- Classification: `REAL-engine` (minted with the public-domain SQLite engine via
  the committed `gen.py`; no third-party content), confidence `✓` (generated and
  the ground truth confirmed with the `sqlite3` CLI). **Committed** (CC0).
- Detector A — `rowid > sqlite_sequence` on an AUTOINCREMENT table: `b_autoinc.db`
  fires on residue rowids 6..10; `upd_autoinc.db` fires on rowid 1000 — a row a
  *current-instance* `UPDATE` moved past the high-water mark (proving A is a hint,
  not proof); `b_plainpk.db` (no AUTOINCREMENT) **never** fires — the honest limit
  that a same-schema, plain-PK drop+recreate is undecidable.
- Detector B — sidecar prior schema differs: `b_journal_altered.db` + `-journal`
  fires for `students` (the prior CREATE SQL lacks the later `ALTER`'s column);
  `b_journal_dml.db` + `-journal` (DML-only last txn) **never** fires. Detector B is
  table-level and deliberately does NOT fire on a same-schema drop+recreate or a
  `VACUUM` page move.
- Consumed by `forensic/tests/drop_recreate_risk.rs` (Detector A),
  `forensic/tests/detector_b.rs` (Detector B), the CLI provenance-column test, and
  the `core` prior-schema unit tests.
- md5 (the `.db` files are byte-reproducible; the `-journal` sidecars embed a random
  checksum nonce so their md5 varies per run — the tests read content, not hash):

  | file | md5 | bytes |
  |---|---|---|
  | `b_autoinc.db` | `b5f380a6376a8701e73514eb09a4ef27` | — |
  | `b_plainpk.db` | `042ab37d307951db79df011a9eb0deec` | — |
  | `upd_autoinc.db` | `6225cdb9cd88973bcad4a4325830c0a1` | — |
  | `b_journal_altered.db` | `3a77f03ea3ac1ef40f8e9b284af98a59` | — |
  | `b_journal_dml.db` | `2c1a405f4cc27856b367059554b319bf` | — |

## §O `tests/data/paper_fp/` false-positive scenarios  (real-engine **replication**, **committed**)

Real-engine **replications** of the three false-positive scenarios from the 2025
survey (Lee, Park, Lee & Choi, *FSI:DI* **55**, art. 302031,
[DOI](https://doi.org/10.1016/j.fsidi.2025.302031)). These reproduce the survey's
Table-5 *construction* with the real SQLite engine — they are **not** the authors'
byte-identical corpus (the official corpus is released "upon request" / not public
yet). Generator + full ground truth: `tests/data/paper_fp/README.md`.

- Classification: `REAL-engine` (minted by the committed `gen.py` via Python stdlib
  `sqlite3`; no third-party data embedded), confidence `✓`. **Committed** (CC0).
- `f.db` — **0F**, B-tree rebalancing (Type \*\*): live ids 51..80, deleted 1..50.
  Our carver excludes live rowids structurally → **0 live-row false positives**
  where `bring2lite` re-surfaces 13.
- `b.db` — **0B**, table reinsertion with the SAME schema (Type \*): live ids 1..5
  (`NEW-NAME`), dropped residue = 10 `OLD-NAME` rows. The genuinely-undecidable
  same-schema case.
- `wcase.db` + `wcase.db-wal` — **10**, WAL + `secure_delete=ON`: the residue lives
  **only** in the `-wal`; the main image holds zero message bodies. **FQLite's
  scenario-10 number is cited from the paper, not measured here** — its WAL recovery
  is GUI-coupled (see §F.2).
- Consumed by `forensic/tests/paper_fp_scenarios.rs` and the oracle comparison in
  [`competitive-landscape.md`](competitive-landscape.md).
- md5 (`.db` files byte-reproducible; `wcase.db-wal` is content-stable but
  salt-variant per run):

  | file | md5 | bytes |
  |---|---|---|
  | `f.db` | `a61a446a1cf0e5304956384b69644071` | 45056 |
  | `b.db` | `042ab37d307951db79df011a9eb0deec` | 8192 |
  | `wcase.db` | `22ebdd36e102f2af2f5766b7297dcad3` | 4096 |
  | `wcase.db-wal` | `baaf207913b60136c1762dbe435bb03e` | 16512 (content-stable, salt-variant) |

## §P Josh Hickman iOS-17 image corpus  (REAL-device, env-gated, **not committed**)

Genuine iOS-17 application SQLite databases from Josh Hickman's public reference
image — real-device data used as a **robustness sweep** (no-panic), NOT a
known-answer recall oracle. The full open → audit → carve pipeline must survive
every real db without panicking.

- Classification: `REAL-device` (third-party real-device artifacts), confidence `✓`
  (the sweep runs the pipeline over every db). **Not committed** — large, owned by
  the `issen` corpus; downloaded manually and read in place, env-gated like §G/§M.
- Test gate: `SQLITE_FORENSIC_IOS_CORPUS` (absolute path to the extracted corpus
  root). `forensic/tests/ios_realdata_robustness.rs` opens every `.db`/`.sqlite`/
  `.sqlite3` under it and asserts the pipeline never panics; it **skips cleanly**
  when the var is unset, so a plain `cargo test` stays green.

## §M `tests/data/paper_fp/large_messages.db`  (throughput benchmark, generated, **not committed**)

A ~100 MB messages-like database for the throughput benchmark that sits alongside
the survey's reported 100 MB timings (see `docs/competitive-landscape.md`
"Throughput"). Real-engine / minted-input tier: built by the committed generator
`tests/data/paper_fp/gen_large.py` via Python's stdlib `sqlite3`, deterministic on
the same engine. The **DB is large and gitignored** — documented here, downloaded
on demand, read in place by an env-gated test — exactly like §G and the other
large artifacts.

- Classification: `REAL-engine` (minted with the public-domain SQLite engine, no
  third-party content), confidence `✓` (generated and carved).
- Construction: one `messages(id INTEGER PRIMARY KEY, ts, sender, body)` table,
  178,000 rows with ~512-byte id-tagged bodies (`MSG-<id>-…`), `secure_delete=OFF`,
  `auto_vacuum=NONE`; then `DELETE WHERE id BETWEEN 40001 AND 120000` (an 80k-row
  contiguous middle subset, leaving live rows on both sides). Lands at ~100 MB
  on disk (freed pages retained). The deleted range is written to a sidecar
  `<db>.deleted.json` manifest so the test reads ground truth without hardcoding.
- Test gate: `SQLITE_FORENSIC_PERF_DB` (absolute path to the generated `.db`). The
  perf-smoke `forensic/tests/perf_large_carve.rs` carves it, asserts the deleted
  subset is recovered with zero live false positives, and enforces a generous
  120 s wall-clock ceiling so a catastrophic perf regression fails CI. It **skips
  cleanly** when the var is unset or the file is absent — a plain `cargo test`
  stays green and fast.
- Generate: `python3 tests/data/paper_fp/gen_large.py [out.db]` (defaults to
  `$SQLITE_FORENSIC_PERF_DB` or `large_messages.db` beside the script).

## §Q Freeblock / dropped-schema / NIST-DLC fixtures  (**committed**)

Small committed fixtures backing the recovery work added in v0.7.x. Full
provenance + generators are co-located in [`tests/data/README.md`](https://github.com/SecurityRonin/sqlite-forensic/blob/main/tests/data/README.md).

- **`tests/data/nist_dlc_snapshot.db`** — **REAL-ext, Tier 1, NIST public domain.**
  The Google Drive `snapshot.db` from the **NIST CFReDS Data Leakage Case**,
  recovered from a **Volume Shadow Copy** of the case's 20 GB PC image (the image
  itself is not committed). NIST's published answer to "what files were deleted
  from Google Drive?" is the independent ground truth; both deleted `cloud_entry`
  records — `do_u_wanna_build_a_snow_man.mp3` (clean) and the freeblock-clobbered
  `happy_holiday.jpg` — are recovered, the live `root` never re-surfaced
  (`nist_dlc_snapshot.rs`). Extraction recipe (7z → `.dd`, `mmls`, libvshadow VSC,
  pytsk3 NTFS) in `tests/data/README.md`.
- **`tests/data/freeblock_2byte_rowid.db`** — SYNTHETIC, Tier 2 (`sqlite3`-built;
  ground truth from construction). Non-adjacent high-rowid deletions; pins
  freeblock-clobbered **2-byte-rowid** (rowid ≥ 128) recovery (`freeblock_highrowid.rs`).
  Real-corpus twin: the env-gated `sqlite-unhide` `09.db`.
- **`tests/data/freeblock_coalesced.db`** — SYNTHETIC, Tier 2. **Adjacent**
  deletions coalesced into one multi-cell freeblock; pins span-level exact-tiling
  recovery (same test).
- **`tests/data/dropped_table_schema.db`** — SYNTHETIC, Tier 2. A dropped
  `secrets` table; pins `recover_dropped_schemas` + the
  `SQLITE-DROPPED-SCHEMA-RECOVERED` audit finding (`dropped_schema.rs`).

The env-gated **`sqlite-unhide`** corpus (nine author-keyed DBs; FREEWARE/home-use,
never committed) is documented in [`tests-oracle-corpus/README.md`](https://github.com/SecurityRonin/sqlite-forensic/blob/main/tests-oracle-corpus/README.md).

## §R Belkasoft "SQLite Exercises" corpus  (REAL-device, env-gated, **not committed**)

Genuine mobile/desktop **messenger and app** SQLite databases (Android/iOS WhatsApp,
Viber, Skype, Facebook Messenger, Safari, CarPlay) with live WAL and rollback-journal
sidecars — a real-world **robustness sweep** (no-panic over vendor data), the sibling
of the Josh Hickman iOS set (§P). It is **not** a known-answer recall oracle (no
committed deletion key); scored recall lives in Nemetz (§I) and CFReDS (§K).

- Classification: `REAL-device` (third-party real-device artifacts), confidence `✓`
  (the sweep runs the full open → audit → carve → history pipeline over every db).
  **Not committed** — Belkasoft's Terms of Use prohibit redistribution; the archive
  and everything extracted from it are gitignored. Provenance + hashes:
  [`tests/data/belkasoft/README.md`](https://github.com/SecurityRonin/sqlite-forensic/blob/main/tests/data/belkasoft/README.md).
- Source: the "Advanced SQLite Queries with Belkasoft" training,
  <https://belkasoft.com/advanced-sqlite-queries-with-belkasoft-training> (downloaded
  2026-07-16 as `SQLite Exercises.zip`, SHA-256
  `cf0c5485b39b609d3655529a20250b47e33b3b8d35377411e62171ed3e9d106b`). 52 SQLite dbs,
  28 live `-wal`, ~16 non-empty `-journal`; 44 UTF-8; no `WITHOUT ROWID`, no encrypted
  main dbs.
- Test gate: `SQLITE_FORENSIC_BELKASOFT_CORPUS` (absolute path to the extracted root).
  `forensic/tests/belkasoft_robustness.rs` finds every db **by file magic** (several
  vendor dbs are extensionless, e.g. `viber_messages`), applies live WAL/journal
  sidecars, and asserts the pipeline never panics; it **skips cleanly** when the var
  is unset, so a plain `cargo test` stays green.
- Yield: this corpus **found a real defect** — an unchecked `serial_body_len` sum in
  the inferred carver overflowed `usize` on a free-space serial near `i64::MAX`
  (panic in debug / silent wrap in release). Fixed with `checked_add` (`88ebfd9`);
  all 52 dbs now survive. The Doer-Checker discipline paying off on real data.

## §H MD5 manifest

Committed fixtures (under `tests/data/`, `tests/data/`):

| file | md5 | bytes |
|---|---|---|
| `tests/data/places.db` | `f07a69d05358f227e2120080370bbb6b` | 8192 |
| `tests/data/overflow.db` | `1c17320320a173fb5968c598f9df7373` | 16384 |
| `tests/data/wal_places.db` | `bad96eb068359bcb142533696b6515fc` | 8192 |
| `tests/data/wal_places.db-wal` | `84b08a77d90914c917d92e60a6c8eeab` | 4152 |
| `tests/data/wal_carve.db` | `6747389de0fefcc4c23543353a31325a` | 8192 |
| `tests/data/wal_carve.db-wal` | `598e80ad38536f4b7a6cb51ddaedc767` | 8272 |
| `tests/data/deleted_places.db` | `16682d7df99b1e8a89287a508d95eb47` | 53248 |
| `tests/data/updated_messages.db` | `e1edbb56bf37efa6a7c1e738040f1360` | 8192 |
| `tests/data/journal/hot.db` | `6dfd120f216ff997b819bdc755ea6431` | 20480 |
| `tests/data/journal/hot.db-journal` | `d428e2fcf8e6f3d9c71a58b18c6f4dcc` | 22016 |
| `tests/data/journal/ddl_persist.db` | `0271673fb35215d80f313e5f549dbbaf` | 16384 |
| `tests/data/journal/ddl_persist.db-journal` | `fe785dd18b5eb58b6dd4176ae5864130` | 8720 |
| `tests/data/drop_recreate/b_autoinc.db` | `b5f380a6376a8701e73514eb09a4ef27` | — |
| `tests/data/drop_recreate/b_plainpk.db` | `042ab37d307951db79df011a9eb0deec` | — |
| `tests/data/drop_recreate/upd_autoinc.db` | `6225cdb9cd88973bcad4a4325830c0a1` | — |
| `tests/data/drop_recreate/b_journal_altered.db` | `3a77f03ea3ac1ef40f8e9b284af98a59` | — |
| `tests/data/drop_recreate/b_journal_dml.db` | `2c1a405f4cc27856b367059554b319bf` | — |
| `tests/data/paper_fp/f.db` | `a61a446a1cf0e5304956384b69644071` | 45056 |
| `tests/data/paper_fp/b.db` | `042ab37d307951db79df011a9eb0deec` | 8192 |
| `tests/data/paper_fp/wcase.db` | `22ebdd36e102f2af2f5766b7297dcad3` | 4096 |
| `tests/data/paper_fp/wcase.db-wal` | `baaf207913b60136c1762dbe435bb03e` | 16512 |
| `tests/data/freeblock_2byte_rowid.db` | `e32a55e60a40e3072917d4d5cd3494f5` | 20480 |
| `tests/data/freeblock_coalesced.db` | `e064fd01f8040c49dc5cf8913532b36f` | 20480 |
| `tests/data/dropped_table_schema.db` | `69087f66e1fc37a47ebf1803d951301d` | 12288 |
| `tests/data/nist_dlc_snapshot.db` | `a37a765981eea87d2c2cd5f7be0c6c0a` | 20480 |

The `drop_recreate` and `paper_fp` `-journal`/`-wal` sidecars embed a per-run nonce,
so their md5 varies; the consuming tests read content, not hash. The 141 committed
Nemetz databases under `tests/data/nemetz/` (CC0, §I) have their own md5 manifest in
`tests/data/nemetz/README.md` to avoid duplicating it here.

Not committed (provenance only — see §F, §G and the per-directory READMEs):
`tools/undark`, the fqlite tap under `tools/fqlite/` (source, jars, built classes
— recipe in `tools/fqlite/README.md`), the `bring2lite` checkout + PyQt5 shim
under `tools/bring2lite/` (§F.3), the pip-installed `sqlite_dissect` (DC3, §F.4 —
no committed source, `pip install sqlite-dissect`), the DC3 corpus under
`tests-oracle-corpus/dc3-sqlite-dissect/` (full
sha256/md5 list in `tests-oracle-corpus/README.md`), the env-gated Josh Hickman
iOS-17 image corpus (`SQLITE_FORENSIC_IOS_CORPUS`, §P), and the ~100 MB throughput
db (`SQLITE_FORENSIC_PERF_DB`, §M). The committed `scripts/run-bring2lite.sh` /
`scripts/run-sqlite-dissect.sh` wrappers are the stable harness interface to the
gitignored tool sources.