All jobs

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 RushOpens 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 Fields 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

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

The same job on other stacks

Keep reading