Multi-tenant data isolation in a shared PostgreSQL schema

FlowBoard is a project management tool built for teams: organizations, projects, tasks, and role-based access. The core requirement is that Organization A and Organization B share the same application and the same database, but their data is completely invisible to each other.

The naive version of this is easy to state: just filter every query by organization. The problem is that a codebase large enough to have multiple developers has no reliable way to enforce that by convention alone. You are one forgotten WHERE clause away from returning another tenant's tasks in a response, and that bug is silent. Nobody panics when data is missing. They do not always notice when extra data appears.

Alongside isolation, the platform needed real-time collaborative updates: when a teammate moves a task, the Kanban board updates without a reload. Those WebSocket broadcasts also needed to be org-scoped. A message about Organization A's tasks cannot go to a connection belonging to Organization B.

Three approaches to multi-tenant isolation come up in every discussion of this problem. Separate database per tenant: the best isolation, the worst operational story. Separate schema per tenant: slightly better operationally, still bad for migrations. Shared schema with a discriminator column: simplest to operate, migrations are global, and isolation is entirely a query discipline problem.

For a personal project on a single database, shared schema was the only realistic option. The question shifted from which approach to how to enforce the discipline reliably.

The failure mode in shared-schema multi-tenancy is a query that runs against the right table but omits the organization scope. The data comes back clean. The response looks correct. The isolation is gone. This is the class of bug that appears in code review as an oversight and in production as a data breach.

Every table that holds tenant data has an organization_id foreign key referencing the organizations table. There are no exceptions. The JWT issued at login carries an organization_id claim. A NestJS auth guard extracts that claim and attaches it to the request context. Repository methods receive the org ID from request context and include it in every query. The scope is injected, not looked up.

For WebSocket broadcasts: Redis pub/sub channels are namespaced by organization ID. Task updates publish to org:{id}:updates. WebSocket connections subscribe to their organization's channel on authentication. A message for Organization A never reaches a connection that authenticated as Organization B.

For background jobs: Graphile Worker job payloads always include organization_id. The worker reads it from the payload and scopes all database operations accordingly. The tenant context travels with the job, not inferred at processing time.

Role-based access sits above the isolation layer. A user's role (owner, admin, member) is stored as a membership record joining users to organizations. Permission checks run after the organization scope is established, not instead of it.