`feat` PRD: `order_by` NULLS FIRST / NULLS LAST placement

[0x054]

Origin: Pinch PRD 0014 / pinch-backend#415 (pinned-first conversation list), filed as ferro-orm#358 (2026-08-28). Owner-approved after grilling: the SQL shape in the originating request is a requirement; several implementation choices in that request were redesigned (SQLite IS NULL emulation dropped; unspecified placement stays the backend default). Glossary: QueryIR payload, golden vector, relation traversal, projected record (CONTEXT.md).

Problem Statement

As a ferro user I cannot say where NULL sort keys go. order_by accepts only "asc" / "desc". Postgres and SQLite already disagree about the rest: Postgres DESC puts NULLs first (SQL standard); SQLite DESC puts them last (NULL is smallest).

The blocked workload is a pinned-first list. Conversation.pinned_at is a nullable timestamp — set when the user pins a thread, null otherwise. The contract is pinned first, by pinned_at desc, then unpinned by updated_at desc:

ORDER BY pinned_at DESC NULLS LAST, updated_at DESC, id DESC

Today .order_by(lambda c: c.pinned_at, "desc") on Postgres puts unpinned rows first — the opposite of the contract. Any "optional stamp leads" list is inexpressible as one ORDER BY.

Solution

An optional nulls= on every order_by() term, same vocabulary as SQL:

await (
    Conversation.select()
    .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")
    .all()
)

That renders ORDER BY pinned_at DESC NULLS LAST, updated_at DESC, id DESC on both backends. Omit nulls= and ferro emits a plain ASC/DESC — the database keeps its usual rule. No warning, no error. If you care about empty-vs-set order, you pass nulls=.

User Stories

1. As a Pinch backend developer, I want pinned_at DESC NULLS LAST as one order_by term, so that pinned conversations lead the list on Postgres instead of rising unpinned rows to the top.
2. As a Pinch backend developer, I want the same query to produce the same row order on SQLite, so that tests against the test stand-in match production.
3. As an application developer, I want nulls="first" as well as "last", so that "missing values lead" lists (e.g. never-completed tasks first) are spellable too.
4. As an application developer, I want nulls= to be keyword-only, so that .order_by(col, "desc", "last") is not a thing and "last" is never a cryptic third positional.
5. As an application developer, I want each chained order_by() to carry its own nulls=, so that only the nullable key is annotated and the tiebreakers stay plain DESC.
6. As an application developer, I want omitting nulls= to leave the database's usual rule in place, so that existing queries do not change order when this feature ships.
7. As an application developer, I want no warning or error when I omit nulls= on a nullable column, so that deleted_at, optional timestamps, and other ordinary nullable sorts keep working without ceremony.
8. As an application developer who does care, I want the docs to say that nullable sort keys are dialect-defined unless I pass nulls=, so that I find the SQLite-vs-Postgres footgun before I ship it.
9. As an application developer, I want a bad nulls value ("sideways", "nulls last") to fail at build time with ValueError, the same way a bad direction does, so that I never round-trip to the database to learn the vocabulary.
10. As an application developer, I want "FIRST" / "LAST" to work case-insensitively like "ASC" / "DESC", so that I am not punished for SQL-style capitalization.
11. As an application developer, I want nulls= to be legal on a NOT NULL column, so that ORDER BY id DESC NULLS LAST is not a ferro-invented error for SQL that is already valid.
12. As an application developer, I want nulls= on a left-joined related column even when that column is NOT NULL on the related model, so that rows without the relation (NULL in the result) can still be placed.
13. As an application developer, I want nulls= on a traversed forward-FK column (order_by(lambda t: t.account.closed_at, "desc", nulls="last")), so that relation-path sorts are not a second, lesser API.
14. As an application developer, I want nulls= on a column-name string (order_by("pinned_at", "desc", nulls="last")), so that the string form of order_by is not missing a kwarg the lambda form has.
15. As an application developer, I want nulls= on ProjectedQuery.order_by, including output-alias strings and aggregate lambdas (order_by(lambda t: t.amount.sum(), "desc", nulls="last")), so that every order_by() that exists today takes the same kwarg.
16. As an application developer, I want an omitted nulls= to be absent from the QueryIR payload, so that existing golden vectors stay valid and a reader of the wire can tell "I didn't say" from "I said last".
17. As a maintainer, I want Query IR to stay on version 7, so that an optional field does not pay the cost of a new required node kind.
18. As a maintainer, I want a hand-authored golden vector for a query that does set nulls, so that the Python emitter and the Rust decoder cannot drift on the new field.
19. As a maintainer, I want both backends to emit the native NULLS FIRST / NULLS LAST clause (no (col IS NULL), col rewrite), so that the SQL is the same, indexes keep a real sort key, and SQLite's supported clause is used rather than emulated.
20. As a docs reader, I want a guide example in lambda style that shows nulls="last" next to the rendered SQL, so that the feature is learnable from the docs alone.
21. As a docs reader, I want the dialect-default split stated once where order_by is documented, so that I am not surprised that omitted nulls= can disagree across backends.

Implementation Decisions

• API: order_by(selector, direction="asc", *, nulls=None) on every order_by() that exists today (Query and ProjectedQuery; lambda, string, traversal, aggregate, output alias). nulls is "first" or "last", keyword-only, per term. Validated at build time like direction (case-insensitive; anything else is ValueError).
• Omitted means backend default. When nulls is None, emit a plain ORDER BY col ASC|DESC with no NULLS clause. No warning, no error, including on nullable columns. Existing queries keep today's per-backend order.
• Always legal. nulls= is not gated on schema nullability. A left-joined related column can be NULL in the result even when the related model declared it NOT NULL.
• Wire: optional nulls on each QueryOrderBy entry. Emit the key only when the user passed it. ir_version stays 7 (additive optional field; not a new node kind). Existing golden vectors unchanged.
• SQL: native NULLS FIRST / NULLS LAST on Postgres and SQLite. No IS NULL emulation. Ferro has no MySQL, which is the dialect that actually lacks the clause.
• Docs: lambda predicates (I-8). Note once that omitting nulls= on a nullable key is dialect-defined. Model-declaration examples that show Field() stay in both assignment and Annotated tabs (I-7).

Testing Decisions

Tests assert external behavior only: build-time errors and order_by clause shape, payload shape at the golden-vector wire seam, and result-set order at the end-to-end query seam. No assertions on renderer internals or sea-query call shapes.

Three existing seams — no new ones:

1. Build time — extend the existing order_by validation tests (the suite that already rejects "sideways" as a direction and pins lambda/string extraction). Cover: nulls="first" / "last" accepted; keyword-only (positional third arg is TypeError); bad value is ValueError; case-insensitivity; omitted nulls leaves today's clause shape so existing assertions keep passing; NOT NULL columns accept nulls=.
2. Wire — one new hand-authored golden vector for a query that sets nulls on a term (and omits it on a chained term). Asserted from both the Python emitter and the Rust decoder, same contract as the rest of tests/fixtures/ir_vectors/. Existing vectors stay byte-identical (nulls absent, ir_version still 7).
3. End-to-end row order — both database backends via the existing db_url matrix, in the style of the existing per-feature query tests (e.g. related-column order_by in the joins suite). Seed a nullable sort key with a mix of set and NULL values. With nulls="last" under DESC, set values lead and NULLs trail on both backends (the Pinch shape, with a unique tiebreaker). With nulls="first", the reverse. Traversal and a projected aggregate each get one placement assertion. An omitted-nulls DESC on a nullable column is not asserted to match across backends — that split is specified.

Negative paths pin messages: invalid nulls names "first" / "last" the way invalid direction names "asc" / "desc".

Out of Scope

• Keyset / cursor pagination with NULL-tolerant comparison. Ferro has no keyset helper; docs already tell you to write where(lambda t: t.id > cursor) yourself. Tuple comparison with NULLs is a different feature. Pinch can use nulls="last" with offset pagination, keep two keysets, or write the NULL-tolerant where themselves.
• Changing the omitted-nulls default to Postgres convention (or always-NULLS LAST) on SQLite. Adding the kwarg must not rewrite existing ORDER BY on SQLite.
• Warning or erroring when a nullable column is sorted without nulls=.
• Emulating the clause with (col IS NULL), col on SQLite.
• Query IR version bump.
• Manual / user-arranged ordering, and any change to where() NULL predicates (== None / != None already render IS NULL / IS NOT NULL).

Further Notes

The originating request asked for SQLite emulation "where the backend lacks the clause." SQLite has had NULLS FIRST / NULLS LAST since 3.30 (2019); the query builder Ferro already uses emits the native clause on SQLite. Emulation is declined, not deferred.

Omit-nulls= staying dialect-defined is a scoped-down product choice, not a hollow feature: the requested capability (express NULLS FIRST / NULLS LAST) is complete. The leftover footgun (nullable sort without nulls= can disagree between SQLite tests and Postgres prod) is a docs problem.