Multi-Tenancy Patterns

---

What is Multi-Tenancy?

Multi-tenancy is an architecture where a single application serves multiple customers (tenants), with each tenant's data isolated from others. Common in SaaS applications where each company/organization has their own workspace.

---

The Challenge

Single Database, Multiple Tenants:

Tenant A's Data ─┐
                 ├──→ Same Database ──→ Must be isolated!
Tenant B's Data ─┘

User from Tenant A must NOT see Tenant B's data

---

Approach 1: Column-Based Tenancy

Add a tenant_id column to every table:

CREATE TABLE tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE 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')),
  PRIMARY KEY (tenant_id, user_id)
);

-- Every tenant-scoped table has tenant_id
CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE NOT NULL,
  name text NOT NULL,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE NOT NULL,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  title text NOT NULL
);

RLS for Tenant Isolation

-- Helper function: Get user's tenant
CREATE FUNCTION get_user_tenant_id() RETURNS uuid AS $$
  SELECT tenant_id FROM tenant_users WHERE user_id = auth.uid() LIMIT 1
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Restrictive policy on all tenant tables
CREATE POLICY "Tenant isolation"
  ON projects
  AS RESTRICTIVE
  FOR ALL
  USING (tenant_id = get_user_tenant_id())
  WITH CHECK (tenant_id = get_user_tenant_id());

CREATE POLICY "Tenant isolation"
  ON tasks
  AS RESTRICTIVE
  FOR ALL
  USING (tenant_id = get_user_tenant_id())
  WITH CHECK (tenant_id = get_user_tenant_id());

Pros and Cons

Pros	Cons
Simple to implement	Every table needs tenant_id
Easy backup/restore per tenant	Queries always filter by tenant
Single schema to maintain	Potential for mistakes

---

Approach 2: Schema-Based Tenancy

Each tenant gets their own PostgreSQL schema:

-- Create schema per tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Same table structure in each
CREATE TABLE tenant_acme.projects (...);
CREATE TABLE tenant_globex.projects (...);

Switching Schemas

-- Set search path based on tenant
SET search_path TO tenant_acme, public;

-- Now 'projects' refers to tenant_acme.projects
SELECT * FROM projects;

Pros and Cons

Pros	Cons
Complete isolation	Complex schema management
Per-tenant customization possible	Migrations for each tenant
No tenant_id needed	Harder to query across tenants
Natural PostgreSQL feature	Limited by schema count

---

Approach 3: Database-Based Tenancy

Each tenant gets their own database or Supabase project:

tenant-a.supabase.co ──→ Database A
tenant-b.supabase.co ──→ Database B
tenant-c.supabase.co ──→ Database C

Pros and Cons

Pros	Cons
Complete isolation	Multiple projects to manage
Independent scaling	Higher cost
Easy compliance	Complex deployment
No RLS needed	No cross-tenant queries

---

Recommended: Column-Based for Most Apps

Column-based tenancy works well with Supabase because:

• RLS naturally handles isolation
• Single codebase and schema
• Easy to implement
• Scales to many tenants

---

Implementing Column-Based Tenancy

Step 1: Core Tables

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()
);

CREATE TABLE organization_members (
  organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role text DEFAULT 'member',
  PRIMARY KEY (organization_id, user_id)
);

-- Index for RLS
CREATE INDEX idx_org_members_user ON organization_members(user_id);

Step 2: Helper Functions

-- Get all orgs user belongs to
CREATE FUNCTION get_user_organizations()
RETURNS SETOF uuid AS $$
  SELECT organization_id
  FROM organization_members
  WHERE user_id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Check if user is in org
CREATE FUNCTION is_org_member(org_id uuid)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM organization_members
    WHERE organization_id = org_id
    AND user_id = auth.uid()
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Check if user is org admin
CREATE FUNCTION is_org_admin(org_id uuid)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM organization_members
    WHERE organization_id = org_id
    AND user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Step 3: Tenant-Scoped Tables

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE NOT NULL,
  name text NOT NULL,
  created_at timestamptz DEFAULT now()
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Org members can view projects"
  ON projects FOR SELECT
  USING (is_org_member(organization_id));

CREATE POLICY "Org members can create projects"
  ON projects FOR INSERT
  WITH CHECK (is_org_member(organization_id));

CREATE POLICY "Org admins can update projects"
  ON projects FOR UPDATE
  USING (is_org_admin(organization_id));

CREATE POLICY "Org admins can delete projects"
  ON projects FOR DELETE
  USING (is_org_admin(organization_id));

Step 4: Client Usage

// Get all projects for user's organizations
const { data: projects } = await supabase
  .from('projects')
  .select('*, organization:organizations(name)')

// Create project in specific org
const { error } = await supabase
  .from('projects')
  .insert({
    organization_id: orgId,
    name: 'New Project'
  })

---

Handling Multiple Organizations

Users often belong to multiple organizations:

// List user's organizations
const { data: orgs } = await supabase
  .from('organization_members')
  .select('role, organization:organizations(*)')
  .eq('user_id', user.id)

// Current org context (stored in app state)
const [currentOrgId, setCurrentOrgId] = useState(null)

// Filter to current org
const { data: projects } = await supabase
  .from('projects')
  .select('*')
  .eq('organization_id', currentOrgId)

---

Invitations and Onboarding

CREATE TABLE organization_invites (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  email text NOT NULL,
  role text DEFAULT 'member',
  token text UNIQUE DEFAULT encode(gen_random_bytes(32), 'hex'),
  expires_at timestamptz DEFAULT now() + interval '7 days',
  created_at timestamptz DEFAULT now()
);

-- Accept invitation function
CREATE FUNCTION accept_invite(invite_token text)
RETURNS void AS $$
DECLARE
  invite record;
BEGIN
  SELECT * INTO invite FROM organization_invites
  WHERE token = invite_token AND expires_at > now();

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Invalid or expired invite';
  END IF;

  INSERT INTO organization_members (organization_id, user_id, role)
  VALUES (invite.organization_id, auth.uid(), invite.role);

  DELETE FROM organization_invites WHERE id = invite.id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

---

Key Takeaways

1. Column-based is most common: Works well with RLS
2. Helper functions simplify policies: is_org_member(), is_org_admin()
3. Index organization_id: Critical for query performance
4. Handle multiple orgs: Users often belong to several
5. Invitations for onboarding: Token-based invite flow

---

Next Steps

With tenancy patterns covered, we'll explore soft deletes and audit trails for data management.

---

Multi-tenancy is a solved problem in Supabase. RLS provides the isolation, PostgreSQL provides the performance, and the column-based approach keeps things simple. Start simple, scale as needed.