Stack context

Follows #27495 (merged), which gives chat_messages.id an append-order guarantee and moves the history reads onto it. This PR applies the same fix to the query that builds the model prompt.

Why?

GetChatMessagesForPromptByChatID mixed two orderings. It selected the compaction boundary with created_at DESC, id DESC, then applied that boundary with an id > comparison, and returned rows with created_at ASC, id ASC.

created_at is now(), so it is the transaction start time. Every row in one insert batch shares it, and concurrent transactions can commit in the opposite order to the one they started in. Two consequences, both reaching the provider:

• Malformed prompts. A tool result could be ordered ahead of the assistant message that requested it. chatprompt.injectMissingToolResults does not repair this: it only handles tool rows already contiguous after an assistant row, and adds missing results. It never moves a tool row that precedes its assistant, and nothing re-sorts the rows in Go.
• Wrong compaction boundary. The boundary is picked by timestamp but compared by id, so a stale compressed summary could be retained while the actual latest one was dropped.

Changes

Both the boundary CTE and the outer query order by id. The id > predicate is unchanged, which is the point: the ordering now matches the comparison that was always being made.

The boundary index was dead, so it is rebuilt to match. idx_chat_messages_compressed_summary_boundary was created for exactly this lookup, but its predicate requires role = 'system' while compaction writes its summary with the user role (message_conversion.go:334, the only writer of compressed = true). It matched zero rows, and no other query can use it. Migration 000560 rebuilds it as (chat_id, id DESC) WHERE compressed AND NOT deleted AND visibility = 'model', which also matches the new order key.

Measured on PostgreSQL 13 with a 20k-message chat, 11 summaries, and 14 sibling chats so chat_id is selective:

Not in scope: the outer SELECT still inspects every row of the chat, because its role = 'system' AND compressed = FALSE disjunct has no lower id bound. That predates this PR and needs a query rewrite rather than an index.

Testing

Two subtests, both verified red by reverting the ORDER BY and regenerating:

• OrdersByIDWhenTimestampsDisagree returned [4,3,2,1] instead of [1,2,3,4], placing the tool result before the assistant call.
• CompactionBoundaryUsesID selected the stale summary and leaked the messages between the two summaries into the prompt.

Existing subtests pass unchanged. Migration up/down tests pass, and the rebuilt index was verified red-green: restoring the old predicate returns the plan to a 267-buffer scan, and the old predicate matches 0 rows in the fixture.

Opened by Mux on behalf of Mike.