All jobs

Add Cursor Pagination to a SQLModel API

Yes. An agent adds a cursor beside skip and limit on a FastAPI items list, keys it on created_at and id, returns next_cursor and proves it with pytest.

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 starts from the list route, not from a pagination library. In an app laid out like fastapi/full-stack-fastapi-template, backend/app/api/routes/items.py defines read_items with skip: int = 0, limit: int = 100, runs one select(func.count()).select_from(Item) for the total and one select(Item).order_by(col(Item.created_at).desc()).offset(skip).limit(limit) for the page, in two branches: a superuser reads every item and anyone else gets .where(Item.owner_id == current_user.id) on both statements. The answer is ItemsPublic(data=…, count=count) from backend/app/models.py, where Item has a uuid.UUID primary key made by uuid4 and a created_at that the migration fe56fa70289e_add_created_at_to_user_and_item.py added as a nullable timezone-aware column. That nullable column is the first thing the agent writes down: rows from before that migration can hold no timestamp, and a keyset that ignores them skips or repeats rows.

The change keeps skip and limit working and adds cursor: str | None = None. When a cursor is present the route ignores skip, orders by created_at descending with nulls last and then id descending, and filters with a tuple comparison on (created_at, id) against the values the cursor carries. The cursor itself is the base64 of the last row's timestamp and id, so a client treats it as opaque; a row with no created_at gets a cursor that carries only the id, and the comparison for that cursor falls back to id alone within the null group. ItemsPublic gains next_cursor: str | None = None, set when the query fetched limit + 1 rows, and count is still returned in both modes so the existing table header keeps its total. A cursor that does not decode, or decodes to the wrong number of parts, is refused with a 422 and a message that names the parameter, before any query runs. The same shape goes on read_users in users.py, which repeats the count-then-offset pattern.

Keyset reads need an index that matches the order, so the agent writes an Alembic revision under backend/app/alembic/versions/ adding one on (owner_id, created_at, id) for the owner branch, and leaves the primary key to serve the superuser branch. The frontend's frontend/src/routes/_layout/items.tsx calls ItemsService.readItems({ query: { skip: 0, limit: 100 } }) through the generated client in frontend/src/client/, so the agent reruns scripts/generate-client.sh, which dumps app.main.app.openapi() to frontend/openapi.json and runs the frontend's generate-client and lint scripts, so types.gen.ts learns cursor and next_cursor. The table keeps its first-100 query; a Load more button is a separate change and the pull request says so.

Proof is the existing suite in backend/tests/api/routes/test_items.py, where test_read_items creates two random items and asserts at least two come back. The agent adds a test that creates five items with create_random_item, walks the list with limit=2 following next_cursor until it is null, and asserts five distinct ids in creation order with no overlap; one that inserts an item with created_at set to None and checks it appears once, last; one for a malformed cursor; and one for the owner branch with a second user's items that must never appear. The sandbox has no Docker, so instead of the compose db service the run installs PostgreSQL with apt, points the backend's database settings at it, runs scripts/prestart.sh for the migrations and then backend/scripts/test.sh, which is coverage run -m pytest tests/, and ruff and mypy under the strict settings in backend/pyproject.toml.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add cursor pagination to the items list in this FastAPI and SQLModel backend. Read backend/app/api/routes/items.py, backend/app/api/routes/users.py, backend/app/models.py, the Alembic versions under backend/app/alembic/versions/, backend/tests/api/routes/test_items.py, backend/scripts/test.sh, scripts/generate-client.sh and frontend/src/routes/_layout/items.tsx first. Keep skip and limit working. Add an optional cursor query parameter: when present, order by created_at descending with nulls last then id descending, filter with a keyset comparison on (created_at, id), fetch limit + 1 rows and return next_cursor in ItemsPublic, keeping count. Encode the cursor as opaque base64 and refuse a malformed one with 422. Handle rows whose created_at is null without skipping or repeating them. Add an Alembic revision with an index on (owner_id, created_at, id). Regenerate the frontend client with scripts/generate-client.sh. Add pytest tests that walk pages with limit=2 and check no overlap, the null created_at case, a bad cursor and the owner filter. Install PostgreSQL in the sandbox, run scripts/prestart.sh, backend/scripts/test.sh, ruff and mypy, 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

The pull request changes backend/app/api/routes/items.py, backend/app/api/routes/users.py and backend/app/models.py, adds one revision under backend/app/alembic/versions/, adds four tests to backend/tests/api/routes/test_items.py, and updates frontend/openapi.json and the files under frontend/src/client/. Its description pastes the pytest run with the new tests named, the coverage report, and the ruff and mypy output, so a reviewer sees that the walk with limit=2 returned five distinct ids and that the null-timestamp row came back exactly once.

A client that sends skip and limit sees the same answer as before plus a next_cursor field it can ignore. A client that follows next_cursor never sees a row twice when items are created between its requests, which offset pagination cannot promise, and the database stops counting rows it will not return on every page.

Before merging, a reviewer checks the index revision against production's row count, since creating it locks the table on an older PostgreSQL unless the agent used postgresql_concurrently, and decides whether count should stay in cursor mode at all, because that one select(func.count()) is the query the cursor was meant to avoid.

Run it on every list endpoint that still pages by offset

A team with several FastAPI services sends this body once per repository to POST /o/{org}/sessions from a script, each with its own repository in resources and a request_id that is a UUID v5 of the repository's full name, so a retried call returns the run already started. Each run reads its own list routes: a service that already orders by a unique column gets a cursor on that column alone, one on SQLite in tests gets a tuple comparison rewritten as two predicates, and one with no generated client skips the regeneration step.

Each run opens its own pull request on its own branch. GET /o/{org}/sessions lists them newest first, and the key's webhook_url receives one run.completed per run with the pull request in result, so the person who started the batch reads every test run from one place.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

Cursor pagination is easy to get subtly wrong: a nullable sort column, a timestamp with duplicates, a cursor that leaks the raw values, an index that does not match the order. The agent reads the migration history and the test fixtures before writing, and proves the walk against a real PostgreSQL in its sandbox rather than against SQLite, where tuple comparison and null ordering behave differently.

The run keeps every command and its output, so the reviewer reads the pytest and mypy results without installing uv, PostgreSQL or the frontend toolchain. Several services can be converted at once, each in its own sandbox, and one whose list already uses a keyset gets only the tests.

Frequently asked questions

Will skip and limit keep working?

Yes. The route reads cursor first and uses skip only when no cursor is sent, so the frontend's readItems({ query: { skip: 0, limit: 100 } }) call and any script that pages by offset are unchanged. The pull request says which clients could move to the cursor and which can stay.

Why not sort by id alone?

The primary key is a random uuid4, so id order is not creation order and the list would stop reading newest first. The keyset uses created_at for the order and id only to break ties and to give rows without a timestamp a stable position.

Does this work with SQLite in tests?

The repository tests against PostgreSQL, through the compose db service in .github/workflows/test-backend.yml and a local PostgreSQL in the sandbox, and the tuple comparison runs there as written. If a project tests on SQLite the agent writes the comparison as two predicates, which both databases accept, and the page's tests still pass.

What is in the cursor?

The last row's created_at in ISO 8601 and its id, base64 encoded, nothing else. It is not signed, because the values are already visible in the response, and it is validated on every request so a hand-edited cursor answers 422 rather than an empty page.

More SQLModel jobs

The same job on other stacks

Keep reading