# 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 1. `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. 2. 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. 3. A dedicated `ledger_mode_switch` reason exists, reverses balance without NULLing `lessons.player_package_id`, and its inverse migration is written and tested. 4. 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. 5. 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. 6. 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. 7. Paid, non-package seats are excluded from roster sync, and the removal job sends notice. 8. `ledger_mode` is scoped to the academy or template, not the facility. 9. 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.