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

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?