Sport Club Organizer

Multi-tenant SaaS for running a sports club: seasons, teams, athletes, trainers, gyms, recurring availability, a calendar, and a deterministic engine that generates a weekly training schedule the club can actually rely on.

Status: Phase 1 (Foundation) complete. See Roadmap.

---

Stack

Concern	Choice
Framework	Next.js 16 (App Router), React 19, TypeScript
Database	Postgres via Supabase, with Row Level Security on every table
Auth	Supabase Auth — email/password and Google. No public sign-up.
UI	Tailwind CSS 4, shadcn/ui
Validation	Zod 4 + React Hook Form

---

Getting started

1. Create a Supabase project

At supabase.com/dashboard → New project. Then in Project Settings → API Keys, copy the project URL, the publishable key (sb_publishable_…) and the secret key (sb_secret_…).

The older anon / service_role JWT keys still work and are accepted, but Supabase deprecates them at the end of 2026 — the app logs a warning if it sees one. Prefer the new keys.

2. Configure the environment

cp .env.example .env.local
openssl rand -base64 32   # paste into ENCRYPTION_KEY

Fill in the Supabase values. Every variable is validated at startup by src/env.ts — the app refuses to boot half-configured rather than failing mysteriously later.

3. Apply the database schema

pnpm install
supabase login
pnpm db:link <your-project-ref>     # the ref is in your project URL
pnpm db:push                        # applies supabase/migrations in order

4. Turn off public sign-up

In Authentication → Sign In / Providers, disable Allow new users to sign up. The product is invitation-only by design; this makes the database's position and the auth provider's position agree.

While you're there, enable Google and add https://<your-project>.supabase.co/auth/v1/callback as an authorized redirect URI in the Google Cloud console.

5. Create the first club and its owner

There is no public sign-up, and invitations must come from an existing Owner — so the very first account is created from the command line, from a machine that already holds the secret key:

pnpm bootstrap:club --email [email protected] --name "Riverside Athletics"

Without --password, this sends a Supabase invite email so you can choose your own. Add --password '<something long>' to skip the email in local development.

6. Optional: seed development data

pnpm db:seed

Creates a season, 5 teams, 6 trainers, 3 gyms, ~62 athletes and a full week of availability — including two deliberate scheduling conflicts, so the conflict UI has something real to render rather than a happy path.

7. Run it

pnpm dev

---

Scripts

Command	What it does
pnpm dev	Development server
pnpm build	Production build
pnpm typecheck	tsc --noEmit
pnpm lint	ESLint
pnpm db:link <ref>	Link the CLI to a Supabase project
pnpm db:push	Apply migrations
pnpm db:diff <name>	Generate a migration from dashboard changes
pnpm db:types	Regenerate src/types/database.ts from the live schema
pnpm db:seed	Seed development data (--reset to wipe first)
pnpm bootstrap:club	Create the first club and Owner
pnpm platform:admin	Grant, revoke or list platform administrators
pnpm i18n:check	Verify every locale has every message key
pnpm test	Unit tests — availability, conflicts, timezones, permissions
pnpm test:schema	Verify the app and database still agree
pnpm test:security	Attempt cross-tenant access from every angle
pnpm verify	All of the above, in order

---

Architecture

The guiding rule: the web UI, the API, AI, Google, and MCP are all different interfaces to one secure application domain. None of them gets a private door.

UI / Server Action / Route Handler / MCP tool / Background job
                          │
                   AuthContext          user + club + role + resolved permissions
                          │
              Authorization service     requirePermission / assertOutranks
                          │
                Application service     business rules, transactions, audit
                          │
                       Postgres         constraints + RLS as the final word

Authorization

Permission resolution is defined once and implemented twice — identically — so neither layer can be bypassed:

explicit user override  >  role permission  >  deny by default

• In TypeScript: src/server/auth/authorization.ts
• In SQL: app.has_permission() in …_0008_authorization.sql, which every RLS policy calls

The application check is the first line of defence; RLS is the second. A bug in the first cannot leak another club's data through a user-scoped client.

Tenant resolution never trusts the client. The active club lives in a cookie, but the cookie only selects among clubs the user already belongs to — membership is re-resolved from auth.uid() on every request. A forged value resolves to nothing.

Platform administrators

A platform admin is staff of the system, not a member of any club: they hold no tenant_membership yet can administer every club, for support and operations. Granted only from the command line:

pnpm platform:admin --list
pnpm platform:admin --email [email protected] --note "why they have this"
pnpm platform:admin --email [email protected] --revoke

This deliberately punches a hole in tenant isolation, so it is built to be narrow and observable:

• The grant lives in platform_admins, a table with no privileges for authenticated — it is emphatically not a boolean on profiles, which carries a self-update policy and would therefore make the flag self-assignable.
• The bypass is folded into app.is_tenant_member() and app.has_permission(), so it takes effect in exactly one place rather than as a special case across ~40 policies — and any policy added later inherits it automatically.
• Entering a club is written to that club's audit log, and the app shows a persistent banner while staff are inside a club they don't belong to.

Verified against the live database: a signed-in non-admin cannot read or write platform_admins (403 both ways), profiles exposes no privilege column, and admin_list_tenants() returns nothing to anyone who isn't staff.

Bootstrapping: who invites the first person?

There is no public sign-up, so account creation always traces back to an authority. That creates a chicken-and-egg problem at the very start — a club needs an Owner with an account, and an account needs an invitation, which needs a club — and it is broken in exactly two places:

1. pnpm bootstrap:club, run from a machine holding the secret key.
2. A platform admin creating a club. If the owner's address has no account, one is created and an invitation link is issued in the same step.

Neither is a loophole: both require an authority that already exists. Everything after that flows through ordinary club invitations.

Until email lands in Phase 7, that invitation link is handed back in the UI for the admin to pass on. It is generated with generateLink rather than inviteUserByEmail deliberately — the latter depends on SMTP being configured, and would otherwise half-succeed by creating an account whose invitation email silently failed to send.

Where the secret key is allowed

createAdminClient() uses SUPABASE_SECRET_KEY and bypasses RLS, so its uses are deliberately few, and each performs its own authorization first:

• reading and writing secret-bearing tables (ai_provider_configurations, oauth_connections, mcp_api_keys, calendar_sync_links, email_outbox), which have no grants at all for authenticated
• writing audit_logs, which clients cannot insert into — an audit trail a client can write is not an audit trail
• issuing and previewing invitations, which must touch rows the recipient cannot yet read
• background jobs and MCP, which have no user JWT to present

Secrets

Secret	Treatment
Tenant AI provider keys	AES-256-GCM encrypted at rest, decrypted server-side only
Google OAuth refresh tokens	AES-256-GCM encrypted at rest, never sent to the browser
MCP API keys	SHA-256 hashed; the raw value is shown once at creation
Invitation tokens	SHA-256 hashed; matched by hash, never stored raw

src/server/services/audit-service.ts additionally scrubs anything whose key looks like a credential before writing, so a diff built from a row can't smuggle one into the log.

Scheduling (Phase 5)

The optimizer will be a deterministic constraint engine, not an LLM. It takes structured input and returns structured output, testable with in-memory data and no database. AI can explain a schedule, interpret a preference written in prose, or suggest an adjustment — but it is never the authority on whether a schedule is valid.

The database already enforces the hard invariants directly, via exclusion constraints on schedule_entries: within a schedule version, no gym, trainer or team can be double-booked. Overlapping availability windows are rejected the same way.

---

Database

37 tables across 16 versioned migrations in supabase/migrations/.

Migration	Contents
0001_foundation	Extensions, private app schema, enums, timerange type
0002_tenancy_rbac	profiles, tenants, roles, permissions, memberships, overrides, invitations
0003_domain_entities	seasons, gyms, trainers, teams, athletes and their links
0004_availability	Recurring availability + exceptions, training requirements
0005_scheduling	Schedule versions, entries, calendar events, jobs
0006_operations	Audit, notifications, email outbox, AI config, OAuth, MCP keys
0007_permissions_seed	Permission catalogue and the role → permission matrix
0008_authorization	app.has_permission and friends — the RLS primitives
0009_rls_policies	RLS on every table
0010_invariants_rpc	Last-owner guard, tenant provisioning, invitation acceptance, transactional publish
0011_platform_admin	System staff: the grant table, the bypass, and the admin console RPCs
0012_error_messages_and_owner_guard	Owner guard no longer blocks club deletion; user-facing messages survive the error mapper
0013_invitation_token_hardening	accept_invitation takes the raw token, not its hash
0014_invitation_column_grants	token_hash removed from what client roles may select
0015_audit_immutability_scope	Audit guard no longer blocks deleting a club with history
0016_publish_for_non_session_callers	Publishing works for callers with no JWT; the transaction and the auth check are separated

Availability model. Recurring weekly windows plus date-specific exceptions. Weekdays are ISO-8601 (1 = Monday … 7 = Sunday, matching extract(isodow)). Times are wall-clock in the club's scheduling timezone; a window running to midnight uses end_time = '24:00', and one that truly crosses midnight is stored as two rows, so every row satisfies start < end. Overlapping windows for the same owner are rejected by exclusion constraints rather than silently merged.

Error messages. SQLSTATEs alone can't distinguish "Postgres rejected a constraint" (technical, must not be shown) from "our function raised copy written for a human". Deliberate raises therefore carry HINT = 'SCO_USER_MESSAGE', which Postgres never sets itself; fromDatabaseError shows those messages verbatim and falls back to a generic one for everything else. Without it, "No account exists for that email" surfaced as "That club could not be found" — unhelpful, and untrue.

Delete behaviour. Historical scheduling data is never destroyed to tidy up a roster. Entities soft-delete (deleted_at); seasons and schedule versions archive; team and trainer assignments keep left_at / unassigned_at so past squads remain reconstructable.

---

Availability

Recurring weekly windows plus date-specific exceptions, shared by gyms, trainers and teams — the three tables are structurally identical, so they share one service rather than three copies that drift.

src/domain/availability.ts holds the resolution logic as pure functions with no database and no React anywhere near them, because the scheduling engine will depend on it and must be testable with in-memory data. pnpm test covers it: half-open intervals so back-to-back sessions don't count as a conflict, 24:00 as end-of-day rather than wrapping to zero, ISO weekdays throughout (never Date.getDay(), which is 0-indexed on Sunday), and exception precedence — including that a whole-day closure beats a same-day override regardless of the order they were entered.

Overlapping windows for one owner on one weekday are rejected by a Postgres exclusion constraint, so contradictory availability never reaches the scheduler. The app matches that failure by SQLSTATE rather than constraint name: Postgres generates those names by truncating the column list, and the three tables end up with three unpredictable names for the same rule.

Team training requirements separate hard constraints the schedule must satisfy from preferences it should try to — the distinction the optimizer is built around, and one an organizer needs to already understand when reading a conflict explanation later.

Verification

pnpm verify   # typecheck -> lint -> unit tests -> schema drift -> security

pnpm test — 102 unit tests over the pure domain: availability resolution (half-open intervals, 24:00, exception precedence), conflict grading, timezone conversion across both DST boundaries, the optimizer's scenarios, and the permission resolution order.

pnpm test:schema — the application and the database define the permission taxonomy and role hierarchy in two places by necessity. This checks they still agree, that Owner holds everything and Admin cannot delete the club, that Athlete's grants are all read-only, and that all 28 tenant-owned tables return nothing to an anonymous caller.

pnpm test:security — the suite the spec asks for by name. It builds two real clubs and then, as a member of one, tries to read, write, rename, delete and smuggle its way into the other. Every assertion is phrased as an attack that must fail:

Vector	Result
Read any of 14 tenant tables belonging to club B	nothing returned
Read 6 secret-bearing tables	refused outright
Insert into 8 of club B's tables	refused
Rename, delete, or drop club B	no rows affected
Point a team in A at B's season	refused by trigger
Grant self platform-admin	refused
Forge the active-club cookie	no data shown
Use club A's MCP key against club B	nothing returned

43 attacks blocked, 0 succeeded. A failing run names the vector that got through.

Accessibility

Semantic HTML, a skip link past the sidebar's fifteen nav items, labelled form controls, visible focus rings, aria-live on regions that update asynchronously, and status never carried by colour alone — every coloured indicator has text beside it.

One gap, stated rather than glossed: dragging a session in the week view is pointer-only. Every session is a real button that opens its detail panel with the keyboard, so reading and inspecting the schedule never needs a mouse — but moving one currently does, and a keyboard alternative is still owed.

Observability

src/lib/observability.ts emits one JSON object per line in production and readable lines in development. Fields whose names look credential-shaped are redacted at that boundary regardless of what the caller passed, because a context object assembled from a row can pick one up by accident. Schedule generation is timed, since it is the operation whose cost grows with a club's size.

MCP

POST /api/mcp — JSON-RPC 2.0, protocol 2025-11-25, stateless. Every tool is a thin shell over a service the web UI already calls, so MCP owns no business logic: change a rule in a service and it changes for MCP in the same commit. That is the spec's closing principle made mechanical rather than aspirational.

Two independent gates, both must pass. The key's owner must hold the tool's permission — that one cannot be argued with. And the key's scopes must allow the tool: an empty scope list means "the ordinary tools my owner can use"; naming any scope narrows the key to exactly those; and generating or publishing a schedule must always be named explicitly. A tool a key cannot call is never advertised.

Scopes gate tools, not permissions. Got this wrong first: masking the permission set meant a key scoped to schedule.generate was offered the tool and then failed inside it, because generating legitimately reads seasons, teams, gyms and availability along the way. A permitted tool now runs with its owner's real permissions. The guarantee is unchanged — a scoped key reaches fewer capabilities, never more — and the failure mode is gone.

A key never outlives its owner's authority. Demote the person and every key they issued weakens with them; suspend their membership and their keys stop working on the next request. Verified: an Athlete's key that asks for schedule.publish gets nothing at all.

Only the SHA-256 of a key is stored, and MCP writes are attributed as actor_type = MCP in the audit log, so a club can always tell an agent's work from a person's.

AI providers

Bring-your-own-key, per club. One interface (AiProvider), three adapters — Anthropic, Gemini, OpenAI — each using that vendor's official SDK. The application never imports a vendor SDK directly; it asks the registry for whatever the tenant configured. A fourth provider is a new adapter and nothing else.

AI is never authoritative about scheduling. It explains, summarises and drafts; whether a schedule is valid is decided by the deterministic engine, and the engine's structured findings are always rendered alongside any AI prose, never replaced by it. The prompt says so, but the architecture is what enforces it: there is no code path from a model's output to a schedule change.

Key handling. Keys are encrypted with AES-256-GCM before storage and decrypted only where a provider client is constructed. ai_provider_configurations has no grants for authenticated at all, so every read goes through the secret-key client after this service has checked the caller's permission. Nothing returns a key: the UI gets the last four characters and a "test key" button, because that is the only honest way to answer "does this work". Audit entries record that a key was rotated, never the key.

Verified live: the envelope is versioned and authenticated (a tampered one is rejected), the same key encrypts differently each time, the table is unreachable from a signed-in client (403), and no key or ciphertext appears in what the UI receives or in the audit log.

Trainers and teams

A trainer must be assigned to at least one team. This is not bureaucracy: the scheduler's candidate generation only offers a coach for teams they actually coach, so an unassigned trainer is silently invisible to it. Requiring the assignment turns "I added a trainer but the schedule ignores them" into a message at the point of entry.

Assignment works from both sides — the team form picks its coaches, the trainer form picks their teams — and both write the same trainer_teams rows. Removing an assignment sets unassigned_at rather than deleting, so who coached what through a season survives a roster change.

Trainers created before the rule are still listed and still editable; they are flagged No team so the gap is visible rather than silent. Onboarding puts teams before trainers, since the reverse order walks someone into a form they cannot submit.

Members and permissions

Roles set defaults; an individual can be granted or denied a single permission on top. Three states, not two — From role, Always allow, Never allow — because removing an override restores the role's default rather than denying, which is what override > role > deny actually means.

Rank guards who may act on whom: you can never edit, promote or remove someone at or above your own level, and you can never grant a role above your own. Checked in the UI so the menu doesn't offer what will fail, and again on the server, which is what enforces it.

Invitation tokens. The raw token is shown once, to the inviter; only its SHA-256 is stored. Two things make that hashing worth something, both added after testing showed it wasn't:

• accept_invitation takes the raw token and hashes it inside the database. It previously took the hash, which made the stored value itself a usable credential — hashing protects nothing if knowing the stored value is enough.
• token_hash is removed from what client roles may SELECT, at the column grant level. A table-level grant is not narrowed by revoking one column, so the privilege is dropped and re-granted column by column; a column added to that table later is invisible to clients until it is listed, which is the right default for a table holding a secret.

The scheduling engine

src/domain/scheduling/ is a deterministic constraint engine. No database, no clock, no randomness — plain data in, plain data out — which is what makes it testable and what makes a generated schedule reproducible when an organizer reruns it after a small change and wants to see only the difference that change caused.

The pipeline is separated the way the spec asks:

requirements + availability      buildScheduleInput   (the only DB-aware part)
            ↓
    candidate generation         every legal (day, time, gym, trainer)
            ↓
     constraint evaluation       hard rules eliminate; soft rules score
            ↓
        optimization             most-constrained-first, then a repair pass
            ↓
      conflict analysis          why a requirement could not be met
            ↓
     versioned persistence       a DRAFT, never the published schedule

Why most-constrained-first. A team with 60 possible slots can nearly always be fitted around others; a team with 2 cannot. Scheduling the fragile teams first stops them losing their only options to a team that had a choice. It is not an optimal solver and does not pretend to be — but it is explainable at every step, which for a club schedule matters more than the last few points of score. assign is deliberately isolated so a stronger solver can replace it without touching anything else.

Availability has a date it takes effect from, and the representative week must be one where it is actually in force. Anchoring purely on the season start looks right and is not: a club whose season began in August but who entered its opening hours in September gets a week in which none of that availability applies, so the engine sees an empty club. The anchor is the later of the season start and today, clamped inside the season.

The diagnosis distinguishes no hours entered from hours entered but not in force this week — the same empty result, but entirely different things to go and fix.

Explanations are first-class. Every placed session records what it satisfied, what it traded away, its score, and how many alternatives existed — "only one slot was possible" tells an organizer far more than "score 62". Every unmet requirement records why: no eligible gym, no assigned trainer, session longer than the allowed window, or no overlapping availability at all.

Scores are normalised to 0–100 so they mean the same thing regardless of how the weights are tuned, and the total is multiplied by the completion ratio — a schedule that leaves teams unplaced is not a good schedule, whatever the placed sessions scored.

Weights live in one object (DEFAULT_WEIGHTS), not scattered through the scoring code, so tuning is configuration rather than a code change.

Calendar and conflicts

Day, week, month and agenda views, all filtered server-side through the URL. The week view is a time grid — "what's free on Tuesday evening" is a spatial question — and drops back to the agenda on small screens, because a shrunken time grid is unreadable rather than merely smaller.

src/domain/scheduling/conflicts.ts decides whether a placement is valid, and the calendar, the manual editor and the Phase 5 optimizer all call it, so they cannot disagree. It emits stable codes, never prose, so the same conflict reads correctly in both languages. Severity is graded, and the grading matters:

INVALID	Structurally impossible — ends before it starts
CONFLICT	A hard rule broken — double-booked hall, outside opening hours
WARNING	A preference unmet, or no trainer assigned yet
VALID	Nothing wrong

Manual moves are refused only for CONFLICT and INVALID. An organizer knows things the optimizer does not, so a move is never blocked merely because the optimizer would have chosen differently — and every manual move is audited with its before and after, so a schedule that drifts can still be explained.

Cancelling and deleting are different things, and the calendar offers both. Cancelling records that something real is not happening — people were told about it, so it stays on the calendar struck through. Deleting is for an event that should never have existed: a typo, a duplicate, the wrong date. Only manual events can be removed either way; training belongs to a schedule version and is changed by discarding or republishing that version, so the schedule's history stays coherent. A deletion is audited with what the event was, since the audit entry becomes the only remaining record of it.

Timezones. Events are timestamptz; availability is wall-clock in the club's timezone. Every conversion goes through TZDate, because an offset is not a constant — it is a function of the instant. pnpm test covers the DST boundaries directly: an 18:00 session stays at 18:00 for the people attending it on both sides of the October change, even though the UTC instant shifts. Every formatter call is given the club's timezone explicitly; a formatter without one silently falls back to whatever the server is set to, which was a real bug caught in testing.

Collections

Every list page works the same way, and all of it happens in Postgres:

• Search, filter, sort and paging live in the URL. The server component reads searchParams, so a filtered view is linkable and survives a refresh — and no page ever pulls a table into the browser to filter it there.
• Empty states distinguish "nothing yet" from "nothing matched". The first needs a way to create something; the second needs a way to clear the filter.
• Permission-denied is its own state. Pages check before reading, so a user without gyms.read sees an explanation rather than "Something went wrong". The service still asserts underneath — that is the control, this is the face.
• Archive, not delete. Past sessions reference teams, gyms and trainers, so everything archives. Removing a trainer from a team sets unassigned_at, and an athlete leaving a squad sets left_at, keeping the record of who trained what and when.

Related counts use a grouped query rather than a PostgREST embed — embeds need relationship metadata the hand-written schema types don't carry yet. That is one extra indexed query per page, not an N+1, and it becomes an embed the day pnpm db:types can run.

Internationalisation

English and Italian, with no locale URL prefix. The whole app sits behind authentication and is marked noindex, so the SEO case for /en/… and /it/… does not apply — and language is a property of the person, not the URL, so a link shared between colleagues who read different languages still works for both.

Locale resolves per request: the sco_locale cookie, then Accept-Language, then English. Choosing a language writes both the cookie (what every request reads, so no database round-trip per page) and the profile (what makes the choice follow someone to a new device — signing in seeds the cookie from it).

pnpm i18n:check   # every en key exists in every other locale, no extras

English is the reference catalogue and is what types the keys, so t("nav.tems") is a build error. TypeScript can't catch a key missing from Italian though — that is what i18n:check is for, and it runs 163 keys against each locale.

One screen is deliberately untranslated: global-error.tsx renders when the root layout itself failed, which means the provider never mounted.

Adding a language: drop messages/<code>.json in, add the code to LOCALES in src/i18n/config.ts, run pnpm i18n:check.

Theming

Light, dark and system, via next-themes with a class strategy and tokens defined for both in globals.css. The picker sits in the account menu next to the language picker. suppressHydrationWarning on <html> is required and intentional: the theme class is applied by a blocking inline script before first paint, so the server-rendered markup cannot match.

Fonts are the platform's own UI stack — nothing is downloaded, so there is no font request and no swap flash.

Roadmap

Phase	Scope	Status
1	Foundation: schema, RLS, auth, tenancy, RBAC, app shell	Done
2	Seasons, teams, athletes, trainers, gyms	Done
3	Availability editors, exceptions, team preferences	Done
4	Calendar: views, filters, drag/drop, conflict detection	Done
5	Scheduling engine: constraints, candidates, optimizer, explanations	Done
6	Review and publishing workflow	Done (with Phase 5)
7	Invitations, members, permission overrides, audit UI, settings, onboarding	Done
8	AI provider configuration done; Google Calendar sync needs OAuth credentials	Partly done
9	MCP server: credentials, scopes, tools	Done
10	Hardening: tests, security tests, a11y, observability	Done