Multi-Tenancy Implementation Guide

Version: 1.0 Last Updated: 2025-10-25 Applies To: All versions (MVP.0+)

---

Overview

This guide provides implementation patterns for TVL's 3-tier multi-tenancy model:

• Organization (Org): Top-level tenant boundary
• Account: Sub-tenant/actor within org
• User: Person identity across orgs

Key Principle: All data scoped by org_id + account_id for complete isolation

---

Multi-Tenancy Architecture

3-Tier Model

┌─────────────────────────────────────┐
│         Organization (Org)           │  ← Tenant Boundary
│  id, name, slug, tier, settings     │
└──────────────┬──────────────────────┘
               │ 1:*
               ▼
┌─────────────────────────────────────┐
│           Account                    │  ← Actor/Sub-tenant
│  id, org_id, name, type             │
└──────────────┬──────────────────────┘
               │ *:*
               ▼
┌─────────────────────────────────────┐
│             User                     │  ← Person (global)
│  id, email, google_sub              │
└──────────────┬──────────────────────┘
               │ 1:*
               ▼
┌─────────────────────────────────────┐
│          Membership                  │  ← Access Grant
│  user_id, org_id, account_id, role  │
└─────────────────────────────────────┘

Design Decisions

Decision	Rationale	Trade-off
Shared database (logical isolation)	Simpler infrastructure, cost-effective for MVP	Requires strict RLS policies
org_id on ALL tables	Clean isolation, queryable	Slight storage overhead
account_id on actor-owned tables	Sub-tenant isolation	Must enforce NOT NULL
Default Account per Org	Simplifies single-owner use case	Unused for some orgs

---

Implementation Patterns

Pattern 1: Entity Scoping

Rule: Every actor-owned entity MUST include both org_id and account_id

-- CORRECT: Actor-owned table (e.g., spaces)
CREATE TABLE spaces (
  id UUID PRIMARY KEY,
  org_id UUID NOT NULL REFERENCES organizations(id),
  account_id UUID NOT NULL REFERENCES accounts(id),
  name VARCHAR(255) NOT NULL,
  -- ... other columns
  CONSTRAINT fk_account_in_org
    FOREIGN KEY (org_id, account_id)
    REFERENCES accounts(org_id, id)
);

-- INCORRECT: Missing account_id
CREATE TABLE spaces (
  id UUID PRIMARY KEY,
  org_id UUID NOT NULL REFERENCES organizations(id),
  name VARCHAR(255) NOT NULL
);

Entities requiring both:

• spaces, units, channel_targets, channel_listings
• bookings, payments, payouts
• media_assets, descriptions

Entities with org_id only:

• organizations, users, roles (global)
• memberships (scoped to org or account)

---

Pattern 2: Query Filtering

Rule: ALWAYS filter by org_id (and account_id if applicable) in WHERE clause

// CORRECT: Explicit org scoping
async function getSpaces(req) {
  const { org_id, account_id } = req.session;

  return db.query(
    `SELECT * FROM spaces
     WHERE org_id = $1
     AND account_id = $2
     AND status = 'active'`,
    [org_id, account_id]
  );
}

// INCORRECT: Missing org filter (data leak!)
async function getSpaces(req) {
  return db.query(
    `SELECT * FROM spaces
     WHERE status = 'active'`  // ❌ CROSS-TENANT LEAK
  );
}

---

Pattern 3: RLS Policy Structure

Rule: Enable RLS on all tables, create org and account policies

-- Enable RLS on table
ALTER TABLE spaces ENABLE ROW LEVEL SECURITY;

-- Policy 1: Org Isolation (blocking cross-tenant reads)
CREATE POLICY org_isolation ON spaces
  USING (org_id = current_setting('app.current_org_id')::uuid);

-- Policy 2: Account Access (within org)
CREATE POLICY account_access ON spaces
  USING (
    account_id = current_setting('app.current_account_id')::uuid
    OR EXISTS (
      SELECT 1 FROM memberships m
      WHERE m.user_id = current_setting('app.current_user_id')::uuid
        AND m.org_id = spaces.org_id
        AND m.account_id IS NULL  -- Org-wide membership
        AND m.status = 'active'
    )
  );

Setting session context:

async function setSessionContext(client, { org_id, account_id, user_id }) {
  await client.query(
    `SET LOCAL app.current_org_id = $1;
     SET LOCAL app.current_account_id = $2;
     SET LOCAL app.current_user_id = $3;`,
    [org_id, account_id, user_id]
  );
}

// Usage in transaction
await db.transaction(async (client) => {
  await setSessionContext(client, req.session);
  await client.query('SELECT * FROM spaces;');  // RLS filters applied
});

---

Pattern 4: Org-Wide vs Account-Scoped Access

// Check if user has org-wide access
async function hasOrgWideAccess(user_id, org_id) {
  const result = await db.queryOne(
    `SELECT COUNT(*) > 0 AS has_access
     FROM memberships
     WHERE user_id = $1
       AND org_id = $2
       AND account_id IS NULL  -- Org-wide
       AND status = 'active'`,
    [user_id, org_id]
  );
  return result.has_access;
}

// Get accessible accounts for user
async function getAccessibleAccounts(user_id, org_id) {
  const orgWide = await hasOrgWideAccess(user_id, org_id);

  if (orgWide) {
    // Return all accounts in org
    return db.query(
      `SELECT * FROM accounts WHERE org_id = $1 AND status = 'active'`,
      [org_id]
    );
  } else {
    // Return only explicitly granted accounts
    return db.query(
      `SELECT a.* FROM accounts a
       JOIN memberships m ON m.account_id = a.id
       WHERE m.user_id = $1
         AND m.org_id = $2
         AND m.status = 'active'`,
      [user_id, org_id]
    );
  }
}

---

Pattern 5: Default Account Usage

// Create organization with default account
async function createOrganization({ name, slug, creator_user_id }) {
  return db.transaction(async (client) => {
    // 1. Create org
    const org = await client.queryOne(
      `INSERT INTO organizations (name, slug, status)
       VALUES ($1, $2, 'active')
       RETURNING *`,
      [name, slug]
    );

    // 2. Create default account
    const account = await client.queryOne(
      `INSERT INTO accounts (org_id, name, type, is_default, status)
       VALUES ($1, $2, 'owner', true, 'active')
       RETURNING *`,
      [org.id, `${name} (Default)`]
    );

    // 3. Create admin membership for creator
    await client.query(
      `INSERT INTO memberships (org_id, account_id, user_id, role_id, status)
       SELECT $1, NULL, $2, r.id, 'active'
       FROM roles r WHERE r.name = 'admin'`,
      [org.id, creator_user_id]
    );

    return { org, account };
  });
}

---

Common Pitfalls

Pitfall 1: Forgetting account_id in Foreign Keys

-- ❌ WRONG: No account_id
ALTER TABLE units
  ADD CONSTRAINT fk_space
  FOREIGN KEY (space_id) REFERENCES spaces(id);

-- ✅ CORRECT: Composite FK ensures account match
ALTER TABLE units
  ADD CONSTRAINT fk_space_in_account
  FOREIGN KEY (org_id, account_id, space_id)
  REFERENCES spaces(org_id, account_id, id);

Pitfall 2: RLS Bypass in Queries

// ❌ WRONG: Using separate connection bypasses RLS
const adminClient = await db.connect();  // No session context
await adminClient.query('SELECT * FROM spaces');  // All orgs visible!

// ✅ CORRECT: Use same transaction with context
await db.transaction(async (client) => {
  await setSessionContext(client, req.session);
  await client.query('SELECT * FROM spaces');  // RLS enforced
});

Pitfall 3: Hardcoding Org IDs

// ❌ WRONG: Hardcoded org_id
const spaces = await db.query(
  `SELECT * FROM spaces WHERE org_id = '123e4567-e89b-12d3-a456-426614174000'`
);

// ✅ CORRECT: From session
const spaces = await db.query(
  `SELECT * FROM spaces WHERE org_id = $1`,
  [req.session.org_id]
);

---

Testing Multi-Tenancy

Test 1: Cross-Tenant Isolation

test('users cannot access other org data', async () => {
  const org1 = await createOrg('Org A');
  const org2 = await createOrg('Org B');
  const user1 = await createUser('user1@orga.com', org1.id);
  const user2 = await createUser('user2@orgb.com', org2.id);

  const space1 = await createSpace(org1.id, 'Villa A');

  // User 2 should NOT see Org 1's space
  await expectAccessDenied(
    () => getSpace(user2.id, space1.id)
  );
});

Test 2: Org-Wide vs Account Access

test('org-wide membership sees all accounts', async () => {
  const org = await createOrg('Org A');
  const account1 = await createAccount(org.id, 'Account 1');
  const account2 = await createAccount(org.id, 'Account 2');
  const userOrgWide = await createUser('admin@orga.com', org.id, { org_wide: true });

  const accounts = await getAccessibleAccounts(userOrgWide.id, org.id);
  expect(accounts).toHaveLength(3);  // Default + Account 1 + Account 2
});

---

Performance Optimization

Index Strategy

-- Always index org_id first for tenant filtering
CREATE INDEX idx_spaces_org_account ON spaces(org_id, account_id);
CREATE INDEX idx_units_org_account_space ON units(org_id, account_id, space_id);

-- Partial indexes for active records
CREATE INDEX idx_spaces_active
  ON spaces(org_id, account_id)
  WHERE status = 'active';

Query Performance

-- EXPLAIN ANALYZE with RLS to check plans
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM spaces
WHERE org_id = '...'
  AND account_id = '...';

-- Look for Index Scan (good) vs Seq Scan (bad)

---

Migration to Multi-Tenancy

If adding multi-tenancy to existing single-tenant system:

1. Add org_id column (nullable initially)
2. Create default organization
3. Backfill org_id for all existing data
4. Make org_id NOT NULL
5. Add account_id column (nullable)
6. Create default account per org
7. Backfill account_id
8. Make account_id NOT NULL
9. Enable RLS policies
10. Test thoroughly

---

Related Documents

• RLS Policy Patterns
• Identity & Tenancy Specification
• Authorization & Access Specification