Implement stable keyset pagination with async SQLAlchemy

• Use a unique deterministic ordering.
• Apply the cursor predicate in the same direction as ordering.
• Fetch limit + 1 to determine whether another page exists.
• Encode and validate cursor fields as one opaque API value.
• Do not use offset for subsequent pages.

1. Start a select(Order) with descending timestamp and ID ordering.
2. Decode the cursor and add a tuple comparison when present.
3. Fetch one extra row through the request-scoped AsyncSession.
4. Return the first limit rows.
5. Encode the last returned row only when the extra row proves another page.

from sqlalchemy import select, tuple_

async def order_page(session, limit: int, cursor: str | None):
    if not 1 <= limit <= 100:
        raise ValueError("invalid limit")

    stmt = select(Order).order_by(Order.created_at.desc(), Order.id.desc())
    if cursor:
        created_at, order_id = decode_cursor(cursor)
        stmt = stmt.where(
            tuple_(Order.created_at, Order.id) < tuple_(created_at, order_id)
        )

    rows = list((await session.scalars(stmt.limit(limit + 1))).all())
    visible = rows[:limit]
    next_cursor = None
    if len(rows) > limit and visible:
        last = visible[-1]
        next_cursor = encode_cursor(last.created_at, last.id)
    return visible, next_cursor

With a matching composite index, page lookup is O(log n + page size). Memory remains O(page size).

• Several orders can share the same timestamp, so ID is required.
• Deleted rows do not invalidate the ordering contract.
• Malformed cursors return a client error, not a server exception.
• Mutable sort keys need a documented stability policy.

• Create equal timestamps and verify no duplicate or missing IDs across pages.
• Insert a newer order between requests and verify the second page remains stable.
• Verify exact final-page cursor behavior.
• Inspect the query plan for the composite descending index.

• Ordering only by timestamp is not deterministic.
• Using > with descending order walks in the wrong direction.
• Returning a cursor whenever the page is full can advertise an empty page.
• Sharing AsyncSession across concurrent page requests violates session ownership.