Payout and revenue accuracy round 2: partial refunds, dead revenue queries, fee divergence, rounding

[guillermoscript]

Part of EPIC #540. §3.1–3.4.

Four money-accuracy defects that survived epic #493's payout hardening. #511/#515/#516's clawback arithmetic itself was re-audited and is correct — these are different problems.

1. A partial refund erases the school's entire share 🔴

lib/payments/webhook-dispatch.ts:252-257 — refund.succeeded unconditionally does .update({ status: 'refunded' }). No refunded amount is read or stored, and the mapped event carries none: lib/payments/paypal-provider.ts:601-611 and lib/payments/binance-provider.ts:319-326 drop the amount into raw and never surface it.

All three platform-settled providers support partial refunds programmatically — paypal-provider.ts:750-751 ("Partial refunds need the currency…"), binance-provider.ts:348 (refundAmount), and supportsRefunds: true for paypal/lemonsqueezy/binance in types.ts. But lib/payments/payouts-owed.ts:121 types status as a binary 'successful' | 'refunded', and :256-261 drops the whole sale.

So a $10 goodwill refund on a $100 PayPal sale removes $100 from grossCollected/grossOwed — under-paying the school $72 at an 80% split. It also fires entitlements → revoked (:259-266), so a partially-refunded student loses course access outright. Silent in both directions.

The codebase already knows partials exist: app/api/stripe/webhook/route.ts:227-228 computes isFullRefund = charge.amount_refunded >= charge.amount and keeps the row successful on a partial. Stripe Connect is the one provider excluded from the payout math, so that guard protects the one case that did not need it.

Fix: carry amount/currency through NormalizedEvent; persist a refunded amount; give PlatformSettledTxn a refundedAmount so grossOwed subtracts the refunded slice rather than the row; revoke entitlements only on a full refund. Mind the per-provider units (PayPal major units vs Binance).

2. Two revenue screens query a column that does not exist 🔴

• app/[locale]/dashboard/teacher/revenue/page.tsx:30 — .select('amount, status, payment_provider, created_at') … .order('created_at', …), and :45 filters on t.created_at.
• app/[locale]/dashboard/admin/analytics/page.tsx:69 — .select('amount, status, created_at') … .gte('created_at', …) .order('created_at', …).

transactions has transaction_date, not created_at — confirmed absent from the live table and from lib/database.types.ts. PostgREST rejects the whole request; both call sites destructure { data } with no error check, so transactions is null.

Every school sees $0.00 total revenue, $0.00 school share, $0.00 last-30-days and a count of 0 on every render, permanently, with nothing logged. This is the school-side counterpart of the payout numbers epic #493 spent seven sub-issues getting right.

Fix: created_at → transaction_date at all four references; surface error instead of falling through to zeros.

3. The platform page and the school page disagree by the entire platform fee 🔴

app/actions/admin/revenue.ts:68-72 classifies every transaction as t.stripe_payment_intent_id ? 'stripe' : 'manual', then charges the platform fee only if that label appears in applies_to_providers — which defaults to ARRAY['stripe'] (20260216212440_create_revenue_infrastructure.sql:20).

A PayPal / Lemon Squeezy / Binance sale is therefore labelled manual, bears 0% fee, and the school's netRevenue shows 100% of it. Meanwhile app/actions/platform/payouts.ts:20-22 + payouts-owed.ts:255 apply the 80% school_percentage to those same rows, and neither the payout math nor the #512 backstop trigger reads applies_to_providers at all.

Two shipped, authoritative-looking screens differing by the whole platform fee, on every platform-settled sale.

Fix — pick a side: either retire applies_to_providers (it names stripe/manual, not the eight real provider slugs — it reads like a stale abstraction from the Connect-only era) and have getRevenueOverview apply platform_percentage to the same settlesToPlatformAccount set getPayoutsOwed uses; or populate it with real slugs and have both readers honour it. State which you chose and why in the PR.

4. Rounding residue and payout idempotency 🟡

Residue. payouts-owed.ts:255 — scaledAmount = (txn.amount * effectivePercentage) / 100, in float, summed unrounded. One $49.99 sale at 80% → 39.992. The dialog pre-fills netOwed.toFixed(2) = 39.99 (mark-payout-paid-dialog.tsx:31), payouts.amount is NUMERIC(10,2), so alreadyPaid is exactly 39.99 and netOwed settles at 0.002 — never 0. app/[locale]/platform/payouts/page.tsx:298 renders that as $0.00 while mark-payout-paid-dialog.tsx:82 gates the button on disabled={netOwed <= 0}, so it stays enabled. Every school whose owed total isn't an exact cent parks a permanent phantom balance on a permanently actionable row, and residue accumulates across cycles (any .99 price at 80%, any 3%/7% split). Operators learn to ignore a row reading $0.00 that still demands action.

Idempotency. app/actions/platform/payouts.ts:126-135 inserts into payouts with no period_start/period_end and no idempotency key. The table's only uniqueness is UNIQUE (tenant_id, period_start, period_end) (20260216212440:90), and 20260724120000_manual_payouts.sql:15-16 made both columns nullable for exactly this path — Postgres treats NULLs as distinct, so the constraint never fires for a manual row. mark-payout-paid-dialog.tsx:147 disables the button only while loading, which does not survive a reload, a second tab, a second super admin, or a server-action retry. Two inserts of the same wire double alreadyPaid; netOwed floors to 0 and the excess surfaces only as overpaid, which the module itself notes is recovered by carry-forward "only for a school that keeps selling". For a dormant school the platform has paid twice with no reversal path — CHECK (amount > 0) forbids a correcting negative row.

Fix: round each scaledAmount to the currency's minor unit before accumulating (or compute in integer minor units throughout — decide the rounding mode so the platform never systematically rounds in its own favour), and use a < 0.005 epsilon for both the floor and the button gate. Add a client-generated idempotency_key uuid with a partial unique index WHERE payout_method='manual', generated once per dialog open; on conflict return {status:'ok'} rather than erroring.

Out of scope — audited and correct, do not change

computeOwedBalances' clawback arithmetic (clawback is deliberately absent from netOwed; refunded rows leave grossOwed, their payouts stay in alreadyPaid, so the overpayment nets out exactly once — #511 got this right). wasPlausiblyPaidOut (same-tenant and same-currency, parses dates rather than string-comparing, fails closed). Double-clawback of the same transaction (impossible — webhook-dispatch.ts:249 breaks unless status === 'successful' and the update carries .eq('status','successful')). The #516 mismatch-retry interaction (mismatch is cleared on every keystroke, so confirmMismatch: true can only accompany the exact amount warned against). Currency-case handling (transactions.currency is a lowercase enum; no zero-decimal currency exists in it). The #512 backstop trigger (live on cloud, correct).

Acceptance criteria

• A partial refund reduces grossOwed by the refunded slice only, and does not revoke the entitlement. Unit-tested per provider, with the units asserted.
• Teacher and admin revenue pages show real figures; a query error surfaces instead of rendering zeros.
• For the same tenant and period, the school-facing net revenue and the platform-facing netOwed reconcile to the same split. Assert it in a test with a PayPal sale.
• netOwed for a $49.99 sale at 80%, fully paid, is exactly 0, and the Mark-as-paid button is disabled.
• Submitting the Mark-as-paid dialog twice records one payouts row.
• tests/unit/payouts-owed.test.ts gains fractional cases (49.99 @ 80%, 33.33 @ 70%) — all 31 existing cases use round amounts and round percentages, which is why the residue was invisible.
• markPayoutPaid's mismatch guard has direct coverage at the netOwed === 0 boundary (today it has none — grep -rl "markPayoutPaid" tests/ returns only comments).
• npm run test:unit green (370/370 baseline at 146a8cfa).

Effort: M · Risk: MED for the refund path (touches the shared dispatcher and the entitlement-revoke branch; a wrong units assumption moves real money) and for §3 (whichever side changes, one set of schools sees their revenue figure move). LOW for §2 and §4.

[guillermoscript]

Plan of attack

Surveyed all four sections read-only. The diagnosis holds in every case; two things differ from the issue text and are called out below.

1. Partial refunds — carry the amount, subtract the slice

Root cause. NormalizedBillingEvent (lib/payments/types.ts:347-365) has no money fields at all, so the three refund mappers have nowhere to put the refunded amount and drop it into raw. webhook-dispatch.ts:261-266 therefore treats every refund as total, and computeOwedBalances models the transaction's fate as a binary status.

Approach.

• Add amount?: number / currency?: string to NormalizedBillingEvent, documented as major units of currency, normalized by the provider mapper — the dispatcher stays unit-free. Each mapper converts: PayPal resource.amount.value is already major units; Lemon Squeezy order_refunded reports cents (/100); Binance carries the figure inside its parsed data payload.
• Add transactions.refunded_amount NUMERIC(10,2) NOT NULL DEFAULT 0 — the cumulative refunded slice in the row's own currency. New migration; server-write-only like the rest of transactions (issue Solana settlement columns are caller-controlled at INSERT — a pending row can under-quote what it owes (#528 follow-up) #538), so no new grant.
• refund.succeeded accumulates rather than overwrites: newRefunded = min(prior + slice, amount), isFull = newRefunded >= amount - 0.005. Status flips to refunded only on a full refund; a partial keeps the row successful with the slice recorded — exactly the shape app/api/stripe/webhook/route.ts:227-228 already uses, which is why that route stays the reference behaviour. The entitlement revoke moves under isFull.
• Two safety rules, both deliberately conservative: an absent amount means full refund (today's behaviour, unchanged for a provider that stops sending one), and a currency mismatch between event and transaction discards the event's amount and treats the refund as full, with a console.error. Trusting a mismatched figure is the one failure mode that moves real money in the wrong direction.
• PlatformSettledTxn gains refundedAmount; computeOwedBalances scales amount - refundedAmount instead of amount, for grossCollected, grossOwed and byProvider alike. Fully-refunded rows keep their current treatment (excluded, clawback unchanged).
• The legacy Stripe route persists refunded_amount too (charge.amount_refunded / 100). Stripe is outside the payout math, but the school-facing revenue screens read the same column and would otherwise overstate a partially-refunded Stripe sale.

2. Dead revenue queries — one page is louder than reported

transactions has transaction_date; created_at does not exist on it (confirmed against lib/database.types.ts). Both call sites are broken, but not identically:

• app/[locale]/dashboard/admin/analytics/page.tsx:69-71 destructures { data } with no error check → silent $0.00, exactly as reported.
• app/[locale]/dashboard/teacher/revenue/page.tsx:38-41 was moved onto fetchAllRows by Finish the #533 pagination sweep — eight unbounded reads remain #548, and that helper throws on a page error. So this page does not render zeros today — it raises [fetchAllRows] transactions page failed … and the route 500s. Still broken, still caused by the same four references, but the reported symptom belongs to the admin page alone.

Fix: created_at → transaction_date at all four references, and give the admin analytics read an explicit error check so a future column drift surfaces instead of rendering zeros.

3. Fee divergence — retire applies_to_providers

Chosen side: retire it. It stores 'stripe' / 'manual', which are not provider slugs — getRevenueOverview has to reconstruct those two labels from stripe_payment_intent_id to match against it, and the moment a real slug (paypal) meets that array the answer is 0%. It is an artifact of the Connect-only era, and populating it with eight real slugs would leave the same fact duplicated in a per-tenant column and in PROVIDER_CAPABILITIES, free to drift per tenant.

There is a third reader the issue does not name: get_platform_revenue (supabase/migrations/20260617120000_get_platform_revenue.sql:62-77) applies the identical provider = any(applies_to_providers) test, so /platform/revenue under-reports platform fees on every non-Stripe sale as well. Three screens, three answers. All three move together or the fix is cosmetic.

Approach. Add a bearsPlatformFee capability to ProviderCapabilities, true wherever the platform actually takes a cut — stripe (application fee), paypal / lemonsqueezy / binance (platform-settled, school paid out manually), solana / solana_subs (split on-chain from revenue_splits) — and false for manual and binance_personal, where the money never touches a platform account. That is the same fact applies_to_providers was reaching for, expressed once, in the file the codebase already treats as authoritative for provider abilities.

Both TypeScript readers and the RPC then compute the school's share from the transaction's own school_percentage_snapshot (falling back to the tenant's current split), which is what getPayoutsOwed uses — so the school-facing net revenue and the platform-facing netOwed are the same arithmetic on the same inputs and reconcile by construction, not by coincidence. applies_to_providers stays as a column (no destructive migration) but stops being read; the migration comments it as deprecated.

4. Rounding residue and payout idempotency

• Residue. Round each transaction's share to the currency's minor unit before accumulating, half-up, in a shared roundMoney helper. Half-up on the school's share means a tie rounds toward the school, so the platform never systematically rounds in its own favour. Sums are re-rounded on the way out to clear float dust.
• Epsilon. netOwed / overpaid floor at < 0.005, and mark-payout-paid-dialog.tsx gates its button on the same epsilon rather than <= 0. Both sides of the same threshold, so a row rendering $0.00 can never stay actionable.
• Idempotency. payouts.idempotency_key TEXT + CREATE UNIQUE INDEX … WHERE payout_method = 'manual' AND idempotency_key IS NOT NULL. The dialog generates one key per open (nanoid, not crypto.randomUUID — the latter is undefined on plain HTTP, which is how this app runs locally) and clears it on close, so a double submit, a reload, a second tab and a server-action retry all collapse to one row. markPayoutPaid treats a 23505 conflict as { status: 'ok' } rather than an error: the wire went out once, which is the outcome the operator asked for.

Risks

• Units (§1) are the real risk. Each provider's refund payload is normalized in its own mapper and asserted per provider in tests, with the expected major-unit value written out literally. The absent-amount and mismatched-currency paths both degrade to today's full-refund behaviour, so a mapper that is wrong in an unanticipated way fails to the status quo rather than to silent under-recording.
• §3 moves a visible number. Every school selling through PayPal / Lemon Squeezy / Binance / Solana will see its net revenue drop by the platform fee — that figure was wrong, and it was wrong in the school's favour. The platform-revenue page moves the other way. No data is rewritten; only the arithmetic over it.
• §1 changes the shared dispatcher, which the legacy Stripe route also calls. The refund branch is the only one touched, and Stripe refunds do not route through it.
• refunded_amount is additive with a default, so every existing row reads as unrefunded and no balance shifts on deploy.

Test plan

tests/unit/ (Vitest), baseline 370 green:

• payouts-owed.test.ts — fractional cases the existing 31 all avoid: 49.99 @ 80% fully paid settles at exactly 0; 33.33 @ 70%; a partial refund reduces grossOwed by the refunded slice only; a full refund keeps its current exclusion.
• webhook-dispatch.test.ts — a partial refund records the slice, leaves the row successful, and does not revoke the entitlement; a full refund does both; two distinct partials accumulate; an absent amount and a mismatched currency each fall back to full.
• Provider mappers — PayPal, Lemon Squeezy and Binance each emit amount in major units from a realistic payload, with the cents conversion asserted for LS.
• Reconciliation — a PayPal sale produces the same split from the school-facing revenue path and from computeOwedBalances.
• markPayoutPaid — direct coverage at the netOwed === 0 boundary (it has none today), plus the duplicate-key path returning { status: 'ok' }.

The migration is validated locally inside a BEGIN/ROLLBACK and will not be pushed to cloud in this PR.

[guillermoscript]

Follow-up on §2 while verifying the fix: the blast radius is more than double what this issue records.

§2 names four references across two pages. A static guard added to the suite (tests/unit/phantom-column-guard.test.ts, which walks the source for PostgREST chains against the money tables and checks every column against lib/database.types.ts) reports seven references across five pages. The three that were not known:

• app/[locale]/dashboard/admin/page.tsx — the admin dashboard's recent-transactions list
• app/[locale]/dashboard/admin/users/[userId]/page.tsx — a user's payment history
• app/[locale]/platform/tenants/[tenantId]/page.tsx — the platform tenant detail page

All three order by (or select) transactions.created_at. Ordering alone is enough — PostgREST rejects the whole request with 42703 either way, confirmed against the running API:

$ curl "…/rest/v1/transactions?select=*&order=created_at.desc&limit=1"
{"code":"42703","message":"column transactions.created_at does not exist"}

Each destructures { data } with no error check, so transactions comes back null and the page renders its ordinary empty state — "No transactions yet." Three more screens telling a school, or an admin looking at a specific student, that there were never any payments. Same silent-failure signature as the analytics page, which is why none of them were noticed.

All five are fixed in #565, including the two render sites that formatted transaction.created_at into a date.

Two other corrections to the issue text, both also in the PR:

1. §2's teacher revenue page does not render zeros — it 500s. Finish the #533 pagination sweep — eight unbounded reads remain #548 moved that read onto fetchAllRows, which throws on a page error. The silent-$0.00 symptom belongs to the admin analytics page alone.
2. §3 has three divergent readers, not two. get_platform_revenue (20260617120000:62-77) applies the identical provider = any(applies_to_providers) test, so /platform/revenue under-reported platform fees on every non-Stripe sale too.

On §3 the side chosen is retire applies_to_providers, for the reason the issue suggests — it names stripe/manual rather than the real provider slugs, so getRevenueOverview had to reconstruct those two labels from stripe_payment_intent_id and any real slug matched nothing. Whether a platform fee is taken is a property of the provider, not of the tenant, so it now lives in one place as ProviderCapabilities.bearsPlatformFee. All three readers additionally take the rate from each transaction's own school_percentage_snapshot — the same input getPayoutsOwed uses — so the school-facing and platform-facing figures reconcile by construction rather than by coincidence.

[guillermoscript]

Closed by #565, merged as d61fd85.

All four sections are fixed and all eight acceptance criteria are met. Three corrections to this issue's own account of the problem, each documented in the PR:

• §2 affected five screens, not two. Ordering by transactions.created_at is enough to make PostgREST reject the request — the column need not be selected — so the admin dashboard's recent-transactions list, the admin user-detail payment history and the platform tenant-detail page were broken the same way and showed their ordinary empty state instead of an error.
• §2's teacher revenue page did not render zeros; it 500'd, because Finish the #533 pagination sweep — eight unbounded reads remain #548 had moved that read onto fetchAllRows, which throws. The silent-$0.00 symptom belonged to the admin analytics page alone.
• §3 had three divergent readers, not two — the get_platform_revenue RPC applied the identical applies_to_providers test, so /platform/revenue under-reported platform fees on every non-Stripe sale.

On §3 the side chosen was to retire applies_to_providers, for the reason this issue suggested: it stored stripe/manual labels rather than provider slugs. Whether a platform fee is taken is a property of the provider, so it now lives once, as ProviderCapabilities.bearsPlatformFee.

Remaining work, not covered by this issue:

• The migration is local only — it still needs applying to cloud.
• No browser verification was done; §3 moves a visible revenue figure for every school on a platform-settled provider.