Measure and reduce per-message write amplification into captain_sessions

[moshloop]

Problem

captain_touch_session_activity is an AFTER INSERT OR UPDATE ... FOR EACH ROW trigger installed on eight tables including captain_messages (migrations/52_session_activity_triggers.sql:93-131). Its first statement is:

row_data jsonb := to_jsonb(NEW);

to_jsonb(NEW) serializes the entire tuple, including the parts JSONB blob, purely so the function can read session_id and occurred_at as text (:46-69). It then issues an UPDATE public.captain_sessions.

Ingesting a 10,000-message transcript executes 10,000 trigger functions and 10,000 full-row JSONB conversions. convergeMessages re-fires the trigger for every row its UPDATE ... IS DISTINCT FROM actually touches (pkg/database/session_message_ingest.go:75-99).

Physical session updates are fewer than trigger fires — the update is guarded on a newer timestamp (:73, :86) — so the cost is dominated by function invocation and tuple serialization rather than by row updates.

This issue is measurement-first

migrations/71_session_storage_params.sql:11 records the symptom:

7,791 live rows spread over 40,458 heap pages (316 MB) with just 8.8% of pages holding any live tuple — roughly 40x the ~10 MB the live set warrants. It was also the single largest source of physical reads in the database, at 1.47M of 1.76M total block reads and a 25% heap cache hit ratio while every other table sat above 92%.

But that migration attributes the churn broadly to heartbeats, state rewrites and triggers. Nothing in the tree isolates the to_jsonb cost, and the applied remedy — fillfactor = 70 plus autovacuum tuning — treats the bloat rather than whatever produces it.

Measure before changing anything. Capture per-ingest trigger cost and pg_stat_user_tables.n_tup_upd on captain_sessions for a representative transcript. SessionStorageStats (pkg/database/session_maintenance_store.go:110-145) already reports the bloat telemetry to compare against.

If measurement justifies a change

The rewrite must be per-table statement triggers using transition tables, not one generic drop-in:

• Statement-level triggers have no scalar NEW; they must consume a REFERENCING NEW TABLE AS transition relation.
• INSERT and UPDATE need separate trigger arrangements.
• The current function considers occurred_at, state_observed_at, started_at and resolved_at (:64-69). Aggregating MAX(occurred_at) alone silently changes behaviour for turns, model calls, prompt runs and turn requests.
• The CASE TG_TABLE_NAME indirection (:46-59) exists because captain_prompt_run_iterations and captain_model_calls reach their session indirectly; that lookup must be preserved.
• Go writes messages in batches of 500 (pkg/database/session_ingest_store.go:228), so 10,000 messages is roughly 20 statement invocations, not one.

Reading NEW.session_id and NEW.occurred_at directly instead of through to_jsonb(NEW) is a smaller, independently valuable change that removes the per-row serialization without restructuring the trigger.

Cheaper first move, worth evaluating in the same breath

migrations/72_ingest_storage_params.sql:23-31 records that captain_turns and captain_model_calls are rewritten in full on every transcript append, because turns carry running aggregates over the whole file. The turn and model-call upserts are unconditional (pkg/database/session_ingest_store.go:408, pkg/database/session_message_ingest.go:14). Adding IS DISTINCT FROM guards — the pattern convergeMessages already uses — suppresses no-op churn with no trigger surgery at all.

Also in scope

migrations/72_ingest_storage_params.sql:7 justifies its settings with "captain_messages is written once and never updated." That is false — convergeMessages updates role, parts, raw, turn_id, source_line and occurred_at. The tuning itself is defensible (it sets insert and analyze thresholds and cost delay, not a fillfactor chosen for immutability), so correct the comment without disturbing the parameters.

Acceptance Criteria

• A recorded measurement isolates per-ingest trigger cost and captain_sessions update volume before any behavioural change.
• If the trigger is changed, pg_stat_user_tables.n_tup_upd on captain_sessions per ingested transcript drops materially against that baseline.
• Activity semantics are unchanged for all eight source tables, asserted per table rather than only for messages.
• The indirect session lookup for prompt-run iterations and model calls is preserved.
• The false comment at migrations/72_ingest_storage_params.sql:7 is corrected without changing the storage parameters.
• No-op turn and model-call upserts are suppressed, or the decision not to is recorded with its measurement.

Verification

---

cwd: .
timeout: 20m
codeBlocks: [test, lint]
ai: {}
verify:
scope: diff
threshold: 80

Focused tests

paths: [./pkg/database/..., ./pkg/monitor/..., ./migrations/...]
framework: [go, ginkgo]
test-timeout: 12m
show-passed: true

Changed-code lint

changed: true
fix: false
timeout: 5m

Acceptance Criteria

• A recorded measurement isolates per-ingest trigger cost and captain_sessions update volume before any behavioural change.
• If the trigger is changed, pg_stat_user_tables.n_tup_upd on captain_sessions per ingested transcript drops materially against that baseline.
• Activity semantics are unchanged for all eight source tables, asserted per table rather than only for messages.
• occurred_at, state_observed_at, started_at and resolved_at are all still honoured as activity sources.
• The indirect session lookup for captain_prompt_run_iterations and captain_model_calls is preserved.
• The false comment at migrations/72_ingest_storage_params.sql:7 is corrected without changing the storage parameters.
• No-op turn and model-call upserts are suppressed, or the decision not to is recorded together with its measurement.

[coderabbitai]

🔗 Related PRs

#38 - Improve transcript ingestion and setup [open]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!