Skip to content

ADR-0002: PostgreSQL with row level security per company

  • Status: Accepted
  • Date: 2026-11-12

Ochema is multi-tenant: carriers of any size, from one vehicle up, use the same system. The data of one company must never be read or changed by another, including when a query in the application forgets a filter.

Where is the isolation between companies enforced?

  • Isolation must hold below the application code.
  • One database to migrate, back up and operate.
  • The rule must be provable by an automated test for every module.
  • The API must not hold a privilege that turns the isolation off.
  1. One database and one schema, a tenant_id column on every business table, and PostgreSQL row level security.
  2. One database or one schema per company. Common market alternative, listed for comparison.
  3. One database with tenant_id filtered only by the application queries. Common market alternative, listed for comparison.

Chosen option: 1.

Every business table has tenant_id and a policy that compares it with app.tenant_id(), a function that reads a setting of the current transaction. The API sets app.tenant_id and app.user_id in the first statement of each transaction (withContext in packages/db/src/context.ts, used by TransactionInterceptor). The setting is local to the transaction, so a pooled connection returns clean.

The runtime role of the API and the worker, ochema_app, cannot bypass row level security and owns no table. The tables belong to ochema_owner, used only by migrations, and they have FORCE ROW LEVEL SECURITY, so the owner obeys the policies too. A transaction without context sees no business row.

Option 3 was not chosen because a single missing filter would expose data. With option 1 the same mistake returns only rows of the current company.

  • Good, because a query without a company filter is still confined to one company.
  • Good, because the isolation is tested at two levels: packages/db/test/isolation*.test.ts against the database and apps/api/test/isolation.test.ts through the API.
  • Good, because one schema serves every company: one migration history, one set of indexes.
  • Bad, because flows that start without a company (sign-in, public pages, the worker) need controlled exceptions, see ADR-0003 and ADR-0006.
  • Bad, because a policy limits the query planner: a condition with a non-leakproof operator such as LIKE is not used as an index condition under a policy. Text search needed its own design, see ADR-0022.
  • Bad, because all companies share the capacity and the maintenance windows of one database server.
  • packages/db/sql/bootstrap-roles.sql: the database roles and what each one may do.
  • packages/db/migrations/0000_foundation_prelude.sql: app.tenant_id() and app.user_id().
  • packages/db/src/context.ts: withContext and setContext.
  • Concept page: Tenant isolation.
  • Reference: Database tables.