CODAGT-319 Follow-up — Dedicated /prompts endpoint

Context

The merged feature (#25004 / d32842f) cycles only through messages already loaded in the in-memory chat store, which is capped at the first 50 messages of the current page. Long chats and chats whose oldest turns have rolled out of the page can no longer recall their full prompt history. This follow-up exposes a dedicated server endpoint that returns the user-authored prompts in a chat, newest first, and rewires the composer to use it.

Design

Endpoint

GET /api/experimental/chats/{chat}/prompts?limit=N

Returns:

type ChatPrompt struct {
    ID   int64
    Text string
}
type ChatPromptsResponse struct {
    Prompts []ChatPrompt
}

• limit: 0..2000. 0 (the default) → server-side default of 500. The wire-level default is encoded in SQL as COALESCE(NULLIF($limit, 0), 500). Negatives are rejected upstream by PositiveInt32; the handler only caps the upper bound.
• Auth: parent-chat read in dbauthz, mirroring GetChatMessagesByChatID.
• Listed under the experimental router so we can iterate without API guarantees.

SQL

The query lives in coderd/database/queries/chats.sql as GetChatUserPromptsByChatID:

• Filters role='user', deleted=false, visibility IN ('user','both') to mirror the composer's "what the user actually typed and can re-send" contract.
• Guards the lateral with jsonb_typeof(content) = 'array' so legacy V0 rows whose content is a scalar JSON string (predates migration 000434) are silently excluded instead of raising "cannot extract elements from a scalar".
• Unrolls content JSONB with jsonb_array_elements WITH ORDINALITY and concatenates only type='text' parts, preserving original order via string_agg(... ORDER BY ordinality).
• Casts the result to text so sqlc emits a string field instead of []byte.
• Drops whitespace-only prompts via HAVING string_agg(...) ~ '\S' so cycling never lands on a blank entry.
• Orders by cm.id DESC (id is a sequence, so this is "newest first" without relying on created_at).

Index

New partial index added in migration 000494:

CREATE INDEX idx_chat_messages_user_prompts
ON chat_messages (chat_id, id DESC)
WHERE deleted = false
  AND role = 'user'
  AND visibility IN ('user', 'both');

The partial WHERE clause matches the query's filter exactly, so the planner can use the index for both filtering and ordering without a sort step.

Frontend

• chatPromptsKey(chatId) and chatPromptsQuery(chatId) factories in site/src/api/queries/chats.ts. staleTime: 30s, enabled: chatId !== "". Asks the server for 500 prompts (well below the 2000 max, plenty for the cycle).
• ChatPageContent.tsx replaces the in-memory derivation with useQuery(chatPromptsQuery(chatId ?? "")). The composer's cycleHistorySnapshotRef already takes a stable snapshot at cycle entry, so a refetch arriving mid-cycle cannot shift the indexed prompt out from under the user.
• getEditableUserMessagePayload extracts the edit-path text from raw user-message parts (filter type === "text", join verbatim) instead of going through parseMessageContent / appendText, which is built for assistant streams and intentionally drops whitespace-only chunks. Without this, cycling and clicking Edit on the same message could produce different draft text for messages with whitespace-only interleaved text parts.
• Cache invalidation: createChatMessage.onSuccess, editChatMessage.onSettled, and useChatStore.upsertCacheMessages (when at least one upserted message has role === "user") all invalidate chatPromptsKey(chatId).

Tests

• TestGetChatUserPrompts (coderd/exp_chats_test.go) covers:
  • NewestFirstFiltering — multi-part concatenation, non-text parts skipped, whitespace-only filtered, soft-deleted excluded, model-only visibility excluded, assistant-role excluded by cm.role = 'user', legacy V0 scalar row silently excluded by the jsonb_typeof guard, ordering newest first.
  • LimitClampsResults — explicit limit=2 returns the two newest prompts.
  • InvalidLimitRejected — limit=5000 is 400 Bad Request.
  • NotFoundForOtherUsers — a separate user in the same org gets 404, not the prompts.
  • EmptyResultIsJSONArray — zero-message chat and assistant-only chat both return Prompts: [] (non-nil, empty).
• messageParsing.test.ts adds a unit test asserting that getEditableUserMessagePayload(["hello", " ", "world"]) returns "hello world", locking in the agreement with the SQL string_agg.
• dbauthz_test.go adds the MethodTestSuite.TestChats/GetChatUserPromptsByChatID entry, asserting the parent-chat policy.ActionRead.

Out of scope

• Cross-chat history.
• Per-user opt-out for the cycle.
• File-reference / attachment cycling — the cycle still reproduces plain text only, by design.

Four review rounds, eight unique findings, all addressed in this PR (approved twice). Rebased onto main twice after R4: first to pick up new migrations 000491 / 000492, then again for 000493_idx_chat_diff_statuses_url_lower. The prompts-index migration was renumbered 000491 → 000493 → 000494 via coderd/database/migrations/fix_migration_numbers.sh; no other diff changes.