Skip to content

ADR-0003: Gate schema with definer functions for paths without a session

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

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?

  • 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.
  1. SECURITY DEFINER functions in a schema of their own, gate, owned by a role that cannot log in.
  2. 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.
  3. Functions that receive the company id as a parameter. Rejected: the isolation would depend on the API never passing the wrong id.

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), sets app.tenant_id for 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.
  • 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.ts and the wave0*-public-functions.test.ts files).
  • 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.ts and packages/db/src/context.ts are 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.
  • packages/db/migrations/0003_gate_functions.sql, 0024_wave02_public_functions.sql, 0038_wave03_automation_functions.sql and 0051_wave04_public_functions.sql: the functions.
  • packages/db/src/context.ts: withPublicContext and the list of public keys.
  • Concept pages: Tenant isolation, Authentication and permissions.
  • Related: ADR-0018, ADR-0022, ADR-0023.