All jobs

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

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

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

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

The same job on other stacks

Keep reading