Multi-Tenancy: Choosing Your Isolation Strategy

Every SaaS product is a multi-tenant system. The question is how you isolate tenants' data. There are three patterns:

• Separate databases, Maximum isolation, maximum operational cost. Each tenant has its own database. Good for enterprise contracts with strict compliance requirements.
• Separate schemas, Medium isolation, medium cost. One database, one schema per tenant. Supported natively in PostgreSQL, complex in MySQL.
• Shared schema with tenant ID, Minimum isolation, minimum cost. All tenants share tables, every row has a tenant_id foreign key. Requires rock-solid query filtering.

For most B2B SaaS products starting out, shared schema with tenant ID is the right default. It's the simplest to operate and migrate from. You can move to schema or database isolation per tenant later if compliance requires it.

FastAPI Middleware for Tenant Resolution

In FastAPI, tenant resolution happens in middleware. Every request carries a tenant identifier, typically in the JWT, a subdomain header, or an API key prefix.

The middleware extracts the tenant ID and attaches it to the request state. Every downstream database call reads the tenant ID from request state and applies it as a filter.

The critical rule: never trust a tenant ID from the request body. Always resolve it from an authenticated source (JWT claim or API key lookup). A tenant ID in the body can be forged.

RBAC: Role-Based Access Control

SaaS products need roles. At minimum: admin (full access), manager (team management), and member (standard access).

We implement RBAC with a three-table pattern: users, roles, and user_roles. A user can have multiple roles. Each role has a set of permissions stored as a JSON array.

FastAPI dependencies check permissions on each route:

MySQL Connection Pooling

MySQL's default connection limit is 151. A FastAPI application running 4 workers can easily exhaust this with 40+ concurrent requests if each request opens a new connection.

SQLAlchemy's connection pool handles this. Key settings for production:

• pool_size: 5, connections kept open per worker
• max_overflow: 10, additional connections allowed under peak load
• pool_timeout: 30, seconds to wait for a connection before raising an error
• pool_recycle: 1800, recycle connections every 30 minutes to prevent stale connection errors

With 4 workers × (5 + 10) = 60 connections max, well within MySQL's default limit.

Lessons Learned

The biggest operational mistake we see in multi-tenant SaaS: missing tenant_id on database indexes. A query filtering by tenant_id without an index does a full table scan. At 1,000 tenants and 1M rows per table, that's catastrophic.

Index rule: every table has a composite index on (tenant_id, <primary_query_column>). Without exception.