LeadRefinery — How the Platform Works

Multi-tenancy and isolation

Traced at commit ffe4e48 · libs/temporal/src/activities/db.ts · libs/db/migrations/*.sql (49 policies) · libs/temporal/src/activities/dial-gate.ts

Summary

  1. One database holds the data of every client.
  2. The database enforces the separation. The application does not.
  3. Each database call runs in a transaction. The transaction first sets three session values.
  4. 49 row-level security policies read those values.
  5. The application connects with a role that is not the table owner. A policy therefore always applies.
  6. Two crossings are deliberate. This page lists both.
  7. A missing filter in a query returns fewer rows. It does not return the rows of another client.
Every database call runs inside a transaction that first sets three session values: the user, the client, and whether the caller is a super administrator. Forty-nine row-level security policies then filter every table to the current client. The application connects as a role that is not the table owner, so the policies always apply. Two crossings are deliberate: background work runs as a super administrator scoped to one client, and the stop list is readable across all clients so one person does not receive calls from several clients at once. A separate box explains that the connection pool is a shared limit and not an isolation boundary.
The database refuses the read. The query does not have to remember to ask correctly.

How the platform isolates one call

  1. A request arrives. It carries a user and a client.
  2. withRlsContext opens a transaction.
  3. The transaction sets app.user_id, app.tenant_id and app.is_super_admin.
  4. The system sets these values with set_config(..., true). The values are transaction-local.
  5. A leaked connection therefore cannot pass the context of one client to the next request.
  6. The connection uses the role app_api. This role does not own the tables.
  7. The role has no BYPASSRLS attribute. Nothing can skip a policy.
  8. Each policy has the same shape: is_super_admin = true OR tenant_id = app.tenant_id.
  9. The query returns the rows of one client. The rows of other clients do not exist to that query.

Note. Isolation does not depend on the query. A developer who forgets a WHERE tenant_id clause gets fewer rows than expected. That developer does not get the rows of another client.

Crossing 1 — background work

  1. Background work has no signed-in user.
  2. The worker calls systemRlsContext(tenantId).
  3. This function sets is_super_admin to true.
  4. The policies then permit every row.
  5. The work still names one client and stays inside that client.
  6. This escape is the mechanism for background work. It is not a defect.
  7. Read any call to systemRlsContext as a claim that the code that follows is client-safe by construction.

Crossing 2 — the stop list

  1. A stop record can have the scope GLOBAL.
  2. The safety gate matches a GLOBAL record from any client.
  3. Without this rule, a person who asks one client to stop still receives calls from the other clients.
  4. The person does not know that the platform serves several clients.
  5. See Opt-outs for the records and The dial gate for the check.

The connection pool is not an isolation boundary

  1. The worker pool holds 36 connections.
  2. A rolling deploy runs the old worker and the new worker at the same time.
  3. Two pools must therefore fit inside the database limit of 100 connections.
  4. An earlier value of 48 exhausted the limit.
  5. The database then refused psql itself.
  6. The failure looked like a database outage. The cause was a pool size.
  7. Set WORKER_DB_POOL_MAX only after you confirm the connection limit of the server.

Opt-outs — the one record type that crosses clients on purpose.