Multitenancy System Design

Selling the same application to many organisations has three possible shapes. A database per tenant is the safest and the most expensive to operate: a hundred customers means a hundred migrations. A schema per tenant is cheaper but still multiplies the operational surface. A shared schema with a tenant column is the cheapest and the most dangerous, because the entire isolation guarantee is one predicate that a developer can forget.

Grit takes the third shape and spends its effort on making the predicate impossible to forget rather than on hoping. That is the only defensible version of the cheap option.

The second problem is the one nobody plans for: an operator needs to see a customer’s data to answer a support ticket. Doing that by turning off the predicate is how a support tool becomes the breach. Impersonation has to be a thing the system knows it is doing.

The system has to be able to:

• Resolve which organisation a request belongs to, from the identity rather than from the request.
• Narrow every query on a tenant-owned table, in the layer that builds queries.
• Stamp the tenant on creation and refuse to let it be changed afterwards.
• Let a user belong to more than one organisation and switch between them explicitly.
• Let an operator act inside a tenant deliberately, with that fact recorded.
• Combine with per-user ownership, so "my invoices inside my organisation" is expressible.

Functional requirements

• An embedded tenant.Owned on models that belong to an organisation, which is what marks them.
• Tenant resolution in middleware, from the membership on the identity.
• Query scoping applied in the service, alongside and independent of owner scoping.
• Membership records joining users to organisations, with a role per membership.
• An explicit organisation switch, which changes the tenant on subsequent requests.
• Operator impersonation of a tenant, time-boxed and audited.

Non-functional requirements

• Never from the request. the tenant is derived from the authenticated membership. A header or a body field naming an organisation is ignored, because otherwise it is an access control bypass with a nice name.
• Fails closed. a request whose tenant cannot be resolved matches nothing. There is no state in which an unresolved tenant means "all of them".
• Immutable after creation. the tenant column is not writable through update or patch. Moving a row between organisations is an explicit operation, not a field edit.
• Opt-in per model. a shared reference table scoped by accident becomes invisible to every query, with no error to explain it. Marking is deliberate.
• Impersonation is visible. an operator inside a tenant is a recorded fact with a beginning and an end, not an invisible capability.

Numbers for a mid-sized deployment. They are here to size the thing, not to predict your traffic: change an assumption and the sums below move with it.

Assumptions

Tenant scoping sits beside owner scoping rather than replacing it. A row can be narrowed by organisation, by user, or by both.

Core components

• tenant.Owned. an embedded struct carrying the organisation id. A model that embeds it is tenant-scoped; one that does not is shared.
• Tenant resolver. middleware that puts the current organisation on the context, from the membership on the identity and the user’s active selection.
• Tenant scope. the query predicate, applied in the service. Composes with owner scoping rather than competing with it.
• Membership store. who belongs to which organisation and in what role. One user may have several.
• Impersonation. an operator-initiated, time-boxed entry into a tenant, which appears in the audit log of that tenant.

Request flow

1. 1The request arrives with an access token. Nothing in it names an organisation, and if it did it would be ignored.
2. 2Authentication produces the identity. The tenant is not yet known.
3. 3The resolver reads the user’s memberships and their active selection, and puts the organisation on the context. A user with no membership resolves to nothing.
4. 4The handler calls the service exactly as it would in a single-tenant project. It does not mention the organisation.
5. 5The service applies both scopes. The tenant predicate comes from the context; the owner predicate, if the resource is also user-owned, comes from the same place.
6. 6The narrowed query runs. An unresolved tenant produces a predicate that matches nothing rather than one that is omitted.
7. 7Writes are recorded in the tenant’s audit log, including the fact that an operator was impersonating, when that is the case.

Data flow

• The organisation id is written at creation from the context and is not in the writable column set afterwards.
• Shared reference data, such as a currency table, is deliberately not tenant-scoped. Marking it would make it invisible to everybody.
• An impersonating operator carries both identities: their own, for the audit record, and the tenant, for the scope.
• Switching organisation is an explicit call that changes the active membership. It does not change any row.

Where it lives

• One database, one schema. The cost of that choice is paid in discipline about the predicate, and the benefit is one migration rather than five thousand.
• Every index on a tenant-owned table leads with the tenant column. An index that does not is useless to a scoped query.
• Keyset pagination rather than offset, because the largest tenant would otherwise pay a growing cost for each page.

Core types

Design principles applied

• Opt in, never infer. a model is tenant-scoped because somebody said so. Inferring it from a column name would silently scope a shared table and make it vanish.
• Compose, do not replace. tenant scope and owner scope are separate predicates that both apply. Collapsing them into one would make "my rows in my organisation" inexpressible.
• The dangerous path is explicit. crossing a tenant boundary is impersonation, which has a name, a duration and a log entry, rather than a flag that disables a predicate.

Patterns

• The tenant predicate improves selectivity, so a correctly indexed multitenant query is faster than the unscoped equivalent.
• Tenant skew is the thing to watch. The largest tenant can be a tenth of the table, and a plan that suits the median can be a scan for them.
• Keyset pagination keeps the cost per page constant regardless of tenant size, which offset pagination does not.
• A single very large tenant can be moved to its own database later without changing application code, because the predicate is already there.
• Memberships are small and cached per user. Resolving a tenant does not query on the hot path.

What breaks first

• The forgotten predicate. a hand-written query that does not go through the service sees every tenant. This is the entire risk of the shared-schema model, concentrated in one failure.
• Tenant skew. one customer with a tenth of the rows experiences a different application from everybody else, and they are usually the customer who notices.
• Noisy neighbours. a heavy report run by one tenant consumes connections and cache that every other tenant shares.
• Impersonation drift. a support tool that can enter any tenant without expiry or record becomes the softest way into every customer’s data.
• Untested combinations. tenant scoping combined with tree structures or public endpoints is where the predicate is most likely to be dropped, and least likely to be covered.

What to do about it

• Test the breach, not the feature. a fixture with two tenants, asserting that each cannot see the other, verified to fail when the predicate is removed.
• Index leading with the tenant. every composite index starts with the tenant column, so the largest tenant gets the same plan as the smallest.
• Per-tenant limits. rate limiting keyed by organisation as well as by address, so one tenant cannot consume the shared capacity.
• Time-box and record impersonation. a session with an expiry and an entry in the tenant’s own audit log, so the customer can see it happened.