# 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 Rush](https://cloud.getrush.ai/run/add-cursor-pagination/sqlmodel/). Opens 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`

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

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

- [Add Database Indexes to a SQLModel App With Alembic](https://getrush.ai/run/add-database-indexes/sqlmodel/)
- [Add Email Verification to a FastAPI App on SQLModel](https://getrush.ai/run/add-email-verification/sqlmodel/)
- [Add Full-Text Search to a SQLModel and FastAPI App](https://getrush.ai/run/add-full-text-search/sqlmodel/)

## The same job on other stacks

- [Add Cursor Pagination to a Rust API With SQLx](https://getrush.ai/run/add-cursor-pagination/sqlx/)
- [Add Cursor Pagination to a tRPC Router](https://getrush.ai/run/add-cursor-pagination/trpc/)
- [Add Cursor Pagination to a TypeORM + NestJS API](https://getrush.ai/run/add-cursor-pagination/typeorm/)

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