Ordering Determinism — AX-QRY-001 Kernel Axiom Delivery

Kernel Milestone: 4 of 6 Previous: Milestone 4 (Part 3) — Idempotency Contract — AX-CON-002 (v5.21.0) Next: Milestone 4 (Part 5) — Schema-Derived Service Boundary — AX-BND-001

System Impact
-------------
Ledger integrity:         strengthened
Ledger mutation:          unchanged
Tenant isolation:         unchanged
Financial immutability:   unchanged
State machine:            unchanged
Breaking changes:         none
Replay determinism:       strengthened — query layer now deterministic

Invariant Change
----------------
Every SELECT capable of returning multiple rows now carries an explicit
ORDER BY clause. PostgreSQL row ordering is no longer implicitly assumed
anywhere in the API surface.

Why This Matters

PostgreSQL makes no ordering guarantee without an explicit ORDER BY clause. A query executed twice against the same data can return rows in different sequences depending on internal page layout, vacuum activity, or index selection at execution time. For a general-purpose application this is a minor inconvenience. For a financial platform it is a structural problem.

SpeyBooks processes financial records that must be reproducible: ledger exports, reconciliation sequences, paginated transaction lists, dividend vouchers, VAT summaries. If the underlying row ordering is non-deterministic, two exports of the same data produced minutes apart may differ. Pagination may silently shift — a record appearing on page 2 in one request may not appear at all in the next. Reconciliation logic that processes rows sequentially may reach different conclusions on different days. None of this produces an error. It simply produces wrong results, intermittently, in ways that are extraordinarily difficult to diagnose after the fact.

AX-QRY-001 eliminates this class of defect entirely by making deterministic ordering a mechanically enforced invariant rather than a coding convention.

Ordering Determinism

AX-QRY-001 — What Was Enforced

A full audit of all 112 TypeScript files in the API surface identified every SELECT statement capable of returning more than one row. Each was classified into one of two categories:

Single-row lookups — queries returning exactly one row by primary key or unique constraint. These were corrected by adding an explicit LIMIT 1, documenting intent in code and allowing the PostgreSQL query planner to apply a single-row optimisation path.

Multi-row queries — list queries, join queries, set membership lookups, and aggregate queries grouped by a non-unique field. These were corrected by adding a deterministic ORDER BY clause, with a stable tie-breaker column (id) ensuring total ordering even when the primary sort field contains duplicate values.

Ordering Conventions Established

The following deterministic ordering conventions are now consistently applied across the codebase:

• Transactions, invoices, dividends — ORDER BY created_at DESC, id DESC
• Contacts, accounts, directors — ORDER BY name/code, id
• Administrative lists — ORDER BY created_at DESC, id DESC
• Report aggregates — ORDER BY account_type or ORDER BY account_id
• Audit and event log queries — ORDER BY created_at DESC, id DESC

The CI Gate

A permanent TypeScript AST gate (scripts/check-order-by.ts) was introduced as part of this milestone. The gate uses a full TypeScript AST parser combined with a SQL AST parser to analyse every query call in the codebase. It is not regex-based — it understands TypeScript syntax, template literals, and SQL grammar. It classifies each SELECT and fails the build if any multi-row query lacks a deterministic ORDER BY.

The gate correctly exempts genuine single-row patterns: pure aggregates without GROUP BY, scalar function calls, SELECT 1 literals, LIMIT 1 queries, and primary key lookups. Legacy migrations predating AX-QRY-001 are held in a versioned exemption set.

The gate is now a permanent part of the build. Every query written from this point forward is automatically checked before it can reach production.

Threat Closure

Kernel Closure Statement

Axiom: AX-QRY-001 — Ordering Determinism

Status: CLOSED as of SpeyBooks v5.22.0

Enforcement layers verified:
  Application layer    — LIMIT 1 on all single-row lookups
  Application layer    — ORDER BY with stable tie-breaker on all multi-row queries
  CI gate              — scripts/check-order-by.ts (TypeScript AST + SQL AST)

CI verification:
  check-order-by.ts    — PASS (0 violations across 112 files, 60 migration files)

Residual risk:
  None within the query ordering domain.
  Raw SQL executed outside the standard query call pattern is not scanned.
  No such patterns exist in the current codebase.

Next dependent axiom:
  AX-BND-001 — Schema-Derived Service Boundary

Security Posture Change

Prior to this release, row ordering in multi-row queries was a Class O guarantee — dependent on PostgreSQL’s internal execution behaviour and developer discipline. It was not enforced and could not be verified programmatically. This release moves query ordering to Class M: the CI gate rejects any query without an explicit ORDER BY, making it impossible for a non-deterministic multi-row query to reach production without a deliberate exemption.

Verification Record

Pre-flight:
  167/167 violations identified by TypeScript AST gate
  Triage complete: 123 single-row lookups, 44 genuine multi-row queries

Patch application:
  Pass 1: 74 fixes applied across 28 files
  Pass 2: 78 fixes applied across 33 files
  Pass 3:  7 fixes applied across  7 files (duplicate query instances)
  Total:  167 queries corrected across 43 files

Post-commit:
  V1  pnpm build — clean, 0 TypeScript errors
  V2  check-order-by.ts — PASS, 0 violations
  V3  112 TypeScript files scanned, 60 migration files scanned

Adversarial review:
  9.5 / 10 Production Gold

Architectural Context

AX-QRY-001 is the fourth axiom delivered under Milestone 4 (Provenance). The previous three axioms established sequential identifier correctness (AX-CON-001), temporal determinism (AX-TMP-001), and idempotency (AX-CON-002). Together they ensure that records are created with correct identifiers, at correct times, and exactly once.

AX-QRY-001 extends this foundation to the read path: records are now not only written deterministically but retrieved deterministically. This is the prerequisite for AX-BND-001 (Schema-Derived Service Boundary), which enforces type correctness at service interfaces — a guarantee that depends on queries returning rows in a predictable, stable order.

More significantly, AX-QRY-001 is a direct prerequisite for Milestone 6, the append-only cryptographic ledger. Ledger hashing requires that the same query run twice produces the same sequence of financial events to hash. Without deterministic query ordering at the application layer that guarantee is structurally impossible. It is now in place.

Operational Impact

Reproducibility: Identical queries now produce identical row sequences across executions, regardless of PostgreSQL internal state.

Pagination stability: Paginated endpoints return consistent records across pages and requests. Silent record duplication or omission at page boundaries is no longer possible.

Export consistency: Financial exports generated at different times against the same data are now sequence-consistent.

CI enforcement: Zero ongoing maintenance cost. The gate runs on every build and rejects any new unordered query before it can reach production.

Ledger readiness: The query layer is now deterministic end-to-end, satisfying the ordering prerequisite for the M6 cryptographic audit trail.

Kernel Status

Files Changed

Backend:

• scripts/check-order-by.ts — TypeScript AST ordering gate (permanent CI invariant)
• api/src/lib/idempotency.ts — LIMIT 1 on idempotency key lookup
• api/src/lib/bank-import/duplicate-detection.ts — ORDER BY on candidate row fetch
• api/src/middleware/admin.ts — LIMIT 1 on admin status check
• api/src/middleware/plan-gate.ts — LIMIT 1 on subscription status check
• api/src/routes/ — 33 route files updated with LIMIT 1 or ORDER BY
• api/src/services/ — 5 service files updated with LIMIT 1 or ORDER BY

AX-BND-001 (Schema-Derived Service Boundary) is next — TypeBox runtime validation at service interfaces, closing the final M4 axiom and establishing type correctness enforcement from API boundary through to the database layer.