Red team — subscription + session-ledger target model

Reviewed 16 Sep 2026 against the real Setpoint tree (/Users/Alvaro/cowork/setpoint), read-only. Attack surface: _target-model.md §1-§5 vs the shipped ledger, billing cycle, group-coaching and POS-bridge code.

1. Reservation without deduction

[FATAL] There is no lock left to take. Today the only over-booking invariant is physical: consume_package_session takes FOR UPDATE OF pps on the player_package_sessions bucket (supabase/migrations/20260824200640_package_session_ledger.sql:259) and refuses at zero. Under on_done, "reserved" becomes a COUNT over lesson_players on future lessons — a read with no row to lock. Two staff adding the same student to two groups in the same second both see 3-of-4 reserved and both insert. allow_overbook_reserved=false is stated as a setting with no enforcement mechanism anywhere in the proposal. It needs either a real reservations row with a unique/exclusion constraint or pg_advisory_xact_lock(player_package_id) around every roster insert.
[FATAL] get_player_package_remaining_sessions is the live over-booking hole. It sums pps.sessions_remaining (supabase/migrations/20260803101048_get_player_package_remaining_sessions.sql), not the ledger, and apps/admin/src/app/api/admin/lessons/recurring/route.ts:181 uses it to cap a series. Under on_done that number stops moving until somebody checks in, so the recurring generator will enrol a 4-session student in all 13 weekly occurrences. This is not a hypothetical: it is the existing cap, reading the wrong source, the day the flag flips.
[FATAL] A roster seat has no package id. create_lesson_atomic only writes lesson_players.player_package_id when payment_method='package' (ledger migration, lesson_players INSERT block). §3's generated rosters have no such selection step. "Reserved = seats on future lessons covered by the package" is therefore uncomputable for exactly the seats the model is about. Gold B Mon+Wed on Essential reserves 8-9 seats a month against 4 purchased and nothing binds either seat to a package row.
[HIGH] Pay-per-seat joins bypass packages entirely. confirm_group_lesson_seat and confirm_group_lesson_seat_from_hold (supabase/migrations/20260618000000_group_coaching_academy_sessions.sql:308,350) never call consume_package_session and never set player_package_id; they set paid_amount and payment_method='stripe'. Those are the OpenRally/TPS joins. Under on_done they create scheduled seats the ledger will want to consume at check-in — charging a student a session they already paid cash for.
[HIGH] Holds vanish. release_group_lesson_hold DELETEs the lesson_players row. Put attendance_state on that row and the audit trail of a released reservation is deleted with it; the proposed delta-0 release row has nothing to point at.
[HIGH] Private lessons consume at the lesson level. create_lesson_atomic's final block consumes p_lesson_player_package_id against lessons.player_package_id, not a player row. attendance_state lives on lesson_players. For a private lesson there may be no player row carrying the package, so on_done has no defined trigger point for the private path.
[MED] Packages stop exhausting. trg_check_package_exhaustion fires AFTER UPDATE OF sessions_remaining only (00000000000000_initial_schema.sql:7892). Under on_done a fully-reserved package stays active with sessions_remaining intact, so every status='active' gate in the app says "sessions available" when all four are spoken for.
[MED] Two cancellation policies. Money cancellation already runs off cancellation_policies.applies_to_lessons + free_cancellation_hours (20260513095629_coach_booking_openrally_rpcs.sql:158-183). §2 adds an independent 24h session rule via student_cancel_rule_individual. A club that sets 48h gets 24h for sessions and 48h for refunds, silently.
[MED] Capacity is checked, reservations are not. lesson_capacity_exceeded guards max_pax per lesson. Nothing guards the per-student period total, which is the new invariant. It has to be checked in the same statement that inserts the seat, not in a later report.
[LOW] Credits path is untouched and inconsistent. record_credit_transaction still debits at add time for payment_method='credits'. Two payment rails, two moments of truth, on the same roster.

2. Ledger reasons, the CHECK, and the append-only invariant

[FATAL] release as a delta-0 row breaks the ledger's stated contract. DEC-319/320 and the migration header are explicit: every ledger row is written in the SAME transaction as a counter mutation, and player_package_session_id attributes it to a bucket. A release mutates no counter and belongs to no bucket — which bucket does it release from, when bucket selection only happens at consume time? The row would be an unattributable orphan in the one table the club is supposed to trust in a dispute. Reservations do not belong in an append-only balance ledger; they belong in the seat row or a reservations table.
[FATAL] Two new consume reasons split every existing reader. reason is a CHECK'd union (20260825073459_backfill_balance_reason.sql) mirrored as a literal TypeScript union in apps/pos/src/types/database.ts, apps/terminal/src/types/database.ts and academy-payments-types.ts. Adding no_show_consume and student_cancel_consume means utilization reports that filter reason='consume' silently stop counting no-shows: the balance drops while "sessions used" does not. Either every reader is updated in the same PR, or — better — keep one consume reason and put the flavour in metadata, which §2 already does for no-shows anyway. The proposal is internally inconsistent on this point.
[HIGH] balance_after >= 0 is fine, the ordering is not. balance_after is written from a FOR UPDATE-held bucket. Delta-0 rows inserted outside that lock can interleave and record a balance_after that was never true. If release ships at all it must take the same bucket lock, at which point it is cheaper to not ship it.
[HIGH] refund is the wrong reason for the migration's compensating rows (see §3). 20260825073459 exists precisely because reusing an operator-facing reason for machine-reconstructed history "would actively mislead" in a dispute. The switch would repeat that mistake with refund.
[MED] Renewal boundary is undefined. reset_package_sessions zeroes sessions_used at renewal. A seat reserved on 30 Sep for a lesson on 2 Oct consumes under which period? Deduct-on-add made this unambiguous; on_done does not, and the answer decides whether October's 4 sessions are really 4.
[MED] Reserved has no index. Counting reserved seats per student per period means scanning lesson_players joined to lessons by start_time on every roster add and every balance render. Needs a covering index before it is a hot path.
[LOW] The backfill's idempotence key is bucket-scoped. backfill_package_session_ledger skips buckets that already have any row. Any new backfill in this project must not reuse that guard, or it will no-op on every package the ledger already covers.

3. The backfill / flag switch

[FATAL] Compensating refund rows destroy the link the new model needs. The ledger migration's own header states refund_package_session NULLs lessons.player_package_id. If the switch calls it (or mimics it), the evidence of which package covers each future lesson is erased at exactly the moment the reservation model starts depending on it. The switch would eat its own inputs. Write a dedicated ledger_mode_switch reason that reverses the balance without touching the link.
[FATAL] no_show inference will libel months of real attendance. checked_in_at is only written by the PWA check-in; attended is a separate nullable boolean written by the coach-feedback RPCs (20260622033935_add_feedback_to_lesson_players.sql:5, 20260707082200). Most historical rows have both NULL. "Otherwise → no_show" therefore labels ordinary attended, already-paid lessons as no-shows. Rule must invert: past consumed seats default to attended; no_show only inside a date window where check-in was demonstrably in use.
[HIGH] Re-typing Growth in place changes every existing student. lesson_packages.package_type is a template column; every player_packages row points at it by package_id. Flipping session_bank → recurring changes expiry and renewal semantics retroactively for students who bought the old thing. Also billing_period CHECK is ('monthly','quarterly') at template level and 20260902173637 makes billing require it. Create a new Growth-recurring template row and migrate students; never re-type.
[HIGH] 62 packages with expires_at NULL fall off every roster. §3's rule is "session bank with expires_at >= date". NULL fails that comparison, so 62 students become "needs package" and are excluded from generated rosters on day one. get_player_package_remaining_sessions treats NULL as never-expiring; the two rules disagree.
[HIGH] 9 students with >1 active package have no tie-break. consume_package_session takes the package id from the caller. Reservation-by-coverage has no caller. Define deterministic selection (soonest expires_at, then oldest created_at) and persist the chosen id on the seat, or the same seat will attribute to different packages on successive renders.
[MED] ledger_mode on the facility is the wrong grain. Packages and academies span facilities; Pad Thai Padel runs several sites. A student training at two sites gets half their seats on each rule. Put the flag on the academy or the package template.
[MED] Dry-run and commit are not the same snapshot. Between Jose reviewing the report and pressing commit, coaches keep checking people in. The commit must run as one transaction keyed to the report's generation timestamp and refuse rows that moved.
[MED] The switch is not reversible. No rollback is specified. A compensating-rows migration that cannot be un-done should ship with its inverse written and tested first.
[LOW] Future lessons already cancelled. Seats on lessons with status='cancelled' may already have been refunded; the switch must not refund twice. Idempotence has to be per-seat, not per-package.

4. Billing

[FATAL] There is no saved payment method to charge. stripe_customer_id exists on customers (initial_schema:3636, written by apps/admin/src/app/api/v1/_lib/stripe-customer.ts), but a repo-wide grep for setup_intent, SetupIntent, off_session and save_payment_method across apps/ and supabase/ returns nothing. No mandate is ever collected and no default payment method is ever stored. "PaymentIntent off-session on the saved payment method" is a feature that does not exist yet, not a change to process-package-billing. Mandate collection is the long pole and it is not in the plan.
[FATAL] Most Thai academy money is not chargeable off-session. The POS bridge maps tender to cash | card | other (20260901170806:196) and the academy payments UI labels PromptPay QR. Cash, transfer and PromptPay cannot be auto-charged at all. The "charge on the 1st" design covers card students only; everyone else stays on payment_requests + reminders, which is what already ships. The doc should say so rather than imply full automation.
[HIGH] Hourly re-fire becomes a double charge. dispatch_package_billing runs 0 * * * * and net.http_post is fire-and-forget; the migration comment says repeat delivery is harmless because the payment_requests unique index catches it. On 23505 the function deliberately continues (process-package-billing/index.ts, duplicate branch) to finish a prior delivery's work. Put a charge after that point and a re-fire while the first PaymentIntent is still processing charges twice. Mandatory: a Stripe idempotency key derived from (player_package_id, period_start) plus a persisted charge-attempt row consulted before the API call.
[HIGH] SCA/3DS turns technical failures into past_due. Thai issuers routinely require 3DS per transaction; an off-session PI returns authentication_required. Under §4 that student is past_due on day 3 and frozen on day 7 for a reason that is not non-payment. Needs an on-session retry link and a distinct state before the dunning clock starts.
[HIGH] The bridge has nowhere to land. process-package-billing writes amount_amount: template.price_amount unconditionally. There is no bridge_tier or bridge_days column on payment_requests and no branch that computes them. As written the bridge is a spreadsheet rule, not a shipped behaviour.
[HIGH] past_due stops billing entirely. dispatch_package_billing selects WHERE pp.status='active'. Setting past_due on day 3 means the student is invisible to next month's dispatcher while still attending until day 7. Either past_due stays billable or the dispatcher's filter widens — and process-package-billing early-returns package_not_active before advancing anything.
[MED] paused leaves a stale anchor. A paused package early-returns without advancing next_billing_date, so on resume the hourly cron fires immediately and raises a request for the period that was paused, with reminders computed off a due date in the past. Resume must re-anchor.
[MED] Day-26 cannot be a reminder_offset. Offsets are relative to due_date, and due_date = period_start + default_due_days (7). An offset of −5 lands on the 3rd, not the 26th. The pre-renewal notice needs its own dispatcher.
[MED] Partial payments double the POS mirror. record_academy_payment_in_pos_row fires AFTER INSERT per payments row and mirrors payments.amount verbatim as a complete order. Two part-payments of one month produce two POS orders, each looking like a full academy sale, against a single payment_requests.amount_amount. Nothing reconciles the two.
[MED] Stripe Subscriptions are already live for some rows. process-package-billing skips any package with stripe_subscription_id because invoice.paid already drives reset_package_sessions (AC-41). Choosing scheduled PaymentIntents is right, but the doc must state that the existing subscription rows keep their own path and are never migrated silently.
[LOW] No POS location, no mirror. The bridge raises a WARNING and returns when a facility has no active pos_locations row. Academy revenue then never reaches the POS reports, quietly.
**[LOW] reminder_offsets default {-3,0,3,7} plus a day-1 charge means three chasers inside the first week. Worth tuning before it lands on real students.

5. Roster follows package

[HIGH] Never evict a paid seat. Seats created by confirm_group_lesson_seat carry paid_amount and payment_intent_id. §3's lapse rule ("active package covers the date") would remove paying, non-package customers from TPS-published group sessions. The sync must hard-exclude any seat with paid_amount > 0, a payment_intent_id, or payment_method other than package.
[HIGH] A 03:00 job rewrites a roster the coach already planned. Removing players from future lessons the night before changes what the coach sees in the PWA with no notification specified, and lesson_players changes already emit webhooks (webhook-delivery-worker, emit triggers from 20260622033935). TPS consumers will see a roster change they cannot explain. Removal needs a notice to coach and student, and a same-day cutoff.
[MED] Two slots per group break the series cap. apps/admin/src/app/api/admin/lessons/recurring/route.ts:214-219 includes "the first N occurrences in date order" where N is the remaining balance. For a Mon+Wed group on 4 sessions/month that enrols the student for the first two weeks and nothing after — the wrong shape for a fixed-slot subscription, which wants 4 of the ~8-9 slots spread across the month, or an explicit second-slot price.
[MED] Overrides are invisible to money. override_reason/override_by with no ledger row means a favour granted at the desk never shows up as a session owed or comped. Give it a reason code so it can be billed or written off deliberately.
[MED] Grace and freeze can fight. Day-7 freeze blocks new roster adds; roster_grace_days default 7 removes existing seats. Both land on the same day for a package that went past_due on day 3, so a student loses everything at once with one notification.
[LOW] Two places name the coach. academy_groups.coach_id and academy_group_slots.coach_id. Pick the slot and derive the group's, or they drift.

6. Preconditions for Pad Thai Padel `on_done` on 1 Nov 2026

get_player_package_remaining_sessions returns purchased − consumed − reserved, and api/admin/lessons/recurring uses that; otherwise the series cap over-books from hour one.
Every roster seat carries a player_package_id chosen by a deterministic rule, written at insert, with the reservation counted under a lock in that same statement.
A dedicated ledger_mode_switch reason exists, reverses balance without NULLing lessons.player_package_id, and its inverse migration is written and tested.
The past-lesson inference defaults to attended, with no_show only inside a verified check-in window, and Jose signs a dry-run whose commit is one transaction against that snapshot.
Growth exists as a NEW recurring template; the 9 multi-package students and the 62 NULL-expires_at packages are resolved by hand before the flag moves.
Check-in actually happens every session — the whole model rests on it. If coaches do not check in, auto_mark_no_show_hours decides revenue by default, and that must be a conscious club setting, not a fallback.
Paid, non-package seats are excluded from roster sync, and the removal job sends notice.
ledger_mode is scoped to the academy or template, not the facility.
Billing changes ship separately from ledger changes. Do not move money and move the invariant in the same release.

Verdict

KEEP: the subscription states, the day-1/3/7 dunning cadence, the bridge tiers, roster-follows-package as a concept, and the ledger itself as the balance's single source of truth. CHANGE: reservations must be a locked, package-attributed fact on the seat row, not a computed count and not a delta-0 ledger row; one consume reason with the flavour in metadata; the switch needs its own reason and an inverse. DROP: off-session auto-charging on the 1st for this release — no mandate is ever collected anywhere in Setpoint, and most Thai academy money is cash, transfer or PromptPay, which cannot be charged at all. THREE CHANGES THAT MOST REDUCE RISK: (1) make get_player_package_remaining_sessions reservation-aware and lock the reservation at insert, before any flag flips; (2) default the backfill's past lessons to attended, never no_show, and give the switch a ledger_mode_switch reason that does not NULL the package link; (3) ship billing and the ledger as two separate releases.