Multi-tenant SaaS database schema design with data isolation

Design a Multi-Tenant Database Schema for:

Product: [SAAS PRODUCT NAME]
Description: [WHAT THE SYSTEM DOES]
Main entities: [LIST THE MAIN BUSINESS ENTITIES]
Database: [POSTGRESQL / MYSQL / SQL SERVER]
ORM: [PRISMA / TYPEORM / SEQUELIZE / SQLALCHEMY / NONE]
Expected tenants in 12 months: [NUMBER]
Expected tenants in 3 years: [NUMBER]
Data volume per tenant (estimate): [AVERAGE DB SIZE PER CLIENT]
Per-tenant customization needs: [YES — describe / NO]
Compliance requirements: [GDPR / CCPA / HIPAA / SOC2 / NONE]

## PART 1 — MULTI-TENANCY STRATEGIES: COMPARISON

### Strategy A — Database per Tenant (Isolated Database)

```
Tenant A → database_a (PostgreSQL)
Tenant B → database_b (PostgreSQL)
Tenant C → database_c (PostgreSQL)
```

**Pros:**
- Maximum isolation: data leakage between tenants is impossible
- Independent per-tenant backup and restore
- Per-tenant schema migration
- Simple compliance (GDPR right to erasure = drop database)
- Performance: no competition between tenants

**Cons:**
- High cost: database connection per tenant
- Multiplied maintenance operations (N schemas to migrate)
- Connection complexity (connection pooling is critical)
- Not viable for free tier with thousands of tenants

**Best for**: enterprise products with few high-paying tenants, highly sensitive data (healthcare, financial).

---

### Strategy B — Schema per Tenant (Schema Isolation)

```sql
-- PostgreSQL Schemas
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;
CREATE SCHEMA tenant_c;
-- Each schema has the same tables
```

**Pros:**
- Strong isolation (no WHERE tenant_id everywhere)
- One database to operate
- Per-schema backup possible in PostgreSQL
- Migrations per schema (Flyway, Liquibase)

**Cons:**
- Management complexity with N schemas
- More complex connection pooling
- Cross-tenant reports require UNION ALL
- Doesn't work well in MySQL (no real schemas)

**Best for**: PostgreSQL, medium number of tenants (dozens to few hundreds), sensitive data.

---

### Strategy C — Shared Table with tenant_id (Shared Everything)

```sql
CREATE TABLE units (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  unit_number VARCHAR(20) NOT NULL,
  ...
);
-- Row Level Security ensures isolation
CREATE POLICY tenant_isolation ON units
  USING (tenant_id = current_setting('app.current_tenant')::UUID);
```

**Pros:**
- Simple operations (1 database, 1 schema)
- Scales easily to thousands of tenants
- Native cross-tenant reporting
- Lower infrastructure cost
- Migrations done once

**Cons:**
- Risk of data leakage if you forget WHERE tenant_id
- Large tenant can impact performance of others (noisy neighbor)
- More complex compliance (GDPR requires precise deletes)
- Per-tenant customizations are harder

**Best for**: most SaaS products, scale from hundreds to millions of tenants, non-critical data.

---

## PART 2 — RECOMMENDATION FOR YOUR CONTEXT

**Recommended strategy**: [A / B / C / HYBRID] — justification based on the responses above.

**Hybrid strategy** (common in mature products):
- Free plan: Strategy C (shared, cheap to operate)
- Pro plan: Strategy B (isolated schema)
- Enterprise plan: Strategy A (dedicated database)

## PART 3 — COMPLETE SCHEMA (Strategy C recommended)

**Tenants table:**
```sql
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug VARCHAR(100) UNIQUE NOT NULL,   -- used in URL: app.com/SLUG
  name VARCHAR(255) NOT NULL,
  plan VARCHAR(50) NOT NULL DEFAULT 'free',  -- free, pro, enterprise
  status VARCHAR(50) NOT NULL DEFAULT 'active', -- active, suspended, cancelled
  settings JSONB DEFAULT '{}',         -- per-tenant customizations
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Pattern for all data tables:**
```sql
CREATE TABLE [entity] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  -- entity fields...
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ            -- soft delete required
);

-- Composite index: tenant_id FIRST (selectivity)
CREATE INDEX idx_[entity]_tenant ON [entity](tenant_id);
CREATE INDEX idx_[entity]_tenant_status ON [entity](tenant_id, status)
  WHERE deleted_at IS NULL;         -- partial index: active only
```

**Row Level Security (PostgreSQL):**
```sql
-- Enable RLS on table
ALTER TABLE units ENABLE ROW LEVEL SECURITY;

-- Policy: user only sees their tenant's data
CREATE POLICY tenant_isolation ON units
  AS PERMISSIVE FOR ALL
  USING (tenant_id = current_setting('app.current_tenant', TRUE)::UUID);

-- At start of each request, set the tenant:
SET LOCAL app.current_tenant = 'tenant-uuid-here';
```

**User schema (can belong to multiple tenants):**
```sql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,  -- email is global, not per-tenant!
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tenant_users (
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  role VARCHAR(50) NOT NULL,           -- admin, member, viewer
  status VARCHAR(50) DEFAULT 'active',
  invited_at TIMESTAMPTZ,
  joined_at TIMESTAMPTZ,
  PRIMARY KEY (tenant_id, user_id)
);
```

**Per-tenant customizations (dynamic fields):**
```sql
-- Option 1: JSONB column (simple, no type safety)
ALTER TABLE tenants ADD COLUMN metadata JSONB DEFAULT '{}';
-- Query: SELECT * FROM tenants WHERE metadata->>'primary_color' = '#FF0000'

-- Option 2: settings table (more structured)
CREATE TABLE tenant_settings (
  tenant_id UUID REFERENCES tenants(id),
  key VARCHAR(100) NOT NULL,
  value TEXT,
  PRIMARY KEY (tenant_id, key)
);
```

## PART 4 — PER-TENANT OPERATIONS

**Per-tenant backup:**
```sql
-- Export data for a specific tenant
COPY (SELECT * FROM units WHERE tenant_id = 'tenant-uuid')
TO '/backup/tenant-uuid/units.csv' CSV HEADER;
```

**Tenant deletion (GDPR/CCPA right to erasure):**
```sql
-- Soft delete first
UPDATE tenants SET status = 'deleted', deleted_at = NOW()
WHERE id = 'tenant-uuid';

-- Hard delete after legal period (90 days)
-- ON DELETE CASCADE handles child tables
DELETE FROM tenants WHERE id = 'tenant-uuid';
```

**Per-tenant health queries:**
```sql
-- Data volume per tenant (for billing and limits)
SELECT 
  t.name,
  COUNT(DISTINCT u.id) AS units,
  COUNT(DISTINCT r.id) AS residents,
  pg_size_pretty(SUM(pg_column_size(r.*))) AS data_size
FROM tenants t
LEFT JOIN units u ON u.tenant_id = t.id
LEFT JOIN residents r ON r.tenant_id = t.id
GROUP BY t.id, t.name;
```

## PART 5 — MULTI-TENANT SECURITY CHECKLIST

- [ ] RLS enabled on ALL tables with tenant data
- [ ] Automated tests: tenant A cannot see tenant B's data
- [ ] Application middleware: tenant_id injected into request context
- [ ] Logging: tenant_id present in all logs
- [ ] Composite indexes: tenant_id always as first column
- [ ] Soft delete on all tables (never hard delete in production)
- [ ] Per-tenant limits: implement quotas to prevent noisy neighbor
- [ ] Audit logging: log all sensitive actions with tenant_id and user_id

Format: strategy comparison, complete ready-to-use SQL schema, and security checklist.