Cheat sheet · Architecture
Multi-tenancy
The problem
A B2B invoicing service keeps 1 200 customer companies in one database. One forgotten `WHERE tenant_id` leaks invoices, one big customer's export slows everyone, and one migration can break them all at once. The fixes: isolation enforced by the database, a tenant taken only from the token, per-tenant quotas, a silo for the big tenant behind a routing catalog, per-tenant keys and migrations in waves.
What you learn
- Why a
tenant_idfilter in application code leaks, and how row-level security with a per-transaction tenant context stops it - Where the tenant must come from: the signed token, never a header or URL the client controls
- Noisy neighbours: per-tenant quotas, 429 with limit headers, heavy work in a queue
- Pool, silo and bridge, tenant routing, per-tenant keys and migrations rolled out in waves
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_ta(["Small tenant"])
n_tb(["Large tenant"])
n_api["Invoicing API"]
n_db[("Shared DB")]
n_exporter("Export worker")
n_catalog[("Tenant catalog")]
n_db900[("Silo DB")]
n_kms>"Key service"]
n_migrator("Migration runner")
n_ta --> n_api
n_tb --> n_api
n_api --> n_db
n_api -->|"async"| n_exporter
n_exporter --> n_db
n_api --> n_catalog
n_api --> n_db900
n_api --> n_kms
n_migrator --> n_db
n_migrator --> n_db900Mermaid: request sequence
sequenceDiagram
participant n_api as Invoicing API
participant n_db as Shared DB
participant n_tb as Large tenant
participant n_catalog as Tenant catalog
participant n_kms as Key service
participant n_migrator as Migration runner
Note over n_api,n_db: 1 200 companies, one database
n_api->>n_db: SELECT id, total FROM invoices
n_db-->>n_api: 3 rows
Note over n_api,n_db: One forgotten filter, 37 companies in one CSV
n_api->>n_db: SELECT id, tenant_id, total FROM invoices
n_db-->>n_api: 1840 rows
Note over n_api,n_db: Row-level security: the database filters
n_api->>n_db: ALTER TABLE invoices ENABLE ROW LEVEL SECURITY#59;
n_db-->>n_api: 312 rows (t_900 only)
Note over n_tb,n_api: The tenant came from a header the user controls
n_tb->>n_api: GET /v1/invoices?status=open HTTP/1.1
n_api-->>n_tb: HTTP/1.1 200 OK
Note over n_tb,n_api: Tenant from the signed token only
n_tb->>n_api: Bearer token, decoded claims
n_api-->>n_tb: tenant context = t_900 (from the token)
Note over n_api,n_db: One tenant's export slows everyone
n_api->>n_db: top tenants by DB time, last 5 min
Note over n_tb,n_api: Per-tenant quotas and an export queue
n_tb->>n_api: POST /v1/exports HTTP/1.1
n_api-->>n_tb: HTTP/1.1 429 Too Many Requests
Note over n_api,n_catalog: The biggest tenant gets its own database
n_api->>n_catalog: SELECT cell, db_host, state FROM tenant_routes
n_catalog-->>n_api: cell #124; db_host #124; state
Note over n_api,n_kms: One encryption key per tenant
n_api->>n_kms: POST /v1/keys/decrypt HTTP/1.1
n_kms-->>n_api: HTTP/1.1 200 OK
Note over n_migrator,n_db: Migrations in waves, not all at once
n_migrator->>n_db: migrate 0042_invoice_currencyD2
direction: right
n_ta: "Small tenant" {
shape: rectangle
}
n_tb: "Large tenant" {
shape: rectangle
}
n_api: "Invoicing API" {
shape: rectangle
}
n_db: "Shared DB" {
shape: cylinder
}
n_exporter: "Export worker" {
shape: rectangle
style.multiple: true
}
n_catalog: "Tenant catalog" {
shape: cylinder
}
n_db900: "Silo DB" {
shape: cylinder
}
n_kms: "Key service" {
shape: cloud
}
n_migrator: "Migration runner" {
shape: rectangle
style.multiple: true
}
n_ta -> n_api
n_tb -> n_api
n_api -> n_db
n_api -> n_exporter: "async"
n_exporter -> n_db
n_api -> n_catalog
n_api -> n_db900
n_api -> n_kms
n_migrator -> n_db
n_migrator -> n_db900PlantUML: request sequence
@startuml
participant "Invoicing API" as n_api
database "Shared DB" as n_db
participant "Large tenant" as n_tb
database "Tenant catalog" as n_catalog
boundary "Key service" as n_kms
participant "Migration runner" as n_migrator
note over n_api, n_db : 1 200 companies, one database
n_api -> n_db : SELECT id, total FROM invoices
n_db --> n_api : 3 rows
note over n_api, n_db : One forgotten filter, 37 companies in one CSV
n_api -> n_db : SELECT id, tenant_id, total FROM invoices
n_db --> n_api : 1840 rows
note over n_api, n_db : Row-level security: the database filters
n_api -> n_db : ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
n_db --> n_api : 312 rows (t_900 only)
note over n_tb, n_api : The tenant came from a header the user controls
n_tb -> n_api : GET /v1/invoices?status=open HTTP/1.1
n_api --> n_tb : HTTP/1.1 200 OK
note over n_tb, n_api : Tenant from the signed token only
n_tb -> n_api : Bearer token, decoded claims
n_api --> n_tb : tenant context = t_900 (from the token)
note over n_api, n_db : One tenant's export slows everyone
n_api -> n_db : top tenants by DB time, last 5 min
note over n_tb, n_api : Per-tenant quotas and an export queue
n_tb -> n_api : POST /v1/exports HTTP/1.1
n_api --> n_tb : HTTP/1.1 429 Too Many Requests
note over n_api, n_catalog : The biggest tenant gets its own database
n_api -> n_catalog : SELECT cell, db_host, state FROM tenant_routes
n_catalog --> n_api : cell | db_host | state
note over n_api, n_kms : One encryption key per tenant
n_api -> n_kms : POST /v1/keys/decrypt HTTP/1.1
n_kms --> n_api : HTTP/1.1 200 OK
note over n_migrator, n_db : Migrations in waves, not all at once
n_migrator -> n_db : migrate 0042_invoice_currency
@endumlPlantUML: components
@startuml
left to right direction
agent "Small tenant" as n_ta
agent "Large tenant" as n_tb
component "Invoicing API" as n_api
database "Shared DB" as n_db
collections "Export worker" as n_exporter
database "Tenant catalog" as n_catalog
database "Silo DB" as n_db900
cloud "Key service" as n_kms
collections "Migration runner" as n_migrator
n_ta --> n_api
n_tb --> n_api
n_api --> n_db
n_api --> n_exporter : async
n_exporter --> n_db
n_api --> n_catalog
n_api --> n_db900
n_api --> n_kms
n_migrator --> n_db
n_migrator --> n_db900
@endumlRecap
Six rules for many customers in one system.
1) Do not trust every query to remember tenant_id: enforce it in the database with row-level security and SET LOCAL per transaction. 2) The tenant comes only from the signed token; any other source is input to verify, not to trust. 3) Quotas and queues per tenant keep one customer from taking everything. 4) Pool by default, silo for those who need or pay for it, a catalog that routes each tenant. 5) One key per tenant: a leak stays small, leaving is a key deletion. 6) Migrate in waves, with a canary and a version per tenant.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Rows leaked | 0 | 0 |
| API p99 | 120 ms | 140 ms |
| Hit by one change | 100% | 1% |
Steps
- Case 1 200 companies, one database
An invoicing SaaS sells to companies.
- Problem One forgotten filter, 37 companies in one CSV
A developer adds an "overdue invoices" report for t_900.
- Fix Row-level security: the database filters
A policy on the table adds
tenant_id = current tenantto every query, even one that forgets it. - Problem The tenant came from a header the user controls
The API took the tenant from an
X-Tenant-Idheader sent by the web app. - Fix Tenant from the signed token only
At login the identity service writes the tenant into the access token and signs it.
- Problem One tenant's export slows everyone
t_900, the biggest customer, starts an export of seven years of invoices, and then starts it again because it seemed slow.
- Fix Per-tenant quotas and an export queue
The API counts requests and running exports per tenant.
- Fix The biggest tenant gets its own database
t_900 is 30% of all data and pays for an isolated setup.
- Fix One encryption key per tenant
Every tenant gets its own data key, wrapped by a master key in the key service.
- Fix Migrations in waves, not all at once
Last quarter one
ALTER TABLElocked the shared table for 40 minutes, for all tenants. - Recap Recap
Six rules for many customers in one system.
Terms
- Row-level security
- A database policy that adds a condition such as the current tenant to every query on a table, so rows of other tenants stay invisible even when a query forgets the filter.
- Noisy neighbour
- One tenant in a shared system using so much CPU, disk or connections that every other tenant slows down.
- Pool, silo and bridge
- Tenant isolation models: all tenants share resources (pool), a tenant gets its own (silo), or a mix with shared code and separate data (bridge).