skip to content

A lawyer pages through matters while ethical walls open and close mid-walk — what should the cursor and the total do?

level: seniorimportance: should knowfreq 40%

answer

  1. the set moves while you walk it
  2. counting rows versus naming a position
  3. sort key plus a unique tiebreaker
  4. the total describes a different set

basics

~20 s

Page by position, not by count: a keyset cursor naming the sort key and identifier of the last row survives a set that moves, while an offset skips and repeats rows. Offer a total only after the permission predicate, or not at all.

solid answer

~50 s

An offset means skip N rows of the result produced now, and N is exactly what changes when a matter enters or leaves the permitted set — so the caller skips rows they never saw and repeats rows they did. A keyset cursor names a position in the sort order instead (the sort key plus a unique tiebreaker of the last row returned), and churn elsewhere in the order cannot move that boundary. What it guarantees is that paging itself introduces no duplicates and no skips; it does not guarantee a stable set, because a matter unwalled after the caller passed its position will not appear in this walk at all. For totals: a count taken before the permission predicate tells the caller how many matters exist that they may not see, and a count taken after it repeats the expensive predicate on every request. Most systems answer `has more` by fetching one row beyond the page.

code

sql · 12 lines
sql
-- Keyset page: the permission predicate is evaluated INSIDE the paging query,
-- so the boundary is computed over rows that actually survive the filter.
SELECT m.id, m.title, m.opened_on
FROM matter m
WHERE EXISTS (
        SELECT 1 FROM matter_visibility v
        WHERE v.matter_id = m.id
          AND v.principal_id = :principal          -- re-derived from the session, never from the cursor
      )
  AND (m.opened_on, m.id) < (:cursor_opened_on, :cursor_id)
ORDER BY m.opened_on DESC, m.id DESC
LIMIT 26;   -- 25 returned; a 26th row present means 'there is more', with no COUNT

go deeper

for a junior

Remember that an offset counts rows and a cursor names a position, and that a listing whose rows can disappear between requests is counting something that will not hold still.

for a middle

Explain the two concrete anomalies an offset produces over a moving set — a skipped row when one leaves, a repeated row when one enters — and why a keyset boundary needs a unique tiebreaker alongside the sort key.

for a senior

Show you have operated this: the permission predicate inside the paging query, the cursor re-validated against the principal rather than trusted, a short non-final page treated as an alert, and a total you either refuse or take after filtering.

for a principal

Decide what the endpoint promises. A live traversal and a reproducible snapshot are different products, and conflating them either delays a wall taking effect or hands an auditor a set that moved.

## What actually changes between two page requests A lawyer walks a listing of the matters they may see. Between the request for page three and the request for page four, a conflicts check raises a wall on one matter and drops a wall on another. **The set being paged over is not the set it was a second ago**, and no cursor design makes it the same one. What you are choosing is which anomalies the caller experiences, and what you promise them. ## An offset cannot survive a predicate that moves `OFFSET 75` means *skip 75 rows of the result this query produces now*. That is a count, and the count is precisely what the permission change altered. - A matter **left** the permitted set: everything after it shifts up one position, and the caller never sees the row that moved into the gap — a silent skip. - A matter **entered** the permitted set before the cursor's position: everything shifts down one, and the last row of page three arrives again at the top of page four — a duplicate. Neither anomaly announces itself. The response is well formed, the page is full, and the missing matter is missing from a list nobody is reconciling. A **keyset** (seek) cursor names a POSITION instead: the sort key and the unique tiebreaker of the last row returned. Page four asks for *permitted matters ordered by opened date descending, strictly after (2023-11-04, 8812)*. Rows appearing or disappearing elsewhere in the order do not move that boundary, because the boundary is a value, not a count. ## What the cursor promises, and what it cannot - **It does promise**: paging itself introduces neither duplicates nor skips. Every row returned was permitted at the moment its page was built. - **It does not promise a stable set.** A matter whose wall came down after the caller passed its sort position sits behind the cursor and will not appear in this walk at all. That is the honest contract and it should be stated: *a walk is a consistent traversal of the ORDER, not a snapshot of the SET.* - **It requires a unique tiebreaker.** Sorting by a non-unique column alone leaves the boundary ambiguous between rows sharing a value, which reintroduces both skips and duplicates through the back door. - **It requires the permission predicate to be evaluated inside the same query as the paging.** If rows are filtered after the boundary is computed, the boundary describes rows you then threw away, and you are back to the offset's problem with extra steps. - **It must not carry permissions.** The cursor is a position, not an entitlement. Re-derive the permission predicate from the authenticated principal on every page. A cursor that encodes the permitted identifier set, or a tenant, or a role, is an entitlement the caller can edit and replay. If a caller genuinely needs a stable set — an export, a regulatory report, a printed index of matters — that is a different endpoint: freeze the permitted identifier set once, persist it, and page over the frozen set. Say so explicitly; it is the answer to *but the audit has to be reproducible*. ## The total is two different numbers | count | cost | what it discloses | |---|---|---| | before the permission predicate | cheap if an index covers the search terms | how many matters exist that the caller is forbidden to see | | after the permission predicate | a second full evaluation of the predicate you just paged over | nothing beyond what the rows already show | On a system whose whole purpose is that a lawyer cannot learn that a walled matter exists, the pre-filter count *is* the leak — the rows were withheld and the number handed over instead. The post-filter count is honest and expensive, and it is the one to reach for when a total is genuinely required. In practice, systems land here in this order: 1. **Offer no total.** Answer *is there more* by requesting one row beyond the page and reporting whether it came back. This costs nothing and covers the overwhelming majority of listings. 2. **Offer a capped count.** Evaluate the predicate up to a bound and answer `500+`. The bound is a latency budget, not a UX preference. 3. **Offer an exact filtered count** only where the predicate is local and indexed, and only where the endpoint's traffic can pay for it on every request. A total also has a stability problem of its own: the number computed for page one describes a set that has changed by page seven, so an interface rendering *page 3 of 19* asserts something it cannot keep. That is an argument for infinite scroll over numbered pages on any permission-filtered listing, and it is worth making out loud. ## Operating it - **Treat the cursor as opaque** so its encoding stays yours to change, and validate it against the current principal rather than trusting its contents. - **Alarm on short pages.** A page shorter than requested that is not the last page means the filter ran somewhere it should not have, or a refill cap was hit. - **Keep the sort key immutable, or accept the consequence.** If the sort key can change while a walk is open, a row can move across the boundary and be seen twice or not at all, independently of any permission change.

  • An auditor needs a listing whose contents do not change while they work through it. What do you give them?
    A different endpoint. Evaluate the permission predicate once, persist the resulting identifier set with a timestamp, and page over that frozen set. The listing endpoint stays a live traversal; the export becomes a snapshot with a stated as-of time. Trying to make one endpoint do both means either freezing visibility for ordinary users, which delays a wall taking effect, or handing the auditor a set that moved under them.
  • The interface renders page 3 of 19 for a permission-filtered listing. What breaks?
    The 19 was computed against a set that has since changed, so the last pages may not exist or may hide rows. Numbered pages also need an exact filtered count on every request, which is the expensive count. Either accept a capped or approximate figure and label it as such, or switch to a continue-from-here control that needs only the one extra row a keyset query already fetches.

Walking the shelves of a file room by saying resume after the folder labelled 4 November, number 8812 works even while clerks add and remove folders around you. Saying skip the first seventy-five folders does not: someone removed one, and the seventy-sixth is now a folder you will never look at.

saying these in an interview costs you the question

  • Offsets are fine as long as the sort order is stable
  • A cursor guarantees the caller eventually sees every matter they may see
  • Show the unfiltered total; the rows themselves are never returned
  • Sort by a single non-unique column; ties are rare enough to ignore
  • Put the permitted identifiers in the cursor so the next page is cheap