tenant isolation

authorization code decides who may act; only the schema guarantees every row carries one unambiguous owner. tenant scope living solely in route checks means one forgotten guard, refactored query, or new reporting path leaks another tenant's data invisibly. a durable owner key on every row makes correct scoping the default and turns a would-be breach into a zero-row query — defense in depth, not a single forgettable boundary.

tenant ownership is a durable schema fact, not a route convention. every tenant-scoped row has an explicit owner key; every uniqueness constraint, index, query, aggregate, export, and maintenance job begins from that scope.

authorization decides whether a principal may access a row; the schema ensures the row has one unambiguous owner. neither replaces the other.

• make (workspace_id, authored_key) unique when a name is local to a tenant.
• make owner/filter/order indexes start with workspace_id for tenant reads.
• include tenant scope in aggregate and idempotency identities.
• derive worker eligibility from tenant-scoped durable rows, not a queue payload.
• require an explicit operator boundary for exports, support tooling, and cross-tenant reports; log the actor, scope, and purpose.

bad

// route handler relies on authorization check alone
app.get("/api/videos/:videoId", async (req, res) => {
  const user = await auth.verify(req.headers.authorization); // ✓ user authenticated
  const permission = await checkPermission(user.id, req.params.videoId); // ✓ user has permission

  // query uses only the record id, trusting authorization already ran
  const video = await db.select().from(videos).where(eq(videos.id, req.params.videoId));

  if (!video) return res.status(404).json({ error: "not found" });
  return res.json(video);
});

if the permission check is skipped, buggy, or missing from an internal service call, the query returns a valid row from any tenant — the schema enforces nothing. authorization-only boundaries fail under scale or refactoring: (1) internal service call forgets the tenant check, (2) conditional authorization misses a code path, (3) schema changes without the authorization logic, (4) a new reporting query never gets the authorization logic. the row has no durable proof of ownership, so the leak is invisible until a user or audit log finds it.

good

-- every tenant-owned row must have workspace_id
create table videos (
  id text primary key,
  workspace_id text not null references workspace(id),
  title text not null,
  created_at text not null,
  unique (workspace_id, id) -- ensure id is scoped by workspace
);

-- every read of tenant data includes workspace scope
create index videos_by_workspace on videos(workspace_id, id);

// route handler uses both authorization and schema to enforce boundary
app.get("/api/videos/:videoId", async (req, res) => {
  const user = await auth.verify(req.headers.authorization);
  const permission = await checkPermission(user.id, req.params.videoId); // ✓ authorization

  // query includes workspace scope—the schema guarantees this is the correct owner
  const video = await db
    .select()
    .from(videos)
    .where(
      and(
        eq(videos.workspace_id, user.workspace_id), // ✓ durable schema fact
        eq(videos.id, req.params.videoId),
      ),
    );

  if (!video) return res.status(404).json({ error: "not found" });
  return res.json(video);
});

// every maintenance job also scopes by workspace
async function deleteOldVideos(workspaceId: string) {
  const cutoff = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
  await db.delete(videos).where(
    and(
      eq(videos.workspace_id, workspaceId), // ✓ cannot accidentally delete another tenant's data
      lt(videos.created_at, cutoff.toISOString()),
    ),
  );
}

workspace_id is a durable foreign-key column, so every row carries explicit proof of ownership. every query and job begins from that scope, so a missing authorization check or refactored query cannot leak another tenant's data. the index keeps workspace-scoped reads fast. if authorization is forgotten in one place, the schema still stops the query at the tenant boundary — defense in depth, not authorization alone.

authorization · identifiers and keys · query cost · aggregate design