Audit SQLite maintenance, migration, WAL, and prune boundaries

[Widthdom]

Context

Dogfooding found a large SQLite maintenance and migration surface. Read-only checks worked in the current workspace, but apply/write maintenance modes remain only partially exercised.

Representative search inventory:

dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search PRAGMA --format count --limit 100
dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search transaction --format count --limit 100
dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search migration --format count --limit 100
dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search WAL --format count --limit 100
dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search integrity_check --format count --limit 100
dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search --recipe sqlite-query-policy-surfaces --format count --summary-only --json --limit 5

Observed baseline:

• PRAGMA: 218 hits across 57 files.
• transaction: 112 hits across 28 files.
• migration: 121 hits across 36 files.
• WAL: 144 hits across 45 files.
• integrity_check: 50 hits across 14 files.
• sqlite-query-policy-surfaces: 658 hits across 55 files.
• db --integrity-check --json succeeded with integrity_ok.
• db prune --dry-run --json succeeded with zero orphan rows.
• db schema --summary-only --json --limit 20 returned zero entries by design while object counts reported tables/indexes/triggers.

Audit questions

• Are WAL/read-only/immutable fallback paths, checkpoints, busy timeouts, and stale snapshot diagnostics tested across normal and failure cases?
• Do migrations and user_version compatibility checks fail safely on newer/older databases and partial migration attempts?
• Does db prune --apply protect active rows and report deleted counts/transactions accurately under interruption?
• Are maintenance commands (optimize, vacuum, prune, integrity-check, schema) consistent about JSON shape, truncation metadata, and support-safe diagnostics?

Acceptance criteria

• SQLite maintenance commands have representative tests for read-only, dry-run, apply, failure, and cancellation cases.
• Migration and compatibility diagnostics are stable and actionable without raw SQL/path leakage.
• WAL and vacuum/optimize/prune guidance is consistent with status.maintenance_guidance.
• Summary-only DB schema output clearly distinguishes intentional row omission from truncation.

[Widthdom]

Additional dogfood findings: SQLite query policy count breakdown

A later pass expanded the SQLite recipe baseline with per-query counts.

Representative command:

dotnet ./src/CodeIndex/bin/Debug/net8.0/cdidx.dll search --recipe sqlite-query-policy-surfaces --format count --limit 200

Observed additions:

• Total: 658 hits across 55 files.
• CommandText: 134 hits across 26 files.
• CreateCommand: 130 hits across 25 files.
• ExecuteReader: 28 hits across 10 files.
• ExecuteNonQuery: 26 hits across 5 files.
• ExecuteScalar: 57 hits across 19 files.
• PRAGMA: 46 hits across 15 files.
• BeginTransaction: 33 hits across 10 files.
• codeindex_meta: 31 hits across 14 files.
• user_version: 27 hits across 14 files.
• read-only: 46 hits across 24 files.
• Migration: 30 hits across 8 files.
• AddWithValue: 0 hits, which is useful positive evidence for typed parameter policy.

Follow-up angle: keep the audit focused on SQLite command/reader ownership, typed parameter policy, PRAGMA allowlisting, migration compatibility, read-only fallback, and transaction/cancellation behavior.

[Widthdom]

Additional runtime coverage from the 2026-07-07 pass.

Maintenance probes:

• db --integrity-check --json completed with integrity_ok.
• db prune --dry-run --json completed with zero orphan rows.
• vacuum --dry-run --json completed with freelist_count_before: 3993, estimated reclaimable bytes 16355328, and recommended_command:"none".
• backfill-fold --dry-run --json completed with was_already_complete:true, fold_ready_before:true, and fold_ready_after:true.
• diff against the same DB returned identical:true.

Follow-up angle:

• Keep dry-run maintenance output aligned on whether estimated reclaimable pages are advisory only or imply a recommended action.

[Widthdom]

Round 4 dogfood exercised real temp-DB maintenance paths and found one user-facing diagnostic wrinkle to keep in this audit.

Newly exercised against /private/tmp

• Real import <archive> --db /private/tmp/... --json.
• backfill-fold --dry-run --json.
• Real backfill-fold --no-checkpoint --json no-op path.
• optimize --json.
• vacuum --dry-run --json.
• Real vacuum --json.
• db prune --dry-run --json.
• Real db prune --apply --json.
• Real db checkpoint <name> --json.
• db checkpoints --list --json.
• status --db /private/tmp/... --check --json.

Observations

• Real vacuum --json reduced DB file size from 305790976 bytes to 283979776 bytes, but WAL grew from 7992832 bytes to 22775392 bytes during the command. A later status call showed WAL back at 4152 bytes. The before/after fields look accurate, but the command output can be surprising without explaining WAL checkpoint timing.
• db checkpoint <name> --json succeeded and wrote the checkpoint. A list call run in parallel with creation initially returned an empty list; a later serial list showed the checkpoint. This is likely command ordering, but maintenance diagnostics/tests should avoid implying a checkpoint was lost.
• db prune --dry-run and db prune --apply completed cleanly on the imported temp DB.

Related probes

• backfill-fold: 133 matches / 40 files.
• no-checkpoint: 9 / 6.
• fold_ready_before: 5 / 4.
• was_already_complete: 4 / 3.
• writes_since_optimize: 5 / 4.
• fts_optimize: 8 / 5.
• pages_reclaimed: 1 / 1.
• freelist_count: 13 / 9.
• auto_vacuum: 24 / 12.
• wal_size_bytes: 12 / 8.
• sqlite_validate: 4 / 3.
• database_entry: 3 / 2.
• sha256: 119 / 46.
• pruned_paths: 5 / 3.

[Widthdom]

Round 5 dogfood added corrupt-DB error coverage.

Newly exercised

• db integrity --db /private/tmp/.../corrupt.db --json where corrupt.db is a non-SQLite text file.
• status --db /private/tmp/.../corrupt.db --json on the same file.

Observations

• db integrity --json returned a JSON error envelope with E008_DB_ERROR, which is the right shape for automation.
• status --db corrupt.db --json returned plain text instead of JSON; I cross-posted that JSON-contract part to Audit CLI output stream and JSON contract boundaries #4319.
• The corrupt DB path is a useful regression test for early SQLite-open failures across maintenance/status commands.

Related probes

• db integrity: 50 matches / 15 files.
• integrity_check: 50 / 14.
• file is not a database: 135 / 36.
• SQLite Error 26: 10 / 2.
• corrupt: 33 / 19.

[Widthdom]

Work started on branch fix-issue4302-4338.