roedyrustam/vibes-plug/saas-multi-tenant

SaaS Multi-Tenant Expert (2026 Edition)

English | Bahasa Indonesia

<a name="english"></a>

English

Orchestration & Integration

• Upstream Orchestrators: Works under brainstorming and zero-to-prod-orchestrator (Phase 3 - Schema Design & Database Architecture) for multi-tenant data modeling.
• Downstream Cooperating Skills:
• database-orm-expert — Implement tenant migrations, foreign keys, and PostgreSQL RLS policies.
• supabase-security-expert — Audit RLS rules, JWT custom claims, and service role bypass vectors.
• saas-billing — Connect workspace plans to subscription limits, dunning, and seat allowances.
• payment-gateway-expert — Provide tenant-aware checkout flows and billing portal access.
• cloud-hosting-expert — Super Admin domain isolation (admin.yourdomain.com) and routing.
• saas-architect — Coordinate 9-phase modernization pipeline when transforming single-tenant codebases.
• session-memory-manager — Store tenant isolation matrices and schema configuration checkpoints.

Description

Expert guide for designing and implementing multi-tenant SaaS architectures with full tenant isolation, Supabase/PostgreSQL Row Level Security (RLS), schema-per-org patterns, RBAC, and Super Admin access controls.

Trigger Conditions

• Building a SaaS application that serves multiple organizations (workspaces/tenants).
• Implementing Row Level Security (RLS) policies in Supabase or PostgreSQL.
• Designing a data model that isolates tenant data securely.
• Implementing role-based access control (RBAC) within a tenant.
• Building the Super Admin management panel for cross-tenant operations.
• Choosing between shared schema vs. schema-per-org isolation strategies.

Tenant Isolation Strategies

Strategy 1: Shared Schema + RLS (Recommended for Most SaaS)

Core Schema Design

-- Central workspaces (tenants) table
CREATE TABLE workspaces (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    slug        TEXT UNIQUE NOT NULL,
    plan        TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
-- Users belong to multiple workspaces via memberships
CREATE TABLE workspace_members (
    workspace_id    UUID REFERENCES workspaces(id) ON DELETE CASCADE,
    user_id         UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    role            TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (workspace_id, user_id)
);
 
-- All tenant data has workspace_id
CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id    UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    created_by      UUID REFERENCES auth.users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Row Level Security Policies

-- Enable RLS on all tenant tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Policy: Users can only see projects in their workspaces
CREATE POLICY "workspace members can view projects"
    ON projects FOR SELECT
    USING (
        workspace_id IN (
            SELECT workspace_id FROM workspace_members
            WHERE user_id = auth.uid()
        )
    );
 
-- Policy: Only admins and owners can create projects
CREATE POLICY "admins can create projects"
    ON projects FOR INSERT
    WITH CHECK (
        workspace_id IN (
            SELECT workspace_id FROM workspace_members
            WHERE user_id = auth.uid()
              AND role IN ('owner', 'admin')
        )
    );
 
-- Policy: Super Admin can bypass RLS (service role only)
-- ⚠️ NEVER expose service role key to frontend

Session Management & RLS Optimization

To avoid performance bottlenecks, embed workspace_id and role directly into the user's Session JWT (Custom Claims). This allows RLS policies to check the session token directly (auth.jwt()->>'workspace_id') instead of joining the workspace_members table on every query.

RLS Helper Functions

-- Helper: Check if current user has a minimum role in a workspace
CREATE OR REPLACE FUNCTION user_has_role(
    p_workspace_id UUID,
    p_min_role TEXT
) RETURNS BOOLEAN AS $$
DECLARE
    role_hierarchy TEXT[] := ARRAY['viewer', 'member', 'admin', 'owner'];
    user_role TEXT;
BEGIN
    SELECT role INTO user_role
    FROM workspace_members
    WHERE workspace_id = p_workspace_id AND user_id = auth.uid();
 
    RETURN (
        array_position(role_hierarchy, user_role) >=
        array_position(role_hierarchy, p_min_role)
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Strategy 2: Schema per Org (Compliance-Heavy)

For regulated industries requiring strict data separation:

-- Dynamically create a schema for each new tenant
CREATE OR REPLACE FUNCTION create_tenant_schema(p_slug TEXT) RETURNS VOID AS $$
BEGIN
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS tenant_%s', p_slug);
    
    -- Create all tenant tables in the new schema
    EXECUTE format('
        CREATE TABLE tenant_%s.projects (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            name TEXT NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )', p_slug);
END;
$$ LANGUAGE plpgsql;
 
-- Bypass pattern: set search_path per connection
SET search_path TO tenant_acme, public;
SELECT * FROM projects;  -- Reads from tenant_acme.projects only

Super Admin Architecture

Super Admin is a separate system that operates across all tenants with elevated privileges:

// Super Admin routes are ONLY accessible at admin.yourdomain.com
// Enforced at DNS + middleware level
// middleware.ts — verify super admin domain
if (hostname === 'admin.yourdomain.com') {
  const session = await verifyAdminSession(req);
  if (!session?.user.isSuperAdmin) {
    return NextResponse.redirect('https://yourdomain.com');
  }
}

-- Super Admin uses service role to bypass RLS
-- admin.sql — queries bypass all RLS policies when using service role key
 
-- Cross-tenant query (only accessible with service role)
SELECT w.name, COUNT(wm.user_id) as member_count, w.plan
FROM workspaces w
LEFT JOIN workspace_members wm ON w.id = wm.workspace_id
GROUP BY w.id
ORDER BY member_count DESC;

RBAC Implementation (TypeScript)

type Role = 'owner' | 'admin' | 'member' | 'viewer';
const ROLE_PERMISSIONS: Record<Role, string[]> = {
  owner: ['*'],  // All permissions
  admin: ['project:create', 'project:delete', 'member:invite', 'member:remove'],
  member: ['project:create', 'project:read', 'project:update'],
  viewer: ['project:read'],
};
function can(userRole: Role, permission: string): boolean {
  const perms = ROLE_PERMISSIONS[userRole];
  return perms.includes('*') || perms.includes(permission);
}
// Usage in API handler
if (!can(currentMember.role, 'project:delete')) {
  throw new ForbiddenError('Insufficient permissions');
}

Sovereign Execution, Token Efficiency & Invariants

<a name="bahasa-indonesia"></a>

Bahasa Indonesia

Integrasi Orkestrasi

• Orkestrator Hulu: Beroperasi di bawah arahan brainstorming dan zero-to-prod-orchestrator (Fase 3 - Desain Skema & Arsitektur Database) untuk pemodelan data multi-tenant.
• Skill Hilir & Kerja Sama:
• database-orm-expert — Implementasi migrasi tenant, foreign keys, dan kebijakan RLS PostgreSQL.
• supabase-security-expert — Audit aturan RLS, custom claims JWT, dan vektor bypass service role.
• saas-billing — Menghubungkan paket workspace dengan batas langganan dan alokasi kursi (seat).
• payment-gateway-expert — Menyediakan alur checkout dan akses portal billing per tenant.
• cloud-hosting-expert — Isolasi domain Super Admin (admin.yourdomain.com) dan konfigurasi routing.
• saas-architect — Mengoordinasikan pipeline modernisasi 9 fase saat mentransformasi aplikasi single-tenant.
• session-memory-manager — Menyimpan matriks isolasi tenant dan checkpoint konfigurasi skema.

Deskripsi

Panduan ahli untuk merancang dan mengimplementasikan arsitektur SaaS multi-tenant dengan isolasi tenant penuh, Row Level Security (RLS) Supabase/PostgreSQL, pola schema-per-org, RBAC, dan kontrol akses Super Admin.

Kondisi Pemicu

• Membangun aplikasi SaaS yang melayani beberapa organisasi (workspace/tenant).
• Mengimplementasikan kebijakan Row Level Security (RLS) di Supabase atau PostgreSQL.
• Merancang model data yang mengisolasi data tenant dengan aman.
• Mengimplementasikan role-based access control (RBAC) dalam tenant.
• Membangun panel manajemen Super Admin untuk operasi lintas tenant.
• Memilih antara shared schema vs. schema-per-org.

Strategi Isolasi Tenant

Strategi 1: Shared Schema + RLS (Direkomendasikan)

Rancang tabel workspaces (tenant), workspace_members (keanggotaan + role), dan semua tabel data dengan kolom workspace_id. Terapkan RLS agar pengguna hanya dapat melihat data workspace mereka sendiri.

Optimasi Session Management & RLS

Untuk menghindari bottleneck performa, sematkan workspace_id dan role langsung ke dalam Session JWT pengguna (Custom Claims). Ini memungkinkan kebijakan RLS untuk memeriksa token sesi secara langsung (auth.jwt()->>'workspace_id') daripada melakukan join ke tabel workspace_members pada setiap query.

Fungsi Helper RLS

Buat fungsi user_has_role() yang dapat digunakan kembali di seluruh kebijakan RLS untuk memeriksa apakah pengguna saat ini memiliki role minimum yang diperlukan dalam workspace tertentu.

Strategi 2: Schema per Org

Untuk industri teratur yang membutuhkan pemisahan data ketat. Buat schema PostgreSQL terpisah untuk setiap tenant secara dinamis. Atur search_path per koneksi untuk mengarahkan query ke schema tenant yang benar.