# Add Full-Text Search to a Drizzle and PostgreSQL App

Yes. An agent adds PostgreSQL full-text search over chats and messages with a Drizzle schema change, a GIN index, a query and a route, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-full-text-search/drizzle/). Opens the console with this prompt ready to send, in a Project that has your repository.

## 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`

```json
{
  "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"
    }
  ]
}
```

1. **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.
2. **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.
3. **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.

## 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

- [Add a Drizzle Migration Drift Check to CI](https://getrush.ai/run/add-migration-check-to-ci/drizzle/)
- [Make CI Fail When a Drizzle Schema Changes Without a Migration](https://getrush.ai/run/add-migration-drift-check/drizzle/)
- [Add Query Timeouts to a Drizzle and postgres.js Setup in Next.js](https://getrush.ai/run/add-query-timeouts/drizzle/)

## The same job on other stacks

- [Add Full-Text Search to a Mongoose API](https://getrush.ai/run/add-full-text-search/mongoose/)
- [Add Full-Text Search to a Prisma App](https://getrush.ai/run/add-full-text-search/prisma/)
- [Add Full-Text Search to a SQLModel and FastAPI App](https://getrush.ai/run/add-full-text-search/sqlmodel/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
