Multi-tenant data isolation

One deployment serving many customers. The whole design question is where the boundary between them sits, because that choice sets your blast radius, your per-tenant cost, and how hard “delete this customer’s data” turns out to be.

The three models

Shared schema puts a tenant_id column on every table. One database, one schema, one connection pool. It is what most SaaS runs on, and the isolation is only as good as your query discipline.

Schema per tenant gives each tenant its own set of tables in one database. Backups and restores become per-tenant, and a bad query cannot cross the boundary. Migrations now run N times, and at a few thousand tenants the catalogue itself becomes a performance problem.

Database per tenant is full isolation, including noisy-neighbour and compliance isolation, at the cost of N databases to migrate, monitor and pay for. This is enterprise-plan territory.

The honest default is shared schema, with the ability to promote a large or regulated tenant to its own database.

The failure mode of shared schema

It is always the same bug: a query that forgot the filter.

# One missing clause and tenant A reads tenant B.
await session.execute(
    select(Invoice).where(Invoice.status == "paid")
)

This will happen. Code review does not reliably catch a missing where, and the damage is a cross-tenant data leak - the incident class that ends enterprise contracts.

So the design rule is: do not rely on every query being written correctly. Push the filter somewhere it cannot be omitted.

Enforcing it in the database

Postgres row-level security moves the check below the query, so a forgotten filter returns nothing rather than everything.

ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoice
  USING (tenant_id = current_setting(
           'app.tenant_id')::uuid);

The application sets the variable per transaction, and every statement in it is scoped automatically:

async with session.begin():
    await session.execute(
        text("SELECT set_config("
             "'app.tenant_id', :t, true)"),
        {"t": str(tenant_id)},
    )
    # every query below is now scoped

The true third argument makes it transaction-local, which is what makes this safe with a connection pool - the setting cannot leak into the next checkout of that connection.

Two things to know: RLS does not apply to the table owner unless you add FORCE ROW LEVEL SECURITY, and the policy expression is evaluated per row, so index tenant_id and expect a real planning cost.

Enforcing it in the application

If RLS is not available, the equivalent is a repository layer that no caller can bypass, taking the tenant from request context rather than an argument.

class TenantRepo:
    def __init__(self, s: AsyncSession, tenant: UUID):
        self._s, self._t = s, tenant

    def _scoped(self, model):
        return select(model).where(
            model.tenant_id == self._t
        )

This is weaker than RLS - it holds only while everyone uses the repository - but it is the reason “no SQL in the service layer” is a rule in multi-tenant codebases rather than a style preference.

Where tenancy comes from

The tenant must be derived from something the caller cannot choose freely. A tenant_id in the request body is a vulnerability; taken from a verified JWT claim or subdomain, it is a fact.

async def tenant_ctx(claims=Depends(verify_jwt)):
    return UUID(claims["tid"])

Then it belongs in a context variable, not threaded through every signature - so background tasks and repositories read the same value.

What leaks even with isolation

The boundary is rarely only the database. The common misses:

• Caches. A Redis key without the tenant in it serves one tenant’s data to another. Namespace every key: t:{tid}:user:{id}.
• Search indexes. Elasticsearch or a vector store needs a tenant filter too, and in vector search that filter must apply before the nearest neighbour scan, not after.
• Background jobs. A task takes an id and re-reads the row without a tenant context. Pass the tenant into the job payload.
• Object storage. Prefix by tenant and check the prefix on read.
• Connection pools. A per-tenant pool multiplied by tenants exhausts Postgres connections. Share the pool and scope per transaction.

The operational questions

The interview usually goes to the parts that hurt later:

• Noisy neighbours. One tenant’s report scan degrades everyone. Per-tenant rate limits and a statement timeout are the minimum.
• Per-tenant deletion. GDPR erasure in a shared schema is a delete across every table plus caches, backups and logs. In a database per tenant, it is dropping a database.
• Migrations. Shared schema migrates once; schema-per-tenant migrates N times and needs a strategy for partial failure.
• Per-tenant cost. You cannot price a plan without attributing usage, which means tagging queries and storage by tenant from the start.

Related

• Repository and Unit of Work Patterns - the layer that enforces scoping
• Multi-Tenant SSO - identity across tenants
• Authorization: RBAC, ABAC and where to enforce them - permissions within a tenant