The API pagination decision record: why the pagination model you chose determines your consistent-snapshot failure surface and your cursor opacity contract

A 30-person analytics SaaS company built a data explorer that let customers browse and export all of their reports — data transformation job outputs that could run into the tens of thousands for large enterprise customers. The API was designed to return results in pages of 100, with standard offset-based pagination: the caller specified a page number, and the server computed OFFSET (page - 1) * 100 LIMIT 100 against the reports table. The implementation was straightforward and matched the pattern in the team's HTTP API framework documentation. An enterprise customer with 12,000 reports built an automated overnight workflow that iterated all 120 pages via the API, downloaded the results, and pushed them to an internal billing system that invoiced based on the reports' computed totals.

The analytics platform ran a nightly maintenance job that scanned the reports table for duplicate and stale records and deleted them. The deletion job was not new — it had run without incident since the product launched. Three months after the paginated export workflow was deployed, the nightly deletion job and the customer's overnight export ran simultaneously for the first time. The export began at 11:58 PM. At 12:03 AM, the deletion job began and removed 340 duplicate records. The export was on page 32 at that moment, having fetched rows 1–3,200. The 340 deletions removed records distributed across the first 5,000 rows — all before the export's current position.

When the export fetched page 33, the server executed OFFSET 3200 LIMIT 100. The deletions had shifted every row after position 2,860 backward by 340 positions in the offset ordering. Rows that had been at positions 3,201–3,340 were now at positions 2,861–3,000 — already returned in pages 29–32. Rows that had been at positions 3,541–3,640 were now at positions 3,201–3,300 — the export would process them again on pages 33–36, having already returned them in what the caller believed were pages 36–39 of the pre-deletion dataset. The export finished at 12:47 AM with 12,000 records returned — the same count as before, because 340 records were duplicated to compensate for 340 skipped records. The API responses were well-formed. The HTTP status codes were all 200. The response bodies were valid JSON with the expected page structure. The billing system received 12,000 records and invoiced normally. Three days later, the customer's finance team reconciled the invoices against their internal ledger and found 340 duplicate invoice line items — each corresponding to a report that had been processed twice in the same nightly run.

The incident investigation required reconstructing the overlap between the deletion job's execution window and the export's page-fetching timeline from server-side logs. The root cause was identified: the offset pagination model provided no consistent-snapshot guarantee, and the concurrent deletion had shifted the offset boundary mid-export. The fix required switching the export endpoint to cursor-based pagination — WHERE id > :cursor ORDER BY id LIMIT 100 — which provides consistent traversal regardless of insertions or deletions at positions before the cursor. The offset-based endpoint was retained for the human-navigated UI, where occasional out-of-order items are tolerable. The founding session that picked offset pagination for the export endpoint had not documented the consistent-snapshot behavior of the model under concurrent mutation, because the failure mode is invisible during testing: a test that runs a paginated export against a static dataset finds no gaps or duplicates regardless of which pagination model is used.

A 20-person B2B compliance SaaS company built an audit log export API for enterprise customers — a paginated endpoint that returned all audit events for a customer's organization in reverse chronological order. The engineering team had read that cursor-based pagination was the correct approach for large result sets and implemented it. The cursor was the base64 encoding of the last-fetched event's row ID: base64("audit_events:" + row_id). The first page returned a next_cursor of YXVkaXRfZXZlbnRzOjEwMDA= (decoding to audit_events:1000). The API documentation noted that callers should treat the cursor as opaque and not attempt to construct cursors manually. The note was in a paragraph at the bottom of the pagination section, not in the OpenAPI schema and not enforced by the server.

Enterprise customers integrated the export into their SIEM pipelines. One customer's integration engineer, inspecting the API responses with a debugging proxy, decoded the cursor and discovered the pattern. Rather than storing the cursor from each API response and passing it to the next request, the integration script was rewritten to construct cursors directly: the script maintained a local integer counter and encoded it as the cursor for each request. The approach worked correctly for that customer's data because their integration always started from row 0 and traversed forward. The script was open-sourced in the customer's public GitHub repository as a reference integration for other teams building similar pipelines.

Six months after the API launched, a security researcher audited the public integration script and noticed the cursor construction pattern. The researcher tested whether a cursor encoding another organization's row ID would be accepted by the API. The test confirmed the bypass: the server executed WHERE id > :cursor AND org_id = :calling_org ORDER BY id DESC LIMIT 100, but the cursor value :cursor was the decoded row ID, not validated against the calling organization's row ID range. A cursor constructed for row ID 1 (which belonged to the first organization ever created on the platform) returned audit events from that organization's log when called with any customer's authentication token. The platform had row-level tenant isolation on the main result set but not on the cursor validation step — the cursor was assumed to be a value issued by the API for the calling tenant's dataset, and no check verified that assumption. The researcher disclosed the vulnerability privately; it was patched within 48 hours by adding an AND EXISTS (SELECT 1 FROM audit_events WHERE id = :cursor AND org_id = :calling_org) validation step before executing the pagination query. The fix closed the cross-tenant access path.

Fourteen months after the API launched, the team migrated from PostgreSQL integer sequence primary keys to UUID v4 primary keys for horizontal sharding. The cursor encoding changed from base64(audit_events:1000) to base64(audit_events:550e8400-e29b-41d4-a716-446655440000). Every existing integration that constructed cursors directly — including the reference integration and 23 customer pipelines built from it — broke immediately on the migration date. The API returned HTTP 400 for any cursor that failed to parse as a valid UUID after the audit_events: prefix. Support tickets arrived from 23 customers within 6 hours of the migration. The fix for the customers was to rewrite their integrations to use the cursor values returned by the API rather than constructing them. The fix for the API was to add server-side cursor validation that returned a descriptive error rather than a generic 400 when a cursor was malformed, and to add HMAC signing to new cursors so that the server could detect and reject manually constructed cursors at the validation step before executing the query. The founding cursor pagination session had documented "cursor = base64(table_name:row_id)" in a design comment. It had not documented the opacity requirement — that the cursor encoding must not be predictable from the response, and that callers must not be permitted to construct valid cursors without the server's signing key — because the violation of opacity was a gradual process: the documentation said "treat as opaque," but the server accepted synthesized cursors without complaint, and the reference integration demonstrated the non-opaque pattern before the security implication was visible.

A 25-person e-commerce company built a product catalog API that returned products in newest-first order for their storefront and for internal tooling that generated price comparison exports. The original implementation used offset-based pagination, which the team replaced with keyset pagination after reading that keyset was more performant at scale. The keyset query used the created_at timestamp as the sort key: WHERE created_at < :cursor ORDER BY created_at DESC LIMIT 50, with the cursor set to the created_at value of the last product on each page. The implementation was tested with a dataset of 80,000 products and performed correctly: pages loaded in under 20 milliseconds, the traversal was stable, and a full export of all 80,000 products produced a file with exactly 80,000 rows. The pagination was deployed to production.

Eight months after the keyset pagination was deployed, the merchandising team prepared for a seasonal flash sale. The preparation involved bulk-importing 3,200 new products from a supplier catalog. The import script inserted all 3,200 products in a single database transaction with a single NOW() call for all created_at values — all 3,200 products received the identical timestamp 2026-03-14 09:17:22.441329. An internal pricing tool ran a full export of the product catalog immediately after the import completed, to verify that all 3,200 products had been imported correctly before publishing them to the storefront.

The export started at page 1 (products with created_at less than now) and iterated forward. At page 180 of the 80,000-product catalog, the cursor value was 2026-03-14 09:17:22.441329 — the exact timestamp shared by all 3,200 newly imported products. The keyset query for page 181 was WHERE created_at < '2026-03-14 09:17:22.441329' ORDER BY created_at DESC LIMIT 50. The condition excluded all rows with created_at equal to the cursor timestamp. All 3,200 products with that exact timestamp were excluded by the strict less-than comparison. Page 181 returned the next 50 products from before the flash sale import. The export continued and completed with 80,000 rows — the 3,200 new products were excluded and the export was padded by the next 3,200 products in the pre-import catalog to maintain the row count. No error was raised. The tool's validation check compared the export row count to the expected count of products in the database at the time of export, but used a cached count from before the import completed, so the discrepancy was not detected.

The merchandising team published the storefront with the new products. The flash sale launched at 10 AM. Customers searching for sale items found them on the storefront's search results (powered by a real-time index, not the paginated export), but the pricing comparison tool — which generated the sale's advertised price comparisons from the export data — did not include any of the 3,200 new products. Customer support received complaints from customers who could not find the advertised sale prices in the comparison tool. The investigation identified the keyset pagination gap within 2 hours; the fix was to extend the cursor to include a tie-breaking column: WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 50. The compound comparison includes all rows with created_at strictly less than the cursor timestamp, plus all rows with the same cursor timestamp whose id is strictly less than the cursor ID — capturing all rows in the equal-timestamp batch except those already returned. A composite index on (created_at DESC, id DESC) was added to support the compound comparison efficiently. The founding keyset pagination session had documented "sort by created_at descending, cursor = last created_at value." It had not documented the uniqueness requirement for the sort key or the tie-breaking column, because the failure mode — a batch of rows with identical timestamps at a page boundary — was absent from the test dataset, where each product had been inserted individually with a distinct timestamp at least 1 millisecond apart.

Structural properties set by the pagination decision

Three structural properties are determined when a team decides how to paginate a collection endpoint. None appear explicitly in the sessions that add pagination to an API — they are the operational consequences of choices made under the pressure of shipping a working API quickly, where "working" means the paginated response returns the correct count of results in the correct order under the test conditions that happen to not involve concurrent mutations, synthesized cursors, or equal-valued sort keys at page boundaries.

Property 1: The pagination model and the consistent-snapshot surface. A pagination model defines the traversal mechanism: how the server determines which rows belong to page N given the caller's position in the result set. Offset-based pagination uses a count of skipped rows as the position: OFFSET N LIMIT M. The position is computed relative to the current state of the dataset at query time. If the dataset changes between page requests — rows inserted before the current position push subsequent rows forward, rows deleted before the current position pull subsequent rows backward — the offset position no longer corresponds to the same set of rows it addressed on the previous page. The skipped rows change without the caller or the server detecting it. This is not a bug in the query execution — offset pagination is specified to skip N rows from the current dataset state, not from the dataset state at the time the traversal began. It is correct behavior for a model that makes no consistent-snapshot guarantee.

Cursor-based pagination uses the identity of the last-fetched row as the position: WHERE id > :cursor LIMIT M. The position is a specific row boundary in the dataset's identity space, not a count of skipped rows. Insertions and deletions before the cursor position do not change which rows come after the cursor — the rows with IDs greater than the cursor ID are the same regardless of how many rows at lower IDs have been inserted or deleted since the cursor was issued. This provides consistent forward traversal: a caller that fetches page by page will eventually see every row that existed at the cursor position or was inserted after the cursor was issued, without duplicating any row, as long as the cursor position is not deleted. If the cursor row itself is deleted, the query still returns the correct next page — the deleted row's ID is still a valid position boundary in the ID space, even if the row no longer exists. The caching strategy decision record documents the interaction between pagination and cache invalidation: offset-paginated responses cannot be cached reliably because the same offset returns different rows after any mutation, while cursor-paginated responses can be cached with a TTL because the same cursor returns the same rows until the cache expires.

Keyset pagination generalizes cursor pagination to arbitrary sort orders. The cursor encodes the values of all sort columns at the last-fetched row. The query uses a compound comparison to identify the boundary: for ascending sort on (col_a, col_b), the condition is WHERE (col_a, col_b) > (:last_col_a, :last_col_b). Keyset pagination has the same consistent-snapshot property as cursor pagination — the boundary is a position in the value space, not a count of skipped rows. It adds the sort key uniqueness requirement: the compound comparison partitions the result set into before-boundary and after-boundary rows, which requires that the sort key values uniquely identify a position. If two rows have identical values for all sort columns, the compound comparison does not produce a deterministic partition — both rows are at the same boundary position, and the strict greater-than comparison either includes both or excludes both, depending on which side of the comparison they fall. The tie-breaking column is the mechanism that makes the keyset boundary deterministic: the primary key (or any globally unique column in the result set) added as the final sort column and the final cursor field ensures that no two rows share the same compound sort key value. The database indexing strategy decision record documents the composite index requirements for keyset pagination: the index must cover all sort columns in the query's ORDER BY clause in the same order and direction, because the query planner uses the index for both the compound comparison filter and the sort, and a missing or mis-ordered index produces a full table scan on each page request.

Property 2: The cursor design and the opacity contract. A cursor encodes the pagination position — the boundary in the result set that divides already-fetched rows from not-yet-fetched rows. The cursor's encoding determines whether callers can decode, modify, and re-encode it to construct cursors for arbitrary positions. A cursor is non-opaque when its encoding reveals the position values it encodes, allowing callers to infer the structure and construct valid cursors without the API's involvement. Common non-opaque encodings: base64 of a readable string (trivially decodable), a URL query parameter (the position values are in the URL), a numeric page number (the position is explicit), a composite string of readable values joined by colons or pipes.

The opacity contract specifies that the cursor is an opaque token — a value that encodes a position without revealing the position's structure to the caller. The contract has two implications. The security implication: a non-opaque cursor encoding only the row ID provides no tenant isolation guarantee — a caller who knows the ID space can construct cursors for rows belonging to other tenants and use the pagination API to traverse another tenant's result set, bypassing the row-level access control that applies to the first page. The authorization model decision record documents the row-level access control model; the cursor design must embed or validate the tenant context in the cursor to make cross-tenant cursor construction impossible or detectable. A signed cursor (HMAC-SHA256 of the position values and the tenant ID, with the signature appended to the cursor) achieves opacity by requiring the server's signing key to produce a valid cursor — the caller cannot modify the position values or the embedded tenant ID without invalidating the signature, and the server rejects cursors with invalid signatures before executing the query.

The migration implication: a non-opaque cursor encoding the underlying storage identifier (an integer primary key, a UUID v4, a composite of table name and row ID) ties the cursor format to the storage schema. Callers who construct cursors rather than using API-returned cursors build an implicit dependency on the storage schema that is invisible to the API team. When the storage schema changes — a migration from integer to UUID primary keys, a change from row ID to composite cursor fields, a switch from one sort column to two — all constructed cursors break, while all API-returned cursors would be valid if they had been stored and replayed. The API versioning decision record documents the breaking change model; changing the cursor format is a breaking change to the pagination contract even if the request and response schemas are unchanged, because callers who constructed cursors from a predictable format can no longer construct valid cursors after the format changes. Cursor opacity prevents this dependency from forming: callers who store and replay API-returned cursors are insulated from storage format changes because the server can issue new-format cursors while still accepting old-format cursors during a migration window.

Property 3: The sort key uniqueness and the tie-breaking model. Keyset pagination produces a deterministic page partition if and only if the sort key is unique within the result set. Uniqueness here means that no two rows in the result set have identical values for all columns in the ORDER BY clause — the compound sort key (col_a, col_b, ..., tiebreaker_id) forms a total order over the result set with no ties. A total order is required because the boundary comparison partitions rows into exactly two groups: those before the cursor and those after it. A partial order (where ties exist) produces an ambiguous partition at the tie: rows sharing the cursor value may be on either side, depending on the database's arbitrary row ordering for equal sort keys. SQL does not guarantee a deterministic ordering for rows with equal sort key values unless all sort columns together are unique — the presence of ties means the page boundary is undefined for those rows, and different executions of the same query may return different subsets of the tied rows on different pages.

The tie-breaking column resolves the ambiguity by extending the sort key to include a column that is guaranteed to be unique within the result set. The primary key is the natural choice — it is unique by definition and is available on every table. The composite ORDER BY becomes ORDER BY sort_col ASC, id ASC (or DESC for reverse order), and the cursor encodes both (last_sort_col, last_id). The compound comparison for the next page boundary is WHERE sort_col > :last_sort_col OR (sort_col = :last_sort_col AND id > :last_id) — a row is after the boundary if its sort column is strictly greater than the cursor's sort value, or if its sort value is equal to the cursor's but its ID is strictly greater. This comparison partitions the tied rows correctly: all rows with the same sort value as the cursor are partitioned by ID, so exactly the tied rows with IDs greater than the cursor ID appear on subsequent pages, and the tied rows with IDs less than or equal to the cursor ID were already returned. The database partitioning decision record documents the pagination model for partitioned tables; keyset pagination across partitions requires that the tiebreaker column is unique across all partitions, not just within each partition — a per-partition sequence that restarts from 1 on each partition is not a valid tiebreaker for cross-partition keyset pagination.

What the founding session records and what it omits

The founding pagination session typically records the chosen model (offset, cursor, or keyset), the page size limit (25, 50, or 100), and the response format (next_page, next_cursor, or has_more). It may record the rationale for choosing cursor over offset (performance at large offsets, avoiding full table scans for deep pages) or the rationale for choosing keyset over cursor (support for arbitrary sort orders beyond primary key). What it does not record is the consistent-snapshot guarantee — whether the model provides consistent traversal under concurrent insertions and deletions, and for which use cases (human-navigated UI vs. machine-readable export) inconsistency is acceptable versus impermissible. It does not record the cursor opacity contract — whether the cursor encoding is opaque, how opacity is enforced, what tenant context is embedded in the cursor for access control, and how the cursor format will be migrated when the storage schema changes. It does not record the sort key uniqueness requirement — whether the ORDER BY columns together form a total order, what tiebreaker column is used when the primary sort column is non-unique, and what composite index is required to make the keyset query efficient.

The omissions are consequential for different failure modes. The absence of a consistent-snapshot specification produces silent data skips or duplications during machine-readable exports that run concurrently with background mutations — the failure mode is invisible during testing (test datasets are static), invisible during production operation (API responses are always well-formed), and visible only when downstream consumers detect data integrity failures, at which point reconstructing the export-vs-mutation overlap requires log archaeology. The absence of a cursor opacity contract produces either a security vulnerability (non-opaque cursor allows cross-tenant traversal) or a migration fragility (constructed cursors break on storage schema changes) — the security failure appears when an adversary deliberately probes the API, the migration fragility appears on the first storage format change and affects the callers who were implicitly depending on the predictable format. The absence of a sort key uniqueness specification produces silent gaps at page boundaries when rows are inserted with equal sort values — the failure mode is absent from test data where timestamps are always distinct, and present in production data where batch imports or high-throughput concurrent writes frequently produce equal timestamps.

The pagination decision record does not need to be exhaustive. It needs to answer four questions: which pagination model is used for which endpoint category (offset for human-navigated UI, cursor or keyset for machine-readable exports and analytical traversals), what consistent-snapshot guarantee does the model provide and which use cases require that guarantee, what is the cursor opacity contract and how is it enforced at the server, and what sort key uniqueness invariant does the keyset implementation rely on, including the tiebreaker column and the composite index that makes the compound comparison efficient. Four answers written down in the founding session avoid the duplicate invoice export, the cross-tenant audit log access, and the silent flash sale product gap that each trace back to a pagination property that was not specified in the implementation that chose the model.

The WhyChose decision extractor finds the founding pagination sessions in your ChatGPT and Claude export — the "should we use cursor or offset pagination?" thread, the "how should we encode the cursor?" conversation, the "what should we sort by?" research session. It extracts the decision and the trade-off that was actually considered, not the surrounding API design discussion that buries the pagination model choice in thirty messages about HTTP status codes and response envelope formats.

The five ADR sections for a pagination decision

Section 1: Pagination model selection — offset, cursor, and keyset, and when each applies. Specify the pagination model for each endpoint category. Endpoint categories are distinguished by their consumer type and their data integrity requirements. Human-navigated endpoints — UI list views, paginated search results, dashboards — are consumed by a human who sees one page at a time, tolerates occasional item reordering as the dataset updates, and does not depend on the traversal being complete or non-duplicate. Offset-based pagination is acceptable for this category. Machine-readable export endpoints — full dataset exports, analytics traversals, integration sync operations — are consumed by automated processes that expect a complete, non-duplicate traversal of the dataset. Offset-based pagination is not acceptable for this category. Cursor or keyset pagination is required.

Cursor-based pagination (WHERE id > :cursor ORDER BY id LIMIT N) is the correct default for machine-readable exports when the result set is ordered by primary key. It provides consistent traversal under insertions and deletions at positions before the cursor, has linear query cost (uses the primary key index), and requires no composite index beyond the primary key. Keyset pagination is the correct choice when the result set must be ordered by a non-primary-key column (created_at, updated_at, a business-key field). It has the same consistent-traversal properties as cursor pagination but requires a composite index on the sort columns plus the tiebreaker and requires a compound comparison in the WHERE clause. Specify which model is used on each endpoint in the API, the justification for the choice, and the consistent-snapshot guarantee provided: "this endpoint uses cursor pagination and provides consistent forward traversal — rows inserted or deleted before the cursor position after the cursor is issued are excluded from subsequent pages, and rows inserted after the cursor position are included when their position is reached." This guarantee must be documented in the API's public documentation if external callers need to rely on it for their data integrity guarantees.

Specify the endpoint's behavior at the boundaries of consistency: what happens if the caller's cursor row is deleted before the cursor is used; what happens if a row is inserted between the cursor position and the next page's first row (it will appear on the next page, which the caller may or may not expect); what happens if the result set is empty (no rows match, the first page returns an empty list and a null next_cursor). Specify the maximum page size: the largest N the server will accept. The maximum page size is a rate limiting surface — a caller who requests N = 10,000 on an unindexed endpoint can cause a full table scan and a multi-second response. The API rate limiting decision record documents the rate limiting model; the pagination maximum page size is enforced at the server (requests with N above the maximum receive a 400 with a descriptive error) and documented in the API schema. A sensible default is N ≤ 1,000 for indexed keyset queries, N ≤ 100 for complex queries with joins. Specify the default page size if the caller omits N.

Section 2: Cursor design — opacity, encoding, tenant context, and migration model. Specify the cursor encoding format. The encoding must be opaque: callers must not be able to decode the cursor to extract position values, modify the position values, and re-encode a valid cursor. Three opaque encoding models:

Signed token: the cursor payload is the JSON-serialized position values (e.g., {"created_at": "2026-03-14T09:17:22Z", "id": "abc123"}) plus the tenant ID. The payload is HMAC-SHA256 signed with a server-side secret and base64url-encoded with the signature appended. The server validates the signature before executing the pagination query and rejects cursors with invalid signatures with a 422 response. The signing key is rotated on a schedule; old cursors signed with previous keys are accepted for a migration window equal to the maximum cursor lifetime. Signed tokens are stateless (no server-side storage), expire implicitly when the signing key is rotated or when the caller discards the cursor, and are auditable (the server can log the cursor payload on validation failure for debugging without exposing the signing key).

Encrypted blob: the cursor payload is AES-GCM encrypted with a server-side key. Callers cannot decode the payload. The server decrypts and validates the cursor before executing the query. Encryption is appropriate when the position values themselves are sensitive (e.g., if the position value reveals the number of rows in the dataset). Encryption adds decryption overhead on every page request; signed tokens are sufficient for non-sensitive position values and have lower overhead.

Server-side opaque handle: the cursor is a random UUID stored in a server-side cache (Redis, Memcached) that maps the UUID to the position values. The server looks up the UUID in the cache and retrieves the position values. Handles expire with the cache TTL; callers who use a cursor after the TTL receive a 404 or 410 response. Server-side handles add state management overhead and a cache lookup on each page request but are the most opaque encoding — the cursor value contains no information about the position at all. Handles are appropriate for highly sensitive pagination positions or for APIs that need to guarantee cursor expiry without key rotation.

Specify the tenant context embedded in the cursor: the tenant ID (organization ID, customer ID, or equivalent partition key) must be included in the cursor payload for signed or encrypted cursors, and the server must validate that the cursor's embedded tenant ID matches the calling tenant's identity before executing the query. Specify the cursor lifetime: the maximum duration a cursor issued by the API is valid. Cursors for human-navigated endpoints should expire within the browser session or within 1–24 hours; cursors for large dataset exports should remain valid for the expected export duration (hours, not days). The API contract testing decision record documents cursor opacity testing: the test suite must verify that a cursor issued for tenant A is rejected when used by tenant B, and that a cursor with a tampered payload is rejected with a 422 rather than executing the query with the tampered position values.

Specify the cursor migration model: how the cursor format will change when the storage schema changes (primary key type migration, sort column change, additional sort columns added). For signed or encrypted cursors, the migration model is key rotation with a backward-compatible acceptance window: old cursors signed with the previous key are accepted for the window duration, and new cursors are issued with the new key or new payload format. For server-side handles, the migration model is TTL-based expiry: old handles expire, callers receive 404, and must re-start pagination from the first page with a new cursor. Document the migration model in the ADR so that the storage migration team knows what cursor lifecycle management is required during the migration. The API schema design decision record documents backward-compatible evolution principles; cursor format changes are a versioning concern even if they do not change the request or response schema visible to callers.

Section 3: Sort key and tie-breaking specification for keyset pagination. Enumerate the sort columns for each keyset-paginated endpoint. For each sort column, specify whether it is unique within the result set. If the sort column is not unique (a timestamp, a status field, a category, any non-primary-key column), specify the tiebreaker column: the primary key or another globally unique column appended as the final sort column. Document the composite index required: for a two-column sort of (created_at DESC, id DESC), the required index is a composite covering index on (created_at, id) both descending, with created_at as the leading column so the index supports both the sort and the compound comparison filter. A covering index that includes the columns needed by the SELECT clause eliminates the heap lookup on each page.

Specify the compound comparison SQL for the tiebreaker. For ascending sort on (created_at, id), the next-page boundary condition is WHERE (created_at > :last_created_at) OR (created_at = :last_created_at AND id > :last_id). For descending sort on (created_at, id), the condition is WHERE (created_at < :last_created_at) OR (created_at = :last_created_at AND id < :last_id). Some databases support the row value comparison syntax WHERE (created_at, id) > (:last_created_at, :last_id) which the query planner may handle more efficiently than the equivalent OR condition; verify the query plan for both forms and specify the form that produces an index range scan rather than a filter scan. Document any cases where the result set may contain NULL values in the sort columns and specify how NULLs are handled (SQL standard: NULLs sort last in ascending order, first in descending order in most databases; specify the explicit NULLS FIRST or NULLS LAST clause and include NULLs in the boundary comparison if they can appear at page boundaries).

Specify the page boundary invariant in testable form: for any two consecutive pages fetched by the same caller with the same filter parameters, no row ID that appears in page N appears in page N+1, and no row that existed at the time of the cursor issue and satisfies the filter is absent from the complete traversal. This invariant is testable as an integration test: generate a dataset with known contents, run a full export with the paginated API, verify that the set of returned row IDs is identical to the set of IDs in the dataset. Run the test with a dataset that includes rows with identical sort key values at expected page boundaries (a batch of rows inserted with the same timestamp, with enough rows to span multiple pages) to verify that the tiebreaker handles the equal-sort-value case correctly.

Section 4: Page size limits and the rate limiting surface. Specify the maximum page size for each endpoint. The maximum page size is the rate limiting surface for database-heavy pagination queries — a caller who requests all rows in one page (N = total row count) bypasses the pagination mechanism entirely and causes a full index scan with the result set loaded into memory. The maximum page size should be set based on the query's expected execution time at the maximum N: a keyset query on an indexed column with a 50-millisecond p99 at N = 100 will have a proportionally higher execution time at N = 1,000 due to the larger result set transferred from the database and serialized into JSON. Measure execution time and memory allocation at maximum N under realistic dataset sizes and traffic conditions before setting the limit. A common approach: set the maximum page size at 2–5× the default page size, where the default page size is chosen to produce a response time under 200 milliseconds p95.

Specify the server-side enforcement: requests with a page size parameter exceeding the maximum receive an HTTP 400 response with a body that includes the maximum allowed page size and a link to the pagination documentation. Requests without a page size parameter receive the default page size. Specify whether the server silently clamps the page size to the maximum or returns an error — silent clamping is user-friendly but can cause confusion when the caller expects to receive N rows and receives fewer; an explicit error forces the caller to handle the limit explicitly. The API rate limiting decision record documents whether page-size-limited requests count against the caller's rate limit at the requested page size or the actual returned page size — a caller who requests N = 10,000 and receives a 400 should not consume rate limit budget proportional to 10,000 rows.

Specify the deep-page behavior for offset-based endpoints: the query execution time for OFFSET N LIMIT 100 grows linearly with N as the database scans past N rows to reach the offset position. For large datasets, deep pages (N > 10,000) may produce multi-second query times that exceed the API's timeout budget. Specify the maximum permitted offset — requests with offset above the maximum receive a 400 response with a recommendation to use cursor-based traversal for complete dataset access. Document that cursor-based traversal has O(1) page cost (constant query cost per page regardless of the page number, because the index range scan starts from the cursor position rather than from the beginning of the dataset). The observability strategy decision record documents deep-page latency alerting: a histogram of pagination query execution times by page depth (offset range or cursor age) enables detection of callers who are fetching deep offset pages and experiencing multi-second response times without triggering the timeout.

Section 5: Pagination observability — consistency monitoring, cursor validation metrics, and deep-page alerting. Specify the metrics required to make the pagination model observable. Three categories of pagination metrics are relevant to different failure modes.

Consistency monitoring: for machine-readable export endpoints, instrument the export completion rate (fraction of exports that complete all pages without receiving an error or cursor expiry response) and the export row count distribution (the distribution of total rows returned per completed export). An export that returns fewer rows than expected indicates a silent skip due to concurrent mutations or a cursor that was not advanced correctly. The expected row count may not be known at the start of the export (the dataset may change during the export), but a significant mode in the distribution at values below the known dataset size is a signal that skipping is occurring. Instrument the cursor reuse rate: the fraction of page requests where the cursor was issued more than a configured duration ago (e.g., more than 1 hour). A high rate of old cursor reuse indicates callers who store cursors for long periods and may encounter cursor expiry or data staleness at the cursor position.

Cursor validation metrics: instrument the cursor validation failure rate — the fraction of paginated requests where the server rejects the cursor as invalid (malformed, expired, invalid signature, or tenant ID mismatch). A nonzero tenant ID mismatch rate is a signal of cross-tenant cursor construction and should trigger an immediate security review. A high malformed cursor rate is a signal that callers are constructing cursors rather than using API-returned cursors and will break on the next storage format change. Instrument cursor format version distribution when the cursor format is versioned during migration: the fraction of requests using old-format versus new-format cursors determines when the old-format acceptance window can be closed.

Deep-page alerting: for offset-paginated endpoints, instrument query execution time by offset range. Alert when the p95 execution time for offset range N > threshold exceeds the API's target response time budget. This alert identifies callers who are fetching deep offset pages and experiencing latency degradation without encountering an error — they are successfully receiving responses but with latency that may exceed their client timeout. The correct remediation is to direct the caller to the cursor-based export endpoint. The background job infrastructure decision record documents the interaction between background jobs and paginated exports; background jobs that mutate the dataset while an export is running are the source of offset pagination inconsistency, and the observability model must include a metric that correlates export inconsistency events with background job execution windows to establish causation when an inconsistency is reported.

Further reading

• The API versioning decision record — cursor format changes are a breaking change to the pagination contract even when the request and response schemas are unchanged; the versioning model must cover cursor format migration alongside field and endpoint changes.
• The API rate limiting decision record — page size is the rate limiting surface for paginated endpoints; the maximum page size must be set based on measured query execution time at maximum N, and requests above the maximum must consume rate limit budget at a controlled rate.
• The API schema design decision record — cursor design as a schema decision; the cursor type in the OpenAPI schema must be declared opaque (string with format: cursor or an equivalent extension) to discourage callers from parsing the cursor value.
• The caching strategy decision record — offset-paginated responses cannot be cached reliably under concurrent mutations; cursor-paginated responses can be cached with a TTL equal to the cursor lifetime, reducing database load for repeated page requests.
• The database indexing strategy decision record — keyset pagination requires a composite index on the sort columns in the ORDER BY clause; a missing or mis-ordered composite index causes a full table scan on each page request, defeating the performance advantage of keyset over offset pagination.
• The authorization model decision record — cursor opacity is a security boundary; a non-opaque cursor encoding only the row ID is a row-level access control bypass; the tenant ID must be embedded in the cursor payload and validated against the calling tenant's identity before executing the pagination query.
• The API contract testing decision record — pagination contract testing must verify cursor opacity (a cursor issued for tenant A is rejected when used by tenant B), page size enforcement (requests above the maximum page size receive 400), sort key uniqueness (a dataset with equal sort key values at a page boundary produces a complete traversal with no gaps or duplicates), and end-of-results signal (a page with fewer than N rows correctly indicates the end of the result set).
• The observability strategy decision record — deep-page latency alerting, export consistency monitoring, cursor validation failure rate as a security signal, and cursor format version distribution during migration must be instrumented as first-class metrics on paginated endpoints.
• The database partitioning decision record — keyset pagination across partitioned tables requires a tiebreaker column that is globally unique across all partitions; a per-partition sequence that restarts from 1 is not a valid tiebreaker for cross-partition keyset pagination and produces duplicate position values at partition boundaries.
• WhyChose decision extractor — finds the founding "should we use offset or cursor pagination?" and "how should we encode the cursor?" sessions in your ChatGPT or Claude export and extracts the decision and the trade-offs that were actually weighed, without the surrounding API design discussion that buries the pagination model choice in thirty messages about response envelope formats and HTTP status codes.