Setpoint — session ledger, packages, billing, payments, reminders, POS bridge

Read-only research. Repo root: /Users/Alvaro/cowork/setpoint. All paths below are relative to it.

1. package_session_transactions (the ledger)

Created: supabase/migrations/20260824200640_package_session_ledger.sql:21-48

reason CHECK values

Original set (20260824200640.sql:46): purchase_grant, consume, refund, admin_adjust, renewal_reset, expiry_forfeit, cancellation_forfeit

Widened in supabase/migrations/20260825073459_backfill_balance_reason.sql:27-33 to add backfill_balance. Final set = the 7 above + backfill_balance.

expiry_forfeit and cancellation_forfeit are declared but never written by any code path — no INSERT anywhere uses them. Ledger-visible forfeiture on expiry/cancel does not exist today.

Indexes (:92-96)

(player_package_id, created_at DESC), (user_id, created_at DESC), (facility_id, created_at DESC), (player_package_session_id), partial (lesson_id) WHERE lesson_id IS NOT NULL.

RLS (:100-113)

• SELECT: facility staff via user_facility_roles.
• INSERT: service_role only. authenticated reaches it only through SECURITY DEFINER RPCs.

adjust_package_sessions() signature

20260824200640.sql:521

adjust_package_sessions(
  p_player_package_id uuid,
  p_adjustments       jsonb,   -- [{"session_id":uuid,"sessions_remaining":int,"sessions_used":int}, ...]
  p_reason            text,    -- REQUIRED, raises P0001 if blank
  p_actor_id          uuid DEFAULT NULL
) RETURNS json   -- { adjusted, buckets_changed, sessions_remaining }

SECURITY DEFINER, REVOKE ALL FROM PUBLIC, GRANT ... TO service_role only (:610-612). Locks each bucket FOR UPDATE, refuses a session_id from another package, CONTINUEs on a no-op bucket, writes one admin_adjust row per changed bucket with metadata = {previous_sessions_remaining, previous_sessions_used, sessions_used}.

Who inserts ledger rows (complete list)

All in 20260824200640_package_session_ledger.sql + the backfill migration. No TypeScript ever inserts.

Important: the ledger only ever tracks sessions_remaining. player_packages itself has no counters — the counter pair lives on player_package_sessions (one bucket per package_item). SUM(delta) per bucket == that bucket's sessions_remaining is the stated invariant (:87-88), asserted at the end of the migration (:1028+).

Note a correctness wrinkle worth flagging: refund_package_session restores sessions_remaining but adjust_package_sessions writes sessions_used only into metadata, never as a ledger quantity. sessions_used history is therefore reconstructible only through renewal_reset.metadata.previous_sessions_used.

Read API

apps/admin/src/app/api/admin/students/[userId]/package-transactions/route.ts:91 — the only TS reader.

2. DEDUCT-ON-ADD — where a session is actually taken

2a. Lesson creation → create_lesson_atomic

Redefined verbatim-plus-two-lines in 20260824200640_package_session_ledger.sql:635-860.

Group/any lesson, per player (:806-824 of the file, inside the lesson_players loop):

INSERT INTO lesson_players (
  lesson_id, user_id, email, phone, is_primary, payment_method, player_package_id
) VALUES (...);

IF v_player_payment = 'credits' AND p_per_pax_amount > 0 THEN
  PERFORM record_credit_transaction(...);
ELSIF v_player_payment = 'package'
      AND v_player_package IS NOT NULL
      AND p_class_type <> 'private' THEN
  PERFORM consume_package_session(
    p_player_package_id := v_player_package,
    p_class_id          := p_class_id,
    p_lesson_id         := v_lesson.id
  );
END IF;

Private lesson, lesson-level package, consumed exactly once after the loop:

IF p_class_type = 'private'
   AND p_lesson_payment_method = 'package'
   AND p_lesson_player_package_id IS NOT NULL THEN
  PERFORM consume_package_session(
    p_player_package_id := p_lesson_player_package_id,
    p_class_id          := p_class_id,
    p_lesson_id         := v_lesson.id
  );
END IF;

Then every linked booking with payment_method='package' is flipped to bookings.payment_status='paid' in the same transaction.

So: deduction happens at lesson/player CREATION, not at check-in, not at attendance. There is no lesson_players INSERT trigger — the consume is an explicit PERFORM inside create_lesson_atomic.

2b. Adding a player to an existing lesson

apps/admin/src/app/api/admin/lessons/[id]/route.ts:492-512 — the PATCH player-sync block. For each player in toAdd:

if (isGroupLesson && p.payment_method === "package" && p.player_package_id && lessonClassInfo?.class_id) {
  const { error: consumeError } = await supabase.rpc("consume_package_session", {
    p_player_package_id: p.player_package_id,
    p_class_id: lessonClassInfo.class_id,
    p_lesson_id: id,          // AC-3
  });
  if (consumeError) return NextResponse.json({ error: `Failed to deduct package session for ${p.email}: ...` }, { status: 400 });
}

The lesson_players rows are inserted after the consume loop (:521) — not atomic with it.

2c. Other consume call sites

• apps/admin/src/app/api/v1/lessons/[id]/join/route.ts:98 — 2-arg form, so lesson_id is NULL on the ledger row.
• apps/admin/src/app/api/v1/lessons/[id]/confirm/route.ts:101 — 2-arg form, same gap. This is the public web booking / confirm_lesson_from_hold path.

Flag: both v1 routes still call the backwards-compatible 2-arg overload (20260824200640.sql:304-316), which writes lesson_id = NULL. AC-3 traceability is unmet on the player-facing booking flow.

2d. Cancel / player removal → refund

Player removed from a lesson — apps/admin/src/app/api/admin/lessons/[id]/route.ts:468-482:

for (const p of toRemove) {
  if (p.player_package_id && lessonClassInfo?.class_id) {
    await (supabase as any).rpc("refund_package_session", {
      p_player_package_id: p.player_package_id,
      p_class_id: lessonClassInfo.class_id,
    });   // NOTE: no p_lesson_id, and the error is not checked
  }
}

Then the rows are deleted. The refund error is swallowed here — the only such swallow in this area.

Lesson cancelled — apps/admin/src/lib/cancel-lesson-cascade.ts, the single source of truth, called from PATCH /api/admin/lessons/[id], PATCH /api/admin/bookings, POST /api/v1/me/bookings/[id]/cancel:

1. Idempotency gate: returns {alreadyCancelled:true} if lessons.status === 'cancelled' (:59-61).
2. Per-player refund (:68-85): refund_package_session(p_player_package_id, p_class_id) — no p_lesson_id — then lesson_players gets player_package_id = null, payment_method = 'pending' so a re-run cannot double-refund. Throws on error.
3. Lesson-level refund (:87-98): refund_package_session(pkg, class, p_lesson_id: lessonId) — this one does pass the lesson.
4. Cancels linked bookings; 5. flips lessons.status='cancelled', which fires emit_lesson_status_event → lesson_cancelled OpenRally event.

Refund reason on the ledger is always 'refund' (delta +1). refund_package_session also nulls lessons.player_package_id to prevent double-refund (:353), and transitions the package exhausted → active only (never cancelled/expired).

2e. Check-in and attendance — do they touch the ledger?

No. Neither does anything at all to sessions or the ledger.

• lesson_players.checked_in_at timestamptz — supabase/migrations/00000000000000_initial_schema.sql:3796; coaches granted UPDATE on that single column by 20260721091646_coaches_can_check_in_lesson_players.sql.
• lesson_players.attended boolean DEFAULT NULL — added by 20260622033935_add_feedback_to_lesson_players.sql:5, alongside coach_feedback text, coach_rating smallint (1..5). Written by the coach-feedback RPC (20260707100830...), which emits an OpenRally event but no ledger row.
• Both are read only by business reports (20260902081705_business_report_accuracy_on_perf.sql:634-651).

2f. Is there a status column on lesson_players?

No. Full column list (00000000000000_initial_schema.sql:3783-3798 + additive migrations): id, lesson_id, user_id, email, phone, is_primary, joined_at, payment_intent_id, paid_amount (int, >=0), payment_method (text, unconstrained), player_package_id, checked_in_at, attended, coach_feedback, coach_rating.

There is no status enum and no CHECK on payment_method at the lesson_players level. Values used in code: 'package', 'credits', 'pending', 'stripe', 'xendit', plus whatever the mark-paid path writes.

3. Package tables

player_packages (00000000000000_initial_schema.sql + 3 additive migrations)

There is no price_amount on player_packages — the price lives on lesson_packages.price_amount and must be joined.

lesson_packages (the template)

id, academy_id NOT NULL, facility_id, name, description, package_type (CHECK 'session_bank'|'recurring', default 'session_bank'), price_amount int >= 0, billing_period, valid_days int, schedule jsonb, is_active bool, created_at

• billing_period CHECK originally ('monthly','quarterly'); widened by 20260824200650:26-31 to ('weekly','monthly','quarterly','annual'), still nullable.
• billing_mode text NOT NULL default 'manual', CHECK ('automatic','manual') — 20260824200650:13-18 (DEC-327: every pre-existing row stamped manual).

package_items

id, package_id NOT NULL, class_id, class_type, session_count int > 0

• CHECK: class_id IS NOT NULL OR class_type IS NOT NULL
• class_type CHECK originally ('private','group'), widened to include 'any' by 20260709060349_package_items_any_class_type.sql.
• class_id FK is ON DELETE SET NULL (AC-40 of the lifecycle spec).

player_package_sessions (the counters — one bucket per package_item)

id, player_package_id NOT NULL, package_item_id NOT NULL, sessions_remaining int >= 0 default 0, sessions_used int >= 0 default 0 Bucket-matching priority in consume (20260824200640:245-259): exact class_id → class_type = lesson type → class_type = 'any'; within a tier, smallest bucket first. Uses a CASE (not (cond)::int DESC) to dodge the NULLS-FIRST inversion bug documented at :228-240.

4. process-package-billing edge function

supabase/functions/process-package-billing/index.ts (+ billing-cycle.ts helpers).

Auth: requireMachineCaller(req) — accepts x-internal-secret (the cron) or the service-role key (an operator "Renew").

Flow:

1. Parse body { player_package_id, facility_id, period_start }; 400 without player_package_id.
2. Load player_packages (id, facility_id, user_id, package_id, status, billing_mode, next_billing_date, billing_anchor_day, stripe_subscription_id). 404 if missing.
3. Three skip gates (each returns {skipped:true, reason} 200):
   • status !== 'active' → package_not_active
   • billing_mode !== 'automatic' → manual_billing (AC-43)
   • stripe_subscription_id present → stripe_subscription (AC-41, touches nothing, not even next_billing_date)
   • !next_billing_date → no_next_billing_date
4. Load facility timezone, currency. Compute localToday(tz); if next_billing_date > today → not_due_yet (AC-44, club's calendar not UTC).
5. Load lesson_packages (billing_period, price_amount). 404 if missing. 422 if billing_period IS NULL — deliberately fails loud rather than guessing "monthly".
6. anchorDay = billing_anchor_day ?? anchorDayFrom(periodStart); periodEnd = nextBillingDate(periodStart, period, anchorDay). Both stored as the UTC instant of local midnight so the value is byte-identical on re-fire.
7. Load academy_payment_settings; missing row falls back to {enabled:false, reminder_offsets:[-3,0,3,7], channels:['email'], send_hour_local:9, default_due_days:7}. Settings govern chasing only, never whether the invoice is raised.
8. due_date = period_start + default_due_days (calendar days, local).
9. Insert payment_requests unconditionally: {facility_id, user_id, subject_type:'player_package', subject_id: pkg.id, amount_amount: price_amount, currency, status:'draft', due_date, period_start, period_end} A 23505 is the expected duplicate signal (idempotency is the DB unique index payment_requests_subject_period_key, not an app read-then-write). On 23505 it looks the existing row up and continues — the reconciling steps below still run.
10. Queue reminders: one row per offset, channel:'email', template_key:'package_payment_reminder', status:'queued', scheduled_for = local send_hour on (due_date + offset). Offsets already in the past are not queued. Inserted one at a time (not upsert — PostgREST onConflict cannot match the partial unique index). 23505 counted as reminders_already_queued. Non-email channels reported back as reminders_skipped_channels (WhatsApp has no automated transport — DEC-323).
11. Advance the cycle last, conditionally: UPDATE player_packages SET next_billing_date = periodEnd, billing_anchor_day = anchorDay WHERE id = ? AND next_billing_date = periodStart. A crash anywhere above leaves the package still due for the next hourly run.

It never grants sessions. sessions_granted: false is returned explicitly. DEC-325: sessions are granted only when a payment_request is marked paid.

Stripe is never called from this function. No checkout, no intent, no subscription. Packages already on Stripe are skipped entirely.

Idempotency keys:

• payment_requests_subject_period_key UNIQUE (subject_type, subject_id, period_start) WHERE period_start IS NOT NULL AND status <> 'cancelled'
• payment_reminders_request_template_schedule_key UNIQUE (payment_request_id, template_key, scheduled_for) WHERE scheduled_for IS NOT NULL (later widened to include channel by 20260902172910)
• payment_requests_checkout_id_key UNIQUE (checkout_id) WHERE NOT NULL

dispatch_package_billing() cron

supabase/migrations/20260824200650_academy_billing_cycles.sql:100-145; scheduled at :202-206.

SELECT cron.schedule('dispatch-package-billing',   '0 * * * *', $$SELECT public.dispatch_package_billing()$$);
SELECT cron.schedule('dispatch-payment-reminders', '0 * * * *', $$SELECT public.dispatch_payment_reminders()$$);

Hourly, both. dispatch_package_billing reads internal_function_secret from Vault (raises P0001 if missing — no silent stop), selects billing_mode='automatic' AND status='active' AND next_billing_date <= (now() AT TIME ZONE COALESCE(f.timezone,'UTC'))::date, and fires one net.http_post per package with a 10s timeout. It re-fires every hour until the processor advances the date — deliberate self-healing.

dispatch_payment_reminders() (:150-197) first UPDATEs queued reminders to 'skipped' where the request is paid/cancelled/expired, then dispatches only where aps.enabled = true.

5. Payments / reminders / templates / settings tables

All in supabase/migrations/20260824200644_academy_payments_core.sql.

payment_requests (:32-92)

id, facility_id, user_id, subject_type, subject_id, amount_amount int >= 0, currency, status, provider, checkout_id, checkout_url, checkout_expires_at, due_date date, period_start, period_end, created_by, paid_at, created_at

• subject_type CHECK ('player_package','lesson','membership')
• status CHECK ('draft','sent','viewed','paid','cancelled','expired')
• CHECK (period_start IS NULL) = (period_end IS NULL)
• RLS: staff SELECT; service_role only for writes — a customer can never mark their own request paid.

payment_reminders (:109-176)

id, facility_id, payment_request_id, channel, template_key, status, to_email, to_phone, subject_rendered, body_rendered, provider_message_id, error, scheduled_for, sent_at, attempt int >= 1, created_by, created_at

• channel CHECK ('email','whatsapp')
• status CHECK ('queued','sent','sent_manual','failed','skipped')
• error is persisted and surfaced in the UI (AC-51), never only console-logged.

message_templates (:178-240)

id, facility_id (NULL = shipped system default), key, channel, name, subject, body, is_active, updated_by, created_at, updated_at

• CHECK: channel IN ('email','whatsapp'); subject allowed only on email; btrim(body) <> ''
• UNIQUE (facility_id, key, channel) plus a partial unique index on (key, channel) WHERE facility_id IS NULL (the plain UNIQUE would let two system rows through, since SQL treats NULLs as distinct).
• Resolution: facility row, then system default.
• Seeded keys: package_payment_reminder × email and × whatsapp (:277-300).

academy_payment_settings (:242-275) — this is where the "day 26 / day 3 / day 7" style settings live

There are no freeze / past-due / grace-period day settings anywhere. No "day 26" anchor exists — the billing anchor is player_packages.billing_anchor_day (1..31), a per-student day-of-month, and the due date is derived as period_start + default_due_days. Nothing suspends or freezes a package for non-payment; the only status transitions are active/exhausted/expired/cancelled (exhaustion trigger + expiry cron).

payments (00000000000000_initial_schema.sql:4090-4108 + two migrations)

id, facility_id, user_id, type, reference_id, amount int, currency (default 'usd'), status, stripe_payment_id, payment_method, created_at, bonus_amount int >= 0, notes, reverses_payment_id

• type CHECK widened by 20260824200644:19-24 → ('booking','membership','lesson','merchandise','program','credit_topup','package')
• payment_method CHECK (NULL OR 'stripe','cash','credits','pending','other','package','external') — Xendit has no value of its own, it records as 'external' (settle-payment-request.ts:60-62).
• reverses_payment_id uuid, added by 20260902172043_academy_payment_pos_reversal.sql:46, with UNIQUE partial index payments_one_reversal_per_original on (reverses_payment_id) WHERE NOT NULL.
• status has no CHECK constraint; values used: 'succeeded', and the summary route excludes 'refunded' | 'failed' | 'cancelled'.

POS bridge trigger

supabase/migrations/20260901170806_academy_payment_pos_bridge.sql

• pos_orders.channel CHECK widened to ('pos','terminal','academy') (:42).
• record_academy_payment_in_pos_row(p_payment payments) RETURNS uuid (:56) mirrors one payments row into a POS order + line item + captured payment, keyed on 'academy_payment:' || payments.id via the POS tables' partial-unique source_id indexes.
• Guards — it returns NULL and mirrors nothing when:
  • type NOT IN ('package','lesson')
  • status IS DISTINCT FROM 'succeeded'
  • amount <= 0
  • payment_method IS NULL or IN ('credits','package','pending') — entitlement consumption and unsettled payment-link sales are not takings.
  • the facility has no active pos_locations row.
• Location chosen deterministically: oldest active pos_locations for the facility.
• Trigger: record_academy_payment_in_pos_trigger AFTER INSERT ON payments FOR EACH ROW (:354-358). Same transaction as the money row — a mirror failure aborts the payment.
• Recovery: backfill_academy_payments_into_pos(p_facility_id uuid) (:374).

Reversal (20260902172043): branch keys off reverses_payment_id IS NOT NULL, never off the sign of the amount (:25). It finds the original POS order by source_id = 'academy_payment:' || reverses_payment_id and writes a POS refund; raises if the original mirror is missing (:123). backfill_academy_payments_into_pos is amended to skip reversal rows (:472).

Revert route: apps/admin/src/app/api/admin/academy-payments/revert/route.ts — POST {facility_slug, payment_id, reason}. Append-only: never updates or deletes the original. Inserts a NEW payments row with the negative amount, status:'succeeded', reverses_payment_id: original.id, notes: 'Reverted to due by staff: <reason>'. Reason is mandatory (400 without). 409 if the target is itself a reversal or already reversed. Then sets player_packages.payment_status = 'pending' and re-opens the request. Membership reverts are refused (422) — membership lifecycle is out of scope.

6. Stripe integration today

• apps/admin/src/lib/stripe.ts — thin: getStripe() plus a lazy stripe Proxy for backward-compatible imports. No business logic.
• Stripe Customer ids ARE stored, on the customers table: apps/admin/src/app/api/v1/_lib/stripe-customer.ts — reads customers.stripe_customer_id, else stripe.customers.create() and writes it back (:18-43). Confirmed in packages/database/src/database.types.ts:2353.
• facilities.payment_provider — added as NOT NULL DEFAULT 'stripe' by 20260727025048, redefined nullable by 20260811145715, then backfilled to NULL for everyone by 20260824123907_payment_provider_nullable_backfill.sql. CHECK ('stripe','xendit'). So today no facility can take an online payment, which is the premise of DEC-322/DEC-325.
• Webhook meta types — apps/admin/src/app/api/v1/webhooks/stripe/route.ts, checkout.session.completed branch: booking_payment (:48), lesson_payment (:66), credit_topup (:104), lesson_package_recurring (:145), lesson_package_payment (:246), membership_subscription (:370); payment_intent.succeeded branch: lesson_package (:805), credit_topup (:818), match_join (:850), program_enrollment (:868), group_lesson_join (:883).
• Subscription code (AC-41, untouched by the new feature): lesson_package_recurring creates the player_packages row with stripe_subscription_id (:198), idempotent on that id (:152-166). invoice.paid (:581-670) resolves the subscription, checks memberships first, then calls reset_package_sessions (:654) and failEvent on error (:663). That is the only automatic session grant driven by Stripe.
• Settlement of payment_requests: apps/admin/src/app/api/v1/webhooks/_lib/settle-payment-request.ts — shared by both provider webhooks.
  • Match on checkout_id (unique index guarantees ≤ 1 row).
  • Idempotency is a compare-and-swap in Postgres: UPDATE payment_requests SET status='paid', paid_at=now() WHERE id=? AND status <> 'paid'. Only the winner grants sessions and writes the payments row. The loser still retires queued reminders — self-healing if the winner died mid-way.
  • Subject settlement (settleSubjectForRequest): player_package → set payment_status='paid', payment_method='payment_link', and only if period_start && period_end call reset_package_sessions (a one-off sale already got its sessions at purchase). lesson → update that one lesson_players row. membership → status='active', end_date = period_end.
  • Writes the payments row (type = package/lesson/membership, payment_method = stripe/external, stripe_payment_id = provider ref), guarded by a pre-check on stripe_payment_id against an admin "Mark paid" race.
• supabase/functions/pos-stripe-webhook/index.ts is the POS terminal webhook and does not touch payment_requests (only status = 'payment_requested' tickets).
• No checkout/intent route creates a payment_request checkout in what I read; AC-23 (lazy link generation at reminder-send time via resolvePaymentProvider(facility_id)) lives in the reminder path, apps/admin/src/app/api/admin/payment-requests/_lib/reminders.ts — I did not read that file in full.

7. Acceptance criteria and decisions

apps/docs/src/data/ac-academy-payments.ts — AC-1..AC-53

• Ledger (AC-1..10): table shape; reason/source CHECK sets; consume writes -1 with lesson+class; refund writes +1 preserving lesson_id; reset writes one renewal_reset per bucket before zeroing; adjust_package_sessions persists reason + actor; purchase grant reconciles; backfill from surviving lesson links; migration purely additive; RLS staff-read / service-write.
• Profile (AC-11..14): GET /api/admin/students/[userId]/package-transactions; "Session activity" table on the academy student profile; a new Packages tab on the CRM customer profile; the Adjust-sessions action now persists its reason.
• Payments View (AC-15..21): new /{facility}/academy/payments route; payments.type gains 'package'; unified paginated feed; filters; four tiles (Collected / Outstanding / Overdue / Renewals due next 30 days); row actions (Send reminder, Copy link, Mark paid, Open student); overdue computed in the facility's timezone.
• Payment Requests (AC-22..25): the table; lazy link generation; must work with payment_provider IS NULL; webhooks settle idempotently on checkout_id.
• Templates (AC-26..30): table + resolution order + shared applyMergeTags in packages/common + HTML-escaping of merge values + Academy > Setup editor.
• Reminders (AC-31..38): table; send-payment-reminder edge function; WhatsApp manual wa.me deep link; messaging provider interface; E.164 normalisation; scheduled from reminder_offsets; uniqueness; one automatic reminder per request per 24h.
• Billing (AC-39..47): billing_mode on both package tables; next_billing_date/billing_anchor_day; Stripe path unchanged; automatic-without-card rolls + invoices but grants nothing; manual waits for a human; timezone rollover with month-end clamping; hourly cron idempotent per (player_package, period_start); academy_payment_settings; cycle config in the package form.
• Cross-cutting (AC-48..53): requireFacilityAccess() + facility-scoped RLS everywhere; *_amount minor units; no consumer (TPS) named anywhere; no silent errors; data-testid + focus rings + labels; docs sync.

Explicit out of scope (ac-academy-payments.ts:60-64): dunning/retry beyond the existing invoice.payment_failed handler; migrating clubs off Stripe test keys; issuing monetary refunds (the ledger records session refunds only); and a noted separate bug — crm_campaigns.scheduled_at has no scheduler.

apps/docs/src/data/ac-package-session-lifecycle.ts — AC-1..AC-44

Grouped: Session Consumption (1-6), Session Refund (7-10), Expiration (11-14), Exhaustion (15-18), Recurring (19-23), Lesson Generation (24-25), Admin (26-29), Web UI (30-37), Data Safety (38-42), Accessibility (43-44). Load-bearing ones: SELECT FOR UPDATE against races (AC-2); exact-then-type bucket matching, smallest bucket first (AC-3); refund only exhausted → active (AC-8); double-refund prevented by verifying lessons.player_package_id (AC-9); hourly expiry cron that also cancels future lessons and their bookings (AC-11/12); an AFTER UPDATE exhaustion trigger that also un-exhausts (AC-15/16); invoice.paid uses Stripe's own period_start/period_end as authoritative (AC-20).

Decisions DEC-319..327 (apps/docs/src/data/decisions.ts)

• DEC-319 (data-model) — the append-only package_session_transactions table. Rejected deriving history from lessons.player_package_id, which refund_package_session NULLs, and from counters, which reset_package_sessions zeroes. Precedent: crm_discount_applications.
• DEC-320 (architecture) — ledger rows written inside the existing plpgsql RPCs, same transaction as the counter mutation; the one exception is purchase_grant, written by an AFTER INSERT trigger on player_package_sessions because buckets are created from three places. A ledger failure aborts the balance change.
• DEC-321 (business rule) — widen payments.type with 'package' and have the assignment route write a payments row for in_club / payment_link / credits. Complimentary and zero-price sales write nothing.
• DEC-322 (data-model) — a first-class payment_requests table; the local subject exists first, the provider artefact second and optionally.
• DEC-323 (technology) — WhatsApp ships manual (wa.me deep link, status='sent_manual'); the Cloud API provider throws not-configured. Meta verification is a business lead-time item.
• DEC-324 (architecture) — message_templates keyed (facility_id, key, channel), NULL facility = system default; applyMergeTags extracted into packages/common; email merge values HTML-escaped (stored-injection risk).
• DEC-325 (architecture) — three billing lanes: automatic+Stripe unchanged; automatic-without-card rolls the cycle, raises a request, schedules reminders and grants sessions only on payment; manual never bills itself. Granting on schedule was rejected as "unsecured credit issued by a cron job".
• DEC-326 (infrastructure) — pg_cron → net.http_post → edge function, secret from Vault; two hourly jobs. Vercel cron rejected.
• DEC-327 (data-model) — billing_mode DEFAULT 'manual', and the migration stamps every pre-existing row manual. "A migration must never change what a running business charges people."

8. academy-reports route

apps/admin/src/app/api/admin/academy-reports/route.ts (539 lines). GET ?facility_slug&date_from&date_to&coach_ids. requireFacilityAccess() enforced; all date boundaries computed in the facility timezone via facilityDateBoundaries / utcToFacilityLocal.

Tables read: facilities, academy_facilities, academies, lessons, lesson_players, lesson_courts, bookings, player_packages (joined to lesson_packages for price_amount / package_type), player_package_sessions, coaches, customers.

It does not read package_session_transactions at all. Utilisation and consumption are derived from lesson_players.payment_method='package' counts and from the sessions_remaining + sessions_used counter pair — exactly the lossy sources DEC-319 was written to replace.

Response shape:

• cashflow: total_cash_in, package_sales_amount, package_sale_count, walkin_revenue_amount, walkin_session_count. Package sales counted cash-basis, only payment_status='paid'.
• sessions: total_lessons, completed_lessons, total_hours, package_backed_count, walkin_count.
• costs: total_commission_amount, net_amount.
• targets, academy_id, facility_id, daily_trend (all dates pre-seeded across the span).
• package_health: active_count, active_recurring_count, active_session_bank_count, sessions_consumed_in_period, deferred_revenue_amount (remaining sessions × price-per-session), expiring_soon_count + expiring_soon[] (30-day horizon on expires_at ?? current_period_end), underutilized_recurring[] (recurring packages whose in-period consumption is < 50 % of entitlement, carrying utilization_pct), expired_forfeited: {count, total_unused_sessions, forfeited_value_amount}.
• coach_breakdown[]: per coach lessons, package_sessions, walkin_sessions, hours, commission_amount.

Revenue per player falls back through three sources in order: lesson_players.paid_amount → lesson price override/class default → the linked court booking price divided by pax. paid_amount = 0 is treated as "not recorded", not as zero revenue.

Gaps and things I could not confirm

1. expiry_forfeit and cancellation_forfeit are dead enum values — declared in the CHECK, written by nothing. An expired package's unused sessions are forfeited silently, with no ledger row.
2. The v1 join and confirm routes still call the 2-arg consume_package_session, so the player-facing booking flow writes lesson_id = NULL on its ledger rows. AC-3 is unmet there.
3. Player-removal refunds drop p_lesson_id and ignore the RPC error (apps/admin/src/app/api/admin/lessons/[id]/route.ts:471-476). cancel-lesson-cascade.ts also omits p_lesson_id on its per-player refunds (:72-77) while passing it on the lesson-level one.
4. No freeze / past-due / grace settings exist. There is no "day 26 / day 3 / day 7" config in the schema — the closest is reminder_offsets {-3,0,3,7} relative to a due date and default_due_days 7. Nothing suspends a package for non-payment.
5. I did not read apps/admin/src/app/api/admin/payment-requests/_lib/reminders.ts or subjects.ts, academy-payments/_lib/feed.ts, summary/route.ts, or the send-payment-reminder edge function in full — AC-23's lazy link generation and AC-38's 24h rate limit live there.
6. I did not open .env.production and ran no Supabase, npm or git commands, per the brief. Nothing here is verified against the live database.