Brainstorm: restore ListAIBridgeSessions performance without sacrificing pagination

Regression source: PR #24440 (AIGOV-208).
Goal: sort AI Bridge sessions by last activity (last prompt time) and keep the current numbered/offset pagination + total count, while making the query scale to large interception/prompt row counts.

Constraints chosen by the user:

• Light denormalization only: a single denormalized column + backfill is acceptable. No new session-summary table. Maintain the column from the application write path, not DB triggers.
• Keep current pagination semantics: numbered pages (offset) + total count, as rendered today by PaginationContainer. Not switching to pure keyset.

1. Root cause (verified in the tree)

ListAIBridgeSessions orders by a runtime aggregate COALESCE(MAX(latest_prompt.latest_prompt_at), MIN(ai.started_at)) via a LEFT JOIN LATERAL over aibridge_user_prompts, per interception. Pre-#24440 it ordered by MIN(ai.started_at) (a stored column).

Why this hurts at scale:

1. Prompt lateral runs over the whole filtered set, not the page. The lateral sits inside the session_page CTE before LIMIT/OFFSET, so Postgres probes aibridge_user_prompts for every matched interception in the entire filter window, then aggregates, sorts, and slices the page. Dominant new cost.
2. The sort key is not indexable. last_active_at is computed per group, so the planner must build and sort every group before honoring OFFSET/LIMIT. The after_session_id keyset path does not help either: its HAVING compares the same aggregate, so all groups are still materialized.

Supporting facts: no index covers a per-session "last activity" value; session_id is a stored generated column so a session is not a first-class row; CountAIBridgeSessions is independent of the sort key; prompts are written from Server.RecordPromptUsage, the natural place to maintain a denormalized timestamp.

2. Reframing the tradeoff

"keyset vs numbered pagination" is a false dichotomy. The real blocker is that the sort key is not materialized. Once it derives from stored columns: offset pagination gets cheaper, total count is already independent, and a future keyset path becomes possible.

3. Recommended direction

Denormalize a per-interception activity timestamp and drop the prompt lateral.

3.1 Schema

Add last_prompt_at timestamptz NULL to aibridge_interceptions = max created_at of that interception's prompts (NULL when none). Backfill in the migration. Follow DATABASE.md (migration pair, make gen, audit table if needed).

3.2 Write-path maintenance (app write, no triggers)

Fold the bump into the prompt insert as a single CTE (GREATEST is NULL-safe and monotonic; retries/out-of-order records never regress the value). Rejected: two store calls wrapped in InTx (extra transaction + round trip for a cache write inseparable from the insert).

3.3 Query rewrite

Session sort key becomes COALESCE(MAX(ai.last_prompt_at), MIN(ai.started_at)). session_page is a single GROUP BY over interceptions on stored columns; cursor_pos and the keyset HAVING use the same expression. Page-only lateral joins and ORDER BY unchanged.

3.4 Optional follow-up (deferred): true early termination

Decision (7.2): tabled. If deep pages remain slow, add an activity_at column + index and a recursive-CTE skip scan that emits the max-activity row per session in global order and stops after offset+limit. Phase 2, gated on measurement.

4. Correctness considerations

Fallback: prompt-less sessions fall back to MIN(started_at) (only when every interception has last_prompt_at IS NULL). Multiple prompts per interception handled by the max. Monotonic via GREATEST. ended_at IS NOT NULL filter stays. Backfill must match the query aggregate. Audit table entry not required (not audited).

5. Alternatives considered

• Session-summary table + triggers: best read perf and native keyset, but violates the no-new-table/no-triggers constraint. Long-term option.
• Prompt-driven loose index scan: denormalizes onto the higher-volume prompts table and needs filter columns there; awkward for prompt-less sessions.
• Query-only / index-only: cannot remove the whole-set lateral nor make the aggregate indexable.

6. Validation plan

Large synthetic seed; EXPLAIN (ANALYZE, BUFFERS) before/after for first page, deep offset, filtered, time-window, and keyset paths; golden comparison of ordering and count; Go tests for ordering/fallback/multi-prompt/prompt-less/pagination; make gen/fmt/lint/test.

7. Decisions

1. 7.1: maintain last_prompt_at only; compute activity_at inline later if needed.
2. 7.2: defer 3.4 (skip scan); ship 3.3 and measure.
3. 7.3: fold the bump into the prompt insert CTE.