Migrate offset pagination to stable keyset cursors

[lamenting-hawthorn]

Problem

Several session/turn list operations use opaque page tokens backed by SQL/in-memory offsets. Deep offset pagination becomes increasingly expensive because stores must scan and discard preceding rows, and concurrent inserts can shift page boundaries.

Adding an arbitrary maximum offset would be a breaking product-policy choice rather than a durable fix: legitimate clients eventually hit the ceiling and previously issued deep tokens become invalid.

Proposed direction

Move the affected list operations to stable keyset/cursor pagination using an existing ordering key and deterministic tie-breaker. Preserve the existing opaque page_token HTTP contract so clients do not depend on cursor internals.

Design questions

• Which resources should migrate first: sessions, turns, or both?
• What ordering tuple should each cursor encode to remain stable across equal timestamps?
• Must previously issued offset tokens remain valid during a transition?
• Should previous-page navigation remain supported, and what reverse-query contract should stores use?
• Can the existing session-event cursor implementation serve as the canonical pattern?

Acceptance criteria

• Query cost does not grow linearly with page depth.
• No duplicates or omissions under inserts between page requests for the documented ordering semantics.
• In-memory, SQLite, and Postgres stores pass the same pagination contract tests.
• Tokens remain opaque and malformed tokens fail deterministically.
• Any migration/backward-compatibility behavior is documented before implementation.

[saanidhi-git]

Hi @lamenting-hawthorn, I would love to take this issue on!

I have experience managing backend database queries and optimizing APIs using Python and frameworks like FastAPI, and I’m very familiar with transitioning from offset to keyset pagination across different storage backends (Postgres/SQLite/In-memory).

Regarding your design questions, here are my initial thoughts on how we could approach this:

Which to migrate first: I suggest starting with sessions to establish and test the pattern thoroughly, then mirroring it for turns.

Ordering tuple: Using (created_at, id) (or the equivalent timestamp/UUID fields) is likely the safest bet to ensure stability across identical timestamps.

Token encoding: We can base64 encode a JSON payload (e.g., {"ts": ..., "id": ...}) for the page_token to keep it opaque to the client.

Backward compatibility: If we need to support old offset tokens during the transition, we could attempt to decode the token; if it parses as an old offset, we fall back to the legacy query, otherwise, we use the new cursor logic.

I will definitely use the existing session-event cursor implementation as the canonical pattern to keep things consistent.

Could you assign this to me? I'd be happy to open a draft PR shortly with the initial sessions migration for feedback!