Multi-Tenant SaaS with Supabase RLS: Lessons from a 71-Page ERP

The short version: Row-Level Security is the difference between a SaaS app that leaks data between customers and one that's enterprise-ready. I learned this the hard way building a cloud ERP with 71 pages, 155+ API routes, and 317 database migrations. Here's how to set it up correctly from day one — the helper functions, the policies, the gotchas, and the testing pattern that catches isolation bugs before customers do.

What we're building

A multi-tenant SaaS pattern where:

• Each organization's data is completely isolated
• Users can only see their own org's data
• No application-level filtering needed — the database enforces it
• Works with Supabase Auth out of the box

Step 1: Database schema

Every table that holds tenant-specific data needs an org_id column:

-- Organizations table
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Users belong to organizations
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  org_id UUID NOT NULL REFERENCES organizations(id),
  full_name TEXT,
  role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Example tenant-scoped table
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  name TEXT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Index on org_id for every tenant-scoped table
CREATE INDEX idx_products_org ON products(org_id);

Rule: Every tenant-scoped table gets an org_id column AND an index on it. No exceptions. Skip the column on one table and you have a data leak waiting to happen. Skip the index and you have a performance cliff at ~50K rows.

Step 2: Helper function to get the current user's org

RLS policies need to know which org the current user belongs to. Create a helper function:

-- Returns the org_id of the currently authenticated user
CREATE OR REPLACE FUNCTION auth.user_org_id()
RETURNS UUID AS $$
  SELECT org_id
  FROM user_profiles
  WHERE id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;

SECURITY DEFINER means this function runs with the permissions of its creator (typically postgres), so it can read user_profiles even when RLS is enabled on that table. STABLE tells Postgres the function returns the same result within a single transaction, enabling query optimization.

Step 3: Enable RLS and create policies

-- Enable RLS on all tenant-scoped tables
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

-- Products: users can only see their org's products
CREATE POLICY "Users see own org products" ON products
  FOR SELECT USING (org_id = auth.user_org_id());

CREATE POLICY "Users insert own org products" ON products
  FOR INSERT WITH CHECK (org_id = auth.user_org_id());

CREATE POLICY "Users update own org products" ON products
  FOR UPDATE USING (org_id = auth.user_org_id());

CREATE POLICY "Users delete own org products" ON products
  FOR DELETE USING (org_id = auth.user_org_id());

-- User profiles: users see members of their own org
CREATE POLICY "Users see own org members" ON user_profiles
  FOR SELECT USING (org_id = auth.user_org_id());

Now every query automatically filters by the current user's organization. No application code needed.

Step 4: Next.js server-side usage

With RLS in place, your API routes become simpler because the database handles authorization:

// app/api/products/route.ts
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';
import { NextResponse } from 'next/server';

export async function GET() {
  const cookieStore = await cookies();

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll();
        },
      },
    }
  );

  // RLS automatically filters to the user's org
  // No WHERE clause needed for org_id
  const { data, error } = await supabase
    .from('products')
    .select('*')
    .order('created_at', { ascending: false });

  if (error) {
    return NextResponse.json({ error: error.message }, { status: 500 });
  }

  return NextResponse.json(data);
}

Notice there's no WHERE org_id = ... in the query. RLS handles it. This means even if a developer forgets to filter, the data is still protected. That defensive layer is the whole point.

Step 5: Role-based access within an org

Sometimes you need different permissions for different roles (owner, admin, member):

-- Helper function to get current user's role
CREATE OR REPLACE FUNCTION auth.user_role()
RETURNS TEXT AS $$
  SELECT role
  FROM user_profiles
  WHERE id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Only admins and owners can delete products
DROP POLICY "Users delete own org products" ON products;
CREATE POLICY "Admins delete own org products" ON products
  FOR DELETE USING (
    org_id = auth.user_org_id()
    AND auth.user_role() IN ('admin', 'owner')
  );

-- Only owners can manage org settings
CREATE POLICY "Owners manage org" ON organizations
  FOR UPDATE USING (
    id = auth.user_org_id()
    AND auth.user_role() = 'owner'
  );

Step 6: Database migrations

Never modify the production database directly. Use migration files:

-- supabase/migrations/20260115_add_inventory_tracking.sql

-- Add batch tracking to products
ALTER TABLE products ADD COLUMN batch_number TEXT;
ALTER TABLE products ADD COLUMN expiry_date DATE;
ALTER TABLE products ADD COLUMN manufactured_date DATE;

-- Create inventory movements table (tenant-scoped)
CREATE TABLE inventory_movements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  product_id UUID NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  movement_type TEXT NOT NULL CHECK (movement_type IN ('in', 'out', 'adjustment')),
  reference TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_inventory_org ON inventory_movements(org_id);

ALTER TABLE inventory_movements ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own org movements" ON inventory_movements
  FOR SELECT USING (org_id = auth.user_org_id());

CREATE POLICY "Users insert own org movements" ON inventory_movements
  FOR INSERT WITH CHECK (org_id = auth.user_org_id());

Push migrations with:

npx supabase db push --linked

Common pitfalls

1. Forgetting RLS on a new table. Every new table needs ALTER TABLE ... ENABLE ROW LEVEL SECURITY and policies. Add it to a pre-deploy checklist or, better, a CI check that fails the build if any table with org_id lacks RLS.
2. Using the service role key in client code. The service role key bypasses RLS entirely. Only use it in server-side code for admin operations. The anon key respects RLS — that's what your app should use.
3. Not indexing org_id. Without an index, RLS policies cause full table scans. On a table with 100K+ rows across all tenants, this kills performance.
4. Circular dependencies in helper functions. If auth.user_org_id() reads from a table that has an RLS policy which calls auth.user_org_id(), you get infinite recursion. Use SECURITY DEFINER on the helper function to bypass RLS for that specific lookup.
5. Testing with only one org. Your RLS might work perfectly until a second org signs up. Always test with at least 2 organizations and verify data isolation between them. This is the #1 RLS bug found in production.

Testing RLS isolation

-- Verify isolation: this should return 0 rows for org B's data
-- when authenticated as a user from org A
SET request.jwt.claims = '{"sub": "user-a-uuid"}';
SELECT * FROM products; -- Should only show org A products

In our ERP, this check runs as part of the test suite — 350+ tests, including explicit multi-tenant isolation tests. Every new feature ships with at least one cross-org test. RLS bugs are silent in single-tenant testing — they only surface with two tenants. Bake the test in from day one.

Related reading

RLS is the database-layer foundation; the application layer of multi-tenant AI products has its own gotchas — see Why I'm Still Building RAG in 2026 for retrieval architecture decisions in multi-tenant AI, and Why RAG Fails in Production for the chunking/embedding patterns that survive a real customer load.

Frequently Asked Questions

Bottom line

Multi-tenant Postgres with Supabase RLS is the cleanest pattern I've shipped. The database becomes the source of truth for isolation, your app code stays simple, and you sleep at night knowing a forgotten WHERE clause cannot leak customer data. The setup cost is one day. The cost of getting it wrong later is potentially the company.

Building a multi-tenant SaaS and want a second pair of eyes on the schema before you ship? Email hello@quickcomet.com — happy to review.