Multi-tenant SaaS database schema design with data isolation

Design a Multi-Tenant Database Schema for:\\\\\\\\n\\\\\\\\nProduct: [SAAS PRODUCT NAME]\\\\\\\\nDescription: [WHAT THE SYSTEM DOES]\\\\\\\\nMain entities: [LIST THE MAIN BUSINESS ENTITIES]\\\\\\\\nDatabase: [POSTGRESQL / MYSQL / SQL SERVER]\\\\\\\\nORM: [PRISMA / TYPEORM / SEQUELIZE / SQLALCHEMY / NONE]\\\\\\\\nExpected tenants in 12 months: [NUMBER]\\\\\\\\nExpected tenants in 3 years: [NUMBER]\\\\\\\\nData volume per tenant (estimate): [AVERAGE DB SIZE PER CLIENT]\\\\\\\\nPer-tenant customization needs: [YES — describe / NO]\\\\\\\\nCompliance requirements: [GDPR / CCPA / HIPAA / SOC2 / NONE]\\\\\\\\n\\\\\\\\n## PART 1 — MULTI-TENANCY STRATEGIES: COMPARISON\\\\\\\\n\\\\\\\\n### Strategy A — Database per Tenant (Isolated Database)\\\\\\\\n\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\nTenant A → database_a (PostgreSQL)\\\\\\\\nTenant B → database_b (PostgreSQL)\\\\\\\\nTenant C → database_c (PostgreSQL)\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Pros:**\\\\\\\\n- Maximum isolation: data leakage between tenants is impossible\\\\\\\\n- Independent per-tenant backup and restore\\\\\\\\n- Per-tenant schema migration\\\\\\\\n- Simple compliance (GDPR right to erasure = drop database)\\\\\\\\n- Performance: no competition between tenants\\\\\\\\n\\\\\\\\n**Cons:**\\\\\\\\n- High cost: database connection per tenant\\\\\\\\n- Multiplied maintenance operations (N schemas to migrate)\\\\\\\\n- Connection complexity (connection pooling is critical)\\\\\\\\n- Not viable for free tier with thousands of tenants\\\\\\\\n\\\\\\\\n**Best for**: enterprise products with few high-paying tenants, highly sensitive data (healthcare, financial).\\\\\\\\n\\\\\\\\n---\\\\\\\\n\\\\\\\\n### Strategy B — Schema per Tenant (Schema Isolation)\\\\\\\\n\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\n-- PostgreSQL Schemas\\\\\\\\nCREATE SCHEMA tenant_a;\\\\\\\\nCREATE SCHEMA tenant_b;\\\\\\\\nCREATE SCHEMA tenant_c;\\\\\\\\n-- Each schema has the same tables\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Pros:**\\\\\\\\n- Strong isolation (no WHERE tenant_id everywhere)\\\\\\\\n- One database to operate\\\\\\\\n- Per-schema backup possible in PostgreSQL\\\\\\\\n- Migrations per schema (Flyway, Liquibase)\\\\\\\\n\\\\\\\\n**Cons:**\\\\\\\\n- Management complexity with N schemas\\\\\\\\n- More complex connection pooling\\\\\\\\n- Cross-tenant reports require UNION ALL\\\\\\\\n- Doesn't work well in MySQL (no real schemas)\\\\\\\\n\\\\\\\\n**Best for**: PostgreSQL, medium number of tenants (dozens to few hundreds), sensitive data.\\\\\\\\n\\\\\\\\n---\\\\\\\\n\\\\\\\\n### Strategy C — Shared Table with tenant_id (Shared Everything)\\\\\\\\n\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\nCREATE TABLE units (\\\\\\\\n  id UUID PRIMARY KEY,\\\\\\\\n  tenant_id UUID NOT NULL REFERENCES tenants(id),\\\\\\\\n  unit_number VARCHAR(20) NOT NULL,\\\\\\\\n  ...\\\\\\\\n);\\\\\\\\n-- Row Level Security ensures isolation\\\\\\\\nCREATE POLICY tenant_isolation ON units\\\\\\\\n  USING (tenant_id = current_setting('app.current_tenant')::UUID);\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Pros:**\\\\\\\\n- Simple operations (1 database, 1 schema)\\\\\\\\n- Scales easily to thousands of tenants\\\\\\\\n- Native cross-tenant reporting\\\\\\\\n- Lower infrastructure cost\\\\\\\\n- Migrations done once\\\\\\\\n\\\\\\\\n**Cons:**\\\\\\\\n- Risk of data leakage if you forget WHERE tenant_id\\\\\\\\n- Large tenant can impact performance of others (noisy neighbor)\\\\\\\\n- More complex compliance (GDPR requires precise deletes)\\\\\\\\n- Per-tenant customizations are harder\\\\\\\\n\\\\\\\\n**Best for**: most SaaS products, scale from hundreds to millions of tenants, non-critical data.\\\\\\\\n\\\\\\\\n---\\\\\\\\n\\\\\\\\n## PART 2 — RECOMMENDATION FOR YOUR CONTEXT\\\\\\\\n\\\\\\\\n**Recommended strategy**: [A / B / C / HYBRID] — justification based on the responses above.\\\\\\\\n\\\\\\\\n**Hybrid strategy** (common in mature products):\\\\\\\\n- Free plan: Strategy C (shared, cheap to operate)\\\\\\\\n- Pro plan: Strategy B (isolated schema)\\\\\\\\n- Enterprise plan: Strategy A (dedicated database)\\\\\\\\n\\\\\\\\n## PART 3 — COMPLETE SCHEMA (Strategy C recommended)\\\\\\\\n\\\\\\\\n**Tenants table:**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\nCREATE TABLE tenants (\\\\\\\\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\\\\\\\\n  slug VARCHAR(100) UNIQUE NOT NULL,   -- used in URL: app.com/SLUG\\\\\\\\n  name VARCHAR(255) NOT NULL,\\\\\\\\n  plan VARCHAR(50) NOT NULL DEFAULT 'free',  -- free, pro, enterprise\\\\\\\\n  status VARCHAR(50) NOT NULL DEFAULT 'active', -- active, suspended, cancelled\\\\\\\\n  settings JSONB DEFAULT '{}',         -- per-tenant customizations\\\\\\\\n  created_at TIMESTAMPTZ DEFAULT NOW(),\\\\\\\\n  updated_at TIMESTAMPTZ DEFAULT NOW()\\\\\\\\n);\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Pattern for all data tables:**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\nCREATE TABLE [entity] (\\\\\\\\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\\\\\\\\n  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,\\\\\\\\n  -- entity fields...\\\\\\\\n  created_at TIMESTAMPTZ DEFAULT NOW(),\\\\\\\\n  updated_at TIMESTAMPTZ DEFAULT NOW(),\\\\\\\\n  deleted_at TIMESTAMPTZ            -- soft delete required\\\\\\\\n);\\\\\\\\n\\\\\\\\n-- Composite index: tenant_id FIRST (selectivity)\\\\\\\\nCREATE INDEX idx_[entity]_tenant ON [entity](tenant_id);\\\\\\\\nCREATE INDEX idx_[entity]_tenant_status ON [entity](tenant_id, status)\\\\\\\\n  WHERE deleted_at IS NULL;         -- partial index: active only\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Row Level Security (PostgreSQL):**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\n-- Enable RLS on table\\\\\\\\nALTER TABLE units ENABLE ROW LEVEL SECURITY;\\\\\\\\n\\\\\\\\n-- Policy: user only sees their tenant's data\\\\\\\\nCREATE POLICY tenant_isolation ON units\\\\\\\\n  AS PERMISSIVE FOR ALL\\\\\\\\n  USING (tenant_id = current_setting('app.current_tenant', TRUE)::UUID);\\\\\\\\n\\\\\\\\n-- At start of each request, set the tenant:\\\\\\\\nSET LOCAL app.current_tenant = 'tenant-uuid-here';\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**User schema (can belong to multiple tenants):**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\nCREATE TABLE users (\\\\\\\\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\\\\\\\\n  email VARCHAR(255) UNIQUE NOT NULL,  -- email is global, not per-tenant!\\\\\\\\n  password_hash VARCHAR(255) NOT NULL,\\\\\\\\n  created_at TIMESTAMPTZ DEFAULT NOW()\\\\\\\\n);\\\\\\\\n\\\\\\\\nCREATE TABLE tenant_users (\\\\\\\\n  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,\\\\\\\\n  user_id UUID REFERENCES users(id) ON DELETE CASCADE,\\\\\\\\n  role VARCHAR(50) NOT NULL,           -- admin, member, viewer\\\\\\\\n  status VARCHAR(50) DEFAULT 'active',\\\\\\\\n  invited_at TIMESTAMPTZ,\\\\\\\\n  joined_at TIMESTAMPTZ,\\\\\\\\n  PRIMARY KEY (tenant_id, user_id)\\\\\\\\n);\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Per-tenant customizations (dynamic fields):**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\n-- Option 1: JSONB column (simple, no type safety)\\\\\\\\nALTER TABLE tenants ADD COLUMN metadata JSONB DEFAULT '{}';\\\\\\\\n-- Query: SELECT * FROM tenants WHERE metadata->>'primary_color' = '#FF0000'\\\\\\\\n\\\\\\\\n-- Option 2: settings table (more structured)\\\\\\\\nCREATE TABLE tenant_settings (\\\\\\\\n  tenant_id UUID REFERENCES tenants(id),\\\\\\\\n  key VARCHAR(100) NOT NULL,\\\\\\\\n  value TEXT,\\\\\\\\n  PRIMARY KEY (tenant_id, key)\\\\\\\\n);\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n## PART 4 — PER-TENANT OPERATIONS\\\\\\\\n\\\\\\\\n**Per-tenant backup:**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\n-- Export data for a specific tenant\\\\\\\\nCOPY (SELECT * FROM units WHERE tenant_id = 'tenant-uuid')\\\\\\\\nTO '/backup/tenant-uuid/units.csv' CSV HEADER;\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Tenant deletion (GDPR/CCPA right to erasure):**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\n-- Soft delete first\\\\\\\\nUPDATE tenants SET status = 'deleted', deleted_at = NOW()\\\\\\\\nWHERE id = 'tenant-uuid';\\\\\\\\n\\\\\\\\n-- Hard delete after legal period (90 days)\\\\\\\\n-- ON DELETE CASCADE handles child tables\\\\\\\\nDELETE FROM tenants WHERE id = 'tenant-uuid';\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n**Per-tenant health queries:**\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\n-- Data volume per tenant (for billing and limits)\\\\\\\\nSELECT \\\\\\\\n  t.name,\\\\\\\\n  COUNT(DISTINCT u.id) AS units,\\\\\\\\n  COUNT(DISTINCT r.id) AS residents,\\\\\\\\n  pg_size_pretty(SUM(pg_column_size(r.*))) AS data_size\\\\\\\\nFROM tenants t\\\\\\\\nLEFT JOIN units u ON u.tenant_id = t.id\\\\\\\\nLEFT JOIN residents r ON r.tenant_id = t.id\\\\\\\\nGROUP BY t.id, t.name;\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\n\\\\\\\\n## PART 5 — MULTI-TENANT SECURITY CHECKLIST\\\\\\\\n\\\\\\\\n- [ ] RLS enabled on ALL tables with tenant data\\\\\\\\n- [ ] Automated tests: tenant A cannot see tenant B's data\\\\\\\\n- [ ] Application middleware: tenant_id injected into request context\\\\\\\\n- [ ] Logging: tenant_id present in all logs\\\\\\\\n- [ ] Composite indexes: tenant_id always as first column\\\\\\\\n- [ ] Soft delete on all tables (never hard delete in production)\\\\\\\\n- [ ] Per-tenant limits: implement quotas to prevent noisy neighbor\\\\\\\\n- [ ] Audit logging: log all sensitive actions with tenant_id and user_id\\\\\\\\n\\\\\\\\nFormat: strategy comparison, complete ready-to-use SQL schema, and security checklist.