Multi-Tenancy

What multi-tenancy means

Multi-tenancy means your app serves multiple organizations (tenants) from the same codebase and database. Acme Corp and Globex Inc share the same server, the same tables, and the same code. But they must never see each other’s data.

In the auth course, data isolation was per-user (include user_id in every query). In this course, it is per-organization (include org_id in every query). The principle is the same — the scope is larger.

The scoping rule

Every database query that returns tenant-specific data must include the org_id. There are no exceptions.

// WRONG — returns notes from ALL organizations
db.prepare("SELECT * FROM notes").all();

// WRONG — scopes by user but not by org (a user in multiple orgs sees everything)
db.prepare("SELECT * FROM notes WHERE created_by = ?").all(user.id);

// CORRECT — scoped to the specific organization
db.prepare("SELECT * FROM notes WHERE org_id = ?").all(orgId);

Our routes already do this because they take orgId from the URL path (/orgs/:orgId/notes). But there are subtler ways to break scoping.

Subtle scoping bugs

Bug 1: Joining without scoping

// WRONG — finds a note by ID without checking its org
const note = db.prepare("SELECT * FROM notes WHERE id = ?").get(noteId);

// CORRECT — note must belong to the org
const note = db.prepare("SELECT * FROM notes WHERE id = ? AND org_id = ?").get(noteId, orgId);

This is the IDOR pattern from the web security course, but applied to organization scoping. A note ID from another organization should not be accessible.

Bug 2: Counting or aggregating across orgs

// WRONG — counts notes across all organizations
const count = db.prepare("SELECT COUNT(*) as c FROM notes").get();

// CORRECT — counts notes in the specific org
const count = db.prepare("SELECT COUNT(*) as c FROM notes WHERE org_id = ?").get(orgId);

Bug 3: Forgetting to scope in updates and deletes

// WRONG — could delete a note from another org if the ID is guessed
db.prepare("DELETE FROM notes WHERE id = ?").run(noteId);

// CORRECT — scoped to the org
db.prepare("DELETE FROM notes WHERE id = ? AND org_id = ?").run(noteId, orgId);

Enforcing scoping

A common pattern is to create query helpers that always include the org_id:

// src/queries/notes.ts
import db from "../db.js";

export function listNotes(orgId: string) {
  return db.prepare("SELECT * FROM notes WHERE org_id = ? ORDER BY created_at DESC").all(orgId);
}

export function getNote(noteId: string, orgId: string) {
  return db.prepare("SELECT * FROM notes WHERE id = ? AND org_id = ?").get(noteId, orgId);
}

export function deleteNote(noteId: string, orgId: string) {
  return db.prepare("DELETE FROM notes WHERE id = ? AND org_id = ?").run(noteId, orgId);
}

Route handlers call these functions instead of writing raw SQL. The scoping is centralized and cannot be accidentally forgotten:

route.get("/orgs/:orgId/notes/:noteId", {
  resolve: (c) => {
    const user = authenticate(c.request);
    if (user instanceof Response) return user;

    const perm = requirePermission(user, c.params.orgId, "notes:read");
    if (perm instanceof Response) return perm;

    const note = getNote(c.params.noteId, c.params.orgId);
    if (!note) return Response.json({ error: "Not found" }, { status: 404 });

    return Response.json(note);
  },
}),

Exercises

Exercise 1: Review every db.prepare call in your routes. Does every one include org_id?

Exercise 2: Create the src/queries/notes.ts helper module. Refactor the notes routes to use it.

Exercise 3: Log in as Alice and try to access a Globex note via the Acme endpoint (GET /orgs/org-acme/notes/note-3). The note exists in Globex but the query is scoped to Acme, so it should return 404.

Why must every query include org_id, even when the note ID is unique?

Access Required