About 5 GB of a 12.7 GB archive is tool output that is stored twice

[dqtz5vpvj9-create]

agentsview version

main at d0395b5 (schema data_version 96)

Install method

Built from source

OS / platform

Linux x86_64

Which agent and version

Mixed archive: Claude Code, Codex, Cursor, zcode; 1,637 sessions

Which model(s)

Not applicable

What happened, and what did you expect

Scope

One of three independent causes of a daemon footprint that tracks archive size instead of change rate; see #1584 (after-sync embedding refresh reads the whole corpus) and #1585 (glibc retention of freed SQLite memory).

What happened

A 12.7 GB sessions.db holds 170 MB of conversation text. Measured with dbstat and SUM(LENGTH(...)):

table	size	rows	of which
tool_calls	6,053 MB	545,069	result_content 5,341 MB, input_json 340 MB
tool_result_events	5,533 MB	463,378	content 5,232 MB
messages	384 MB	693,095	content 170 MB, thinking_text 16 MB
indexes, FTS, signals	about 700 MB

Only 23 of the 545,069 tool calls have more than one result event. For every other call, tool_calls.result_content (the summary produced by SummarizeToolResultEvents) is byte-for-byte the single event's content, so about 5.2 GB, 41% of the archive, is the same text stored twice. Tool output is 83% of the file; conversation text is 1.3%.

Every cost that scales with archive size inherits the amplification: page cache pressure (a full resync streams 13 GB through the cache), resync duration, backup size, and the full-parse re-read paths.

Expected behavior

result_content should be derived, not stored, when it equals the single event's content: leave it NULL and read it from tool_result_events through the existing (session_id, tool_call_message_ordinal, call_index) key, keeping result_content_length so list views need no join, and store the summary only for the few calls whose events differ. That removes 5.2 GB with one data_version bump; per-call reads add one indexed lookup. A follow-up could compress tool_result_events.content at rest (tool output is highly repetitive) and would take the remaining 5.2 GB to roughly a quarter, but the deduplication alone is the simple, large win.

Sample session file or snippet

SELECT COUNT(*) FROM (
  SELECT session_id, tool_call_message_ordinal, call_index, COUNT(*) c
  FROM tool_result_events GROUP BY 1,2,3 HAVING c > 1);   -- 23
SELECT SUM(LENGTH(result_content))/1048576 FROM tool_calls;         -- 5341
SELECT SUM(LENGTH(content))/1048576 FROM tool_result_events;        -- 5232

Steps to reproduce

1. Open any sizeable archive read-only and run the three queries above.
2. Compare with SELECT name, SUM(pgsize)/1048576 FROM dbstat GROUP BY name.

Checklist

• I searched existing issues
• I removed secrets and private data from any attached session files