Pagination, filtering and sorting: returning large result sets safely

A payment API that lists transactions must handle the case where the result is large: a corporate account may have 100,000 payments in a year, and returning all of them in one response would be slow, expensive and likely to time out. Pagination splits the result into pages, and the client requests one page at a time. The two common approaches are offset pagination and cursor pagination, and the choice matters for payments because the underlying data changes while the client is paging through it. Source: API design guidance; bank API developer documentation. Offset pagination uses a page number and a page size: GET /payments?offset=0&limit=50 returns the first 50 payments, and offset=50&limit=50 returns the next 50. It is simple to understand and easy to implement, but it has a flaw: if a new payment arrives between the first and second request, every payment after it shifts by one position, so the client either sees the same payment twice or misses one entirely. For a reconciliation process that must account for every payment, a missed payment is a break. Offset pagination is acceptable for a user interface where a missed row is harmless, but it is not acceptable for a process that must be complete. A business analyst must know which use case each endpoint serves before choosing the pagination style. Cursor pagination uses an opaque pointer to the last item the client saw: GET /payments?cursor=abc123&limit=50 returns the next 50 payments after the item identified by abc123. Because the cursor points to a specific item rather than a position, a new payment arriving in between does not shift the results: the client continues from where it left off and sees every payment exactly once. For a business analyst, the requirement is to define this behaviour in the API specification, because the client builds against the specification and nothing else. The cursor is opaque, meaning the client must not try to interpret it; it simply passes back the value the server returned. The server typically encodes the sort key and the identifier of the last item in the cursor, so it can resume the query efficiently. Cursor pagination is the correct choice for payment listings, transaction history and any endpoint where completeness matters. The API specification must state that the cursor is opaque and that clients must not construct or modify it. Filtering and sorting let the client ask for a subset. A corporate treasury system that wants to reconcile Friday's supplier payments asks for GET /payments?from=2026-11-06&to=2026-11-06&status=ACCC, and receives only the payments that settled on that day. The API must validate the filter values: a from date after the to date returns a 400 error with a clear message, and an unknown status value returns a 400, not an empty list, so the client knows it asked for something wrong. Sorting is specified with a parameter such as sort=bookingDate:desc, and the API must state the default sort order when the client does not specify one, because a client that assumes chronological order and receives reverse chronological order will reconcile incorrectly. For a business analyst, the requirement is to define the filter parameters, the validation rules for each, the sort options, and the default sort, and to test each combination. Sorting by a field that is not unique can produce unstable results. If a client sorts payments by booking date and two payments have the same booking date, the order of those two payments is undefined, and it may change between requests. The client that pages through the results may then see the same payment twice or miss one. The fix is to sort by a unique key, or by a combination of fields that is unique, such as booking date and then payment identifier. The API specification must state the sort key, and the server must apply it consistently. A business analyst who writes a requirement for a payment listing endpoint must specify the sort key, the default sort, and the behaviour when two items have the same sort value, because these details are what make the endpoint reliable for reconciliation. The API documentation must state this rule plainly, because a client that has to guess will guess wrong. Filtering by status needs a defined code set and a defined behaviour for unknown values. If a client asks for payments with status SETTLED, and SETTLED is not a code the API uses, the API must return a 400 error with a code such as INVALID_STATUS, not an empty list. An empty list tells the client there are no settled payments, which is wrong; a 400 tells the client it asked for something the API does not understand, which is true. The same applies to date filters: a from date in the future, or a to date before the from date, must return a 400 with a clear message, not an empty list. For a business analyst, the requirement is to define the filter parameters, the code sets for each, the validation rules, and the error returned for each invalid combination. Further reading: API design guidance; ISO 20022 camt.052 and camt.053 account reporting message definitions. Last reviewed 7 November 2026.