Tenant isolation
Ochema is multi-tenant: every carrier that signs up is a company, called a tenant in the code, and all companies share one database. This page describes the mechanism that keeps the data of one company out of reach of every other. It is part of section 9 of the arc42 template. The reasons for the design are in ADR-0002 and ADR-0003; this page shows how the code applies it and what proves it.
The problem
Section titled “The problem”A recruiting and compliance system holds Social Security numbers, driver records and test results. A single query that forgets its company filter must not be able to show one carrier the drivers of another. The protection therefore cannot live in the queries of the application: it has to hold when the application code is wrong.
The rule
Section titled “The rule”- Every business table has a
tenant_idcolumn and a row level security policy that compares it with the company of the current transaction. - The company of a transaction comes from one of three places: the session of the user, a public identifier that the database itself resolves, or the worker’s list of companies with work due. It never comes from a parameter of the request.
- The database role used by the API and the worker cannot bypass row level security, and owns no table.
- Anything that must act before a company is known, or across companies, is a database function from a closed list, in the schemas
gate,staffandsearch.
How the code applies it
Section titled “How the code applies it”Database roles
Section titled “Database roles”The roles are created by packages/db/sql/bootstrap-roles.sql. None is a superuser and none has BYPASSRLS.
| Role | Logs in | What it is for |
|---|---|---|
ochema_owner |
Yes | Owns the schemas and the tables. Used only to run migrations. |
ochema_app |
Yes | The runtime role of the API and the worker. Owns nothing, and every privilege it has is an explicit grant on one table or one function. |
ochema_gate |
No | Owns the functions of the gate schema. |
ochema_staff |
No | Owns the functions of the staff schema, the platform panel. |
ochema_search |
No | Owns the functions of the search schema. |
Every table has ENABLE and FORCE ROW LEVEL SECURITY, so the owner of the tables obeys the policies too.
The transaction context
Section titled “The transaction context”Two settings carry who is acting and in which company: app.user_id and app.tenant_id. The functions app.user_id() and app.tenant_id() read them (packages/db/migrations/0000_foundation_prelude.sql), and the policies call those functions.
withContext (packages/db/src/context.ts) opens a transaction and sets both values in its first statement, with set_config(..., true). The third argument makes the value local to the transaction: it ends on commit and on rollback, so a pooled connection goes back clean. Each value must be a UUID or the call throws before reaching the database.
---
config:
sequence:
width: 110
actorMargin: 20
---
sequenceDiagram
actor U as User<br/>(web app)
participant API
participant DB as PostgreSQL
U->>API: Request with the<br/>session cookie
API->>DB: gate.session_resolve<br/>(hash of the token)
DB-->>API: User and<br/>active company
API->>DB: BEGIN, then set<br/>app.user_id and<br/>app.tenant_id
loop Each statement of the route
API->>DB: Query
DB-->>API: Rows of that<br/>company only
end
API->>DB: COMMIT
Note over API,DB: Both settings end<br/>with the transaction
API-->>U: Response
SessionGuard(apps/api/src/core/auth/guards.ts) hashes the cookie token and callsgate.session_resolve. The active company is a column of the session row, checked against the memberships of the user. See Authentication and permissions.TransactionInterceptor(apps/api/src/core/http/transaction.interceptor.ts) wraps the route inwithContextwith the user and the company of the session. Every service of every module reads and writes through that one transaction (RequestContext.tx).- A statement without any company filter still returns only rows of that company. A statement that would write a row into another company fails the
WITH CHECKof the policy.
The policies
Section titled “The policies”A business table declares its policy with the helper appAll (packages/db/src/schema/helpers.ts), which produces <table>_app_all:
CREATE POLICY candidates_app_all ON candidates FOR ALL TO ochema_app USING (tenant_id = (SELECT app.tenant_id())) WITH CHECK (tenant_id = (SELECT app.tenant_id()));Three more properties of the schema support the policy:
- Foreign keys carry the company. A foreign key between two tables of a company is composite,
(tenant_id, id), so a row cannot point at a row of another company. The foreign keys withouttenant_idpoint at tables that belong to no company: users and the catalogs of the platform. - Privileges are explicit.
ochema_appreceivesSELECT,INSERT,UPDATEandDELETEtable by table, often column by column. Tables that must be kept have noDELETEat all. See ADR-0011. - Tables without
tenant_idhave policies too. They are the identity tables and the catalogs. A session reads the users who are members of its company, only its own sessions, and the companies it belongs to. Catalogs such aspermissionsandcompliance_rulesare read-only for the application.
One policy goes further than the company. The tables of the drug and alcohol file, and the rows of that vault in files, also call app.has_permission(...), so they exist only for a user who holds the permission. See Files and vaults.
Three ways a transaction gets its company
Section titled “Three ways a transaction gets its company”%%{init: {"flowchart": {"wrappingWidth": 170}}}%%
flowchart LR
session["<b>Request with<br/>a session</b><br/>Company kept<br/>in the session"]
public["<b>Request without<br/>a session</b><br/>Slug, number,<br/>key or token"]
worker["<b>Worker</b><br/>Company from the<br/>discovery or<br/>from the job"]
gatefn["<b>gate function</b><br/>public_context_by_*"]
ctx["<b>Transaction context</b><br/>app.tenant_id,<br/>app.user_id"]
rls["<b>Policies</b><br/>tenant_id =<br/>app.tenant_id()"]
session -->|"withContext"| ctx
public -->|"withPublicContext"| gatefn
gatefn -->|"Sets the company"| ctx
worker -->|"withContext,<br/>no user"| ctx
ctx --> rls
Legend. Box: a step. Arrow: the call that leads to the next step, labelled with the helper of packages/db/src/context.ts that makes it. Every path ends in the same policies.
With a session. Described above.
Without a session. A public page, a webhook or a link has no user. The route calls withPublicContext with a key, and the first statement of the transaction is a gate.public_context_by_* function. The function finds the company from an identifier that is unique in the platform, sets app.tenant_id for that transaction and returns only ids. The route code runs only if a row came back. From there the transaction is ochema_app under the same policies as a session of that company, with no user.
| The request presents | Function | Used by |
|---|---|---|
| Slug of the careers page | gate.public_context_by_slug |
Careers page, job page, application form |
| Code of a short link | gate.public_context_by_link |
/r/<code> |
| SHA-256 of an API key | gate.public_context_by_api_key |
Lead intake API |
| SMS number of the company | gate.public_context_by_sms_number |
Inbound SMS and SMS status webhooks |
| SHA-256 of a reply token | gate.public_context_by_reply_token |
Inbound email |
| Provider and its message id | gate.public_context_by_provider_message |
Bounce and complaint notices |
| SHA-256 of a scheduling token | gate.public_context_by_scheduling_token |
Interview scheduling page |
| SHA-256 of an unsubscribe token | gate.public_context_by_unsubscribe_token |
Unsubscribe page |
| SHA-256 of a calendar token | gate.public_context_by_calendar_token |
Calendar feed of a recruiter |
| SHA-256 of a full application token | gate.public_context_by_full_application_token |
Full application |
| SHA-256 of a portal token | gate.public_context_by_portal_token |
Candidate and driver area |
| SHA-256 of a signing token | gate.public_context_by_signing_token |
Signing page |
| SHA-256 of a safety history token | gate.public_context_by_safety_history_token |
Response of a previous employer |
The thirteen functions share four properties, each covered by tests:
- None takes a company id. The company is the result of the lookup.
- Each refuses, with an error, a transaction that already has a company or a user. They can only give a company to a transaction that has none, once.
- An identifier that is unknown, expired or revoked, and a suspended company, return no row and leave the context empty. The route then answers
404for a page or a link and401for an API key, and accepts a webhook without writing anything. - They return ids only. A webhook verifies the signature of the provider before it opens the transaction.
In the worker. A routine never reads a business table without a company. It asks gate.tenants_with_due_work(kind) which active companies have work of one kind due, outside any context, and then opens one withContext per company with no user. A job put on a queue by the API carries the company id in its body and opens its transaction the same way. See Background work.
The gate schema
Section titled “The gate schema”Every function of the schema is SECURITY DEFINER, owned by ochema_gate, with a fixed search_path. The role reads only the lookup columns each function needs, through select-only policies and column grants. The typed wrappers in packages/db/src/gate.ts and packages/db/src/context.ts are the only callers.
| Group | Functions | What ochema_app may do |
|---|---|---|
| Account flows that start without a company | register_tenant, login_lookup, session_resolve, invitation_lookup, accept_invitation, password_reset_request, password_reset_consume |
Execute |
| Public context | The thirteen public_context_by_* functions |
Execute |
| Work discovery | tenants_with_due_work |
Execute |
| Contact form of the landing site | submit_lead, mark_lead_notified |
Execute |
| Cleanup of expired rows | purge_expired, purge_expired_application_drafts |
Execute |
| Platform configuration read by the API | calendar_provider_config |
Execute |
| Drug and alcohol file, narrow answers | drug_alcohol_on_file, drug_alcohol_attach_person, drug_alcohol_set_termination |
Execute |
| Triggers that keep a derived column | For example candidate_consents_sync, which keeps candidates.consent_sms equal to the latest consent row |
Nothing: only the trigger runs them |
| Default data of a new company | The ensure_default_* functions |
Nothing: only migrations and register_tenant run them |
The trigger functions and the drug and alcohol functions are tied to the company of the transaction: the policies they run under compare tenant_id with app.tenant_id(), so they fail without a context instead of acting across companies.
The staff schema
Section titled “The staff schema”The platform team is not a company user and has no company context. The panel at /admin reaches the database only through functions of the staff schema, owned by ochema_staff. Each function receives the hash of the panel session token as its first argument and checks that session itself before doing anything, so no setting made by the API can stand in for a signed-in member. What the panel can do to a company is the list of those functions. See Authentication and permissions and ADR-0023.
The search schema
Section titled “The search schema”Text search is the one read that does not go through the company policy directly, because a policy keeps a LIKE condition out of the trigram index. Six functions, search.<table>_ids, cover candidates, people, vehicles, customers, jobs and locations. They belong to ochema_search, which may read four columns of those tables: id, tenant_id, archived_at and search_text. The function body filters by app.tenant_id() and returns ids only; without a context it returns nothing. The API then loads the rows by id in an ordinary query, so the policy checks every row again. See ADR-0022.
Outside the database
Section titled “Outside the database”- Files. Every object storage key starts with
tenants/<tenant_id>/, and the key is built by the server, never received. See Files and vaults. - Queues. A job carries the company id it was created for; the worker never takes it from anywhere else.
- Browser tabs. The office system sends the header
X-Ochema-Tenantwith the company the tab is showing. When another tab switched company, the API answers409 tenant_changedbefore anything runs. The header never selects the company.
What guarantees it
Section titled “What guarantees it”| Test file | What it proves |
|---|---|
packages/db/test/isolation.test.ts and the packages/db/test/isolation-wave*.test.ts files |
Table by table, as ochema_app: reads return only the rows of company A; updates and deletes aimed at rows of B change zero rows; inserts into B violate the policy; without a context every table returns zero rows; the context does not leak to the next transaction on the same connection. |
packages/db/test/privileges.test.ts |
No role is a superuser or bypasses row level security; every table has it enabled and forced; the runtime role owns no table; turning row_security off makes a query fail instead of returning everything. |
packages/db/test/public-context.test.ts, wave02-public-functions.test.ts, wave03-public-functions.test.ts, wave04-public-functions.test.ts |
Each public context function resolves only its own identifier, refuses a transaction that already has a context, returns nothing for an unknown, expired or revoked identifier and for a suspended company, and leaves no context on the connection. |
packages/db/test/gate.test.ts |
Sign-up, sign-in lookup, session resolution, invitations and password reset. |
packages/db/test/wave03-automation-functions.test.ts |
gate.tenants_with_due_work returns only ids of active companies, sets no context, fails inside a context and on an unknown kind. |
packages/db/test/volume-plans.test.ts |
A search term of company B returns no id in A; the search role reads only four columns; the six functions are definer functions of ochema_search. |
packages/db/test/staff-boundary.test.ts, staff-privileges.test.ts |
Every panel function refuses a call without a valid panel session; setting app.tenant_id gives the panel role no access. |
apps/api/test/isolation.test.ts |
Through the HTTP API: lists show only the active company, forged ids of another company change nothing, a user cannot switch into a company without a membership. |
apps/api/test/tenant-header.test.ts |
A request from a tab that shows another company answers 409 and writes nothing in either company. |
The API tests of each module carry their own isolation case as well, for example apps/api/test/audit-log.test.ts, messaging-settings.test.ts and drug-alcohol.test.ts.
Known limits
Section titled “Known limits”- One database for every company. All companies share the capacity and the maintenance windows of one database server. There is no option to place one company in a database of its own.
- One company at a time. A person who belongs to two companies switches between them. No screen, report or export combines the data of two companies, and two browser tabs cannot work in two companies at once.
- The platform team does not browse company data. Support can see the list of companies, turn modules on and off and set up the messaging sender of a company, because those are the functions the panel has. It cannot open the candidates, drivers or files of a company.
- Search needs three characters. A search term needs at least three letters or digits in a row, a consequence of how search is isolated.
- A new public entry point needs a database migration. Each kind of public link has its own function, by design.
Related
Section titled “Related”- ADR-0002: PostgreSQL with row level security per company.
- ADR-0003: the
gateschema for paths without a session. - ADR-0006: work discovery by company in the worker.
- ADR-0018: public tokens stored as hashes.
- ADR-0022: text search through definer functions.
- ADR-0023: the platform team as a separate identity.
- Authentication and permissions, Files and vaults, Background work.
- Building blocks: the request pipeline every route runs behind.
- Database tables: the generated list of tables, by module.