# Add Full-Text Search to a SQLModel and FastAPI App

Yes. An agent adds a Postgres tsvector column and GIN index to your SQLModel table, a search parameter on the list route, a migration and tests, and opens a PR.

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

## What the agent does

The agent reads the model and the route that would carry the search. In a FastAPI app laid out like fastapi/full-stack-fastapi-template, `backend/app/models.py` defines `Item` with a `title` of at most 255 characters and an optional `description` of the same length, both plain `Field`s on a `SQLModel` with `table=True`. `backend/app/api/routes/items.py` serves `GET /items/` through `read_items`, which takes `skip` and `limit`, runs one `select(func.count())` and one `select(Item)` ordered by `created_at` descending, and scopes both to `owner_id` unless the caller is a superuser. There is no filter of any kind, so a user with many items finds one by paging. `backend/pyproject.toml` pins `sqlmodel` at `>=0.0.46`, `psycopg[binary]` at `>=3.3.6` and `alembic` at `>=1.19.1`, and `compose.yml` runs `postgres:18`, so Postgres full-text search is available with no new dependency.

It adds the search as a generated column rather than a query-time expression. `Item` gets a `search_vector` field declared with `sa_column` as a `TSVECTOR` column computed from `to_tsvector('english', title || ' ' || coalesce(description, ''))` and stored, so the vector is maintained by Postgres on every insert and update, and a GIN index on it. The migration is written with `alembic revision --autogenerate` from `backend/`, where `alembic.ini` lives and `app/alembic/env.py` points `target_metadata` at `SQLModel.metadata` with `compare_type=True`; the agent checks the generated file against the five existing revisions under `app/alembic/versions`, which chain from `e2412789c190` through `fe56fa70289e`, and hand-writes the computed column and the index where autogenerate does not.

The route gets a `q: str | None = None` parameter. When it is set, both the count statement and the page statement add a `where` on `Item.search_vector.op('@@')(func.websearch_to_tsquery('english', q))`, and the page is ordered by `func.ts_rank` of the vector against the same query before `created_at`, so the best match comes first. `ItemsPublic` keeps its shape, with `data` and `count`, so the frontend's generated client at `frontend/src/client` keeps compiling; the agent runs `scripts/generate-client.sh`, which dumps `app.main.app.openapi()` to `openapi.json` and regenerates the client, so `ItemsService.readItems` in `frontend/src/routes/_layout/items.tsx` can pass `q`.

Tests go beside the eleven in `backend/tests/api/routes/test_items.py`. The existing `create_random_item` in `tests/utils/item.py` uses random lowercase strings for titles, which no query would match, so the agent creates items with known titles and asserts that a two-word query returns the item that has both words and not one that has one, that a superuser sees matches across owners and a normal user only their own, and that the `count` matches the page. It installs PostgreSQL in the sandbox, points `DATABASE_URL` at it, runs `alembic upgrade head`, then `bash scripts/test.sh`, which runs pytest under `coverage` and reports; `uv run ruff check`, `mypy` under the `strict` setting in `pyproject.toml`, and the `coverage report --fail-under=90` step from `.github/workflows/test-backend.yml` complete the checks.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add full-text search to the items list in this FastAPI and SQLModel app. Read backend/pyproject.toml, backend/app/models.py, backend/app/api/routes/items.py, backend/app/core/db.py, backend/app/alembic/env.py, the files under backend/app/alembic/versions, backend/tests/conftest.py, backend/tests/api/routes/test_items.py, backend/tests/utils/item.py, backend/scripts/test.sh, scripts/generate-client.sh, compose.yml and .github/workflows/test-backend.yml first. Add a stored tsvector column named search_vector to Item, computed from title and description with the english configuration, and a GIN index on it; write the Alembic migration with alembic revision --autogenerate from backend/ and complete by hand whatever it leaves out. Add an optional q query parameter to GET /items/ that filters both the count and the page with websearch_to_tsquery and orders matches by ts_rank, keeping the owner scoping and the ItemsPublic shape. Regenerate the frontend client with scripts/generate-client.sh. Add tests with known titles that check matching, non-matching, owner scoping and count. Install PostgreSQL in the sandbox, run alembic upgrade head, bash scripts/test.sh, ruff, mypy and coverage report --fail-under=90, and open a pull request with the output.",
  "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 with one new migration under `backend/app/alembic/versions`, the `search_vector` field in `backend/app/models.py`, the `q` parameter in `backend/app/api/routes/items.py`, new tests in `backend/tests/api/routes/test_items.py`, and the regenerated files under `frontend/src/client`. The description shows the SQL the migration runs, the query the route builds for a sample search, and the pytest and coverage output.

The run's record keeps the `alembic upgrade head` run against the sandbox's Postgres and the `EXPLAIN` the agent runs on the search query, so a reviewer sees the GIN index used rather than reads that it would be.

Before merging, a reviewer decides two things the pull request names: whether `english` is the right text search configuration for the data, since stemming differs per language, and whether the migration can run as written on the production table, since a stored generated column is computed for every existing row when it is added.

## Run it on every Linear issue labelled agent

Search requests arrive as issues, one list at a time. With Linear connected under Settings → Connections in the console, add an automation to the Project with a `linear` trigger on `linear.issue.labeled` and the label `agent`. Its saved prompt asks for this without the table name: read the model and route the issue names, add a stored tsvector and index, filter the list with `websearch_to_tsquery`, write the migration and the tests, and open a pull request. The trigger adds the issue after the prompt and fills in no placeholder, so which table and which columns come from the issue text.

The trigger fires only when someone in your Rush org adds the label, and a change made by an app integration never fires it. A burst of labels is held to the automation's `max_runs_per_hour`. From your own server, the same run starts with this request and the issue URL in `resources`, with a `request_id` that is a UUID v5 of the issue id so a retried start returns the run already begun.

## Why run it on Rush?

Search is the change that is easy to write and hard to prove: the query that looks right returns the wrong rows for a two-word search, or skips the index and scans the table. The agent proves it in its own sandbox with a real Postgres, running the migration, loading rows with known titles, running the search and the `EXPLAIN`, and the record keeps all of it beside the diff.

The same body runs against every SQLModel service that needs a search, each in its own sandbox with its own Postgres and its own pull request, and the result for each is the same shape: a migration, a parameter, tests, and the output that shows they pass.

## Frequently asked questions

### Why a stored column and not to_tsvector in the WHERE clause?

A `to_tsvector` call in the query is recomputed for every row on every search and cannot use a plain index. A stored generated column is computed once per write, Postgres keeps it current, and the GIN index on it makes the search an index lookup. The cost is the one-time computation over existing rows when the migration runs.

### Does the frontend need to change?

The agent regenerates the TypeScript client so `readItems` accepts `q`, which is what `scripts/generate-client.sh` exists for. Wiring a search box into `items.tsx` is a separate change; the pull request says so and leaves the page as it is unless the issue asks for it.

### Will the owner scoping still hold?

Yes. The search condition is added with `and` to the existing `owner_id` condition in the non-superuser branch, and the tests include a user who searches for a word that appears only in another user's item and gets nothing.

### What about the 90% coverage floor?

The workflow fails under 90% coverage, so the agent runs the same `coverage report --fail-under=90` after the tests and includes its output. New route code comes with tests for each branch, which is what keeps the figure up.

## More SQLModel jobs

- [Add Optimistic Locking to SQLModel Updates](https://getrush.ai/run/add-optimistic-locking/sqlmodel/)
- [Add Refresh Tokens to a FastAPI App on SQLModel](https://getrush.ai/run/add-refresh-tokens/sqlmodel/)
- [Add Seed Data to a FastAPI SQLModel App](https://getrush.ai/run/add-seed-data/sqlmodel/)

## The same job on other stacks

- [Add Full-Text Search to a Supabase App](https://getrush.ai/run/add-full-text-search/supabase/)
- [Add Full-Text Search to an Axum API](https://getrush.ai/run/add-full-text-search/axum/)
- [Add Full-Text Search to a Drizzle and PostgreSQL App](https://getrush.ai/run/add-full-text-search/drizzle/)

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