EPIC: Payout & non-payment enforcement integrity — revenue-split accuracy and edge cases

[guillermoscript]

EPIC: Payout & non-payment enforcement integrity — revenue-split accuracy and edge cases

Summary

We just shipped manual payout tracking (/platform/payouts — tracks what the platform owes each school for PayPal/Binance Pay/Lemon Squeezy sales, since those providers settle 100% into the platform's own account with no automatic split) and live-verified it end-to-end (PayPal $10 sale → $8.00 owed at 80% school split → Mark as Paid → $0.00, payouts row inserted correctly).

Auditing what surrounds it — specifically "what happens if a school just stops paying us" — surfaced two separate classes of real gaps, code-verified 2026-07-24:

1. Non-payment has no functional consequence. A school that stops paying keeps 100% of its existing students/courses/access forever — there is no lockout, no grandfathering decision, just... nothing happens to existing usage. Only new signups/course creation get blocked.
2. The payout math itself has money-accuracy bugs — revenue split isn't time-sliced (plan changes retroactively reprice historical payouts), transaction amounts get summed across currencies with no grouping, and there's no clawback path if a paid-out transaction is later refunded.

None of this blocks what's already shipped — it's an accounting/enforcement hardening pass before this is trusted for real money at scale.

---

Part 1 — Non-payment has no real consequence

1.1 Downgrade-to-free never touches existing courses/students/entitlements 🔴

• downgradeTenantToFree() (lib/billing/downgrade-tenant.ts:33-59) — triggered by Stripe's customer.subscription.deleted webhook (app/api/stripe/platform-webhook/route.ts:171) or the expire-platform-subscriptions cron (app/api/cron/expire-platform-subscriptions/route.ts:184,205) — only ever writes platform_subscriptions.status, tenants.plan/billing_status/billing_period_end, and revenue_splits. It never queries or writes courses, enrollments, or entitlements.
• Course/lesson access is gated exclusively by has_course_access() (supabase/migrations/20260516120000_entitlements_table.sql:64-78), which checks only entitlements.status='active' + expiry — no reference to tenants.plan or any limit, ever.
• checkPlanLimits (lib/billing/plan-limits.ts:110-137) is only called from billing-flow actions (app/actions/admin/billing.ts:178,246,434,488) — never from the enrollment or course-creation path. The only creation-time soft caps are new-student-join (app/actions/join-school.ts:36-66) and new-course-create (app/actions/teacher/courses.ts:71-101) — both block only new records, never touch existing ones.
• The downgrade notification email (lib/email/templates/plan-downgraded.ts:16) says "your courses and data are safe" — true only because nothing acts on them at all, not because of deliberate grandfather logic. No code decides which students/courses stay active past the new limit; a school with 10,000 students on free plan (50-student limit) keeps all 10,000 fully functional, indefinitely.
• Wanted: a deliberate decision, not an accident — either (a) explicitly grandfather existing usage forever (document it, remove the misleading "over limit" UI urgency), or (b) build real enforcement (e.g., read-only mode past N days over limit, or a hard student-count cutoff with clear admin-facing warning before it bites).

1.2 No super-admin visibility into past-due / at-risk schools 🟠

• app/[locale]/platform/billing/page.tsx:7,26-40 only lists platform_payment_requests (manual bank-transfer requests) — a Stripe subscriber that goes past_due or gets canceled produces no row there at all.
• app/[locale]/platform/tenants/page.tsx:27 selects plan/status but not billing_status — a super admin browsing tenants cannot see who's past-due.
• No page/query anywhere aggregates platform_subscriptions.status='past_due' or tenants.billing_status='past_due' for admin visibility (confirmed via full grep of app/[locale]/platform/**).
• Wanted: a "Billing health" panel (mirrors the payouts-owed page pattern) — schools currently past_due, days into grace, at risk of imminent downgrade.

---

Part 2 — Payout math accuracy

2.1 Revenue split percentage isn't time-sliced — plan changes retroactively reprice historical payouts 🔴

• computeOwedBalances (lib/payments/payouts-owed.ts:48-83) takes one current schoolPercentage per tenant and applies it to the entire all-time sum of that tenant's platform-settled transactions. It has no concept of "what split was in effect on the day of each sale."
• Every plan change (upgrade, downgrade, the free-plan auto-downgrade from Part 1, or a super-admin forceTenantPlanChange) rewrites revenue_splits for that tenant (lib/billing/downgrade-tenant.ts:52-59, app/api/stripe/platform-webhook/route.ts:87-103, etc.) — and the next payout calculation silently reprices every historical transaction, not just new ones, at the new percentage.
• Concrete failure: a school does $10k on the business plan (0% platform fee) then gets auto-downgraded to free (10% fee per supabase/migrations/20260221020000_subscription_expiry_cron.sql:71-72) — the payouts page will then show ~$1k less owed on that already-settled $10k than was actually agreed at the time of sale.
• Wanted: snapshot the split percentage onto each transactions row at creation time (or a revenue_splits history table keyed by effective date), and compute owed amounts per-transaction using the split that was live when that transaction happened — not today's split applied retroactively.

2.2 Payouts-owed sums transaction amounts across currencies with no grouping or conversion 🔴

• transactions.currency is a real, populated currency_type enum column (confirmed in lib/database.types.ts), but getPayoutsOwed() (app/actions/platform/payouts.ts:32-36) selects tenant_id, payment_provider, amount — currency is dropped entirely — and computeOwedBalances sums raw amount with no currency grouping.
• A tenant with both USD and EUR PayPal sales gets a single meaningless summed total (e.g. "$18.00" that's actually $10 + €8).
• markPayoutPaid() (app/actions/platform/payouts.ts:65) then hardcodes currency: 'usd' on the inserted payouts row regardless of what was actually collected — the record itself is wrong the moment any non-USD sale is involved.
• Wanted: group grossCollected/netOwed by currency (a tenant can owe you $40 AND €15 — those are two numbers, not one), and record the correct currency on each manual payout row. Decide whether to support/display multi-currency conversion or just keep totals strictly separated per currency — either is fine, silent mixing is not.

2.3 No refund/chargeback clawback path 🟡

• markPayoutPaid is a one-way ratchet — once a payout is recorded, there is no code path that re-checks whether the underlying transactions it was based on later flip to refunded/failed.
• If a PayPal sale gets refunded after you've already paid the school their 80%, the payouts page has no way to reflect that you're now owed back from a future payout (it'll just show $0 owed going forward, silently absorbing the loss).
• Wanted: when computing netOwed, subtract refunded amounts from grossCollected (or a dedicated refund-clawback line), so a refund after payout reduces what's owed on the next payout cycle instead of vanishing.

2.4 No overpayment guardrail; schools can't see their own payout status 🟡

• markPayoutPaid (app/actions/platform/payouts.ts:57-75) validates only amount > 0 — no check against netOwed, so a typo (e.g. an extra zero) records silently with no confirmation step beyond the dialog's pre-filled suggestion.
• There is no tenant-admin-facing view of payout status — school admins have no way to see "the platform owes us $X" or "we were paid $Y on this date" themselves; only the super admin sees this today (deliberately deferred when this feature shipped, flagging here so it's a tracked decision rather than forgotten scope).
• Wanted: soft-warn (not block) when recording an amount that differs significantly from netOwed; add a read-only tenant-admin payout-history view.

---

Part 3 — Deployment gap

3.1 Manual-payouts migration is local-only 🟡

• supabase/migrations/20260724120000_manual_payouts.sql has been applied and verified locally but not yet pushed to the cloud project; lib/database.types.ts reflects the local schema only.
• Wanted: apply to cloud, regenerate types against cloud, confirm RLS policies on the extended payouts columns behave identically.

---

Phasing — sub-issues

Phase 1 — non-payment enforcement (decide the actual business rule):

• Non-payment produces no access enforcement — over-limit tenants keep full access indefinitely #494 Non-payment produces no access enforcement — over-limit tenants keep full access indefinitely (§1.1)
• No super-admin dashboard for past-due / at-risk schools (billing health) #495 No super-admin dashboard for past-due / at-risk schools (billing health) (§1.2)

Phase 2 — payout money-accuracy (real accounting bugs):

• Revenue split isn't time-sliced — plan changes retroactively reprice historical payouts #496 Revenue split isn't time-sliced — plan changes retroactively reprice historical payouts (§2.1)
• Payouts-owed sums transaction amounts across currencies with no grouping #497 Payouts-owed sums transaction amounts across currencies with no grouping (§2.2)
• No refund/chargeback clawback for manual payouts already marked paid #498 No refund/chargeback clawback for manual payouts already marked paid (§2.3)
• No overpayment guardrail on Mark as Paid + no tenant-facing payout visibility #499 No overpayment guardrail on Mark as Paid + no tenant-facing payout visibility (§2.4)

Phase 3 — deployment:

• Apply manual-payouts migration to cloud + regenerate types #500 Apply manual-payouts migration to cloud + regenerate types (§3.1)

Acceptance criteria

• A documented, deliberate answer exists for "what happens to existing students/courses when a school stops paying" — whether that's explicit grandfathering or real enforcement.
• A super admin can see, at a glance, which schools are past-due/at-risk without querying the DB directly.
• Payout calculations use the revenue split that was in effect at the time of each transaction, not the tenant's current split.
• Payout totals never silently mix currencies. — true on the super-admin view (Payouts-owed sums transaction amounts across currencies with no grouping #497), false on the school-facing one (School-facing payout history sums across currencies and labels the total USD (#497 follow-up) #531)
• A refund on an already-paid-out transaction is reflected in a future payout instead of disappearing.
• Manual payouts migration is live on the cloud project.

Key files

lib/billing/downgrade-tenant.ts · lib/billing/plan-limits.ts · lib/payments/payouts-owed.ts · app/actions/platform/payouts.ts · app/[locale]/platform/payouts/page.tsx · app/[locale]/platform/billing/page.tsx · app/[locale]/platform/tenants/page.tsx · app/api/stripe/platform-webhook/route.ts · app/api/cron/expire-platform-subscriptions/route.ts · supabase/migrations/20260724120000_manual_payouts.sql · supabase/migrations/20260216212440_create_revenue_infrastructure.sql

Audit performed 2026-07-24 (code-verified via two independent Explore sweeps + live browser verification of the payouts page against real local data). Related: #458 (plan-change lifecycle epic — shares the downgrade/webhook code paths touched here).

---

Phase 4 — gaps found verifying the delivered state (added 2026-07-24)

All seven sub-issues above are merged to master and live on the cloud project. A verification pass over the shipped code (see the close-out comment for method) surfaced edge cases the original audit did not cover. All eight are attached as native sub-issues of this epic.

Enforcement (§1 follow-ups)

• Lesson/exercise/exam pages have no access gate — #494 cutoff is a no-op there and any tenant member can read paid content #509 Lesson/exercise/exam pages have no access gate — the cutoff is a no-op there, and any tenant member can read paid content by URL 🔴 security
• Access-cutoff reconciliation depends on a cron that may never run on Dokploy (#494 follow-up) #513 Access-cutoff reconciliation depends on a cron that may never run on Dokploy 🔴
• Access cutoff: single warning email, no in-app signal, undocumented tenant-wide blast radius (#494 follow-up) #517 Single warning email, no in-app signal, undocumented tenant-wide blast radius 🟡 — blocked by Access-cutoff reconciliation depends on a cron that may never run on Dokploy (#494 follow-up) #513

Payout accuracy (§2 follow-ups)

• Payout clawback double-subtracts refunds and claws back sales never paid out (#498 follow-up) #511 Clawback double-subtracts refunds and claws back sales that were never paid out 🔴
• Subscription refunds never flip transaction status, so they are never clawed back (#498 follow-up) #515 Subscription refunds never flip transaction status, so they are never clawed back 🟡 — blocked by Payout clawback double-subtracts refunds and claws back sales never paid out (#498 follow-up) #511
• Payout overpayment is unrecoverable and the mismatch guard is skipped on retry (#499 follow-up) #516 Overpayment is unrecoverable and the mismatch guard is skipped on retry 🟡 — blocked by Payout clawback double-subtracts refunds and claws back sales never paid out (#498 follow-up) #511
• Split snapshot is written at a single call site with no DB backstop (#496 follow-up) #512 Split snapshot is written at a single call site with no DB backstop 🟠
• Billing-health dashboard misses past-due subscriptions and scheduled-cutoff tenants (#495 follow-up) #514 Billing-health dashboard misses past-due subscriptions and scheduled-cutoff tenants 🟠

Suggested order

1. Lesson/exercise/exam pages have no access gate — #494 cutoff is a no-op there and any tenant member can read paid content #509 first regardless of everything else — it is a live paywall bypass, not just an enforcement gap, and it predates this epic.
2. Access-cutoff reconciliation depends on a cron that may never run on Dokploy (#494 follow-up) #513 next: it is a yes/no operational question (is anything on the Dokploy host actually calling /api/cron/*?). If the answer is no, Non-payment produces no access enforcement — over-limit tenants keep full access indefinitely #494 has never fired in production and neither has platform-subscription expiry from Auto-expire lapsed manual-transfer platform subscriptions (cron) #462.
3. Payout clawback double-subtracts refunds and claws back sales never paid out (#498 follow-up) #511 unblocks Subscription refunds never flip transaction status, so they are never clawed back (#498 follow-up) #515 and Payout overpayment is unrecoverable and the mismatch guard is skipped on retry (#499 follow-up) #516, so it gates the rest of the accounting work.
4. Split snapshot is written at a single call site with no DB backstop (#496 follow-up) #512, Billing-health dashboard misses past-due subscriptions and scheduled-cutoff tenants (#495 follow-up) #514 are independent and can land at any point.
5. Access cutoff: single warning email, no in-app signal, undocumented tenant-wide blast radius (#494 follow-up) #517 last, once Access-cutoff reconciliation depends on a cron that may never run on Dokploy (#494 follow-up) #513 confirms the scheduler exists to send reminders from.

Acceptance criterion "a refund on an already-paid-out transaction is reflected in a future payout" is now met: #511 (PR #521) corrected the arithmetic and #515 (PR #522) made subscription refunds reach it.

Phase 4 closed except #513, whose fix is open as PR #519 and needs repo secret CRON_SECRET + repo variable CRON_BASE_URL before it can run.

---

Phase 5 — gaps found reviewing Phase 4's shipped code (added 2026-07-25)

A second pass, this time reading the delivered code rather than the checkboxes, found three more. All attached as sub-issues.

• Lesson-checkpoint attempt route gates on a self-issuable enrollment row, bypassing entitlements and the access cutoff (#509 follow-up) #532 Lesson-checkpoint attempt route gates on a self-issuable enrollments row — bypasses entitlements and access_cutoff_at 🔴 security — Lesson/exercise/exam pages have no access gate — #494 cutoff is a no-op there and any tenant member can read paid content #509 and Non-payment produces no access enforcement — over-limit tenants keep full access indefinitely #494 follow-up: createAdminClient() means the 20260724150000 RLS backstop cannot apply, and enrollments INSERT RLS carries no entitlement predicate, so the gate is self-issuable
• School-facing payout history sums across currencies and labels the total USD (#497 follow-up) #531 School-facing payout history sums across currencies and labels the total USD 🟠 — Payouts-owed sums transaction amounts across currencies with no grouping #497's fix never reached the tenant-side view added by No overpayment guardrail on Mark as Paid + no tenant-facing payout visibility #499; the epic's "never silently mix currencies" criterion is false for the page schools actually read
• getPayoutsOwed reads the ledger unpaginated — a PostgREST row cap silently misstates payout balances #533 getPayoutsOwed reads the ledger unpaginated — a PostgREST row cap silently misstates balances 🟡 — latent (cloud holds 0 transactions today), but truncation is indistinguishable from a complete read and moves the balance in either direction

Also adopted into this epic:

• transactions RLS restricts rows but not columns — a student can self-insert a 'successful' sale #528 transactions RLS restricts rows but not columns — a student can self-insert a successful sale 🔴 security — filed separately; it forges both free enrollment and payout liability, so it belongs to the payout-integrity work here

Deployment gap

supabase/migrations/20260725110000_transaction_split_snapshot_backstop.sql (#512, PR #525) is on master but not applied to the cloud project — cloud schema_migrations stops at 20260725100000. Until it is pushed, transactions.school_percentage_snapshot remains caller-supplied in production and #512's fix is inert there. Same class as §3.1/#500.

Verified sound in this pass

Student page gating (requireCourseAccess + requireRowInCourse across all ten student course routes), the AI chat routes (user-scoped clients, so #509's RLS backstop covers them), computeOwedBalances arithmetic including the #511 and #516 reasoning, the #516 mismatch-retry interaction between the dialog and markPayoutPaid, and billing-health's three-population union (#514). 56/56 unit tests pass across payouts-owed, access-cutoff, billing-health.