Multi-tenant Data Isolation: Adding tenantId Is Only the First Layer

We build an AI agent platform for grassroots public health: an admin console, a multi-tenant permission system, five business modules and an agent bridge. The users are several districts/counties, and each of them has departments and communities underneath.

In phase two we went back through data isolation properly, and found that "we put tenantId on every table" — solves only half the problem.

1. Two layers, not one

When most people say "multi-tenant isolation" they mean the first layer:

Layer 1: tenant isolation — district A's data must be invisible to district B.

The standard approach: every business table carries tenantId, and every query forces that condition.

Real business has a second layer, and it is the one that breaks more often:

Layer 2: data scope inside a tenant — within one district, a community doctor should not see the neighbouring community's data.

Why layer 2 leaks more

Because layer 1 has a natural guard: tenantId comes from the session, and if you forget it in a query you usually catch it during testing (tenant A logging in and seeing tenant B's data is glaringly obvious).

Layer 2 is different. It depends on the current user's role; the same endpoint has to return different subsets for different roles. Forget it and nothing errors — you just quietly return a little extra. And the extra is exactly what someone should not see.

We turned both layers into product-level invariants:

Invariant 1  Business data is isolated by tenantId; queries must not leak across tenants
Invariant 2  Data scope (TENANT_ALL / DEPARTMENT / COMMUNITY / OWNER / CUSTOM)
             must be applied to list *and* detail responses

The words "list and detail" in the second invariant came at a price.

2. Five levels of data scope

Layer 2 lands on a model called DataScope, which reduces "how much can this person see" to five levels:

Roles and data scope are decoupled: the role decides what actions are permitted (permission points), the data scope decides how much data is visible (the subset). A binding relation connects the two.

User ── role binding ──┬── role (action permissions)
                       └── data scope (data boundary)

The value of the design is that "what you may do" and "what you may see" are two orthogonal dimensions. A community doctor and a department head may have identical action permissions while their visible data differs by an order of magnitude. Merge the two and the number of roles explodes.

3. The three places that leak the most

Leak 1: the detail endpoint has no scope filter

This is the first one we hit.

The list endpoint dutifully applied data-scope filtering, so users only saw the rows they should. But the detail endpoint looked the record up by ID directly — bypassing the list filter entirely. Know the ID and you can fetch data outside your scope.

List    GET /residents        → scope where clause  → only rows in scope
Detail  GET /residents/{id}   → direct lookup by ID → out-of-scope rows returned ⚠️

That is why the invariant says "must be applied to list and detail". It is not describing an implementation; it is a reminder to whoever comes next — these are two separate places you have to apply it, and nothing inherits it for you.

The fix is direct: make scope filtering a service-layer capability (DataScopeService) and require every path that reads data to go through it, instead of everyone hand-rolling where clauses. The more people hand-roll where clauses, the more certain it is that one path gets forgotten.

Leak 2: a new table forgets tenantId

The signature of this leak is that nothing breaks at the time, and it explodes six months later.

Add a business table, forget tenantId, and everything works — until some query filters by tenant and finds there is no column to filter on.

We made it a structural constraint rather than relying on code-review memory:

Hard boundary: every new business table must carry tenantId
               (and reserve the three ownership columns: department / community / owner)
Enforcement:   Prisma schema review, checked in CI

Note the second half — "reserve the three ownership columns". The secondary scope may be partitioned by department, community or owner, and if you do not reserve the columns when the table is created, adding them later means altering the table and backfilling data.

Our domain documentation has a table listing, item by item, which invariant each data object falls under:

ResidentRecord            → invariant 1
ResidentServiceTask       → invariant 1
DataScope                 → invariant 2
RoleBinding               → invariant 2
OpenClawScopeMapping      → invariant 4 (agent permission mapping)

The benefit: when you add an object you must answer "which invariant does this fall under". If you cannot answer, the design is not finished.

Leak 3: no allow-list on the query conditions themselves

Strictly speaking this is not an isolation problem, but it shares the same root: do not let the client decide what gets queried.

Our resident query accepts client-supplied filter conditions (field + operator + value). Without field validation, a client can pass any field name — including fields that should not be externally filterable, or fields that leak the shape of the data.

The fix is a field allow-list plus an operator allow-list, rejecting anything outside them, and rejecting before any database query is triggered:

WHEN    filter.conditions[].field = "__evil__" is submitted
THEN    return 400 and trigger no database query at all

The second half matters: it is not "run the query, notice the field is invalid, then error". It must not query at all. Validation has to happen before the query, otherwise the validation itself becomes a probing endpoint.

4. When an agent also reads data, one more layer

The platform includes an agent engine for conversation and task orchestration. It needs to read business data, but it must not connect to the business database directly.

So it gets a dedicated channel, and the signing keys on that channel are isolated per tenant:

Each tenant can configure a dedicated signing key (injected via environment variables)
Tenants without one fall back to the global key
Production without a global key → fail closed, reject outright

Why per-tenant keys? With one shared key, any single leak means every tenant's channel can be forged. Split per tenant and the blast radius is one tenant.

One engineering detail deserves its own paragraph: the verifying side does not look the key up in the database. It takes the tenant identifier from the request header and reads the environment variable directly. The reason: if verification had to query the database first, the verification path itself becomes a database entry point and isolation gains another gap. If a check can be done statelessly, do not introduce a data dependency into it.

5. Four judgments

Out of this pass we distilled four, independent of technology stack:

One — multi-tenancy has two layers.

Tenant isolation (horizontal) and in-tenant data scope (vertical) are different problems. The second leaks more often, because it does not error; it just quietly returns a little extra.

Two — data scope must be applied to list and detail separately.

Do not assume "the list is filtered, so the detail is safe". Detail is usually a direct lookup by ID — an independent path. Make it a shared service-layer capability instead of relying on everyone's diligence.

Three — turn isolation requirements into structural constraints, not review memory.

New tables must carry tenantId, must reserve ownership columns. Put requirements like these into schema checks, not documents. Documents get forgotten; CI does not.

Four — every "query intent" coming from a client goes through an allow-list.

Fields, operators, sort direction — anything a client can specify must be enumerable. What you cannot enumerate is attack surface.

One sentence

Adding tenantId keeps the company next door out. Keeping the department next door out — inside your own company — takes something else.

And the second one is usually where real business breaks first, because it does not raise an error.

Author: A Liang · Chada Software — this is part 2 of the "Building AI Agents in Production" series. Next: agent action auditing — how to record everything an AI did, what to record, and who gets to see it. Happy to compare notes if you are working on something similar.