Storage: report database size and free space, create new stores with incremental auto-vacuum, and add an offline bibcode storage compact #230

Summary

BiBCode's project-data database (state.sqlite) never gives disk space back. When rows are deleted, SQLite keeps the freed pages inside the file, and BiBCode never runs VACUUM. bibcode storage inspect doesn't show how big the file is or how much of it is free, so there's no way to tell if a store has grown. There's also no safe, supported way to shrink it: the recovery guide says not to touch state.sqlite while BiBCode runs, and there is no compact command.

This issue does three things:

1. bibcode storage inspect reports file sizes, free pages and the auto_vacuum mode.
2. New databases are created with auto_vacuum=INCREMENTAL.
3. A new offline bibcode storage compact command takes a verified backup, refuses to run while the store is in use, then VACUUMs. It has a --dry-run mode.

T3 Code shipped the same kind of repair as t3 doctor (references below).

Current behavior (verified at a0ea849ef92b)

Connection setup (apps/server/src/persistence/database.rs)

• open_connection (:752-806) sets busy_timeout 5 s (BUSY_TIMEOUT, :25), foreign_keys, synchronous=FULL (:793), journal_mode=WAL (:797), wal_autocheckpoint 1,000 pages (:800, :26) and journal_size_limit 64 MiB (:803, :27).
• No auto_vacuum, so every database is created with SQLite's default, NONE. auto_vacuum can only be switched on before the first table exists, or later with a full VACUUM. Nothing in apps/server/src runs VACUUM, auto_vacuum or freelist_count (searched).
• Online backup: backup_to / backup_to_cancellable (:629-724) copies 128 pages per step, is cancellable, and the copy is switched to journal_mode=DELETE. Also checkpoint_wal with TRUNCATE (:647-660) and quick_check (:726-737).

CLI (bibcode storage …, "Inspect or explicitly recover offline project data.")

• Subcommands inspect, restore --backup-id, start-empty, each with --json (apps/server/src/config.rs:671-727). run_storage_command is at apps/server/src/lib.rs:185-238.
• inspect output (lib.rs:188-217): classification, storageInstanceId, backups, backupIssues, requestedRoot, effectiveRoot, isFilesystemAlias, issue. No file size, WAL size, page counts or auto_vacuum mode.
• inspect_store (apps/server/src/persistence/backup.rs:607) reads only file existence and the marker; it never opens the database and takes no lock. The desktop recovery dialog uses it too (apps/desktop/src-tauri/src/data_safety.rs:459 → apps/web/src/components/desktop/ProjectDataRecoveryDialog.tsx).

What a compact command can reuse (backup.rs)

• OfflineRecoveryGuard (:351-367) takes an exclusive try_lock on the runtime lock and fails with RecoveryError::StoreRunning ("the project-data store is currently owned by a running server", :242) when a server holds it. restore and start-empty use it (:840, :1018).
• StoreOperationGuard (:376-405) serializes storage operations on .bibcode-storage.lock.
• create_verified_backup (:521) writes a verified generation. Triggers are only PreMigration and PreUpdate (BackupTrigger, :40-43, serialized kebab-case in the manifest). Retention keeps 3 generations (RETAINED_GENERATIONS, :29; apply_retention, :2159).
• The pre-update backup flow is in apps/server/src/persistence/maintenance.rs:320-403.

What deletes rows today

• Activity retention (apps/server/src/activity/repository.rs:36-41, apps/server/src/activity/projection.rs:21) and reserved command receipts (apps/server/src/persistence/repositories.rs:643) delete rows. Orchestration events are never deleted (no DELETE FROM orchestration_events). So free pages build up mostly from activity and receipts, and the file never shrinks.

UI – apps/web/src/routes/settings.diagnostics.tsx has no storage section.

Docs – docs/guides/project-data-recovery.md:10-13: "Do not copy, rename, delete, or open state.sqlite … while any BiBCode process is running … then use the desktop recovery dialog or the offline bibcode storage inspect command."

Existing tests (apps/server/tests/project_data_safety.rs)

• recovery_refuses_to_mutate_a_store_owned_by_a_running_server (:1294).
• recovery_inspection_reports_verified_store_state_without_mutating_it (:1332).
• recovery_inspection_reports_an_absent_root_as_first_run_without_creating_it (:1396).
• recovery_restore_preserves_the_live_store_before_installing_a_verified_backup (:815).

Not measured. I have no real BiBCode database sizes or free-page ratios, and no timing for VACUUM on a large store.

Proposed change

1. storage inspect reports space. Add a database object:
   • fileBytes, walBytes, shmBytes: from file metadata. These are always safe to read.
   • pageSize, pageCount, freelistCount, reclaimableBytes (freelistCount × pageSize) and autoVacuum (none / full / incremental): read through a read-only connection only when the offline guard can be taken. Otherwise report null with "databaseStatsUnavailable": "store-running".
     Inspection must stay non-mutating. A read-only open of a WAL database can create -shm/-wal files, so the PR either uses immutable=1 / nolock while holding the offline guard, or proves no new files appear (the :1332 test checks this).
2. New databases use incremental auto-vacuum. In the first-run path, set PRAGMA auto_vacuum = INCREMENTAL before the first table is created and before journal_mode=WAL, as T3 does (Sqlite.ts:25). Existing databases keep NONE until they are compacted. Optionally, the server runs PRAGMA incremental_vacuum(N) in small steps when idle; this can be its own follow-up.
3. bibcode storage compact [--dry-run] [--json]. Offline only:
   1. Take OfflineRecoveryGuard and StoreOperationGuard. If either fails, refuse with the existing "store is owned by a running server" error, and say to close BiBCode, its local/WSL backends and the background service (bibcode service).
   2. Open the database with busy_timeout = 0. If it's locked, refuse without retrying.
   3. Check free disk space. VACUUM needs room for a full copy, so refuse with the numbers if there isn't enough.
   4. --dry-run: print the inspect numbers and what would happen (backup size, expected reclaim, auto_vacuum change), then exit having changed nothing.
   5. Otherwise take a verified backup with create_verified_backup and a new BackupTrigger::PreCompact, then run quick_check, PRAGMA auto_vacuum = INCREMENTAL, VACUUM, wal_checkpoint(TRUNCATE) and quick_check again.
   6. Report the size before and after, and the backup id. If any step after the backup fails, leave the database as SQLite left it (VACUUM is atomic) and print the backup id to restore with bibcode storage restore.
4. Design doc first. This changes persisted shape (auto_vacuum on new stores, a new backup trigger in manifests) and adds a CLI write path. A docs/plans/YYYY-MM-DD-storage-compaction-design.md needs approval before implementation. It should settle the open questions below.

T3 Code reference (their claims, not measured by us)

• pingdotgg/t3code#19436, "t3 doctor reclaims database space":
  • t3 doctor, --fix and --dry-run (dry run wins over --fix);
  • takes an exclusive SQLite lock and refuses while any server or desktop app has the database open;
  • stops and restarts the background service around the repair;
  • verifies projections before and after compaction;
  • they report a copy of a real database going from 7.02 GiB to 3.83 GiB, auto_vacuum 0 → 2, in 183 s.
  • Their files: DatabaseMaintenance.ts (inspect with page_count/freelist_count/page_size/auto_vacuum at :112-115; lock with busy_timeout=0 and locking_mode=EXCLUSIVE at :133-134; compact with auto_vacuum=INCREMENTAL, VACUUM, wal_checkpoint(TRUNCATE) at :195-197). They take no backup; this issue asks for one.
• #19435, "compact superseded history in the background", and #21880, "keep compaction cadence across restarts": deleting superseded events (see Not in scope).
• #19437, "suggest t3 doctor when the database has space to reclaim": a UI notice, recommended above 1 GiB.
• #21122, "opening a large database no longer blocks startup for minutes": incremental_vacuum(1) steps (DatabaseStorage.ts:62-90). T3's Sqlite.ts also sets a 32 MiB WAL limit (:12), auto_vacuum on zero-table databases (:25), synchronous=NORMAL (:32), and clears sqlite_stat1 with ANALYZE sqlite_schema (:46-49).
• #20948, "t3 doctor can delete the database from before 0.1.0": not relevant to BiBCode.

Acceptance criteria

• Approved design doc docs/plans/YYYY-MM-DD-storage-compaction-design.md, linked by a commit-pinned URL.
• Rust tests in apps/server/tests/project_data_safety.rs, hermetic (TestSandbox, temp roots):
  • recovery_inspection_reports_verified_store_state_without_mutating_it is extended: database.fileBytes, pageCount, freelistCount and autoVacuum are present and correct for a fixture with deleted rows, and the directory listing is unchanged afterwards (no new -wal/-shm);
  • new: inspecting while a server owns the store reports file sizes and databaseStatsUnavailable: "store-running", and changes nothing;
  • new: a first-run store reports autoVacuum: "incremental"; a pre-existing fixture store keeps "none" until compacted;
  • new: compact --dry-run reports the expected reclaim and leaves the database bytes, the backups directory and the marker unchanged;
  • new: compact on an offline store with free pages creates exactly one verified pre-compact backup, shrinks the file, leaves freelistCount at 0 and autoVacuum at incremental, and every project, thread and message still reads back the same;
  • new, modeled on recovery_refuses_to_mutate_a_store_owned_by_a_running_server (:1294): compact while a server is running fails with StoreRunning, and nothing is written (no backup, no change to the database);
  • new: compact against a database another connection holds locked refuses without waiting, and no backup is taken;
  • new: not enough free disk space (through a test hook) refuses before the backup;
  • the existing restore and start-empty tests still pass. A pre-compact backup can be restored with restore --backup-id.
• Unit tests for the CLI parsing in apps/server/src/config.rs (storage compact, --dry-run, --json).
• If the desktop recovery status gains the new fields: apps/web/src/state/projectDataSafety.test.ts and ProjectDataRecoveryDialog.test.tsx still pass, and contract fixtures are regenerated (vp run check:contracts).
• Older-app compatibility: an older BiBCode reading a store with a pre-compact backup manifest still inventories its other backups (test or documented decision).
• Existing behavior unchanged: startup, migrations, pre-update backups and retention of 3 generations (or the design's new rule).
• Desktop e2e: vp run test:ui:desktop still passes (main-window.e2e.ts, platform-capabilities.e2e.ts).
• Gates: cargo fmt --all --check, cargo clippy --workspace --all-targets -- -D warnings, cargo test --workspace, vp check (zero warnings), vp run typecheck, vp run check:contracts, Release Smoke.
• UI.md review recorded for the CLI messages (actionable errors, safe defaults). vercel-react-best-practices review recorded if any web code changes. docs/testing/ runbooks are reviewed and updated or stated "remain accurate".
• Profiler coverage line in the delivery report. Compaction start, finish and failure are recorded without paths or user data (new events documented in docs/skills/bibcode-profiler/references/).

Living docs to update

• docs/guides/project-data-recovery.md: a "Reclaim database space" section (storage inspect fields, storage compact, --dry-run, the backup it takes, how to restore).
• docs/architecture/overview.md: store pragmas (auto_vacuum on new stores).

Not in scope

• Pruning event history (deleting superseded orchestration_events, as T3 #19435 does). It changes what history is kept and needs its own design; follow-up issue.
• Background incremental_vacuum while the server runs (optional follow-up).
• A settings or diagnostics UI notice like T3 #19437.
• Changing synchronous=FULL, the WAL limits or busy_timeout.

Related

• Performance: measure load bytes and time on a 3G link and gate them with a CI byte budget #225 (load measurement), which may want the database size as a data point.
• The project-data safety work in docs/superpowers/plans/2026-08-09-project-data-safety.md (backups, offline recovery).

Open questions

• Should compact stop and restart the background service like t3 doctor does, or only refuse and say how to stop it?
• Does a pre-compact backup count toward the 3 retained generations? If so, two compactions would push out the last pre-update backup.
• Should inspect show page stats while the server runs (read-only open under WAL), or only offline?
• Is there a size or ratio threshold where inspect should recommend compacting (T3 uses 1 GiB)?
• When event history pruning lands, should compact run it first, as T3's doctor does?