Setpoint Academy proposal · Working fileAll deliverables · Scope · Mockups

Research — Planning calendar, conflicts, lessons, groups, rosters, cancellations

Read-only deep read of /Users/Alvaro/cowork/setpoint (admin app + supabase migrations + Flutter coach PWA). Date: 2026-09-16. All paths absolute from repo root /Users/Alvaro/cowork/setpoint.


1. Calendar UI + drag-and-drop feasibility

Component tree

File Lines Role
apps/admin/src/components/admin/calendar-page-client.tsx 60 /[facility]/calendar shell. Holds view (day|week|month), selectedDate, categoryFilter.
apps/admin/src/components/admin/academy-planning-client.tsx 104 /[facility]/academy/planning shell. Same three grids, category hard-locked to ["coaching"] (module const COACHING_ONLY, line 13) and adds a coach tab bar. Defaults to week.
apps/admin/src/components/admin/calendar-grid.tsx 1818 Day view. Both orientations, coach-availability overlay, all mutation handlers.
apps/admin/src/components/admin/week-calendar-grid.tsx 1122 Week view (7 days × courts).
apps/admin/src/components/admin/month-calendar-grid.tsx 352 Month view.
apps/admin/src/components/admin/calendar-view-toggle.tsx — day/week/month toggle.
apps/admin/src/components/admin/calendar-occupancy-badge.tsx 97 Occupancy chip.
apps/admin/src/components/admin/calendar-category-filter-select.tsx — Category multi-select (Planning does NOT render it — filter is locked).

There is no separate shared WeekCalendarGrid package. week-calendar-grid.tsx is the shared week grid; Calendar and Planning both import it from @/components/admin/week-calendar-grid. Planning passes hideCourtTabs (planning-client.tsx:92) so its own coach tabs occupy that row.

Props contract (CalendarGridProps, calendar-grid.tsx:193-207)

facilitySlug: string;
initialDate?: Date;
categoryFilter?: CalendarCategoryFilter;   // default ALL_CALENDAR_CATEGORIES
coachFilter?: string | null;               // lesson bookings taught by this coach only
onCategoryFilterChange?: (f) => void;

WeekCalendarGrid takes the same plus onDateClick and hideCourtTabs.

Data sources

Rendering of cards

Two orientations, switched by local state orientation (calendar-grid.tsx:242), defaulting to "vertical":

Toggle testids: orientation-horizontal / orientation-vertical (lines 1142, 1158). Coach-availability switch: toggle-coach-reservations (line 1120). Week lane/empty cells: week-lane-${ds}-${tickKey}-${courtId}, week-empty-…, card week-booking-${id}.

Cards are <button> elements absolutely positioned with style={{ top, height: span * TICK_HEIGHT_PX - 2 }}. Grid geometry is already a clean function of (tickIdx, courtIdx) and STRIDE_MINUTES = 30 — a drop target maps to (courtId, tick) with no new layout maths.

Lesson cards are visually distinguished by border-l-2 border-l-status-hold and show class_name + coach name + lesson_revenue. Coach reservations render as striped overlays; blocks_courts: false rows (DEC-245/DEC-250) render dashed at ~60% opacity with an "Open" badge but stay bookable.

Drag library — present but unused on the calendar

apps/admin/package.json has @dnd-kit/core ^6.3.1, @dnd-kit/sortable ^10.0.0, @dnd-kit/utilities ^3.2.2. The only consumer in the whole app is apps/admin/src/components/admin/court-images-dialog.tsx (sortable image list). No react-dnd, no framer-motion. So drag-and-drop on the calendar is greenfield UI, but the dependency, its version, and an in-repo usage pattern already exist.

Existing move / reschedule APIs — they already exist

What a "move lesson to court X at time T" call needs: one PATCH /api/admin/lessons/[id] with { start_time, end_time, court_ids: [X] }. Everything downstream (conflict checks, booking re-creation, OpenRally events) is already wired. See §2.


2. Conflict engine

Court occupancy

Coach availability gate

supabase/migrations/20260522082857_coach_availability_booking_conflict_gate.sql:

The gate on the lesson side is check_booking_against_coach_reservations('lesson_court', …), called from two triggers rewritten in 20260731102853_lesson_availability_override.sql:

Both short-circuit in this order: status IN ('cancelled','no_show') → availability_override = true → classes.class_type = 'group' → (UPDATE where coach/start/end all unchanged) → run the check.

lessons.availability_override

supabase/migrations/20260731102853_lesson_availability_override.sql adds lessons.availability_override boolean NOT NULL DEFAULT false. Requested by Nestor 2026-07-31 so ad-hoc private lessons don't need a new availability row. Bypasses only the coach gate — court occupancy is still enforced by findCourtConflicts + the no-overlap index, so an override can never double-book a court. create_lesson_atomic gained p_availability_override boolean DEFAULT false as its 19th parameter.

⚠️ Gap found. The edit form sends availability_override on save (lesson-form-fields.tsx:960, lesson-form-dialog.tsx:1039), but PATCH /api/admin/lessons/[id]'s editableFields array (route.ts:117-128) does not include it. The field is silently dropped on every edit — only the create path persists it. A drag-to-move that lands outside coach hours therefore cannot carry an override, and the toggle in the edit dialog does nothing. Worth confirming against tc-264-lesson-availability-override.e2e.ts before building on it.

How a reschedule / court change is validated (PATCH lessons/[id])

Order of operations in apps/admin/src/app/api/admin/lessons/[id]/route.ts:

  1. Build updateData from editableFields; 400 if nothing to update (line 143).
  2. Look up lessons.facility_id + classes.class_type; 404 if missing.
  3. requireFacilityAccess(req, facility_id) — auth gate.
  4. Coach availability validation, private lessons only (line 162): class_type !== 'group' and coach/start/end changed → validateLessonAgainstAvailability(...) against coach_availabilities + same-date availability_exceptions, scoped to courtIds (DEC-237). Returns 400 with the conflict message.
  5. status === 'cancelled' → delegate to cancelLessonCascade and return early (line 227). See §3.
  6. Capacity check against classes.max_pax → 400.
  7. court_ids branch (line 259): validate courtIds.length === classes.num_courts (400) → findCourtConflicts per court excluding own bookings (409 "Court has a conflicting booking at this time") → cancel every existing leg booking → DELETE FROM lesson_courts WHERE lesson_id → insert new bookings (booking_type: 'lesson', price_amount: 0) + new lesson_courts rows. Trigger errors are humanised by translateCoachReservationError (@/lib/translate-coach-error).
  8. Else time-only branch (line 366): findCourtConflicts on each existing court at the new time (409 "Court has a conflicting booking at the new time") → UPDATE each linked booking's start_time/end_time/duration_minutes in place.
  9. UPDATE the lessons row.
  10. Sync players[] if provided (package debit/refund via consume_package_session / refund_package_session).

Events emitted

For a drag-to-move feature this means: reuse PATCH /api/admin/lessons/[id]. Do not write a new mover. The only new server work would be optimistic pre-flight (a "can I drop here?" probe) — and even that can reuse findCourtConflicts behind a small GET.


3. Lessons

lessons columns

From 00000000000000_initial_schema.sql:3804 plus later migrations:

Column Notes
id, class_id, coach_id, facility_id coach_id is NOT NULL in the initial schema. No migration drops that. The "unassigned coach" story is handled by assign-coach after creation, not by a null.
start_time, end_time, duration_minutes CHECK end_time > start_time
price_per_pax_amount_override nullable, >= 0
commission_amount default 0
status CHECK IN ('scheduled','completed','cancelled','no_show') — only four values
notes, player_package_id, created_at
publish_to_openrally added 20260618000000, default false
availability_override added 20260731102853, default false
recurring_group_id added 20260724064533, nullable, indexed. No cross-occurrence FK on purpose — purely a grouping key for "this and future" edits.
requires_payment added 20260619000000
organizer_emails text[]

lesson_courts (initial:3750)

id, lesson_id, court_id, booking_id (nullable), is_primary.

lesson_players (initial:3784 + 20260618000000 + feedback migrations)

id, lesson_id, user_id (nullable), email, phone, is_primary, joined_at, payment_intent_id, paid_amount, payment_method, player_package_id, checked_in_at, then guest_name, guest_email, hold_expires_at, external_payment_ref, external_payment_method_type, status, then coach_feedback, coach_rating, attended. status CHECK IN ('confirmed','held','cancelled').

create_lesson_atomic signature

Current version lives in supabase/migrations/20260824200640_package_session_ledger.sql:635:

create_lesson_atomic(p_class_id uuid, p_coach_id uuid, p_facility_id uuid,
  p_start_time timestamptz, p_end_time timestamptz, p_duration_minutes int,
  p_price_per_pax_amount_override int, p_commission_amount int, p_notes text,
  p_court_ids uuid[], p_per_pax_amount int, p_lesson_payment_method text,
  p_lesson_player_package_id uuid, p_class_type text, p_max_pax int,
  p_primary jsonb, p_players jsonb,
  p_organizer_emails text[] DEFAULT '{}', p_availability_override boolean DEFAULT false)

Raises create_lesson_atomic requires at least one court and … requires p_per_pax_amount >= 0.

Routes

Route Methods Notes
/api/admin/lessons GET, POST GET paginates + filters (facility, coach, customer search). POST requires facility_id, class_id, court_ids, start_time, end_time; delegates to createSingleLesson (api/admin/lessons/_lib/create-single-lesson.ts) → create_lesson_atomic. Returns 201.
/api/admin/lessons/[id] GET, PATCH No DELETE. Deletion is PATCH { status: "cancelled" }.
/api/admin/lessons/recurring POST Body = single-lesson shape + recurrence: { start_date, end_date, days_of_week[] } (0=Sun). Uses computeOccurrenceDates + RECURRENCE_SOFT_CAP_DAYS from @setpoint/common/recurrence. Only group classes may recur — private lessons keep full coach-availability protection.
/api/admin/lessons/[id]/players POST, DELETE, PATCH roster mutations
/api/admin/lessons/[id]/students PATCH used by both calendar grids (check-in etc.)
/api/admin/lessons/[id]/roster GET Server-to-server, X-API-Key auth (ALB-5 AI feedback agent), facility-scoped. Returns only status = 'confirmed' players.
/api/admin/lessons/[id]/assign-coach PATCH { coach_id, scope: "this" | "series" }. Group lessons only (400 otherwise). "series" = same recurring_group_id, start_time >= this one, status != cancelled. Recomputes commission. Skips rather than fails occurrences where the coach is already busy, and reports the skipped ones back.
/api/admin/lessons/[id]/payment-link, …/email POST Stripe link
/api/admin/lessons/[id]/ai-feedback, …/analyze-video POST

Cancellation cascade

apps/admin/src/lib/cancel-lesson-cascade.ts — cancelLessonCascade(supabase, lessonId, { reason, cancelledBy }). Single source of truth, shared by three entry points: PATCH /api/admin/lessons/[id], PATCH /api/admin/bookings, and POST /api/v1/me/bookings/[id]/cancel.

Steps: idempotency gate (returns alreadyCancelled if already cancelled; throws if the lesson doesn't exist) → refund each lesson_players.player_package_id via refund_package_session, then null the link and set payment_method = 'pending' so a re-run can't double-refund → refund the lesson-level player_package_id (private-lesson back-compat) → cancel every lesson_courts.booking_id (skipping already cancelled/completed) → flip lessons.status = 'cancelled' guarded by .neq("status","cancelled"), which fires emit_lesson_status_event → lesson_cancelled → TPS mirror. The header comment records why the flip is mandatory: booking-only cancels used to leave the lesson scheduled, so the coach stayed busy and the slot could not be re-booked.

Returns { ok, alreadyCancelled, lessonId, cancelledBookingIds }. Throws (does not swallow) on any refund or booking-cancel failure.

PATCH /api/admin/bookings with status: 'no_show' on a lesson leg propagates no_show to the lesson and every other leg (route.ts:1368-1390). Note this path does not go through the cascade, so no refunds happen on a no-show.

Flag: I did not find a tc-150 spec in apps/admin/e2e. The cancellation cascade's own coverage should be re-confirmed by name.


4. Groups

Schema — supabase/migrations/20260722032430_add_academy_groups.sql (ALB-38)

academy_groups: id, academy_id, facility_id, name (CHECK length>0), level text, coach_id (FK coaches ON DELETE SET NULL), is_active, created_at. academy_group_members: id, group_id, user_id (FK customers), joined_at, UNIQUE (group_id, user_id).

RLS on both. Writes restricted to user_facility_role(facility_id) IN ('owner','admin','manager'); staff at linked facilities get SELECT. All admin routes use the service-role client and bypass RLS — the policies are defence-in-depth.

The design decision that matters most

The migration header states it explicitly: v1 is roster + level organisation only, NOT linked to a class or schedule, with no auto-booking. A player's group membership is independent of which lesson instances they attend. Membership is many-to-many, so per-group "classes done/remaining" is deliberately not stored — it is derived from player_packages / player_package_sessions.

Confirmed in code: neither academy-groups-panel.tsx nor group-detail-client.tsx references classes, lessons, or lesson_players. There is today no relation whatsoever between a group and a lesson. Building "schedule this group" means creating that link from scratch.

UI

GROUP-COACHING-PLAN.md phase status

/Users/Alvaro/cowork/setpoint/GROUP-COACHING-PLAN.md — dated 2026-06-17, status header still says "Planning", but Phase 1 shipped. 20260618000000_group_coaching_academy_sessions.sql delivers everything in WS-A:

Follow-ups: 20260618010000 player-join events, 20260619000000 requires_payment, 20260619035520 group lessons bypass the coach-availability check, 20260619070257 feedback fixes.

Phase 2 also shipped — lesson_players.coach_feedback / coach_rating / attended exist, with apply_coach_feedback_to_lesson_player (20260707082200, extended 20260707100830) and coach check-in RLS (20260721091646).

Note academy_groups (2026-07-22) is a later, separate concept from the Phase-1 group-coaching lesson work — the migration header calls out that it fills a gap the plan left deferred.


5. Programs waitlist (parked) — reusable for a lesson waitlist

program_enrollments (initial schema:5459): id, program_id, user_id, facility_id, status, waitlist_position int, notes, enrolled_at, cancelled_at, created_at, payment_id, stripe_payment_intent_id. CHECK constrains status to ('enrolled','waitlisted','cancelled').

A fourth status was added later: 20260605094626_events_payment_reconciliation.sql:79-82 drops and re-adds the CHECK as ('enrolled','pending_payment','waitlisted','cancelled'), with a partial index on status = 'pending_payment'. So the live status set is those four. pending_payment is the in-flight paid-but-unconfirmed state, flipped to enrolled by the Stripe payment_intent.succeeded webhook.

Enroll logic: 409 if an active enrolment exists → count status='enrolled' against programs.max_participants → member pricing from an active user_memberships row → if full, waitlist_position = (max existing position) + 1 and return with no payment taken → free + not full: insert enrolled and fire-and-forget send-program-enrollment edge function → otherwise Stripe.

Reusable for a lesson waitlist: the position-assignment pattern (read max, +1) and the "waitlisted takes no payment" branch. What it does not give you is promotion — nothing in this route promotes the head of the waitlist when a seat frees. The group-lesson side already has the better primitive: expire_group_lesson_holds() emits session_spot_opened, which a waitlist promoter could consume.


6. Coach PWA (apps/calendar_pwa)

It is a Flutter web app, not React. lib/ Dart sources, pubspec.yaml, deployed via vercel.json.

Screens: calendar_screen, qr_checkin_screen, coaching_feedback_flow_screen, expense_submit_screen, expense_review_screen, my_expenses_screen, login_screen, plus screens/coach/{availability,coach_schedule,book_session}_screen.dart. Widgets include lesson_detail_sheet.dart, book_coaching_sheet.dart, slot_action_sheet.dart, calendar_grid.dart.

What a coach can do on a lesson (lib/services/lesson_service.dart):

Push notifications: none. pubspec.yaml dependencies are supabase_flutter, intl, shared_preferences, http, mobile_scanner, cupertino_icons — no firebase_messaging, no flutter_local_notifications, no web-push. web/index.html links a manifest.json (Flutter's default installability) and Flutter generates its own flutter_service_worker.js for asset caching only. Notifying a coach of a moved lesson would be net-new infrastructure, not a config change.


7. Cancellation policies

cancellation_policies (initial schema:3199): id, facility_id, free_cancellation_hours int DEFAULT 24, applies_to_bookings bool DEFAULT true, applies_to_matches bool DEFAULT true, created_at, updated_at. 20260513094341_coach_booking_openrally_foundation.sql:76 adds applies_to_lessons boolean NOT NULL DEFAULT true.

The leave-deadline logic lives in POST /api/v1/me/bookings/[id]/cancel, which reads the policy, computes the free-cancellation cut-off from free_cancellation_hours before start_time, and delegates to cancelLessonCascade when the booking is a lesson leg.

Students have no lesson cancellation lane. apps/admin/src/app/api/v1/me/lessons/[id]/route.ts exports only OPTIONS and GET — no POST/PATCH/DELETE, no cancel sub-route. A player can only cancel a lesson court booking through /api/v1/me/bookings/[id]/cancel, which then cascades the whole lesson. There is no per-seat student cancel for a group lesson from the player API, even though the RPC cancel_group_lesson_seat exists for the OpenRally/TPS lane.


8. e2e conventions

Specs live in apps/admin/e2e/tc-NNN-*.e2e.ts, Playwright, run with npm run test:e2e from apps/admin.

apps/admin/e2e/test-helpers.ts (672 lines) — exports

Constants: ADMIN_URL (3100), WEB_URL (3101), TERMINAL_URL (3007), MAILPIT_URL, MAILOSAUR_SERVER_ID, DEFAULT_TIER_THRESHOLDS, wait. Dates: tomorrow(), daysFromNow(n), daysFromNowInTz(n, tz). Auth/DB: getSupabaseAdmin(), ensureTestUser(email, password, { first_name, last_name }), signInAndGetSession(...), loginViaOtp(page, email), loginTerminalViaOtp(...), getOtpViaAdminApi(email), openDrawerSession(facilityId, actorId). Mail: getOtpFromMailpit, clearMailpit, getEmailFromMailosaur, deleteMailosaurMessage, mailosaurAddress, extractStripeUrlFromEmail, MailosaurMessage. Player typeahead: addKnownPlayerViaTypeahead, addNewPlayerViaTypeahead, selectOrAddPlayerViaTypeahead. Payments: clickGetPaymentLink, clickSendPaymentLinkEmail, completeStripeCheckout, readClipboard. Tiers/credits: seedDefaultTierConfigs, insertTopup, getTier, getCreditBalance. Assertions: expectGenuinelyVisible(locator).

Seeding pattern

apps/admin/e2e/setup-booking-data.ts exports setupBookingData(runId) → BookingTestData { facilityId, facilitySlug, adminUserId, adminEmail, adminPassword, courtIds[], courtNames[], pricingRuleId, academyId, coachId, coachUserId, coachName ("Maria Garcia"), classId, className ("Private Padel Lesson"), packageId, packageName, packageItemId } and cleanupBookingData(data).

Convention, from tc-266-academy-planning.e2e.ts: const RUN_ID = Date.now() for unique names → beforeAll calls setupBookingData(RUN_ID) and seeds extras with the service-role client (getSupabaseAdmin()), asserting each insert's error is null with a message → afterAll deletes extras then cleanupBookingData → test.setTimeout(180_000) → in the test, loginViaOtp(page, testData.adminEmail) then create domain objects through the real API via page.request.post(\${ADMIN_URL}/api/admin/lessons`, …)and assertstatus() === 201. Dates are computed in Asia/BangkokviatoLocaleDateString("en-CA", { timeZone: "Asia/Bangkok" })`.

tc-262-academy-groups.e2e.ts adds: two students via ensureTestUser, each given a past completed lesson so they appear as "active"/Unassigned, one with a player_package carrying known sessions_used/sessions_remaining.

Current highest tc number

tc-283 (tc-283-sidebar-edge-toggle-flyout.e2e.ts). tc-280 is absent. Relevant neighbours: tc-262 groups, tc-263 calendar occupancy, tc-264 lesson availability override, tc-265 court covering trigger, tc-266 academy planning, tc-279 academy setup IA, tc-282 group business report. A new spec should start at tc-284.


Not found / flagged

  1. check_court_availability — no such function in this repo. Court conflicts are findCourtConflicts (TS) + idx_bookings_no_overlap (partial unique index). No EXCLUDE USING gist range constraint on bookings, so partial overlaps are guarded only at route level.
  2. tc-150 — no spec by that number in apps/admin/e2e.
  3. availability_override is dropped by PATCH /api/admin/lessons/[id] even though both edit dialogs send it. Likely a real bug.
  4. tc-264-lesson-availability-override.e2e.ts covers only the create path — it asserts the lesson row persists availability_override = true after creation. Nothing exercises the edit path, which is why the dropped PATCH field (item 3) has gone unnoticed.
  5. lessons.coach_id is NOT NULL; the "assign a coach later" flow works by reassignment, not by a null coach.
  6. Coach changes on a lesson emit no OpenRally event, by explicit design decision.
  7. The coach PWA has no push capability of any kind.