find-domains: NAME/DEPTH ordering over nullable sort columns misses the index on DESC and drops the NULL tail in keyset pagination

[shrugs]

Summary

find-domains (Query.domains, Account.domains, Registry.domains, Domain.subdomains) supports ordering by NAME (Domain.canonicalName) and DEPTH (Domain.canonicalDepth). Both columns are nullable — they are NULL for non-canonical Domains. Account.domains, Registry.domains, and Domain.subdomains can return non-canonical Domains (only Query.domains is implicitly canonical-only via its required name filter), so null-valued rows can appear in those orderings.

This nullability causes two latent issues (the same two that REGISTRATION_TIMESTAMP / REGISTRATION_EXPIRY had before they were fixed):

1. DESC ordering misses the index. ORDER BY <nullable col> DESC NULLS LAST does not use the plain (registry_id, …) composite (verified via EXPLAIN: the planner falls back to a parallel seq-scan + top-N sort), because a forward index is ASC NULLS LAST and scanning it backward yields DESC NULLS FIRST — the NULLs placement disagrees. On a large registry this is the same multi-second full-partition sort that motivated the registration fix.

2. Keyset pagination silently drops the NULL tail. The non-NULL cursor tuple compare excludes NULL-valued rows, so forward pagination never yields the trailing non-canonical Domains. Today this is consistent forward/backward (both directions force NULLS LAST, so both symmetrically drop the tail), which is why the pagination tests pass — but the null rows are effectively unpaginatable.

Context

Discovered while fixing the analogous REGISTRATION_TIMESTAMP / REGISTRATION_EXPIRY slow-ordering bug. There, the fix was to materialize the sort value onto the Domain row as a NOT NULL column with a +∞ sentinel (REGISTRATION_SORT_SENTINEL) for absent values — non-null sort columns let a single plain (registry_id, col, id) composite serve both directions with a plain keyset tuple, no NULL-placement special-casing. See packages/ensdb-sdk/src/ensindexer-abstract/unigraph.schema.ts and apps/ensapi/src/omnigraph-api/lib/find-domains/find-domains-resolver-helpers.ts.

NAME/DEPTH were intentionally left out of that change to keep it scoped to the reported registration query.

Proposed direction

• DEPTH: straightforward — canonicalDepth is an int; the same sentinel approach applies (NOT NULL + sentinel, or backfill a value for non-canonical), plus a (registry_id, canonical_depth, id) composite for the registry-scoped case (today only a global (canonical_depth) index exists).
• NAME: needs its own design — canonicalName is indexed via a left(canonical_name, 256) expression index (btree per-tuple size constraints), and there is no natural string sentinel. Options: a canonical-only filter on NAME/DEPTH orderings, an (canonical_name IS NULL)-prefixed expression index, or excluding non-canonical Domains from these orderings.

Acceptance criteria

• Registry-scoped NAME and DEPTH ordering use an index-ordered scan in both directions (no seq-scan + sort), verified by EXPLAIN.
• Forward and backward keyset pagination are consistent and deterministically place/yield non-canonical (null-valued) Domains.
• Integration coverage in apps/ensapi/src/test/integration/find-domains/test-domain-pagination.ts exercises a result set containing non-canonical Domains for NAME/DEPTH.

Parent: #1360

[coderabbitai]

🔗 Related PRs

#1654 - feat: implement canonical filter for findDomains [merged]
#1742 - Registrar Actions: Inconsistent Order [merged]
#2125 - feat(ensapi): refactor domain name queries onto materialized canonical fields [merged]
#2155 - find-domains query perf fixes [merged]
#2175 - fix(ensindexer): make bridged-resolver subregistry write order-independent [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!