Supabase Multi-Tenant Architecture Guide

Status: Complete

Overview

This guide explains how to implement multi-tenant architectures in Supabase, covering different isolation strategies, Row Level Security (RLS) patterns, and best practices for SaaS applications.

Multi-Tenancy Strategies

1. Row-Level Isolation (Recommended)

Single database, shared schema, RLS policies

┌─────────────────────────────────┐
│         Single Database         │
├─────────────────────────────────┤
│  Table: organizations           │
│  - id: org_1                   │
│  - id: org_2                   │
│  - id: org_3                   │
├─────────────────────────────────┤
│  Table: users                   │
│  - org_id: org_1               │
│  - org_id: org_2               │
│  - org_id: org_3               │
├─────────────────────────────────┤
│  RLS Policies enforce isolation │
└─────────────────────────────────┘

Pros:

• Simple to implement
• Cost-effective
• Easy cross-tenant queries (admin)
• Single backup/migration

Cons:

• Shared resources
• Complex RLS policies
• Potential noisy neighbor issues

2. Schema-Level Isolation

Single database, separate schemas per tenant

┌─────────────────────────────────┐
│         Single Database         │
├─────────────────────────────────┤
│  Schema: tenant_1               │
│  - tables, views, functions     │
├─────────────────────────────────┤
│  Schema: tenant_2               │
│  - tables, views, functions     │
├─────────────────────────────────┤
│  Schema: shared                 │
│  - common tables                │
└─────────────────────────────────┘

Pros:

• Better isolation
• Simpler security model
• Per-tenant customization

Cons:

• Complex migrations
• Higher maintenance
• Schema proliferation

3. Database-Level Isolation

Separate Supabase project per tenant

┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│  Project 1   │ │  Project 2   │ │  Project 3   │
│  Tenant: A   │ │  Tenant: B   │ │  Tenant: C   │
│  Database    │ │  Database    │ │  Database    │
│  Auth        │ │  Auth        │ │  Auth        │
│  Storage     │ │  Storage     │ │  Storage     │
└──────────────┘ └──────────────┘ └──────────────┘

Pros:

• Complete isolation
• Independent scaling
• Compliance friendly

Cons:

• Highest cost
• Complex management
• Difficult cross-tenant operations

Implementation: Row-Level Multi-Tenancy

Database Schema

-- =====================================================
-- CORE TABLES
-- =====================================================

-- Organizations (Tenants)
CREATE TABLE organizations (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    plan TEXT DEFAULT 'free',
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- User profiles with organization association
CREATE TABLE profiles (
    id UUID REFERENCES auth.users PRIMARY KEY,
    email TEXT NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Organization membership
CREATE TABLE organization_members (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    role TEXT CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(organization_id, user_id)
);

-- Create index for performance
CREATE INDEX idx_org_members_user ON organization_members(user_id);
CREATE INDEX idx_org_members_org ON organization_members(organization_id);

-- =====================================================
-- TENANT-SPECIFIC TABLES
-- =====================================================

-- Example: Projects belong to organizations
CREATE TABLE projects (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Example: Contacts belong to organizations
CREATE TABLE contacts (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    email TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(organization_id, email)
);

Helper Functions

-- =====================================================
-- HELPER FUNCTIONS
-- =====================================================

-- Get user's current organization
CREATE OR REPLACE FUNCTION get_user_organization_id(user_uuid UUID)
RETURNS UUID AS $
    SELECT organization_id 
    FROM organization_members 
    WHERE user_id = user_uuid 
    LIMIT 1;
$ LANGUAGE SQL SECURITY DEFINER;

-- Get user's organizations
CREATE OR REPLACE FUNCTION get_user_organizations(user_uuid UUID)
RETURNS SETOF UUID AS $
    SELECT organization_id 
    FROM organization_members 
    WHERE user_id = user_uuid;
$ LANGUAGE SQL SECURITY DEFINER;

-- Check if user has role in organization
CREATE OR REPLACE FUNCTION user_has_role(
    user_uuid UUID,
    org_uuid UUID,
    required_role TEXT
) RETURNS BOOLEAN AS $
DECLARE
    user_role TEXT;
    role_hierarchy JSONB := '{"owner": 4, "admin": 3, "member": 2, "viewer": 1}';
BEGIN
    SELECT role INTO user_role
    FROM organization_members
    WHERE user_id = user_uuid 
    AND organization_id = org_uuid;
    
    IF user_role IS NULL THEN
        RETURN FALSE;
    END IF;
    
    RETURN (role_hierarchy->>user_role)::INT >= 
           (role_hierarchy->>required_role)::INT;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- Set organization context (for connection pooling)
CREATE OR REPLACE FUNCTION set_org_context(org_uuid UUID)
RETURNS void AS $
BEGIN
    PERFORM set_config('app.current_org', org_uuid::TEXT, false);
END;
$ LANGUAGE plpgsql;

-- Get organization context
CREATE OR REPLACE FUNCTION get_org_context()
RETURNS UUID AS $
BEGIN
    RETURN current_setting('app.current_org', true)::UUID;
END;
$ LANGUAGE plpgsql;

Row Level Security Policies

-- =====================================================
-- RLS POLICIES
-- =====================================================

-- Enable RLS on all tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;

-- -----------------------------------------------------
-- Organizations Policies
-- -----------------------------------------------------

-- Users can view organizations they belong to
CREATE POLICY "Users view own organizations" ON organizations
    FOR SELECT USING (
        id IN (SELECT get_user_organizations(auth.uid()))
    );

-- Only owners can update organization
CREATE POLICY "Owners update organization" ON organizations
    FOR UPDATE USING (
        user_has_role(auth.uid(), id, 'owner')
    );

-- -----------------------------------------------------
-- Organization Members Policies
-- -----------------------------------------------------

-- Members can view their organization's members
CREATE POLICY "View organization members" ON organization_members
    FOR SELECT USING (
        organization_id IN (SELECT get_user_organizations(auth.uid()))
    );

-- Admins can manage members
CREATE POLICY "Admins manage members" ON organization_members
    FOR ALL USING (
        user_has_role(auth.uid(), organization_id, 'admin')
    );

-- -----------------------------------------------------
-- Projects Policies (Tenant Data)
-- -----------------------------------------------------

-- View projects in user's organizations
CREATE POLICY "View organization projects" ON projects
    FOR SELECT USING (
        organization_id IN (SELECT get_user_organizations(auth.uid()))
    );

-- Members can create projects
CREATE POLICY "Members create projects" ON projects
    FOR INSERT WITH CHECK (
        user_has_role(auth.uid(), organization_id, 'member')
    );

-- Members can update projects
CREATE POLICY "Members update projects" ON projects
    FOR UPDATE USING (
        user_has_role(auth.uid(), organization_id, 'member')
    );

-- Admins can delete projects
CREATE POLICY "Admins delete projects" ON projects
    FOR DELETE USING (
        user_has_role(auth.uid(), organization_id, 'admin')
    );

-- -----------------------------------------------------
-- Contacts Policies (Tenant Data)
-- -----------------------------------------------------

-- View contacts in user's organizations
CREATE POLICY "View organization contacts" ON contacts
    FOR SELECT USING (
        organization_id IN (SELECT get_user_organizations(auth.uid()))
    );

-- Members can manage contacts
CREATE POLICY "Members manage contacts" ON contacts
    FOR ALL USING (
        user_has_role(auth.uid(), organization_id, 'member')
    );

Advanced RLS Patterns

1. Hierarchical Permissions

-- Role-based access with hierarchy
CREATE POLICY "Hierarchical access" ON sensitive_data
    FOR ALL USING (
        CASE 
            WHEN user_has_role(auth.uid(), organization_id, 'owner') THEN true
            WHEN user_has_role(auth.uid(), organization_id, 'admin') THEN NOT is_sensitive
            WHEN user_has_role(auth.uid(), organization_id, 'member') THEN is_public
            ELSE false
        END
    );

2. Time-based Access

-- Temporary access with expiration
CREATE POLICY "Time-limited access" ON documents
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM document_shares
            WHERE document_id = documents.id
            AND shared_with = auth.uid()
            AND expires_at > NOW()
        )
    );

3. Attribute-based Access

-- Department-based access
CREATE POLICY "Department access" ON department_data
    FOR SELECT USING (
        department_id IN (
            SELECT department_id 
            FROM user_departments 
            WHERE user_id = auth.uid()
        )
    );

Client Implementation

TypeScript/JavaScript SDK

// types/database.ts
export interface Organization {
  id: string
  name: string
  slug: string
  plan: 'free' | 'pro' | 'enterprise'
}

export interface OrganizationMember {
  organization_id: string
  user_id: string
  role: 'owner' | 'admin' | 'member' | 'viewer'
}

// lib/supabase-client.ts
import { createClient } from '@supabase/supabase-js'

export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// hooks/useOrganization.ts
export function useOrganization() {
  const [organization, setOrganization] = useState<Organization | null>(null)
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    fetchUserOrganization()
  }, [])

  async function fetchUserOrganization() {
    const { data: { user } } = await supabase.auth.getUser()
    
    if (user) {
      const { data } = await supabase
        .from('organization_members')
        .select('organization:organizations(*)')
        .eq('user_id', user.id)
        .single()
      
      setOrganization(data?.organization)
    }
    setLoading(false)
  }

  async function switchOrganization(orgId: string) {
    // Update user's active organization
    const { data: { user } } = await supabase.auth.getUser()
    
    await supabase
      .from('user_settings')
      .upsert({
        user_id: user!.id,
        active_organization_id: orgId
      })
    
    await fetchUserOrganization()
  }

  return { organization, loading, switchOrganization }
}

// components/OrganizationProvider.tsx
export const OrganizationContext = createContext<{
  organization: Organization | null
  setOrganization: (org: Organization) => void
}>({ organization: null, setOrganization: () => {} })

export function OrganizationProvider({ children }) {
  const { organization, loading } = useOrganization()

  if (loading) return <Loading />

  return (
    <OrganizationContext.Provider value={{ organization }}>
      {children}
    </OrganizationContext.Provider>
  )
}

// Usage in components
function ProjectList() {
  const { organization } = useContext(OrganizationContext)

  const { data: projects } = await supabase
    .from('projects')
    .select('*')
    .eq('organization_id', organization?.id)
    .order('created_at', { ascending: false })

  return (
    <div>
      {projects?.map(project => (
        <ProjectCard key={project.id} project={project} />
      ))}
    </div>
  )
}

API Route Handlers

// app/api/organizations/[id]/route.ts
import { createRouteHandlerClient } from '@supabase/auth-helpers-nextjs'
import { cookies } from 'next/headers'

export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  const supabase = createRouteHandlerClient({ cookies })
  
  // Check user has access to organization
  const { data: { user } } = await supabase.auth.getUser()
  
  const { data: member } = await supabase
    .from('organization_members')
    .select('role')
    .eq('organization_id', params.id)
    .eq('user_id', user?.id)
    .single()
  
  if (!member) {
    return Response.json({ error: 'Unauthorized' }, { status: 403 })
  }
  
  // Fetch organization data
  const { data: organization } = await supabase
    .from('organizations')
    .select('*')
    .eq('id', params.id)
    .single()
  
  return Response.json({ organization, role: member.role })
}

Testing Multi-Tenancy

Test Scenarios

-- Create test organizations
INSERT INTO organizations (name, slug) VALUES
  ('Acme Corp', 'acme'),
  ('Tech Startup', 'techstartup');

-- Create test users (use Supabase Auth)
-- User 1: owner of Acme
-- User 2: member of Acme
-- User 3: owner of Tech Startup
-- User 4: member of both

-- Test isolation
-- Login as User 1, should only see Acme data
-- Login as User 3, should only see Tech Startup data
-- Login as User 4, should see both

-- Test permissions
-- User 2 (member) cannot delete projects
-- User 1 (owner) can manage everything

RLS Testing Queries

-- Test as specific user
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claim.sub TO 'user-uuid-here';

-- Check what organizations user can see
SELECT * FROM organizations;

-- Check what projects user can see
SELECT * FROM projects;

-- Reset
RESET role;

Performance Optimization

1. Indexes for Multi-Tenant Queries

-- Critical indexes for performance
CREATE INDEX idx_projects_org ON projects(organization_id);
CREATE INDEX idx_contacts_org ON contacts(organization_id);
CREATE INDEX idx_org_members_composite ON organization_members(user_id, organization_id);

-- Partial indexes for active records
CREATE INDEX idx_projects_active ON projects(organization_id) 
  WHERE deleted_at IS NULL;

2. Materialized Views for Analytics

-- Per-organization statistics
CREATE MATERIALIZED VIEW organization_stats AS
SELECT 
    organization_id,
    COUNT(DISTINCT user_id) as member_count,
    COUNT(DISTINCT CASE WHEN role = 'owner' THEN user_id END) as owner_count,
    COUNT(DISTINCT CASE WHEN role = 'admin' THEN user_id END) as admin_count
FROM organization_members
GROUP BY organization_id;

-- Refresh periodically
CREATE OR REPLACE FUNCTION refresh_organization_stats()
RETURNS void AS $
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY organization_stats;
END;
$ LANGUAGE plpgsql;

3. Connection Pooling Strategy

// Use transaction-level organization context
async function executeInOrgContext(orgId: string, callback: Function) {
  const { data, error } = await supabase.rpc('set_org_context', {
    org_uuid: orgId
  })
  
  if (!error) {
    return await callback()
  }
}

// Usage
await executeInOrgContext(organizationId, async () => {
  // All queries here automatically filtered by organization
  const { data } = await supabase.from('projects').select()
  return data
})

Monitoring & Compliance

Audit Logging

-- Audit log table
CREATE TABLE audit_logs (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    organization_id UUID REFERENCES organizations(id),
    user_id UUID REFERENCES auth.users(id),
    action TEXT NOT NULL,
    table_name TEXT,
    record_id UUID,
    old_data JSONB,
    new_data JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS trigger AS $
BEGIN
    INSERT INTO audit_logs (
        organization_id,
        user_id,
        action,
        table_name,
        record_id,
        old_data,
        new_data
    ) VALUES (
        COALESCE(NEW.organization_id, OLD.organization_id),
        auth.uid(),
        TG_OP,
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        to_jsonb(OLD),
        to_jsonb(NEW)
    );
    RETURN NEW;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply to tables
CREATE TRIGGER audit_projects
    AFTER INSERT OR UPDATE OR DELETE ON projects
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Data Isolation Verification

-- Verify no cross-tenant data leakage
CREATE OR REPLACE FUNCTION verify_tenant_isolation()
RETURNS TABLE(table_name TEXT, issue TEXT) AS $
DECLARE
    rec RECORD;
BEGIN
    -- Check for missing RLS
    FOR rec IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public' 
        AND tablename NOT IN ('schema_migrations')
    LOOP
        IF NOT EXISTS (
            SELECT 1 FROM pg_policies 
            WHERE tablename = rec.tablename
        ) THEN
            RETURN QUERY SELECT rec.tablename, 'Missing RLS policies';
        END IF;
    END LOOP;
    
    -- Check for missing organization_id
    FOR rec IN 
        SELECT table_name, column_name 
        FROM information_schema.columns 
        WHERE table_schema = 'public' 
        AND column_name = 'id'
    LOOP
        IF NOT EXISTS (
            SELECT 1 FROM information_schema.columns 
            WHERE table_schema = 'public' 
            AND table_name = rec.table_name 
            AND column_name = 'organization_id'
        ) AND rec.table_name NOT IN ('organizations', 'profiles') THEN
            RETURN QUERY SELECT rec.table_name, 'Missing organization_id column';
        END IF;
    END LOOP;
END;
$ LANGUAGE plpgsql;

-- Run verification
SELECT * FROM verify_tenant_isolation();

Migration Guide

From Single-Tenant to Multi-Tenant

-- Step 1: Add organization structure
ALTER TABLE existing_table ADD COLUMN organization_id UUID;

-- Step 2: Create default organization
INSERT INTO organizations (id, name, slug) 
VALUES ('00000000-0000-0000-0000-000000000001', 'Default', 'default');

-- Step 3: Assign existing data
UPDATE existing_table 
SET organization_id = '00000000-0000-0000-0000-000000000001';

-- Step 4: Add NOT NULL constraint
ALTER TABLE existing_table 
ALTER COLUMN organization_id SET NOT NULL;

-- Step 5: Add foreign key
ALTER TABLE existing_table 
ADD CONSTRAINT fk_organization 
FOREIGN KEY (organization_id) 
REFERENCES organizations(id) ON DELETE CASCADE;

-- Step 6: Enable RLS
ALTER TABLE existing_table ENABLE ROW LEVEL SECURITY;

-- Step 7: Create policies
CREATE POLICY "Tenant isolation" ON existing_table
    FOR ALL USING (
        organization_id IN (SELECT get_user_organizations(auth.uid()))
    );

Best Practices

1. Always use RLS - Never rely on application-level filtering alone
2. Index organization_id - Critical for query performance
3. Audit everything - Track all tenant data access
4. Test isolation - Regular penetration testing
5. Monitor usage - Per-tenant resource tracking
6. Plan for scale - Consider sharding strategy early
7. Backup per tenant - Enable tenant-specific recovery
8. Document policies - Clear RLS policy documentation
9. Use strong types - TypeScript for client safety
10. Regular reviews - Audit RLS policies quarterly

Next Steps

1. Implement organization onboarding flow
2. Add billing and subscription management
3. Create admin dashboard for cross-tenant operations
4. Set up monitoring and alerting
5. Implement data export/import per tenant
6. Plan disaster recovery strategy

Resources

• PostgreSQL RLS Documentation
• Supabase RLS Guide
• Multi-tenant Architecture Patterns
• SaaS Tenant Isolation Strategies