Cursor Pagination

The Model

 GET /trips?limit=20                          first page
 GET /trips?limit=20&cursor=<opaque>          continue after bookmark

 the cursor encodes "position in a stable sort order":
   typically (sort_value, id) of the LAST item returned

 SELECT * FROM trips
 WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
 ORDER BY created_at DESC, id DESC
 LIMIT 20;

The tuple comparison is the trick: created_at alone collides on ties; adding unique id as tiebreaker makes the position total and unambiguous.

Why It’s Stable Under Writes

 cursor = "everything before THIS row in this order"

 new rows inserted at top → irrelevant, they're AFTER the cursor
 rows deleted mid-list    → remaining positions still well-defined
 unlike offset: nothing shifts because nothing is counted by position
 
 infinite scroll just works: each page's last item mints the next cursor

The Opaque Envelope

 internal: {"c": "2026-08-24T10:00:00Z", "id": "trip_9f2k"}
 wire:     base64(json)  →  eyJjIjoiMjAyNi4uLg

 opacity is deliberate:
 - encoding can change (add filters, switch direction) invisibly
 - clients can't forge deep positions to probe data cheaply
 - server can embed context (the query it belongs to) for validation

 validate on receipt: decode + check belongs-to-this-query,
 else 400 — cursors are credentials-adjacent input

Index Requirements (where the cost hides)

The pattern only stays fast with a matching composite index:

 ORDER BY created_at DESC, id DESC
 requires INDEX ON (created_at DESC, id DESC)

 missing index → same linear-scan pain as offsets, but worse:
 now it's per-page forever, not just at depth

 every new sort option (?sort=fare) needs its own index pair —
 sort options are a performance budget, spend deliberately

Limitations to State Honestly

Limitation	Consequence	Workaround
No random access	Can’t jump to page N	Keep offset for admin UIs
Forward/backward only	No totals, no page numbers	UX redesign (infinite scroll)
Sort must be stable + indexed	Dynamic sorts expensive	Pre-restrict sort choices
Cursor expiry semantics	Old cursors may break	TTL them; document behavior

Filter-value changes mid-scroll also invalidate cursors — new filter = new walk from the start.

Bidirectional Walking

 next: WHERE key < :cursor        ORDER BY key DESC
 prev: WHERE key > :cursor_first  ORDER BY key ASC  LIMIT n → reverse

 two cursors per response (next_cursor, prev_cursor) if UX needs both

Interview Framing

“Design infinite scroll” is answered by: stable sort key + tiebreaker, opaque base64 cursor, composite index named explicitly, limits enforced. The seniority marker is volunteering the cost: “each sortable field costs an index — we’ll ship exactly two sort orders.” Candidates who treat cursors as magic strings miss the index conversation that follows.