Research — CRM, messaging, roles, audit, export, docs conventions

Read-only survey of /Users/Alvaro/cowork/setpoint (monorepo: apps/{admin,pos,terminal,web,calendar_pwa,docs,apidocs,staff-docs,openrally-docs,apps}, packages/{common,database}, supabase/{migrations,functions}). 189 migrations; 00000000000000_initial_schema.sql is the squashed baseline (141 CREATE TABLEs).

1. CRM data model

Tables (all in supabase/migrations/00000000000000_initial_schema.sql unless noted)

Note the tag/note tables key on profile_id (= customers.id = auth.users.id), not on a facility-customer row id.

API surface

apps/admin/src/app/api/admin/crm/ — customers (+ [id], [id]/notes, [id]/notes/[noteId], [id]/tags, [id]/tags/[tagId], [id]/timeline, [id]/coaching, [id]/discounts, customers/merge, customers/delete), tags, segments (+ preview, [id]/members), campaigns (+ [id]/send, [id], webhook, ai-design, upload-image), groups (+ [id]/members), discount-rules (+ preview, [id]/applications), templates, email-settings, analytics, pos-categories, pos-catalog-items.

The customer list is a thin wrapper over an RPC: apps/admin/src/app/api/admin/crm/customers/route.ts:38 calls crm_search_customers(p_facility_id, p_q, p_filter, p_sort, p_limit, p_offset) — filter/search/sort/paginate all in SQL, page size 50.

Timeline is computed, not stored. apps/admin/src/app/api/admin/crm/customers/[id]/timeline/route.ts:28 fans out six parallel queries (bookings, match_players, program_enrollments, memberships, credit transactions, pos_orders) and merges them chronologically. There is no customer_timeline table.

Merge / delete audit migrations

supabase/migrations/20260705120233_merge_customer_records.sql (364 lines) — merge_customer_records(p_keeper_id, p_loser_id, p_credit_resolution, p_membership_resolution). Re-points ~48 FKs onto the keeper, then deletes the loser's auth account. Two conflicts return needs_input with zero writes until re-invoked with a resolution: p_credit_resolution (combine | keep_keeper) and p_membership_resolution (keep_keeper | keep_loser). A dropped membership's stripe_subscription_id is surfaced in the response but never cancelled — billing actions need their own explicit path. Helper public._merge_role_rank(text) ranks owner=1 … guest=7 (IMMUTABLE, deliberately not inlined so the ranking can't drift). Audit table created here:

CREATE TABLE IF NOT EXISTS public.crm_customer_merges (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  facility_id uuid NOT NULL REFERENCES public.facilities(id),
  keeper_id uuid NOT NULL, loser_id uuid NOT NULL,
  performed_by uuid NOT NULL REFERENCES auth.users(id),
  credit_resolution text, membership_resolution text,
  result jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);

supabase/migrations/20260706041805_delete_customer_record.sql (174 lines) — preview_customer_delete(p_customer_id) returns a jsonb count sheet (bookings, payments, packages, credit_balance_amount, memberships, matches organised, notes authored, and other_players_affected — deleting a match organiser cascades away other customers' match history). delete_customer_record(...) then nulls the nullable NO-ACTION FKs (crm_customer_group_members.added_by, crm_customer_tags.assigned_by, crm_tags.created_by, api_keys.created_by, program_enrollments.user_id) and explicitly deletes the NOT-NULL actor rows (crm_notes.author_id, expense_claims.submitted_by, match_result_disputes.disputed_by, match_results.submitted_by, crm_discount_applications.profile_id) before the final auth delete. Product rule recorded in the header: always offer merge first, but "delete anyway" must actually work.

Lead / prospect / source — FLAG

• No lead or prospect concept exists anywhere. Repo-wide grep over apps/admin/src, supabase/migrations and packages returns nothing.
• No users.source or signup_source. There is no users table at all; the profile table is customers.
• The only source-like column is facility_customers.source (default 'organic') + source_id + metadata jsonb, and that table is very likely not deployed to production — see §8.
• ac-crm-plan.ts mentions a funnel twice, both as unbuilt crm-v2.0 scope: line 200 "Program conversion funnel — track casual → trial → enrolled → completed → re-enrolled drop-off", and line 467 the marketing card "Program Conversion Funnel". That is a program-enrolment funnel over existing customers, not lead capture.
• prd-crm-v1.ts is a spec for a separate apps/crm Next.js app (Vercel project crm-setpoint, root apps/crm) that does not exist in the repo. Scope: scaffold, auth, facility selector, customer directory, contact detail, staff notes, colour tags. No leads.

Conclusion: a lead/prospect/funnel model would be greenfield.

2. Messaging

Provider interface — apps/admin/src/lib/messaging/

types.ts defines:

• MessagingChannel = "email" | "whatsapp"
• MessagingProviderId = "email" | "whatsapp_manual" | "whatsapp_cloud"
• RenderedMessage { subject?: string | null; body: string } — merge tags already applied and already escaped for the channel; providers transport, never render.
• SendMessageRequest { facilityId, facilityName, recipient: { email?, phone? (E.164 only), name? }, message, templateKey, paymentRequestId? }
• SentMessageStatus = "sent" | "sent_manual"
• SentMessage { status, provider, providerMessageId: string | null, actionUrl?: string } — actionUrl present iff sent_manual.
• MessagingProvider { readonly id; readonly channel; send(req): Promise<SentMessage> }

index.ts — resolveMessagingProvider(channel, providerId?). CHANNEL_DEFAULTS = { email: "email", whatsapp: "whatsapp_manual" }. Synchronous on purpose: the payments twin reads facilities.payment_provider (a column that exists); the messaging equivalent was to live on academy_payment_settings and the per-facility lookup was deferred to "step 11". Throws on unknown channel, unknown provider, or channel/provider mismatch rather than falling back.

providers/email.ts — does not call Resend. Invokes the send-payment-reminder edge function via supabase.functions.invoke, body { recipient_email, recipient_name, facility_id, facility_name, subject, html_body, template_key, payment_request_id }. Checks both error and data?.error. Returns providerMessageId: data?.message_id.

providers/whatsapp-manual.ts — calls buildWhatsAppDeepLink(phone, body) from @setpoint/common/whatsapp, returns status: "sent_manual", providerMessageId: null, actionUrl. Body is plain text and deliberately not HTML-escaped (percent-encoded at link-build time instead).

providers/whatsapp-cloud.ts — stub that throws. Documented as missing: a Meta Business account through Business verification, a dedicated sending number not already on a personal WhatsApp account, and Meta-approved template messages (free-form is blocked outside the 24-hour customer-initiated window, and a payment reminder is always business-initiated). It throws rather than silently falling back to the manual lane. No env vars are read — there is no config to supply yet.

DEC-323 (apps/docs/src/data/decisions.ts:108)

Date 2026-08-25. Question: "Nestor asked for reminders over WhatsApp or e-mail. There is no WhatsApp, SMS or LINE integration anywhere in the monorepo. Which transport?" Decision: ship the manual wa.me lane now, stub the automated one, both behind MessagingProvider. Alternatives rejected: Meta Cloud API now (deferred, not rejected — business lead time), Twilio (same Meta template gating plus per-message cost), email only (rejected: WhatsApp is where these customers read messages), tel:/SMS fallback (no SMS provider either). Open with Nestor: whether to stand up the Cloud API, and which number it sends from — that is openQuestions Q1/Q2 referenced from both WhatsApp provider files. A later entry (decisions.ts:40) records that a WhatsApp-only club still cannot queue automatic reminders: rows would sit at queued forever, so the skip is now reported as reminders_skipped_channels rather than silent.

message_templates — supabase/migrations/20260824200644_academy_payments_core.sql:178

Columns: id, facility_id (nullable = system default), key, channel, name, subject, body, is_active, updated_by, created_at, updated_at. Constraints: channel IN ('email','whatsapp'); subject only allowed when channel='email'; btrim(body) <> ''; UNIQUE (facility_id, key, channel) plus a partial unique index message_templates_system_default_key on (key, channel) WHERE facility_id IS NULL — SQL treats NULLs as distinct, so the plain UNIQUE would admit two system rows. Resolution is facility-row-then-system-default.

Only one key is seeded: package_payment_reminder, in both channels (lines 288 and 297). Email subject: Payment reminder for {{package_name}} at {{facility_name}}.

Merge tags — packages/common/src/merge-tags.ts

One renderer, extracted from the CRM campaign route so two implementations of "what does {{first_name}} mean" cannot drift. Pattern is /\{\{(\w+)\}\}/g — word chars only, no whitespace, so {{ name }} and {{first name}} render literally. Unknown keys render literally (own-property guard). MERGE_TAG_KEYS (a chip list for the editor, explicitly not an allow-list): first_name, full_name, facility_name, package_name, amount, due_date, payment_link, sessions_remaining. applyMergeTags = verbatim (plain-text channels); applyMergeTagsHtml escapes the substituted values only, never the operator's template markup. The CRM campaign route supplies its own extra {{email}} tag without registering it.

send-payment-reminder edge function — supabase/functions/send-payment-reminder/index.ts (466 lines)

Two callers, two shapes, documented at lines 12–46:

1. PRE-RENDERED — the admin email provider passes subject + html_body already merge-tagged and escaped. The function only transports and returns the Resend id; the admin route owns the payment_reminders row and writes the outcome.
2. RENDER — the hourly pg_cron dispatcher dispatch_payment_reminders() posts { reminder_id, facility_id } via net.http_post. No admin process in that path, so the function loads the queued payment_reminders row, its payment_request, the customer, academy_payment_settings and the resolved template, renders, sends, and writes status / provider_message_id / error back.

Auth: requireMachineCaller (from _shared/send-email.ts) accepts either the cron x-internal-secret or the service-role key as Bearer. verify_jwt = false in config.toml because pg_net attaches no JWT. Known limit, stated in the header: it does not mint hosted checkout links. Lazy link generation lives in the admin app where Stripe/Xendit already are; in RENDER mode it uses the link already persisted on the payment_request, else falls back to the facility's pay-at-club instructions. Default fallback string: "Online payment is not set up yet — please settle at the front desk on your next visit." The merge-tag renderer is duplicated into the function (Deno cannot resolve the npm workspace package) with a "keep the two in sync" comment. It also carries bodyToHtml (newlines → <br>, bare URLs → anchors) and wrapHtml (the standard dark-header email shell).

Campaign send route — apps/admin/src/app/api/admin/crm/campaigns/[id]/send/route.ts

Uses applyMergeTags from @setpoint/common/merge-tags and a global RESEND_API_KEY env var (getResendKey()), not the per-facility crm_email_settings.resend_api_key. ensureAudience() lazily creates a Resend audience named "<Facility> — Setpoint CRM" and upserts the id onto crm_email_settings.resend_audience_id; syncContactToAudience() idempotently POSTs each contact. Recipients come from loadEnrichedCustomers + matchesFilters imported from ../../../segments/preview/route. Every failure path in the audience helpers returns null/swallows — audience sync is best-effort.

Push notifications — FLAG: none exist

Grepped apps/calendar_pwa, apps/web, apps/admin for service worker, web-push, FCM/Firebase, APNs, OneSignal, Expo notifications, Notification.requestPermission, pushManager, push_token. Zero hits. The only artefacts are two static manifest.json files (apps/terminal/public/manifest.json, apps/calendar_pwa/web/manifest.json); calendar_pwa is a Flutter app (pubspec.yaml, lib/), not a JS PWA. There is no registered service worker anywhere. Push would be greenfield.

3. Roles and permissions

user_facility_roles — 00000000000000_initial_schema.sql:5530

id, user_id, facility_id, role, created_at. Original CHECK: owner, admin, manager, coach, member, guest. staff was added by supabase/migrations/20260611100000_add_staff_role.sql ("club employees who can clock in/out and submit expenses but don't have coaching duties or management access"), giving the current seven: owner, admin, manager, coach, staff, member, guest. That migration also widened the bookings and customers read policies to ['coach','staff'].

There is a UNIQUE INDEX idx_user_facility_roles_lookup on (user_id, facility_id) — one role per user per facility — which is what makes .single() safe in the auth helper. The table's own 3-column UNIQUE would not.

apps/admin/src/lib/auth.ts (273 lines)

• SessionUser = Pick<User,"id"> & Partial<Omit<User,"id">>. Trap documented in a 🔴 block at line 30: the signed-middleware fast path carries only id; the Bearer and cookie paths carry the full User. So user.email is a real string on some requests and undefined on others for the same route.
• getSessionUser(req) — order: Bearer header → signed internal identity header (readInternalIdentity(headers, method, pathname), HMAC over method + pathname so a header minted for one route cannot be replayed on another) → cookie session.
• requireFacilityAccess(req, facilityId, user?): Promise<{ authorized: true; user: SessionUser; role: string } | NextResponse> — single indexed user_facility_roles lookup. PGRST116 (no rows) → 403; any other error → 503, deliberately not collapsed into 403.
• requireSessionUser(req) — session only, no facility scope.
• withFacilityAccess<P, TCtx>(resolveFacilityId, handler) — HOF: 401 → resolve facility (resolver gets the user, can return a ResolverResult<T>, null → 404, or a NextResponse) → facility access → handler. 401/403 are flattened to 404 (anti-probing, "R8"); 5xx passes through unchanged.

FLAG: there is no requireRole helper. Nothing in lib/auth.ts checks role. Every route does it inline against access.role.

How routes distinguish roles

Ad-hoc per-file constants, re-declared in ~20 files:

The same name WRITE_ROLES means two different things in different files. The only reusable gate is in one feature: apps/admin/src/app/api/admin/message-templates/_lib/templates.ts:422, which takes options.writeRoles + options.action and returns a sentence-readable 403 via listRoles() ("owner, admin or manager"). Worth generalising.

Routes that call the service-role client bypass RLS, so the inline check is load-bearing — academy-payments/settings/route.ts:77 says so explicitly.

FLAG — apps/admin/src/app/api/admin/team/route.ts and team/[userId]/route.ts have no role gate at all. GET, POST (creates an auth user and grants a role, validRoles = ["owner","admin","manager","coach","staff"] at line 92), PATCH and DELETE only call requireFacilityAccess. Any member with any role at the facility can add or remove teammates, including granting owner.

Discount approval code — the only per-action approval pattern

supabase/migrations/20260901042741_pos_discount_approvals.sql + supabase/functions/send-discount-approval-code/index.ts. Table pos_discount_approvals: facility_id, requested_by, approver_user_id, kind (CHECK 'manual_discount'), code_hash, expires_at, attempts, consumed_at. RLS: SELECT only, for owner/admin/manager (audit/history). No INSERT/UPDATE/DELETE policies for authenticated users — inserts go through the edge function (service role), mutation only through the RPC. verify_discount_approval(p_request_id, p_code) — SECURITY DEFINER, SELECT … FOR UPDATE so concurrent guesses cannot exceed the attempt budget or double-consume. Edge function: APPROVER_ROLES = ["owner","admin","manager"], CODE_TTL_MINUTES = 10, MIN_SECONDS_BETWEEN_REQUESTS = 20 (abuse floor, explicitly "not a full rate limiter"), CSPRNG 6-digit code with rejection sampling, sha256-hex stored, plaintext never persisted, max 5 attempts, single use. Per DEC (decisions.ts:5226) owner/admin/manager bypass the gate entirely, and both the request and the grant write pos_activity_logs rows.

Shape to copy for any new per-action approval: hashed short-lived single-use code + row-locked SECURITY DEFINER verifier + service-role-only insert + read-only RLS for elevated roles.

4. Audit

pos_activity_logs — 00000000000000_initial_schema.sql:4165

id, facility_id (NOT NULL), actor_id (nullable), action (text, NOT NULL), entity_type (text, nullable), entity_id (uuid, nullable), description (text, NOT NULL), metadata jsonb default {}, created_at.

Writer: apps/terminal/src/app/actions/log-activity.ts — a server action taking LogActivityInput { facilityId, actorId, action, entityType?, entityId?, description, metadata? }, inserting with the service-role key and swallowing every error: // fire-and-forget: silently swallow errors so logging never breaks the main flow. Readers: apps/terminal/src/queries/use-activity-logs.ts, apps/pos/src/app/(dashboard)/reports/activity-log/page.tsx.

This is explicitly rejected as a model for anything with stakes — decisions.ts:180: "Reuse pos_activity_logs — rejected: it is a POS-owned, app-written, deliberately best-effort audit stream whose writer swallows errors on purpose. A session balance is an entitlement customers pay for; its ledger must be transactional."

Other audit surfaces

• crm_customer_merges (merge migration) — the only record of who merged whom, because the merge is irreversible.
• package_session_transactions (20260824200640_package_session_ledger.sql) — append-only ledger; actor_id FK to auth.users ON DELETE SET NULL; adjust_package_sessions(p_player_package_id, p_adjustments, p_reason, p_actor_id) writes reason='admin_adjust', source='live'. RLS per AC-137: staff read via user_facility_roles, INSERT restricted to service_role, every row carries facility_id and every read filters by it.
• pos_discount_approvals — read-only history of approval requests.
• There is no generic admin-wide audit/activity table. CREATE TABLE … audit / … activity_log across all 189 migrations returns only pos_activity_logs.

created_by / updated_by on academy tables

All FK auth.users(id) ON DELETE SET NULL:

• payment_requests.created_by (academy_payments_core.sql:55)
• payment_reminders.created_by (:129)
• message_templates.updated_by (:187)
• academy_payment_settings.updated_by (:252)
• package_session_transactions.actor_id (package_session_ledger.sql:33)
• CRM: crm_tags.created_by, crm_segments.created_by, crm_campaigns.created_by, crm_customer_tags.assigned_by, crm_notes.author_id (NOT NULL)

payments.reverses_payment_id — 20260902172043_academy_payment_pos_reversal.sql:45

ALTER TABLE "public"."payments"
  ADD COLUMN IF NOT EXISTS "reverses_payment_id" "uuid"
  REFERENCES "public"."payments"("id") ON DELETE RESTRICT;
CREATE UNIQUE INDEX IF NOT EXISTS "payments_one_reversal_per_original"
  ON "public"."payments" ("reverses_payment_id") WHERE "reverses_payment_id" IS NOT NULL;

A reversal is an appended row with the negated amount and status='succeeded', so SUM(amount) nets to zero with no special-casing. Branching keys off reverses_payment_id IS NOT NULL, never off the sign of the amount — "the column states the intent, the sign is a consequence." The partial unique index makes a double-revert fail loudly at the database. The same migration adds pos_refunds.source_id (+ partial unique index) for idempotency, value shape 'academy_payment_reversal:<payments.id of the reversal row>'.

payments itself (line 4089): facility_id, user_id, type, reference_id, amount, currency, status, stripe_payment_id, payment_method, bonus_amount, notes. payment_method CHECK: stripe, cash, credits, pending, other, package, external. type CHECK in the baseline: booking, membership, lesson, merchandise, program, credit_topup — widened with package per DEC-321.

What an admin-wide audit_log would need to be consistent

1. facility_id NOT NULL on every row, and every read filtered by it (the house rule).
2. actor_id nullable, FK auth.users(id) ON DELETE SET NULL — so deleting a customer anonymises rather than destroys the log (this is exactly the pattern the delete migration relies on).
3. RLS: staff SELECT via public.user_facility_role(facility_id) IS NOT NULL (or an elevated-role list for sensitive actions), INSERT service_role only, no UPDATE/DELETE policy.
4. action + entity_type + entity_id + human description + metadata jsonb — the pos_activity_logs column shape is the precedent worth reusing even though its writer is not.
5. Transactional writes, not fire-and-forget. DEC at decisions.ts:180 is the explicit rule: anything with stakes aborts the operation if the log row fails.
6. Append-only, with reversal-by-appending (reverses_*_id) rather than mutation, matching both the payments reversal and the session ledger.

5. Export

CSV helper — packages/common/src/csv.ts

export function csvCell(value: string): string {
  const safe = /^[=+\-@]/.test(value) ? `'${value}` : value;
  return `"${safe.replace(/"/g, '""')}"`;
}

Quote-wraps, doubles embedded quotes, and neutralises formula injection by prefixing =/+/-/@ with a single quote (OWASP mitigation). It is the only export helper in the monorepo.

Where it is used — apps/pos only, all client-side

• apps/pos/src/app/(dashboard)/items/page.tsx:61,100-102 (exportItemsCsv, line 90)
• apps/pos/src/app/(dashboard)/reports/discounts/page.tsx:137,142
• apps/pos/src/app/(dashboard)/reports/sales-taxes/page.tsx:313
• apps/pos/src/app/(dashboard)/reports/sales-report/page.tsx:243

Pattern (items/page.tsx:90–119): build a header array, map rows through csvCell, join(","), join("\n"), then new Blob([csv], {type:"text/csv;charset=utf-8;"}) → URL.createObjectURL → synthetic <a download="setpoint-<thing>-YYYY-MM-DD.csv"> → click() → revokeObjectURL. No server route, no streaming.

FLAG: apps/admin has zero CSV export. Grep for text/csv, .csv, downloadCsv, exportCsv across apps/admin/src returns nothing. Any admin export is new work — though csvCell is importable today as @setpoint/common/csv.

Coach hours — apps/admin/src/app/api/admin/academy-reports/route.ts

GET /api/admin/academy-reports?facility_slug=…&date_from=…&date_to=…&coach_ids=id1,id2. Filter parsed at line 12–13; one id → .eq("coach_id", …), several → .in(…) (lines 68–72). Hours are computed from lesson duration, not from any timesheet: line 62 selects duration_minutes and commission_amount; the coach breakdown at lines 396–417 accumulates entry.hours += (lesson.duration_minutes ?? 0) / 60 and entry.commission_amount += lesson.commission_amount ?? 0, plus package_sessions vs walkin_sessions counted from lesson_players.payment_method === "package". Coach names resolve via coaches.user_id → customers (line 421–437). Output rounds to one decimal (Math.round(c.hours*10)/10). Response also carries cashflow, sessions, costs and a dailyTrend. The per-coach loop over allLessonPlayers is O(lessons × players) — worth noting if the date range grows.

6. Docs conventions (apps/docs, apps/apidocs)

apps/docs/src/data/versions.ts

export interface VersionFeature { name: string; done: boolean }
export interface Version {
  id: string; name: string; date: string;
  status: "shipped" | "in-progress" | "planned";
  features: VersionFeature[];
  e2eTests?: number;
  description: string;
}
export interface NextVersion { id: string; name: string; status: "planned" | "in-progress"; description: string; items: string[] }
export interface Product { id: string; name: string; description: string; accentColor: string; versions: Version[]; nextVersions: NextVersion[]; emptyState?: string }
export const products: Product[] = [...]           // line 2137
export const sharedProduct = {...}                 // line 2318
export const versions: Version[] = products.flatMap(p => p.versions)  // line 2326

Latest entry: v0.51.0, "Academy Payments — payments view, reminders, templates, billing cycles, deduction ledger", date "25 Aug", status "in-progress", e2eTests: 0 (line 36). Descending from there: v0.50.0, v0.49.0, v0.48.0, v0.47.0, v0.46.0, v0.45.0, v0.44.1, v0.44.0, v0.43.0… Convention: date is a short human string ("25 Aug"), description is a long prose paragraph ending with a decision reference ("See DEC-319 through DEC-327."), and each features[].name is a file-path-or-route-prefixed sentence describing the actual artefact, e.g. "supabase/migrations/<ts>_package_session_ledger.sql — package_session_transactions (delta, balance_after, …) with staff-read/service-write RLS; …".

apps/docs/src/data/feature-matrix.ts

export interface FeatureComparison { feature: string; setpoint: string | null; playbypoint: boolean; notes: string }
export const featureMatrix: FeatureComparison[] = [...]

setpoint is the version string it landed in ("v0.19") or null if not built; playbypoint is the competitor flag; notes is one short differentiator line.

apps/apidocs/src/data/api-reference.ts

export interface ApiParam { name; type; required: boolean; description; location: "path"|"query"|"body"|"header" }
export interface CodeExample { curl: string; javascript: string; python: string }
export interface StatusCode { code: number; description: string }
export interface ApiEndpointDoc {
  id: string; method: "GET"|"POST"|"PATCH"|"DELETE"; path: string;
  title: string; description: string;
  auth: "api-key"|"jwt"|"stripe-sig"|"jwt+admin";
  category: string;
  parameters: ApiParam[]; requestBody?: string; requestExample?: CodeExample;
  responseExample: string; statusCodes: StatusCode[];
}
export type ApiCategory = "Facilities"|"Bookings"|"Pricing"|"Programs"|"Memberships"|"Credits"|"Coaches"|"Lessons"|"Matches"|"Match Results"|"Levels"|"Customer Portal"|"Webhooks"|"Admin";
export const apiCategories: ApiCategory[] = [...]
const BASE = "https://admin-setpoint.vercel.app";
export const apiEndpointDocs: ApiEndpointDoc[] = [...]

apps/docs/src/data/decisions.ts

export interface Decision {
  id: string;            // "DEC-329"
  date: string;          // "2026-09-03T02:00"  (ISO, minute precision)
  feature: string;       // "Academy Payments — the Payments view an operator actually works from (v0.51.0)"
  area: "architecture"|"ux"|"business-rule"|"data-model"|"api"|"infrastructure"|"trade-off"|"technology"|"naming"|"other";
  question: string; decision: string; alternatives: string[]; rationale: string;
  madeBy: string;        // "boss" | "team" | "claude"
}
export const decisionsMeta = { title, description, lastUpdated: "2026-09-03T02:00" };
export const decisions: Decision[] = [ /* newest first */ ];

Latest: DEC-329, 2026-09-03. Note the sequence has gaps in the file (DEC-329, then DEC-327, DEC-326 …). Each alternatives entry is a full sentence ending in — rejected: <reason> or — deferred, not rejected: <reason>; that phrasing is the house style.

apps/docs/src/data/monthly-planning.ts

export interface PlanningGoal { title: string; status: "done"|"in-progress"|"planned"; description?: string; productId: "setpoint-pms"|"openrally-protocol"|"shared" }
export interface MonthlyPlan { month: string; year: number; theme: string; goals: PlanningGoal[]; carryOver?: string[] }
export const currentPlan: MonthlyPlan = { month: "April", year: 2026, theme: "Integration Depth & GA Runway", goals: [...] };

FLAG: stale. currentPlan is still April 2026 while decisions.ts is at September 2026.

ac-*.ts files

Header comment first, and it is load-bearing: ac-academy-payments.ts:2 says "This file is ephemeral — deleted after the feature PR merges. Lasting context lives in decisions.ts, versions.ts, and the code." Then: origin quote from the requester, a numbered "WHAT THE CODEBASE AUDIT FOUND" block, a SCOPE POSTURE line, then exports:

export const academyPaymentsMeta = { title, version, status, requestedBy, date, description, branch, nestorConstraints: string[] };
export const outOfScope = [ /* each a sentence saying WHY it is out */ ];
export const acceptanceCriteria = [
  { id: "AC-1", area: "Ledger", criteria: "New append-only table package_session_transactions records one row per balance change: …", priority: "MUST" },
];

priority values seen: "MUST". Criteria are written as assertions about the finished system, naming exact tables, columns and CHECK values. A different shape exists for roadmap-style ACs — ac-crm-plan.ts exports crmMeta, crmVersions (each with name, status, tagline, description, scope: string[], database: string[], dependencies: string[], notes), featureCategories (cards with icon emoji, title, description, differentiator: boolean, version) and totalFeatureCount.

How a plan page renders a data file

apps/docs/src/app/plan/<slug>/page.tsx is a plain server component that imports named exports from @/data/<file> and renders them. Example apps/docs/src/app/plan/crm/page.tsx (275 lines):

import { ArrowLeft, CheckCircle2, Circle, Clock } from "lucide-react";
import { crmMeta, crmVersions, featureCategories, totalFeatureCount } from "@/data/ac-crm-plan";
import { Footer } from "@/components/footer";
function StatusBadge({ status }: { status: "planned"|"in-progress"|"shipped" }) { … }
export default function CrmPlanPage() { … }

Structure: sticky <nav> with a back-link to / ("Hub") and the doc title, a bordered header <section> with an amber eyebrow (Product Brief), {meta.title}, {meta.description} and pill badges, then sections per exported array. Design tokens are semantic Tailwind classes (bg-bg-page, border-border-subtle, text-text-muted, text-accent-amber). Existing plan slugs: crm, crm-campaigns, crm-segments, coach-availability-overhaul, coach-booking-openrally, pos-thailand-tax, tps-to-setpoint-ptp-import, matches-programs-openrally{,-full,-guide,-handoff}.

Registration for discovery is apps/docs/src/data/documents.ts — it imports each data file's *Meta export and appends a DocumentEntry:

export interface DocumentEntry { title: string; description: string; href: string; productId: "setpoint-pms"|"setpoint-pos"|"openrally-protocol"|"setpoint-crm"|"shared"; status: string; statusColor: string; version?: string }

So a proposal page = one apps/docs/src/data/<name>.ts exporting a *Meta + typed arrays, one apps/docs/src/app/plan/<slug>/page.tsx rendering them, one DocumentEntry in documents.ts.

7. Settings pattern

Two coexisting patterns.

A. Dedicated per-facility settings table (the newer, preferred one). academy_payment_settings — 20260824200644_academy_payments_core.sql:242:

facility_id uuid PRIMARY KEY REFERENCES facilities(id) ON DELETE CASCADE,
enabled boolean DEFAULT false NOT NULL,          -- default FALSE so the migration starts nothing for live clubs
reminder_offsets integer[] DEFAULT '{-3,0,3,7}' NOT NULL,
channels text[] DEFAULT '{email}' NOT NULL,      -- CHECK channels <@ ARRAY['email','whatsapp']
send_hour_local smallint DEFAULT 9 NOT NULL,     -- CHECK 0..23
default_due_days integer DEFAULT 7 NOT NULL,     -- CHECK >= 0
pay_at_club_instructions text,
updated_by uuid REFERENCES auth.users(id) ON DELETE SET NULL,
updated_at timestamptz DEFAULT now() NOT NULL

RLS: staff read (user_facility_role(facility_id) IS NOT NULL), owner/admin insert, owner/admin update. Grants to anon, authenticated, service_role.

Route apps/admin/src/app/api/admin/academy-payments/settings/route.ts — GET/PUT keyed by ?facility_slug=. The row is created on first save, not at facility creation, so GET answers with a DEFAULTS constant (lines 12–19) that mirrors the migration exactly, and returns is_default: !data plus payment_provider and role alongside. PUT re-checks access.role !== "owner" && !== "admin" because the service-role client bypasses RLS, then re-validates each field ("Mirrors the CHECK constraints … so a bad payload gets a 400 the operator can read rather than an opaque 23514 from Postgres"): offsets must be integers with |o| <= 365, channels a non-empty subset, send_hour_local 0–23.

UI: apps/admin/src/components/admin/academy-payments-settings-tab.tsx, with its own WRITE_ROLES = ["owner","admin"] at line 38 for read-only rendering.

B. facilities.settings jsonb (00000000000000_initial_schema.sql:3660, nullable). The only writer is apps/admin/src/app/api/admin/facilities/route.ts:90-100, which shallow-merges into the existing object rather than replacing it. No schema, no validation, no CHECK.

Also on facilities as first-class columns: checkout_tips_enabled, checkout_receipt_enabled, checkout_note_enabled, payment_provider (added 20260811145715 / nullable-backfilled 20260824123907), is_listed, qr_payment_image_url.

Recommendation for new academy settings (grace window, student-cancel rule, pause policy): follow pattern A — add columns to academy_payment_settings (or a sibling academy_*_settings table keyed on facility_id) with defaults that preserve today's behaviour, a CHECK per field, staff-read/owner-write RLS, a DEFAULTS constant in the route that mirrors the migration, and duplicate validation in the route for readable 400s. Do not put them in facilities.settings.

8. Student / customer identity

There is no users table. The chain is:

auth.users (Supabase Auth)
   └─ 1:1 ─ public.customers  (id IS the auth.users id; hard FK)     ← "the profile"
              ├─ user_facility_roles (user_id, facility_id, role)    ← staff/member access
              ├─ coaches (user_id)                                    ← coach identity
              ├─ crm_customer_directory (facility_id, profile_id)     ← "in this facility's CRM"
              └─ facility_customers (facility_id, profile_id)         ← probably NOT in production

• customers.id is the same UUID as auth.users.id (stated in the merge migration header: "both share the same UUID, customers.id has a hard FK to auth.users"). Everything CRM-side keys on this as profile_id; bookings/payments key on it as user_id.
• Contact fields live on customers: email, phone, phone_country_code.
• Stripe customer id: customers.stripe_customer_id (baseline line 3635). customers.default_payment_method_id holds the saved method.
• Code refers to customers rows as "profiles" throughout (profilesMap, profile_id, .from("customers").select(...) in the team route and academy reports). The naming is inconsistent with the table name; there is no separate profiles table.
• displayName(profile) from apps/admin/src/lib/display-name.ts is the shared formatter.
• Placeholder emails are a real concept: isPlaceholderEmail from apps/admin/src/lib/find-or-create-user.ts is applied in the CRM customer list (crm/customers/route.ts:57) to render null instead of a synthetic address. resolveOrCreatePlayerAccount from the same module is how admin-created walk-ins get an auth account.

FLAG — facility_customers is very likely not deployed to production

Two migration headers assert this after direct production checks:

• 20260705120233_merge_customer_records.sql (lines ~35–44): "Not handled: facility_customers. Confirmed directly against production (both an information_schema.tables lookup and a live pg_constraint scan …) that this table does not exist there today, despite being present in the local schema dump — production is running an older crm_search_customers that derives CRM membership from bookings/pos_orders/facility_tickets/user_memberships directly." If it is ever deployed, the merge function needs a new dedup/re-point step (unique on (facility_id, profile_id)).
• 20260706042103_add_crm_customer_directory_for_new_player.sql: production's live crm_search_customers only includes a customer via facility_customer_ids, a UNION of bookings / pos_orders / facility_tickets / user_memberships. A "New Player" created with no activity was invisible, so crm_customer_directory was added as a small targeted marker table, explicitly *"NOT a resurrection of the larger, deliberately-deferred facility_customers migration."

Consequence for any proposal: do not build on facility_customers, its source column, its metadata jsonb or its email_unsubscribed flag. Facility membership in production is derived activity plus crm_customer_directory. That also removes the only existing place a lead/source value could be stored.

Not found / gaps