# 02 · Technical specification Written 16 September 2026. Companion to `_target-model.md` (intent), `_research-ledger-billing.md` / `_research-planning-groups.md` / `_research-crm-messaging-roles.md` (repo facts, every claim file:line), `04-decisions.md` (D1–D16, defaults assumed), `_redteam-ledger.md` (adversarial review of the ledger and billing model), `PHASE-1-SPEC.md` (Step 1 Academy Health, already specified, not repeated here). Repo under specification: `/Users/Alvaro/cowork/setpoint`. Nothing in it was modified to write this document. --- ## 1. Scope and principles ### 1.1 Feature letters The letters used by `04-decisions.md`. A is specified already in `PHASE-1-SPEC.md` and is referenced, not redesigned. | Letter | Feature | Spec sections | |---|---|---| | A | Academy Health (read-only worklist, `tc-284`) | 9 (persistent successor only) | | B | Groups with a schedule, roster follows package | 2, 6 | | C | Attendance state and the session ledger | 2, 3, 12 | | D | Subscription lifecycle: pause, past due, freeze, month-end cycle | 2, 4 | | E | Plans and packages: Growth re-platforming, bridge pricing | 2, 4, 12 | | F | Messaging: WhatsApp Cloud API, template keys | 2, 8 | | G | Card collection on the 1st: mandate, charge, dunning | 2, 4, 5 | | H | Planning drag-and-drop, waitlist | 2, 7 | | I | Leads and conversion | 2 | | J | What a player sees | 8, 10 | | K | Exports | 10 | | L | Audit log and role gates | 2, 11 | ### 1.2 Principles 1. **Extend, never restyle.** Every route, RPC, trigger and component named below already exists unless marked *new*. No file is rewritten to a different pattern because a newer one is nicer. 2. **Consumer-agnostic.** No table, column, setting, template key or test fixture names Pad Thai Padel or The Padel Society. Club-specific facts live in data, per `ac-academy-payments.ts` AC-50. 3. **Every money or session mutation is transactional and ledgered.** Balance changes are written inside the plpgsql RPC that mutates the counter, in the same transaction (DEC-320). Admin actions write `admin_audit_log` in the same transaction, never fire-and-forget — `decisions.ts:180` rejects the `pos_activity_logs` swallow-errors pattern explicitly for anything with stakes (`_research-crm-messaging-roles.md` §4). 4. **Flags default to today's behaviour.** Every new setting ships with the default that reproduces current production behaviour, following DEC-327 ("a migration must never change what a running business charges people") and `academy_payment_settings.enabled = false`. 5. **Billing and the ledger ship as separate releases.** Red-team §6.9. The ledger switch (C) is release n; card collection (G) is release n+1. They share no migration. 6. **Reservations are a locked fact on a row, not a computed count and not a ledger row.** See section 3 and the red-team responses there. ### 1.3 Red-team findings answered in this document | Finding | Severity | Answered in | |---|---|---| | No lock left to take under `on_done` | FATAL | 3.2 | | `get_player_package_remaining_sessions` over-books the series cap | FATAL | 2 M4, 3.6 | | A roster seat has no package id | FATAL | 3.2, 6.2 | | `release` as a delta-0 ledger row breaks DEC-319/320 | FATAL | 3.3 | | Two new consume reasons split every reader | FATAL | 3.3 | | Compensating `refund` rows NULL `lessons.player_package_id` | FATAL | 12.3 | | `no_show` inference libels real attendance | FATAL | 12.4 | | No saved payment method exists anywhere | FATAL | 5.2 | | Most Thai academy money is not chargeable off-session | FATAL | 4.6 | | Pay-per-seat joins bypass packages | HIGH | 3.5 | | Holds vanish on release | HIGH | 3.5 | | Private lessons consume at lesson level | HIGH | 3.5 | | `balance_after` ordering outside the bucket lock | HIGH | 3.3 | | `refund` is the wrong reason for the switch | HIGH | 12.3 | | Hourly re-fire becomes a double charge | HIGH | 4.4 | | SCA/3DS turns technical failure into `past_due` | HIGH | 4.5 | | The bridge has nowhere to land | HIGH | 2 M9, 4.3 | | `past_due` stops billing entirely | HIGH | 4.5 | | Re-typing Growth changes existing students | HIGH | 12.5 | | 62 packages with NULL `expires_at` fall off rosters | HIGH | 6.2, 12.6 | | 9 students with >1 active package have no tie-break | HIGH | 6.2 | | Never evict a paid seat | HIGH | 6.4 | | A 03:00 job rewrites the coach's roster | HIGH | 6.4 | Medium and low findings are answered inline where the design touches them, each marked **RT**. --- ## 2. Schema deltas Migration files are authored with `supabase migration new`; the timestamps below are placeholders of the form `YYYYMMDDHHMMSS_.sql`. **No applied migration is ever edited** — every change is additive and forward-only. `packages/database/src/database.types.ts` is regenerated once per PR. **Id mapping.** `03-plan.md` and `01-scope.html` name migrations per feature; this spec numbers them in build order. Both refer to the same files: | Plan / scope id | This spec | Notes | |---|---|---| | M-L1 | M12 `admin_audit_log` | PR 4 | | M-C1 | M3 `lesson_players_attendance_state` | PR 6 | | M-C2 | M1 `academy_settings` (+ `academies.ledger_mode`) and M4 `package_session_reservations` | PR 6: flag defaults to today; reservation-aware balance and insert-time lock | | M-C3 | M5 `ledger_mode_switch_reason` + `mark_attendance` RPC (§3) | PR 7 | | M-C4 | M18 `ledger_mode_switch` + M19 `ledger_mode_switch_inverse` | PR 7, data migrations (§12) | | M-C5 | §12 data script: Growth (monthly) template, multi-package and NULL-expiry resolution | PR 7, no schema change | | M-B1 | M6 `academy_group_schedule` + M7 `lessons_academy_group_id` | PR 8 | | M-B2 | roster generation RPC (§6.2), rides on M4/M6 | PR 8 | | M-B3 | M8 `lesson_waitlist` | PR 9 | | M-D1 | M2 `player_packages_pause_past_due` | PR 10 | | M-D2 | M1 `academy_settings` | PR 10 (same file as M-C2's settings; the flag ships first) | | M-D3 | M16 `dispatch_roster_sync` + M17 `dispatch_renewal_notices` | PR 10 | | M-D4 | M10 `package_charge_attempts` + M14 `customer_payment_mandates` | PR 12; mandates table only used by the later opt-in | | M-E1 | M9 `payment_requests_bridge` | PR 11 | | M-F1 | M13 `message_templates_academy_keys` + opt-out stop-gap (8.2) | PR 16 | | M-G1 | M15 `academy_reconciliation_reports` | PR 13 | | M-H1 | attendance-vs-payment view (§9) | PR 14 | | M-I1 / M-I2 | M11 `academy_leads` (`leads` + `lead_events`) | PR 17 | | M-J1 | M13 `message_templates_academy_keys` + message log | PR 18 | | M-P1 | level history | PR 19, outside this spec (Plan Step 4) | | M-M2 | M21 `bookings_source` | PR 20 | | M-M1 | M20 `programs_source_external_event` (+ `program_type` values) | PR 21 | | M-M3 | M22 `program_session_payload_instructor` (function change, no table) | PR 21 | | # | File slug | Table | Column / constraint | Type | Default | Why | |---|---|---|---|---|---|---| | **M1** | `academy_settings` | `academy_settings` *(new)* | `academy_id` PK | uuid | — | FK `academies(id)` CASCADE. Keyed on the academy, **not** the facility: Pad Thai Padel runs several sites and a student training at two would otherwise get half their seats on each rule (RT §3.4). `academy_payment_settings` stays facility-keyed and keeps reminder transport. Follows settings pattern A (`_research-crm-messaging-roles.md` §7). | | | | `academies` | `ledger_mode` | text | `'on_add'` | CHECK `('on_add','on_done')`. D17 puts the flag on `academies`, not the facility: a facility flag splits a student who trains at two of the same academy's sites. Default reproduces today. Read through a helper `academy_ledger_mode(p_facility_id, p_academy_id)` so no call site has to join. | | | | | `auto_mark_no_show_hours` | smallint | `NULL` | NULL = manual only, which is where this academy starts. D9 turns it to 12 **only once check-in is a habit** — more than 80 % of seats checked in for two consecutive weeks, measured by the nightly report. CHECK `> 0`. | | | | | `student_cancel_rule_group` | text | `'deduct'` | CHECK `('deduct','follow_cancellation_policy')`. D1 default (a). Not an hours value — the hours stay on `cancellation_policies.free_cancellation_hours` so a club cannot hold two different deadlines (RT §1.8). | | | | | `student_cancel_rule_individual` | text | `'follow_cancellation_policy'` | same CHECK. D2 default (b). | | | | | `roster_grace_days` | smallint | `7` | CHECK 0..60. D3: counted from the **lapse day**, so `past_due` on day 3 means removal on day 10 — after the day-7 freeze, so a student never loses adds and existing seats on the same day (RT §5.5). See 6.4. | | | | | `pause_notice_day` | smallint | `26` | CHECK 1..28. | | | | | `past_due_day` | smallint | `3` | CHECK 1..28. | | | | | `freeze_day` | smallint | `7` | CHECK 1..28, `>= past_due_day`. | | | | | `reminder_day_before_1st` | smallint | `26` | CHECK 1..28. Dispatched by its own job, not a `reminder_offset` (RT §4.8). | | | | | `allow_overbook_reserved` | boolean | `false` | Enforced by M4's lock, not by a report. | | | | | `proration_mode` | text | `'none'` | CHECK `('none','weekly_quarters')`. `none` reproduces today. | | | | | `slot_allocation` | text | `'spread'` | CHECK `('date_order','spread')`. D18: a four-session plan in a two-slot group gets four seats spread across the month, and the second weekly slot is sold as a priced add-on rather than implied (RT §5.3). See 6.3. | | | | | `updated_by`, `updated_at` | uuid, timestamptz | NULL, `now()` | FK `auth.users` SET NULL, per the house pattern. | | **M2** | `player_packages_pause_past_due` | `player_packages` | `status` CHECK | text | — | DROP + ADD CHECK `('active','exhausted','expired','cancelled','past_due','paused')`. Additive: no existing row changes value. | | | | | `paused_from`, `paused_until` | date | NULL | Both NULL or both set (CHECK). `paused_until` is exclusive and always a 1st. | | | | | `pause_requested_at`, `pause_requested_by` | timestamptz, uuid | NULL | Proof the notice landed before `pause_notice_day` (D4). | | **M3** | `lesson_players_attendance_state` | `lesson_players` | `attendance_state` | text | `NULL` | CHECK `attendance_state IS NULL OR attendance_state IN ('scheduled','attended','no_show','cancelled_by_academy','cancelled_by_student')`. **Nullable** so 698 historical seats stay valid without a data pass; NULL means "pre-switch seat, read `checked_in_at`/`attended`". | | | | | `attendance_marked_at`, `attendance_marked_by` | timestamptz, uuid | NULL | FK `auth.users` SET NULL. | | | | | `override_kind` | text | NULL | CHECK `('comp','makeup','trial','goodwill','billing_pending')`. A free-text reason alone leaves a favour invisible to money (RT §5.4); the code says how it is settled. | | | | | `override_reason` | text | NULL | CHECK `override_kind IS NULL OR btrim(override_reason) <> ''`. | | | | | `override_by` | uuid | NULL | FK `auth.users` SET NULL. Written only by owner/admin per D7. | | | | | `player_package_session_id` | uuid | NULL | FK `player_package_sessions` SET NULL. **Pins the bucket at reservation time** so the same seat can never attribute to a different bucket on a later render (RT §3.5). | | | | | `coverage_state` | text | `NULL` | CHECK `(NULL,'covered','pending_period','uncovered')`. `pending_period` = the lesson falls in a future billing period whose grant does not exist yet (RT §2.5). | | | | | index | — | — | `idx_lesson_players_package_state ON lesson_players (player_package_id, attendance_state) WHERE player_package_id IS NOT NULL`. | | **M4** | `package_session_reservations` | `player_package_sessions` | `sessions_reserved` | integer | `0` | CHECK `>= 0`. **The reservation counter.** Mutated only inside the same `SELECT … FOR UPDATE` on the bucket that `consume_package_session` already takes (`20260824200640_package_session_ledger.sql:259`), which is the lock the red team found missing (RT §1.1). | | | | RPC | `reserve_package_session` *(new)* | — | — | See 3.4. | | | | RPC | `release_package_reservation` *(new)* | — | — | See 3.4. | | | | RPC | `mark_attendance` *(new)* | — | — | See 3.4. | | | | fn | `get_player_package_remaining_sessions` | — | — | **Redefined** (`20260803101048_get_player_package_remaining_sessions.sql`) to return `SUM(sessions_remaining - sessions_reserved)`. Without this the recurring generator at `apps/admin/src/app/api/admin/lessons/recurring/route.ts:181` enrols a 4-session student in 13 occurrences the day the flag flips (RT §1.2). | | | | trigger | `check_package_exhaustion` | — | — | Trigger widened from `AFTER UPDATE OF sessions_remaining` (`00000000000000_initial_schema.sql:7892`) to `AFTER UPDATE OF sessions_remaining, sessions_reserved`, comparing effective remaining (RT §1.7). | | **M5** | `ledger_mode_switch_reason` | `package_session_transactions` | `reason` CHECK | text | — | DROP + ADD with **one** new value: `ledger_mode_switch`. `no_show_consume`, `student_cancel_consume` and `release` are **rejected** — see 3.3. | | **M6** | `academy_group_schedule` | `academy_groups` | `class_id` | uuid | NULL | FK `classes` SET NULL. The template a generated lesson is created from. | | | | `academy_group_slots` *(new)* | `id, group_id, weekday smallint, start_time time, duration_minutes int, court_id uuid, coach_id uuid, is_active bool, created_at` | — | — | UNIQUE `(group_id, weekday, start_time)`. `weekday` CHECK 0..6 (0 = Sunday, matching `@setpoint/common/recurrence`). `coach_id` NULL = inherit `academy_groups.coach_id`; the slot wins when set, so the two never drift (RT §5.6). | | **M7** | `lessons_academy_group_id` | `lessons` | `academy_group_id` | uuid | NULL | FK `academy_groups` SET NULL, indexed. The only link between a group and a lesson; `20260722032430_add_academy_groups.sql` states none exists today. | | | | | `academy_group_slot_id` | uuid | NULL | FK `academy_group_slots` SET NULL. Lets the sync job recognise its own output. | | **M8** | `lesson_waitlist` | `lesson_waitlist` *(new)* | `id, facility_id, lesson_id, user_id, player_package_id, position int, status text, claim_expires_at, promoted_lesson_player_id, created_at` | — | — | `status` CHECK `('waiting','claim_offered','claimed','expired','cancelled')`, copying the four-value shape of `program_enrollments` (`20260605094626:79-82`). Position by read-max-plus-one, the pattern at `_research-planning-groups.md` §5. UNIQUE `(lesson_id, user_id) WHERE status IN ('waiting','claim_offered')`. | | **M9** | `payment_requests_bridge` | `payment_requests` | `bridge_tier` | text | NULL | CHECK `(NULL,'PRORATE75','PRORATE50','PRORATE25','full','wait')`. The bridge currently has nowhere to land (RT §4.5) — `process-package-billing` writes `amount_amount: template.price_amount` unconditionally. | | | | | `bridge_days` | integer | NULL | CHECK `>= 0`. Days from the period start to the next 1st, as computed. | | | | | | | | CHECK `(bridge_tier IS NULL) = (bridge_days IS NULL)`, mirroring the existing `period_start`/`period_end` pair CHECK. | | **M10** | `package_charge_attempts` | `package_charge_attempts` *(new)* | `id, facility_id, player_package_id, payment_request_id, period_start date, stripe_idempotency_key text, stripe_payment_intent_id, status text, failure_code, failure_message, attempt smallint, next_retry_at, created_at, updated_at` | — | — | UNIQUE `(player_package_id, period_start, attempt)`; UNIQUE `(stripe_idempotency_key)`. `status` CHECK `('pending','processing','succeeded','authentication_required','failed','abandoned')`. Consulted **before** the Stripe call, so an hourly re-fire cannot double-charge (RT §4.3). | | **M11** | `academy_leads` | `leads` *(new)* | `id, facility_id NOT NULL, academy_id, name, email, phone, phone_country_code, source text, source_detail, status text, owner_id, converted_customer_id uuid, converted_at, notes, metadata jsonb, created_by, created_at, updated_at` | — | — | `source` CHECK `('walk_in','website','instagram','whatsapp','referral','event','import','other')`. `status` CHECK `('new','contacted','trial_booked','won','lost','duplicate')`. `owner_id` FK `auth.users` SET NULL (D12: one owner). **`converted_customer_id` FK `customers(id)`** — a lead converts into the existing identity chain `auth.users → customers`; it must **not** reference `facility_customers`, which two migration headers confirm is not deployed to production (`_research-crm-messaging-roles.md` §8). Conversion calls the existing `resolveOrCreatePlayerAccount` (`apps/admin/src/lib/find-or-create-user.ts`) and then writes a `crm_customer_directory` row so the new player is visible in production's `crm_search_customers`. Partial UNIQUE on `(facility_id, lower(email)) WHERE converted_customer_id IS NULL AND status <> 'lost'`. | | | | `lead_events` *(new)* | `id, lead_id, facility_id, event_type text, actor_id, body text, metadata jsonb, created_at` | — | — | `event_type` CHECK `('created','contacted','note','status_changed','message_sent','converted','lost')`. Append-only; no UPDATE/DELETE policy. | | **M12** | `admin_audit_log` | `admin_audit_log` *(new)* | `id uuid PK, facility_id uuid NOT NULL, actor_id uuid, action text NOT NULL, entity_type text NOT NULL, entity_id uuid, description text NOT NULL, before jsonb, after jsonb, metadata jsonb NOT NULL DEFAULT '{}', created_at timestamptz NOT NULL DEFAULT clock_timestamp()` | — | — | Column shape copied from `pos_activity_logs` (`00000000000000_initial_schema.sql:4165`) plus `before`/`after`; **the writer is not copied** (`decisions.ts:180`). `actor_id` FK `auth.users` SET NULL so deleting a customer anonymises rather than destroys the log, which `20260706041805_delete_customer_record.sql` depends on. `clock_timestamp()` not `now()`, so several rows in one transaction stay orderable (the ledger's own reasoning, `20260824200640:39-44`). Index `(facility_id, created_at DESC)`, `(entity_type, entity_id, created_at DESC)`. | | **M13** | `message_templates_academy_keys` | `message_templates` | seed rows | — | — | New system-default keys (facility_id NULL), each in both `email` and `whatsapp`: `package_renewal_notice`, `package_past_due`, `package_frozen`, `package_paused_confirm`, `roster_seat_released`, `lesson_rescheduled`, `waitlist_seat_offered`, `card_authentication_required`. Inserted through the partial unique index `message_templates_system_default_key`. Merge tags limited to the registered set in `packages/common/src/merge-tags.ts` plus three new ones registered there: `lesson_date`, `group_name`, `claim_deadline`. | | **M14** | `customer_payment_mandates` | `customer_payment_mandates` *(new)* | `id, facility_id NOT NULL, customer_id uuid NOT NULL, stripe_customer_id text NOT NULL, stripe_payment_method_id text NOT NULL, setup_intent_id text, brand, last4, exp_month, exp_year, status text, consent_at timestamptz NOT NULL, consent_source text, revoked_at, created_at` | — | — | `status` CHECK `('active','revoked','expired','failed')`. Partial UNIQUE `(facility_id, customer_id) WHERE status = 'active'`. **Why a new table and not a column:** `customers.stripe_customer_id` already exists and is already written by `apps/admin/src/app/api/v1/_lib/stripe-customer.ts:18-43`, so the customer id needs no new home; what does not exist anywhere is a stored mandate — a repo-wide grep for `setup_intent`, `off_session` and `save_payment_method` returns nothing (RT §4.1). A mandate is per-facility consent with a timestamp and a revocation, which is a row, not a column. `customers.default_payment_method_id` is global and its semantics are **UNVERIFIED**; it is not used. | | **M15** | `academy_reconciliation_reports` | `academy_reconciliation_reports` *(new)* | `id, facility_id, report_date date, generated_at, rows jsonb NOT NULL, summary jsonb NOT NULL` | — | — | UNIQUE `(facility_id, report_date)`. The persistent successor of Step 1's three tabs; see section 9. | | **M16** | `dispatch_roster_sync` | pg_cron | — | — | — | `cron.schedule('dispatch-roster-sync','0 2 * * *', …)`, one daily run, same `net.http_post` + Vault secret shape as `dispatch_package_billing` (`20260824200650:100-145`, DEC-326). | | **M17** | `dispatch_renewal_notices` | pg_cron | — | — | — | `cron.schedule('dispatch-renewal-notices','0 * * * *', …)`; hourly so `send_hour_local` lands correctly per facility timezone, firing only on `reminder_day_before_1st`. A `-5` offset on `reminder_offsets` lands on the 3rd, not the 26th (RT §4.8). | | **M18** | `ledger_mode_switch` | data migration | — | — | — | The on_add → on_done reconciliation. Section 12. | | **M19** | `ledger_mode_switch_inverse` | data migration | — | — | — | Written and tested **before** M18 runs (RT §3.7). Section 12.8. | RLS on every new table follows the house rule (`_research-crm-messaging-roles.md` §4): `facility_id NOT NULL`, staff SELECT via `public.user_facility_role(facility_id)`, INSERT/UPDATE restricted to `service_role`, no DELETE policy on the append-only tables (`admin_audit_log`, `lead_events`, `package_charge_attempts`). Nothing else is added. In particular: no `users` table (there is none — the profile table is `customers`), no `stripe_customers` table, no column on `facility_customers`, no new `lesson_players.status` value. --- ## 3. Ledger state machine ### 3.1 States ```mermaid stateDiagram-v2 [*] --> scheduled : reserve_package_session() scheduled --> attended : mark_attendance('attended') scheduled --> no_show : mark_attendance('no_show') / auto job scheduled --> cancelled_by_academy : lesson cancel · roster sync · staff remove scheduled --> cancelled_by_student : student or staff on their behalf attended --> scheduled : mark_attendance reversal (< 48h, owner/admin) no_show --> attended : mark_attendance correction (< 48h, D8) cancelled_by_student --> scheduled : staff reinstate (< 48h) attended --> [*] no_show --> [*] cancelled_by_academy --> [*] cancelled_by_student --> [*] ``` `scheduled` holds a reservation. `attended`, `no_show` and a deducting `cancelled_by_student` hold a consumption. `cancelled_by_academy` and a releasing `cancelled_by_student` hold neither. ### 3.2 Reservation is a counter under a lock — red-team finding → design response **Finding (FATAL, RT §1.1/§1.3):** under `on_done`, "reserved" becomes a COUNT over `lesson_players`, which is a read with no row to lock; and a generated roster seat carries no `player_package_id` at all, so the count is uncomputable for exactly the seats the model is about. **Response.** Reserved is not counted. It is `player_package_sessions.sessions_reserved` (M4), incremented inside the same `SELECT … FOR UPDATE OF pps` that `consume_package_session` already takes at `20260824200640_package_session_ledger.sql:259`. Every roster insert for a package seat goes through `reserve_package_session`, which in one statement: picks the bucket by the existing exact-`class_id` → `class_type` → `'any'` priority (`:245-259`), locks it, refuses when `sessions_remaining - sessions_reserved <= 0` unless `allow_overbook_reserved`, increments `sessions_reserved`, and writes `lesson_players.player_package_id`, `player_package_session_id` and `attendance_state='scheduled'`. Two staff adding the same student in the same second serialise on the bucket row; the second gets `package_no_sessions_remaining`, the existing error. `allow_overbook_reserved` is therefore enforced by a constraint path, not a report. ### 3.3 One `consume` reason, no `release` row — red-team finding → design response **Finding (FATAL, RT §2.1/§2.2, HIGH §2.3):** a delta-0 `release` row mutates no counter and belongs to no bucket, breaking DEC-319/320 and possibly recording a `balance_after` that was never true; and adding `no_show_consume` / `student_cancel_consume` silently breaks every reader that filters `reason='consume'` (the literal union is mirrored in `apps/pos/src/types/database.ts`, `apps/terminal/src/types/database.ts` and `academy-payments-types.ts`). **Response.** The ledger records balance changes only. - **No `release` reason.** A released reservation decrements `sessions_reserved` under the bucket lock and writes one `admin_audit_log` row (`action='reservation.released'`, entity `lesson_player`). The audit trail lives in the audit table, which is where a non-balance fact belongs. - **One `consume` reason.** The flavour goes in `metadata`: `{"attendance":"attended"|"no_show"|"student_cancel","lesson_player_id":…,"rule":"group_deduct"|"policy_deduct"}`. `_target-model.md` §2 already put no-show in `metadata`; this makes it the single mechanism. Existing readers keep working unchanged; utilisation reports gain a `metadata->>'attendance'` breakdown in the same PR. - **The CHECK gains exactly one value, `ledger_mode_switch`** (M5), used only by the one-time switch (12.3). ### 3.4 Transitions, ledger rows, and the RPC that writes them `c` = `consume_package_session` (extended to accept `p_metadata jsonb` and `p_lesson_player_id`), `r` = `refund_package_session`, `res` = `reserve_package_session` *(new)*, `rel` = `release_package_reservation` *(new)*. All are SECURITY DEFINER, `REVOKE ALL FROM PUBLIC`, granted to `service_role` only, matching `adjust_package_sessions` (`20260824200640:610-612`). | Transition | Counter effect | Ledger row | RPC | |---|---|---|---| | roster add (auto or manual), `ledger_mode='on_done'` | `sessions_reserved +1` | none | `res` | | roster add, `ledger_mode='on_add'` (today) | `sessions_remaining -1`, `sessions_used +1` | `consume`, delta −1, `source='live'` | `c` unchanged | | `scheduled → attended` | `sessions_reserved -1`, `sessions_remaining -1`, `sessions_used +1` | `consume`, delta −1, `metadata.attendance='attended'` | `mark_attendance` → `c` | | `scheduled → no_show` | same as attended | `consume`, delta −1, `metadata.attendance='no_show'` | `mark_attendance` → `c` | | `scheduled → cancelled_by_student`, group, rule `deduct` (D1) | same as attended | `consume`, delta −1, `metadata.attendance='student_cancel'`, `metadata.rule='group_deduct'` | `mark_attendance` → `c` | | `scheduled → cancelled_by_student`, outside `free_cancellation_hours` | same as attended | `consume`, delta −1, `metadata.rule='policy_deduct'` | `mark_attendance` → `c` | | `scheduled → cancelled_by_student`, inside the free window (D2) | `sessions_reserved -1` | none | `mark_attendance` → `rel` | | `scheduled → cancelled_by_academy` (lesson cancel, roster sync, staff remove) | `sessions_reserved -1` | none | `rel` | | `attended → scheduled` / `no_show → scheduled` reversal within 48h | `sessions_remaining +1`, `sessions_used -1`, `sessions_reserved +1` | `refund`, delta +1, `note` mandatory | `mark_attendance` → `r` then `res` | | already-consumed seat on a cancelled lesson (`on_add` rows) | `sessions_remaining +1` | `refund`, delta +1 | `r` unchanged, via `cancel-lesson-cascade.ts` | `mark_attendance(p_lesson_player_id uuid, p_state text, p_actor uuid, p_reason text DEFAULT NULL) RETURNS json`: 1. Lock the `lesson_players` row `FOR UPDATE`, join its `lessons` row. 2. Refuse (`P0001 attendance_not_a_package_seat`) when the seat is not a package seat — see 3.5. 3. Refuse when the requested state equals the current state (idempotent no-op returns `{changed:false}`), and when moving out of a terminal state more than 48 hours after `attendance_marked_at` (D8: the manager corrects within 48 h with a reason; `p_reason` is mandatory for any correction, raising `P0001` if blank, exactly like `adjust_package_sessions`). 4. Lock the pinned bucket via `player_package_session_id`, apply the counter effects and the ledger row in the same transaction. 5. Write `attendance_state`, `attendance_marked_at = now()`, `attendance_marked_by = p_actor`, and one `admin_audit_log` row with `before`/`after`. **Exhaustion moves to done time.** `check_package_exhaustion` is widened to fire on `sessions_reserved` too (M4), so a fully reserved package flips to `exhausted` even though `sessions_remaining` has not moved. Without that, every `status='active'` gate in the app claims sessions are available when all four are spoken for (RT §1.7). ### 3.5 Which seats the ledger owns — red-team findings → design response **Findings (HIGH, RT §1.4/§1.5/§1.6, LOW §1.9).** Pay-per-seat joins (`confirm_group_lesson_seat`, `confirm_group_lesson_seat_from_hold`, `20260618000000:308,350`) set `paid_amount` and `payment_method='stripe'` and never touch a package. `release_group_lesson_hold` DELETEs the row. Private lessons consume against `lessons.player_package_id`, not a player row. Credits debit at add time via `record_credit_transaction`. **Response.** One predicate, `is_package_seat(lp)`, used by `reserve_package_session`, `mark_attendance`, `dispatch_roster_sync` and every report: ``` lp.payment_method = 'package' AND lp.player_package_id IS NOT NULL AND COALESCE(lp.paid_amount, 0) = 0 AND lp.payment_intent_id IS NULL AND lp.external_payment_ref IS NULL ``` - A seat that fails the predicate is never reserved, never consumed and never evicted. A student who paid cash for a drop-in is not charged a session at check-in. - `release_group_lesson_hold` gains one line before its DELETE: call `release_package_reservation` when the row holds one, and write the `admin_audit_log` row. The audit of a released hold survives the delete. - **Private lessons.** Under `on_done` the package attribution for a private lesson moves to the primary `lesson_players` row, which `create_lesson_atomic` already inserts; `lessons.player_package_id` continues to be written for back-compatibility but is no longer the consumption trigger. When a private lesson has no player row carrying the package, `create_lesson_atomic` raises rather than creating an unattributable seat. - **Credits are out of scope** and stay on deduct-at-add. Stated, not silently mixed. ### 3.6 Reserved beyond the current period A seat may be reserved against a recurring package only when the lesson's facility-local start date falls inside `current_period_start .. current_period_end`; against a session bank only when `expires_at IS NULL OR lesson_date <= expires_at`. Seats on lessons beyond that window are created with `player_package_id = NULL` and `coverage_state = 'pending_period'`, and `dispatch_roster_sync` attaches the package on the renewal date. This removes the ambiguity the red team raised about which period a 2 October lesson booked on 30 September consumes (RT §2.5) and keeps "October's four sessions" true. --- ## 4. Billing flow ### 4.1 Sequence ```mermaid sequenceDiagram participant Cron as pg_cron participant PPB as process-package-billing participant Stripe participant DB as Postgres participant POS as POS bridge trigger participant Msg as send-payment-reminder Note over Cron,Msg: Day 26 — renewal notice Cron->>Msg: dispatch-renewal-notices (hourly, fires at send_hour_local on day 26) Msg-->>DB: payment_reminders row, template package_renewal_notice Note over Cron,POS: Day 1 — raise and charge Cron->>PPB: dispatch_package_billing (hourly, status IN active/past_due) PPB->>DB: INSERT payment_requests (23505 = already raised, continue) PPB->>DB: compute_bridge() -> bridge_tier, bridge_days, amount_amount PPB->>DB: INSERT package_charge_attempts (status pending, idempotency key) alt default lane (D5c) — every student PPB->>Stripe: Checkout Session (card + PromptPay) -> checkout_id, checkout_url PPB->>DB: queue reminders from reminder_offsets carrying the link else opted-in mandate lane (D5b, later release) PPB->>Stripe: PaymentIntent confirm=true off_session=true (Idempotency-Key) Stripe-->>PPB: succeeded | authentication_required | card_error end Stripe-->>PPB: webhook payment_intent.succeeded PPB->>DB: settle-payment-request: CAS status -> paid, reset_package_sessions PPB->>DB: INSERT payments (type package, method stripe) DB->>POS: record_academy_payment_in_pos_trigger (same transaction) Note over Cron,DB: Day 3 — past due Cron->>DB: unpaid and no attempt in authentication_required -> status past_due Note over Cron,DB: Day 7 — freeze Cron->>DB: freeze: no new roster adds; existing seats stay Note over Cron,DB: Day 10 — roster removal (lapse day + roster_grace_days) ``` ### 4.2 What does not change `process-package-billing` keeps its three skip gates, its 422 on a NULL `billing_period`, its local-midnight period arithmetic, its "advance the cycle last" ordering and its rule that sessions are granted only when a request is marked paid (DEC-325). Packages carrying `stripe_subscription_id` keep their existing `invoice.paid` → `reset_package_sessions` path and are **never migrated silently** (AC-41, RT §4.10). ### 4.3 `compute_bridge` A pure SQL function so the same number appears in Step 1's proposal tab and in the charge. ```sql compute_bridge(p_price integer, p_days_to_next_1st integer) RETURNS TABLE (amount integer, tier text, days integer) -- weeks := LEAST(FLOOR(p_days_to_next_1st / 7), 4) -- p_days_to_next_1st <= 7 -> (0, 'wait', p_days) -- weeks >= 4 -> (p_price, 'full', p_days) -- weeks = 3 -> (round(p_price * 3 / 4.0), 'PRORATE75', p_days) -- weeks = 2 -> (round(p_price * 2 / 4.0), 'PRORATE50', p_days) -- weeks = 1 -> (round(p_price * 1 / 4.0), 'PRORATE25', p_days) ``` Applied **once**, on the first `payment_requests` row for a package (`period_start = purchase period`), then never again; `proration_mode='none'` bypasses it entirely. Session banks are never bridged — the function is not called for `package_type='session_bank'`. `wait` raises no request: the student starts on the next 1st. ### 4.4 Idempotency — red-team finding → design response **Finding (HIGH, RT §4.3):** `dispatch_package_billing` runs `0 * * * *` with fire-and-forget `net.http_post`; `process-package-billing` deliberately continues past a 23505 to finish a prior delivery's work. A charge after that point double-charges while the first PaymentIntent is still `processing`. **Response.** Three layers, all required: 1. `package_charge_attempts` (M10) is inserted **before** the Stripe call with UNIQUE `(player_package_id, period_start, attempt)`. A second hourly run loses that insert and reads the existing row; if its status is `pending`, `processing` or `succeeded` it returns `{skipped:true, reason:'charge_in_flight'}` and touches nothing. 2. `stripe_idempotency_key = 'pkgchg:' || player_package_id || ':' || period_start || ':' || attempt`, sent as the `Idempotency-Key` header. Stripe collapses a duplicate even if layer 1 is lost. 3. The existing compare-and-swap in `settle-payment-request.ts` (`UPDATE payment_requests SET status='paid' WHERE id=? AND status <> 'paid'`) stays the single winner-takes-all gate for granting sessions. ### 4.5 Failure paths, past due and SCA — red-team findings → design response **Findings (HIGH, RT §4.4/§4.6):** Thai issuers routinely require 3DS, so an off-session PaymentIntent returns `authentication_required`, which is not non-payment; and `dispatch_package_billing` selects `WHERE pp.status='active'`, so a package set `past_due` on day 3 is invisible to next month's dispatcher while the student is still attending. **Response.** | Stripe outcome | `package_charge_attempts.status` | Package status | Student-facing action | |---|---|---|---| | `succeeded` | `succeeded` | `active` | receipt | | `requires_action` / `authentication_required` | `authentication_required` | **stays `active`** | `card_authentication_required` template with an on-session confirmation link; the day-3 clock is suspended while an attempt is in this state and a link was sent within 48 h | | `card_declined`, `insufficient_funds` | `failed`, `next_retry_at` = +2 days | `past_due` on `past_due_day` | `package_past_due` template | | network / 5xx | `pending`, retried on the next hourly dispatch | `active` | none | Retry schedule for `failed`: attempt 2 at +2 days, attempt 3 at +4 days, then `abandoned` and the request stays open for manual settlement. `dispatch_package_billing`'s filter widens to `status IN ('active','past_due')`, and `process-package-billing`'s `package_not_active` gate is amended to skip only `paused`, `cancelled`, `expired` and `exhausted`. `paused` is excluded from billing, and on pause `next_billing_date` is set forward to the first of the month after `paused_until`, so a resumed package does not fire immediately for a period that was never charged (RT §4.7). ### 4.6 What is actually chargeable — red-team finding → design response **Finding (FATAL, RT §4.2):** the POS bridge maps tender to `cash | card | other` (`20260901170806:196`) and the academy UI labels PromptPay QR. Cash, transfer and PromptPay cannot be auto-charged at all. **Response, stated plainly in the product copy and in the spec.** Automatic collection on the 1st covers exactly the students with an `active` row in `customer_payment_mandates`. Everyone else keeps today's lane: a `payment_requests` row on the 1st, reminders from `reminder_offsets`, settlement by a human marking it paid or by the POS. The day-1/3/7 cadence applies to both lanes; only the charge differs. On the 10 September export **no** Pad Thai Padel student has a mandate, because no mandate collection exists anywhere in Setpoint, so feature G ships as: collect mandates (5.3), then charge the students who gave one. Section 12 does not depend on any of this — that is principle 1.5. **RT §4.9 (partial payments).** Out of scope for this release. `process-package-billing` raises exactly one request per period; a second `payments` row settling the same request produces a second POS order that the nightly reconciliation report (section 9) flags as a POS variance rather than silently netting. **RT §4.11 (no POS location):** the bridge returns NULL and warns; the same report flags a facility with academy payments and no active `pos_locations` row. **RT §4.12:** with a day-1 charge the default `{-3,0,3,7}` puts three chasers in week one; the recommended per-facility value for a card-charged academy is `{0,3,7}`, set in settings, not in a migration. --- ## 5. Stripe object mapping ### 5.1 Mapping | Setpoint entity | Stripe object | Notes | |---|---|---| | `customers.stripe_customer_id` | Customer | Already written by `apps/admin/src/app/api/v1/_lib/stripe-customer.ts:18-43`. Reused as-is; no new column, no `stripe_customers` table. | | `customer_payment_mandates` (M14) | PaymentMethod + SetupIntent | `setup_intent_id` for provenance, `stripe_payment_method_id` for the charge, `consent_at` for the mandate record Stripe requires for off-session use. | | `player_packages` + `payment_requests` period | PaymentIntent | `amount = payment_requests.amount_amount`, `currency = facilities.currency`, `customer`, `payment_method`, `off_session: true`, `confirm: true`, `Idempotency-Key` per 4.4. | | `package_charge_attempts` | (no Stripe object) | Local attempt ledger; `stripe_payment_intent_id` links out. | | `payments.stripe_payment_id` | PaymentIntent id | The matching key used by reconciliation (section 9). | | `payment_requests.checkout_id` | Checkout Session id | Unchanged; still the key `settle-payment-request.ts` matches on. | | `player_packages.stripe_subscription_id` | Subscription | Legacy lane, untouched (AC-41). | Metadata on every PaymentIntent: `{facility_id, academy_id, player_package_id, payment_request_id, period_start, kind:'academy_package'}`. `kind` is the discriminator the webhook branches on, following the existing `meta.type` convention in `apps/admin/src/app/api/v1/webhooks/stripe/route.ts`. ### 5.2 Webhook Events consumed: `payment_intent.succeeded`, `payment_intent.payment_failed`, `payment_intent.requires_action`, `setup_intent.succeeded`, `payment_method.detached`. **Extend the existing `apps/admin/src/app/api/v1/webhooks/stripe/route.ts`, do not add a new endpoint.** It already has a `payment_intent.succeeded` branch dispatching on `meta.type` (`:805-883`) and already shares `settle-payment-request.ts` with the Xendit webhook. `supabase/functions/pos-stripe-webhook/index.ts` is the POS terminal webhook, handles only `status='payment_requested'` tickets and stays out of this. A second endpoint would mean a second signing secret and a second settlement path for the same `payment_requests` row. ### 5.3 Mandate collection New route `POST /api/admin/academy/payment-mandates/setup-intent` (staff-initiated, returns a SetupIntent client secret and a short-lived link) and `POST /api/v1/me/payment-mandates` (student-initiated from the web app). `setup_intent.succeeded` writes the `customer_payment_mandates` row. This is the long pole of feature G and is **not** a change to `process-package-billing` (RT §4.1). ### 5.4 Test keys to live keys `facilities.payment_provider` was backfilled to NULL for every facility by `20260824123907_payment_provider_nullable_backfill.sql`, so today no facility can take an online payment; `CLAUDE.md` records that production runs Stripe test keys by design. Moving one facility to live keys is therefore a gate, not a task in this spec: set `facilities.payment_provider='stripe'` for that facility, swap the environment's Stripe secret, and re-run the mandate flow, because **test-mode PaymentMethods and Customers do not exist in live mode** — every mandate collected under test keys must be collected again. The spec's ordering (mandates first, charging second) makes that re-collection the same work either way. Whether Pad Thai Padel's Setpoint instance is live-key ready is **UNVERIFIED**. --- ## 6. Roster follows package ### 6.1 Generation from slots ``` generate_group_lessons(group_id, from_date, to_date): slots := active academy_group_slots for group_id for each slot, for each date D in [from_date, to_date] where dow(D) = slot.weekday: if a lesson already exists with (academy_group_slot_id = slot.id and local_date(start_time) = D): continue lesson := create_lesson_atomic( class_id := slot.class_id ?? group.class_id, coach_id := slot.coach_id ?? group.coach_id, court_ids := [slot.court_id], start/end := D + slot.start_time, + slot.duration_minutes, class_type := 'group', players := []) -- roster attached below, never inside the atomic set lesson.academy_group_id, lesson.academy_group_slot_id for each member M in academy_group_members(group_id) ordered by joined_at: pkg := select_covering_package(M.user_id, D) if pkg is null: insert lesson_players(user_id := M, attendance_state := 'scheduled', coverage_state := 'uncovered', payment_method := 'pending') else if D outside pkg.current_period: insert lesson_players(..., coverage_state := 'pending_period', player_package_id := null) else: insert lesson_players(..., payment_method := 'package') reserve_package_session(pkg.id, class_id, lesson.id, lesson_player.id) -- locks, may refuse on refusal: set coverage_state := 'uncovered', player_package_id := null ``` Reuses `POST /api/admin/lessons/recurring`'s `computeOccurrenceDates` and `RECURRENCE_SOFT_CAP_DAYS` from `@setpoint/common/recurrence`; only group classes may recur, which is already enforced there. ### 6.2 Coverage predicate and package selection `select_covering_package(user_id, lesson_date)` is deterministic and returns at most one row: ``` FROM player_packages pp JOIN lesson_packages lp ON lp.id = pp.package_id WHERE pp.user_id = :user AND pp.facility_id = :facility AND pp.status IN ('active','past_due') -- past_due still attends until the freeze AND ( (lp.package_type = 'recurring' AND :lesson_date BETWEEN local_date(pp.current_period_start) AND local_date(pp.current_period_end)) OR (lp.package_type = 'session_bank' AND (pp.expires_at IS NULL OR :lesson_date <= local_date(pp.expires_at))) ) -- NULL = never expires AND get_player_package_remaining_sessions(pp.id) > 0 ORDER BY (pp.expires_at IS NULL), pp.expires_at ASC, pp.created_at ASC LIMIT 1 ``` **NULL `expires_at` means never expires** (RT §3.5), matching `get_player_package_remaining_sessions`' own treatment, so the 62 packages with a null expiry do not fall off every roster on day one. The `ORDER BY` is the tie-break for the 9 students with more than one active package: soonest real expiry first, nulls last, then oldest. The chosen id is **persisted on the seat** (`player_package_id` + `player_package_session_id`), so the same seat never re-attributes on a later render (RT §3.6). ### 6.3 Two slots, four sessions — D18 A Gold B group running Monday and Wednesday generates 8–9 slots a month against an Essential entitlement of 4. The recurring route's existing "first N occurrences in date order" cap (`apps/admin/src/app/api/admin/lessons/recurring/route.ts:214-219`) would enrol the student for the first two weeks and nothing after — the wrong shape for a fixed-slot subscription (RT §5.3). D18 settles it: the second weekly slot is a **priced add-on**, not an implication of the plan. So `slot_allocation` defaults to `spread`: with `entitlement = 4` and `slots = 9`, reserve every `ceil(9/4)`-th slot in date order, giving one seat roughly every week rather than four in a fortnight. The remaining slots become `coverage_state='uncovered'` seats that render as "second slot not purchased" and appear on the Academy Health worklist as an upsell line, never as a silent free session. A student who buys the add-on gets a second `player_packages` row and the predicate in 6.2 covers both slots naturally. `date_order` remains selectable for a club that wants today's behaviour. ### 6.4 Override and lapse **Override.** Only owner and admin may attach an uncovered student (D7); a manager records the reason but the route returns 403. `PATCH /api/admin/lessons/[id]/players` gains `override_kind` + `override_reason`, writes `override_by`, and writes one `admin_audit_log` row in the same transaction. `override_kind='comp'` additionally writes an `admin_adjust` ledger row with the reason at attendance time, so a favour granted at the desk is visible as a comped session rather than invisible to money (RT §5.4). **Lapse, `dispatch_roster_sync` (M16, 02:00 facility-local).** For each package that left `active`/`past_due`: 1. Removal happens at **lapse day + `roster_grace_days`** — `past_due` on day 3 plus 7 = **day 10**, three days after the day-7 freeze (D3). The freeze and the removal are deliberately different dates, so a student does not lose the ability to book and every existing seat in one notification (RT §5.5). 2. The job **never touches a lesson starting within 48 hours**. Those go to the Academy Health worklist for a human, so a 02:00 job cannot rewrite a roster the coach has already planned for tomorrow (RT §5.2). 3. The job **hard-excludes any seat failing `is_package_seat`** — `paid_amount > 0`, a `payment_intent_id`, or `payment_method <> 'package'`. Paying, non-package customers on TPS-published sessions are never evicted (RT §5.1). 4. Every removal: `release_package_reservation`, `attendance_state='cancelled_by_academy'`, one `admin_audit_log` row, and one `payment_reminders` row per affected coach and student using `roster_seat_released`. 5. The job also **attaches** packages: seats in `coverage_state='pending_period'` whose period has now started get `reserve_package_session` called. **OpenRally impact.** `lesson_players` changes already emit `group_lesson_player_event` (`emit_group_lesson_player_event()` + `trg_lesson_players_emit_group_session_event`, `20260618000000`), and lesson changes emit `emit_lesson_status_event`, `emit_lesson_modified_event` (`20260813071629`) and `emit_lesson_court_change_event` (`20260813074246`). Generated lessons and synced rosters therefore appear on the TPS consumer with no new plumbing — and with no new event type, which is why the notice in step 4 is a message, not an event. --- ## 7. Drag-and-drop planning (H) **Fix first: `availability_override` is dropped on every edit.** Both edit dialogs send it (`lesson-form-fields.tsx:960`, `lesson-form-dialog.tsx:1039`) but `editableFields` in `apps/admin/src/app/api/admin/lessons/[id]/route.ts:117-128` omits it, so the toggle does nothing and `tc-264` covers only the create path. Adding it to `editableFields` is a one-line change that must land before drag-to-move, because a drop outside coach hours otherwise cannot carry an override. | Concern | Design | |---|---| | Mutation | Reuse `PATCH /api/admin/lessons/[id]` with `{start_time, end_time, court_ids:[X]}`. It already moves the lesson and every court leg together, re-runs `findCourtConflicts`, recreates bookings and emits the events. **No new mover.** | | Court bookings | `PATCH /api/admin/bookings` with `{booking_id, court_id, start_time, end_time, duration_minutes}`. A lesson leg returns **409** with `"Lesson bookings are rescheduled through the lesson"`, so the drop handler branches on `booking.lesson` before choosing the endpoint. | | Library | `@dnd-kit/core ^6.3.1` is already a dependency; the in-repo usage pattern is `court-images-dialog.tsx`. No new dependency. | | Geometry | A drop target is `(courtId, tickIdx)`. Vertical: `TICK_HEIGHT_PX = 28`; horizontal: `CELL_WIDTH_PX = 48`; `STRIDE_MINUTES = 30`. `newStart = dayStart + tickIdx × 30 min`; duration is preserved, so `end = start + duration_minutes`. Snap is one tick. | | Pre-flight | Optional `GET /api/admin/lessons/[id]/can-move?court_id&start_time` wrapping the existing `findCourtConflicts`, used only to colour the drop shadow. It is advisory; the PATCH remains authoritative. | | Conflict | A 409 rolls the card back to its origin with a toast carrying the server message, humanised by `translateCoachReservationError`. A 400 from coach-availability validation offers "move anyway" only to owner/admin, which sets `availability_override`. | | Undo | Client-side last-move stack (origin court + start, capped at 20) with an Undo toast that issues the inverse PATCH. The server-side record is the `admin_audit_log` row written by the PATCH with `before`/`after` — the undo is a new move, never a deletion of the audit row. | | Keyboard | Every card stays a `