[Backend] Keyset Pagination with Snapshot-Consistent Totals for List Endpoints Under Concurrent Writes

[ogazboiz]

Contributor coordination and questions: https://t.me/+DOylgFv1jyJlNzM0

Task Classification: Correctness defect - pagination window instability and total-count drift
Affected Layers: backend, frontend
Affected Paths: backend/src/lib/pagination.ts (new), backend/src/routes/*, backend/src/services/eventIndexer.ts, backend/migrations/, frontend/app/, frontend/hooks/, .github/workflows/ci.yml, .github/workflows/loadtest.yml
Severity: Medium
Estimated Window: 24-40 hours

Technical Context & Monorepo Integration Failure

List endpoints (GET /api/loans, GET /api/remittances, GET /api/events) page with OFFSET/LIMIT and derive footer totals from a separate COUNT(*). The backend writes to the same tables continuously: eventIndexer.ts inserts rows as it drains Soroban getEvents, and defaultChecker.ts mutates loan rows on settlement. Offset windows are positional, so any insert or delete between two page fetches shifts every subsequent row index. The COUNT(*) runs in a different snapshot from the page query, so the total diverges from the set actually delivered.

A single-layer fix is not sufficient. The backend must change its query shape and its response contract. The frontend infinite-scroll consumer reads page and passes an offset today; it must carry an opaque cursor and a pinned snapshot instead. Changing one side without the other breaks end-detection or reintroduces drift.

Concurrent-write failure walkthrough:

1. Client requests GET /api/remittances?offset=0&limit=50 ordered created_at DESC. Server returns rows at positions 0-49 and a footer total = 500 from COUNT(*).
2. eventIndexer.ts ingests a remittance_minted event and inserts one row with the newest created_at, at the top of the sort.
3. Client requests offset=50&limit=50. The inserted row pushed the window down by one, so the row previously at position 49 now sits at position 50 and is returned again. Result: a duplicated row across pages.
4. Symmetric case: defaultChecker.ts removes a settled row between requests. The window pulls up by one and the row at position 50 is never returned. Result: a skipped row.
5. The page-2 COUNT(*) now reads 501 while the client accumulated its set against 500. The footer total no longer equals the sum of delivered rows, and infinite-scroll end-detection misfires.

Core Component Invariants & Code Paths

Smart Contract Infrastructure
No Soroban or Rust changes. Pagination is a read-path concern over indexed events. The durable ordering source remains the on-chain tuple (ledger_sequence, tx_index, event_index) emitted by loan_manager and remittance_nft via env.events().publish (events loan_opened, remittance_minted, loan_settled). Invariant: eventIndexer.ts must map that tuple to a monotonic ingestion key so the backend cursor preserves contract emission order and never reorders a previously ingested row.

Backend/API Layer
Add a monotonic seq BIGINT GENERATED ALWAYS AS IDENTITY column to loans, remittances, and ledger_events, backfilled in (created_at, id) order. Create composite indexes, for example idx_remittances_seek ON remittances(created_at DESC, seq DESC). Add backend/src/lib/pagination.ts exporting encodeCursor(), decodeCursor(), and buildKeysetClause(). Route handlers replace OFFSET/LIMIT with the seek predicate:

WHERE seq <= $snapshot_seq
  AND (created_at, seq) < ($cursor_created_at, $cursor_seq)
ORDER BY created_at DESC, seq DESC
LIMIT $limit + 1

The first request pins snapshot_seq = MAX(seq). Totals compute as COUNT(*) WHERE seq <= $snapshot_seq, so rows inserted after the scroll began are excluded from both the window and the total. Fetching limit + 1 yields next_cursor and end-detection without a second round trip. Response contract for every list endpoint:

{ "items": [...],
  "page": { "next_cursor": "<opaque base64url|null>",
            "snapshot_seq": 148223,
            "total_at_snapshot": 4021,
            "limit": 50 } }

Invariants: no OFFSET in any list query; cursors are opaque base64url of (created_at, seq); a malformed cursor returns HTTP 400 with code INVALID_CURSOR; limit is clamped by PAGINATION_MAX_LIMIT (default 100).

Frontend Client
Convert the offset consumer to TanStack useInfiniteQuery in frontend/hooks/useInfiniteList.ts: getNextPageParam: (last) => last.page.next_cursor ?? undefined, with snapshot_seq threaded on every subsequent request. The cursor stays opaque; the client never parses it. The SSE consumer must not splice live rows into the paginated snapshot mid-scroll, which would break cursor stability. New SSE rows accumulate into a separate "new items" banner and are merged only on refetch. Read the API base from NEXT_PUBLIC_API_BASE_URL. Invariant: rendered lists contain no duplicated or missing keys across page boundaries during active writes.

Root Configuration & Orchestration
No root workspace exists, so no orchestrator change. The migration-check job (service postgres:16) must run the new migration up and down. Because backend and frontend define the response shape independently, add a checked-in response-shape contract at docs/pagination-contract.md referenced by both layers to bound drift. Extend loadtest.yml with a deep-pagination scenario that walks a full cursor chain under concurrent inserts. .github/workflows/ci.yml job wiring is unchanged; the three isolated jobs (backend, migration-check, frontend) must remain green.

Verification & Acceptance Criteria

• Backend compiles from backend/: npm run build and npm run typecheck pass. Frontend compiles from frontend/: npm run build and npm run typecheck pass. contracts/ builds unchanged.
• Migration up and down both succeed against postgres:16 in the migration-check job; backfill leaves seq non-null and strictly ordered.
• Unit tests: encodeCursor/decodeCursor roundtrip, INVALID_CURSOR rejection, buildKeysetClause predicate shape.
• Integration test: a harness inserts and deletes rows between page fetches and asserts zero skipped and zero duplicated rows, and total_at_snapshot constant across the full chain.
• End-to-end Playwright (test:e2e): infinite scroll to list end under a concurrent writer, no duplicate keys, banner surfaces post-snapshot rows.
• PR attachments: EXPLAIN ANALYZE for each list query showing an index scan on the seek index and no sequential scan; before/after query plans; a state dump of page responses captured during concurrent writes.

Suggested Execution Path

Phase 0 - Ingestion ordering alignment (2-4h). Confirm eventIndexer.ts assigns ingestion order from (ledger_sequence, tx_index, event_index). Deliverable: documented seq derivation. Exit check: mapping preserves contract emission order with no Rust change.

Phase 1 - Schema and migration (4-6h). Add seq identity columns and composite seek indexes; backfill; author the down migration. Deliverable: migration pair plus docs/pagination-contract.md. Exit check: migration-check up/down green and EXPLAIN shows an index scan.

Phase 2 - Backend pagination and contract (8-12h). Add pagination.ts, refactor handlers to the seek predicate, pin snapshot_seq, compute total_at_snapshot, return INVALID_CURSOR. Deliverable: updated endpoints and unit tests. Exit check: concurrency integration test passes with stable totals.

Phase 3 - Frontend consumer (6-10h). Move to useInfiniteQuery carrying the opaque cursor and snapshot; route SSE rows to the banner. Deliverable: updated hook, components, and types. Exit check: Playwright e2e passes.

Phase 4 - CI, loadtest, docs (4-8h). Add the deep-pagination loadtest.yml scenario and finalize the contract doc. Deliverable: CI and loadtest updates. Exit check: backend, migration-check, and frontend jobs green and the loadtest scenario completes.

[Jayking40]

Hi @ogaboiz,

I would love to take on this issue to implement keyset pagination with snapshot-consistent totals under concurrent writes across the list endpoints. As an experienced full-stack engineer with 3,200+ GitHub contributions, I have extensive experience optimizing database queries, building resilient API pagination logic, and maintaining data consistency under high concurrent write loads.

My Proposed Approach:

1. Keyset (Cursor) Pagination Implementation: I'll update the list query logic to replace legacy offset pagination with deterministic keyset ordering (created_at, id), utilizing encoded cursors (next_cursor) for efficient, stable traversal without missing or duplicate items during concurrent mutations.
2. Snapshot-Consistent Totals: To ensure totals remain consistent across pages under concurrent writes, I'll implement a snapshot metadata/caching mechanism (or transactional evaluation depending on the database driver) so total count snapshots remain locked per pagination session.
3. Automated Tests & Regression Prevention: I will write targeted unit/integration tests to simulate concurrent write conditions during paginated requests, verifying zero data drift, accurate cursor boundary handling, and proper total count preservation.

Professional Profile:

• GitHub: @Jayking40
• ETA: 24 hours from assignment

I’m ready to dive into the codebase immediately. Could you please assign this issue to me?

Thanks!

[whitezaddy]

I have experience and have reviewed the code; I can submit a PR within 2 days.

[OTimileyin]

Hi manintainer,
The root failure is that OFFSET pagination is positional, any insert or delete between page fetches shifts the window, causing skipped or duplicated rows, and the separate COUNT(*) runs in a different snapshot from the page query.

The strategy to resolve this is to replace OFFSET/LIMIT with a seek predicate pinned to snapshot_seq = MAX(seq) via a new monotonic identity column on the three tables, so the window and total_at_snapshot always compute against the same snapshot and concurrent writes can't cause skips or duplicates. Frontend moves to TanStack useInfiniteQuery with an opaque cursor, SSE rows routed to a separate banner.

Full coverage: migration up/down, cursor unit tests, concurrency integration test, Playwright e2e, and EXPLAIN ANALYZE plans attached.

[aboisamcrypto]

Hi ser! I'm a Full-Stack Developer and I'm interested in working on this issue. I've reviewed the requirements and I'm confident I can complete it successfully. Please assign me. Thanks!

[grantfox-oss]

🦊 GrantFox — @aboisamcrypto 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 #1384)
2. Your PR will be reviewed by the LabsCrypt maintainers

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Official Campaign | FWC26 campaign!

@aboisamcrypto's PR #1447 was approved and merged by @ogazboiz.

🏆 @aboisamcrypto: You earned 35 FoxPoints for this contribution! Your current tier: Explorer (153 total points). Track your full progress on GrantFox.

👏 Great work, @aboisamcrypto! Keep contributing to LabsCrypt.