Target model (shared source for scope, spec, mockups, red team)

Written 16 Sep 2026. Everything here must map onto the real Setpoint schema confirmed read-only on local Supabase (177 tables) and the 10 Sep 2026 exports. Column names are the real ones.

1. Subscription

• Monthly plans Essential (recurring, 4 sessions, 4,500 THB), Growth (today package_type='session_bank' with billing_period='monthly', 6 sessions, 9,500), Performance (recurring, 7 sessions, 14,500) become recurring subscriptions anchored to the 1st: player_packages.billing_anchor_day = 1, billing_mode = 'automatic', next_billing_date set. Growth gets a NEW recurring template row and students are migrated to it (red team: re-typing the template in place changes expiry semantics for everyone who bought the old one).
• Credit packs (Starter Pack 10 sessions / 90 days, Pro Pack 18 / 90, the 5-10-15 Coaching Session Packages / 365 days, specials) stay session_bank with expires_at; never prorated; nothing due on the 1st.
• States on player_packages.status today: active | exhausted | expired | cancelled. Target adds past_due and paused (CHECK widened by migration). No trialing.
  • active: paid for the current period, sessions available.
  • past_due: request unpaid by day 3; student keeps attending until the day-7 freeze; roster flag "past due". Still billable: the dispatcher selects active OR past_due (red team: today's WHERE status='active' would hide the student from the next cycle).
  • paused: planned absence declared before the 26th; no request on the 1st; slots are LOST (fixed-slot rule), nothing credited; roster seat released after the grace window; on resume next_billing_date is re-anchored to the next 1st (red team: otherwise the hourly cron bills the paused period).
  • cancelled: student or academy ended it; no future rosters; past attended lessons untouched.
  • expired: session bank past expires_at or recurring never renewed.
• Anchor: first cycle may be partial (bridge, see 4); every later cycle is current_period_start = 1st 00:00 facility-local, current_period_end = last day 23:59:59.

2. Session ledger: reservation separated from consumption

Today: create_lesson_atomic calls consume_package_session when a player is added (deduct on add). refund_package_session restores on cancellation. lesson_players.status = confirmed | held | cancelled. lesson_players.checked_in_at and attended exist but never touch the ledger. package_session_transactions.reason CHECK = purchase_grant | consume | refund | admin_adjust | backfill_balance | renewal_reset | expiry_forfeit | cancellation_forfeit; balance_after >= 0; source = live | backfill.

Target: a per-player attendance state on lesson_players (new column attendance_state, CHECK) and a reservation that does not deduct.

Balance = purchased − Σ consume rows (attended + no_show + student-cancel per rule) ± adjustments. Reserved = seats with attendance_state='scheduled' whose player_package_id points at the package: a locked fact written at insert (red team), capped so reserved + consumed ≤ purchased, and get_player_package_remaining_sessions becomes reservation-aware before any flag flips. Shown side by side: "6 · 2 used · 3 reserved · 1 free".

Ledger reasons: keep ONE consume reason and record the flavour in metadata.attendance (attended | no_show | student_cancel); no delta-0 rows (red team). Add ledger_mode_switch (with an inverse) for the one-time switch. Existing refund stays for staff reversals. Because package_session_transactions is append-only with balance_after, a reservation must never write a negative or provisional row.

Migration path from deduct-on-add:

1. Stop the deduction at add: create_lesson_atomic and the group-seat confirm RPCs stop calling consume_package_session; they set attendance_state='scheduled' instead (behind an ACADEMY-level flag academies.ledger_mode = 'on_add' | 'on_done', default on_add so no live club changes; red team: a facility flag splits a student who trains at two sites).
2. One-time reconciliation for Pad Thai Padel when the flag flips: for every consume row whose lesson is still in the future → insert a compensating refund row (source='backfill', note "ledger_mode switch") and set the seat to scheduled; for past lessons: lesson status='cancelled' → cancelled_by_academy (+ refund if consumed); otherwise → attended (never inferred no_show: only 52 of 698 seats were ever checked in, so absence of a check-in proves nothing); rows use reason ledger_mode_switch and keep the package link; dry-run report reviewed by Jose first.
3. Exhaustion check (check_package_exhaustion) moves from add-time to done-time; over-reservation is allowed up to purchased sessions + 0 (setting allow_overbook_reserved default false): a student with 4 sessions can hold at most 4 reserved-or-consumed seats in the period.

3. Roster follows package

• academy_groups gains a schedule template: weekday smallint[], start_time time, duration_minutes, preferred_court_id, class_id (link to the group class template), coach_id already exists. One group can have two slots (Gold B Monday + Wednesday) → child table academy_group_slots (group_id, weekday, start_time, duration, court_id, coach_id).
• Generating lessons from a slot (extends api/admin/lessons/recurring): each lesson gets academy_group_id; roster = every academy_group_members user whose package covers the lesson date: recurring with status in ('active','past_due') and current_period_start <= date <= current_period_end; or session bank with expires_at IS NULL OR expires_at >= date (62 live packages have NULL expiry) and remaining > 0. Package selection is deterministic and PERSISTED on the seat (lesson_players.player_package_id): soonest expires_at, then oldest created_at; the reservation is counted under pg_advisory_xact_lock(player_package_id) in the same statement that inserts the seat, and refused when reserved + consumed would exceed purchased. A member in two slots (Gold B Mon + Wed) on a 4-session plan gets 4 seats spread across the month by slot order, not the first two weeks (the second weekly slot is a priced add-on per the Bangkok packages proposal). Uncovered members appear as "needs package" and are excluded unless overridden: lesson_players.override_reason text, override_reason_code (comp | bill_later | trial), override_by uuid (audit row).
• Lapse: when a package leaves active (past_due, paused, cancelled, expired) → member flagged; roster_grace_days (default 7) counts from the lapse day, so a package past_due on day 3 is removed on day 10, after the day-7 freeze, not on the same day. Removal never touches past attended lessons and NEVER touches a paid non-package seat (paid_amount > 0, payment_intent_id set, or payment_method <> 'package': those are OpenRally/TPS pay-per-seat joins). The nightly job dispatch_roster_sync runs at 03:00, skips lessons starting within 24 h, notifies coach and student through the messaging lane, and writes audit rows.

4. Month-end billing

• Day 26 (facility-local): renewal notice for every active recurring package from its own dispatcher (dispatch_renewal_notices); it cannot be a reminder_offsets entry because offsets are relative to due_date, not the 1st.
• Day 1: process-package-billing creates the payment_requests row for the period (exists) and ISSUES it: a Stripe payment link (card / PromptPay) plus the pay-at-club instructions, sent by the reminder pipeline. No off-session charge in this release (red team: Setpoint collects no card mandate and most Thai academy money is cash, transfer or PromptPay). A saved-card off-session PaymentIntent is a per-student opt-in in a later release (D5): customers.stripe_customer_id exists, but no SetupIntent or saved payment method is collected anywhere today. Any charge call carries a Stripe idempotency key from (player_package_id, period_start) and a persisted attempt row, because the dispatcher re-fires hourly. Packages that already carry stripe_subscription_id keep their existing invoice.paid path and are never migrated silently. Stripe Subscriptions are not used: their proration fights the bridge rule.
• Day 3: unpaid → past_due; reminder. Day 7: booking freeze (student cannot be added to new rosters; existing seats stay until grace ends). Existing default_due_days=7 and reminder_offsets {-3,0,3,7} cover the cadence after the 1st.
• Bridge for mid-cycle starts: amount = round(price × min(floor(days_to_next_1st ÷ 7), 4) ÷ 4); tiers 3 weeks PRORATE75, 2 PRORATE50, 1 PRORATE25, 4+ full, ≤ 7 days wait for the 1st; applied once on the first payment_requests row (bridge_tier text, bridge_days int), then full. Session banks never bridged.
• "What is due" view reconciles three sets: scheduled-not-paid (reserved seats on a package with an unpaid request), attended-not-paid (consumed seats on unpaid request), paid-not-scheduled (paid period, zero reserved seats → churn risk).

5. Settings (facility-level, academy_payment_settings or a new academy_settings row)

ledger_mode (on_add | on_done), auto_mark_no_show_hours (null = manual), student_cancel_rule_group (deduct | release_if_24h), student_cancel_rule_individual (release_if_24h), roster_grace_days (7), pause_notice_day (26), freeze_day (7), past_due_day (3), reminder_day_before_1st (26), allow_overbook_reserved (false), proration_mode (none | weekly_quarters).