ADR-0002: PostgreSQL with row level security per company
- Status: Accepted
- Date: 2026-11-12
Context and problem statement
Section titled “Context and problem statement”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?
Decision drivers
Section titled “Decision drivers”- 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.
Considered options
Section titled “Considered options”- One database and one schema, a
tenant_idcolumn on every business table, and PostgreSQL row level security. - One database or one schema per company. Common market alternative, listed for comparison.
- One database with
tenant_idfiltered only by the application queries. Common market alternative, listed for comparison.
Decision outcome
Section titled “Decision outcome”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.
Consequences
Section titled “Consequences”- 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.tsagainst the database andapps/api/test/isolation.test.tsthrough 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
LIKEis 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.
More information
Section titled “More information”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()andapp.user_id().packages/db/src/context.ts:withContextandsetContext.- Concept page: Tenant isolation.
- Reference: Database tables.