`feat` PRD: keyset `after()`/`before()` derived from order_by, NULL-placement aware

[0x054]

Requested by: Pinch • Motivated by: pinch-finance/pinch-backend#415 and #428 (pinned-first conversation list; PRD pinch-backend#413). Follow-on to #358 / v0.20.0 order_by(nulls=).

Grilled 2026-08-30. ADRs: 0017 (omitted nulls= means last), 0018 (position paging). Glossary: Paging, Position, Order key, Null placement.

Problem Statement

As a ferro user I can sort a list so empty optional stamps trail (order_by(lambda c: c.pinned_at, "desc", nulls="last")), but I cannot ask for the next page of that list as one query. A SQL tuple compare (pinned_at, updated_at, id) < (:ts, :ts2, :id) has no meaning when the cursor row's leading key is NULL, so I have to tag my cursor with "which bucket" and write two where() branches. The ORDER BY is one line; the keyset predicate is still two.

Offset paging does not solve this: it is not stable under inserts, and it is a different QueryIR start. I already keep my own opaque cursor; I need Ferro to turn the last row's order-key values into the correct bound, including the non-NULL → NULL transition.

Solution

after(position) / before(position) are a paging start — a sibling of limit/offset, not a where() predicate. A position is the ordered tuple of the query's order-key values. position_of(row) reads it; passing the row itself is sugar. Ferro derives the NULL-aware bound from each order key's direction and null placement. Cursor encoding stays mine.

q = (Conversation.where(lambda c: c.ledger_id == lid)
     .order_by(lambda c: c.pinned_at, "desc", nulls="last")
     .order_by(lambda c: c.updated_at, "desc")
     .order_by(lambda c: c.id, "desc"))

page = await q.after(position).limit(20).all()
position = q.position_of(page[-1])
# or: await q.after(page[-1]).limit(20).all()

Omitted nulls= means last on every order_by (same order on both backends). "native" is the explicit dialect-default escape.

User Stories

1. As an application developer, I want after(position).limit(n) to return the next n rows after a position in the query's declared order, so that I can page a list without an offset.
2. As an application developer, I want before(position).limit(n) to return the n rows immediately before that position, still in the declared order, so that a "Previous" button works.
3. As an application developer, I want both bounds exclusive of the position, so that the cursor row is not repeated on the next or previous page.
4. As an application developer, I want position_of(row) to return the ordered tuple of order-key values, so that I can store a cursor without knowing which columns the query sorts by.
5. As an application developer, I want after(row) / before(row) to mean after(position_of(row)), so that I do not have to call position_of when the row is still in hand.
6. As an application developer, I want to rebuild a position from a decoded cursor as a plain tuple and pass it to after(), so that Ferro does not own cursor encoding.
7. As a Pinch backend developer, I want a pinned-first conversation list (pinned_at DESC NULLS LAST, updated_at DESC, id DESC) to page as one query, so that I can drop the pinned/active bucket tag and the two WHERE branches.
8. As a Pinch backend developer, I want after((None, updated_at, id)) to continue through the unpinned bucket, so that crossing from a pinned row into unpinned rows is not a special case in my handler.
9. As an application developer, I want two rows to never share a position, so that a page boundary cannot skip or repeat a row. The order keys must include the model's primary key.
10. As an application developer with a uuid4 primary key, I want that PK as a last-key tiebreaker only, so that random UUIDs do not set the list order — they only make "after this row" unambiguous.
11. As an application developer, I want after() without a PK among the order keys to fail at build time, so that I find the skip/duplicate bug before production.
12. As an application developer of a PK-less model, I want after() / before() to fail at build time, so that I am not offered a paging API the model cannot support.
13. As an application developer, I want a wrong-arity tuple to fail at build time, so that a stale cursor from a different order_by cannot silently page the wrong rows.
14. As an application developer, I want None in a non-PK slot to be legal, so that a nullable leading key (or a missing left_join relation) can sit in the position.
15. As an application developer, I want None in the PK slot to fail at build time, so that I cannot invent a position no row can have.
16. As an application developer, I want Ferro not to consult column nullability when accepting None, so that left_join + a NOT NULL related column still pages when the relation is missing.
17. As an application developer, I want a position that matches no remaining rows to return an empty page, so that "past the end" is not an error.
18. As an application developer, I want after() + offset() on the same query to fail at build time, so that I cannot stack two starts.
19. As an application developer, I want after() + before() on the same query to fail at build time, so that a range is not mistaken for a page start.
20. As an application developer, I want count() to ignore after / before the same way it ignores limit / offset, so that I can count the matching set from the same branched query.
21. As an application developer, I want update() / delete() to reject after / before the same way they reject limit / offset, so that I cannot write a non-portable mutation.
22. As an application developer, I want after(position).all() (no limit) to return every remaining row after the position, so that "the rest of the list" is expressible.
23. As an application developer, I want unbounded before(position).all() to return every earlier row in declared order (a prefix), so that after and before are symmetric about whether a limit is required.
24. As an application developer, I want to be told in the docs that on unbounded before(), first() (adjacent, via limit 1) and all()[0] (prefix head) disagree, so that I do not treat them as interchangeable.
25. As an application developer, I want before(position).first() to return the single adjacent previous row, so that "the row just before this cursor" is one call.
26. As an application developer, I want paging to compose with where(), traversal, exists(), include(), and order_by(), so that a filtered, joined list pages the same way a simple one does.
27. As an application developer, I want after / before on a ProjectedQuery, so that a column-subset page uses the same paging API.
28. As an application developer, I want position_of on a projected record to work when every order key is in the projection, so that I can cursor a record page without keeping the full model.
29. As an application developer, I want position_of on a projected record that omitted an order key to fail at build time, so that I pass a tuple instead of getting a silent wrong position.
30. As an application developer, I want after / before on a grouped aggregate to fail (the PK is not an order key of a GROUP BY), so that I am not offered position paging the projection cannot uniquely key.
31. As an application developer, I want aggregate / expression order keys to be a build-time error on after / before, so that v1 stays on columns Ferro can read off a row.
32. As an application developer ordering by a related column, I want after((label, id)) to work from a decoded tuple without include(), so that my cursor round-trip does not require populated relations.
33. As an application developer, I want position_of on a model instance to fail when a traversed order key is not populated, so that I do not read a proxy or trigger a fetch inside paging.
34. As an application developer, I want omitted nulls= to mean last on every order_by, so that the same sort (and the same pages) run on Postgres and SQLite.
35. As an application developer, I want nulls="first" and nulls="last" to pin placement explicitly, so that I can put empties at the top when I mean to.
36. As an application developer, I want nulls="native" when I deliberately want that backend's default, so that the dialect split is visible and never implied.
37. As an application developer upgrading from v0.20.0, I want the changelog / PR to say that omitted nulls= on Postgres DESC and SQLite ASC flips (NULLs move to last), so that I can audit existing sorts.
38. As a docs reader, I want a worked pinned-first list with after / before and a stored tuple cursor, so that I copy the official pattern (lambda order_by, both field-declaration styles elsewhere as usual).
39. As a docs reader, I want the all()[0] vs first() note on unbounded before() in the paging guide, so that the accepted disagreement is not a surprise.
40. As a maintainer, I want the QueryIR payload to carry at most one position bound plus typed values, so that paging is not smuggled through the predicate tree.
41. As a maintainer, I want every order_by term on the wire to carry an explicit nulls (last | first | native), so that Rust never treats a missing key as dialect-native.
42. As a maintainer, I want one expansion function to render the keyset SQL from the declared order keys, so that after and before cannot drift and both dialects share the decision table.
43. As a maintainer, I want a hand-authored golden vector for the paging-bound wire shape, so that the Python emitter and Rust decoder cannot silently disagree.
44. As a maintainer, I want the QueryIR version bumped, so that a mixed-version payload cannot decode a new bound as "no start."
45. As a test author, I want result-set assertions at the backend-matrix seam for the NULL-bucket crossing and adjacent before, so that I pin user-visible order without asserting SQL strings in pytest.
46. As a test author, I want build-time tests at compile_query / the chainer for the loud errors, so that illegal queries never reach the database.

Implementation Decisions

• Paging, not a predicate (ADR-0018). after / before are a QueryIR paging start, compiled only by compile_query. They do not add a QueryNode to where. count drops the bound (paging keys become null, same as today's limit/offset). Mutating verbs reject the bound the same way they reject limit/offset and omit paging keys on the wire.
• Position is an ordered tuple of order-key values, declaration order. position_of(row) produces it. after(row) / before(row) desugar to position_of. No Position type. No cursor codec.
• Order keys in v1 are root or traversed columns. Aggregates and other expressions are a build-time error on after / before. The model's primary-key fact must be among the order keys; do not append it silently. PK-less models fail at build time.
• Position validation happens at the chainer / compile: arity equals the order-key count; None is legal in every non-PK slot; None in the PK slot is an error; column nullability is not consulted. A bound that matches zero rows is an empty page.
• position_of on a model instance reads root keys via attributes and traversed keys only from populated relations; an unpopulated traversal is a build-time error. A caller-supplied tuple does not require include().
• position_of on a projected record requires every order key to appear in the projection (match the order key's source, including output aliases). Otherwise error; pass a tuple. Grouped aggregates fail the PK-in-order-keys rule — no extra gate.
• before + limit is adjacent (ADR-0018). Fetch path: invert each order key's direction, swap first↔last (native stays native — each dialect's ASC native is already the reverse of its DESC native), apply the inverted bound, LIMIT n, reverse the fetched rows so the result is declared order. Unbounded before is the before-bound plus declared order (prefix). Limit is optional on both sides.
• At most one start. after + offset, before + offset, and after + before are build-time errors. Chaining after twice last-wins, like limit.
• Null placement (ADR-0017). nulls= accepts last | first | native. Omitted at the chainer means last, on all order_by, not only when paging. Breaking: Postgres DESC and SQLite ASC with omitted nulls= flip. native is never implied.
• Wire (QueryIR version bump). Every order_by term carries explicit nulls (last | first | native) — a missing key is invalid at decode. Fetch payloads carry at most one position bound (after or before) whose values are the existing typed query-value nodes (so None and typed scalars survive). Mutating payloads still omit paging keys. Hand-authored golden vector pins the new bound; existing query vectors update for explicit nulls and the version bump.
• One expansion function in the Rust query renderer turns (order keys × direction × null placement × after/before × position values) into the IS NULL / compare tree. Both dialects consume it. native is resolved to that dialect's default at render, not in Python.
• Docs use lambda order_by (I-8). Paging examples do not declare fields; ADR-0017's order_by change is called out where omitted nulls= is shown.

Testing Decisions

Good tests assert external behavior only: result-set order and membership at the end-to-end query seam, payload shape at the golden-vector wire seam, and build-time error messages at the compile/chainer seam. No assertions on the expansion function's internals, node graphs, or generated SQL in pytest.

Seam 1 — backend-matrix result sets (highest). Same style as order_by nulls and uniform negation: both backends, assert row order / ids, not SQL. Covers: the Pinch pinned-first list; after crossing from a non-NULL leading key into the NULL bucket; before(pos).limit(n) adjacent (not prefix); unbounded after / before; first() on limited before; exclusive bounds; None in a non-PK slot; left_join + None for a NOT NULL related column; traversed order key with a raw tuple; include() + position_of; ProjectedQuery happy path; empty page past the end; omitted nulls= is last on both backends (cross-assert); nulls="native" is not cross-asserted (documented split).

Seam 2 — QueryIR golden vectors. One new hand-authored vector for a fetch payload with an after bound and typed values (including a JSON null in a non-PK slot). Python builder equality and the Rust decoder both assert the same bytes. Existing query vectors bump with the version and explicit nulls: "last" on terms that used to omit the key. Verb policy (count drops the bound; mutate omits paging keys) stays as inline expected payloads next to the existing count/mutate pins — no fetch-shaped vector for those verbs.

Seam 3 — build-time compile / chainer. Same style as the order_by nulls wire tests and query column validation: stop at compile_query or the chainer, no database. Pins: no PK in order keys; PK-less model; aggregate order key; after + offset; after + before; None in the PK slot; wrong arity; position_of on an unpopulated traversal; position_of(Row) missing an order key; invalid nulls= token.

Rust renderer (cargo). The expansion function's combinatorics (ASC/DESC × last/first/native × after/before × NULL-bucket) are pinned as unit tests on rendered SQL / condition trees — I-4: pure SQL generation stays in cargo test. pytest does not duplicate that matrix.

Prior art: backend-matrix e2e for order_by(..., nulls=) and ~; builder→wire golden vectors; compile_query wire tests that stop before execution.

Out of Scope

• Offset pagination (already exists; stacking with a position bound is an error, not a feature).
• Cursor encoding (Pinch and other callers keep their own opaque cursors).
• Aggregate or expression order keys as position components.
• Unique-but-not-PK as the uniqueness stand-in (the PK is the rule).
• Inclusive bounds; a range (after + before together).
• Requiring a limit on before().
• A named/opaque Position type.
• Silently appending the PK as a hidden order key.
• Optimistic locking / SELECT … FOR UPDATE.
• Changing count() / exists() to honor a position bound.

Further Notes

• ADR-0017 is a global order_by break, not a paging-only rule. Ship it in the same PR as after / before so omitted nulls= and the expansion function never disagree about what "default" means.
• native + limited before: invert direction only; do not swap the token. Each dialect's ASC native is the reverse of its DESC native, so the adjacent fetch stays a true reverse.
• Shape-preserving query and the complete-instance invariant still hold: paging does not change result type; position_of on a model always sees a complete row's root columns.
• I-8: docs and examples use lambda order_by / lambda where. Operator-style order_by is unchanged.
• I-9: the implementing PR closes feat PRD: keyset after()/before() derived from order_by, NULL-placement aware #372 explicitly.