main regression: #326 daily_stats fix still fails at ~10 GiB RSS with internal OOM on 967k-message corpus

[etafund]

Summary

cass index --full still cannot get past the daily_stats repair on current upstream main, despite #326 being closed as fixed by 8110944f.

On a large but ordinary readable corpus, both the released v0.6.22 binary and a fresh optimized build of current main fail after ~33 seconds with:

index failed: rebuilding daily_stats with bounded fallback before index planning for .../agent_search.db | out of memory

This is not host memory exhaustion. The current-main process peaks at about 10.4 GB RSS while the host still has ~702 GiB available. There is no cgroup memory limit, no process RSS/address-space limit, and no kernel/cgroup OOM event. The error is being generated inside CASS/frankensqlite.

This follow-up is intentionally separate from the original #326 report: the transaction-bounded staging change is present in the tested source, but this corpus still reaches the same user-visible failure before index planning.

Environment

released binary: cass 0.6.22
released SHA-256: f6b9ab835affd7fb579ae3c7ecd7b47b13695960c8c24776fc005c514b868782

main commit:       968a5f3c914e975327c998368ab2c5510aa22654
main commit title: fix(indexer): deferred-checkpoint mode uses the bounded bulk-import WAL cadence instead of disabling autocheckpoint
main binary SHA-256: 08309118bd8cd7755b14d1eed6819acef6d9c42d753f0e866d1b4e5851ca1b1c
build:             cargo build --release --locked

OS:                Linux x86_64, kernel 6.17.0-40-generic
libc:              Ubuntu GLIBC 2.42-0ubuntu3.1
CPU:               AMD EPYC 9684X, 64 logical CPUs / 64 physical cores
RAM:               885 GiB total, ~702 GiB available during diagnosis
swap:              none
DB size:           5,633,396,736 bytes
canonical rows:    5,920 conversations / 967,195 messages

The stale materialization that triggers repair is:

daily_stats rows for agent_slug='all', source_id='all': 18
materialized session_count sum:                          640
materialized message_count sum:                      126,803
canonical conversation count:                          5,920
canonical message count:                             967,195

No session content or source paths are needed to reproduce the failure mode.

Reproduction

Build and install current main:

git clone https://github.com/Dicklesworthstone/coding_agent_session_search.git
cd coding_agent_session_search
git checkout 968a5f3c914e975327c998368ab2c5510aa22654
cargo build --release --locked
install -m 0755 target/release/cass ~/.local/bin/cass

Run the full index with a wall-clock guard:

timeout 1800 cass index --full --json --progress-interval-ms 10000

Observed final robot payload on current main:

{
  "success": false,
  "error": "index failed: rebuilding daily_stats with bounded fallback before index planning for /home/ubuntu/.local/share/coding-agent-search/agent_search.db | out of memory",
  "elapsed_ms": 33392,
  "code": 9,
  "kind": "index",
  "retryable": true
}

The released v0.6.22 binary produces the same error in 33,331 ms. Current main reproduced repeatedly in 32.7-33.6 seconds.

Resource evidence: not a machine OOM

A 500 ms /proc/<pid>/status sample during the current-main run showed:

sample  RSS KB     VmHWM KB   CPU
1       5,058,576  5,060,176  99.7%
5       6,358,880  6,360,300  99.7%
9       7,858,580  7,860,108  99.9%
13      9,297,004  9,297,996  99.9%
17      9,969,972  9,970,920  99.8%
21     10,261,548 10,372,108  99.8%
peak   10,373,996             ~100%

At the same time:

MemTotal:       928,370,384 kB
MemAvailable:   736,686,320 kB
CommitLimit:    464,185,192 kB
Committed_AS:   220,188,180 kB

Limits/events:

cgroup v2 memory.max:       max
cgroup v2 memory.high:      max
cgroup memory.events oom:   0
cgroup memory.events kill:  0
RLIMIT_RSS:                 unlimited
RLIMIT_AS:                  unlimited
kernel OOM log entries:     none
vm.overcommit_memory:       0

Therefore the machine has hundreds of GiB of real and commit headroom when CASS reports out of memory.

Relevant code path

Current main reaches:

src/indexer/mod.rs
  repair_daily_stats_if_drifted
    rebuild_daily_stats_from_conversation_packets(...)
      -> internal out-of-memory
    FrankenStorage::rebuild_daily_stats(...)
      -> internal out-of-memory propagated as the final error

src/storage/sqlite.rs
  FrankenStorage::rebuild_daily_stats
    daily_stats_rebuild_stage temp table
    bounded conversation batches
    per-conversation bounded message batches
    transaction-per-aggregate-batch

The #326 staging/commit changes are visibly present. Yet the fallback still grows to ~10 GiB and fails. The robot error does not identify whether the final failure occurred in:

• the conversation scan,
• the per-conversation message scan,
• the staging-table upsert,
• a transaction commit,
• or the final publish.

It also omits the last successful conversation ID, message index, effective batch sizes, and underlying engine error chain. Those fields would make the next diagnosis much cheaper.

Expected behavior

For this corpus, cass index --full should:

1. Rebuild daily_stats with memory bounded independently of total messages/content.
2. Proceed into scan/index planning.
3. Finish with success: true.
4. Leave SUM(session_count) for the all/all rollup equal to canonical conversation count.
5. Avoid multi-GB materialization for a 5,920-conversation corpus.

If an engine allocation still fails, the robot error should include a stable phase and cursor/batch telemetry rather than only out of memory.

Suggested implementation checks

These are investigation pointers, not assumptions about the final fix:

1. Add phase-tagged context around every query/upsert/commit in FrankenStorage::rebuild_daily_stats, including:
   • conversation_scan
   • message_scan
   • stage_upsert
   • stage_commit
   • publish
2. Include last_conversation_id, conversation_id, cursor_message_idx, and current batch sizes in the propagated error.
3. Verify whether frankensqlite's query materialization allocates proportional to the full table even with LIMIT, especially for:

   SELECT m.idx, COALESCE(LENGTH(CAST(m.content AS BLOB)), 0)
   FROM messages m
   WHERE m.conversation_id = ?1 AND m.idx > ?2
   ORDER BY m.conversation_id, m.idx
   LIMIT ?3

4. Consider using an index-qualified streaming access path or native SQL aggregation by day/agent/source if the current executor cannot keep the query bounded.
5. Ensure the initial packet rebuild transaction does not retain all batch writes until the corpus completes; it also reaches the internal OOM before the fallback begins.

Regression test / acceptance criteria

A useful non-private fixture can generate enough messages with large content blobs to force multiple packet and fallback batches while keeping the number of conversations modest.

The test should assert:

• process exits 0;
• daily_stats totals match canonical counts;
• both packet and fallback paths are exercised independently;
• progress/cursor telemetry identifies the active phase;
• peak live aggregation state is capped by batch size rather than corpus size;
• rerunning is idempotent and does not rebuild when the archive fingerprint is unchanged.

Relationship to existing issues

• v0.6.22: daily_stats bounded fallback uses unbounded memory on large corpora (24-36 GB RSS on 603k messages) #326 reported the v0.6.22 unbounded daily_stats fallback and was closed as fixed in 8110944f.
• This report reproduces the same externally visible blocker on a fresh build of 968a5f3c, which contains 8110944f.

I can retest a targeted patch against this 5.6 GB / 967k-message corpus if useful.

[etafund]

Follow-up diagnosis from three independent read-only source/corpus investigations (current main; the live indexer was not interrupted and the DB was not mutated during this analysis):

Stronger root-cause chain

The reported out of memory is almost certainly FrankenSQLite's logical PageBufPool exhaustion, not host allocator failure.

CASS pins fsqlite 0.1.13. Its default file-backed page-buffer ceiling is 262,144 buffers. PageBufPool::acquire returns typed FrankenError::OutOfMemory when total_buffers >= max_buffers, even if the host has abundant RAM. The observed ~10.4 GiB RSS is not a hard threshold: the later successful indexing process exceeded it (~11-12 GiB RSS). Pool/cache/write-set copies plus aligned page allocation and allocator arenas amplify RSS beyond the nominal pool size.

Two concrete bugs combine:

1. CASS fallback query shape defeats the FrankenSQLite composite index

Current CASS raw fallback SQL (src/storage/sqlite.rs, FrankenStorage::rebuild_daily_stats) is:

SELECT m.idx, COALESCE(LENGTH(CAST(m.content AS BLOB)), 0)
FROM messages m
WHERE m.conversation_id = ?1
  AND m.idx > ?2
ORDER BY m.conversation_id, m.idx
LIMIT ?3

FrankenSQLite 0.1.13 derives an equality prefix for conversation_id = ?1 but retains both ORDER BY terms while matching UNIQUE(conversation_id, idx). It rejects the composite index and uses a two-pass full-table sorter. Parameterized LIMIT ?3 does not activate its literal-only Top-N path.

Canonical SQLite correctly treats the leading order term as constant and uses sqlite_autoindex_messages_1. CASS already uses the safe form elsewhere:

FROM messages INDEXED BY sqlite_autoindex_messages_1
WHERE conversation_id = ? AND idx > ?
ORDER BY idx
LIMIT ?

FrankenSQLite issue filed with source-level analysis and tests: Dicklesworthstone/frankensqlite#130

Immediate CASS mitigation: use the explicit autoindex and ORDER BY idx for both raw and metrics daily-stats message scans, and pin the plan with an EXPLAIN QUERY PLAN regression asserting no temp sorter.

2. Packet rebuild and fallback write admission can exhaust the shared pool

The packet path opens one write transaction before scanning the archive and commits only after the entire corpus. It materializes 256 conversations per fetch with no max-message/max-content guard, reads canonical messages, then upserts aggregate rows into the same long-lived transaction.

The fallback is transaction-bounded per aggregate batch now, but it reuses the same hot connection after packet OOM. Its message scans can shrink batches, yet stage upsert/commit has no OOM retry/context.

In fsqlite, read allocation catches pool OOM, evicts a cache page, and retries. SimpleTransaction::write_page stages bytes from the same pool directly and propagates capacity exhaustion without asking the clean read cache to evict or reserving writer capacity. Thus a bad full scan can fill the pool; the next one-row daily_stats stage upsert needs a write buffer and returns OutOfMemory despite hundreds of GiB free.

FrankenSQLite issue filed: Dicklesworthstone/frankensqlite#131

Corpus measurements

conversations:                    ~5,930
messages:                         ~968,280
DB size:                          5,633,396,736 bytes
messages table bytes:             3,729,498,112
message content bytes:            1,472,755,014
largest message:                  616,055 bytes
messages >= 1 MiB:                0
largest conversations:            11k-17k messages
worst 256-conversation packet:    178,354 messages / 331,729,325 content bytes

No giant blob explains the failure. The adverse shape is many ordinary messages clustered in relatively few conversations.

Native SQLite executes the relevant composite-index seeks—including an 11,588-row conversation fetch—in ~0.1s including CLI startup.

message_metrics has only 126,803 rows vs ~968k messages, so this corpus deterministically selects the raw-message fallback rather than the metrics JOIN.

CASS instrumentation requested

The current final error erases the failing operation. Please attach stable context at each fallback phase:

• count_messages
• create_stage
• conversation_scan
• conversation_stage_upsert
• conversation_stage_commit
• message_scan
• message_stage_upsert
• message_stage_commit
• stage_count
• publish_delete
• publish_insert
• publish_commit

Include last conversation ID, current conversation ID, message cursor, effective batch sizes, and fsqlite Connection::memory_stats() at phase/error boundaries. Pool diagnostics should ideally include total/max/free/in-use/pinned/dirty buffers and write-set pages.

Regression-test gap

The current packet-OOM fallback test injects OOM before lookups/transaction/cache activity. It proves error classification, not recovery from a pool saturated mid-rebuild.

A useful child-process integration test should:

1. set a low FSQLITE_PAGE_BUFFER_MAX before connection initialization;
2. create enough ordinary messages to cross pool pressure mid-packet;
3. force actual engine OOM after N batches (not the pre-function synthetic hook);
4. assert the fallback succeeds and exact daily totals match;
5. assert the raw SQL plan uses the composite autoindex and no temp B-tree;
6. verify bounded memory/cached pages and idempotent rerun.

Workaround validation

Rebuilding the same derived daily_stats rollup using native SQLite aggregation took 1.7 seconds and produced exact totals (5,920 sessions / 967,195 messages at that snapshot). After that atomic derived-rollup repair, the exact same cass index --full immediately passed the fatal preparing phase and entered normal multi-connector indexing. Canonical conversations/messages were never rewritten by the workaround.

This makes the daily-stats CASS/frankensqlite path—not corpus corruption or host capacity—the discriminating blocker.

[etafund]

Follow-up outcome: the same process reached ~24.4 GB RSS, definitively ruling out a ~10.4 GiB hard cap

After rebuilding only the stale derived daily_stats rollup through native SQLite, the exact same current-main CASS build passed the original failing phase and completed:

• all seven connector scans;
• canonical ingest to 9,596 conversations / 1,209,091 messages;
• the full staged authoritative lexical rebuild;
• lexical publish and ready/fresh checkpoint state.

Near the end, CASS reported:

{
  "phase": "indexing",
  "total": 9596,
  "current": 9596,
  "host_available_memory_bytes": 737048977408,
  "process_rss_bytes": 24375410688,
  "controller_mode": "steady",
  "controller_reason": "steady_budget_with_headroom"
}

This proves the earlier repeatable ~10.4 GB point was not a cgroup, RLIMIT, allocator, or VM hard ceiling. The process later reached roughly 24.4 GB RSS while hundreds of GiB remained available.

The run subsequently encountered a different logical OOM while recreating the non-authoritative contentless fts_messages fallback after lexical publish. That recurrence is documented on CASS #327 and FrankenSQLite #131:

• v0.6.22: post-full-rebuild FTS repair hard-fails (exit 9) when shadow tables are missing #327 (comment)
• Shared PageBufPool: clean read-cache saturation makes next transactional write return false OutOfMemory without eviction/retry frankensqlite#131 (comment)

The discriminating result for this issue remains strong:

• stale daily_stats + CASS/fsqlite rebuild path → repeatable logical OOM around 10.4 GB;
• same archive after native derived-rollup rebuild → passes that phase immediately;
• same process later exceeds 10.4 GB by more than 2×.

So the title's RSS value is an observed consequence of the bad planner/shared-pool state at that point, not an actual memory cap.

[etafund]

Full-run outcome: no 10.4 GiB host cap; staged lexical publish succeeds, final FrankenSQLite FTS repair fails

The long cass index --full --json run completed its source scan and published a fresh staged Tantivy index, but then failed in the final derived FTS repair. This provides a cleaner separation between the working canonical/lexical stages and the failing FrankenSQLite stage.

Corpus/result at the failure boundary

conversations:  9,596
messages:       1,209,091
message ID max: 2,243,647
lexical fingerprint: content-v1:9596:23302:2243647

The staged lexical rebuild processed all 9,596 conversations and published successfully. Search against the published Tantivy index succeeds and reports the index ready/fresh.

The command then failed with exit code 9 at:

repairing frankensqlite-owned fallback FTS ...
creating derived fts_messages via frankensqlite rebuild
out of memory

This happens while creating/rebuilding the contentless fts_messages shadow, not while parsing source logs or building Tantivy.

Peak RSS disproves the apparent 10.4 GiB cap

Immediately before the final failure, the process had grown well beyond the earlier plateau:

cass RSS:              24,375,410,688 bytes (about 22.7 GiB)
host available memory: 737,048,977,408 bytes (about 686 GiB)

There was still no cgroup memory limit, RLIMIT RSS/address-space limit, kernel OOM event, or commit exhaustion. Therefore ~10.4 GiB is not a VM/process hard cap. It is a common internal plateau produced by particular FrankenSQLite materialization/page-cache paths; a different stage can allocate more than twice that amount.

Stock SQLite control

After recovering the canonical file described below, stock SQLite created and populated the same contentless FTS5 shape for all 1,209,091 messages and rebuilt daily stats in approximately 44 seconds. Validation included:

FTS row count:                    1,209,091
canonical messages:              1,209,091
real MATCH query (`cass`) hits:  9,140
FTS shadow table count:          5
stock SQLite quick_check:        ok

However, FrankenSQLite cannot subsequently reopen the native-populated FTS shadow because its shadow reload path does not support the resulting WITHOUT ROWID structures. The operationally safe state is therefore an absent in-DB FTS shadow plus matching generation/fingerprint markers, with Tantivy remaining authoritative (a state CASS source already describes as benign).

Canonical corruption discovered after the failed run

Stock SQLite quick_check on the post-run database and on a completed .backup snapshot reported:

Page 41: never used
wrong # of entries in index sqlite_autoindex_conversations_1

The conversations table remained scan-readable and contained 9,596 unique IDs and 9,596 unique external IDs, but forcing the implicit UNIQUE index returned SQLITE_CORRUPT. This has been filed separately as Dicklesworthstone/frankensqlite#132; causation is not asserted, but the committed-file residual appeared after the large FrankenSQLite workload and persisted in the snapshot.

Recovery used stock SQLite .recover --ignore-freelist into an isolated file. Apart from a nonfatal sqlite_sequence reserved-name warning, the recovered database preserved exact canonical invariants:

conversations:          9,596
unique external IDs:   9,596
conversation ID range: 1..23,302
messages:               1,209,091
message ID range:       1..2,243,647
sum(length(content)):   1,613,255,843
lost_and_found rows:    0
stock quick_check:      ok

The recovered file was backed up, checksummed, validated, and only then copied over the live database. Tantivy search and CASS status subsequently returned healthy/ready/fresh.

Progress/telemetry defects observed during the same full run

The source total expanded lazily to 7,439 and changed non-monotonically at connector boundaries. Several batches exceeded the 120-second stall threshold and emitted stall_detected even though the process remained around one full CPU core and later advanced normally. For agent supervisors this creates two false signals:

1. a denominator that changes after work begins makes ETA/percent unreliable;
2. elapsed-wall-clock-only stall detection labels CPU-bound forward progress as a stall.

The progress schema should distinguish enumerating, parsing, committing, lexical_publish, and derived_fts_repair, report whether totals are provisional, and include a monotonically increasing work counter/heartbeat.

Suggested acceptance test

For a million-message, multi-gigabyte file-backed corpus:

1. run the complete index --full pipeline, including final derived FTS handling;
2. assert canonical counts/checksums before and after every derived stage;
3. validate the closed artifact with stock SQLite quick_check;
4. require the Tantivy publish to remain usable if optional in-DB FTS repair fails;
5. bound PageBufPool memory and report its configured/actual high-water marks;
6. emit stage-specific progress and a provisional/final total flag;
7. inject a slow CPU-bound batch and assert it is not called stalled while work counters advance;
8. make a repeated full run with an identical content fingerprint a fast, successful no-op.

Related reduced engine reports:

• FrankenSQLite "brew install dicklesworthstone/tap/cass" reports checksum mismatch #130: bad ORDER BY/index-plan choice drives sorter materialization
• FrankenSQLite cass search misses exact bead IDs present in recent Codex sessions after successful manual reindex #131: shared PageBufPool read saturation makes a later write fail OutOfMemory
• FrankenSQLite feat: implement MarkdownExporter for session results #132: committed orphan page + corrupt UNIQUE autoindex field residual
• FrankenSQLite Log Line Spams the Console 100s of times a second on cass index #133: false integrity_check corruption at the exact 1 GiB page boundary

[etafund]

Final large-corpus reproduction + tested CASS-side mitigation

Reproduced again from upstream CASS main at 968a5f3c914e975327c998368ab2c5510aa22654 on 2026-07-12.

Exact command and terminal result

$ cass index --full --json --progress-interval-ms 30000
# source scan reached 7,439 / 7,439
# final result after 20,478,608 ms (5h 41m 18.608s)
ERROR ... repairing frankensqlite-owned fallback FTS after full index run ...
        creating derived fts_messages via frankensqlite rebuild | out of memory
{"success":false,"code":9,"kind":"index","retryable":true,...}

At the final preparing event, CASS itself reported:

{
  "host_available_memory_bytes": 714828066816,
  "process_rss_bytes": 29679951872,
  "controller_mode": "steady",
  "controller_reason": "steady_budget_with_headroom"
}

That is about 665.8 GiB host memory still available and 27.6 GiB process RSS. This is not a host/cgroup/ulimit OOM and is not a 10.4 GiB hard cap. The source scan and authoritative Tantivy publication had already completed; only the optional DB-resident fallback FTS repair failed.

Post-failure safety evidence

Immediately after the exit-9 failure:

$ sqlite3 agent_search.db 'PRAGMA quick_check; PRAGMA integrity_check(20);'
ok
ok

The canonical database remained readable and intact. After the final targeted catch-up and acceptance pass:

canonical SQLite: 9,750 conversations / 1,223,462 messages
fts_messages: absent
Tantivy: 1,154,324 documents
cass doctor --json: exit 0, status=healthy, 18 checks, 0 failures
cass health --json: exit 0, healthy=true, index ready/fresh
cass status --json: exit 0, healthy=true, checkpoint completed + db_matches=true
cass search cass --robot --limit 1 --fields summary --robot-meta: exit 0, 1 hybrid hit
native quick_check + integrity_check(20): both ok

Doctor explicitly treats absent DB FTS as healthy because lexical search is served by Tantivy.

Minimal local mitigation tested

I added a narrowly scoped nonfatal outcome to src/indexer/mod.rs:

• classify out of memory as nonfatal only when the error text also identifies fts_messages, fallback FTS, or derived FTS;
• preserve the successful canonical SQLite + Tantivy build and emit a warning;
• keep generic/canonical OOMs fatal.

Focused test result:

running 6 tests
... contextual derived OOM is nonfatal ... ok
... canonical resource failure remains fatal ... ok
... existing fallback-FTS repair tests ... ok
test result: ok. 6 passed; 0 failed

The combined release binary also passed doctor/status/health/search/native-integrity acceptance above. I did not spend another 5h41m rerunning the entire full crawl after this one-line policy branch; the exact failing error string is covered by the regression test.

Suggested upstream acceptance criteria

1. A completed canonical + Tantivy full build must not exit nonzero solely because optional fallback FTS materialization returns a contextual derived-index OOM.
2. Canonical database read/write/I/O/resource failures must remain fatal.
3. Robot output should identify the degraded optional asset and the preserved authoritative assets.
4. Add a fixture/injection test for creating derived fts_messages ... out of memory after successful Tantivy publication.
5. Ideally expose an explicit --skip-fallback-fts / policy knob for installations where Tantivy is authoritative and the fallback is known unsupported at corpus scale.