Track conversation-items query scaling for long-running threads

[AnthonyRonning]

Summary

The mixed conversation-items pagination query behind GET /v1/conversations/:id/items is fine at current scale, but it is a hot path and is likely to become more important as we introduce longer-running agent-style conversations.

A likely future use case is an agent project that keeps a single conversation thread forever, with new items continuously appended over time. That makes this query shape worth tracking now even if it is not yet a production bottleneck.

This issue is not about the recent conversations.updated_at / recency work for sidebar ordering. That work is separate and should remain separate.

Current code path

The current query shape comes from RawThreadMessage::get_conversation_context(...) in src/models/responses.rs.

It is used by:

• GET /v1/conversations/:id/items
• the broader item-loading path behind GET /v1/conversations/:id/items/:item_id

Why this is worth tracking

The current shape:

• builds a merged conversation_messages stream across user, assistant, tool-call, tool-output, and reasoning tables
• uses that merged stream once to find the cursor row
• then uses the merged stream again to produce the page

That is acceptable for normal conversations today, but it becomes less attractive if a single conversation accumulates a very large number of mixed items over time.

Current assessment

This is not urgent at current scale.

Reasons:

• it is scoped to a single conversation_id
• the underlying tables already have per-conversation indexes
• it is a natural access pattern for a conversation-items API
• other clearly expensive queries should be optimized first if they become user-visible bottlenecks

That said, this path is hot and its cost grows with the size of an individual conversation timeline.

What is less ideal about the current query

1. The unioned CTE is referenced twice.
2. Cursor lookup happens through the merged stream instead of directly using table-specific uuid indexes.
3. Cost scales with conversation size more than page size.
4. The query returns content_enc, so it cannot realistically become index-only.

Future optimization options

Option 1: Resolve the cursor separately first

Do a small cursor lookup query first, using underlying row uuid indexes directly, then pass the resolved (created_at, id) values into the main paginated query.

Why this is attractive:

• smallest conceptual change
• preserves the current mixed-stream model
• removes the need to find the cursor through the unioned stream

Option 2: Push the cursor predicate into each UNION ALL arm

Instead of building the full mixed stream and filtering afterward, filter each child table by conversation_id and cursor bounds first, then union the restricted subsets.

Why this is attractive:

• reduces the size of the merged result set
• gives the planner more chances to use per-table composite indexes effectively

Tradeoff:

• SQL gets more complex
• cursor logic gets duplicated across the union arms

Option 3: Introduce a unified conversation-items stream

If very large, tool-heavy, agent-driven conversations become a primary use case, move toward a single append-only conversation_items table or equivalent unified stream.

Why this is attractive:

• simpler pagination
• simpler cursor resolution
• more direct support for long-running mixed timelines

Tradeoff:

• larger schema and application refactor
• not justified yet without stronger evidence

Current recommendation

For now:

• keep the current query shape
• keep monitoring it as conversation sizes evolve
• if it later needs work, prefer:
  1. Option 1 first
  2. Option 2 second
  3. Option 3 only if long-running agent timelines truly become a dominant workload

One adjacent note

The single-item retrieval path currently loads the broader conversation stream and then finds one item in memory. If that endpoint becomes hot, it may be an easier and more obvious optimization target than redesigning the paginated mixed-stream query immediately.

Current query

SQL

with conversation_messages as (
    select $1 as message_type, um.id, um.uuid, um.content_enc, $2::text as status, um.created_at, r.model, um.prompt_tokens as token_count, null::uuid as tool_call_id, null::text as finish_reason, null::text as tool_name from user_messages um left join responses r on um.response_id = r.id where um.conversation_id = $3
    union all
    select $4 as message_type, am.id, am.uuid, am.content_enc, am.status, am.created_at, r.model, am.completion_tokens as token_count, null::uuid as tool_call_id, am.finish_reason, null::text as tool_name from assistant_messages am left join responses r on am.response_id = r.id where am.conversation_id = $5
    union all
    select $6 as message_type, tc.id, tc.uuid, tc.arguments_enc as content_enc, $7::text as status, tc.created_at, null::text as model, tc.argument_tokens as token_count, tc.uuid as tool_call_id, null::text as finish_reason, tc.name as tool_name from tool_calls tc where tc.conversation_id = $8
    union all
    select $9 as message_type, tto.id, tto.uuid, tto.output_enc as content_enc, $10::text as status, tto.created_at, null::text as model, tto.output_tokens as token_count, tc.uuid as tool_call_id, null::text as finish_reason, tc.name as tool_name from tool_outputs tto join tool_calls tc on tto.tool_call_fk = tc.id where tto.conversation_id = $11
    union all
    select $12 as message_type, ri.id, ri.uuid, ri.content_enc, ri.status, ri.created_at, null::text as model, ri.reasoning_tokens as token_count, null::uuid as tool_call_id, null::text as finish_reason, null::text as tool_name from reasoning_items ri where ri.conversation_id = $13
), cursor_message as (
    select created_at, id from conversation_messages where uuid = $14
)
select cm.*
from conversation_messages cm, cursor_message
where (cm.created_at > cursor_message.created_at)
   or (cm.created_at = cursor_message.created_at and cm.id > cursor_message.id)
order by created_at asc, id asc
limit $15