Multi-tenancy

Multi-tenancy & Row-Level Security

UQL scopes queries to the current tenant with a security filter whose condition reads a per-request context. Once set up, every read, update, and delete - including relations and cascades - is scoped automatically. You never write WHERE tenantId = ... by hand, and you can’t forget it.

1. Mark the tenant filter `security`

Its condition is a function of the ambient context:

import { Entity, Id, Field, Filter } from 'uql-orm';

@Filter('tenant', {
  // with no tenant, return `undefined` (instead of { companyId: undefined }) so the query throws instead of running unscoped
  condition: (ctx) => (ctx?.tenantId != null ? { companyId: ctx.tenantId } : undefined),
  security: true,
})
@Entity()
export class Invoice {
  @Id() id?: number;
  @Field() companyId?: number;
  @Field() total?: number;
}

Optionally type the context once so ctx.tenantId is typed everywhere:

declare module 'uql-orm' {
  interface UqlContext {
    tenantId: number;
    userId: string;
  }
}

2. Set the context for a unit of work

withContext establishes the ambient context; it propagates across await, Promise.all, and transactions - including the pool-level reads, so one wrapper scopes a whole parallel fan-out:

import { withContext } from 'uql-orm';

await withContext({ tenantId: 42 }, async () => {
  await querier.findMany(Invoice, { $where: { total: { $gt: 100 } } });
  // Generated SQL:
  //   SELECT ... FROM "Invoice" WHERE ("total" > $1) AND ("companyId" = $2)   -- $2 = 42

  await Promise.all([pool.findMany(Invoice, {}), pool.count(Invoice, {})]); // both scoped to tenant 42
});

Wire it once at your HTTP boundary and every request is scoped. getContext receives the framework’s request; read the tenant from a verified source (a session store, or a decoded JWT) - never from client input, which a caller could forge to reach another tenant:

// framework-agnostic HTTP handler - tenant from the session
createRequestHandler({ getContext: (req) => ({ tenantId: req.session.tenantId }) });

// ...or from an auth layer that populated req.user (Passport, a guard, JWT middleware)
createRequestHandler({ getContext: (req) => ({ tenantId: req.user.tenantId }) });

// NestJS - see the NestJS guide
UqlModule.forRoot({ pool, getContext: (req) => ({ tenantId: req.user.tenantId }) });

What `security: true` guarantees

Always applied - { filters: false } and per-name bypass are ignored for security filters.
Can’t be widened by the client - the condition is AND-merged as its own predicate, so a request sending $where: { companyId: 999 } becomes companyId = 999 AND companyId = 42, which matches no rows, never a cross-tenant read.
Fails closed - if the context is missing (tenantId undefined, so the condition returns undefined), the query throws UqlSecurityError instead of running unscoped. (onMissing: 'skip' is rejected on security filters - they must fail closed.)
Wire-safe over HTTP - the request parser only accepts query keys, so a remote client can’t inject filters/context to bypass it. Always derive the tenant from a verified source (session, JWT), never from client input.

Combining with soft-delete

Security and convenience filters compose. An Invoice that is also soft-deletable gets both predicates automatically:

SELECT ... FROM "Invoice" WHERE ("companyId" = $1) AND ("deletedAt" IS NULL)

A cross-tenant admin task can bypass convenience filters but never the security one:

querier.findMany(Invoice, {}, withDeleted()); // includes trashed, still tenant-scoped

Non-HTTP contexts (jobs, scripts, tests)

Anything that isn’t an HTTP request just wraps its work in withContext:

await withContext({ tenantId }, () => runNightlyBilling(tenantId));

For trusted work that must span all tenants (startup recovery, cleanup sweeps), give the condition a system branch that returns {} (“no restriction”) and run those jobs under an explicit system context - queries with no context at all still fail closed:

condition: (ctx) => (ctx?.system ? {} : ctx?.tenantId != null ? { companyId: ctx.tenantId } : undefined),

await withContext({ system: true }, () => recoverStaleJobs()); // spans every tenant, deliberately

Event-driven pipelines (emitters, timers, queues)

withContext uses AsyncLocalStorage, which follows await chains but does not propagate into event-callback ticks: an emitter listener, a timer, or a queued job runs outside the scope that registered it. Two tools cover those boundaries:

captureContext() - capture once where the context exists, replay wherever the callback fires:

const scoped = captureContext(); // e.g. when the session/queue item is created inside a scoped request
deepgram.on('transcript', (t) => scoped(() => saveTranscript(t))); // runs with the captured context

{ context } per unit of work - when pipeline code already knows its tenant locally (a resource.tenantId in hand), pass it right where the querier is acquired; no ambient wiring at all:
```
await pool.withQuerier((q) => q.updateMany(Resource, { $where: { id } }, patch), { context: { tenantId } });
```

Rule of thumb: same mechanism, pick by scope - withContext scopes a span (a request, a whole job), { context } scopes a single pool call, and captureContext() carries a span’s context across callback boundaries.

Wrap your app’s few chokepoints (event bus, queue runners, socket dispatch) rather than every function - everything they await inherits the context.

Filling the tenant column on insert

Filters scope reads, updates, and deletes - inserts still need the tenant value on the row. Fill it from the ambient context so payloads never mention it (an explicitly provided value still wins):

@Field({ references: () => Company, updatable: false, onInsert: () => getContext()?.tenantId })
companyId?: number;

See Query Filters for the full filter model (named, default-on, bypassable) that this builds on.