SQLite forensics: why deleting rows doesn't erase secrets (FTS, free pages, VACUUM)

2026-08-16 · 3 min read

You deleted the row. The secret is gone from the app, the queries return nothing, and the dashboard is clean. In SQLite — the database behind most session stores, browser profiles, and agent state files — that delete is a fiction. The bytes are still in the file.

Three ways deleted data survives

1. Free pages. SQLite doesn’t zero out the space a deleted row occupied. The page is marked free and added to the freelist; the old bytes stay until they’re overwritten by a future write. A file that’s been deleted-from is a forensics goldmine: recover the freelist pages and the “deleted” rows come back.

2. FTS virtual tables. If the database uses SQLite’s full-text search (FTS5), the FTS index keeps its own copies of the indexed text, maintained separately from the source tables. Delete the row from the source table and the FTS index still contains the tokens — searchable. This is the one that catches people: their app shows the secret is gone, and the FTS index still has it.

3. WAL and journal files. In WAL mode, recent writes live in the -wal file; transactions in the -journal file. Both can retain pre-delete content until checkpointed or cleaned.

“Deleted” in SQLite means “no longer referenced”, not “no longer present”.

What erasure actually requires

Making a secret physically disappear from a SQLite database takes three operations, in order:

  1. Replace the value everywhere it lives. Known secret values get replaced across all tables; pattern matches (API key formats) get masked. Two layers, because you can’t enumerate every secret that leaked.

  2. Rebuild the FTS indexes. INSERT INTO t(t) VALUES('rebuild') style rebuilds, or drop/recreate the virtual tables — so the index no longer contains the old tokens.

  3. Run VACUUM. VACUUM rewrites the entire database file, copying only live data into a fresh file — free pages with old bytes are discarded in the process. After VACUUM, the file’s raw bytes no longer contain the secret. (Note: VACUUM doesn’t shrink WAL files; those need a checkpoint too.)

The design that makes scrubbing safe

Retroactive scrubbing is a destructive operation, so the tooling around it matters as much as the SQL:

  • Dry-run by default. Scan and report per-table hit counts without writing a byte. You see what would be scrubbed before anything changes.
  • Backup option. Keep a .bak copy before applying changes.
  • Minimum length guard. Don’t let pattern rules shred short, low-value strings — only secrets above a minimum length get masked.

Where this bites agent setups

Agent session databases are exactly the SQLite databases where this matters: they accumulate everything the agent read, including the config values, environment dumps, and debug output that occasionally contain keys. A credential strategy that includes “the past is scrubbed” closes the loop that prevention and DLP can’t — the retrospective exposure.

trustless implements this as trustless dlp scrub-db: two-layer redaction (known values + gitleaks-style patterns), FTS rebuild, VACUUM, dry-run default, --backup, and --min-len — with the same pattern rules that power the outbound DLP. The command reference is in the documentation.

Next time someone tells you a secret was “deleted” from a database, ask them two questions: did the FTS index get rebuilt, and did anyone run VACUUM?

← Blog