ADR-0003: Gate schema with definer functions for paths without a session
- Status: Accepted
- Date: 2026-11-12
Context and problem statement
Section titled “Context and problem statement”With row level security (ADR-0002), a transaction without a company context reads nothing. Some flows start exactly there: company sign-up, sign-in, accepting an invitation and resetting a password. Later the same need appeared on public paths: the careers page found by its slug, a short link, a lead intake API key, an inbound SMS found by the number it arrived at, and links that carry a token (scheduling, unsubscribe, full application, signing, candidate area, previous employer reply).
How does a request with no session reach the right company without weakening the isolation?
Decision drivers
Section titled “Decision drivers”- The API role never receives the privilege to bypass row level security.
- The exceptions must be few, listed in one place and tested one by one.
- A public request must never choose the company by passing its id.
Considered options
Section titled “Considered options”SECURITY DEFINERfunctions in a schema of their own,gate, owned by a role that cannot log in.- Give the API role, or a second connection of the API, the privilege to bypass row level security. Rejected when the decision was made: the API never receives that privilege.
- Functions that receive the company id as a parameter. Rejected: the isolation would depend on the API never passing the wrong id.
Decision outcome
Section titled “Decision outcome”Chosen option: 1.
The role ochema_gate owns the functions, cannot log in and cannot bypass row level security. It reads only the lookup columns it needs, through select-only policies. The API role may execute the functions and nothing else changes for it. The functions fall into these groups:
- Account flows without a company:
gate.register_tenant, sign-in lookup, session resolution, invitation and password reset. - Public context: thirteen
gate.public_context_by_*functions. Each one finds the company by an identifier that is unique in the platform (a slug, a short link code, an SMS number, the message id of an email provider, or the SHA-256 of a key or token), setsapp.tenant_idfor that transaction only and returns only ids. An unknown, expired or revoked identifier, or a suspended company, returns no row and leaves the context empty. Each function refuses a transaction that already has a company or a user. After the call the transaction continues as the API role, under the same policies as a company session, with no user. - Work discovery for the worker:
gate.tenants_with_due_work, see ADR-0006. - Triggers that keep derived columns, the default data of a new company, and the narrow functions of the drug and alcohol file, see ADR-0016.
Consequences
Section titled “Consequences”- Good, because the exceptions to the isolation are a closed list in versioned migrations, each with tests (
packages/db/test/gate.test.ts,public-context.test.tsand thewave0*-public-functions.test.tsfiles). - Good, because a public route cannot be pointed at another company: the company comes from the lookup, never from a parameter.
- Bad, because every new public entry point needs a migration with a new function.
- Bad, because this logic is written in PL/pgSQL, outside the TypeScript type checker. Typed wrappers in
packages/db/src/gate.tsandpackages/db/src/context.tsare the only callers. - Bad, because a definer function runs with the rights of its owner, so each one needs a review that ordinary application code does not.
More information
Section titled “More information”packages/db/migrations/0003_gate_functions.sql,0024_wave02_public_functions.sql,0038_wave03_automation_functions.sqland0051_wave04_public_functions.sql: the functions.packages/db/src/context.ts:withPublicContextand the list of public keys.- Concept pages: Tenant isolation, Authentication and permissions.
- Related: ADR-0018, ADR-0022, ADR-0023.