[Feature]: Add supported retention and compaction for unbounded local SQLite growth

[Jules-Astier]

Before submitting

• I searched existing issues and did not find a duplicate.
• I am describing a concrete problem or use case, not just a vague idea.

Area

apps/server

Problem or use case

T3 Code's local SQLite database grows without a supported retention or compaction path. Settling, archiving, or deleting a task does not appear to reclaim its persisted activity/event payloads; settling currently behaves like a UI/state override rather than a storage lifecycle operation.

On one macOS installation using 0.0.32-nightly.20260804.997, ~/.t3/userdata/state.sqlite reached 3.7 GiB with:

• 171 threads
• 389,181 orchestration events
• 388,131 command receipts
• 350,773 projected activity rows
• only 1,928 turns

A small number of long-running/tool-heavy threads dominated storage. One thread alone held about 215.8 MiB in projected activities. The database remained valid (PRAGMA quick_check = ok), so this was live retained data, not corruption or free-page bloat.

The size eventually contributed to V8 heap OOM crashes during hydration. The desktop UI reported these as a failed local environment connection to 127.0.0.1:3773, obscuring the storage problem.

An offline, backup-first maintenance pass that retained only the newest 500 projected activities per thread removed 300,475 projection rows and reduced the database from 3.7 GiB to 2.3 GiB after VACUUM. It preserved all events, receipts, messages, turns, and threads. That still leaves the append-only event copy unbounded.

Proposed solution

Add a supported storage-retention and compaction system for the local SQLite database:

1. Bound projected activity history per thread (for example, retain the newest 500 by default), with pagination/lazy loading if older activity remains available.
2. Compact or snapshot the append-only event history so obsolete activity/message deltas and command receipts do not grow forever.
3. Preserve unresolved approvals, user-input requests, current session state, final messages, checkpoints, and other state required to reconstruct the thread correctly.
4. Apply a documented lifecycle policy to settled, archived, and deleted threads. Settling could queue eligible compaction, or the UI should clearly state that it has no storage effect.
5. Run checkpoint/VACUUM safely when worthwhile, outside latency-sensitive paths.
6. Add a diagnostics/settings view showing database size, largest threads, retained activity/event counts, and a backup-first “Compact now” action.
7. Make retention thresholds configurable and provide a dry-run estimate before destructive compaction.

Why this matters

Long-lived T3 installations should not require manual SQLite surgery to stay usable. Unbounded growth consumes disk, increases startup/hydration cost, worsens replay behavior, and can crash the backend on machines with ordinary memory limits.

A supported retention policy would keep local installations stable while making the meaning of “settled,” “archived,” and “deleted” predictable.

Smallest useful scope

A first useful version could:

• retain only the newest 500 rows in projection_thread_activities per thread,
• always preserve unresolved approval/user-input activities,
• expose a backup-first offline “Compact database” action,
• checkpoint and VACUUM,
• report before/after sizes and run PRAGMA quick_check.

This alone reclaimed about 1.4 GiB in the installation above without removing conversation messages or event history.

Alternatives considered

• Manually deleting projection rows while T3 is fully closed, then checkpointing and vacuuming. This works but is unsupported and risky.
• Deleting entire threads/projects. This loses useful conversation history and still requires database compaction to reclaim disk.
• Treating “settled” as cleanup. It currently does not reduce database size.
• Increasing the V8 heap. This only postpones crashes and does not address disk growth.
• Turning off assistant streaming. This reduces one source of event amplification but tool activity payloads still dominate some databases.

Risks or tradeoffs

• Event compaction must preserve enough state for deterministic projection rebuilds or establish a new trusted snapshot boundary.
• Retention must not discard unresolved approvals, user-input requests, active-session state, or data needed for recovery.
• VACUUM can temporarily require substantial free disk and must not run while the database is active.
• Tool payloads may contain private code, paths, output, or credentials, so diagnostics should report aggregate sizes without uploading content.

Examples or references

Related but distinct reports:

• [Bug]: Per-chunk assistant persistence causes progressive live output lag and rapid SQLite growth #5110 — per-chunk assistant persistence causes rapid SQLite growth.
• [Bug]: t3 repeatedly crashing on long thread, says it is network connectivity, but is actually do to unbounded query caused by Backend OOM crash when loading a thread with large tool-activity history #5351 — unbounded thread activity hydration causes backend OOM.

Those issues describe important producers and consequences. This request is for the missing general-purpose retention, compaction, diagnostics, and task-lifecycle policy.

Contribution

• I would be open to helping implement this.