Bulletproof shared-schema isolation

Shared-schema multi-tenancy is unforgiving.

Every table stores many tenants. Every query must isolate the current tenant. Every missed where tenant_id = ... is a potential data breach.

That is not a developer discipline problem. It is an architecture problem.

For Northstar Health Group, the failure is not theoretical. A support dashboard opened while helping Northstar US could accidentally show overdue invoices for Northstar EU, or worse, another customer entirely. That is a breach even if the developer only forgot one filter.

Tenant isolation belongs in infrastructure, not every service method

The enterprise problem space

This looks harmless:

const invoices = await prisma.invoice.findMany({
  where: { status: "overdue" },
});

In a shared-schema SaaS database, that query may read every tenant’s overdue invoices.

The fix is not “remember to add the filter.” Humans forget. The platform must make unsafe access difficult.

Failure mode: support dashboard leak

The bug usually enters through useful code:

export async function findOverdueInvoicesForDashboard() {
  return prisma.invoice.findMany({
    where: { status: "overdue" },
    orderBy: { due_at: "asc" },
  });
}

Nothing about that function looks malicious. It is still unsafe. In a shared-schema system, the absence of tenant context is a production incident waiting to happen.

Repository as domain collection

The application layer should ask for tenant-owned aggregates through a repository:

const configuration = await tenantConfigurationRepository.get(tenantId);

It should not know storage details:

// Not in application code.
await prisma.tenantConfiguration.findFirst({
  where: {
    tenant_id: tenantId,
    deleted_at: null,
  },
});

The repository is not just abstraction theater. It is the place where domain access can be forced through tenant-aware infrastructure.

Tenant context

The current tenant should come from authenticated execution context, not a hand-passed optional parameter:

type TenantExecutionContext = {
  tenantId: TenantId;
  actorId: UserId;
  correlationId: CorrelationId;
};

const tenantContext = new AsyncLocalStorage<TenantExecutionContext>();

export function currentTenantId(): TenantId {
  const context = tenantContext.getStore();
  if (!context) throw new Error("tenant context missing");
  return context.tenantId;
}

HTTP requests, jobs, and saga handlers all enter through this context.

Global query filtering

With Prisma, a client extension can inject tenant filters:

const tenantPrisma = prisma.$extends({
  query: {
    $allModels: {
      async findMany({ model, args, query }) {
        if (tenantScopedModels.has(model)) {
          args.where = {
            ...args.where,
            tenant_id: currentTenantId(),
          };
        }

        return query(args);
      },
    },
  },
});

The same idea applies to TypeORM subscribers, query builders, or a custom repository layer.

The rule is simple:

Standard tenant-scoped reads and writes should be isolated automatically.

Test the guardrail

Test the infrastructure behavior directly:

it("adds the current tenant filter to tenant-scoped reads", async () => {
  const parsedTenantId = tenantId("ten_claims_us");

  await withTenantContext(tenantExecutionContext({ tenantId: parsedTenantId }), async () => {
    await invoiceRepository.findOverdue();
  });

  expect(prisma.invoice.findMany).toHaveBeenCalledWith(
    expect.objectContaining({
      where: expect.objectContaining({
        tenant_id: parsedTenantId,
      }),
    }),
  );
});

Also test the failure path:

it("rejects tenant-scoped reads without tenant context", async () => {
  await expect(invoiceRepository.findOverdue()).rejects.toThrow("tenant context missing");
});

The first test prevents accidental leakage. The second prevents background jobs from silently running outside a tenant boundary.

Zero-trust exceptions

Some queries are legitimately cross-tenant: billing rollups, platform operations, migration checks. Those should be explicit and rare:

await platformRepository.withCrossTenantAccess("monthly billing rollup", async () => {
  return billingReportRepository.calculateGlobalUsage();
});

Make cross-tenant access noisy in code, logs, and review.

Database safeguards

Application filters are necessary but not sufficient.

Use database-level constraints where possible:

• composite indexes starting with tenant_id;
• foreign keys that include tenant_id;
• row-level security for supported databases;
• views or stored policies for sensitive tables;
• migration tests that scan for tenant-scoped tables without tenant indexes.

Example composite foreign key:

alter table tenant_users
add constraint tenant_users_tenant_fk
foreign key (tenant_id, tenant_configuration_id)
references tenant_configurations (tenant_id, id);

The schema should make cross-tenant joins hard to express accidentally.

Layered isolation

No single guardrail is enough for high-risk tenant data.

For lower-risk internal tables, repository plus global filters may be enough. For invoices, identities, PHI, financial data, or customer content, add database-level enforcement too.

No-downtime migrations

Shared-schema migrations affect every tenant. Treat them like production events:

1. Add nullable column or new table.
2. Backfill in tenant-sized batches.
3. Dual-write from application code.
4. Verify per-tenant completeness.
5. Flip reads.
6. Remove old column later.

Do not ship a migration that locks a hot table across thousands of tenants because the domain model wanted a new invariant.

The takeaway

Never require developers to manually add tenant filters to standard queries.

Repositories express domain access. Execution context identifies the tenant. Infrastructure injects isolation. Database constraints provide the final guardrail.

In shared-schema enterprise SaaS, tenant isolation is not a convention. It is a system property.

Principles to apply in your own work

1. Inventory every tenant-scoped table.
2. Find raw ORM calls that bypass repositories.
3. Make tenant context required for standard repository methods.
4. Add tests proving tenant filters are injected.
5. Add composite keys or row-level security for the highest-risk tables.
6. Make cross-tenant access explicit, logged, and reviewed.

Next: Bringing the onboarding engine together.