[Feature] Implement Cursor-Based Pagination for Large Dataset Endpoints

[KarenZita01]

Description

The offset-based pagination implemented in Issue #17 works well for small to medium datasets, but becomes inefficient for large datasets (millions of records) because offset pagination requires scanning and skipping rows, leading to performance degradation on higher page numbers. This issue implements cursor-based pagination (also known as keyset pagination) as an alternative pagination strategy for endpoints that deal with large, frequently-updated datasets.

Cursor-based pagination uses a unique, sequential cursor (typically a timestamp, UUID, or auto-incrementing ID) to mark position in the dataset. Instead of ?page=2&limit=20, clients use ?cursor=<lastItemId>&limit=20 to request the next page. This approach offers: consistent results even when new items are inserted (no page drift), O(1) performance regardless of page depth, and better support for real-time data feeds.

The implementation should: support both forward and backward pagination (via ?cursor and ?before parameters), include the cursor value in response metadata ({ data: [...], pagination: { cursor, hasNext, hasPrev, nextCursor, prevCursor } }), and encode the cursor as a base64 URL-safe string to prevent manipulation and allow including multiple sort values. The cursor format should be opaque to clients — they should only pass it back as received.

For the initial implementation, the cursor-based pagination should be available as an alternative to offset pagination, selectable via ?paginate=cursor or ?paginate=offset query parameter. The default remains offset-based for backward compatibility.

Technical Context & Impact

• Dependencies: No new external dependencies. Uses built-in Buffer for base64 encoding. Relies on the data access layer (Issue [Refactor] Implement Repository Pattern for Clean Data Access Layer Abstraction #22) for sorted data retrieval.
• Architecture: Extend src/utils/pagination.js (from Issue [Feature] Create Pagination, Filtering, and Search Utilities for All List Endpoints #17) with cursor-based functions. New functions: encodeCursor(value), decodeCursor(cursor), paginateCursor(data, { cursor, limit, sortBy }). The response format changes slightly to include cursor metadata instead of page/totalPages.
• Impact: Essential for endpoints that serve large, append-heavy datasets (meter readings, audit logs, webhook delivery logs). Without cursor-based pagination, these endpoints will become slower over time as data accumulates.

Step-by-Step Implementation Guide

1. Create Cursor Utilities: In src/utils/pagination.js, add encodeCursor(value) — converts a value (or JSON object with multiple values) to a base64 URL-safe string. decodeCursor(cursor) — decodes back to the original value. Handle invalid cursor formats gracefully (return null for malformed input).
2. Implement Forward Cursor Pagination: Write paginateCursorForward(data, { cursor, limit, sortBy, sortOrder }) — return items after the cursor position. For in-memory arrays, find the cursor index and slice. For database-backed implementations, this would use WHERE id > cursor or WHERE createdAt > cursor.
3. Implement Backward Cursor Pagination: Write paginateCursorBackward(data, { cursor, limit, sortBy, sortOrder }) — return items before the cursor position (for "previous page" functionality).
4. Update Response Format: Create a response formatter that includes cursor metadata: { data, pagination: { cursor: nextCursor, hasNext, hasPrev, prevCursor } }. Note that total is intentionally omitted for cursor-based pagination (computing total is expensive for large datasets) — it can be included optionally.
5. Add Query Parameter Selection: In list endpoints, add support for ?paginate=cursor to switch pagination mode. Parse cursor, before, and limit query parameters. Validate that cursor is provided when using cursor pagination mode.
6. Apply to High-Volume Endpoints: Convert the meter readings list endpoint and webhook delivery logs to support cursor-based pagination alongside offset-based.
7. Write Tests: Create tests/unit/cursorPagination.test.js testing: encode/decode roundtrip, forward pagination with sequential IDs, backward pagination, empty datasets, single-page datasets, edge cases (cursor at start, cursor at end, invalid cursor format).

Verification & Testing Steps

1. Call a list endpoint with ?paginate=cursor&limit=5 — verify the response includes pagination.cursor (base64 string) and pagination.hasNext.
2. Decode the cursor value (using Buffer.from(cursor, 'base64url').toString()) — verify it contains the last item's sort field value.
3. Use the returned cursor in the next request: ?paginate=cursor&cursor=<value>&limit=5 — verify the next 5 items are returned correctly with no overlap.
4. Test backward pagination using ?paginate=cursor&before=<cursor>&limit=5 — verify items before the cursor are returned.
5. Insert new items while paginating forward — verify that cursor pagination does not skip or duplicate items (no page drift), unlike offset pagination.
6. Pass an invalid/non-decodable cursor — verify a 400 error with a clear message about invalid cursor format.