Every database query is scoped to the active tenant

A single unscoped query against a tenant-owned table is all it takes for one customer to read, overwrite, or delete another's data — the textbook definition of CWE-639 (Authorization Bypass Through User-Controlled Key) and OWASP A01 (Broken Access Control). In SaaS products this failure is catastrophic: it breaches contractual data-isolation guarantees, triggers GDPR and SOC 2 breach-notification obligations, and can expose trade-secret or PII data from every tenant in the database. The attack surface is wide because any route that touches a tenant-scoped table without a filter is exploitable by any authenticated user — no privilege escalation needed.

Enforce isolation at the database layer — application-level filters are too easy to miss in new routes. For Prisma, add a global middleware in src/lib/prisma.ts that injects the tenant scope on every query:

// src/middleware/tenantScope.ts
export function withTenantScope(tenantId: string) {
  return { $allOperations: ({ args, query }) => {
    args.where = { ...args.where, organizationId: tenantId };
    return query(args);
  }};
}

For Supabase, enable RLS on every tenant-owned table and write policies that read the org claim from the JWT — the anon/user client cannot bypass RLS. After wiring either approach, add a cross-tenant probe test: impersonate Tenant A, attempt to fetch a record whose organizationId is Tenant B — the query must return nothing.

• ID: saas-multi-tenancy.data-isolation.every-query-tenant-scoped

• Severity: critical

• What to look for: Examine all database query files — ORM queries, raw SQL, service layers, repositories. For every table that has a tenant identifier column (tenant_id, organization_id, team_id, workspace_id, or equivalent), verify that every SELECT, UPDATE, and DELETE query against that table filters by the tenant identifier. Check for ORM-level global scopes, Prisma middleware tenant filters, Supabase RLS policies, or consistent manual where clause patterns.

• Pass criteria: Enumerate all database query locations touching tenant-owned tables and confirm every query includes a tenant scope via one of: (a) database-enforced RLS that cannot be bypassed by application code. Quote the actual RLS policy or middleware configuration used, (b) ORM global filter applied at the connection/context level, (c) repository layer that injects tenant scope on all query methods, or (d) explicit where tenantId = [session tenant] on every individual query. Spot-check at least 5 different query locations across different entity types. On pass, report the count of scoped queries vs. total queries found.

• Fail criteria: Any query against a tenant-scoped table that omits the tenant filter — even a single instance — does not count as pass — even a single instance — constitutes a fail. Also fail if tenant filtering is inconsistent (some queries filtered, some not) without a global enforcement mechanism that covers the unfiltered ones.

• Skip (N/A) when: No database is detected (no ORM, no database dependencies, no schema files). Signal: absence of prisma, drizzle, typeorm, sequelize, mongoose, pg, mysql2, @supabase/supabase-js in package.json and no database schema files.

• Detail on fail: Describe which tables and query locations were found without tenant scoping. Example: "Queries against 'documents' table in src/lib/documents.ts and src/api/search/route.ts do not filter by organizationId. No global RLS or ORM scope found to compensate."

• Cross-reference: Compare with saas-multi-tenancy.data-isolation.no-cross-tenant-api-responses — query scoping (this check) prevents leaks at the DB layer; API response checks catch leaks at the transport layer.

• Remediation: Every query missing tenant scope is a potential cross-tenant data exposure. The safest approach is to enforce isolation at the database layer rather than in application code:

  For Supabase: Enable Row Level Security on every tenant-scoped table and write policies that filter by a tenant claim from the JWT. Application code cannot bypass RLS when using the anon/user client.

  For Prisma: Add a Prisma middleware or extension that injects where: { tenantId: ctx.tenantId } on every find/update/delete operation for scoped models. Alternatively, use the repository pattern where every repository method receives the tenantId as a required parameter.

  For Drizzle/raw SQL: Create a typed query builder factory that accepts a tenantId and wraps all queries with the appropriate where clause. Never call the base query builder directly in route handlers.

  After implementing, write a test that impersonates Tenant A's session and attempts to read Tenant B's records by ID — it should return nothing:

  // src/middleware/tenantScope.ts
  export function withTenantScope(tenantId: string) {
    return { $allOperations: ({ args, query }) => {
      args.where = { ...args.where, organizationId: tenantId };
      return query(args);
    }};
  }