Multi-tenant SaaS architecture

01Three tenancy models

Picture an office building. A shared schema is an open-plan floor: every tenant's rows sit in the same tables, separated by a tenant_id column. It is the cheapest and simplest to operate, with one migration for everyone and easy cross-tenant analytics, but isolation depends entirely on every query being scoped. Schema-per-tenant is a floor with separate rooms: each tenant gets its own set of tables in one database. Isolation is stronger and per-tenant restore is easier, but migrations must run across hundreds or thousands of schemas and connection pooling gets harder. Database-per-tenant is separate buildings: the strongest isolation, per-tenant performance tuning, location and encryption keys, and easy deletion, at the highest operating cost. Many products mix them: a shared schema for most tenants and dedicated databases for large or regulated customers.

• Shared schema + tenant_id: cheapest, scales to many small tenants, isolation via code and RLS.
• Schema per tenant: middle ground; migration fan-out is the main pain.
• Database per tenant: strongest isolation and data residency; highest cost and ops load.

02Enforcing isolation in layers

Isolation should never rely on developers remembering a WHERE clause. Resolve the tenant once per request from a trusted source (the authenticated token, an API key or the subdomain), never from a body field the client controls. Store it in request context and make the data layer enforce it: a repository base class, an ORM extension or global scope that adds the tenant filter and throws if it is missing. Add a second, independent layer in the database with PostgreSQL row-level security, so even a forgotten filter returns nothing. Make the default response for another tenant's ID a 404, not a 403, so attackers cannot discover which IDs exist. Then prove it with tests: every module gets a 'wrong tenant' test that tries to read and modify another tenant's records and expects failure.

1ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
2ALTER TABLE orders FORCE ROW LEVEL SECURITY;
3 
4CREATE POLICY tenant_isolation ON orders
5  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
6  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);
7 
8-- Per request, inside a transaction, using a non-owner app role:
9BEGIN;
10SET LOCAL app.tenant_id = '00000000-0000-0000-0000-000000000001';
11SELECT id, total FROM orders;  -- only this tenant's rows
12COMMIT;

03Plans, limits and feature flags

Tenants pay for different things, so model entitlements explicitly. A plan defines capabilities (modules such as loyalty or kitchen screens) and limits (branches, users, storage, API calls per minute). Check capabilities in one place, a policy or guard, rather than scattering 'if plan === pro' through the code. Enforce hard limits at write time (creating the eleventh branch fails with a clear message) and soft limits with warnings and usage dashboards. Feature flags are a separate tool: they turn functionality on gradually for beta tenants, enable kill switches and allow per-tenant overrides agreed in sales deals. Keep both auditable, because 'why can this tenant do that?' is a support question you will hear. Metering usage accurately also supports billing, so record it as events rather than recomputing it from live tables.

04Tenant-aware caching, queues and realtime

Every shared component is a place where tenants can leak into each other. Cache keys must include the tenant ID (tenant:42:menu:v3), or one café will see another's menu. Background jobs must carry the tenant ID in their payload, and the worker must restore tenant context before touching data, exactly as a request would. Realtime channels need the same care: subscribe clients to rooms per tenant and per branch, and authorise every subscription on the server. Files go under tenant-prefixed paths in object storage and are served through short-lived signed URLs. Logs and traces should include the tenant ID so support can investigate one customer without scanning everyone. Finally, rate limits and queue concurrency should be per tenant, so one tenant's import cannot starve the others.

• Cache, storage and channel names always include the tenant.
• Jobs restore tenant context before running.
• Per-tenant rate limits and fair queueing against noisy neighbours.

05Noisy neighbours, data export and deletion

In a shared system, one tenant running a huge report or import can slow everyone. Protect the others with per-tenant rate limits and quotas, statement timeouts on queries, separate queues or concurrency caps for heavy jobs, and scheduled heavy work outside peak hours. Watch per-tenant resource usage so you can spot the outlier, and move very large tenants to dedicated resources when needed; that is one reason to keep the data layer able to route a tenant to a different database. Tenants also own their data. Offer a complete export in documented formats (CSV, JSON), and implement deletion as a real process: soft-delete for a grace period, then hard-delete across the database, files, caches and search indexes, with backups expiring on a known schedule. Document this for GDPR-style requests.

06Offline-capable clients and sync

Point-of-sale terminals, field apps and kiosks must keep working when the internet drops. The pattern: the client or a local hub keeps a local database (often SQLite) with the data it needs, records every change as a command with a unique ID, and pushes those commands to the server when the connection returns. The server applies each command idempotently, deduplicating by command ID and device, so retries after flaky reconnects are safe. Decide conflict rules per entity: append-only records such as sales rarely conflict; shared records such as stock or prices need a clear owner, server-wins rules or explicit merge. Version the local schema and migrations carefully, because devices update at different times. Give the offline device only the permissions and data its branch needs, and use device-bound tokens that can be revoked.

07Row-level security in production: pooling, performance and roles

SET LOCAL cannot take bind parameters, so many apps build the statement as a string. A safer option is set_config('app.tenant_id', $1, true), where true means 'local to this transaction', which works with parameters and with transaction-mode poolers. Use the missing_ok form of current_setting in policies so an unset value yields NULL, which matches no rows, instead of an error. Performance: policies add a predicate to every query, so put tenant_id first in composite indexes (tenant_id, created_at) and in unique constraints (tenant_id, slug). Functions called inside policies should be simple and STABLE; complex subqueries per row are slow. Use three roles: a migration role that owns the tables, an application role without BYPASSRLS, and a separate, audited maintenance role for genuine cross-tenant jobs.

1-- Inside each request transaction (works with bind parameters and poolers)
2SELECT set_config('app.tenant_id', $1, true);
3 
4-- Indexes and constraints lead with the tenant
5CREATE INDEX orders_tenant_created_idx ON orders (tenant_id, created_at DESC);
6ALTER TABLE products ADD CONSTRAINT products_tenant_sku_key UNIQUE (tenant_id, sku);
7 
8-- App role cannot bypass policies
9CREATE ROLE app_rw LOGIN NOBYPASSRLS;
10GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;

08Routing tenants: tenant directory, cells and placement

Keep a tenant directory, a small, highly available table or service that maps each tenant to where its data lives: shared cluster A, dedicated database B, region EU. Every request resolves the tenant, then its placement, and connects to the right pool. This one indirection makes later moves possible: migrate a large tenant to its own database by copying data, replaying changes, briefly freezing writes and flipping the directory entry. Taken further, a cell-based architecture runs several identical stacks (app, database, cache, queues), each serving a subset of tenants. A bad deploy or overload then affects one cell, not every customer, and you roll out changes cell by cell. The cost is more infrastructure and tooling for cross-cell operations such as global admin search and billing, so most products start with one cell and the directory ready for more.

09Migrations and schema changes across many tenants

In a shared schema, one migration touches every tenant's data at once, so it must be online: add columns as nullable or with constant defaults, create indexes CONCURRENTLY, backfill in small batches, then add constraints as NOT VALID and validate separately. Never lock a busy table for minutes. With schema-per-tenant or database-per-tenant, the problem becomes fan-out: thousands of migrations that can partially fail. Run them through a job runner with per-tenant status, retries and concurrency limits, keep the app compatible with both old and new schema during the rollout (expand, migrate, contract), and make it safe to stop halfway. Track schema version per tenant and refuse to serve a tenant whose version the running code does not support.

• Expand first, deploy code that handles both, contract later.
• CREATE INDEX CONCURRENTLY and batched backfills for big tables.
• Per-tenant migration status for schema- or database-per-tenant setups.

10Offline sync internals: ordering, clocks and conflicts

Device clocks drift, so wall-clock timestamps alone cannot order changes from several devices. Common approaches: let the server assign a monotonically increasing sequence when it accepts a command, and have clients pull 'everything after sequence N'; or use hybrid logical clocks, which combine physical time with a counter to stay close to real time while preserving causality. For conflicts, pick a rule per field: append-only events never conflict; last-writer-wins is simple but silently drops edits; field-level merge keeps non-overlapping changes; CRDTs merge automatically for counters, sets and text at the cost of complexity. Keep commands small and intention-based ('add 2 units to order 15') rather than full-state overwrites, which merge far better. Limit how long a device may stay offline before it must resync fully.