Multi-tenant RLS

Multi-tenancy là gì?

Single-tenant vs Multi-tenant

┌─────────────────────────────────────────────────────────────┐
│                    TENANCY MODELS                            │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  SINGLE-TENANT                                               │
│  ┌─────────┐  ┌─────────┐  ┌─────────┐                     │
│  │ App     │  │ App     │  │ App     │                     │
│  │ + DB    │  │ + DB    │  │ + DB    │                     │
│  │ Tenant1 │  │ Tenant2 │  │ Tenant3 │                     │
│  └─────────┘  └─────────┘  └─────────┘                     │
│                                                              │
│  MULTI-TENANT                                                │
│  ┌─────────────────────────────────────────┐               │
│  │              Shared App + DB            │               │
│  │  ┌─────────┬─────────┬─────────┐       │               │
│  │  │ Tenant1 │ Tenant2 │ Tenant3 │       │               │
│  │  │  data   │  data   │  data   │       │               │
│  │  └─────────┴─────────┴─────────┘       │               │
│  │         Isolated by RLS                  │               │
│  └─────────────────────────────────────────┘               │
│                                                              │
└─────────────────────────────────────────────────────────────┘

---

Multi-tenant Schema Design

Core Tables

-- Tenants (Organizations/Companies)
CREATE TABLE public.tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  plan TEXT DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tenant memberships
CREATE TABLE public.tenant_users (
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (tenant_id, user_id)
);

-- Index for performance
CREATE INDEX idx_tenant_users_user ON tenant_users(user_id);
CREATE INDEX idx_tenant_users_tenant ON tenant_users(tenant_id);

---

Business Tables

-- All business tables have tenant_id
CREATE TABLE public.projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  assignee_id UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index tenant_id on all tables
CREATE INDEX idx_projects_tenant ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant ON tasks(tenant_id);

---

RLS Helper Functions

Get User's Tenants

-- Return all tenant IDs user belongs to
CREATE OR REPLACE FUNCTION get_user_tenant_ids()
RETURNS SETOF UUID AS $$
  SELECT tenant_id
  FROM tenant_users
  WHERE user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Check if user is in specific tenant
CREATE OR REPLACE FUNCTION is_tenant_member(p_tenant_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM tenant_users
    WHERE tenant_id = p_tenant_id
    AND user_id = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Get user's role in tenant
CREATE OR REPLACE FUNCTION get_tenant_role(p_tenant_id UUID)
RETURNS TEXT AS $$
  SELECT role FROM tenant_users
  WHERE tenant_id = p_tenant_id
  AND user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

---

Tenant-based RLS Policies

Basic Tenant Isolation

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Projects: Tenant members can access
CREATE POLICY "Tenant members access projects"
  ON projects FOR ALL
  USING (tenant_id IN (SELECT get_user_tenant_ids()))
  WITH CHECK (tenant_id IN (SELECT get_user_tenant_ids()));

-- Tasks: Tenant members can access
CREATE POLICY "Tenant members access tasks"
  ON tasks FOR ALL
  USING (tenant_id IN (SELECT get_user_tenant_ids()))
  WITH CHECK (tenant_id IN (SELECT get_user_tenant_ids()));

Role-based within Tenant

-- Helper for admin check
CREATE OR REPLACE FUNCTION is_tenant_admin(p_tenant_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM tenant_users
    WHERE tenant_id = p_tenant_id
    AND user_id = auth.uid()
    AND role IN ('owner', 'admin')
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Only admins can delete projects
CREATE POLICY "Admins delete projects"
  ON projects FOR DELETE
  USING (is_tenant_admin(tenant_id));

-- Only owners can manage tenant settings
CREATE POLICY "Owners manage tenant"
  ON tenants FOR UPDATE
  USING (
    id IN (
      SELECT tenant_id FROM tenant_users
      WHERE user_id = auth.uid()
      AND role = 'owner'
    )
  );

---

Tenant Context Patterns

Pattern 1: Always Include tenant_id

// Client must always include tenant_id
const { data: projects } = await supabase
  .from('projects')
  .select('*')
  .eq('tenant_id', currentTenantId);  // RLS also enforces this

// Insert with tenant_id
await supabase.from('projects').insert({
  tenant_id: currentTenantId,
  name: 'New Project',
});

Pattern 2: Active Tenant in User Profile

-- Store active tenant in profile
ALTER TABLE profiles ADD COLUMN active_tenant_id UUID REFERENCES tenants(id);

-- Function to get active tenant
CREATE OR REPLACE FUNCTION get_active_tenant_id()
RETURNS UUID AS $$
  SELECT active_tenant_id FROM profiles
  WHERE id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Policies use active tenant
CREATE POLICY "Access active tenant data"
  ON projects FOR SELECT
  USING (tenant_id = get_active_tenant_id());

---

Pattern 3: Tenant from JWT Claim

-- Set tenant in app_metadata during invite/join
-- auth.users.app_metadata = { tenant_id: "uuid" }

-- Function to get tenant from JWT
CREATE OR REPLACE FUNCTION get_jwt_tenant_id()
RETURNS UUID AS $$
  SELECT (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::UUID;
$$ LANGUAGE sql STABLE;

-- Use in policies
CREATE POLICY "Access tenant from JWT"
  ON projects FOR ALL
  USING (tenant_id = get_jwt_tenant_id());

Switch Tenant

// Update active tenant
await supabase.from('profiles')
  .update({ active_tenant_id: newTenantId })
  .eq('id', userId);

// Or update JWT claim (requires Edge Function)
await supabase.functions.invoke('switch-tenant', {
  body: { tenantId: newTenantId }
});

---

Tenant Invitation Flow

Invite User to Tenant

CREATE TABLE public.invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  role TEXT DEFAULT 'member',
  invited_by UUID REFERENCES auth.users(id),
  token TEXT UNIQUE DEFAULT gen_random_uuid()::TEXT,
  expires_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '7 days',
  accepted_at TIMESTAMPTZ
);

-- Only tenant admins can invite
CREATE POLICY "Admins can invite"
  ON invitations FOR INSERT
  WITH CHECK (is_tenant_admin(tenant_id));

Accept Invitation

CREATE OR REPLACE FUNCTION accept_invitation(invitation_token TEXT)
RETURNS void AS $$
DECLARE
  inv RECORD;
BEGIN
  -- Get invitation
  SELECT * INTO inv FROM invitations
  WHERE token = invitation_token
  AND expires_at > NOW()
  AND accepted_at IS NULL;

  IF inv IS NULL THEN
    RAISE EXCEPTION 'Invalid or expired invitation';
  END IF;

  -- Add user to tenant
  INSERT INTO tenant_users (tenant_id, user_id, role)
  VALUES (inv.tenant_id, auth.uid(), inv.role)
  ON CONFLICT DO NOTHING;

  -- Mark invitation as accepted
  UPDATE invitations
  SET accepted_at = NOW()
  WHERE id = inv.id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

---

Cross-tenant Queries

Admin Dashboard (Service Role)

// Service role bypasses RLS
// Use for admin dashboards, analytics

const supabaseAdmin = createClient(url, serviceRoleKey);

// Get all tenants stats
const { data } = await supabaseAdmin
  .from('tenants')
  .select(`
    id,
    name,
    projects(count),
    tenant_users(count)
  `);

User's All Tenants View

-- User can see tenants they belong to
CREATE POLICY "Users see own tenants"
  ON tenants FOR SELECT
  USING (id IN (SELECT get_user_tenant_ids()));

// Get all user's tenants
const { data: myTenants } = await supabase
  .from('tenants')
  .select(`
    id,
    name,
    slug,
    tenant_users!inner (
      role,
      joined_at
    )
  `);

---

Performance Considerations

Indexes

-- Essential indexes for multi-tenant
CREATE INDEX idx_tenant_users_user ON tenant_users(user_id);
CREATE INDEX idx_tenant_users_tenant ON tenant_users(tenant_id);
CREATE INDEX idx_projects_tenant ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant ON tasks(tenant_id);

-- Composite index for common queries
CREATE INDEX idx_tasks_tenant_status ON tasks(tenant_id, status);

Function Caching

-- Mark functions as STABLE for caching
CREATE FUNCTION get_user_tenant_ids()
RETURNS SETOF UUID AS $$
  ...
$$ LANGUAGE sql SECURITY DEFINER STABLE;  -- Important!

---

Tổng kết

Multi-tenant Checklist

1. ✅ tenant_id column on all business tables
2. ✅ tenant_users junction table for membership
3. ✅ Helper functions for tenant checks
4. ✅ RLS policies using tenant_id
5. ✅ Indexes on tenant_id columns
6. ✅ Invitation flow for adding users

Key Patterns

Pattern	Description
Direct tenant_id	Client sends tenant_id each request
Active tenant	Store in user profile
JWT claim	Store in app_metadata

Security Rules

✅ Every table has tenant_id
✅ RLS enabled on all tables
✅ Cannot access other tenant's data
✅ Service role only for admin ops

---

Q&A

1. Có cần multi-tenant trong dự án?
2. Một user thuộc nhiều tenants được không?
3. Làm sao handle tenant switching?