Multi-tenant isolation: three patterns, and why we default to one
Every CRM build has to answer this early, because retrofitting it is expensive. There are
three viable patterns and each is correct in a different situation.
Database per tenant. Strongest isolation, easiest compliance story, trivially simple
per-tenant backup and restore. Costs: migrations must be applied N times, connection
pooling gets awkward past a few hundred tenants, and cross-tenant reporting requires a
separate pipeline. Right answer when tenants are few, large, and regulated.
Schema per tenant. A middle ground that in practice inherits the migration pain of
the first pattern without inheriting all of its isolation benefits. We rarely choose it.
Shared schema with a tenant discriminator. One database, a TenantId on every table,
one migration. Operationally by far the simplest, and the only pattern where cross-tenant
analytics is a query rather than a project. The risk is obvious: one query that forgets
its filter is a data breach.
We default to the third, with the filter enforced where a developer cannot forget it — a
global query filter at the ORM level, driven by the tenant claim on the authenticated
principal. Queries that omit the filter return nothing rather than everything. Bypassing
it requires an explicitly named call that exists in exactly one place in the codebase, is
covered by tests that assert on cross-tenant leakage, and is what the audit log watches
hardest.
For a client with a hard regulatory isolation requirement, we use database-per-tenant and
accept the operational cost. The point is that this is a decision with a real trade-off,
not a default to be inherited from a tutorial.
The activity feed is a write-heavy denormalisation problem
The activity timeline is the most-read screen in any CRM and the most-written table. Every
email sync, stage change, call log, task completion and field edit produces a row, and the
volume outgrows the deal table by two orders of magnitude within a year.
Three decisions keep it fast:
Append-only. Activities are never updated or deleted. A corrected note is a new
activity superseding the old one. This removes update contention entirely, makes the table
safe to partition by time, and means the audit trail is the same object as the feed rather
than a parallel structure that can drift from it.
Denormalised at write. The actor’s name, the record’s title and the activity’s display
summary are written onto the activity row rather than joined at read time. Names change
rarely; timelines are read constantly. The trade — a background job that rewrites
denormalised names when a user is renamed — is worth it many times over.
Partitioned by month, with the hot partition indexed differently. Recent activity is
queried by record; historical activity is queried by date range for reporting. Those want
different indexes, and partitioning lets them have different ones.
The result is a timeline that opens in a few milliseconds on records with thousands of
activities, which matters because it is the screen a rep looks at before every call.
Permissions as a query filter, not a post-fetch check
The tempting implementation of “a rep sees only their own deals” is to fetch and then
filter. It is wrong for two reasons: the database does work that is thrown away, and
sooner or later some endpoint returns a count, an aggregate, or an export that was
computed before the filter was applied.
Instead, a user’s permission grants are resolved once per request into a filter expression
that is composed into every query against a protected entity. A rep’s query for deals is
physically different SQL from a manager’s. The consequences:
- Counts, sums and forecasts are correct by construction, because the aggregate never sees
rows the user cannot see.
- Pagination is correct — no more “page 3 is empty because everything on it was filtered
out after fetching”.
- Adding a new endpoint cannot accidentally omit the check, because the filter is attached
to the entity’s query root rather than to the endpoint.
Field-level permissions work the same way, projected at the query rather than nulled
afterwards. When a rep is not permitted to see margin, the margin column is not in the
SELECT — so it cannot leak through an API response, a CSV export, or a debug log.
Webhooks are a delivery problem
Every integration story starts with “we’ll fire a webhook” and gets interesting when the
receiver is down for an hour.
Webhook deliveries are persisted before they are attempted, retried with exponential
backoff, and moved to a dead-letter queue after a bounded number of failures — where they
are visible and manually replayable rather than lost. Each delivery carries a signature
and a monotonic sequence number per subscription, so a receiver can detect gaps and
reordering rather than assuming it saw everything.
This is unglamorous and it is the difference between an integration that works in a demo
and one that survives a year in production.