tracking: SQLite data integrity — unbounded prompt bloat (no cap/retention/VACUUM) and busy_timeout=0 drops writes under contention

[thedotmack]

Root Cause

user_prompts.prompt_text stores entire UserPromptSubmit payloads verbatim (~990KB average, duplicated in sdk_sessions.user_prompt), with no retention policy, size cap, or VACUUM, causing databases to grow to 100+ GB. Separately, the SQLite connection runs with busy_timeout=0, so any write under lock contention immediately fails and the observation is silently dropped. These are distinct but both SQLite data-integrity bugs.

Tracked Items

Issues

• Closes Database grows unbounded: prompt_text stored verbatim (×2), no retention, no VACUUM #2793 — Database grows unbounded: prompt_text stored verbatim (×2), no retention, no VACUUM #2793
• Closes Worker drops observations under lock contention (busy_timeout=0 → 500 "database is locked") #2821 — Worker drops observations under lock contention (busy_timeout=0 → 500 "database is locked") #2821

PRs

• Related PR fix(sqlite): reclaim residual prompt and queue bloat (refs #2793) #2904 — fix(sqlite): reclaim residual prompt and queue bloat (refs #2793) #2904
• Related PR fix(sqlite): apply busy_timeout to primary SQLite connections (closes #2821) #2849 — fix(sqlite): apply busy_timeout to primary SQLite connections (closes #2821) #2849

Resolution

This is the canonical tracking issue for this root cause. Individual issues and PRs listed above will be closed in favor of this one.