Multi-Tenancy

If you’re building an ERP product rather than implementing one, tenancy is your first and most consequential architectural decision. It is very hard to change later.

The models

1. Database per tenant

tenant_acme     → database acme_db
tenant_meridian → database meridian_db


Isolation	Excellent — a bad query affects one tenant
Data residency	Easy — put the DB in the required region
Backup/restore per tenant	Trivial
Customisation per tenant	Possible, including schema differences
Cost	High — connection pools, backups, monitoring × N
Operations	Migrations must run N times; N failure modes
Cross-tenant analytics	Painful
Practical ceiling	Hundreds, not thousands

Best for: large enterprise customers, strict data residency, high-value low-volume.

2. Schema per tenant

Database: erp_prod
  Schema acme.journal_line
  Schema meridian.journal_line

Middle ground. Shared connection pool, per-tenant logical isolation. PostgreSQL handles this well up to a few thousand schemas; beyond that, catalog operations and pg_dump get slow. Migration is still N-times but within one database.

3. Shared schema with a tenant column

CREATE TABLE journal_line (
  tenant_id BIGINT NOT NULL,
  ...
  PRIMARY KEY (tenant_id, id)
);


Cost	Lowest — one DB, one migration
Scale	Thousands to millions of tenants
Isolation	Weakest — one missing WHERE clause leaks another tenant’s ledger
Noisy neighbour	Real
Data residency	Hard
Per-tenant restore	Hard

Best for: SMB SaaS, high tenant counts, low per-tenant revenue.

4. Hybrid (what most successful products do)

Small tenants  → shared schema, pooled
Large tenants  → dedicated schema or database
Regulated/regional → dedicated database in-region

Design for this from day one by keeping tenant_id on every table and keeping the connection routing configurable. Then moving a tenant to its own database is a data migration, not a rewrite.

Getting isolation right in a shared schema

The tenant filter must be impossible to forget. Three layers of defence:

1. Row-level security (the strongest)

ALTER TABLE journal_line ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON journal_line
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

Set app.tenant_id once when the connection is checked out. The database enforces isolation even if application code forgets. Also enable FORCE ROW LEVEL SECURITY so the table owner isn’t exempt.

2. Repository/ORM enforcement

Every query goes through a data-access layer that injects the filter. No raw SQL outside it.

3. Tests

An automated test that runs every query path as tenant A and asserts zero rows from tenant B. Plus a static check that no SQL string in the codebase touches a tenant table without the filter.

Belt and braces. A cross-tenant data leak in an ERP means one company seeing another’s financials. It is a company-ending incident.

The tenant key in every index

-- Wrong: index doesn't lead with tenant
CREATE INDEX ON journal_line (posting_date, account_id);

-- Right
CREATE INDEX ON journal_line (tenant_id, posting_date, account_id);

Every index, every primary key, every foreign key leads with tenant_id. SAP did this with the MANDT client field in 1992 and it’s still the correct pattern.

Configuration and customisation per tenant

The hardest part of multi-tenant ERP: tenants need genuinely different behaviour.

Level 1  Settings          key-value per tenant                  easy
Level 2  Configuration     document types, workflows, mappings   the ERP way
Level 3  Custom fields     extra columns per tenant              needs design
Level 4  Custom logic      tenant-specific rules                 dangerous
Level 5  Custom code       tenant-specific deployments           you've lost multi-tenancy

Custom fields in a shared schema

Approach	Notes
Reserved columns (`custom_1..custom_20`)	Fast, indexable, ugly, finite
JSONB column	Flexible, indexable with GIN, no schema migration. The pragmatic default on PostgreSQL
EAV side table	Maximum flexibility, terrible query performance
Per-tenant added columns	Breaks the shared-schema model

Recommended: a custom_fields JSONB column plus a metadata table describing each tenant’s fields (label, type, required, validation, display order). The metadata drives UI rendering, API validation, and reporting.

ALTER TABLE sales_order ADD COLUMN custom_fields JSONB DEFAULT '{}';
CREATE INDEX ON sales_order USING GIN (custom_fields);

CREATE TABLE tenant_field_definition (
  tenant_id, entity, field_key, label, data_type,
  required, validation_regex, options JSONB, display_order,
  PRIMARY KEY (tenant_id, entity, field_key)
);

Custom logic

Never per-tenant if statements in core code — that path leads to an unmaintainable codebase within two years.

Instead:

Configuration-driven rules — a rules table the tenant edits
Sandboxed scripts — tenant-authored logic in an isolated runtime with CPU/memory/time limits and no network or filesystem access
Webhooks — call the tenant’s own service at defined extension points, with a timeout and a defined failure behaviour
Plugin/app model — versioned, reviewed extensions installed per tenant

Noisy neighbours

One tenant running a 40-million-row report should not slow everyone down.

Per-tenant rate limits on API and report execution
Query timeouts, enforced at the database
Resource groups / workload management where the DB supports it
Separate pools for interactive vs batch
Job queues with per-tenant concurrency caps
Move heavy tenants to dedicated infrastructure — the reason the hybrid model exists

Migrations across tenants

Shared schema: one migration, all tenants at once. Simple, but a bad migration hits everyone.

Per-tenant schemas: migrations run N times, which means:

Idempotency is mandatory
Progress tracking and resumability
A canary group first
Backward-compatible changes (expand → migrate → contract) so old and new code can both run during the rollout

Never take a lock on a billion-row table during a migration. Use ALTER TABLE ... ADD COLUMN with a default only where your DB does it without a rewrite; otherwise add nullable, backfill in batches, then add the constraint.

Tenant lifecycle

Provision → Configure → Seed master data → Go live → Operate → Suspend → Export → Delete

Each step needs to be automated and each has traps:

Step	Requirement
Provision	Create tenant, seed default config (chart of accounts template, document types, roles), first admin user
Seed	Import master data — the tenant’s real onboarding cost
Suspend	Read-only access, not deletion. Payment disputes get resolved
Export	A complete, machine-readable export of their data. This is a legal right in many jurisdictions and a contractual one in most B2B deals
Delete	Verifiable, including backups, within the contracted period

Build the export before you need it. A customer leaving and demanding their data on a deadline is not when you want to write it.

Data residency

tenant_id 1001 → region eu-west-1   (GDPR)
tenant_id 1002 → region ap-south-1  (India data localisation)
tenant_id 1003 → region us-east-1

Consequences:

A tenant’s data never leaves its region — including backups, logs, and analytics
Your control plane (tenant registry, billing, auth) may be global; the data plane is regional
Cross-region reporting for your own business must use aggregated, anonymised data
Support staff access must be region-aware

If you plan to sell into the EU, India, China, Saudi Arabia, or Russia, design the regional split before your first customer, not after.

Observability per tenant

Every log line, metric, and trace should carry the tenant ID:

log.info({ tenant_id, user_id, correlation_id, operation, duration_ms })

Without it you cannot answer “is this slow for everyone or just for them?” — which is the first question in every support ticket.

Decision guide

Situation	Model
<100 large enterprise customers	Database per tenant
100–5,000 mid-market	Schema per tenant, or shared with RLS
5,000+ SMB	Shared schema with RLS
Mixed sizes	Hybrid — design for it from the start
Strict data residency	Regional databases, always
Heavy per-tenant customisation	Schema or database per tenant

Rules

tenant_id first column of every table and every index. Always.
Row-level security as the backstop, not just application filtering.
Test for cross-tenant leakage automatically, in CI.
JSONB + metadata for custom fields, not EAV, not per-tenant columns.
No tenant-specific if statements in core code.
Per-tenant rate limits and query timeouts.
Build tenant export and delete early.
Tag every log and metric with the tenant.