Supabase Multi-Schema Architecture: How OpenMyPro Isolates Tenant Data

Multi-tenancy is the hardest database problem in SaaS. Get it wrong and you leak patient data between providers. Get it right and you have an architecture that scales to millions of users without rearchitecting. Here is how OpenMyPro uses Supabase's PostgreSQL foundation to build multi-schema tenant isolation that is both HIPAA-compliant and developer-friendly.

Why Multi-Schema Over Shared Tables

There are three common approaches to multi-tenancy. Shared database with a tenant_id column is simplest but riskiest — one missing WHERE clause leaks all data. Separate databases per tenant is safest but operationally expensive. Schema-per-concern with RLS policies is the sweet spot we chose.

OpenMyPro does not use a schema per tenant. Instead, we use schemas to separate concerns: a public schema for shared reference data like categories and locations, a providers schema for provider profiles and availability, a patients schema for patient records and preferences, and a bookings schema for appointment data. RLS policies on every table enforce row-level access based on the authenticated user.

Row-Level Security Policies

RLS is the core of our data isolation strategy. Every table has policies that check auth.uid() against ownership columns. A patient querying the bookings table only sees rows where patient_id matches their auth ID. A provider only sees bookings where provider_id matches. Even if application code accidentally runs a SELECT * without a WHERE clause, the database returns only authorized rows.

We write RLS policies as SQL functions for reusability. A can_access_booking function checks if the current user is either the patient or provider on that booking. This function is referenced by SELECT, UPDATE, and DELETE policies on the bookings table. When business logic changes — say we add admin access — we update one function instead of six separate policies.

Supabase Auth Integration

Supabase Auth generates JWTs that PostgreSQL trusts natively. When a user signs in, they get a token containing their user ID, email, and custom claims including their role (patient, provider, admin). RLS policies read these claims directly from the token using auth.uid() and auth.jwt(). There is no separate authorization service to maintain — the database is the authorization layer.

Custom claims let us encode role information without an extra database lookup per request. When a provider signs in, their JWT includes role: provider and their provider profile ID. RLS policies check these claims in constant time, adding negligible latency to queries.

Schema Migration Strategy

Database migrations are the scariest part of a production system. One bad migration can corrupt data or cause downtime. We use Supabase's migration system with a strict protocol: every migration is tested against a staging branch first, destructive operations use IF EXISTS guards, data migrations run in transactions with explicit rollback points, and every migration includes a corresponding down migration.

We never alter column types in place. Instead, we add a new column, backfill data, verify integrity, swap application code, and then remove the old column in a separate migration. This zero-downtime approach has kept OpenMyPro's uptime at 99.95% since launch.

Performance at Scale

PostgreSQL indexes are our primary performance tool. Every foreign key has an index. Every column used in RLS policies has an index. We use partial indexes for common query patterns — for example, an index on bookings where status = 'confirmed' makes the provider dashboard query near-instant even as the bookings table grows into millions of rows.

Supabase's connection pooler using PgBouncer sits between our application and PostgreSQL, reusing database connections instead of opening new ones per request. This keeps connection count manageable even during traffic spikes when thousands of patients search for providers simultaneously.

Edge Functions for Complex Logic

Some operations are too complex for RLS policies alone. Provider matching, availability calculation, and payment processing run as Supabase Edge Functions — Deno-based serverless functions that execute close to the database. These functions have direct database access with the service role key, bypassing RLS when necessary for cross-tenant operations like analytics aggregation.

Edge Functions are our escape hatch for logic that does not fit the RLS model. We use them sparingly — fewer than 10 functions total — because every bypass of RLS is a potential security risk that requires careful audit.

Lessons for SaaS Builders

If you are building multi-tenant SaaS on Supabase, start with RLS from day one. Retrofitting RLS onto an existing schema is painful and error-prone. Design your tables with auth.uid() ownership columns from the start. Test RLS policies exhaustively with different user contexts. And remember that database-level security is not a replacement for application-level validation — it is a safety net that catches the bugs your application code misses.