getPayoutsOwed reads the ledger unpaginated — a PostgREST row cap silently misstates payout balances

[guillermoscript]

Follow-up to #493 — found reviewing the epic's shipped code (2026-07-25).

Problem

getPayoutsOwed() fetches the entire transaction and payout ledger in one unpaginated PostgREST call. PostgREST caps rows server-side (Supabase's default API "Max rows" is 1000), and a capped response is indistinguishable from a complete one — the balance simply comes out low, with no error.

Evidence

app/actions/platform/payouts.ts:27-40:

admin.from('tenants').select('id, name'),
admin.from('revenue_splits').select('tenant_id, school_percentage'),
admin.from('transactions')
  .select('tenant_id, payment_provider, amount, currency, school_percentage_snapshot, status, transaction_date')
  .in('status', ['successful', 'refunded'])
  .in('payment_provider', PLATFORM_SETTLED_PROVIDERS),
admin.from('payouts')
  .select('tenant_id, amount, currency, period_end, paid_at, created_at')
  .eq('payout_method', 'manual').eq('status', 'paid'),

No .range(), no .limit(), no count check on any of the four. computeOwedBalances() is pure arithmetic over whatever array it is handed (lib/payments/payouts-owed.ts:183), so a truncated input yields a confidently wrong number rather than a failure.

Which side the truncation lands on decides the direction of the error:

• transactions truncated → grossOwed too low → underpays the school
• payouts truncated → alreadyPaid too low → overpays it

The same shape appears in app/api/cron/enforce-plan-limits/route.ts:49 (from('tenants').select('id') with no pagination) — a truncated tenant list silently skips the tail of the sweep, which is #494's only organic-growth trigger.

Impact

Latent today, not reproducible: the cloud project currently holds 0 transactions and 0 payouts rows (checked 2026-07-25) — the manual-payout feature was verified against local data only. It bites silently at scale, in the one place this epic exists to make trustworthy, and it fails without any signal — the exact class of error #496/#497/#511 were about.

Wanted

1. Page the reads (.range() loop until a short page comes back), or move the aggregation into SQL — a SECURITY DEFINER RPC returning per-tenant-per-currency sums would remove the row-count exposure and most of the transfer entirely. The per-transaction split snapshot makes this a plain SUM(amount * school_percentage_snapshot / 100) group-by, so the arithmetic stays where payouts-owed.ts already documents it.
2. Whatever the mechanism, assert completeness — a { count: 'exact' } head query compared against the rows actually processed, and a loud error (not a silent low number) when they disagree.
3. Same treatment for the tenants sweep in enforce-plan-limits.
4. Confirm the project's configured API "Max rows" while doing this, and write it down — the fix should not depend on the reader knowing the default.

[guillermoscript]

Plan

Root cause

getPayoutsOwed() issues four unbounded PostgREST reads (app/actions/platform/payouts.ts:27-40) and hands whatever comes back to computeOwedBalances(), which is pure arithmetic over the arrays it is given. PostgREST caps every response at the project's configured "Max rows" and reports the cap the same way it reports a complete result — a 200 with fewer rows. Nothing downstream can tell the difference, so the balance simply comes out wrong in whichever direction the truncation happened to land: a short transactions page underpays the school, a short payouts page overpays it.

This is not display-only. markPayoutPaid() re-calls getPayoutsOwed() for its 10% mismatch guard (app/actions/platform/payouts.ts:91), so a truncated balance also decides whether an operator gets warned about a wrong payout amount — the guard would wave through the very typo it exists to catch.

The tenants sweep in app/api/cron/enforce-plan-limits/route.ts:49 has the same shape with a different symptom: past the cap the daily reconcile silently stops visiting the tail of the tenant list, which is the only organic-growth trigger for #494's access cutoff.

Configured cap. supabase/config.toml:18 sets max_rows = 1000 for the local stack. On the cloud project there is no role-level override — pg_db_role_setting holds no pgrst.* entry — so it runs on Supabase's hosted default, also 1000. The fix is deliberately written not to depend on that number being right (see below).

Approach

Page the reads rather than moving the aggregation into SQL. A SECURITY DEFINER RPC would cut the transfer, but it would also fork the arithmetic: lib/payments/payouts-owed.ts carries ~100 lines of documented, test-covered rules (the per-transaction split snapshot with its pre-#496 fallback, per-currency grouping, the reporting-only clawback heuristic, the #516 overpaid term). Re-expressing those in SQL creates two implementations that must agree forever. Paging keeps one source of truth and is enough to close the correctness hole.

1. New helper lib/supabase/fetch-all-rows.ts. Takes a page-builder callback (from, to) => query.range(from, to) and loops until a short page comes back. Requires the caller's query to carry { count: 'exact' } and a stable .order() on a unique column — without deterministic ordering, .range() windows can overlap or skip rows, which would swap a truncation bug for a duplication bug. Page size is our own constant (1000), independent of the server cap.

2. Assert completeness, loudly. PostgREST returns the total matching count on every ranged request. The helper compares the rows it actually accumulated against the count from the first page and throws — naming the relation and both numbers — when it collected fewer. Fetching more than the first count is not an error: rows inserted concurrently mid-sweep are benign, and treating growth as corruption would make the platform payouts page flaky. It also carries a page-count ceiling so a pathological server can't spin the loop forever.

3. Apply to all four reads in getPayoutsOwed(), each ordered by its primary key (tenants.id, revenue_splits.split_id, transactions.transaction_id, payouts.payout_id).

4. Apply to the enforce-plan-limits tenant sweep, ordered by tenants.id.

5. Document the rule and the confirmed cap in docs/PLATFORM_ADMIN.md, next to the existing admin-client PostgREST gotcha, so the next platform-wide sweep gets written correctly instead of rediscovering this.

Risks

• Ordering. .range() without a stable sort is undefined across pages. Every call site orders by a unique key; the helper documents the requirement.
• False alarms. A concurrent insert lands between pages. Handled by the asymmetric assertion (short = error, long = fine).
• Latency. Four sequential page loops instead of four single round trips. Promise.all across the four relations is preserved, so only pages within one relation serialize — and today every relation fits in a single page.
• A throw where there was silence. getPayoutsOwed() can now fail the platform payouts page instead of rendering a wrong number. That is the intended trade — this issue exists because a wrong number was indistinguishable from a right one.

Test plan

• Unit tests for fetchAllRows: single short page; exact multiple of the page size (must not loop forever or drop the boundary); multi-page accumulation in order; server truncation (count says 1500, server returns 1000) throws naming the relation; concurrent-insert growth (rows > first count) does not throw; PostgREST error on page 2 propagates; runaway page ceiling trips.
• npm run test:unit — existing tests/unit/payouts-owed.test.ts (~30 cases) must stay green; the arithmetic module is untouched.
• npm run typecheck and npm run build.
• Manual: /platform/payouts as owner@e2etest.com (super admin) still renders balances, and enforce-plan-limits returns the same tenant tallies as before.

Out of scope, found while surveying: the same unpaginated-sweep shape exists in solana-reconcile, binance-personal-reconcile, expire-subscriptions, solana-pull, expire-platform-subscriptions (four sweeps) and app/actions/platform/billing-health.ts. They now have a helper to adopt; I'll list them on the PR for a follow-up issue rather than widen this one.

[guillermoscript]

Shipped in #536 (squashed to 3318403).

The four ledger reads in getPayoutsOwed() and the tenants sweep in enforce-plan-limits now go through fetchAllRows() (lib/supabase/fetch-all-rows.ts), which pages until the relation is exhausted and then checks the rows it collected against PostgREST's own count: 'exact' — a shortfall throws, naming the relation and both numbers, instead of returning a plausible low balance.

Answering the four asks in order:

1. Paged, not moved into SQL. A SECURITY DEFINER aggregate would have forked the arithmetic: payouts-owed.ts holds the documented, test-covered rules (per-transaction split snapshot with its pre-Revenue split isn't time-sliced — plan changes retroactively reprice historical payouts #496 fallback, per-currency grouping, the reporting-only clawback heuristic, Payout overpayment is unrecoverable and the mismatch guard is skipped on retry (#499 follow-up) #516's overpaid), and restating them in SQL leaves two implementations that must agree forever. The RPC remains available later as a performance change, made on purpose.
2. Completeness is asserted, and deliberately asymmetrically — fewer rows than the count is an error, more is not, since rows inserted mid-sweep are benign and treating growth as corruption would make the payouts page fail whenever a sale lands mid-render.
3. enforce-plan-limits got the same treatment; an incomplete tenant list now fails the run with a 500 rather than quietly reconciling a partial one.
4. Cap confirmed and written down in docs/PLATFORM_ADMIN.md: supabase/config.toml sets max_rows = 1000 locally, and the cloud project carries no pgrst.db_max_rows role override, so it runs on Supabase's hosted default — also 1000.

One thing worth recording, because it nearly shipped as a bug inside the fix: PostgREST applies the cap to ranged requests too. Asking for .range(0, 999) against a cap of 500 returns 500 rows, and a loop that reads "short page" as "end of relation" reproduces exactly the truncation this issue is about. The helper instead adopts whatever page size the server proved it can return and continues, so it never depends on the cap's value. Verified against the local stack with the cap forced to 5: a bare .select() returned 5 of 20 rows with no error, while fetchAllRows returned all 20, unique and in order, still requesting 500-row pages.

Left open on purpose — the same unpaginated-sweep shape exists in solana-reconcile, binance-personal-reconcile, expire-subscriptions, solana-pull, expire-platform-subscriptions (four sweeps in that one file) and app/actions/platform/billing-health.ts, plus app/sitemap.xml/route.ts asking .limit(5000) against a 1000 cap. Each has its own blast radius and deserves its own reasoning, but they all now have a helper to adopt.