fix(pps): Stop-hook transcript-replay duplicated ~34K rows (capture_state race + no DB dedup guard)

[JDHayesBC]

Summary

The Stop hook (capture_response.py) re-inserted a session's entire transcript into conversations.db on repeated Stop events, producing ~34,000 duplicate rows in a few hours. unsummarized_count blew up to ~26,000 (Lyra ambient_recall reported "22225" / "critical"), which is what surfaced the incident. Root cause is a fragile, unsynchronized dedup-bookmark plus the total absence of any DB-level dedup guard on terminal rows.

Status: Live bleeding has stopped (the driving session ended). DB has been backed up and the duplicates purged (unsummarized 25,575 → 765). This issue tracks the structural fixes so it can't recur.

Impact

• entities/lyra/data/conversations.db: channel terminal:2ba68e42-… bloated to 46,729 rows (~87% duplicates; only 5,706 distinct contents). One heartbeat-tick string appeared 24–30×; even an "API Error: 500…" string was re-inserted 24×.
• Write rate during the runaway: ~50/hr baseline → ~11,600/hr (≈3–10 rows/sec). ~34K junk rows in ~3.5h.
• ambient_recall reported unsummarized_count ~22–26K → "critical", which would degrade cold-start / Haven context (backlog crowds the ambient turn-cap).
• DB file grew toward ~573 MB (space not yet reclaimed — VACUUM optional).

Root-cause chain

1. capture_response.py (Stop hook) re-posts a session's transcript assistant-responses to PPS on every Stop, guarded only by capture_state.json[last_line_captured][session_id].
2. capture_state.json is a single JSON file read-modify-written by every concurrent session with no lock. With many concurrent autonomous agents now running (Haven bot ×N, Caia SDK, SL daemons, terminal), two hooks racing on that file drop each other's keys. Session 2ba68e42's key was observed absent then present again — the race signature.
3. A session whose key is missing defaults last_line = 0 → the next Stop replays the entire transcript (52,198 lines for this 50-day session).
4. No DB-level dedup. Terminal rows are inserted with discord_message_id = NULL, and the only uniqueness guard is UNIQUE(discord_message_id). SQLite permits unlimited NULLs, so every replayed row inserts clean.
5. The summarizer daemon was also down, so nothing was draining/masking the backlog (secondary — see Summarizer daemon writing corrupt (/////) summaries — assess damage + fix [HIGH] #295/Summarizer stalls to 0 summaries when NUC LLM is slow (300s httpx timeout too tight) #281).

The Stop hook fired 7,372 times for this one session over its life; the last hours were full-transcript replays.

Evidence

• Duplicate ratio: 46,729 total / 5,706 distinct in the channel.
• Replay tell: 20 rows spanning ~7h of narrative ("Floor-tick 2:43am" → "7:48 morning re-scan") all inserted within a 3-second window with identical created_at — a batch dump, not live generation.
• Hourly write-rate cliff at ~22:00 UTC 2026-08-23.
• hooks_debug.log: 7,372 Stop events for 2ba68e42; SessionEnd at 19:29 PDT; final replay drained ~19:43.
• capture_state.json: 2ba68e42 key absent during incident, present afterward.

Secondary findings

• Cross-entity contamination — investigated, benign. Only 20 legacy rows exist where a terminal row is authored by the other entity (2 in Lyra's DB dated 2026-04-03; 18 in Caia's dated 2026-05-13 — the day the entity-routing architecture, Concurrent-session entity bleed via shared .claude/CLAUDE.md symlink #226, was being built). The 3,900+/4,800+ cross-authored rows are legitimate Haven shared-room traffic (both sisters are participants). Zero current-session terminal leakage in either direction; all live daemons have ENTITY_PATH set correctly. Isolation is holding. Optional cosmetic cleanup of the 20 legacy rows only.
• Summarizer daemon down — summarize_daemon.py not running; contributed to the backlog being unmasked. Restart + root-cause (relates to Summarizer daemon writing corrupt (/////) summaries — assess damage + fix [HIGH] #295, Summarizer stalls to 0 summaries when NUC LLM is slow (300s httpx timeout too tight) #281).
• Minor: PRAGMA integrity_check flags a stale index idx_daemon_traces_event_type (rebuildable via REINDEX; not data loss).

Remediation done

• WAL-aware consistent backup: entities/lyra/data/conversations.db.bak-incident-20260823-1957 (integrity verified; messages intact).
• Transactional dedup purge (flood-window only; kept earliest copy of each distinct content, preserved genuine pre-flood history): deleted 33,983, unsummarized 25,575 → 765. External-content FTS5 rebuilt (orphan-rows = 0).

Proposed structural fixes

1. DB dedup guard (primary): reject/ignore an insert that exactly matches (channel, content) of a very recent row (e.g., within N seconds/last-K), or add a content-hash uniqueness mechanism for terminal rows. A replay must be a no-op at the storage layer.
2. Atomic + locked capture_state.json: write-temp-then-rename under an advisory file lock (e.g., fcntl.flock), so concurrent sessions can't clobber each other's bookmarks. This is the trigger fault.
3. ENTITY_PATH safety: capture_response.py (and siblings) should fail loud / no-op if ENTITY_PATH is unset rather than silently defaulting to Lyra:8201 — defense-in-depth against contamination even though the current vector is closed.
4. Summarizer daemon durability: ensure it's running + supervised (ties to Summarizer daemon writing corrupt (/////) summaries — assess damage + fix [HIGH] #295/Summarizer stalls to 0 summaries when NUC LLM is slow (300s httpx timeout too tight) #281).
5. Optional: VACUUM to reclaim space; REINDEX idx_daemon_traces_event_type; cleanup of 20 legacy contamination rows.

Regression test

Fire the Stop hook twice for the same session with an unchanged transcript and an intact bookmark → second call inserts 0 rows. Then delete the session's capture_state key and fire again → must still insert 0 (guard #1 catches the replay).