Firewall - Data Isolation

The firewall generates WHERE clauses automatically to isolate data by user, organization, or team.

Basic Usage

The cleanest pattern is q.scope() — declare the tenant column once and the firewall is auto-derived:

// features/candidates/candidates.ts
import { feature, q } from '@quickback/compiler';

export default feature('candidates', {
  columns: {
    id:             q.id(),
    name:           q.text().required(),
    email:          q.text().required(),
    organizationId: q.scope('organization'),  // Auto-derives the firewall
  },
  // No firewall: block needed — auto-derived as
  //   [{ field: 'organizationId', equals: 'ctx.activeOrgId' },
  //    { field: 'deletedAt', isNull: true }]
  // ... guards, crud, read
});

If you need to declare the firewall explicitly, two forms are accepted — pick whichever reads better:

Named-scope (compact, default for simple overrides):

firewall: { organization: { column: 'tenant_id' } }
firewall: { owner: { column: 'account_user_id' } }
firewall: { exception: true }   // opt out of tenant scoping entirely

Predicate array (explicit, required for in-lists or custom non-scope predicates):

firewall: [
  { field: 'organizationId', equals: 'ctx.activeOrgId' },
  { field: 'deletedAt', isNull: true },
]

Both shapes compile to the same canonical predicate array internally — the named-scope object is coerced at parse time. Pick by readability.

Auto-detected column synonyms

When you omit the firewall: block entirely, the compiler scans the schema for one of these isolation columns and auto-derives the predicates:

ownerId is not auto-detected — it's reserved for business-logic ownership (who owns this record), not access control. A table that has only ownerId and no isolation column raises a targeted error explaining the distinction; either rename to userId if the column controls access, add an isolation column, or specify firewall: explicitly.

Predicate Shape

type FirewallPredicate =
  | { field: string; equals: string }       // column = ctx.* (or literal)
  | { field: string; isNull: true }         // column IS NULL
  | { field: string; in: string[] | string }// column IN (...)
  | { field: string; via: string }          // column IN (relationship subquery)
  | { exception: true };                    // opt out of tenant scoping

type FirewallConfig = ReadonlyArray<FirewallPredicate>;

// Resource top-level (sibling to `firewall:`)
firewallErrorMode?: 'reveal' | 'hide';

Predicates are AND'd together. firewallErrorMode lives at the resource top level — it's a presentation-layer choice (403 vs 404), not a query-shape one.

Relationship-based scoping (via:)

For relationship authorization — "this user can see rows X if they have a confirmed event_guests row pointing at X" — declare the relationship under authz.relationships in quickback.config.ts, then reference it from firewall: with a via: predicate:

// quickback.config.ts
authz: {
  relationships: {
    guestOf: {
      from: 'event_guests',                                 // table name
      subject: { column: 'userId', equals: 'ctx.userId' },  // caller side
      resource: { column: 'eventId' },                      // resource side
      where: { status: 'confirmed' },                       // optional row filter
    },
  },
}

// features/sessions/sessions.ts
firewall: [
  { field: 'organizationId', equals: 'ctx.activeOrgId' },
  { field: 'eventId', via: 'guestOf' },
]

Compiles to:

WHERE sessions.organizationId = ?
  AND sessions.eventId IN (
        SELECT eventId FROM event_guests
        WHERE userId = ?
          AND status = 'confirmed'
          AND <event_guests' own firewall — tenant scope + soft-delete>
      )

Key properties:

• Firewall stays the outermost AND. A guest of an event in another tenant is still blocked by your tenant predicate. Relationship-based scoping never bypasses tenant isolation.
• The relationship table's own firewall is AND'd into the subquery. Soft-deleting a event_guests row revokes access immediately; cross-tenant relationship rows can't grant access.
• Anonymous callers fail closed. ctx.userId is null-guarded; the subquery short-circuits to WHERE 1 = 0 for unauthenticated requests.
• The relationship table must be tenant-scoped (firewall: { exception: true } is rejected at compile time on relationship tables).

See Access — relationships for the full authz.relationships shape and role-binding patterns.

Context Sources

When equals references the request context, use these standard sources:

These values are populated by the auth middleware from the user's session. Any predicate where equals starts with ctx. is treated as server-derived: the column lands in GUARDS_CONFIG.systemManaged (clients cannot submit it) and is auto-populated on create / upsert from the context.

Auto-Derivation

When the firewall: block is omitted, the compiler scans the schema for known scope columns and synthesises the predicate array:

q.scope(kind) is the canonical way to declare the column — auto-derivation works the same way for plain q.text().required() or Drizzle text(...).notNull() columns named to match the convention.

Rules:

• Exactly one isolation column → auto-derived.
• Zero columns AND no PUBLIC route → compile-time error ("missing isolation column").
• Two or more (e.g. organizationId + ownerId) → compile-time error; declare firewall: explicitly.

What { exception: true } Actually Does

{ exception: true } disables tenant scoping only — the generated buildFirewallConditions() emits no tenant WHERE clauses. It does not affect:

• Audit field injection. createdAt, modifiedAt, createdBy, modifiedBy, deletedAt, deletedBy are always added to every table unless you disable them globally with compiler.features.auditFields: false.
• Soft-delete filtering. If the table has a deletedAt column (which it does by default, thanks to audit injection), the firewall still emits isNull(<table>.deletedAt) so soft-deleted records stay hidden. To opt out of soft-delete filtering on an exception resource, either set compiler.features.auditFields: false globally or hand-author a schema without deletedAt.

So firewall: [{ exception: true }] on the default configuration returns isNull(<table>.deletedAt) from buildFirewallConditions() — no tenant filter, soft-delete filter still applied.

Sysadmin Escape Hatch

When cms: { sysadmin: true } is set on the project (introduced in v0.15.0), every buildFirewallConditions() emits a runtime branch that drops tenant scopes for ctx.userRole === 'sysadmin' callers while keeping the soft-delete predicate:

// Generated for an org-scoped table when cms.sysadmin: true
export function buildFirewallConditions(ctx: AppContext): SQL | undefined {
  if (ctx.userRole === 'sysadmin') {
    return isNull(invoices.deletedAt);  // tenant predicate dropped, soft-delete kept
  }
  return and(
    eq(invoices.organizationId, ctx.activeOrgId!),
    isNull(invoices.deletedAt),
  );
}

The escape branch is only emitted when:

• cms.sysadmin: true is set on the project config
• The table has at least one tenant-scope predicate (org / owner / team / via: relationship). Tables with no tenant scopes already return what a sysadmin would see, so the runtime check would be dead code.
• The firewall does not declare { exception: true } (no tenant scopes to drop).

Sysadmin is distinct from admin. userRole === 'admin' callers still go through the firewall normally — only userRole === 'sysadmin' triggers the bypass. Admins keep their existing universal-bypass at the access layer (cross-org reads on per-record routes via ?organizationId=), but the firewall's row predicate remains in force. Sysadmin is opt-in, configured per-project, and intended for the cross-tenant CMS DB-admin tier — see CMS Configuration.

If your project doesn't set cms.sysadmin: true, the escape branch is never emitted and the role value is functionally inert.

Common Patterns

// Public/system table - no tenant filtering (soft-delete still applied)
firewall: [{ exception: true }]

// Organization-scoped (auto-derived when organizationId is present — block can be omitted)
firewall: [
  { field: 'organizationId', equals: 'ctx.activeOrgId' },
  { field: 'deletedAt', isNull: true },
]

// User-owned personal data
firewall: [
  { field: 'ownerId', equals: 'ctx.userId' },
  { field: 'deletedAt', isNull: true },
]

// Multi-tenant + per-team scoping
firewall: [
  { field: 'organizationId', equals: 'ctx.activeOrgId' },
  { field: 'teamId', equals: 'ctx.activeTeamId' },
  { field: 'deletedAt', isNull: true },
]

// Custom ctx field
firewall: [
  { field: 'workspaceId', equals: 'ctx.activeWorkspaceId' },
]

// Status-gated (literal value, not ctx-derived — does NOT add to systemManaged)
firewall: [
  { field: 'organizationId', equals: 'ctx.activeOrgId' },
  { field: 'status', in: ['active', 'pending'] },
]

Error Responses

When a record isn't found behind the firewall (wrong org, soft-deleted, or genuinely doesn't exist), the error response depends on firewallErrorMode:

Reveal Mode (Default)

Returns 403 with a structured error:

{
  "error": "Record not found or not accessible",
  "layer": "firewall",
  "code": "FIREWALL_NOT_FOUND",
  "hint": "Check the record ID and your organization membership"
}

This is developer-friendly and helps debug access issues during development.

Hide Mode

Returns 404 with a generic error:

{
  "error": "Not found",
  "code": "NOT_FOUND"
}

Use firewallErrorMode: 'hide' for security-hardened deployments where you don't want attackers to distinguish between "record exists but you can't access it" and "record doesn't exist":

export default defineTable(records, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  firewallErrorMode: 'hide',  // Opaque 404s prevent record enumeration
});

Foreign-Key Referential Checks

Generated POST / and POST /batch handlers validate every foreign-key column against the target table's firewall before inserting. If a record references a row that the caller can't see (wrong org, soft-deleted, or non-existent), the request fails with:

{
  "error": "Referenced jobs row not found",
  "code": "FK_NOT_FOUND",
  "layer": "validation",
  "field": "jobId"
}

This closes a cross-tenant injection vector: even if an attacker knows a foreign tenant's row ID, they cannot attach a child record to it, and they cannot trigger a cross-tenant cascade delete. The check uses the target table's buildXFirewallConditions(ctx) helper, so it honors whatever firewall predicates the target declared (organization, owner, team, custom). FKs to tables with { exception: true } get a plain existence check (still verifies the row exists, doesn't tenant-scope it).

The check runs after guards but before the insert, so it appears as layer: "validation" in error responses. Status code is 400, not 404 — returning 404 would leak existence of out-of-scope rows.

Pre-Record ID-Probe Defense

Independent of firewallErrorMode, generated /:id handlers run a role-only access check before the firewall query (see Access ordering). A caller without the right role gets 403 before the database is touched, so they can't distinguish "row exists out-of-scope" from "row doesn't exist" via response timing or status differences.

firewallErrorMode controls what happens after the role check passes but the firewall hides the row — i.e., when an authorized caller asks for a record in another tenant. Use 'hide' to make that case indistinguishable from a non-existent ID.

Rules

• Every resource MUST have at least one tenant predicate OR { exception: true } OR a PUBLIC route
• { exception: true } cannot be combined with tenant predicates in the same array

Why Can't You Mix { exception: true } with Tenant Predicates?

They represent opposite intentions:

• { exception: true } = "Don't generate tenant WHERE clauses, data is public/global"
• Tenant predicates = "Generate tenant WHERE clauses to isolate data"

Combining them would be contradictory — you can't both isolate and not isolate. (Note: soft-delete filtering is independent of this decision, see above.)

Handling "Some Public, Some Private" Data

If you need records that are sometimes public and sometimes scoped, you have two options:

Option 1: Two Separate Tables

Split into two tables - one public, one scoped:

// features/job-templates/template-library.ts - Public job templates
export default defineTable(templateLibrary, {
  firewall: [{ exception: true }],
  // ...
});

// features/job-templates/custom-templates.ts - Org's custom job templates
export default defineTable(customTemplates, {
  firewall: [{ field: 'ownerId', equals: 'ctx.userId' }],
  // ...
});

Option 2: Use Access Control Instead

Keep tenant scope but make access permissive, then control visibility via access:

// features/jobs/jobs.ts
export default defineTable(jobs, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],
  read: {
    // Anyone in the org can list — visibility differences are handled
    // via record-level access conditions on /:id
    access: { roles: ["owner", "hiring-manager", "recruiter", "interviewer"] },
  },
  // Use record conditions to allow public jobs OR owned jobs on GET /:id
  // (single-record GET inherits read.access, with optional record-level overrides)
  // ...
});

Which to Choose?