perf: serve the AI Bridge sessions list from an indexed anti-join - #28123

Related issue: https://linear.app/codercom/issue/AIGOV-580/ai-gateway-sessions-page-takes-5-10-seconds-to-load

Summary

ListAIBridgeSessions and CountAIBridgeSessions aggregate every completed interception before applying LIMIT, so the AI Gateway sessions page scales with the size of aibridge_interceptions (AIGOV-580). On a 2,000,000 interception dataset the list query takes 5.0-5.6 s and the count 5.7 s.

This represents a session by its latest interception that matches the filters, found with a NOT EXISTS probe for a newer interception in the same session. One partial index answers the probe, so the ordered scan stops after LIMIT sessions and the expensive aggregation runs only for that page.

Result on the same dataset: list 126 ms, count 490 ms. No new table, no triggers, no backfill.

Benchmark, 2M interceptions

Dataset: 2,000,000 interceptions, 318,747 sessions, 4.0M prompts, 3.0M token usages, 6.0M tool usages, 10 GB, 50 users, 5 providers, 12 models. Postgres 16, warm cache.

Output was diffed column by column against the pre-change query on that dataset: the unfiltered page is byte-identical. Filtered pages return the same set of sessions, with the ordering difference described below.

Behaviour changes, please review these deliberately

1. Ordering key. The list is ordered by the session's latest completed interception instead of last_active_at (the latest prompt, falling back to the earliest interception when a session has no prompts). last_active_at is still returned for display. The two disagree when interceptions within a session overlap in time, or when a prompt is recorded after a later interception has already started. Two existing subtests asserted the old order and were updated with comments explaining the trade: SortsByLastActive is now SortsByLatestInterception, and PromptlessSessionSortsByStartedAt is now PromptlessSessionOrdersByLatestInterception. If preserving the old ordering exactly is a requirement, this PR is the wrong shape and the alternative below is better.

2. Aggregates with a filter applied. started_at, ended_at, threads and last_active_at are now computed over all completed interceptions of the session rather than only the filter-matching ones. That makes them consistent with providers, models and the token totals, which were already session-wide. Pinned by the new AggregatesSpanUnfilteredInterceptions subtest.

3. Tie-break. Ties in started_at break on interception id instead of session_id.

Bug fixed

Filtered cursor pagination repeated rows. cursor_pos resolved the cursor session's position while ignoring the filters, so the HAVING comparison used filter-restricted values against an unfiltered cursor. On the benchmark dataset, page 2 of a model filtered list repeated 3 of 25 rows from page 1. cursor_pos now applies the same filters and the initiator, and pagination is keyset on a single row's (started_at, id), which is unique. Ten pages of 25 return 250 rows and 250 distinct sessions, filtered and unfiltered. Regression test: KeysetPaginationWithModelFilter, which fails against the old query with session ... returned on more than one page.

Relationship to the other open PRs

Three PRs now address AIGOV-580. Measured on the same 2M dataset:

These are complementary rather than competing. If #27900 lands, the anti-join here can key on its last_prompt_at column and then ordering is preserved with no behaviour change at all. A first attempt at that combination measured 418 ms because the expression needs a second index for the outer ordered scan, so it needs work before it is a viable follow-up. Happy to do that instead if the ordering change in point 1 is not acceptable.

Tests

TestAIBridgeListSessions passes with 29 subtests. Added:

• KeysetPaginationWithModelFilter, disjoint and complete filtered paging, regression test for the repeated rows.
• KeysetPaginationUnfiltered, the same for page sizes 1, 2 and 4.
• OrderedByLatestMatchingInterception, a session spanning two models orders by its latest matching interception.
• CountMatchesListedRows, count agrees with the unpaginated list, filtered and unfiltered.
• NewerInflightInterception, a session with a newer in-flight interception is keyed by its latest completed one.
• AggregatesSpanUnfilteredInterceptions, pins behaviour change 2.

go test ./enterprise/coderd -run TestAIBridgeListSessions -count=1   ok, 29/29 subtests
go test ./coderd/database/dbauthz -run AIBridge -count=1             ok
make pre-commit                                                      ok

Note on generated code

modelqueries.go positional arguments for ListAuthorizedAIBridgeSessions and CountAuthorizedAIBridgeSessions were reordered to match the new placeholder order. These hand-written wrappers are not checked by the compiler against the query text, so the ordering is easy to break silently. Worth reviewing carefully.

This work was generated by Coder Agents.