# Add Cursor Pagination to a SQLAlchemy API

Yes. An agent adds an after cursor to a Flask API that pages users with SQLAlchemy by page number, keeps the page mode, and proves both with unittest.

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

## What the agent does

The agent reads how the API pages today before changing the shape. In an app laid out like miguelgrinberg/microblog, `app/api/users.py` serves `GET /api/users`, `/api/users/<id>/followers` and `/api/users/<id>/following`, each reading `page` with a default of 1 and `per_page` with a default of 10 and a cap of 100, and each calling `User.to_collection_dict` with `sa.select(User)`, `user.followers.select()` or `user.following.select()`. That method lives on `PaginatedAPIMixin` in `app/models.py`: it calls `db.paginate(query, page=page, per_page=per_page, error_out=False)` and returns `items`, a `_meta` block with `page`, `per_page`, `total_pages` and `total_items`, and `_links` with `self`, `next` and `prev` built by `url_for`. `requirements.txt` pins `SQLAlchemy==2.0.23`, `Flask-SQLAlchemy==3.1.1` and `Flask==3.0.0`, and `tests.py` holds four `unittest` cases for the `User` model against an in-memory SQLite database, none of them for the API.

The mixin gains a second method, `to_cursor_dict(query, after, per_page, endpoint, **kwargs)`, and keeps the first. It orders the query by `User.id`, adds `.where(User.id > last_id)` when a cursor is given, fetches `per_page + 1` rows with `db.session.scalars`, and returns `items`, a `_meta` block with `per_page` and `has_more`, and `_links` with `self` and a `next` that carries `after` set to the last item's cursor, or `None` on the last page. There is no `prev` and no `total_items`, because a count is the query the cursor exists to avoid, and the pull request says so. The cursor is the id encoded with `base64.urlsafe_b64encode`, so clients treat it as opaque and a change of key later does not change the API; a cursor that does not decode to an integer answers the existing `bad_request` helper from `app/api/errors.py`.

The three routes read `after` beside `page`: a request that sends `after` gets the cursor method, and one that sends `page`, or nothing, gets the old answer unchanged, so the web client and any script that pages by number keep working. `user.followers.select()` and `user.following.select()` are selects on `User` through the `followers` association table, so the same `order_by(User.id)` and `where` apply to them without touching the relationship. The ids come from an autoincrement primary key, so ascending id is creation order, and no index is needed beyond the primary key.

Proof goes into `tests.py` beside the model cases, in a new `UserAPICase` that reuses `TestConfig` with its `sqlite://` database and no Elasticsearch. It creates twenty-five users with `db.session.add_all`, gets a token with `POST /api/tokens` under basic auth as the existing route allows, walks `/api/users?per_page=10` through three `_links.next` values and asserts twenty-five distinct ids in ascending order and a final `next` of `None`; walks one user's followers the same way; asserts `?page=2&per_page=10` still returns `total_items` and `prev`; and asserts a cursor of `nope` answers 400. `python tests.py` runs the four old cases and the new one.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add cursor pagination to the users endpoints of this Flask and SQLAlchemy API. Read app/api/users.py, app/api/__init__.py, app/api/auth.py, app/api/tokens.py, app/api/errors.py, app/models.py, tests.py and requirements.txt first. Add a to_cursor_dict method to PaginatedAPIMixin that orders by User.id, filters with id greater than the decoded cursor, fetches per_page + 1 rows and returns items, _meta with per_page and has_more, and _links with self and next carrying an after parameter, or None on the last page. Encode the cursor as url-safe base64 of the id and answer bad_request for one that does not decode. Keep to_collection_dict and the page and per_page behaviour unchanged; use the cursor method only when the request sends after. Apply it to /api/users, /api/users/<id>/followers and /api/users/<id>/following. Add a UserAPICase to tests.py that creates twenty-five users, gets a token through POST /api/tokens, walks the list and one user's followers by following next until it is None, checks page mode still returns total_items, and checks a bad cursor answers 400. Run python tests.py 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 `app/models.py`, `app/api/users.py` and `tests.py`, and touches no template, no migration under `migrations/` and nothing in `app/main/routes.py`, where the web pages keep using `db.paginate`. Its description pastes the `python tests.py` run with the new case named and the three `_links.next` values from the walk, so a reviewer sees that the cursor advanced and ended with `None`.

A client that sends `after` reads the user list without the two queries a numbered page costs, one of them a count over the whole table, and never sees a user twice when accounts are created between its requests. A client that sends `page` sees the same answer as before, including `total_items` and `prev`.

Before merging, a reviewer decides whether the web client in `app/main/` should move to the cursor too, and whether `per_page` in cursor mode should keep the cap of 100 or allow more, since a keyset page costs the same whatever its size.

## Run it on every Linear issue labelled agent

Label the issue that names the endpoints to convert and the sort key they should page on, and an automation in the Project with a `linear` trigger on `linear.issue.labeled` and the label `agent` starts one run per labelled issue, with the issue's text after the automation's prompt. The prompt asks for the same shape every time: a cursor method beside the page method, an opaque cursor, the old parameters untouched, and a test that walks every page. A run whose issue names an endpoint that already pages by keyset opens no pull request and says so on the issue.

Each run opens its own pull request that links the issue, so the person who labelled it reviews the diff and the test output there. The automation's `max_runs_per_hour` caps a burst of labels, and a label added twice is deduplicated on Linear's delivery id.

## Why run it on Rush?

Cursor pagination is a small change with one trap per layer: a sort key that is not unique, a cursor that exposes raw ids, a route that breaks the client still sending `page`. The agent reads the mixin, the three routes and the association table before writing, and its test walks every page through the real Flask client and the real SQLAlchemy query in its sandbox rather than asserting on a mocked session.

The run keeps every command and its output, so the reviewer reads the test run without creating a virtual environment or the Elasticsearch and Redis services the full app wants. Several APIs can be converted at once, each in its own sandbox, and one whose list already orders by a unique key gets only the cursor and the tests.

## Frequently asked questions

### Why not replace page and per_page?

Because `to_collection_dict` is public behaviour that scripts and the clients people built against it rely on, and because `total_items` has uses a cursor cannot serve. The cursor is added beside it, and a client chooses by sending `after` or `page`.

### Why order by id and not by last_seen or username?

`User.id` is unique and increases with creation, so a `where id > last` comparison is exact and needs no second column. `last_seen` changes on every request and `username` can be edited, and a cursor on a value that moves skips or repeats rows.

### Does this work for followers and following?

Yes. `user.followers.select()` and `user.following.select()` are `select(User)` statements joined through the `followers` table, so the same `order_by` and `where` apply. The test walks one user's followers to prove it.

### Is the base64 cursor a security feature?

No. It hides the id from casual reading and lets the key change later without breaking clients, and that is all. A decoded cursor is validated as an integer and refused with 400 when it is not.

## More SQLAlchemy jobs

- [Set Up GitHub Actions CI for a SQLAlchemy App](https://getrush.ai/run/add-github-actions-ci/sqlalchemy/)
- [Add Query Timeouts to a Flask and SQLAlchemy App](https://getrush.ai/run/add-query-timeouts/sqlalchemy/)
- [Add Slow Query Logging to a Flask + SQLAlchemy App](https://getrush.ai/run/add-slow-query-logging/sqlalchemy/)

## The same job on other stacks

- [Add Cursor Pagination to sqlc Queries](https://getrush.ai/run/add-cursor-pagination/sqlc/)
- [Add Cursor Pagination to a SQLModel API](https://getrush.ai/run/add-cursor-pagination/sqlmodel/)
- [Add Cursor Pagination to a Rust API With SQLx](https://getrush.ai/run/add-cursor-pagination/sqlx/)

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