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.

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

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_<slug>.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:

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

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

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

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.

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.

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

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.

8. Messaging (F, J)

8.1 WhatsApp Cloud API requirements

apps/admin/src/lib/messaging/providers/whatsapp-cloud.ts is a stub that throws and reads no environment variable — there is no configuration to supply yet (DEC-323). Implementing it needs, in order:

1. A Meta Business account through Business verification (business documents, days to weeks).
2. A dedicated sending number not already registered to a personal WhatsApp account.
3. Approved message templates in the utility category. Free-form messages are blocked outside the customer-initiated window, and a payment reminder is always business-initiated.
4. Env: WHATSAPP_PHONE_NUMBER_ID, WHATSAPP_BUSINESS_ACCOUNT_ID, WHATSAPP_ACCESS_TOKEN (system user, long-lived), WHATSAPP_APP_SECRET. Read at send time, never at module load, so the stub's replacement fails per-send rather than at boot.
5. The free 24-hour service window closes on 1 October 2026, so every message in this design is a paid template send, roughly USD 0.004–0.046 each depending on category and market. At ~60 students × ~4 messages a month that is single-digit dollars, which is a cost note, not a blocker.

Example bodies, seeded as system defaults (M13), utility category, variables numbered as Meta requires:

8.2 Provider slot, opt-in and fallback

resolveMessagingProvider(channel, providerId?) already exists and already refuses unknown or mismatched providers rather than falling back (apps/admin/src/lib/messaging/index.ts). The Cloud provider fills the whatsapp_cloud slot with the same MessagingProvider interface, returning status:'sent' and the Meta message id as providerMessageId. CHANNEL_DEFAULTS.whatsapp moves from whatsapp_manual to a per-facility lookup on academy_payment_settings (the column the research notes was deferred to "step 11"); a facility without Cloud configured keeps the manual wa.me lane, which stays as the permanent fallback (D6 keeps (a)). A Cloud send failure does not silently fall back — it writes payment_reminders.error and surfaces it, per AC-51.

Opt-in and opt-out. Stored per channel on the existing payment_reminders consent path is not enough, so opt-out state lives on leads/customers as a small addition: this release reuses facility_customers.email_unsubscribed? No — that table is not in production. Instead, opt-out is a row in admin_audit_log-adjacent storage would be wrong too. The correct home is one new column in a later PR; for this release, WhatsApp opt-out is recorded as a crm_customer_tags tag (whatsapp-opt-out) and checked before every send, because tags already exist, are facility-scoped and are already auditable via assigned_by. Flagged as a deliberate stop-gap, to be replaced by a first-class preference table when messaging grows beyond utility templates.

Push: none (D14). The coach PWA is Flutter with no messaging dependency and no service worker anywhere in the monorepo; push is greenfield infrastructure and is out of scope.

9. Reconciliation and attendance-versus-payment

Step 1 (PHASE-1-SPEC.md) computes three tabs live on every request. This section makes them durable so trends, not just today, are answerable.

academy_reconciliation_reports (M15) is written nightly at 03:00 facility-local by the same dispatcher pattern, one row per (facility_id, report_date), rows holding the three flagged sets and summary holding the tiles. A materialised table, not a view: the sets depend on "what was true that day" (a request paid later must not retroactively unflag a day the student trained unpaid).

Matching keys. payments.stripe_payment_id ↔︎ PaymentIntent id; payment_requests.checkout_id ↔︎ Checkout Session id; package_charge_attempts.stripe_payment_intent_id ↔︎ PaymentIntent id. Duplicate rule: two payments rows with the same stripe_payment_id is a duplicate and is flagged, never netted; two rows against the same payment_requests.id with different provider ids is a partial payment and is flagged as a POS variance (4.6). POS variance: a payments row of type IN ('package','lesson') with status='succeeded' and amount > 0 that has no POS order with source_id = 'academy_payment:' || payments.id, which is what a facility with no active pos_locations row produces silently today.

10. Exports (K)

apps/admin has no CSV export at all; csvCell from packages/common/src/csv.ts exists and already neutralises formula injection, and is importable as @setpoint/common/csv. New route GET /api/admin/academy-exports?facility_slug&kind&date_from&date_to[&coach_ids], owner/admin/manager only, streamed via a ReadableStream with Content-Disposition: attachment, 12-month maximum range enforced with a 400 (D15: on demand, nothing stored).

coach_hours reuses academy-reports/route.ts's existing arithmetic exactly: hours accumulate as duration_minutes / 60 from the lesson row, rounded to one decimal — there are no timesheets in the repo. Filters are the same facility-local boundaries from packages/common/src/datetime.ts that Step 1 uses. Amounts are exported as decimals with the minor-unit division done once, at the edge.

11. Audit model (L)

admin_audit_log (M12) is written by one helper, writeAuditLog(tx, {facilityId, actorId, action, entityType, entityId, description, before, after, metadata}), called inside the same transaction as the mutation. decisions.ts:180 is the governing rule: pos_activity_logs' writer swallows every error on purpose, and that was explicitly rejected as a model for anything with stakes. A failed audit insert aborts the operation, exactly as a failed ledger insert aborts a balance change (DEC-320).

Entities covered this release (D16 option (a), academy tables only; the table is shaped so (b) is a config change): lesson_player (reserve, release, attendance, override), player_packages (pause, resume, past_due, freeze, cancel), payment_requests (create, mark paid, revert), package_charge_attempts (charge attempted, outcome), academy_groups / academy_group_slots / academy_group_members, lessons (move, court change, cancel), leads, academy_settings, customer_payment_mandates (created, revoked).

Retention: indefinite, append-only, no UPDATE or DELETE policy. actor_id is nullable with ON DELETE SET NULL so deleting a customer anonymises the log rather than destroying it. Readers: owner, admin and manager via a new GET /api/admin/audit-log with facility scope and entity filters; coach and staff cannot read it. A requireRole(access, roles, action) helper is extracted in this PR from apps/admin/src/app/api/admin/message-templates/_lib/templates.ts:422 (the only reusable gate in the repo) and used by every new route, because the same name WRITE_ROLES currently means two different things in different files.

12. Backfill and migration strategy (the on_add → on_done switch)

Switch date 1 November 2026 (D13). October runs Step 1's worklist alongside the old ledger; the dry-run is reviewed by Jose in the last week of October.

12.1 Order of operations

1. Ship M1–M4 with ledger_mode='on_add'. Nothing changes. Reservation code paths exist but are unreachable.
2. Run the dry-run report (12.2) as often as wanted; it writes nothing.
3. Resolve the data preconditions by hand: Growth re-platforming (12.5), the 9 multi-package students, the 62 null-expiry packages (12.6).
4. Jose reviews and signs one dry-run. Its generated_at is the snapshot key.
5. Run M18 in one transaction against that snapshot (12.7).
6. Flip academy_settings.ledger_mode='on_done' in the same transaction.

12.2 Dry-run report

A SECURITY DEFINER function ledger_switch_dry_run(p_academy_id uuid) RETURNS jsonb returning, per seat: the seat id, student, lesson date, current consume row if any, the inferred attendance_state, the inferred player_package_id, and the rule that produced it. Written to academy_reconciliation_reports with report_date = current_date and a kind:'ledger_switch_dry_run' marker so Jose reviews it in the same UI as everything else. Counts by rule are the summary.

12.3 Future lessons: compensating rows — red-team finding → design response

Finding (FATAL, RT §3.1): refund_package_session NULLs lessons.player_package_id (20260824200640:353), so calling it during the switch erases the evidence of which package covers each future lesson at exactly the moment the reservation model starts depending on it. HIGH §2.4: reusing an operator-facing reason for machine-reconstructed history "would actively mislead", which is why 20260825073459_backfill_balance_reason.sql exists at all.

Response. A dedicated path with a dedicated reason. For every consume row whose lesson starts after the switch instant and whose seat is a package seat:

-- inside ledger_mode_switch_commit(p_snapshot_at timestamptz), one transaction
WITH future_consumes AS (
  SELECT pst.*, lp.id AS lesson_player_id, l.start_time
  FROM package_session_transactions pst
  JOIN lessons l        ON l.id = pst.lesson_id
  JOIN lesson_players lp ON lp.lesson_id = l.id AND lp.user_id = pst.user_id
  WHERE pst.reason = 'consume'
    AND l.start_time > p_snapshot_at
    AND l.status <> 'cancelled'                  -- RT 3.8: never refund a seat a cancel already refunded
    AND lp.attendance_state IS NULL              -- idempotence is per seat, not per package
    AND pst.player_package_id = lp.player_package_id
)
-- 1. reverse the balance, WITHOUT touching lessons.player_package_id
INSERT INTO package_session_transactions
  (facility_id, player_package_id, player_package_session_id, package_item_id, user_id,
   delta, balance_after, reason, lesson_id, class_id, actor_id, note, source, metadata)
SELECT fc.facility_id, fc.player_package_id, fc.player_package_session_id, fc.package_item_id, fc.user_id,
       +1,
       (SELECT pps.sessions_remaining + 1 FROM player_package_sessions pps
         WHERE pps.id = fc.player_package_session_id FOR UPDATE),   -- same bucket lock, so balance_after was true
       'ledger_mode_switch', fc.lesson_id, fc.class_id, :actor,
       'on_add -> on_done switch, seat converted to a reservation',
       'backfill',
       jsonb_build_object('lesson_player_id', fc.lesson_player_id, 'snapshot_at', p_snapshot_at)
FROM future_consumes fc;
-- 2. move the balance to a reservation on the same bucket
UPDATE player_package_sessions pps
   SET sessions_remaining = pps.sessions_remaining + 1,
       sessions_used      = GREATEST(pps.sessions_used - 1, 0),
       sessions_reserved  = pps.sessions_reserved + 1
 WHERE pps.id IN (SELECT player_package_session_id FROM future_consumes);
-- 3. mark the seat
UPDATE lesson_players lp
   SET attendance_state = 'scheduled', coverage_state = 'covered',
       player_package_session_id = fc.player_package_session_id
  FROM future_consumes fc WHERE lp.id = fc.lesson_player_id;

lessons.player_package_id is never written. The reason is ledger_mode_switch, not refund, so a dispute never reads a machine reversal as an operator refund. source='backfill' and the guard is per seat (attendance_state IS NULL), not the bucket-scoped guard backfill_package_session_ledger uses, which would no-op on every package the ledger already covers (RT §2.7).

12.4 Past lessons: inference — red-team finding → design response

Finding (FATAL, RT §3.2): checked_in_at is written only by the PWA, attended only by the coach-feedback RPCs, and most historical rows have both NULL — the 10 September export shows 698 seats, 52 check-ins and 5 attended flags. "Otherwise → no_show" would label months of ordinary, paid, attended lessons as no-shows.

Response — the rule inverts. Applied in order, first match wins:

The verified check-in window is the date range within which check-in was demonstrably in routine use, computed as the first through last date with at least one checked_in_at and at least 30 % of that day's seats checked in. Outside it, no_show is never inferred. Every row in rules 3, 4 and 6 appears in Jose's dry-run with its rule number.

12.5 Growth — red-team finding → design response

Finding (HIGH, RT §3.3): lesson_packages.package_type is a template column; every player_packages row points at it. Flipping session_bank → recurring retroactively changes expiry and renewal semantics for students who bought the old thing, and billing_period is CHECK-constrained at template level.

Response. D11's intent (Growth bills monthly, so it is a subscription) is kept; D11's stated method ("one-row data fix") is wrong and is not executed. Instead: create a new lesson_packages row, Growth (recurring, billing_period='monthly', billing_mode='manual', same price, same package_items), leave the old template in place and inactive (is_active=false), and migrate students one at a time on their next renewal by issuing a player_packages row against the new template. Historic rows keep pointing at the historic template and keep their historic meaning. This is a listed item in the switch preconditions, not part of M18.

12.6 The two data cohorts

• 62 packages with expires_at IS NULL. Resolved by the coverage predicate treating NULL as never-expiring (6.2), which matches get_player_package_remaining_sessions. No data change is required, but the dry-run lists them so Jose can set a real expiry on any that should have one.
• 9 students with more than one active package. Resolved by the deterministic ORDER BY in 6.2 and by persisting the chosen id on the seat. The dry-run lists each student with the package the rule picks, for confirmation before the switch.

12.7 Snapshot discipline — red-team finding → design response

Finding (MED, RT §3.6): between Jose reviewing and pressing commit, coaches keep checking people in.

Response. ledger_mode_switch_commit(p_snapshot_at timestamptz) takes the reviewed report's generated_at as its argument, runs as one transaction, and refuses rows that moved: any seat whose lesson_players.xmin-equivalent tracking column (attendance_marked_at, checked_in_at, or the existence of a newer ledger row for that seat) changed after p_snapshot_at aborts the whole commit with a list of the moved seats. Jose re-runs the dry-run and re-signs. Partial application is not possible.

12.8 Rollback

M19 is written and tested before M18 runs (RT §3.7). ledger_mode_switch_rollback(p_snapshot_at) reverses the three statements of 12.3 in order: set attendance_state back to NULL for seats whose only ledger row is a ledger_mode_switch row carrying that snapshot_at, restore the counters, and write the inverse ledger_mode_switch rows with delta = -1 and note = 'rollback'. It refuses to run once any seat has moved to a terminal attendance state under the new mode, because that is real new history. ledger_mode flips back to on_add in the same transaction. Both directions are exercised in tc-000-sql-evals (13.2) before the switch is scheduled.

13. Test plan

Specs live in apps/admin/e2e/tc-NNN-*.e2e.ts. The current highest is tc-283; tc-284 is Academy Health. New specs start at tc-285. Every spec: const RUN_ID = Date.now(), beforeAll calls setupBookingData(RUN_ID) and seeds extras with getSupabaseAdmin() asserting each insert's error is null, afterAll deletes extras then cleanupBookingData(data), test.setTimeout(180_000), loginViaOtp(page, testData.adminEmail), domain objects created through the real API with page.request.post(...) asserting status() === 201, dates computed in the facility timezone via toLocaleDateString("en-CA", {timeZone}). No test.skip, no if (visible), no .catch(() => {}); assertions are on rendered values via expectGenuinelyVisible.

13.1 Specs

13.2 SQL invariants

New snippets under supabase/snippets/, run by the existing tc-000-sql-evals.e2e.ts harness, each asserting zero rows:

-- balance_after >= 0 never violated, and it equals the bucket's running sum
SELECT player_package_session_id
FROM (SELECT player_package_session_id, delta, balance_after,
             SUM(delta) OVER (PARTITION BY player_package_session_id ORDER BY created_at) AS running
      FROM package_session_transactions WHERE player_package_session_id IS NOT NULL) t
WHERE balance_after < 0 OR balance_after <> running;

-- reserved never exceeds purchased, and effective remaining never goes negative
SELECT id FROM player_package_sessions
WHERE sessions_reserved < 0 OR sessions_remaining - sessions_reserved < 0;

-- no double consume for one seat
SELECT metadata->>'lesson_player_id' AS seat, COUNT(*)
FROM package_session_transactions
WHERE reason = 'consume' AND metadata ? 'lesson_player_id'
GROUP BY 1 HAVING COUNT(*) > 1;

-- every consumed or reserved seat is a package seat
SELECT lp.id FROM lesson_players lp
WHERE lp.attendance_state IS NOT NULL
  AND lp.player_package_id IS NOT NULL
  AND (COALESCE(lp.paid_amount,0) > 0 OR lp.payment_intent_id IS NOT NULL
       OR lp.payment_method <> 'package');

-- reserved counter agrees with the seats that claim it
SELECT pps.id FROM player_package_sessions pps
WHERE pps.sessions_reserved <> (
  SELECT COUNT(*) FROM lesson_players lp
  WHERE lp.player_package_session_id = pps.id AND lp.attendance_state = 'scheduled');

-- one charge attempt in flight per package-period
SELECT player_package_id, period_start, COUNT(*)
FROM package_charge_attempts WHERE status IN ('pending','processing')
GROUP BY 1,2 HAVING COUNT(*) > 1;

14. Docs sync per PR

Every PR carries all five, and both docs apps must build.

Proposed decision entries:

15. Operations view (M)

Added 17 September. Feature M is the day-to-day view for coaches, community managers and staff: the same five fields on every card in the Staff PWA (apps/calendar_pwa, Flutter) and the admin app, a coach filter, events as programs, and add-player from the phone. Everything below was checked read-only in ~/cowork/setpoint on 17 September.

15.1 What exists

15.2 Schema deltas

No change to lesson_players, lessons, classes or any ledger table. Group coaching is already classes.class_type='group'.

15.3 Card contract

One shape, computed server-side once and read by both apps:

card = { id, kind: 'court'|'private'|'group'|'event'|'match',
         title, start, end, court,
         coach: {id, name} | null,            -- null renders "Unassigned"
         players: {confirmed, capacity},       -- capacity null for court
         waitlist: n,
         source: 'setpoint'|'tps',
         payment: 'paid_tps'|'due_desk'|'unpaid'|'package' }   -- chips: Paid on TPS · Due at desk · Unpaid · Package

kind is derived: lesson with class_type='group' → group; lesson otherwise → private; program occurrence → event; matches row → match; anything else → court. Admin: GET /api/admin/calendar?coach=<id|unassigned> adds the filter and returns cards; PWA: the same derivation in Dart on the rows it already fetches (data_service.dart loads bookings, lessons, lesson_players, coaches), so PR 20 needs no new PWA endpoint.

15.4 Events as programs

• Occurrence model. Programs have no per-occurrence row: program_next_occurrence returns the earliest slot from today and program_session_payload builds one session_id = or://setpoint/session/<program_id> per program (20260616000100 lines 37–52, 92–131). Feature M therefore treats an americano or first-time padel as a one-day program (one row = one occurrence, one card, one roster). A recurring clinic renders one card per date keyed program_id + date, with roster and payment shared across dates as today. A real occurrence table (per-date rosters and payment for recurring clinics) is a separate decision recorded under D19 and is not in PR 21.
• Creation (both apps) asks "what is this?" first. Event creates a one-day programs row (americano or first_time, instructor_id, max_participants, court_id) and the mirror does the rest; the court is blocked by the program's court booking exactly as today's TPS-initiated events block it.
• Roster and waitlist read program_enrollments; the card's waitlist is count(status='waitlisted'), position is waitlist_position. One writer for capacity and roster: program_enrollments when source='setpoint' (TPS joins arrive as confirm_program_enrollment calls and are accepted or waitlisted here, then echoed to TPS), TPS when source='tps' (Setpoint is read-only). Never both.
• Inbound (D19 (a) until the switch): a TPS event with club_id in Pad Thai Padel's courts arrives through the existing OpenRally webhook as a program with source='tps', external_event_id = TPS event id; roster from event_participants (joined and not left), waitlist from TPS. Read-only in Setpoint: add-player on such a card is disabled with the reason "created on TPS". Echo guard, two halves: (1) the outbound mirror stores the TPS event id returned in the webhook acknowledgement into external_event_id in the same transaction and the payload carries origin='setpoint'; (2) the importer skips any TPS event whose origin marker is Setpoint and upserts the rest on external_event_id, so a mirrored event can never return as a second program. The mirror never emits for source='tps' rows.
• TPS deleted=true (20–30 % of events a month) maps to programs.status='cancelled', never a delete.
• Retire the label: once every event is a program, guest_booking_category='event' stops being offered in the admin create dialog; existing rows keep the value and the category filter maps them to kind='event' until they age out.

15.5 Coach both ways

• Outbound: M22 adds instructor to program_session_payload; TPS adds the matched users row (by email) to event_organizers. If no TPS user has that email the payload still carries the name so the TPS event shows "Coach: Carlota".
• Inbound: email equality is not identity (TPS users have no Setpoint auth row; anyone can register a coach's address on TPS). A co-organiser email that matches a coach is a suggestion shown on the card ("Looks like Carlota · confirm"); instructor_id is set only when that coach, or an admin, confirms once, which writes coach_external_links (M22). Later events with a confirmed pair match automatically. No match leaves instructor_id NULL and the card shows Unassigned with the co-organiser name as a hint.
• Reassign coach is one action: it updates programs.instructor_id, writes the audit row and emits a single session_updated (the change guard stops the trigger firing when nothing changed).
• Unassigned is a queue, owned by the manager: Academy Health lists every event within 7 days with instructor_id IS NULL, and the daily schedule message names those within 48 h.
• Change of coach on either side is an update event, not a create; Setpoint writes an admin_audit_log row (PR 4) with before and after.

15.6 Add player from the PWA

Roles per D20: user_facility_roles staff and manager on any card (events need M23, today owner/admin only); coach only where lessons.coach_id = auth.uid() (the RLS already says so) and never on events. Every add writes an audit row. Money: never; the added player is pay_at_venue until a payment is attached.

15.7 Tests (Playwright only)

• tc-290 admin calendar: coach filter shows only that coach's cards; Unassigned shows the americano with no coach.
• tc-291 admin calendar: an event card shows card-type, card-coach, card-players (8 / 12), card-waitlist (3), card-source (TPS).
• tc-292 create dialog: "what is this?" → Event creates a program and the mirror emits one program_session event with instructor.
• tc-293 import: a TPS event webhook creates one program; re-delivery does not create a second (external_event_id unique).
• tc-294 add player on a full event → waitlisted with position 1, never enrolled; on a group lesson → lesson_players row; as a coach on another coach's lesson → 403; as a coach on an event → 403; as staff on an event before M23 → 403, after → 201.
• tc-295 coach link: a TPS co-organiser email matching a coach shows as a suggestion, not as the coach; confirming once sets instructor_id and the next import matches automatically.
• tc-296 echo: mirror a Setpoint event, replay its TPS webhook → no second program; reassign the coach twice to the same person → one or_events row.
• PWA: Flutter widget tests are outside the Playwright rule; PR 20 ships with a screenshot-per-screen demo GIF as the other PRs do.

Open items, marked plainly

• D18 (settled in 04-decisions): slot allocation for a two-slot group on a four-session plan is spread, and the second weekly slot is a priced add-on; date_order stays selectable to reproduce today.
• UNVERIFIED: customers.default_payment_method_id semantics; Stripe live-key readiness for Pad Thai Padel; Meta Business verification status and whether a dedicated WhatsApp number exists; whether apps/admin/src/app/api/admin/payment-requests/_lib/reminders.ts already mints links in a way that changes 4.6 (not read by any reader).
• Stop-gap, flagged: WhatsApp opt-out stored as a crm_customer_tags tag until a first-class preference table is justified (8.2).
• Deliberate deviation from the brief: the ledger reason CHECK gains ledger_mode_switch only. no_show_consume, student_cancel_consume and release are not added, for the reasons in 3.3 and DEC-331.