Tenancy and row-level security
How one database keeps every workspace’s data apart, and the rules that keep it so.
On this page
Every workspace's rows sit in the same tables of one Postgres database. What keeps workspace A from ever reading workspace B is Postgres row-level security (RLS), enforced by the database itself, not by remembering a WHERE clause. These rules are the ones that, if broken, leak one customer's data to another.
The idea in one picture
- 1Request for workspace A
- 2withTenant(A)opens a transaction
- 3set_config('app.current_tenant_id', A, true)
- 4Every query in itpolicy: organization_id = current_tenant_id()
- 5Transaction endsthe setting is discarded
Each tenant table has a policy like this:
CREATE POLICY widgets_tenant_isolation ON public.widgets
FOR ALL
USING (organization_id = public.current_tenant_id())
WITH CHECK (organization_id = public.current_tenant_id());
Even a query with no WHERE returns only the current workspace's rows. A query with no tenant set returns nothing.
Two database roles
The separation between the two roles is what makes the policies real:
| Role | Used by | Can |
|---|---|---|
| Dokploy superuser | Migrations only, from your machine (MIGRATION_DATABASE_URL in .env.migrate) | Own the schema, run DDL. RLS does not apply to it. |
ikazebot_app | The API, the worker, the test suites (DATABASE_URL) | Read and write rows only. No DDL, NOBYPASSRLS, owns nothing. |
The API refuses to boot on a role that bypasses RLS, in every environment. Development once ran as the superuser and hid exactly the bugs RLS exists to catch.
Querying: TenantContextService
// Normal tenant work: the default.
const rows = await this.tenants.withTenant(organizationId, (tx) =>
tx.select().from(widgets).where(eq(widgets.organizationId, organizationId)),
);
| Method | Use for |
|---|---|
withTenant(orgId, fn) | Everything tenant-scoped. Requests, jobs, all of it. |
withUser(userId, fn) | The auth path only, before a workspace is chosen |
withNewOrganization(…) | Creating a workspace (its id does not exist yet) |
unscoped | Only truly global tables: users, sessions, accounts, verifications |
Reaching for unscoped to touch a tenant table is a review failure. The bug looks like missing data, not like a security hole, which makes it easy to "fix" the wrong way.
The rules
- Every tenant table has
organization_id:NOT NULL, referencingorganizations,ON DELETE CASCADE. ENABLEandFORCErow-level security. WithoutFORCE, the table's owner is exempt, and the owner runs migrations and maintenance scripts.- Policies have
USINGandWITH CHECK.USINGgoverns reads;WITH CHECKgoverns writes. WithUSINGalone, tenant A can insert a row stamped with tenant B's id. - Call
public.current_tenant_id(), nevercurrent_setting(...)::uuidinline. The helper'sNULLIF(…, '')turns a missing tenant into zero rows instead of a 500. - RLS is the net, not the filter. Keep the explicit
where organizationId = …in application queries. The predicate prevents the bug; the policy catches it if the predicate is forgotten. - Policies are hand-written SQL, in their own migration, in the same pull request as the table. drizzle-kit neither generates nor diffs them.
- No soft delete. No
deleted_at, ever. Deletion cascades, because an orphan row whose parent is gone matches no policy again. - Ids are
uuidv7(), minted by Postgres 18, not by the application. updated_atis maintained by a trigger, not Drizzle's$onUpdate, which only covers writes that go through Drizzle.
Defence in depth around it
- Secrets (action API keys, channel tokens) are encrypted with AES-256-GCM in a dedicated table, never in a plain column or
metadata. - Logs never contain message bodies, contact PII, tokens or connection strings; redaction is central in
logger.config.ts. - Authorisation (who may do what within a workspace) is CASL, defined once in
auth/ability.factory.ts. The web app's role checks are convenience only, and the API checks again on every request.
To add a table safely, follow Adding a tenant table step by step.