# Tenancy

Tenancy keeps each tenant's rows apart inside one database. You declare it once on the schema. Every table then stores a tenant key, and every read and write through a scoped client is filtered to one tenant without you writing the filter.

Four terms are used on this page:

- **Tenant key**: the column that says which tenant owns a row. Here it is `tenantId`, a `uuid`.
- **Guarded**: input cannot set the column. The tenant key is a guarded uuid: an insert takes it from the scope, and a `where` or `set` that names it is refused. [Visibility](/docs/schema/visibility) covers guarded columns in general.
- **Scope**: the client that `db.for({ tenantId })` returns. Every call on it reads and writes that tenant's rows only.
- **Tenant filter**: the SQL condition the scope adds, such as `t."tenantId" = $1`.

The tenancy code lives in `okmodel/tenancy` and loads only when you import it.

## The model

The program below declares column tenancy, inserts a note as one tenant, reads it back as that tenant and as another, and prints the SQL. `plans` is a global table, shared by every tenant.

```ts title="tenancy-model.ts"
import { connect } from "okmodel/pg/postgresjs";
import { schema, table, t } from "okmodel/pg";
import { columnTenancy, global } from "okmodel/tenancy";

const notes = table("notes", { id: t.identity(), title: t.text() });
const plans = table(
  "plans",
  { id: t.identity(), code: t.text() },
  { tenancy: global("plans are shared") },
);

export const app = schema({
  tables: [notes, plans],
  tenancy: columnTenancy({ key: "tenantId", type: "uuid" }),
});

const acme = "018f3b2c-7a11-7c3a-8e2d-6b4a9c1d0e5f";
const globex = "018f3b2c-7a11-7c3a-8e2d-6b4a9c1d0e60";

export async function isolate(url: string): Promise<void> {
  await using db = connect(url, { schema: app });
  const scoped = db.for({ tenantId: acme });

  console.log("insert:", JSON.stringify(await scoped.notes.insert({ title: "Hello" })));

  const find = scoped.notes.find({ where: { id: "1" }, limit: 1 });
  const inspected = await find.inspect();
  console.log("find sql:", inspected.sql.text);
  console.log("find params:", JSON.stringify(inspected.sql.params));
  console.log("find rule:", JSON.stringify(inspected.rules.find((rule) => rule.rule === "tenancy")));
  console.log("acme rows:", (await find).length);
  const other = db.for({ tenantId: globex });
  console.log("globex rows:", (await other.notes.find({ where: { id: "1" }, limit: 1 })).length);

  const update = await scoped.notes.update({ where: { id: "1" }, set: { title: "Hi" } }).sql();
  console.log("update sql:", update.statements[0]?.text);

  const shared = await db.plans.find({ limit: 1 }).inspect();
  console.log("plans sql:", shared.sql.text);
  console.log("plans rule:", JSON.stringify(shared.rules.find((rule) => rule.rule === "tenancy")));
}
```

```text title="tenancy-model.out"
insert: {"id":"1","title":"Hello","tenantId":"018f3b2c-7a11-7c3a-8e2d-6b4a9c1d0e5f"}
find sql: select t."id", t."title", t."tenantId" from "notes" t where t."tenantId" = $1 and t."id" = $2 limit $3
find params: ["018f3b2c-7a11-7c3a-8e2d-6b4a9c1d0e5f","1","1"]
find rule: {"rule":"tenancy","contribution":"scoped","provenance":"planner"}
acme rows: 1
globex rows: 0
update sql: update "notes" as t set "title" = $1::text where t."tenantId" = $2 and t."id" = $3
plans sql: select t."id", t."code" from "plans" t limit $1
plans rule: {"rule":"tenancy","contribution":"global plans are shared","provenance":"catalog","source":"tenancy-model.ts:6"}
```

- The insert names only `title`. The scope set `tenantId`, and the row comes back with it.
- The read added `t."tenantId" = $1` before your `where`. `inspect()` lists that as the `tenancy` rule.
- The same id read as `globex` returns no row.
- The update is filtered the same way, so one tenant cannot change another's row.
- `plans` has no tenant column and no filter. Its `tenancy` rule records the reason you gave.

These are the tables the schema creates. The tenant key is part of the primary key, and `notes` also gets a unique constraint on `(id, tenantId)`. `plans` has no key:

```sql title="tenancy-model.sql"
create table "public"."notes" (
  "id" bigint generated always as identity 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");

create table "public"."plans" (
  "code" text not null,
  "id" bigint generated always as identity not null,
  constraint "plans_pkey" primary key ("id")
);
```

## Scoped and unscoped clients

| Client | Tenant tables | Global tables |
| --- | --- | --- |
| `db` | Not on the client: `db.notes` is a type error and `undefined` at run time. | Read and write. |
| `db.for({ tenantId })` | Read and write that tenant's rows. Insert takes the key from the scope. | Read and write. |
| `db.unscoped("reason")` | Read and write every tenant's rows, with no tenant filter. Insert is refused, because there is no key to store. | Read and write. |

`for()` takes a uuid string. `unscoped()` needs a reason, which `inspect()` records. Use it for a support tool or a migration script, not for request handling.

## Choose a tenancy mode

`tenancy` on `schema()` is one of three functions from `okmodel/tenancy`. Omit it and no table gains a key, `for()` is not on the client, and a table that sets `tenancy` is refused.

| Mode | Key | Enforcement |
| --- | --- | --- |
| `columnTenancy({ key, type: "uuid" })` | One column. | The tenant filter on every call. |
| `compositeTenancy({ key: [...], type: "uuid" })` | Several columns, in order. `for()` takes every one. | The tenant filter on every call. |
| `rlsTenancy({ key, type: "uuid" })` | One column. | The tenant filter, plus row-level security (RLS) policies in Postgres. |

`type` is `"uuid"` in every mode.

### Composite

A tenant inside a tenant, such as a workspace inside an organization:

```ts title="composite.ts"
import { connect } from "okmodel/pg/postgresjs";
import { compositeTenancy } from "okmodel/tenancy";
import { schema, table, t } from "okmodel/pg";

const documents = table("documents", {
  id: t.identity(),
  title: t.text(),
});

const app = schema({
  tables: [documents],
  tenancy: compositeTenancy({
    key: ["organizationId", "workspaceId"],
    type: "uuid",
  }),
});

export async function add(url: string, organizationId: string, workspaceId: string, title: string) {
  await using db = connect(url, { schema: app });
  return await db.for({ organizationId, workspaceId }).documents.insert({ title });
}
```

A unique index includes every key, so `index(c.title).unique()` stores `(organizationId, workspaceId, title)`. An ordinary index is prefixed with the first key only.

### Row-level security

Row-level security (RLS) is a Postgres feature: a policy on a table decides which rows a role can see or change. `rlsTenancy()` keeps the tenant filter and adds policies, so a query that bypasses okmodel is still limited to one tenant.

```ts title="rls.ts"
import { rlsTenancy } from "okmodel/tenancy";
import { schema, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

export default schema({
  tables: [notes],
  tenancy: rlsTenancy({ key: "tenantId", type: "uuid" }),
});
```

The migration creates two policies on `notes`: `notes_tenant` for the tenant set in the transaction setting `app.tenant`, and `notes_unscoped_select` for reads when `app.unscoped` is set. A view that exposes the key is created `security_invoker`, so the policies apply through it. `unscoped()` can read and cannot write. A key list and `via()` are refused.

On connect, okmodel checks that the role cannot skip the policies: it must not own the tables, be a superuser, or have `BYPASSRLS` ([OKM1707](/docs/reference/errors#okm1707)), and each table must force RLS with the expected policies ([OKM1708](/docs/reference/errors#okm1708)). A passing check is cached for that endpoint until its generation changes. A failed check is not cached, and the message names the endpoint.

<Callout type="warning" title="What row-level security does not stop">
The policies trust the transaction settings. A role that can run arbitrary SQL can set `app.tenant` to any tenant and read or write that tenant's rows. A reporting role can set `app.unscoped` and read every tenant through the select policy, although it still cannot write. RLS protects against a query that forgets the filter, not against a role that sets the settings on purpose. [Tenant isolation](/docs/concepts/tenant-isolation) covers where the filter applies and how `unscoped` and `trusted` leave it off.
</Callout>

## Tables that differ from the default

A table with no `tenancy` option stores the schema's key. Two marks from `okmodel/tenancy` change that.

### Global tables

`global("reason")` leaves the tenant key off the table. Every client reads and writes it with no tenant filter, as `plans` does in the model. The reason is stored and `inspect()` shows it. A global table cannot reference a tenant table.

### Tables that reach the key through a parent

`via("project.organization")` says the table does not store the key. Its tenant is found by following to-one relations, each step a `one(...)` relation name, separated by `.`, up to three steps. The last table on the path stores the key. Every read and write adds an `exists` condition along that path, and an insert checks that the parent belongs to the scope's tenant.

```ts title="path.ts"
import { connect } from "okmodel/pg/postgresjs";
import { one, schema, table, t } from "okmodel/pg";
import { columnTenancy, via } from "okmodel/tenancy";

const organizations = table("organizations", {
  id: t.identity(),
  name: t.text(),
});

const projects = table(
  "projects",
  {
    id: t.identity(),
    organizationId: t.bigint().references("organizations"),
    name: t.text(),
  },
  {
    relations: { organization: one("organizations") },
    tenancy: via("organization"),
  },
);

const tasks = table(
  "tasks",
  {
    id: t.identity(),
    projectId: t.bigint().references("projects"),
    title: t.text(),
  },
  {
    relations: { project: one("projects") },
    tenancy: via("project.organization"),
  },
);

const app = schema({
  tables: [organizations, projects, tasks],
  tenancy: columnTenancy({ key: "tenantId", type: "uuid" }),
});

export async function open(url: string) {
  await using db = connect(url, { schema: app });
  const scoped = db.for({ tenantId: "018f3b2c-7a11-7c3a-8e2d-6b4a9c1d0e5f" });
  const org = await scoped.organizations.insert({ name: "Acme" });
  const project = await scoped.projects.insert({ name: "Ship", organizationId: org.id });
  return await scoped.tasks.insert({ title: "Write", projectId: project.id });
}
```

`organizations` stores `tenantId`. `projects` reaches it through `organization`, and `tasks` through `project.organization`.

## Uniques and indexes

The tenant key is added to constraints so that one tenant's values never collide with another's:

- The primary key and every unique constraint include the key, so a value is unique inside one tenant and two tenants may store the same value.
- A foreign key to another tenant table includes the key, so `set null` and `set default` are refused ([References](/docs/schema/references)).
- An ordinary index is prefixed with the key, so it serves the tenant filter. A unique index gains every key.

`.unique({ global: "reason" })` leaves the key out of one unique constraint:

```ts title="unique.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  email: t.text().unique({ global: "login email" }),
  title: t.text().unique(),
});
```

`email` is unique across all tenants. `title` is unique inside one tenant.

The index prefix can be set at three levels. The nearest level wins:

| Level | Setting | Effect |
| --- | --- | --- |
| Schema | `indexes` on the tenancy function, default `true` | `false` leaves every index as written. An index that does not start with the key is then refused (OKM1706). |
| Table | `tenantIndexes: false` | Leaves this table's indexes as written. Overrides the schema. |
| Index | `.global("reason")` | Leaves this index as written. Overrides the table. |
| Index | `.scoped()` | Prefixes this ordinary index. Overrides the table. |

An index that already names the key gets it moved to the front, not listed twice. Two indexes that become the same columns are refused (OKM1023). A `using` index is never prefixed; [Indexes](/docs/schema/indexes) explains the `.global("reason")` call it needs. The catalog, `okm generate`, and `describe()` show the resolved columns.

```ts title="indexes.ts"
import { columnTenancy } from "okmodel/tenancy";
import { index, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), email: t.text(), title: t.text() },
  {
    indexes: (columns) => [
      index(columns.email).unique(),
      index(columns.title).global("shared title"),
    ],
  },
);

export default schema({
  tenancy: columnTenancy({ key: "tenantId", type: "uuid" }),
  tables: [notes],
});
```

The `email` index is `(tenantId, email)`, unique inside one tenant. The `title` index stays `(title)`.

When you turn tenancy on for an existing project, an index whose columns already start with the key does not change. Any other index is replaced once: `okm generate` writes `CREATE INDEX CONCURRENTLY` for the new index, then `DROP INDEX CONCURRENTLY` for the old one, so the table is not locked for a rebuild. `okm migrate check` accepts that pair, and the next `okm generate` writes nothing.

## Errors

The messages below use the tables on this page. Your table and key names replace `notes` and `tenantId`.

### Declaring the schema

These are thrown by `schema()`, before any connection.

| Code | When | Message |
| --- | --- | --- |
| [OKM1060](/docs/reference/errors#okm1060) | A table sets `tenancy` and the schema does not. | `Table notes sets tenancy, and the schema does not.` |
| [OKM1060](/docs/reference/errors#okm1060) | A table's `tenancy` is not `global()` or `via()`. | `Table notes tenancy must be global("reason") or via().` |
| [OKM1060](/docs/reference/errors#okm1060) | An empty reason or path. | `global() needs a reason.` / `via() needs a relation path.` / `unique() global needs a reason.` |
| [OKM1060](/docs/reference/errors#okm1060) | A key type other than uuid. | `columnTenancy() type text must be uuid.` |
| [OKM1060](/docs/reference/errors#okm1060) | A composite key names a column twice. | `compositeTenancy() key repeats organizationId.` |
| [OKM1060](/docs/reference/errors#okm1060) | `rlsTenancy()` with a key list. | `rlsTenancy() takes one key. Combining it with compositeTenancy or via() is not supported yet.` |
| [OKM1060](/docs/reference/errors#okm1060) | An index setting with no schema tenancy. | `Table notes sets tenantIndexes, and the schema has no tenancy.` / `Table notes index().global() needs tenancy.` |
| [OKM1012](/docs/reference/errors#okm1012) | The table already declares the key column. | `Table notes already declares tenantId, which tenancy adds. Rename the field or drop it from the table.` |
| [OKM1023](/docs/reference/errors#okm1023) | Two indexes become the same after the prefix. | `Index on notes (title) and (tenantId, title) both become (tenantId, title).` |
| [OKM1705](/docs/reference/errors#okm1705) | A global table references a tenant table. | `Table plans.code is global and references tenant table notes. Drop the reference, or make plans a tenant table.` |
| [OKM1705](/docs/reference/errors#okm1705) | A `via()` step is not a to-one relation, or the path is too long, ends on a global table, or loops. | `Table tasks tenancy via "project" has no to-one relation project.` The message names the step. |
| [OKM1706](/docs/reference/errors#okm1706) | With the prefix off, an index does not start with the key. | `Index on notes starts with title. Put tenantId first.` |

### Calls and connect

| Code | When | Message |
| --- | --- | --- |
| [OKM1701](/docs/reference/errors#okm1701) | An insert on a tenant table without `for()`. | `insert on notes has no tenant. Call for({ tenantId }) so the scope can set it.` |
| [OKM1701](/docs/reference/errors#okm1701) | The root client reaches a tenant table, for example through an `include` from a global table. | `Table notes is tenant-scoped. Call for({ tenantId }).` |
| [OKM1701](/docs/reference/errors#okm1701) | `for()` gets no key, or a value that is not a string. | `for() needs { tenantId } as a uuid.` |
| [OKM1210](/docs/reference/errors#okm1210) | `for()` gets a string that is not a uuid. | `uuid nope must be 8-4-4-4-12 hexadecimal digits, for example 01890c5a-8f0e-7c3a-9b2d-6e4f1a0b9c8d.` |
| [OKM1701](/docs/reference/errors#okm1701) | A write through `unscoped()` under `rlsTenancy()`. | `unscoped() cannot write under row-level security.` |
| [OKM1190](/docs/reference/errors#okm1190) | An insert names the key. | `Field notes.tenantId is the tenant key. The scope sets it. Input cannot.` |
| [OKM1704](/docs/reference/errors#okm1704) | An update or a `where` names the key. | `Field notes.tenantId is the tenant key. An update cannot change it.` / `Field notes.tenantId is the tenant key. A filter cannot set it.` |
| [OKM1705](/docs/reference/errors#okm1705) | A `via()` insert names a parent in another tenant. | `Insert on tasks did not find a parent in this tenant.` |
| [OKM1707](/docs/reference/errors#okm1707) | Under `rlsTenancy()`, the role owns the tables, is a superuser, or has `BYPASSRLS`. | `The connected role is a superuser, so row-level security would not apply. Endpoint primary.` |
| [OKM1708](/docs/reference/errors#okm1708) | Under `rlsTenancy()`, a table does not force RLS or its policies differ. | `Table notes does not force row-level security. Endpoint primary.` |

| Next | You use it for |
| --- | --- |
| [Multi-tenant guide](/docs/guides/multi-tenant) | A full program that isolates two tenants and checks it with `isolation()`. |
| [Tenant isolation](/docs/concepts/tenant-isolation) | Where the filter applies and its limits. |
| [Enums and picklists](/docs/schema/enums-arrays-picklists) | One value, a list, or a Postgres enum. |

Index: [/llms.txt](/llms.txt)