Designing Stable Pagination for Financial Data APIs

Pagination looks simple until the underlying data changes while a client is reading it.

Imagine an application displaying a user's financial transactions. The client requests the first 20 records, then requests the next 20. If a new transaction is inserted between those requests, the second page may contain a duplicate record—or skip one entirely.

For ordinary lists, this can be an annoying UI bug. For financial data, inconsistent results can undermine reconciliation, reporting, and user trust.

This article explores offset pagination, cursor pagination, deterministic ordering, consistency boundaries, and practical tests for data APIs.

1. Why Pagination Is Harder Than It Looks

A basic implementation might use SQL LIMIT and OFFSET:

SELECT transaction_id, created_at, amount
FROM transactions
WHERE account_id = :account_id
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

This works for small datasets, but it has two important limitations.

First, ordering by created_at alone may not be deterministic. Multiple records can share the same timestamp.

Second, offset pagination identifies a page by its position rather than by the last record already seen. Inserts and deletes can shift those positions.

A reliable API needs to define both its ordering and its consistency expectations.

2. Make Ordering Deterministic

Always use a unique tie-breaker when the primary sort field is not unique.

For example:

SELECT transaction_id, created_at, amount
FROM transactions
WHERE account_id = :account_id
ORDER BY created_at DESC, transaction_id DESC
LIMIT 20;

The transaction_id tie-breaker makes the order deterministic, assuming it is unique.

This solves ambiguity when timestamps match, but it does not fully solve pagination drift caused by records being inserted or deleted between requests.

3. Use Keyset Pagination for Large, Ordered Datasets

Keyset pagination uses the last record from the previous page as a continuation point.

Suppose records are ordered by:

created_at DESC, transaction_id DESC

After returning a page, the API can use the final record's timestamp and ID to retrieve older records.

SELECT transaction_id, created_at, amount
FROM transactions
WHERE account_id = :account_id
  AND (
    created_at < :last_created_at
    OR (
      created_at = :last_created_at
      AND transaction_id < :last_transaction_id
    )
  )
ORDER BY created_at DESC, transaction_id DESC
LIMIT 20;

The first request omits the continuation predicate. Subsequent requests provide both cursor values.

This approach avoids scanning and skipping an increasingly large offset. It also makes page boundaries less sensitive to newly inserted records that sort ahead of the cursor.

The query assumes that created_at is non-null and that the ordering fields behave consistently. Production implementations should define how null values, mutable sort keys, and deleted records are handled.

4. Treat the Cursor as an API Contract

A cursor should represent the exact continuation position—not merely a page number.

A conceptual cursor payload might look like this:

{
  "version": 1,
  "created_at": "2026-10-21T09:30:00Z",
  "transaction_id": "txn_8f31",
  "filter_hash": "example-filter-fingerprint"
}

This is an illustrative structure, not a complete production token.

A production API should consider the following:

• Encoding: Encode the cursor into a URL-safe representation.
• Integrity: Sign the cursor or use another mechanism to detect tampering.
• Validation: Reject malformed or unsupported cursor versions.
• Filter binding: Ensure a cursor is not accidentally reused with incompatible filters or sort orders.
• Authorization: Re-check access permissions on every request. A cursor must never grant access by itself.
• Privacy: Avoid placing sensitive financial information in a readable cursor.

Signing a cursor protects its integrity, but it does not encrypt its contents. If the payload contains sensitive information, use an appropriate protected representation or store continuation state server-side.

5. Define What Consistency Means

Cursor pagination does not automatically provide a complete snapshot of a dataset.

Consider a transaction feed that changes while a client navigates through it. New records may appear before the current cursor, while updates or deletions may affect records not yet retrieved.

Different products need different guarantees.

Live feed

A dashboard may prioritise recent activity. Each request can query the latest committed data, accepting that the feed changes over time.

Stable export

A downloadable report may need to represent a consistent dataset. Depending on the database and export requirements, this could involve a snapshot transaction, a materialised set of record IDs, or a versioned dataset.

Reconciliation job

A reconciliation process may need a defined cutoff, deterministic ordering, retry-safe processing, and a way to identify missing or duplicated records.

Choose the guarantee explicitly. Do not describe ordinary cursor pagination as snapshot isolation unless the implementation actually provides it.

Long-running database transactions can have operational costs, so large exports may require a different design from an interactive feed.

6. Build a Response That Supports Continuation

A typical response might look like this:

{
  "data": [
    {
      "transaction_id": "txn_8f31",
      "created_at": "2026-10-21T09:30:00Z",
      "amount": 1250
    }
  ],
  "page": {
    "next_cursor": "opaque-cursor-value",
    "has_more": true
  }
}

The example uses a simplified amount field. A real financial API should define currency, decimal precision, sign conventions, and whether amounts are represented as integer minor units or decimal strings.

For example, storing ₹1,250.75 as 125075 paise can avoid binary floating-point rounding issues, provided the currency and unit are explicitly documented.

The API should also specify what happens when no records remain. A common design returns has_more: false and a null next cursor.

7. Test Pagination as a Sequence of Requests

Testing a single page is not enough. The important behaviour emerges across multiple requests.

A useful test suite should cover:

1. Empty datasets.
2. Fewer records than the page limit.
3. Exactly one full page.
4. Multiple records with identical timestamps.
5. Inserts between page requests.
6. Deletes between page requests.
7. Invalid or tampered cursors.
8. Cursors reused with different filters.
9. Permission changes between requests.
10. Repeated requests using the same cursor.

For a static dataset, a basic property test is that concatenating all pages should produce every expected record exactly once.

def collect_all_pages(fetch_page):
    cursor = None
    collected = []

    while True:
        response = fetch_page(cursor)
        collected.extend(response["data"])

        if not response["page"]["has_more"]:
            break

        next_cursor = response["page"]["next_cursor"]
        assert next_cursor is not None
        assert next_cursor != cursor

        cursor = next_cursor

    return collected

This is a small test helper, not a complete pagination client. In production code, also enforce a maximum number of requests and handle network errors, retries, and repeated cursors safely.

For a static fixture, compare the collected IDs with the expected ordered IDs:

expected_ids = [row["transaction_id"] for row in expected_rows]
actual_ids = [row["transaction_id"] for row in collected_rows]

assert actual_ids == expected_ids

For a changing dataset, define the expected consistency model first. Otherwise, a test may incorrectly classify valid live-feed behaviour as a defect—or overlook a genuine gap in a supposedly stable export.

8. Observability Matters

Pagination failures can be difficult to diagnose from a generic HTTP 200 response.

Consider recording metrics such as:

• Invalid cursor rate
• Average and maximum page-query latency
• Empty-page rate
• Repeated-cursor detection count
• Export completion and failure rates
• Records processed per export job

Avoid logging raw cursor contents if they can reveal internal identifiers or sensitive information. Prefer safe metadata, request IDs, and appropriately controlled diagnostic fields.

These measurements can help distinguish slow database queries from client retry loops, malformed cursors, or unexpectedly changing datasets.

Conclusion

Reliable pagination requires more than adding LIMIT to a query.

Use deterministic ordering, choose offset or keyset pagination based on the workload, validate continuation tokens, and define what happens when the dataset changes between requests.

Most importantly, make consistency guarantees explicit. A live transaction feed and a financial export may both use pagination, but they do not necessarily need the same semantics.

Designing these details deliberately makes APIs easier to test, operate, and trust.

---

Disclosure: This article was prepared with AI assistance and should be reviewed and validated by the author before publication.