Gym CRM SaaS: a multi-tenant Supabase schema with 35 RLS policies — code tour

A Next.js 14 + TypeScript + Supabase gym CRM, open source, multi-tenant by design. I read the schema table by table and walked the route tree. Here's the engineering worth studying if you build multi-tenant apps on Supabase.

Route structure: roles as route subtrees

src/app/
  owner/       dashboard members leads payments plans trainers attendance retention settings
  trainer/     dashboard members workouts diets
  member/      dashboard workout diet attendance payments profile renew
  superadmin/  dashboard

Role-based access is structural — each role gets its own subtree under src/app/, with per-role layout.tsx files. Session handling follows the Supabase SSR pattern: src/middleware.ts matches all non-static routes and calls updateSession() from src/lib/supabase/middleware.ts, keeping auth cookies fresh across Server Components. Client/server Supabase helpers are split in src/lib/supabase/ (client.ts, server.ts, middleware.ts, plus generated types.ts).

The schema: 18 tables, one product story

supabase/schema.sql (~27KB). The tables:

-- Tenancy / SaaS layer
subscription_plans, gyms, gym_subscriptions, profiles, gym_settings
-- Memberships
membership_plans, members, member_memberships
-- Operations
attendance, payments, trainers
-- Programming
workout_plans, workout_plan_items, diet_plans, diet_plan_items
-- Growth
leads, lead_followups, message_templates

Notice the loops the schema closes: leads → lead_followups → members (acquisition), membership_plans → member_memberships → payments → attendance (the money loop), workout_plans → workout_plan_items / diet_plans → diet_plan_items (trainer-authored programming consumed by members). And message_templates is the store behind the WhatsApp retention system.

35 RLS policies: isolation as a database guarantee

This is the part to copy. Tenant isolation isn't trusted to application code — 35 Row Level Security policies filter every tenant-scoped table by the authenticated user's gym. A UI bug can't leak Gym A's members to Gym B, because Postgres won't return the rows. If you're building multi-tenant on Supabase, this repo is a solid reference for what "RLS done thoroughly" looks like.

Zero hardcoded business numbers

The README's claim checks out structurally: pricing, plan durations, and reminder rules live in gym_settings, editable per gym. That's the line between software and a configurable product — and it's what makes one deployment serve gyms with different pricing.

The WhatsApp retention angle

src/lib/utils/whatsapp.ts builds tap-to-send messages from the message_templates table — localised per gym, composed with member data. The product thesis: members churn silently, and the win-back nudge is the highest-ROI feature in gym software. The owner panel even has a dedicated retention route for at-risk members.

Stack and setup

Lean package.json: next 14.1.0, @supabase/ssr + @supabase/supabase-js, react-hook-form + zod (validated forms), recharts (dashboards), date-fns, lucide-react, Tailwind. No UI kit, no state framework — Context + Server Components.

# 1. Paste supabase/schema.sql into the Supabase SQL editor and run it
# 2. cp .env.example .env.local   # add Supabase URL + keys
# 3. npm install && npm run dev

DEPLOYMENT.md covers production deployment.

Honest take

Complete, coherent v1: the role panels are real routes, the schema joins up end to end, and isolation is enforced at the database level. What's next is obvious from the schema's shape — payment-gateway wiring against payments (records exist; collection webhooks don't), automated renewal reminders driven by member_memberships expiry, and a member PWA on the existing member routes. As a reference architecture for multi-tenant Supabase apps, start with schema.sql and the RLS policies.

Repo: https://github.com/Shivay00001/gym-crm-saas

Comments

[Cenk KURTOĞLU]

The 35 policies are the right instinct, but the number is the least interesting part — the risk lives in which of the 18 tables has a permissive policy and in which ones the scoped policies dereference. In practice, the leak in a schema like this is almost never members or payments; it’s a small, “obviously safe” table (plans, gym_settings, message_templates, trainers) that ends up with using (true) because it feels public, and then a tenant-scoped policy subqueries into it — at which point the isolation is only as strong as that one table. Two checks worth running against schema.sql: (1) grep for using (true) and read every hit as if it were the only thing standing between two gyms; (2) for the money loop (member_memberships, payments, attendance) confirm there’s a WITH CHECK on inserts, not just a USING on select — a select-only policy will happily let a member write into another gym’s membership. A UI bug genuinely can’t leak rows here; a permissive lookup table can, by inference, and that’s the one a linter never flags.

[Stack & Ship]

Thorough tour, and database-level tenant isolation is the right place for it. Two things I'd check on a schema like this. First, make sure the INSERT and UPDATE policies have a WITH CHECK that pins the gym id as well as a USING clause, otherwise a user can write a row into another gym even though they can't read it. Second, since schema.sql is run by hand in the SQL editor, those tables won't have RLS enabled automatically and, on projects using the new default from 30 October, they also need explicit GRANTs for authenticated before the Data API can see them. A quick test with two users from different gyms, trying to read and insert across the boundary, will confirm all 35 policies behave as intended.