Picture a dispatcher at a security firm opening the incident list and seeing a break-in at a site their firm has never guarded. It belongs to a competitor. Nobody hacked anything: a developer added a query last week and forgot one where firm_id = ?. The competitor's guards, sites and incident reports are on the wrong screen, and the firm that owns them has a data breach to report.
That is the failure OmniGuard is built to make impossible. It's an operating system for private security firms (control room, incidents, SOS, patrol rounds, rosters) that began as a tool for one firm and had to become a product many firms could lease. One firm must never see another's data: not through a missed filter, not through a support session, not through a backup restored to the wrong place.
This post is how that works in NestJS, from the decision down to the code. If you know Nest but have never touched AsyncLocalStorage, or you know Postgres but not advisory locks, each idea gets a short explanation and a link to its documentation before it's used.
The code below is simplified from OmniGuard: names shortened, logging and most error handling trimmed, the shape kept.
Three ways to keep tenants apart
A tenant is one customer of a shared product. Here it's a security firm. There are three common ways to keep tenants' data apart in Postgres, and they trade safety for operational effort in opposite directions.
Concern | Shared tables | Schema per tenant | Database per tenant |
|---|---|---|---|
How | One set of tables, a | One Postgres schema per firm inside one database | One whole database per firm |
What a missed filter costs | Another firm's data in the response | Usually nothing, if the search path is set right | Nothing: the other data isn't reachable |
Backup and restore of one firm | Hard: rows are mixed with everyone else's | Possible, per schema | Easy: dump or restore one database |
Connections | One pool for everyone | One pool, switch schema per request | A pool per firm |
Migrations | Run once | Run once per schema | Run once per database |
The shared-table approach is the most common, and it works until someone forgets the filter once. Postgres has a safety net for it, row-level security, which makes the database apply the filter itself. It's a good option, but it is still one policy away from a leak, and the policy has to be right on every table.
OmniGuard holds life-safety data, falls under European privacy law, and has to honour erasure requests even after a backup is restored. I wanted isolation that doesn't depend on remembering anything, so every firm gets its own Postgres database. A separate platform database holds what belongs to no single firm: the directory of firms, platform staff accounts, the platform's own audit trail, and each firm's connection string, encrypted. Each firm's audit log lives in its own database.
Postgres draws a strong line between databases. A connection is opened against one database, and ordinary SQL from it can't reach another one. A query in one firm's database that names another firm's table fails before it runs:
select * from firm_b.public.incidents;
ERROR: cross-database references are not implemented: "firm_b.public.incidents"That error is asserted in an integration test the codebase calls "the most important test in the repository": two firms, two databases, and a context resolved for one reaches nothing of the other.
That line only holds for the connections the application actually uses, so who those connections log in as is half of the isolation. Postgres roles belong to the whole server, not to one database, and by default every role may connect to every database, because Postgres grants CONNECT to PUBLIC. A separate login per firm protects nothing until that default is taken away. Provisioning does it explicitly:
// Postgres grants CONNECT to PUBLIC on every new database. Revoking it is the half that matters.
await admin.query(`revoke connect on database ${database} from public`);
await admin.query(`grant connect on database ${database} to ${role}`);
await admin.query(`grant usage, create on schema public to ${role}`); // migrations run as this roleEach firm gets its own role, an ordinary login with no superuser or create-database rights. The firm's migrations run as that role, so it owns its own tables, and it's the only login the API and workers ever use for that firm. An integration test opens a firm's login against a different firm's database and expects the connection to be refused. No extension that can reach across databases, like dblink or postgres_fdw, is installed in a firm's database.
This is isolation for the application's connections, not a claim that nothing could ever cross the line. Two things sit outside it, and it's better to name them than to imply they don't exist.
An administrator role exists for the work the firm roles can't do: creating databases, installing extensions, granting each firm role its schema access, and recovery work like restores. It never serves a request.
And a firm's login can still connect to the platform database. Provisioning revokes CONNECT from PUBLIC on firm databases, but not on the platform one, so the default still applies there. What protects the platform database is that firm roles are granted nothing in it: no table privileges, and no inherited roles. A test logs in as a firm, connects to the platform database, and checks that reading the table of every firm's connection strings is refused, and those strings are encrypted at rest as well. Revoking the connection itself is left as a deployment decision.
What the design removes is the everyday failure: an application bug, like a missing filter, reading another firm's data.
tenant.interceptor.ts (simplified)
const firm = resolveFirm(request.user); // from the tokenconst db = await pool.acquire(firm.id); // lazy, evicted when idlereturn cls.run({ firm, db }, () => db.transaction(() => next.handle()));
A request arrives with a firm A token.
Read all 4 steps
- A request arrives with a firm A token.
- The interceptor picks firm A's connection from a lazy pool.
- Every query in the request runs in that firm's transaction.
- Other firms' databases are never on the path. There is no filter to forget.
The hard part isn't the databases. It's making every query in a request use the right one, without passing a connection through every function call. That's where NestJS gets in the way, at first.
How Nest builds your app
Nest is built around dependency injection. You write classes, mark them @Injectable(), and ask for what they need in their constructor. Nest creates them for you and wires them together:
@Injectable()
export class IncidentsRepository {
constructor(private readonly db: DataSource) {}
}
@Injectable()
export class IncidentsService {
constructor(private readonly incidents: IncidentsRepository) {}
}By default each of these is a singleton. Nest builds one instance when the app starts, and every request uses that same instance. That's cheap and fast, and it's what you want almost all the time.
It's also the problem. A singleton is built before any request exists, so whatever DataSource it receives at startup is the one it keeps. With a database per firm, there is no single DataSource to give it.
The obvious fix: request scope
Nest has an answer that looks made for this: injection scopes. Mark a provider Scope.REQUEST and Nest builds a fresh instance for every incoming request, with the request itself available to inject:
@Injectable({ scope: Scope.REQUEST })
export class FirmConnection {
constructor(@Inject(REQUEST) request: Request, pools: ConnectionManager) {
// read the firm from the request, pick its database
}
}The catch is in the same documentation page: scope bubbles up the injection chain. A provider that depends on a request-scoped provider becomes request-scoped itself, whether you marked it or not. So does whatever depends on that, all the way up to the controller.
firm-connection.ts (the tempting version)
@Injectable({ scope: Scope.REQUEST })export class FirmConnection { constructor(@Inject(REQUEST) req: Request) {}} @Injectable() // now request-scoped as wellexport class IncidentsRepository { constructor(private db: FirmConnection) {}}
By default Nest builds each provider once, at startup, and every request shares it.
Read all 5 steps
- By default Nest builds each provider once, at startup, and every request shares it.
- To point at the caller's firm, the connection becomes request-scoped.
- The repository injects it, so the repository is rebuilt per request too.
- Then the service that injects the repository, and the controller above it.
- Every request now rebuilds the whole chain before it runs a single query.
On a large API that means Nest rebuilds a big slice of the dependency graph on every request, before your code runs a single query. It also leaves a gap: a queue worker or a scheduled job has no HTTP request, so a provider that needs REQUEST can't serve it, and OmniGuard's background jobs need a firm's database as much as its controllers do.
Nest also offers durable providers, which cache that request-scoped subtree per tenant instead of per request. That fixes the rebuild cost, but the tenant is still tied to the dependency graph and to an incoming request. I wanted the firm to be something code reads at the moment it needs it, wherever it runs.
How AsyncLocalStorage carries the firm through a request
Node runs your JavaScript on one thread. While one request waits on the database, another request's code runs on that same thread, and then the first carries on when its query returns. Requests interleave at every await.
That rules out the simplest idea, a module-level let currentFirm. Request A sets it, awaits, request B sets it to something else, and when A resumes it reads B's firm. Languages with a thread per request solve this with thread-local storage. Node has one thread, so it needs something that follows the chain of async work instead.
That's AsyncLocalStorage, built into Node (node:async_hooks). You call run(store, fn), and everything fn starts, including code that runs after any number of awaits, timers or promise callbacks, sees that store through getStore(). Two requests started with two different stores never see each other's, even though they share a thread and the same singleton objects.
You can run this as it is:
import { AsyncLocalStorage } from "node:async_hooks";
import { setTimeout as sleep } from "node:timers/promises";
const context = new AsyncLocalStorage<{ firm: string }>();
// A singleton, created once. It takes no firm parameter.
async function listIncidents() {
await sleep(Math.random() * 50); // stand-in for a query
return `incidents for firm ${context.getStore()?.firm}`;
}
const a = context.run({ firm: "A" }, () => listIncidents());
const b = context.run({ firm: "B" }, () => listIncidents());
console.log(await Promise.all([a, b]));
// [ 'incidents for firm A', 'incidents for firm B' ]Both calls are in flight at once, finish in random order, and each still reads its own firm.
async-context.ts (plain Node)
import { AsyncLocalStorage } from "node:async_hooks";const context = new AsyncLocalStorage<{ firm: string }>(); context.run({ firm: "A" }, () => handle(requestA));context.run({ firm: "B" }, () => handle(requestB)); // deep inside a singleton, after any number of awaits:const firm = context.getStore()?.firm;
Firm A's request arrives and runs inside its own context.
Read all 5 steps
- Firm A's request arrives and runs inside its own context.
- While A waits on the database, firm B's request starts a context of its own.
- Both run through the same service instance, interleaved on one thread.
- When A resumes, the service reads A's store, so A's query goes to A's database.
- B's call reads B's store. Nothing was passed down and nothing was rebuilt.
This turns the problem around. Providers stay singletons, built once at startup. The firm and its connection live in the async context, bound once per request, and a repository reads the connection from the context at the moment it runs a query.
It's worth being precise about what AsyncLocalStorage does and doesn't do. It only carries a value along the chain of async work. It doesn't know what a database is, and it doesn't make anything tenant-aware by itself. In OmniGuard, two other pieces do that work, and the rest of this post is about them: an interceptor that decides which firm a request belongs to and puts that firm's open transaction into the context, and a connection manager that owns every firm's connection pool. The context is only the thread that ties them together.
nestjs-cls: AsyncLocalStorage, shaped for Nest
Using AsyncLocalStorage directly in Nest means writing your own middleware or interceptor to call run(), and an injectable wrapper so providers can read it. nestjs-cls is that wrapper. Its ClsService is an injectable singleton with run(), get() and set() on top of an AsyncLocalStorage. The name comes from "continuation-local storage", the older name for the same idea.
nestjs-cls can bind the context for you in a middleware on every request. OmniGuard turns that off:
ClsModule.forRoot({ global: true, middleware: { mount: false } });A middleware runs before the request's token is verified. Binding a context there would create one with no firm in it that still looks valid. So the context is bound later, by hand, once the firm is known.
Feature code doesn't use ClsService directly. It goes through a small TenantContext that only exposes what the store holds, and refuses to guess:
@Injectable()
export class TenantContext {
constructor(private readonly cls: ClsService<TenantStore>) {}
run<T>(store: TenantStore, fn: () => T): T {
return this.cls.run(() => {
Object.assign(this.cls.get(), store);
return fn();
});
}
get firmId(): string {
const { firmId } = this.cls.get();
if (!firmId) throw new MissingTenantContextError("firm context");
return firmId;
}
// The open transaction. Repositories read it from here, never as a parameter.
get manager(): EntityManager {
const { manager } = this.cls.get();
if (!manager) throw new MissingTenantContextError("open transaction");
return manager;
}
}The getters that database work depends on, the firm and the open transaction, throw instead of returning undefined. With a database per firm there is no default database to fall back to, so code that runs outside a firm's context fails loudly and immediately rather than doing something quietly wrong later.
One interceptor binds it all
An interceptor in Nest wraps a route handler: it runs code before the handler, gets the handler's result back as an RxJS Observable, and can run code after. Registered globally, it wraps every route. It also runs after guards, so by the time it sees a request, the token has been verified.
OmniGuard has one, and for an ordinary request from a firm's staff it does four things:
- Takes the firm from the verified token's claims. Never from a header or the URL: there's a test that sends a header claiming a different firm and checks it's ignored.
- Borrows that firm's database from the connection manager, holding a lease so the pool can't be closed mid-request.
- Opens a transaction on it.
- Binds the firm and the transaction into the async context, then runs the handler inside it.
intercept(context: ExecutionContext, next: CallHandler) {
const { claims } = context.switchToHttp().getRequest();
if (!claims) return next.handle(); // a public route: no firm, no transaction
return from(
this.connections.withConnection(claims.tenantId, (db) =>
db.transaction((manager) =>
this.tenant.run({ firmId: claims.tenantId, userId: claims.sub, manager }, () =>
settleHandler(next),
),
),
),
);
}The interesting line is settleHandler. Nest hands an interceptor the handler's result as an RxJS Observable, a stream that can emit values over time, while a database transaction wants a promise: a single "I'm done" or "I failed". You can't await an Observable directly, and the tempting conversions resolve on the first value, which could commit while the handler is still writing. So settleHandler bridges the two by hand:
// Resolving on the first emission would commit before the work finished.
export function settleHandler(next: CallHandler): Promise<unknown> {
return new Promise((resolve, reject) => {
let value: unknown;
next.handle().subscribe({
next: (emitted) => { value = emitted; },
error: reject,
complete: () => resolve(value),
});
});
}The handler | The promise | The transaction |
|---|---|---|
Emits and completes | Resolves with the last value | Commits |
Completes without emitting | Resolves with | Commits |
Errors or throws | Rejects | Rolls back, and the error reaches the client |
TypeORM's transaction() commits if its callback resolves and rolls back if it rejects, exactly once either way, and the lease on the pool is released in a finally, whatever happened. Tests check both halves: a handler that throws leaves a rollback and no commit, and a handler that finishes its writes a few milliseconds after it starts is still inside the transaction when it commits.
Two limits are worth stating plainly, because they're easy to assume away.
A client that disconnects mid-request doesn't cancel anything. The handler runs to completion and commits anyway. Nothing in the code listens for the disconnect, and no document records it as a choice, so it's simply how the code behaves today.
And nothing bounds a request's total time. The database cancels any single statement that runs longer than 30 seconds, but that's per statement: a handler that runs several statements, or waits on something else while its transaction is open, can take longer. Calls to other services have their own timeouts, from 3 seconds for file storage to 35 for PDF rendering, but there's no limit on the request as a whole and no limit on how long a transaction may sit idle. If you build this, an overall request timeout and Postgres's idle_in_transaction_session_timeout are worth adding from the start.
Some routes deliberately skip the request transaction. A flow whose refusal mustn't undo what it already wrote, for example, runs bound to its firm and commits its own writes as it goes. The interceptor still holds the lease and binds the context; it just doesn't wrap the handler in one transaction.
Events the request produced are delivered only after the commit, so nothing announces a change that was rolled back.
Support access gets the same care, because it's the one sanctioned way for someone outside a firm to see inside it. Platform staff can open a firm by impersonating one of its users, and that session is read-only at two levels. The HTTP layer refuses anything but GET and HEAD, and inside the firm's transaction the first statement is SET TRANSACTION READ ONLY, before anything else can run, so a write that slipped past the first check fails in Postgres. A handful of routes allow a write under a separately elevated session. Every impersonation records who started it, whose account, why and when it ended, and every request made during it is stamped with its method and route. That stamp is written before the firm's transaction commits, so if the audit write fails, the request's changes are rolled back rather than left unaudited.
Feature code never asks which firm it is serving. A repository reads tenant.manager and runs its query, and the only database it can reach is the one in its context.
Connections per firm, without exhausting Postgres
Opening a Postgres connection is expensive: Postgres starts a process for each one, and an instance has a hard limit on how many it accepts. So apps keep a pool: a few connections opened once and lent to queries as they come.
A pool per firm multiplies that. OmniGuard's design notes use a sizing scenario of ten API replicas, not a real deployment count, to make the point. Each replica keeps its own pool per firm, with up to five connections, so if all of them were full at once: 10 replicas × 60 firms × 5 connections = 3,000 connections, before counting workers, migrations or anything else. That's far beyond what one Postgres instance is sized for. Opening every firm's pool at startup isn't an option. The connection manager opens a firm's pool on that firm's first request and closes it when the firm goes quiet.
Setting | Default | Why |
|---|---|---|
Connections per firm, per replica | 5 | Keeps replicas × firms × pool under the instance limit. A request holds its connection for its whole transaction, so a firm's sixth concurrent request waits |
Idle before a pool is closed | 60 s | A firm that stops working gives its connections back |
Sweep interval | 15 s | How often idle pools are looked for |
Connect timeout | 5 s | A sick firm database fails fast instead of piling up requests |
Statement timeout | 30 s | One runaway query can't hold a connection forever |
These are defaults, and the configuration calls the five-connection cap a guess until there's real data on how many firms are busy at once. Background workers use a separate setting: one connection per firm.
Two things make laziness safe. The first is that two requests for a firm whose pool isn't open yet must not open two pools. The manager keeps the in-flight connection attempt and hands the same promise to everyone who asks:
async acquire(firmId: string): Promise<DataSource> {
const open = this.pools.get(firmId);
if (open) return open.dataSource;
// Ten simultaneous first requests would otherwise open ten pools and discard nine.
const inFlight = this.opening.get(firmId);
if (inFlight) return inFlight;
const connecting = this.connect(firmId);
this.opening.set(firmId, connecting);
try {
return await connecting;
} finally {
this.opening.delete(firmId);
}
}The second is that the idle sweep must never close a pool something is using. Every use goes through withConnection, which counts leases, and the sweep skips any pool with a lease out:
async withConnection<T>(firmId: string, work: (db: DataSource) => Promise<T>): Promise<T> {
await this.assertFirmActive(firmId); // a suspended firm is refused even if its pool is warm
const db = await this.acquire(firmId);
const pool = this.pools.get(firmId);
pool.leases += 1;
try {
return await work(db);
} finally {
pool.leases -= 1;
pool.lastUsedAt = Date.now();
}
}One rule was added after the first design. If the platform database is unreachable, a pool closed for being idle can't be reopened, because reopening it needs the firm's connection string from the platform database. So the sweep checks the platform database first and closes nothing while it's down. Without that check, a partial outage would slowly become a total one as each firm's pool timed out.
A firm's pool also checks the firm's schema when it opens. If the database is missing a migration this release expects, the API refuses that firm with a 503 and checks again ten seconds later, instead of running new code against an old schema.
Background workers make the opposite call: they serve the firm anyway and log loudly, because in a control room a missed alarm is worse than a failed query. In practice a worker shouldn't meet an old schema at all. Workers deploy only after every active firm has been migrated, and a suspended or restored firm can't be brought back into service until its schema is current: reinstating or repointing it is refused until then. The serve setting is the fallback for when that ordering breaks, say a worker started before the migrations ran, and it trades a possible failed query for alarms that keep going out.
The opposite direction is covered by a rule the build checks. Migrations run before the new code goes live, so for one release the old code serves on the new schema. A test scans every tenant migration and fails the build on one that drops, renames or retypes a column, or makes one required that old code may leave empty, unless it's on a hand-kept allow-list that names the release that made it safe. That's the contract half of an expand and contract change, shipped after the expand half. It's a pattern check, not a proof, but it catches the changes that break old code most often.
Why TypeORM: the isolation model picked the ORM
Every snippet so far has leaned on TypeORM: a DataSource per firm, dataSource.transaction(), an EntityManager in the context. It's fair to ask whether the design depends on it. It doesn't depend on TypeORM being special, but the ORM choice does depend on the design, and OmniGuard's history shows that clearly.
The earlier design for OmniGuard's tenancy, recorded in its design document before the code in this post was written, used one database with a schema per firm, and the ORM was Drizzle. In that design the firm lived in connection state: each transaction ran SET LOCAL search_path to point at the right schema. TypeORM was ruled out then, in one sentence of the design document: switching firms per transaction couldn't be done "without a connection per tenant, which does not survive pooling". A pooler in transaction mode hands out a server connection only for the length of a transaction and doesn't keep session state like SET between them, so a connection that belonged to one firm was exactly what the design couldn't have.
Moving to a database per firm turned that around. A connection per tenant isn't a workaround any more, it's the whole design: firm A's connections can only ever reach firm A's database. The objection to TypeORM disappeared, and so did the reason Drizzle and Kysely had been the only candidates. The changelog records the switch the same day the isolation model changed.
So the honest answer to "can other ORMs do this?" is yes. Drizzle, Kysely, Prisma and MikroORM can all talk to many databases. The real question is whether an ORM makes three things ordinary:
This design needs | TypeORM | Elsewhere |
|---|---|---|
A connection to a database you only learn about at runtime | Drizzle and Kysely wrap a pool you create; MikroORM runs an | |
Migrations run from code, against that one connection | Drizzle, Kysely and MikroORM all have migrators you call from code; Prisma runs migrations from its CLI, pointed at one database at a time | |
A transaction handle repositories can use without being passed it | | Drizzle, Kysely and Prisma hand a transaction object to a callback; MikroORM forks an entity manager per request |
One caveat on the middle row: TypeORM's documentation teaches migrations through its CLI, but runMigrations() is a public method on DataSource in its source, and that's the call OmniGuard's provisioning and deploy runner use. For this one, check your ORM's API as well as its guide.
TypeORM's DataSource bundles all three into one object: the pool, the entity metadata and the migration runner for a single database. That makes it the natural value for the connection manager's map, one per firm:
export function createTenantDataSource({ url, poolMax }: DatabaseOptions): DataSource {
return new DataSource({
type: "postgres",
url, // this firm's database, from the platform directory
entities: [Customer, Site, Incident /* every tenant entity */],
migrations: tenantMigrations,
extra: { max: poolMax }, // connections in this firm's pool
});
}The same factory serves three callers. The connection manager opens it for requests, provisioning runs runMigrations() on it to build a new firm, and the deploy runner runs runMigrations() on it for every existing firm. Repositories never see it. They take the open transaction from the context:
@Injectable()
export class CustomerRepository {
constructor(private readonly tenant: TenantContext) {}
private get customers() {
return this.tenant.manager.getRepository(Customer); // this request's firm, this request's transaction
}
}It wasn't free. With Drizzle, one table definition produced validators for the database, the API and the apps. With TypeORM, API validation moved to class-validator, a separate set of rules to keep in step. And two TypeORM behaviours are written down in the project as needing care. The design notes warn that migration:generate writes a renamed column as a drop and an add, which across sixty databases would be sixty rounds of data loss, so every generated migration is reviewed by hand. And testing against Postgres showed that save() sends no version check, so a stale write silently overwrites a newer one; versioned rows are written through an update that includes WHERE version = ? instead.
The lesson generalises: decide the isolation model first, then pick the ORM that makes that model ordinary. The same ORM was wrong for OmniGuard under one model and right under the next.
Provisioning a firm: eight steps, each safe to repeat
A new customer means a new database, a new login and a new storage bucket, and any step can fail halfway: a network blip, a timeout, a typo in configuration. So provisioning is idempotent: running it twice has the same effect as running it once. A half-provisioned firm is repaired by running the whole thing again, and every step checks before it acts.
Step | What it does | Why a re-run is safe |
|---|---|---|
| Adds the firm to the platform directory, status | Finds the existing record by slug |
| | Checks |
| A Postgres role that can only reach this firm's database | Checks |
| Installs PostGIS, pg_trgm and btree_gist, then checks they're really there | |
| Builds the firm's schema, as the firm's own login | Already-applied migrations are skipped |
| The firm's first admin account and invite | Finds the existing account, sends no second invite |
| The firm's file storage | Ensures rather than creates |
| Saves the encrypted connection string and marks the firm active | Skips a firm that's already active |
The firm stays provisioning until step eight, so a half-built firm can't be reached: the connection manager refuses any firm that isn't active.
Step four doesn't take the install command's word for it. Geofences are PostGIS columns, pg_trgm powers name search and btree_gist backs a no-overlap constraint, so the schema itself depends on all three. Provisioning installs them and then asks Postgres which ones are present:
for (const extension of ["postgis", "pg_trgm", "btree_gist"]) {
await firm.query(`create extension if not exists ${quoteIdentifier(extension)}`);
}
const installed = await firm.query("select extname from pg_extension where extname = any($1)", [required]);
const missing = required.filter((name) => !installed.some((row) => row.extname === name));
if (missing.length > 0) throw new ProvisioningError("extensions", `missing after install: ${missing.join(", ")}`);Migrations across every firm's database
With one database, a deploy runs its migrations once. With sixty, it runs them sixty times, and two deploys started close together must not both do it at once.
That second part uses a Postgres advisory lock. It's a lock on a number your application chooses rather than on a row or a table. Postgres doesn't know what the number means; it only guarantees that one session holds it at a time. pg_advisory_lock waits until the lock is free, and a session-level lock is released when the connection closes, even if the process holding it crashes.
That last property has a trap in it. "When the connection closes" means the database session ends, and returning a connection to a pool doesn't end it: a pooled connection can carry a lock it took long after the code that took it has finished. So the deploy lock never touches a pool. It opens a dedicated connection just for the lock and ends it in a finally:
export async function withDeployLock<T>(platformUrl: string, work: () => Promise<T>): Promise<T> {
const lock = new Client({ connectionString: platformUrl });
await lock.connect();
try {
await lock.query("select pg_advisory_lock($1)", [DEPLOY_LOCK_KEY]);
return await work();
} finally {
await lock.end(); // closing the connection releases the lock
}
}Under that lock, the pre-deploy step migrates the platform database, then visits every firm's database in turn. It goes one firm at a time, on purpose, active firms first. A migration that fails on one firm usually fails on all of them, so an active firm's failure stops the run: one broken firm instead of sixty half-migrated ones. A suspended or archived firm's failure is recorded and the run carries on, since those firms aren't being served. The run reports which firm failed, so the next run can resume there, and a failed active firm stops the deploy from going live at all. A second deploy that arrives meanwhile waits for the lock, then finds nothing left to do.
Erasures that survive a restore
A database per firm makes backups and restores per firm, which is a real advantage: one firm's data can be restored without touching anyone else's. It also creates a problem the opening promised to deal with. Privacy law lets a person ask to be erased. If a firm erases a guard today and restores last week's backup tomorrow, the restore brings the guard back.
Deleting from the live database can't fix that, because the backup isn't the live database. OmniGuard keeps a record of every erasure instead, an erasure ledger, and replays it after a restore:
- Every erasure writes a ledger row in the firm's database, in the same transaction as the erasure itself. The ledger is append-only, enforced by database triggers.
- The row is copied to the platform database, and a third copy goes to the backup bucket, locked for 13 months in a mode that nobody, including an administrator, can shorten.
- After a restore, a replay step compares the restored ledger with the copies, finds the erasures made since the backup was taken, and applies them again, through the same code the erasure used the first time. The erasure always wins: data that's already gone is skipped, and anything the backup brought back is erased again.
- The restored data isn't served until the replay has finished. If a replay can't apply, the restored data stays offline: a whole-server restore keeps the services stopped, and a single firm keeps running on its live database instead. The failure repeats every time until someone fixes the cause.
The project's decision record explains why the ledger can't live in the firm's database alone: a restore rolls the ledger back with the data, so "the erasures since the backup vanish with their record and nothing is left to replay". The copy that matters is the one the restore can't reach.
There's no per-firm encryption key here, the technique sometimes called crypto-shredding, where deleting a key makes a firm's old backups unreadable. Backups are encrypted, kept for a fixed period and then expire; the ledger is what keeps erasures true in the meantime.
The operational trade-offs
A database per tenant isn't free, and it's worth being honest about where the bill arrives.
Cost | How OmniGuard handles it |
|---|---|
Connections grow with firms × replicas | Lazy pools, idle eviction, five connections per firm per replica |
Deploys get slower as firms are added | Sequential migrations with resume; the cost grows with the firm count |
More moving parts to provision | Eight idempotent steps, a firm unreachable until the last |
Backups and restores are per firm | An erasure ledger, replayed after every restore before the data is served |
Reporting across firms is harder | Anything cross-firm goes through the platform database |
These choices are written down in the project's stack decisions, sized against a capacity model: 60 firms, about 9,000 registered users, 3,000 of them online at peak. The document has one rule for every assumption in it: if a number turns out wrong by more than about three times, the decisions that depend on it get rechecked. For tenancy, OmniGuard's trigger is around 150 firms. Past that, connection fan-out or migration time becomes the real constraint, and a shared-schema tier for small firms is worth costing. Not before. That number comes from OmniGuard's own capacity model and workload. It isn't a general limit; another product will hit its own threshold at a different count, and the way to find it is the same capacity model, filled in with your numbers.
When not to do this
An architecture is good when its trade-offs fit the problem in front of you, and for plenty of products a database per tenant is the wrong call.
If your product has | Prefer | Why |
|---|---|---|
Thousands of small or free-tier tenants | Shared tables with row-level security | Thousands of databases means thousands of pools, migrations and backups for very little data each |
Reporting across tenants at its core | Shared tables, or a separate analytics store | Every cross-tenant question becomes a fan-out over every database |
A small team and no automation yet | Shared tables first | Provisioning and migrations by hand stop working around the tenth customer |
Low-sensitivity data | Shared tables, carefully filtered | A missed filter is still a bug, but not a reportable breach |
It earns its cost when tenants' data is sensitive enough that one missed filter is a reportable incident, when customers ask where their data lives or want it restored or deleted on its own, when you have tens or low hundreds of tenants rather than hundreds of thousands, and when you can automate provisioning and migrations from the first customer.
What it adds up to
None of the pieces is exotic. A database per firm takes the missed filter off the table. AsyncLocalStorage carries the firm through a request without rebuilding anything. One interceptor binds it, a lazy pool keeps the connection count sane, and provisioning and migrations are scripts that are safe to run twice.
Put together, feature code never names a firm, and the dispatcher from the first paragraph can't see a competitor's break-in: the only database their request can reach is their own firm's, through a login that can't open anyone else's. A missing filter is still a bug, but it's no longer a breach.
Further reading
- NestJS: providers and dependency injection, injection scopes and durable providers, interceptors
- Node.js:
AsyncLocalStorage - nestjs-cls
- PostgreSQL: roles, privileges and
GRANT, row-level security, advisory locks,max_connections - TypeORM: DataSource, transactions, migrations
- Other ORMs on the same three needs: Drizzle, Kysely, MikroORM, Prisma
- PgBouncer: pool modes and what transaction pooling breaks
- PostGIS