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
- Column-based is most common: Works well with RLS
- Helper functions simplify policies:
is_org_member(),is_org_admin() - Index organization_id: Critical for query performance
- Handle multiple orgs: Users often belong to several
- 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.
التالي · الدرس 32 من 40
Soft Deletes and Audit Trails
قراءة 5 دقيقة
أنهيت 31 من 40 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

