What the agent does
The agent reads the data before it decides what to index. In a Next.js chat app laid out like vercel/chatbot, lib/db/schema.ts defines Chat with a title and userId, and Message_v2 with chatId, role and parts as a json column holding the message's text, tool and file parts. lib/db/queries.ts connects with postgres and drizzle-orm/postgres-js, and getChatsByUserId pages a user's chats by createdAt with startingAfter and endingBefore; app/(chat)/api/history/route.ts exposes that as GET /api/history, and components/chat/sidebar-history.tsx pages through it with useSWRInfinite. There is no search: a user with a long history scrolls. package.json pins drizzle-orm at ^0.45.2 and drizzle-kit at ^0.31.10, drizzle.config.ts writes migrations to lib/db/migrations, lib/db/migrate.ts applies them with one connection at build time, and lib/utils.ts already has getTextFromMessage, which joins a message's text parts.
It keeps the design to what PostgreSQL does well. On Chat it adds a tsvector column generated from title with to_tsvector('english', title), using Drizzle's generatedAlwaysAs with a custom tsvector type, and a GIN index on it. On Message_v2 it adds a plain text column holding the message's text parts, filled by saveMessages and updateMessage through the same getTextFromMessage helper the client uses, and a second generated tsvector with its own GIN index. The text column exists because a generated column must be built from an immutable expression, and pulling text out of the json parts in SQL is not one; writing it from TypeScript keeps the migration simple and the index honest. The migration comes from drizzle-kit generate plus a hand-written backfill for existing messages that drizzle-kit generate --custom creates.
A new searchChatsByUserId in queries.ts takes the user's id and a query string, turns it into a websearch_to_tsquery('english', ...) so quoted phrases and a leading minus work the way people type them, matches either the chat's title vector or any of its messages' vectors, ranks with ts_rank, and returns chats with a ts_headline snippet from the best matching message, limited and scoped to the user as every query in that file is. GET /api/history accepts a q parameter and calls it, keeping the pagination parameters for the unsearched list. The sidebar gets an input that debounces into the SWR key, so the existing paging and the search share one component.
The agent proves it against a PostgreSQL it installs in its sandbox: it runs the migration with pnpm db:migrate, inserts a user, two chats and a few messages through the project's own saveChat and saveMessages, and queries for a word that appears only in a message body, only in a title, and in neither, reading back the ranked rows and snippets. It runs pnpm check, the Biome lint the project uses through ultracite, and tsc --noEmit, and quotes the three query results in the pull request.
Start it with one API call
POST https://api.phoenix.so/o/{org}/sessions with this body:
{
"agent": "claude",
"input": "Add full-text search over chats and messages. Read package.json, drizzle.config.ts, lib/db/schema.ts, lib/db/queries.ts, lib/db/migrate.ts, lib/db/migrations, lib/utils.ts, app/(chat)/api/history/route.ts and components/chat/sidebar-history.tsx first. Add a generated tsvector column on Chat from the title with a GIN index, a text column on Message_v2 filled from the message's text parts in saveMessages and updateMessage, and a generated tsvector with a GIN index on it, with a migration from drizzle-kit and a custom migration that backfills existing messages. Add searchChatsByUserId using websearch_to_tsquery, ts_rank and ts_headline, scoped to the user, and a q parameter on GET /api/history that calls it. Add a debounced search input to the sidebar that feeds the SWR key. Install a PostgreSQL in the sandbox, run the migration, insert data through the project's own query functions, run searches that hit a title, a message and nothing, run pnpm check and tsc --noEmit, and open a pull request with the results.",
"resources": [
{
"url": "https://github.com/your-org/your-app"
}
]
}- Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
- Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
- Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.
What comes back
What comes back is a pull request that changes schema.ts, queries.ts, the history route, the sidebar and lib/utils.ts, and adds two migrations under lib/db/migrations. The description shows the generated columns and indexes as the migration writes them, the search query, and the three sandbox searches with their ranked rows and snippets, plus the lint and type-check output.
The run's record keeps the migration against a real PostgreSQL, the inserts and each search, so a reviewer can see the title match rank above the body match rather than read that it should.
Before merging, a reviewer decides the language configuration, since english stems English words and a product with other languages may want simple, and whether the backfill should run as part of the build's db:migrate or as a separate step on a large table.
Run it on every Linear issue labelled search
A team makes this a rule. In the Project for the repository, an automation with the prompt above and a linear trigger on linear.issue.labeled for the label search starts a run when someone files "I cannot find the chat where I pasted the invoice". The trigger adds the issue after the prompt, so the run reads which fields the issue wants searched and indexes those.
Each run opens its own pull request on its own branch and links the issue. A repository that already has the vectors gets a description saying so and a pull request only for what the issue adds, such as searching document titles or filtering by date.
The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.
Why run it on Rush?
Full-text search in PostgreSQL is a column, an index and a query, but the details decide whether it works: a generated column has to be immutable, json parts do not index, a query the user types needs websearch_to_tsquery and not to_tsquery, and every row has to stay scoped to its user. The agent reads the schema and the existing queries, makes those choices in the open, and runs the migration and the searches against a real database in its sandbox.
The pull request is a schema change, one query and a route parameter, so review is reading the SQL. Across several apps on the same schema the runs go in parallel, each in its own sandbox with its own pull request.
Frequently asked questions
Why a text column on messages instead of indexing parts directly?
A generated column or an expression index must use an immutable expression, and extracting text from a json array of parts in SQL does not qualify. Writing the text from getTextFromMessage at save time keeps the vector generated from a plain column, and the backfill migration fills it for messages that already exist.
Does the migration lock the messages table?
Adding the columns is quick, and the backfill is a single update the custom migration runs; on a large table it is the slow part. The pull request names it so a reviewer can run the backfill separately. A GIN index built in the migration also takes time proportional to the rows.
Can a user search another user's chats?
No. searchChatsByUserId filters on userId the way getChatsByUserId does, and the route reads the id from the session, never from the request. The sandbox checks include a search for a word that exists only in another user's message and returns nothing.
Why websearch_to_tsquery?
It accepts the syntax people type, quoted phrases, a leading minus, or and and, and never throws on stray punctuation, where to_tsquery errors on an unbalanced quote. The ranking and snippet functions work on the same query.
More Drizzle jobs
The same job on other stacks
- Add Full-Text Search to a Mongoose API (Mongoose)
- Add Full-Text Search to a Prisma App (Prisma)
- Add Full-Text Search to a SQLModel and FastAPI App (SQLModel)