Problem
You are building a B2B SaaS application where each customer (tenant) has their own isolated data. Design the database architecture that provides data isolation between tenants while remaining operationally manageable and cost-effective.
Requirements
- Tenant Isolation: One tenant must never be able to read or modify another tenant's data, even in the case of application bugs.
- Tenant Onboarding: New tenants should be provisionable within seconds, not hours.
- Shared Infrastructure: The solution should not require a separate database server per tenant (cost constraint for early-stage SaaS).
- Per-Tenant Customization: Tenants can have custom fields on core entities (e.g., a "customer" entity might have different custom fields per tenant).
- Cross-Tenant Queries: The platform admin needs to run aggregate queries across all tenants (e.g., total users, MRR breakdown).
- Tenant-Scoped Backups: Support restoring a single tenant's data without affecting other tenants.
Constraints
- The application uses PostgreSQL.
- Expected scale: 1,000 tenants, each with 10,000-500,000 rows in the largest table.
- Most queries are tenant-scoped (filter by tenant_id).
- The application must comply with data residency requirements (some tenants require EU-only storage).
- Performance should not degrade as more tenants are added.
What to Design
- The tenancy model (shared schema, schema-per-tenant, or database-per-tenant) and your rationale
- Row-level security implementation
- How custom fields are stored
- The indexing strategy for tenant-scoped queries
- How you handle data residency and tenant-scoped backups