PR spec — Academy Health (read-only)

Branch feature/academy-health. Repo /Users/Alvaro/cowork/setpoint. Club pad-thai-padel. Written 2026-09-16. No migration, no writes, no new columns.

1. Goal and non-goals

Goal. One read-only admin page that answers the three questions the club currently answers by hand in the stopgap panel (/Users/Alvaro/Desktop/cowork/ptp-academy-control): who is training without paying, which recorded payments have no provider proof, and what is due on the next 1st. Each tab exports CSV. Source of truth stays in the existing tables.

Non-goals (explicit, state them in the PR).

• No migration, no SQL view, no new column. Every join is done in TypeScript in the route from existing tables and the existing RPC. Nothing here needs set-based SQL: the largest fan-out is ~60 students × ~2 months of lessons, well inside one request.
• No writes. The page never inserts a payment_request, never mints a checkout, never adjusts sessions. Tab 3 computes a proposed bridge amount and stops.
• No WhatsApp send. Tab 3 surfaces the phone so the operator uses the existing manual wa.me lane (apps/admin/src/lib/messaging/providers/whatsapp-manual.ts). Unchanged by this PR.
• No Stripe API call. "No provider reference" is decided from rows already in the database, because Stripe runs on test keys by design (CLAUDE.md:86) and real money arrives through external payment links.
• Does not fix proration (audit row 19) or reconciliation (row 20). It reports both.

2. Files to create / modify

Create:

apps/admin/src/app/api/admin/academy-health/route.ts
apps/admin/src/app/[facility]/academy/health/page.tsx
apps/admin/src/components/admin/academy-health-client.tsx
apps/admin/e2e/tc-284-academy-health.e2e.ts
apps/docs/src/data/ac-academy-health.ts

Modify:

apps/admin/src/components/admin/admin-sidebar.tsx      # nav entry, line ~102
apps/docs/src/data/versions.ts
apps/docs/src/data/feature-matrix.ts
apps/docs/src/data/decisions.ts
apps/apidocs/src/data/api-reference.ts
CLAUDE.md                                              # endpoint/feature list only

No supabase/migrations/**, no packages/database/src/database.types.ts regeneration (no schema change). supabase/snippets/academy_health_evals.sql is not worth adding: the snippets run under tc-000-sql-evals.e2e.ts and assert database invariants, while every rule here lives in route TypeScript. Asserting it twice in two languages invites drift.

3. Data contract

GET /api/admin/academy-health?facility_slug=<slug> → 200.

// apps/admin/src/app/api/admin/academy-health/route.ts (exported for the client)
export type UnpaidTrainingRow = {
  lesson_player_id: string;          // stable React key
  user_id: string | null;
  student_name: string;
  student_email: string | null;
  student_phone: string | null;
  lesson_id: string;
  lesson_start_local: string;        // "2026-09-18 18:00" facility-local
  lesson_date: string;               // "2026-09-18"
  class_name: string;
  class_type: string;                // raw value, so "private" + group-name rule is visible
  coach_name: string | null;
  reason: "no_package" | "package_expired" | "package_inactive" | "no_sessions_left";
  reason_detail: string | null;      // e.g. "Growth expired 2026-08-31"
  duplicate_accounts: number;        // >1 ⇒ same email/phone held by several user ids
};

export type PaymentToVerifyRow = {
  payment_id: string;
  user_id: string;
  student_name: string;
  amount_amount: number;             // satang
  currency: string;
  payment_method: string | null;
  paid_at: string;                   // ISO UTC
  paid_date_local: string;           // "2026-09-03"
  type: "package" | "lesson";
  package_name: string | null;
  player_package_id: string | null;
  flags: Array<"no_provider_reference" | "possible_duplicate">;
  duplicate_of_payment_ids: string[];
};

export type NextFirstRow = {
  player_package_id: string;
  user_id: string;
  student_name: string;
  student_phone: string | null;
  package_name: string;
  package_type: "recurring" | "session_bank";
  billing_period: string | null;
  period_start: string | null;       // facility-local date
  period_end: string | null;
  next_first: string;                // facility-local date of the next 1st
  due_amount: number;                // satang, full list price
  proposed_amount: number;           // satang, bridge; == due_amount when tier "full"
  tier: "PRORATE75" | "PRORATE50" | "PRORATE25" | "full" | "wait";
  days_to_next_first: number;
  mid_cycle: boolean;
};

export type AcademyHealthResponse = {
  facility: { id: string; slug: string; timezone: string; currency: string };
  generated_at: string;
  next_first: string;
  tiles: {
    unpaid_training_count: number;
    unpaid_training_students: number;      // distinct user_id
    payments_to_verify_count: number;
    payments_to_verify_total: number;      // satang
    next_first_count: number;
    next_first_due_total: number;          // satang
    next_first_proposed_total: number;     // satang
  };
  unpaid_training: UnpaidTrainingRow[];
  payments_to_verify: PaymentToVerifyRow[];
  next_first: NextFirstRow[];              // key `renewals` to avoid clashing with next_first above
};

Rename the scalar to next_first_date and keep renewals for the array. Errors follow the sibling route: 400 missing slug, 404 facility, 401/403/503 straight from requireFacilityAccess, 503 with a console.error on any query failure (no-silent-errors, as in academy-payments/route.ts:194).

4. Route skeleton and shared preamble

Copy the first 55 lines of apps/admin/src/app/api/admin/academy-reports/route.ts verbatim in shape:

import { NextRequest, NextResponse } from "next/server";
import { createAdminClient } from "@setpoint/database/admin";
import {
  utcToFacilityLocal, facilityDateBoundaries, facilityToday, shiftDateStr,
  monthStartStr, daysBetweenInclusive,
} from "@setpoint/common/datetime";
import { requireFacilityAccess } from "@/lib/auth";

1. read facility_slug; 400 if absent.
2. facilities → id, timezone, currency; 404 if absent.
3. const access = await requireFacilityAccess(req, facility.id); if (access instanceof NextResponse) return access;
4. Role gate — requireFacilityAccess admits coach, so add after it: if (!["owner","admin","manager"].includes(access.role)) return NextResponse.json({ error: "Forbidden" }, { status: 403 }); Mirror the constant already used in academy-payments-client.tsx:62.
5. const tz = facility.timezone || "UTC" and const today = facilityToday(tz).
6. academy_id via academy_facilities → .eq("facility_id", facility.id).limit(1).single().
7. Resolve every student once at the end from customers (id, first_name, last_name, email, phone) with .in("id", [...userIds]) — same map pattern as academy-payments/route.ts:185-205.

5. Query logic per tab

Tab 1 — Training without paying

Window: from = shiftDateStr(today, -30), to = shiftDateStr(today, +31), converted with facilityDateBoundaries(from, tz).start and facilityDateBoundaries(to, tz).end.

supabase.from("lessons")
  .select(`id, start_time, status, coach_id, class_id,
           classes!inner(id, name, class_type, price_per_pax_amount),
           lesson_players(id, user_id, status, player_package_id, guest_name, guest_email, phone)`)
  .eq("facility_id", facility.id)
  .neq("status", "cancelled")
  .gte("start_time", fromBounds.start)
  .lt("start_time", toBounds.end);

Keep a lesson when classes.class_type === "group" || /group/i.test(classes.name) — the ILIKE half is load-bearing: "Group Coaching 90 Mins" is stored class_type='private'. Keep a lesson_players row when status === "confirmed" (the roster route uses exactly that filter, lessons/[id]/roster/route.ts:39) and user_id is not null. Guest-only rows (user_id null, guest_name set) are excluded — they have no package to check and would be noise. Say so in the empty state copy.

Then load every package for those users:

supabase.from("player_packages")
  .select(`id, user_id, status, expires_at, current_period_start, current_period_end,
           purchased_at, package_id, lesson_packages!inner(id, name, package_type, valid_days,
           billing_period, price_amount)`)
  .eq("facility_id", facility.id)
  .in("user_id", [...userIds]);

"Covers that lesson date", evaluated per (user, lesson):

• status must be active. Anything else ⇒ package_inactive.
• package_type === 'recurring': covered when current_period_start <= lessonDate && lessonDate <= current_period_end. If either period field is null, fall back to purchased_at <= lesson && (expires_at is null || lesson <= expires_at).
• package_type === 'session_bank': covered when purchased_at <= lesson and the expiry test passes, where expiry = expires_at ?? purchased_at + valid_days (credit packs are valid_days = 90). A null expires_at with a null valid_days means no expiry — treat as covered, never as expired. Then call get_player_package_remaining_sessions(p_player_package_id, p_class_id); <= 0 ⇒ no_sessions_left. Batch these with Promise.all over the distinct (player_package_id, class_id) pairs, not per row.
• Do not infer monthly from package_type: Growth is session_bank with billing_period='monthly' (SETPOINT-PORT §"Data shapes").
• If several packages exist, the player is clean when any one covers. Report the best failure reason in priority no_package > package_expired > no_sessions_left > package_inactive.

Edge cases to handle and to name in the PR:

• Multiple accounts per person. Tony Palomino holds three. Group customers by lowercased email and by normalised phone; set duplicate_accounts to the group size. Never merge silently — the row still flags, but the UI shows a "2 accounts" badge so the operator checks the sibling account before chasing. Name-only matches are ignored entirely.
• Apple private-relay and @setpoint.local emails are placeholders; exclude them from the email grouping key so two placeholders do not collapse into one person.
• A lesson whose lesson_players.player_package_id is already set still gets checked — the link can point at an expired package.

Tab 2 — Payments to verify

Month bounds: monthStartStr(today, 0) and monthStartStr(today, -1), then facilityDateBoundaries(prevMonthStart, tz).start as the lower bound and now as the upper.

supabase.from("payments")
  .select("id, user_id, type, amount, currency, payment_method, status, reference_id, stripe_payment_id, created_at, reverses_payment_id")
  .eq("facility_id", facility.id)
  .in("type", ["package", "lesson"])
  .eq("status", "succeeded")
  .gte("created_at", lowerBound);

Drop rows where payment_method is cash, and also credits/package/pending — those are entitlement consumption, not money, exactly as the POS bridge skips them (20260901170806_academy_payment_pos_bridge.sql). Drop any row that is a reversal (reverses_payment_id not null) and any row that has been reversed (its id appears as another row's reverses_payment_id).

no_provider_reference when all three are true:

• payments.stripe_payment_id is null (note the column is stripe_payment_id, not ..._intent_id — that spelling lives on player_packages);
• for type='package', the player_packages row at payments.reference_id has stripe_payment_intent_id null;
• no payment_requests row for the facility with subject_id = payments.reference_id and a non-null checkout_id.

Load those two in one round trip each: player_packages ... .in("id", packageRefIds) and payment_requests ... .eq("facility_id", id).in("subject_id", refIds) selecting subject_id, checkout_id, paid_at.

possible_duplicate: same user_id, same amount, two created_at within 72 hours. Flag both rows and cross-list ids in duplicate_of_payment_ids. Ambassador rows sit at 25% of list price and are legitimate — they are not a duplicate signal, and they must not be flagged just for being an unrecognised amount. This tab flags missing proof, never unexpected amount.

Tab 3 — Next 1st worklist

nextFirst = first day of the next facility-local month, from monthStartStr(today, 1). days_to_next_first = daysBetweenInclusive(today, nextFirst) - 1.

supabase.from("player_packages")
  .select(`id, user_id, status, current_period_start, current_period_end, purchased_at,
           next_billing_date, billing_anchor_day, billing_mode,
           lesson_packages!inner(id, name, package_type, billing_period, price_amount)`)
  .eq("facility_id", facility.id)
  .eq("status", "active");

Keep a row when lesson_packages.billing_period === 'monthly' — that, not package_type, is the monthly test. due_amount = lesson_packages.price_amount.

Bridge, only for rows where package_type === 'recurring':

const weeks = Math.min(Math.floor(days_to_next_first / 7), 4);
const proposed = Math.round(price_amount * weeks / 4);

Tier, evaluated in this order: days_to_next_first <= 7 ⇒ "wait"; weeks >= 4 ⇒ "full"; weeks === 3 ⇒ "PRORATE75"; 2 ⇒ "PRORATE50"; 1 ⇒ "PRORATE25". The codes are named for what the student pays and already exist in the live Stripe account, restricted to the "PTP Academy Monthly Packages" product — the page only labels, it never applies one.

mid_cycle is true when current_period_start is set and its day-of-month is not 1. A row that is not mid-cycle gets proposed_amount = due_amount and tier "full".

Session banks are never prorated. They appear in the tab with tier: "full", proposed_amount = due_amount and a UI note "carries over, nothing due on the 1st", so the operator can see the whole monthly-billed population without being offered a bridge that does not apply. Rounding is on satang, so 9,500 THB at 75% is 712500 satang, not a float.

6. UI spec

page.tsx is a server shell identical in shape to apps/admin/src/app/[facility]/academy/payments/page.tsx — heading "Academy Health", subtitle "Who is training unpaid, which payments lack proof, and what renews on the 1st", then <AcademyHealthClient facilitySlug={facility} />.

academy-health-client.tsx mirrors academy-payments-client.tsx: "use client", one useEffect fetch, Skeleton while loading, shadcn Table, Badge, Button, a segmented view switch built the same way as academy-payments-view-switch. Money renders through formatPrice(amount, currency) from @setpoint/common/currency — no raw satang may reach the DOM. Dates render facility-local via utcToFacilityLocal.

Tiles row (data-testid="academy-health-tiles"), four cards: Training unpaid (count + students), Payments to verify (count + formatPrice total), Due on the 1st (count + total), Proposed after bridges (total).

testids follow the sibling convention exactly: container academy-health-<tab>, each row academy-health-<tab>-row with data-row-id, empty state academy-health-<tab>-empty, CSV button academy-health-<tab>-export, tile values academy-health-tile-<name>-value.

Empty states are sentences, not "No data": e.g. "Every player on a group lesson in this window has a package that covers it. Guests without an account are not checked." A non-owner/admin/manager lands on academy-health-forbidden rather than an empty table.

CSV: client-side, no new endpoint. import { csvCell } from "@setpoint/common/csv", build lines.join("\n"), new Blob([...], { type: "text/csv;charset=utf-8;" }), filename academy-health-<tab>-<facility>-<YYYY-MM-DD>.csv. Copy downloadCsv from apps/pos/src/app/(dashboard)/reports/sales-report/page.tsx:235. Amounts export as decimal via toDisplayValue, never satang. This is the first CSV in apps/admin (audit row 29) — say so in the PR.

Nav: add to apps/admin/src/components/admin/admin-sidebar.tsx after the Reports entry (line ~102): { icon: HeartPulse, label: "Health", href: "/academy/health" }, importing HeartPulse from lucide-react alongside the existing icons.

7. Playwright test plan

File apps/admin/e2e/tc-284-academy-health.e2e.ts (current highest is tc-283).

beforeAll via getSupabaseAdmin() from apps/admin/e2e/test-helpers.ts, all rows suffixed with Date.now() for parallel safety, seeding one fixture per rule:

1. student A on a class_type='group' lesson tomorrow, no player_packages row → no_package;
2. student B on a class named "Group Coaching 90 Mins" stored class_type='private', package expired yesterday → proves the name rule and package_expired;
3. student C with an active session-bank package, expires_at null and valid_days null → must not appear (the null-expiry trap);
4. student D, payments row type package, status succeeded, method payment_link, no stripe_payment_id, linked player_packages.stripe_payment_intent_id null, no payment_requests.checkout_id → no_provider_reference;
5. student E, two identical-amount payments 1 day apart → both carry possible_duplicate;
6. a cash payment → must not appear;
7. student F, active recurring monthly package, current_period_start set to the 8th of the current month, price 9,500 THB → tier depends on the run date, so assert against the value the test computes with the same formula rather than a hardcoded string, and additionally assert the ≤7 days ⇒ wait branch with a second fixture whose period start makes it deterministic.

Assertions are on rendered values per .claude/rules/e2e-testing.md: read row text, assert the student name, the reason label, and THB 7,125.00-shaped currency with no raw minor units. Assert the tile counts match the visible row counts. Assert the CSV button downloads (page.waitForEvent("download")) and the suggested filename matches. Assert a coach role gets 403 from the route and sees academy-health-forbidden. No test.skip, no if (visible), no .catch(() => {}). Navigate with domcontentloaded, never networkidle. OTP through Mailpit at Supabase port + 3.

afterAll deletes in FK order: lesson_players → lessons → payments → player_packages → lesson_packages → classes → customers/auth users, each scoped by the seeded ids only.

8. Docs-sync checklist

.claude/rules/docs-sync.md fires because this PR touches apps/admin/src/app/api/**.

• apps/docs/src/data/versions.ts — new version entry, bump e2eTests by 1 (tc-284), add "Academy Health" to the feature list.
• apps/docs/src/data/feature-matrix.ts — bump the admin endpoint count by 1; flip audit rows 21 (attendance vs payment) and 29 (CSV export) from MISSING to PARTIAL with a pointer to this page; add a note on row 20 that an unmatched-payment report now exists in the UI.
• apps/apidocs/src/data/api-reference.ts — GET /api/admin/academy-health, query param facility_slug, roles owner/admin/manager, the response shape from §3.
• apps/docs/src/data/ac-academy-health.ts — new file, 12 criteria:
  1. The page is reachable at /[facility]/academy/health and appears in the academy sidebar.
  2. owner, admin and manager can load it; coach receives 403 and a forbidden panel.
  3. The route takes facility_slug, resolves the facility, and returns 404 for an unknown slug.
  4. Tab 1 lists every confirmed, non-cancelled lesson player in [today-30d, today+31d] on a class that is class_type='group' or whose name contains "group", case-insensitive.
  5. A player whose active package covers the lesson date does not appear in Tab 1.
  6. A session-bank package with a null expires_at and null valid_days counts as covering, and its holder does not appear.
  7. Tab 1 states a reason per row: no package, expired, inactive, or no sessions left.
  8. Tab 2 lists succeeded package/lesson payments from the current and previous facility-local month whose method is not cash and which carry no Stripe id, no package payment-intent id and no matching payment_requests.checkout_id.
  9. Two payments from one user for the same amount within 3 days are both flagged as possible duplicates; a cash payment is never listed.
  10. Tab 3 lists active packages whose template billing_period is monthly, with the next facility-local 1st, the full due amount and a proposed bridge computed as round(price × min(floor(days_to_next_1st/7), 4) / 4).
  11. Tier labels are PRORATE75 / PRORATE50 / PRORATE25 / full / wait, with wait winning whenever seven or fewer days remain, and session banks always shown as full.
  12. Every tab exports CSV with decimal amounts, and the page performs no database write of any kind.
• apps/docs/src/data/decisions.ts — next free DEC- number:

  Academy Health is read-only and migration-free. The three questions the club answers by hand (training unpaid, payments without provider proof, the next 1st) are all derivable from lessons, lesson_players, player_packages, payments and payment_requests. A SQL view was considered and rejected: the set is ~60 students, the rules (the "Group" name override, the null-expiry fallback, the bridge formula) change with business policy rather than with schema, and a view would fix them in a migration that cannot be one-shot edited. The page proposes a bridge amount and never charges it, because proration (audit row 19) does not exist in the billing path yet and a computed number the operator reads is safe where an automatic charge is not.

• CLAUDE.md — add the endpoint to the route list only.
• Then cd apps/docs && npm run build and cd apps/apidocs && npm run build.

9. PR description skeleton

TLDR
Read-only Academy Health page with three tabs and CSV export. No migration, no writes.

Root cause
Three questions the club answers by hand every month have no answer in the product:
who trained without a covering package, which recorded payments have no provider proof,
and what falls due on the next 1st. Audit rows 19, 20, 21 and 29.

Files changed
<file-by-file, each with why>

Reasoning and trade-offs
- Why no migration / no view (see DEC-xxx).
- Why the "Group" name rule exists ("Group Coaching 90 Mins" is stored private).
- Why "no provider reference" is decided from rows, not from the Stripe API
  (test keys in production by design, CLAUDE.md:86).
- Why Tab 3 computes and does not charge.
- Why guests without an account are excluded from Tab 1.

Agent context
Written for a zero-context reader: what academy packages are, what satang is,
what the next-1st cycle means, and where the stopgap panel lives.

Demo before PR is mandatory (CLAUDE.md:121-123): record a narrated pass over the three tabs and a CSV download, attach to the PR. A written summary does not substitute.

10. Build order

Roughly 8 hours of focused work.

11. What could break, and the pre-flight

• Local stack down. supabase status first; Mailpit is on the Supabase port + 3 and the OTP login in the e2e suite fails silently-looking without it. No Mailosaur locally.
• Port clash. Setpoint and OpenRally share a port in this dev setup; start Setpoint's stack alone.
• Branch hygiene. git fetch origin main && git merge origin/main before opening and again before merging. Pre-commit hook rejects direct-to-main. Branch must be feature/academy-health.
• requireFacilityAccess is not enough. It returns role, and coach passes it. Forgetting the explicit role check ships the club's unpaid-student list to every coach. This is the single highest-risk line in the PR.
• Column-name traps. payments.stripe_payment_id (not _intent_id); player_packages.stripe_payment_intent_id; payment_requests.amount_amount (not amount); lessons has no name, the name is on classes.
• Timezone. Every boundary must go through facilityDateBoundaries / facilityToday / monthStartStr. A raw new Date() shifts the month boundary by seven hours for Bangkok and silently moves rows between the current and previous month in Tab 2.
• PostgREST nesting. lessons → lesson_players with an embedded filter is fragile; filter lesson_players.status === 'confirmed' in TypeScript after the fetch rather than with .eq("lesson_players.status", ...).
• RPC fan-out. get_player_package_remaining_sessions takes one pair at a time. Deduplicate the pairs before Promise.all or a 60-student club issues hundreds of calls per page load.
• Playwright timeout. Run in the foreground with the timeout at or under 600s; the suite is long and a backgrounded run loses the video proof.
• Production data. If the spec is ever verified against the live database: no automated cleanup, and never DELETE or UPDATE … WHERE from test code.