Add Cursor-Based Pagination to Settlement and Event Listing Endpoints

[therealjhay]

Category: New Features & Improvements
Difficulty: High

Description:
The /api/events endpoint (indexer, line 235) and /api/settlements endpoints use offset-based pagination (limit + offset). Offset pagination has known problems for large datasets:

• Skipped/duplicate results when rows are inserted or deleted between pages
• Performance degradation: OFFSET 10000 LIMIT 50 still scans 10,050 rows
• Inconsistent total counts under write-heavy workloads (the total may change between pages)

Cursor-based pagination using indexed columns (initiatedAt, ledger, indexedAt) should be added as an alternative, following common patterns (e.g., Relay GraphQL Cursor Connections Spec).

Location:
services/indexer/src/index.ts:235–260, services/settlement-engine/src/index.ts:320–341, services/api-gateway/src/index.ts:772–796

Acceptance Criteria:

• Add cursor-based pagination params to PaginationQuery in shared/validation/schemas.ts: cursor (opaque string), after (ISO date), first (int, replaces limit)
• Implement a encodeCursor(row) / decodeCursor(cursor) pair using base64 encoding
• Add cursor pagination support to the indexer's GET /api/events
• Add cursor pagination support to the settlement engine's GET /api/settlements
• Add cursor pagination support to the gateway's GET /api/settlements
• Keep offset pagination as a fallback for backward compatibility
• Return pageInfo: { hasNextPage, endCursor } in paginated responses
• Write tests for cursor pagination with insertions during pagination

Technical Notes:

• For cursor pagination with initiatedAt, use WHERE initiatedAt < :cursorDate ORDER BY initiatedAt DESC LIMIT :first.
• Ensure the column used for cursor has a database index.
• Encode cursor as base64(JSON.stringify({ id, date })).

[emmysly699]

This is a solid, well-scoped problem . The offset pagination issues you've outlined (row skipping/duplication on writes, the OFFSET scan cost at scale, unstable total counts) are exactly what cursor pagination is meant to solve, and the Relay Cursor Connections pattern you've referenced is the right model to follow here.
My approach: start with the shared layer first, extending PaginationQuery in shared/validation/schemas.ts with cursor, after, and first, then building the encodeCursor/decodeCursor pair using base64-encoded JSON of { id, date } as specified. Getting that foundation solid before touching the three endpoints avoids drift between them.
From there, I'll roll it out to the indexer's GET /api/events, the settlement engine's GET /api/settlements, and the gateway's GET /api/settlements using the WHERE initiatedAt < :cursorDate ORDER BY initiatedAt DESC LIMIT :first pattern you've outlined, with the existing indexed columns (initiatedAt, ledger, indexedAt). Offset pagination stays intact throughout for backward compatibility, and each paginated response will return pageInfo: { hasNextPage, endCursor } as required.
For testing, I'll specifically cover the insertion-during-pagination case mentioned in the acceptance criteria, since that's the exact failure mode cursor pagination is meant to fix , want to actually verify it holds under that condition rather than just testing the happy path.
Given the scope across three services plus the shared validation layer, I'd estimate 5-6 days for a properly tested implementation.

[shepherd-001]

Hello, I have reviewed the issue and am confident I can fix it. Please assign this task to me.

[Ajibose]

I would love to work on this

[grantfox-oss]

🦊 GrantFox — @Ajibose has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #259)
2. Your PR will be reviewed by the Betta-Pay maintainers

Good luck! Track your progress on GrantFox.