# 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.

| attendance_state | Meaning | Ledger effect | Who sets it |
|---|---|---|---|
| scheduled | on a future roster | reserved (no `consume` row); shown as Reserved | roster add (auto from group, or manual) |
| attended | checked in or marked done | `consume` row, delta −1 | coach check-in (PWA QR), manager "mark done", or auto at lesson end + N hours if the facility opts in |
| no_show | fixed slot missed | `consume` row, delta −1, `metadata.attendance='no_show'` | coach / manager "no-show", or auto when a lesson completes with no check-in (setting) |
| cancelled_by_academy | lesson cancelled or seat released by staff | released: no row, or `refund` if already consumed | lesson cancel cascade, roster remove |
| cancelled_by_student | student cancelled | group: `consume` (slot lost); individual: released if > `free_cancellation_hours` (24) else `consume` | student (web/app) or staff on their behalf |

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).