Multi-tenant app

You get a notes table shared by two tenants, a client scoped to one tenant at a time, and proof that a scoped read cannot see the other tenant's rows. The program below shows the tenant key in the SQL, the unstable-order warning (OKM2005) and its fix, and the runtime error for a mistyped key. A compile error and a testing() isolation check follow it.

Detail lives on Tenancy, Tenant isolation, and Testing.

Setup

You need a Postgres database, the notes table with its tenant column, and a client.

The table comes from the schema in the program (app). The test that runs this page renders the catalog (app.catalog) to DDL, the SQL that creates tables, and executes each statement against an empty database. In your own project, create it with bunx okm push on a database you can recreate, or okm generate init then okm migrate apply for reviewed migrations (First migration). For this schema the DDL is:

create table "public"."notes" (
  "createdAt" text not null,
  "id" uuid not null,
  "tenantId" uuid not null,
  "title" text not null,
  constraint "notes_pkey" primary key ("id", "tenantId")
);
alter table "public"."notes" add constraint "notes_id_tenantId_key" unique ("id", "tenantId");
alter table "public"."notes" add constraint "notes_tenantId_title_key" unique ("tenantId", "title");

columnTenancy added tenantId to the table and put it in the primary key and in every unique. That makes title unique per tenant, not across tenants.

The client, called db throughout, comes from connect(url, { schema }). An app keeps one in a module:

import { connect } from "okmodel/pg/postgresjs";
import { app } from "./tenancy.ts";

export const db = connect(process.env.DATABASE_URL!, { schema: app });

db has no notes property of its own, because every table here is a tenant table. A tenant table is reachable only through db.for({ tenantId }). A scoped client, such as a and b below, filters every read and write on that key.

Run it

Both tenant ids and every note id come from crypto.randomUUID(), so the function can run again on the same database without a collision. The printout replaces each id with a label.

tenancy.ts

import { connect } from "okmodel/pg/postgresjs";
import { id, schema, table, text } from "okmodel/pg";
import { columnTenancy } from "okmodel/tenancy";
import { warnings } from "okmodel/warnings";
import { OkmError } from "okmodel";

const notes = table("notes", {
  id: id({ default: "none" }),
  title: text().unique(),
  createdAt: text(),
});
export const app = schema({
  tenancy: columnTenancy({ key: "tenantId", type: "uuid" }),
  tables: [notes],
});

const UUID = /[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}/g;

export async function isolate(url: string): Promise<void> {
  await using db = connect(url, {
    schema: app,
    warnings: warnings({
      env: "development",
      onWarning: (notice) => {
        console.log(`${notice.code}: ${notice.summary}`);
        console.log(`Fix: ${notice.fix.summary}`);
      },
    }),
  });
  const tenantA = crypto.randomUUID();
  const tenantB = crypto.randomUUID();
  const noteA = crypto.randomUUID();
  const noteB = crypto.randomUUID();
  const names = new Map<string, string>([
    [tenantA, "<tenant a>"],
    [tenantB, "<tenant b>"],
    [noteA, "<note a>"],
    [noteB, "<note b>"],
  ]);
  const say = (label: string, value: unknown): void => {
    const text = typeof value === "string" ? value : JSON.stringify(value);
    console.log(`${label}: ${text}`.replace(UUID, (found) => names.get(found) ?? found));
  };
  const a = db.for({ tenantId: tenantA });
  const b = db.for({ tenantId: tenantB });

  const plan = await a.notes.insert({ id: noteA, title: "A", createdAt: "1" }).sql();
  say("insert sql", plan.statements[0]?.text ?? "");
  say("insert params", plan.statements[0]?.params);
  await a.notes.insert({ id: noteA, title: "A", createdAt: "1" });
  await b.notes.insert({ id: noteB, title: "B", createdAt: "1" });

  const list = { orderBy: { title: "asc" }, limit: 10 } as const;
  const read = await a.notes.find(list).sql();
  say("find sql", read.text);
  say("find params", read.params);
  say("tenant a sees", (await a.notes.find(list)).map((row) => row.title));
  say("tenant b sees", (await b.notes.find(list)).map((row) => row.title));

  say("a counts b's note", await a.notes.count({ where: { id: noteB } }));
  const steal = { where: { id: noteB }, set: { title: "taken" } } as const;
  say("update sql", (await a.notes.update(steal).sql()).statements[0]?.text ?? "");
  say("a updates b's note", await a.notes.update(steal));
  say("b still sees", (await b.notes.find(list)).map((row) => row.title));

  await a.notes.find({ limit: 2, orderBy: { createdAt: "asc" } });
  await a.notes.find({ limit: 2, orderBy: { createdAt: "asc", id: "asc" } });

  const typo = { id: crypto.randomUUID(), title: "x", createdAt: "1", tenatId: tenantA };
  try {
    await a.notes.insert(typo);
  } catch (error) {
    if (error instanceof OkmError) say(error.code, error.message);
  }
  const forged = { id: crypto.randomUUID(), title: "y", createdAt: "1", tenantId: tenantB };
  try {
    await a.notes.insert(forged);
  } catch (error) {
    if (error instanceof OkmError) say(error.code, error.message);
  }
}

tenancy.out

insert sql: insert into "notes" ("id", "title", "createdAt", "tenantId") values ($1::uuid, $2::text, $3::text, $4::uuid) returning "id", "title", "createdAt", "tenantId"
insert params: ["<note a>","A","1","<tenant a>"]
find sql: select t."id", t."title", t."createdAt", t."tenantId" from "notes" t where t."tenantId" = $1 order by t."title" asc nulls last limit $2
find params: ["<tenant a>","10"]
tenant a sees: ["A"]
tenant b sees: ["B"]
a counts b's note: 0
update sql: update "notes" as t set "title" = $1::text where t."tenantId" = $2 and t."id" = $3
a updates b's note: {"count":0}
b still sees: ["B"]
OKM2005: A limit whose order does not end in a unique key can return a different row set each time. Under for(), the tenant key is ignored, so the rest of that unique counts.
Fix: End orderBy with the primary key or a unique column.
OKM1120: Field tenatId is not on notes. Accepted names: createdAt, id, title. Did you mean `tenantId`?
OKM1190: Field notes.tenantId is the tenant key. The scope sets it. Input cannot.

What each step ran

1. Insert stamps the key

The insert SQL lists "tenantId" as a column and $4::uuid as its value, although the call passed only id, title, and createdAt. for() supplied the key. The last value in insert params is the tenant id.

2. Read filters on the key

The read adds t."tenantId" = $1 and you wrote no where. Tenant A gets its own row and tenant B gets its own. A count of tenant B's note id from tenant A's client is 0. The update SQL carries the same filter next to the id condition, so an update of that note changes { count: 0 } rows. The filter is part of the same statement as the write, so there is no gap between a check and the write.

3. OKM2005 under `for()`

An orderBy under a limit has to end in a unique key. Otherwise two pages of the same query can disagree about which rows they hold. Under for() the tenant key is already pinned, so it does not count toward that unique tail. orderBy: { createdAt: "asc" } warns, and the warning prints the fix. orderBy: { createdAt: "asc", id: "asc" } is quiet.

title is unique per tenant, so orderBy: { title: "asc" } is enough inside a scope. It did not warn above.

4. OKM1120 and the tenant key at runtime

A key that is not a column is OKM1120. The accepted names omit the tenant key, and the hint can still name tenantId when the typo is close. A value that names the tenant key is refused too: the scope sets it, and input cannot. The code is OKM1190.

The program builds both objects in a variable first. TypeScript rejects an unknown key only in an object literal written at the call. An object held in a variable passes as long as it has the required fields, and a payload parsed from JSON reaches insert that way. The runtime check is the one that catches it.

The same typos at compile time

Written inline, as an object literal, the typo never compiles:

await a.notes.insert({ id: crypto.randomUUID(), title: "x", createdAt: "1", tenatId: tenantA });

error TS2769: No overload matches this call.
  The last overload gave the following error.
    Object literal may only specify known properties, and 'id' does not exist in type 'readonly (RequiredInsert<InsertFrom<{ readonly id: ColumnBuilder<string, IdSuppliedFlags>; readonly title: ColumnBuilder<string, PlainFlags>; readonly createdAt: ColumnBuilder<...>; }> & InsertFrom<...>> & OptionalInsert<...>)[]'.

tsc prints this, without the file and line prefix. It reports against the last overload of insert, so the message names id rather than tenatId. The cause is the extra key: remove it and the call compiles. Passing tenantId in the literal is the same kind of error, and db.notes on the unscoped client is Property 'notes' does not exist.

Test isolation

testing(app, ...).isolation() seeds a row as tenant B, then reads and writes it as tenant A, and fails with the table and the query if any call sees it. It picks new tenant ids every call, so it is safe to run repeatedly. migrate: false tells testing the table already exists. The report names what it checked.

isolation.ts

import { open } from "okmodel/pg/postgresjs";
import { testing } from "okmodel/testing";
import { app } from "./tenancy.ts";

export async function prove(url: string): Promise<void> {
  const harness = await testing(app, { driver: open({ url }), migrate: false });
  harness.factories({ notes: (x) => ({ title: x.words(2), createdAt: "1" }) });
  console.log(JSON.stringify(await harness.isolation()));
  await harness.close();
}

isolation.out

{"checked":["notes"],"skipped":[],"views":[]}

In a test, assert on report.checked. A tenant table with no factory fails with Factory notes is missing. A leak fails with the table, the query, and the row. For rlsTenancy, use a non-superuser role on real Postgres; see Testing.

What can go wrong

Code	When	Fix
OKM1701	A tenant table is used on a client with no tenant context.	Go through `db.for({ tenantId })`.
OKM1190	Input sets the tenant key.	Remove it; `for()` sets it.
OKM1704	An update tries to change the tenant key.	Remove the key from `set`. A row stays in its tenant.
OKM1120	A key is not a field on the table.	Use an accepted name.
OKM2005	`orderBy` under a limit does not end in a unique key.	End it with `id` or another unique column.

Next	You use it for
Tenancy	`global`, `via`, composite keys, and RLS.
Warnings	Levels and ignores for OKM2005.

On this page