feat: Add full text search over chat messages - #27126
Merged
Merged
Conversation
This commit adds the required schema for chat search: - search_tsv tsvector column on chat_messages (default NULL). It is not queried or referenced directly in Go code. - Partial GIN full-text search index on chat_messages.search_tsv where non-null - btree index on chat_messages.search_tsv where null - GIN full-text-search indexes on chats.title and chat_diff_statuses.pull_request_title - chat_message_search_text stored function to extract message content It also modifies the existing trigger functions set_chat_message_revision_before and update_chat_history_after_message_update to disregard search_tsv. This is needed to avoid spurious chat history revision updates.
- Adds a new query `BackfillChatMessagesSearchTsv` to populate search_tsv for a batch of chat_messages rows. This leverages idx_chat_messages_search_tsv_pending added in the previous commit. - Adds Prometheus metric coderd_dbpurge_chat_search_rows_backfilled_total so operators can track chat message indexing progress. - Adds a background task to dbpurge to index up to 50,000 rows in 10,000 row batches.
- Adds a `search` parameter to the `GetChats` query that matches over chat title, PR title, or message body. PostgreSQL full-text search is used for all matches. - Adds database-level tests for chat FTS. NB: a search query that is completely numeric is treated as a PR number search (exact match).
- Adds `search:` to `searchquery.Chats` that accepts a single search parameter (multiple terms in quotes). - Mutually exclusive with `title`, `pr_title`, `pr`. - Validation: only non-empty values are supported.
- Wires the `search` filter through to `GetChats` - Adds a preflight query to check if the tokenized query evaluates to an empty tsquery, returns an 400 error if so. - Adds corresponding API tests.
Docs preview📖 View docs preview for |
johnstcn
requested review from
DanielleMaywood,
hugodutka,
ibetitsmike and
mafredri
July 9, 2026 14:59
Contributor
There was a problem hiding this comment.
Pull request overview
Adds end-to-end support for full-text search over chat history, spanning schema, backfill mechanics, query/filter plumbing, API validation, and documentation/metrics updates.
Changes:
- Introduces
chat_messages.search_tsvplus supporting indexes and trigger adjustments to avoid chatd state bumps during backfill. - Adds a dbpurge-driven backfill (
BackfillChatMessagesSearchTsv) and a query preflight (ChatSearchQueryIsEmpty) to reject searches that tokenize to nothing. - Wires a
search:filter through the searchquery parser,GetChatsSQL, and the experimental chats API, with tests, metrics, and docs updates.
Reviewed changes
Copilot reviewed 21 out of 26 changed files in this pull request and generated 2 comments.
Show a summary per file
| File | Description |
|---|---|
| scripts/metricsdocgen/generated_metrics | Registers the new dbpurge backfill counter in generated metrics output. |
| docs/reference/api/chats.md | Documents search: in the experimental chats endpoint query syntax. |
| docs/admin/integrations/prometheus.md | Adds the new backfill counter to the Prometheus metrics reference table. |
| coderd/x/chatd/chatstate/trigger_test.go | Adds regression coverage ensuring search_tsv updates do not perturb chat state/history fields. |
| coderd/searchquery/search.go | Parses search: and enforces mutual exclusion with title:, pr_title:, and pr:. |
| coderd/searchquery/search_test.go | Adds table-driven test coverage for search: parsing and validation. |
| coderd/exp_chats.go | Validates search text, passes Search through to GetChats, and updates swagger param docs. |
| coderd/exp_chats_test.go | Adds handler-level tests covering matches, composition, empty-token searches, and mutual exclusion errors. |
| coderd/database/queries/chats.sql | Adds backfill and preflight sqlc queries; extends GetChats with full-text search logic. |
| coderd/database/queries.sql.go | Regenerates sqlc output for new queries and the updated GetChats signature/SQL. |
| coderd/database/querier.go | Extends the sqlc querier interface with backfill + preflight methods. |
| coderd/database/querier_test.go | Adds database-level tests verifying GetChats search behavior across title/PR/message arms and composition. |
| coderd/database/models.go | Extends ChatMessage model with the new search_tsv column. |
| coderd/database/modelqueries.go | Threads the new Search param through the authorized chat listing path. |
| coderd/database/migrations/migrate_test.go | Adds migration tests for extraction function behavior and index presence/queue semantics. |
| coderd/database/migrations/000541_chat_search_schema.up.sql | Adds chat_message_search_text, search_tsv, indexes, and trigger changes to ignore search_tsv updates. |
| coderd/database/migrations/000541_chat_search_schema.down.sql | Reverts trigger changes and removes new schema objects. |
| coderd/database/dump.sql | Updates schema dump with new function/column/indexes and trigger logic. |
| coderd/database/dbpurge/dbpurge.go | Adds incremental per-tick backfill loop and a dedicated metric counter. |
| coderd/database/dbpurge/dbpurge_test.go | Adds integration tests for backfill convergence, ordering, sentinel behavior, per-tick bounds, and metrics. |
| coderd/database/dbmock/dbmock.go | Regenerates mocks to include the new store methods. |
| coderd/database/dbmetrics/querymetrics.go | Adds query latency/count instrumentation for the new queries. |
| coderd/database/dbauthz/dbauthz.go | Adds authz gating for backfill (chat:update) and preflight (chat:read). |
| coderd/database/dbauthz/dbauthz_test.go | Adds method-level authz tests for the new queries. |
| coderd/apidoc/swagger.json | Regenerates swagger with updated q parameter documentation. |
| coderd/apidoc/docs.go | Regenerates embedded swagger template with updated q parameter documentation. |
Files not reviewed (5)
- coderd/apidoc/docs.go: Generated file
- coderd/database/dbmetrics/querymetrics.go: Generated file
- coderd/database/dbmock/dbmock.go: Generated file
- coderd/database/models.go: Generated file
- coderd/database/querier.go: Generated file
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
johnstcn
commented
Jul 13, 2026
mafredri
reviewed
Jul 13, 2026
mafredri
left a comment
Member
There was a problem hiding this comment.
This came out simpler than I had feared, nice work. Some feedback inline.
| rbac.ResourceWorkspaceBuildOrchestration.Type: {policy.ActionDelete}, | ||
| // Chat auto-archive sets archived=true on inactive chats. | ||
| // Chat auto-archive sets archived=true on inactive chats; the | ||
| // search_tsv backfill also updates chat messages. |
Member
There was a problem hiding this comment.
This being a part of dbpurge feels a bit off.
Member
Author
There was a problem hiding this comment.
Agreed. Danny has an RFC in the works but I didn't want to block this until that got finalized.
Member
There was a problem hiding this comment.
Unless we have a timeline for when/if it will be actually implemented. It might be worthwhile making a copy of dbpurge, say dblivemigrate and plopping the implementation there.
If not, then at least make it very explicit with comments and possibly file naming that it's in the wrong place and meant to be moved once XXX lands.
| SELECT 1 | ||
| FROM chat_diff_statuses cds | ||
| WHERE cds.chat_id = chats_expanded.id | ||
| AND to_tsvector('simple', cds.pull_request_title) @@ websearch_to_tsquery('simple', @search) |
Member
There was a problem hiding this comment.
Have you looked at how well the query planner can optimize these SELECT 1 queries? IIRC it isn't always very efficient.
Member
Author
There was a problem hiding this comment.
I haven't. I think we may want to do an EXPLAIN on the query once the indexes are in place on dogfood.
mafredri
approved these changes
Jul 15, 2026
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Closes CODAGT-721
Closes CODAGT-722
Closes CODAGT-723
Closes CODAGT-724
Closes CODAGT-725
This PR adds the database and API pieces necessary to support full-text chat message search.
Notes:
dbpurge(sorry). Newest messages get indexed first.CREATE INDEX CONCURRENTLY, but this approach turned out to be way nicer.tsvectorinstead of just having it as a GIN expression index.search_tsv IS NOT NULLwas needed to make the backfill query not be terrible.search_tsvis populated. Otherwise this would mean that the FTS indexing would cause in-progress chats to interrupt due to their revision being bumped.Agents were most definitely used in this PR.