Optimize ingest at depth: deadlock hardening, backlog drain, session_events retention, storage reclaim

Progress: built flower:ingest-backlog report-only command with bounded --rekick for error, summarized zero-chunk, and stale wedged summarized/embedded sessions only. Registered the command, added failed_jobs summary reporting, and covered report/rekick/no-op acceptance tests. Verification: focused IngestBacklogCommandTest passed; full php artisan test passed (489 tests, 488 passed, 1 skipped).

Refined → PLANNED. The acute workstreams already shipped (deadlock b922b47→d375135, query-efficiency 2bc0234, Meili purge 35d16c6, #19 adaptive chunking); session_events pruning explicitly killed by operator (event 89). v1 scoped to the one durable low-risk gap: a `flower:ingest-backlog` command (report pipeline state + `--rekick` for only the stuck/errored/zero-chunk tail). Re-prioritization/#19-tail/concurrency-docs noted v2+. No blocking questions.

## Goal Keep the ingest pipeline healthy under operator-driven `depth` bumps (which recur). The acute issues from the 2026-07-01 surge are FIXED — this brief's v1 adds the one durable, low-risk gap that remains: **backlog visibility + a safe re-kick for the stuck tail.** ## Already shipped (do NOT rebuild) - **Deadlock hardening (ws1) — DONE:** `IngestSession::persistEvents` retry/backoff (1213 / 1205 / 40001) + ordered batched inserts (b922b47, merged d375135). - **Query efficiency (ws2, reframed) — DONE:** bounded page queries + indexes (2bc0234). *(Operator directive event 89: do NOT prune `session_events` / delete rows — slowness is a query problem, not a storage one. Size stays an observation, not a mandate.)* - **Meili test-index purge (ws5) — DONE:** `flower_test_*` teardown + scoped purge (35d16c6). - **#19 streaming stall — largely fixed** via adaptive chunking (its own brief); any rare tail lives there, not here. ## Scope — v1 (single dispatch): backlog visibility + safe re-kick A read-mostly `flower:ingest-backlog` artisan command: 1. **Report** pipeline state — session counts by `ingest_state` (pending / parsed / summarized / indexed / error), the **stuck tail** (error + `summarized`-with-0-chunks + wedged), and the live-vs-backlog split. Progress-friendly output. 2. **`--rekick`** (opt-in, bounded): re-dispatch ONLY the stuck / errored / zero-chunk sessions back through summarize→embed→index (idempotent — content_hash guards re-chunk). Does **not** mass re-queue the healthy `parsed` backlog (Horizon drains that) — so it can't re-trigger load or starve live ingest. 3. Optionally surface the same counts on `/storage` (read-only panel). ## Out of v1 (noted v2+) - Small-first **re-prioritization** of the whole parsed backlog (invasive; risks starving live ingest — the thing to avoid). - The `#19` streaming-stall tail (its own brief). - Concurrency-knob tuning docs (`HORIZON_FAST_LOCAL_MAX_PROCESSES` vs deadlock risk). - `session_events` retention — **explicitly out** (operator: no pruning). ## Acceptance - `flower:ingest-backlog` reports ingest_state counts + the stuck tail; `--rekick` re-dispatches only stuck / errored / zero-chunk sessions (idempotent, bounded), leaving the healthy backlog to Horizon. - Tests (report shape; rekick targets only stuck sessions; no-op when clean). `php artisan test` green + pint. `Brief: #14` trailer. ## Provenance Orchestrator brief #14 (2026-07-01 depth surge). Acute workstreams (deadlock, query-efficiency, Meili purge, #19) shipped; operator killed session_events pruning (event 89). Scoped to the durable backlog-visibility/re-kick gap by flower-design (2026-07-01). No blocking questions.

## Spec: Optimize ingest at depth **Trigger:** operator increased ingest `depth` across many projects (2026-07-01) → ~361 sessions/30min created — a large but FINITE, operator-driven backlog. It surfaced concrete issues; this brief captures the durable optimization workstreams (depth bumps will recur). ### Findings (DB profiled 2026-07-01, TCP `mysql -u root -h 127.0.0.1 -P 3306 flower`) - **`session_events` = 315 MB of a ~380 MB DB (61K rows)** — the raw transcript store (`longtext text` + json). It dominates storage and grows with depth. Indexes are already sensible (`(session_id,ordinal)`, `(session_id,role)`) — this is NOT an index problem. - **Ingest backlog:** of 653 sessions — 367 `parsed`, 278 `indexed`, 4 `error`, 4 `summarized`-wedged. The 367 will drain but slowly (rate-limited chunked summarize; large ones risk the #19 stall). - **FLOWER-6 deadlock storm [IN PROGRESS]:** concurrent `IngestSession::persistEvents` session_events inserts deadlock under the surge (~51 failed jobs/30min; fast supervisor `tries=1`, local `maxProcesses=3`). Dispatched to backend worktree (branch `flower/fix-ingest-deadlock`): deadlock retry+backoff (40001/1213/1205) + deterministic ordered+chunked inserts. - **#19 streaming-stall tail:** sessions 406/737/891 still hit the 300s single-call ceiling (provider streams ~7.8KB then stalls). They fail gracefully; unrecoverable with the current approach. - **~4 GB Meili bloat:** 40+ stray `flower_test_*` indexes (todo #350) — the biggest single storage reclaim. ### Workstreams 1. **[in progress] FLOWER-6 deadlock hardening** — persistEvents retry + ordered/chunked inserts (backend lane). 2. **`session_events` retention / prune** — once a session is fully `indexed` (summarized→chunked→embedded), its raw events are mostly dead weight. Config-gated prune or cold-archive (e.g. keep raw events N days post-index, or drop after successful embed with a re-ingest path if ever needed). Biggest MySQL reclaim. 3. **Backlog drain strategy** — prioritize small/cheap sessions first; a drain command + progress visibility; ensure the finite backlog clears without starving live ingest. 4. **#19 streaming-stall fix (406/737/891)** — streaming-idle-timeout + retry-on-stall, or a smaller per-call token cap, so huge sessions finish. Separate spike. 5. **Meili test-index purge** — todo #350: test teardown of `flower_test_*` + one-time scoped purge (do NOT touch real indexes). 6. **Ingest concurrency knobs** — document/tune `HORIZON_FAST_LOCAL_MAX_PROCESSES` vs deadlock risk (safe to raise again post-FLOWER-6). ### Notes - Ingest is FINITE / operator-driven — the storm self-limits as the backlog drains. Prioritize the durable hardening (1, 2, 5) over one-off firefighting.

Query-efficiency audit + quick wins on flower/query-efficiency-audit. | Page | Queries | Issue | Fix | | --- | ---: | --- | --- | | /briefs | 3 | Clean: bounded list and eager project/counts. | No code change. | | /briefs/{brief} | 15 | Trace, link, and dispatch collections were unbounded. | Limit trace events to 100, links to 100, dispatch requests to 25. | | /sessions | 13 | Full segment collections loaded per page; Codex TTL sampled all Codex sessions; lifecycle stats repeated settings reads. | Eager-load latestSegment only, narrow usage columns, bound Codex TTL sample to 200 sessions, reuse one lifecycle cutoff. | | /sessions/{session}/segments | 2 | Clean: session-scoped segments and indexed event count. | No code change. | | /roster | 3 | Clean: eager project load, small daemon table. | No code change. | | /feedback | 2 | Clean: list capped at 200 plus grouped counts. | No code change. | | /storage | 6 | Sparkline series scanned unbounded history. | Limit series to latest 60 snapshots and add source/name/captured index. | | dispatch queue | 5 | Clean: capped at 100 and eager-loaded. | No code change. | Added a bounded query-count regression test for these pages, plus a session_usage(session_id, ts, id) index for latest usage/context ordering. Verification: - ANTHROPIC_API_KEY= MEILISEARCH_KEY=LARAVEL-HERD ~/bin/php artisan test -> PASS, 350 tests, 340 passed, 10 skipped, 2527 assertions. - Focused page/regression suite -> PASS, 31 tests, 179 assertions. - Pint on touched files -> PASS.

CORRECTION (Mike, 2026-07-01): do NOT prune session_events or delete session rows. Reframe workstream 2 from storage-reclaim to QUERY EFFICIENCY: if page loads are slow it is a query problem (add eager-loading, pagination, bounded selects, better indexes where warranted) — never a reason to delete rows. session_events size (315MB) is an observation, not a mandate to prune. Meili test-index cleanup (ws5) delegated to backend 977. Auto-link trailer+backfill (brief 15) confirmed good by Mike.

## Spec: Optimize ingest at depth **Trigger:** operator increased ingest `depth` across many projects (2026-07-01) → ~361 sessions/30min created — a large but FINITE, operator-driven backlog. It surfaced concrete issues; this brief captures the durable optimization workstreams (depth bumps will recur). ### Findings (DB profiled 2026-07-01, TCP `mysql -u root -h 127.0.0.1 -P 3306 flower`) - **`session_events` = 315 MB of a ~380 MB DB (61K rows)** — the raw transcript store (`longtext text` + json). It dominates storage and grows with depth. Indexes are already sensible (`(session_id,ordinal)`, `(session_id,role)`) — this is NOT an index problem. - **Ingest backlog:** of 653 sessions — 367 `parsed`, 278 `indexed`, 4 `error`, 4 `summarized`-wedged. The 367 will drain but slowly (rate-limited chunked summarize; large ones risk the #19 stall). - **FLOWER-6 deadlock storm [IN PROGRESS]:** concurrent `IngestSession::persistEvents` session_events inserts deadlock under the surge (~51 failed jobs/30min; fast supervisor `tries=1`, local `maxProcesses=3`). Dispatched to backend worktree (branch `flower/fix-ingest-deadlock`): deadlock retry+backoff (40001/1213/1205) + deterministic ordered+chunked inserts. - **#19 streaming-stall tail:** sessions 406/737/891 still hit the 300s single-call ceiling (provider streams ~7.8KB then stalls). They fail gracefully; unrecoverable with the current approach. - **~4 GB Meili bloat:** 40+ stray `flower_test_*` indexes (todo #350) — the biggest single storage reclaim. ### Workstreams 1. **[in progress] FLOWER-6 deadlock hardening** — persistEvents retry + ordered/chunked inserts (backend lane). 2. **`session_events` retention / prune** — once a session is fully `indexed` (summarized→chunked→embedded), its raw events are mostly dead weight. Config-gated prune or cold-archive (e.g. keep raw events N days post-index, or drop after successful embed with a re-ingest path if ever needed). Biggest MySQL reclaim. 3. **Backlog drain strategy** — prioritize small/cheap sessions first; a drain command + progress visibility; ensure the finite backlog clears without starving live ingest. 4. **#19 streaming-stall fix (406/737/891)** — streaming-idle-timeout + retry-on-stall, or a smaller per-call token cap, so huge sessions finish. Separate spike. 5. **Meili test-index purge** — todo #350: test teardown of `flower_test_*` + one-time scoped purge (do NOT touch real indexes). 6. **Ingest concurrency knobs** — document/tune `HORIZON_FAST_LOCAL_MAX_PROCESSES` vs deadlock risk (safe to raise again post-FLOWER-6). ### Notes - Ingest is FINITE / operator-driven — the storm self-limits as the backlog drains. Prioritize the durable hardening (1, 2, 5) over one-off firefighting.