Skip to content

feat(coderd): add search_chat_messages builtin chatd tool - #29616

Closed
jscottmiller wants to merge 2 commits into
mainfrom
scott/search-chat-messages-tool
Closed

jscottmiller wants to merge 2 commits into
mainfrom
scott/search-chat-messages-tool

Conversation

@jscottmiller

Copy link
Copy Markdown
Contributor

Adds a search_chat_messages builtin chatd tool that searches a chat transcript in SQL (case-insensitive substring over text parts and tool name/args/result) and returns bounded excerpts newest first, paged on id. It defaults to the calling chat; chat_id may name an ancestor or descendant chat with the same owner. Explore-mode subagents may only search their own chat. Model-only and deleted rows are never returned.

Backend only, no HTTP route or frontend renderer. The tool is registered for root chats and subagents, allowed in plan mode, and included in the explore allowlist. Also adds a partial index idx_chat_messages_chat_visible_id (chat_id, id DESC) WHERE deleted = false AND visibility IN ('user','both') so an unfiltered first page does not scan the whole chat.

Plan

Plan: search_chat_messages builtin chatd tool

Branch: scott/search-chat-messages-tool off origin/main (38a17ec).
Backend only: coderd/database and coderd/x/chatd. No HTTP route.

Findings that shape the design

  • chat_messages.content is a jsonb array of parts (type in text,
    reasoning, tool-call, tool-result, file, ...). Text lives in part->>'text';
    tool rows carry tool_name, args (jsonb) and result (jsonb).
  • A search_tsv tsvector column already exists, but it is populated
    asynchronously by a dbpurge background job and only for
    role IN ('user','assistant') rows. Recent rows (the ones an orchestrator
    most often wants) can still be NULL, tool rows are never vectorized, and
    stemming is imprecise for identifiers and paths.
  • Existing message reads that a client can see filter
    visibility IN ('user','both') AND deleted = false and page on id
    (GetChatMessagesByChatIDDescPaginated). Query comments state id is the
    durable append order and created_at can disagree with it.
  • Subagent tools (wait_agent, message_agent, interrupt_agent) authorize
    cross-chat access with isSubagentDescendant(ctx, db, caller, target)
    (target must be a descendant of the caller). There is no existing
    child-to-parent helper. Tools run under dbauthz.AsChatd.
  • subagentTools is only appended for root chats
    (appendRootChatTools). The always-present tool list is built in
    generation_preparer.go (tools := []fantasy.AgentTool{...}), then
    filtered by builtinPlanToolAllowed (plan mode) and
    allowedExploreToolNames (explore subagents), both name allowlists.

Decisions (deviations from the overview are marked)

  1. Search mechanism: case-insensitive substring in SQL, not tsvector.
    strpos(lower(search_text), lower(@query)) > 0 over text assembled in SQL
    from text parts plus, for tool rows, tool_name, args::text and
    result::text. Reasons: exact and synchronous (no backfill lag), covers
    tool rows, no LIKE metacharacter escaping. Reasoning and file parts are
    excluded. The scan is bounded to one chat_id. Review finding chore: Initial GHA workflow #1 added a
    partial index idx_chat_messages_chat_visible_id (chat_id, id DESC) WHERE deleted = false AND visibility IN ('user','both') (migration 000598) so
    an unfiltered first page does not scan the whole chat; it also serves
    GetChatMessagesByChatIDDescPaginated.
  2. Excerpt computed in SQL with substring(search_text FROM GREATEST(hit - ctx, 1) FOR 2*ctx + char_length(query)), so only the
    excerpt (not full message text) crosses the wire. Go adds leading and
    trailing ellipses using the returned hit_pos and text_length. Context
    is 160 characters each side; the excerpt is additionally capped at 400
    runes in Go.
  3. Pagination on id DESC with before_id (deviation: overview said
    created_at and id). id is the append order the existing paginated query
    and the API cursor use; created_at is the transaction start time and can
    disagree with append order. Query fetches limit + 1 to set has_more;
    next_before_id is the last returned id.
  4. Visibility: always visibility IN ('user','both') AND deleted = false,
    including when searching the calling chat. Model-only rows (compaction
    summaries, hidden sentinels) are excluded; there is no argument to include
    them. Rationale: keeps the result identical to what the transcript API
    shows and avoids leaking compaction summaries into a model that then
    re-quotes them.
  5. Authorization (deviation: adds one direction): allowed when target is
    the caller, when isSubagentDescendant(caller, target) (existing helper,
    parent to child), or when isSubagentDescendant(target, caller) (same
    helper with arguments swapped, child to parent or any ancestor). In all
    cross-chat cases target.OwnerID == caller.OwnerID is also required.
    Explore-mode subagent callers may search only their own chat (orchestrator
    decision on review finding chore: Formatting - bring .prettierrc over from cdr/m #9).
    Denied and not-found both return the same "chat not found or not
    accessible" tool error. Archived targets are allowed (history recovery is
    a motivating use). The dbauthz method authorizes ActionRead on the chat
    like its siblings, which the chatd actor satisfies.
  6. Registration: appended to the always-present tool list in
    generation_preparer.go so root chats and subagents (general, explore,
    computer_use) all get it. Added as allowed to builtinPlanToolAllowed
    (read-only, for root and child) and allowedExploreToolNames. Not
    added to unbilledSubagentToolNames, so it is billed and counted like
    other local tools. No exclusivity.
  7. Tool args: query (required, trimmed, 1..200 runes), chat_id
    (optional UUID, default calling chat), role (optional, one of
    user/assistant/tool/system), since/until (optional RFC 3339),
    limit (default 10, clamped 1..50), before_id (optional int64 > 0).
    Result: chat_id, query, matches (newest first: id, created_at,
    role, excerpt, tool_name when present), returned, has_more,
    next_before_id (omitted when has_more is false).

Affected files

  • coderd/database/queries/chats.sql: SearchChatMessages :many.
  • coderd/database/migrations/000598_chat_messages_visible_id_index.{up,down}.sql.
  • Generated: coderd/database/queries.sql.go, querier.go, dump.sql,
    dbmock/dbmock.go, dbmetrics/querymetrics.go.
  • coderd/database/dbauthz/dbauthz.go (+ dbauthz_test.go): method
    authorizing ActionRead on the chat.
  • coderd/database/querier_test.go: postgres tests for the query.
  • coderd/x/chatd/search_chat_messages.go (new): args, tool, authz check,
    excerpt shaping.
  • coderd/x/chatd/search_chat_messages_internal_test.go (new).
  • coderd/x/chatd/generation_preparer.go: registration.
  • coderd/x/chatd/chatd.go: plan and explore allowlists.
  • coderd/x/chatd/ARCHITECTURE.md: TODO item in the tools list only.

Sequencing

  1. Commit feat(coderd/database): add SearchChatMessages query: SQL, gen,
    dbauthz method and test, querier tests.
  2. Commit feat(coderd/x/chatd): add search_chat_messages builtin tool:
    tool, registration, allowlists, tests, ARCHITECTURE TODO.
  3. Panel review (3 personas), REVIEW-diff.md, checkpoint report.
  4. Apply corrections, re-run tests, push, open draft PR (label
    experimental), final report.

Testing

  • querier_test.go (dbtestutil, postgres): substring match is
    case-insensitive; role filter; since/until; excludes deleted and
    model-only rows; tool rows match on tool_name/args/result and return
    tool_name; excerpt window bounded and hit_pos correct; before_id paging
    and limit; no match on reasoning-only text.
  • dbauthz_test.go: SearchChatMessages asserts ActionRead on the chat.
  • search_chat_messages_internal_test.go: own chat default; child chat
    from parent; parent chat from child; unrelated chat of same owner denied;
    limit clamps (0 -> default 10, 500 -> 50); invalid role/timestamp/chat_id
    errors; has_more and cursor; tool registered for root and child turns and
    allowed in plan and explore allowlists.
  • Checks: make gen no diff, make lint on touched packages, targeted
    go test runs with postgres.
Review findings and dispositions

Findings and dispositions

# Sev Reviewer Finding Disposition
1 HIGH Dana Without a role filter no index honors ORDER BY id DESC LIMIT n under the visibility/deleted predicate; at 20k messages a first page costs ~290 ms and grows linearly. A partial index (chat_id, id DESC) WHERE deleted = false AND visibility IN ('user','both') brought it to ~2 ms and also serves GetChatMessagesByChatIDDescPaginated. Accept. Add migration creating idx_chat_messages_chat_visible_id with that definition.
2 MEDIUM Dana c.search_text IS NOT NULL is redundant (strpos(NULL, q) > 0 is already not true) and doubles the subselect evaluation per row. Accept. Drop the predicate.
3 MEDIUM Dana, Malik Tool args/result are matched as serialized JSON, so queries containing quotes or newlines miss tool rows; excerpts are raw quoted text. Neither is stated. Accept. State the JSON serialization in the SQL comment and the tool description, and say excerpts are quoted transcript text, not instructions.
4 MEDIUM Malik authorizeChatSearch is the sole gate: the chatd store actor has deployment-wide chat read, so dbauthz always passes. Consistent with wait_agent, but this tool returns content. Add a comment stating the invariant and tests for different owner, sibling, grandchild. Accept. Comment states the local invariant; add the tests.
5 MEDIUM Priya builtinPlanToolAllowed was extended but PlanModeIncludesOnlyAllowlistedBuiltIns and PlanModeChildChatsAllowExplorationOnly were not updated. Accept. Add the tool to both subtests.
6 LOW Dana tool_name subselect uses LIMIT 1 without ORDER BY. Accept. Use WITH ORDINALITY ... ORDER BY ordinality.
7 LOW Dana hit_pos computed on lower(search_text) indexes search_text; correct only while lower() preserves length (true for libc UTF-8, not guaranteed under ICU). Accept. Note the assumption in the SQL comment.
8 LOW Dana LIMIT @limit_val::int has no floor unlike siblings' COALESCE(NULLIF(...), 50). Reject. The only caller passes limit + 1 >= 2; a silent default of 50 would break the has_more arithmetic and hide a caller bug.
9 LOW Malik Explore subagents can now read the parent transcript, including tool results from tools they lack. Accept (orchestrator decision: RESTRICT). authorizeChatSearch denies any target other than the caller when caller.Mode is explore; other callers keep the ancestor/descendant same-owner rule. Test TestSearchChatMessagesTool_ExploreSubagentSearchesOwnChatOnly covers own chat (default and explicit), parent denied, and parent still reaching the explore child. Arg description and ARCHITECTURE TODO updated.
10 LOW Malik Same-owner check is redundant given owner inheritance. No change. Kept because it fails closed if lineage is ever broken.
11 LOW Priya time.Parse(time.RFC3339) rejects date-only and zone-less timestamps models often produce. Accept partially. Add an example to the arg descriptions; reject a date-only fallback because until on a date has ambiguous end-of-day semantics.
12 LOW Priya Role is case-sensitive. Accept. Lowercase before validation.
13 LOW Priya created_at is second precision while since/until compare at full precision, so a returned value fed back as until is lossy. Accept. Format with time.RFC3339Nano.
14 LOW Priya PlanningSubagentOverlayPrompt lists the exploration tools a plan-mode child may use and omits the new tool. Accept. Add it to the sentence.
15 LOW Priya No frontend renderer, icon, or type entry; falls through to the generic renderer. Out of scope per overview. Reported in the final report.
16 NIT Malik role: "system" is accepted but every system row is visibility = 'model', so it can never match. Accept. Remove system from accepted roles and description.
17 NIT Malik Non-ErrNoRows errors from GetChatByID return a different message than the denied case. Reject. A store error is operational, not an authorization oracle; wrapping matches siblings.
18 NIT Priya Allowlists use the literal name while a constant exists. Reject. The siblings in those maps are literals and the literal keeps gofmt alignment; using the constant re-indents 25 unrelated lines.
19 NIT Priya Comment on authorizeChatSearch refers to a sibling function. Accept. Reword to describe the mechanism (reachability via parent_chat_id).
20 NIT Dana Query tests lack a multibyte case and a hit straddling two text parts; SinceUntil mutates a shared row. Accept partially. Add multibyte and straddling cases; use a dedicated row for the since/until test.

Open items

Corrections applied

All accepted dispositions above were applied after the checkpoint:


This PR was generated by Coder Agents on behalf of @jscottmiller.

@jscottmiller jscottmiller added the experimental Changes that might not necessarily be merged, until its approved to proceed with. label Sep 20, 2026
@linear-code

linear-code Bot commented Sep 22, 2026 •

Copy link
Copy Markdown

CODAGT-1103

CODAGT-1035

@github-actions github-actions Bot locked and limited conversation to collaborators Oct 16, 2026
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Labels

experimental Changes that might not necessarily be merged, until its approved to proceed with.

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant