Multi-tenancy & data isolation

Every tenant row belongs to one org, and one forgotten predicate leaks it. This page answers how a request names its org, where the active org lives, what isolates the data, what proves the isolation, and when a read may cross orgs.

Put the org in the path; OrgScope proves membership

Impact: HIGH a path parameter cannot go missing; session state can

• Every tenant endpoint lives in a group with .prefix("/orgs/:orgId") and the OrgScope middleware. Endpoints that act on no org (list my orgs, create an org, accept an invite) go in a separately named group. Every endpoint’s params spreads OrgParams, because HttpApiClient substitutes only the parameters its schema names.
• Add OrgScope before Authentication: in 4.0.3 the last .middleware() call is outermost and runs first. .prefix() and both .middleware() calls come after every .add().
• A non-member, a missing org and an undecodable id are all a 404 OrgNotFound. A failed membership lookup is a PersistenceError, never a “no”. OrgScope annotates app.orgs.org_id once. CurrentOrg carries the org id only; permissions are still resolved by authorization.

❌ Incorrect — the org in a header or the session, answered with 403:

const orgId = request.headers["x-org-id"] ?? session.activeOrgId   // missing → falls back
if (!isMember) return yield* new Forbidden()                       // confirms the org exists

✅ Correct — the org is part of the address:

export const ProjectsApi = HttpApiGroup.make("projects")
  .add(get, list, create)
  .prefix("/orgs/:orgId")
  .middleware(OrgScope)        // added first = runs second: it needs CurrentPrincipal
  .middleware(Authentication)  // added last = runs first: it provides CurrentPrincipal

const get = HttpApiEndpoint.get("get", "/projects/:id", {
  params: { ...OrgParams, id: ProjectId },
  success: Project,
  error: ProjectNotFound,
})

Source: notes/12-security-and-trust/multi-tenancy-and-data-isolation.md · Decision 1, amended

Read CurrentOrg from R in services; pass orgId first to repositories

Impact: HIGH every caller must say which org, and the value is already proven

• CurrentOrg is provided only at an entry point (per request, per job iteration, per message, per outbox row) with Effect.provideService, never in an application Layer. Tests are the exception. An outbox subscriber provides it from event.orgId, with a System principal.
• A repository has no CurrentOrg in its R. It takes orgId: OrgId first, so the SQL is explicit and a repository test passes two orgs with no context.
• A service calling another feature’s service inherits the caller’s org. Only a job loop provides a new one. Same rule as CurrentPrincipal in authentication.

❌ Incorrect — the service trusts whatever org it is handed:

readonly get: (orgId: OrgId, id: ProjectId) => Effect.Effect<Project, ProjectNotFound>
// HTTP passes the path value, a job passes a body field: nobody proved membership

✅ Correct — the service reads the proven org; a job provides it per org:

const get = Effect.fn("Projects.get")(function* (id: ProjectId) {
  const org = yield* CurrentOrg
  return yield* Effect.fromOption(
    yield* repo.findById(org.orgId, id),
    () => new ProjectNotFound({ projectId: id }),
  )
})

Effect.forEach(orgIds, (orgId) =>
  cleanup.run.pipe(
    Effect.provideService(CurrentOrg, { orgId }),
    Effect.provideService(CurrentPrincipal, Principal.cases.System.make({ job: "projects/stale-cleanup" })),
  ))

Source: notes/12-security-and-trust/multi-tenancy-and-data-isolation.md · Decision 2, amended

Pin org_id in every tenant query

Impact: HIGH the only mechanism that works on every dialect and runtime profile

• A tenant table has org_id NOT NULL REFERENCES orgs (id). Every read, update and delete pins org_id, including by-id lookups. An insert takes orgId from the argument, never the payload.
• Every unique constraint on a tenant table leads with org_id, so a name taken in one org does not block or reveal another.
• Another org’s id finds no row, so “a tenant mismatch is NotFound” needs no policy comparison. Non-tenant tables (orgs, org_members, identity’s users and sessions) are named in orgs/’s README. See SQL and transactions.

❌ Incorrect — load by id, then hope the service compares orgs:

const findById = (id: ProjectId) =>
  findOneOption(sql`SELECT * FROM projects WHERE id = ${id}`)

✅ Correct — the predicate in the query, the org in the index:

const findById = (orgId: OrgId, id: ProjectId) =>
  findOneOption(sql`SELECT * FROM projects WHERE org_id = ${orgId} AND id = ${id}`)

CREATE INDEX projects_org_idx ON projects (org_id);
CREATE UNIQUE INDEX projects_org_name_idx ON projects (org_id, name);

Source: notes/12-security-and-trust/multi-tenancy-and-data-isolation.md · Decision 3

Prove isolation with a two-org test per repository method

Impact: HIGH only running the SQL against two orgs sees the WHERE clause

• One test per method, named “<method> never sees another org”. Seed a row in one org, call the method with another. A read returns None or []; a write changes nothing, proved by reading back with the owning org’s id.
• seedTwoOrgs (in apps/server/src/orgs/) is shared and runs on the real embedded database. A mocked repository proves nothing. See testing.
• Review checks that a new tenant repository method arrives with its test. A grep for org_id in SQL is not a guard: it passes WHERE org_id = 'x' OR 1=1.

❌ Incorrect — a textual check that passes the bug:

# CI: every repo.ts SQL string must mention org_id
grep -L 'org_id' apps/server/src/*/repo.ts

✅ Correct — run the statement against two orgs:

it.effect("rename never touches another org's row", () =>
  Effect.gen(function* () {
    const { acme, globex } = yield* seedTwoOrgs
    const p = yield* seedProject(globex.id)
    yield* repo.rename(acme.id, p.id, "pwned")
    const after = yield* repo.findById(globex.id, p.id)
    assert.strictEqual(Option.getOrThrow(after).name, p.name)
  }))

Source: notes/12-security-and-trust/multi-tenancy-and-data-isolation.md · Decision 4

Name cross-org reads …AcrossOrgs; return ids and counts only

Impact: MEDIUM a finite, greppable list; content stays behind CurrentOrg

• A method name ending in AcrossOrgs is the only thing that omits the org predicate, so rg 'AcrossOrgs' is the complete list. It returns ids, counts or sizes, never content rows; per-org work goes back through CurrentOrg and the normal methods.
• justification is a required argument, recorded on the span as app.orgs.cross_org_justification. On day 1 only a System principal calls these methods.
• No caller bypasses the org predicate: not a role, a flag, or an orgId | "all" parameter. Direct database access can read any org; the product states that openly as policy.

❌ Incorrect — an admin bypass on the normal method:

const list = (orgId: OrgId | "all", filter: ProjectFilter) =>
  orgId === "all" ? findAll(sql`SELECT * FROM projects`) : /* … */

✅ Correct — a named, ids-only, justified read:

const staleOrgIdsAcrossOrgs = Effect.fn("ProjectsRepo.staleOrgIdsAcrossOrgs")(function* (
  olderThan: DateTime.Utc,
  justification: string,
) {
  yield* Effect.annotateCurrentSpan("app.orgs.cross_org_justification", justification)
  return yield* findAll(sql`SELECT DISTINCT org_id FROM projects WHERE updated_at < ${olderThan}`)
})

Source: notes/12-security-and-trust/multi-tenancy-and-data-isolation.md · Decision 5

Deferred

• Composite (org_id, id) keys and org-carrying foreign keys — trigger: the first incident in which a row references a row in another org.
• A structural tenant guard on SQL — trigger: the first cross-org read or write that reached a deployed environment past the two-org tests.
• A human caller of AcrossOrgs reads (a platform-admin principal or permission) — trigger: the first endpoint that shows cross-org metadata to a human.
• A support “act as” path into one org’s content (time-boxed, audited, told to the tenant) — trigger: the first support case that cannot be resolved without seeing a tenant’s content.