One Database, Many Companies: How ZenStack Policies Replace WHERE Clauses

Every QC record in AIMQC belongs to an organization. No company sees another's data. We enforce that isolation not with manual WHERE clauses scattered across hundreds of queries, but with access policies baked into the schema itself. The Prisma client that reaches the database is already scoped — automatically, for every query, on every model.

The multi-tenancy problem

AIMQC is a multi-tenant SaaS. A welding contractor in Fort McMurray and a pipeline operator in Edmonton are both users of the same system, on the same database. Their ITPs, inspection records, NCRs, and weld logs must never mix.

The naive solution is to add WHERE organizationId = $currentOrg to every query. It works until someone forgets. It works until a new developer adds a query and doesn't know the convention. It works until a join traverses a relation and the filter is not propagated. On a system with hundreds of models and thousands of queries, "remember to filter" is not a security strategy.

ZenStack policy model

We use ZenStack as a layer over Prisma. ZenStack lets us define access policies directly on the schema model. These policies are enforced by an enhanced Prisma client — the same client our application code uses for every database operation.

model ITPIndex {
  id            String @id @default(uuid())
  organizationId String
  organization  Organization @relation(fields: [organizationId], references: [id])
  // ... other fields

  @@allow('read', auth().organizationId == organizationId)
  @@allow('create', auth().organizationId == organizationId)
  @@allow('update', auth().organizationId == organizationId && auth().role != 'VIEWER')
  @@allow('delete', auth().organizationId == organizationId && auth().role == 'ADMIN')
}

The auth() function returns the current user from the request context. The @@allow directives define who can perform each operation. The enhanced Prisma client translates these policies into SQL at query time — automatically, without the application code needing to think about it.

What this means in practice

Application code queries the database exactly as it would without ZenStack:

// No organizationId filter needed — the policy enforces it
const itps = await db.iTPIndex.findMany()

The enhanced client appends the policy condition before the query reaches PostgreSQL. An inspector logged in as Org A gets only Org A's ITPs. Not because every developer remembered to add the filter. Because the filter is structurally impossible to forget.

Role-based access within an organization

Policies compose. The same model can have rules that vary by user role:

The SysAdmin role bypasses organization filters entirely — necessary for support operations and migrations. This is explicit in the schema, not a hidden superuser path:

@@allow('all', auth().role == 'SYSADMIN')

Child model delegation

For models that belong to a parent (an ITP step belongs to an ITP, which belongs to an organization), ZenStack provides check() to delegate the policy to the parent:

model ITPStep {
  itpId String
  itp   ITPIndex @relation(...)

  @@allow('read', check(itp, 'read'))
  @@allow('update', check(itp, 'update'))
}

The step inherits the parent's access rules. No duplication. If the org policy on ITPIndex changes, ITPStep gets the update automatically.

The payoff

The access model is auditable. The schema is the source of truth for who can see and do what. A security review of AIMQC's access control is a review of the .zmodel file — not a grep across a thousand query files hoping no filter was missed.

In a regulated industry where data isolation is a compliance requirement, "the schema enforces it" is a better answer than "we trust our developers to remember."

David Olsson is CTO at AIMQC. Contact: dolsson@aimqc.com