fix(transfers): stable tiebreak for sellers/buyers list pagination - #1079

Fixes #1072.

What changed

sellers/list-mv.ts and buyers/list-mv.ts sort by the requested column (tx_count, total_amount, latest_block_timestamp, unique_buyers/unique_sellers) with no secondary sort key. Postgres does not guarantee a stable order among tied rows across separate query executions, so LIMIT/OFFSET pagination can duplicate a tied row on one page and omit another entirely — matching this issue's report and the confirming comment ("pagination on the sellers list double-serves boundary rows on ties").

recipient (sellers) / sender (buyers) is already the GROUP BY key in both queries, so it's unique per row after grouping — a ready-made tiebreaker requiring no schema change.

Fix

Added recipient ASC / sender ASC as a secondary sort key to both queries (skipped for the editorial sort in sellers, which already sorts by recipient as primary — avoids a redundant ORDER BY "recipient" ASC, recipient ASC).

The SQL-building logic is extracted into new order-by.ts files (one per service) so it's unit-testable in isolation — list-mv.ts transitively constructs live Prisma/neon/redis clients at module-import time (via @x402scan/transfers-db and @/lib/cache), which made the function untestable while colocated there.

Reachability

Both queries are the live data source for the public sellers/buyers list endpoints — not test/mock code, reachable by any paginated request.

Verification

• Reproduced locally: yes — against real Postgres 16 (Docker), mirroring the exact GROUP BY + ORDER BY + LIMIT/OFFSET shape of the actual query. Two ordinary sequential page requests (LIMIT 5 OFFSET 0 then LIMIT 5 OFFSET 5) against unchanged data returned an overlapping row at the boundary — no concurrent writes needed to trigger it, since Postgres's top-N sort doesn't guarantee consistent tie-break order between separate query executions.
• Mutation-resistance check: reverted only the tiebreaker logic (kept the new tests) → 4 of 5 tests failed for the exact expected reason (missing , recipient ASC / , sender ASC in the generated SQL text); restored the fix → all 5 pass again.
• Full existing test suite: pnpm --filter @x402scan/app test -- --run → 198/198 passing (18 files), no regressions.
• Typecheck: pnpm --filter @x402scan/app run types:check output is byte-for-byte identical with and without this change (345 pre-existing errors either way, none in touched files) — confirmed via git stash + rerun, so those failures are pre-existing and unrelated to this PR.
• Lint: eslint clean on all touched files (--max-warnings 0).
• Environment: Postgres 16 (Docker), Node 20, pnpm 10.28.0, vitest 4.0.17.

Also found, not fixed here (flagging, not bundling)

facilitators/list.ts and buyers/sellers/list.ts have the identical missing-tiebreaker pattern. Left out of this PR to keep it scoped to the reported issue — happy to send a follow-up if useful, or an issue is fine too, whatever's preferred.

Provenance

Fix drafted by an external agent (OpenAI Codex, GPT-5.6 via AgentRouter) in a sandboxed, git-less worktree; I (Claude) independently re-derived the root cause and reproduction from scratch first, then independently re-verified the drafted diff — reran the mutation-resistance check myself, restructured the testability approach after discovering the drafter's original design would have required stubbing the whole app's env-var validation to run in CI, and re-ran the full test/lint/typecheck suite myself before opening this PR.