[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.

[Quicksaver]

I've written up a concrete proposal for this problem that moves archived conversations into compressed storage, with a demo: #15122. Feedback is welcome there.

[XDGFX]

Another data point, on 0.0.45 (macOS). state.sqlite is 3.2 GB after 1,201 threads since mid-May, and only 40 MB of that is projection_thread_messages.

Table	Size
orchestration_events	1.47 GB
projection_thread_activities	0.92 GB
Indexes and receipts	~0.6 GB

Almost all of it postdates #6675, so the remaining growth isn't the old O(N²) tool.updated bug. It's completed tool payloads, each stored twice: once in the thread.activity-appended event and once in the activity row. By item type, from projection_thread_activities alone:

Item type	Calls	Size
image_view	1,017	219 MB
command_execution	53,229	177 MB
dynamic_tool_call	4,998	85 MB

image_view results are inline base64 PNGs, so about 1,000 images account for roughly 440 MB across both tables. Storing images as attachments or by content hash, and capping stored command output, would remove most of the growth even before a retention policy. Archiving into compressed storage (#15122) would cover the rest.