Supabase RLS Multi-Tenant Patterns for Malaysian SaaS Apps

At JRV Systems, we build multi-tenant SaaS applications for Malaysian businesses, from clinic management systems to custom billing platforms. A core challenge in any multi-tenant architecture is data isolation: ensuring Clinic A can never see Clinic B's patient records. Supabase offers a powerful tool for this called Row Level Security (RLS), which enforces data access rules directly in the database.

Over several projects, we've developed a set of reliable Supabase RLS multi-tenant patterns. These aren't theoretical concepts; they are battle-tested policies we've shipped to production. Using RLS correctly from the start prevents complex, error-prone authorization logic in your application code.

Core Patterns for Tenant and User Isolation

These are the foundational policies that most multi-tenant applications will need. They ensure that data is strictly partitioned between different tenants (e.g., companies, clinics) and that users within a tenant can only access their own data.

• Tenant Isolation via JWT Claim: This is the most critical pattern. When a user logs in, we embed their tenant_id into their JSON Web Token (JWT). Supabase makes this easy by using a custom claim in the app_metadata. The RLS policy then checks this claim for every database query. A typical policy for a patients table would look like this: (SELECT app_metadata ->> 'tenant_id' FROM auth.users WHERE id = auth.uid())::uuid = tenant_id. This ensures a user's queries are automatically filtered to their tenant's data, with no chance of cross-tenant data leaks.

• Row Ownership via auth.uid(): Within a tenant, you often need to restrict access to specific rows. For example, a doctor should only see the consultation notes they created. We handle this by adding an owner_id (or created_by) column to the table, which stores the user's UUID. The RLS policy is simple and efficient: auth.uid() = owner_id. This allows users to SELECT, UPDATE, and DELETE their own records.

• Role-Based Access via a Graph Table: Simple ownership isn't always enough. A clinic manager needs to see all records for their clinic, not just their own. We implement this with a user_roles table that links user_id, tenant_id, and a role (e.g., 'admin', 'doctor'). An RLS policy can then check for a specific role: EXISTS (SELECT 1 FROM user_roles WHERE user_id = auth.uid() AND role = 'admin'). This is more flexible than storing a role directly in the auth.users table, as it allows a user to have different roles in different tenants.

Advanced Patterns for Specific Scenarios

Once the core isolation is in place, you often encounter more nuanced requirements. These patterns address common business logic needs like soft deletes and audit trails.

• Gating Access to Soft-Deleted Records: We rarely hard-delete data. Instead, we use a deleted_at timestamp column. By default, users shouldn't see these soft-deleted records. We add deleted_at IS NULL to our SELECT policies. However, an admin might need to view or restore them. For this, we create a separate policy for the 'admin' role that omits the deleted_at IS NULL check, giving them full visibility.

• Read-Only Policies for Audit Trails: For tables like audit_logs, no user should ever be able to modify or delete records after they are created. We implement this with a strict RLS setup. The INSERT policy might be permissive (true), but the UPDATE and DELETE policies are explicitly set to false. This turns the table into an append-only log at the database level, providing a strong guarantee of data integrity.

How We Test RLS Policies Before Deployment

Writing RLS policies is only half the job; testing them is crucial. A mistake can lead to a serious data breach. We rely heavily on the Supabase CLI and its database testing features.

Our process involves creating a seed.sql file that populates the local database with realistic test data for multiple tenants and users with different roles. Then, we write pgTAP tests. Inside a test, you can impersonate a specific user and role to verify that the policies work as expected.

For example, a test can run a query as 'user_A' from 'tenant_1' and assert that they only receive records belonging to 'tenant_1'. Then, it can run the same query as 'user_B' from 'tenant_2' and check their data scope. This automated testing gives us high confidence that our Supabase RLS multi-tenant patterns are correctly implemented before we deploy to production.

By building security directly into the database layer with these RLS patterns, we create more robust and maintainable SaaS applications. The logic is centralized, enforced consistently, and less prone to bugs that can arise from scattered authorization checks in application code.