🐛 [BUG]logs/db SQLite file grows unboundedly due to missing VACUUM

[mattslack-db]

Problem

The ~/.apx/logs/db SQLite database grows without bound because deleted rows are never reclaimed. On my machine it reached 111 GB, of which only ~10 GB is actual data — the remaining ~101 GB is free/unused pages.

Root Cause

The cleanup logic in crates/db/src/logs.rs correctly deletes rows older than 7 days (RETENTION_SECONDS = 604800) every hour via cleanup_old_logs(). However, SQLite's DELETE only marks pages as free — it does not return disk space to the OS. No VACUUM or auto_vacuum pragma is used anywhere, so the file only ever grows.

With ~3M rows/day being written by the flux OTLP collector, the churn is significant and the wasted space accumulates quickly.

Suggested Fix

One or more of:

1. PRAGMA auto_vacuum = INCREMENTAL set at database creation time, with periodic PRAGMA incremental_vacuum calls after cleanup
2. Periodic VACUUM after the hourly cleanup_old_logs() (though this rewrites the entire DB and may be expensive)
3. Row count or file size cap as an additional safeguard

Workaround

Users can manually reclaim space:

sqlite3 ~/.apx/logs/db "VACUUM;"

Or delete the file entirely and let apx recreate it:

rm ~/.apx/logs/db ~/.apx/logs/db-wal ~/.apx/logs/db-shm 2>/dev/null

Environment

• macOS (Darwin 25.3.0)
• apx installed via ~/.local/bin/apx
• SQLite version 3.46.0