fix(coderd): give chat message ids an append-order guarantee - #27495

Chat message ordering was derived from created_at, which is now() and therefore the transaction start time. That makes it unusable as an append-order column for two independent reasons: every row in one InsertChatMessages batch shares a single timestamp, and two concurrent transactions can commit in the opposite order to the one they started in.

This PR gives chat_messages.id a real append-order guarantee and moves the history reads onto it.

Changes

InsertChatMessages had no input-order guarantee. Callers index the returned slice by input position. That only worked because PostgreSQL happens to evaluate the BIGSERIAL default in row order. Ids are now allocated up front and the k-th smallest is assigned to input index k, so the pairing does not depend on where the column default is evaluated. Returned rows are explicitly ORDER BY id.

Three history reads now order by id.

GetChatMessagesByChatID paginated by id while ordering by created_at, which is incoherent on its own terms.

The other two matter because of who consumes them. The stream query supplies incremental updates on the same socket that emits a full GetChatMessagesByChatID snapshot on history reset, so once that snapshot moved to id the two disagreed under timestamp skew. GetLastChatMessageByRole returns an id that is then used as an id cursor, both as AfterID when synthesizing tool cancellations and as chats.last_read_message_id, where a stale anchor leaves later assistant messages permanently unread.

A tie-breaker would not have fixed either one. It only resolves equal timestamps; leading with created_at is the actual defect.

GetLastChatMessageByRole loses its index, so this adds one. ORDER BY created_at DESC, id DESC could take an ordered scan of idx_chat_messages_chat_created. Nothing in the schema can supply ORDER BY id DESC LIMIT 1 for a given chat_id and role, so the planner switches to a backward scan of the primary key and filters every newer row in the table, scanning all of it when the chat has no message in that role, which is the routine case for a fresh chat. Migration 000559 adds (chat_id, role, id DESC) WHERE deleted = false, the same shape as the existing idx_chat_messages_user_prompts. This matters because the query is hot: it runs on every stream connect and disconnect, and once per turn when synthesizing tool cancellations.

GetChatMessagesForPromptByChatID has the same defect and is fixed in the stacked PR, because its compaction boundary change is semantic and deserves a separate review. Auto-archive stays timestamp-based deliberately: it measures activity, not order.

Wrapping the insert in a CTE (needed because INSERT cannot take ORDER BY) makes sqlc synthesize InsertChatMessagesRow. It is structurally identical to ChatMessage, so the call sites use a direct struct conversion that stops compiling if the two ever diverge.

Testing

Behavior tests write created_at values inverted against id order, so a reader that leads with created_at returns the batch backwards. All three queries were verified red by reverting the ORDER BY and regenerating: the stream query returned [3,2,1] for [1,2,3], and GetLastChatMessageByRole picked id 1 instead of id 3.

TestInsertChatMessagesOrderContract asserts against the generated SQL, covering what a behavior test cannot: PostgreSQL evaluates the id default in row order anyway, so a batch still looks ordered once the guarantee is removed.

TestChatMessagesSequenceCacheIsOne guards the cross-batch half of the invariant. Ids follow chat row lock order only while the sequence hands out one value at a time; sequence cache blocks are per session, so with a cache above one a backend holding stale cached values can lock second and still commit lower ids. Bumping a sequence cache is an ordinary throughput tweak, and it would silently corrupt history order.

The index was checked on a 200k row fixture. Without it, the zero-match lookup filters all 200,000 rows over 2763 buffers; with it, the plan is an index scan with both chat_id and role in the index condition, no sort node, and 3 buffers.

Note that the within-batch mapping does not depend on the cache size. It is established by ROW_NUMBER() OVER (ORDER BY id) over the allocated ids, so it holds regardless of nextval evaluation order.

Note on the deleted subagent hand-sort

The subagent history reader's hand-sort stays deleted, but calling it redundant was imprecise. It sorted by created_at then id, so it is only equivalent to id ordering when the two agree. When they disagree the old code selected a different "latest assistant". This is a deliberate behavior change to match the new invariant, not dead-code removal.

Opened by Mux on behalf of Mike.