Tenant policy format

Reference · Tenant policy

tenant-policy.md declares how a schema represents tenancy: the tables that are tenants, the tables that belong to one, and the shared ones. This page lists its fields, what happens when it no longer matches the schema, and the rules the tenant check applies. Scope queries to a tenant walks through using it.

The file

tenant-policy.md sits in the schema artifact, next to schema.json. When the artifact has one, every ask() call must include a tenantScope; without one, ask() throws TenantScopeError before calling the model. The scope’s shapes are in TenantScope.

The front-matter is YAML, validated strictly when the schema artifact loads — unknown keys are rejected, and every table or column reference uses the stable IDs from schema.json (table:public.projects, table:public.projects#organization_id), never bare names.

The markdown body below the front-matter is free-form business context. The prompt’s tenant block is built from the front-matter, not the body. With RAG, the index chunks the body: one chunk for each recognized H2 section (Hierarchy, Scope rules, Sensitive interactions), or one for the whole body when it has none, and retrieval can put those chunks in the prompt.

What a policy declares

Concept	What it means
Tenant roots	The primary tenant-identifying table (e.g. `organizations`) and its `tenantIdColumn`.
Hierarchy	Parent→child foreign-key relationships between tenant roots (agency → sub-agency → client).
Scoped tables	Tables that belong to a tenant via foreign key. The model is told to include the join or predicate, and the tenant check flags SQL without a tenant predicate that filters.
Polymorphic tables	Tables that reference multiple entity types via a discriminator column (`owner_type` / `owner_id`).
Global tables	Tenant-agnostic lookup tables (currencies, countries). Never tenant-filtered.

Field reference

Top-level front-matter fields:

Field	Required	Meaning
`schemaId`	yes	Must match the `schemaId` in the artifact’s `schema.json`. Ties the policy to one schema.
`enforcement`	yes	What happens when the tenant check finds a problem (see What the tenant check requires). `strict` throws `TenantGuardrailError`; `warn` returns the SQL with the findings in `tenantGuardrail.warnings`. Start with `strict`. Neither mode proves the query is correctly scoped. It also decides whether a policy that doesn’t match the schema loads (see When the policy and the schema disagree).
`roots`	yes	The tables that are tenants (at least one). Everything else is scoped relative to a root.
`hierarchy`	no	Parent→child edges between roots (agency → sub-agency → client). Must form a cycle-free graph: a cycle fails the load under `strict`.
`scopedTables`	no	Tables whose rows belong to a tenant, and how to reach the root from them.
`polymorphicTables`	no	Tables that reference different tenant types via a discriminator column pair.
`globalTables`	no	Stable table IDs intentionally exempt from tenant filtering (shared lookups).

Tables that appear in none of scopedTables, polymorphicTables, or globalTables are classified unknown — a query that names one triggers a warning, and in strict mode an error. Classify everything.

roots[] — each entry is one tenant-identifying table:

Field	Required	Meaning
`id`	yes	Stable table ID (e.g. `table:public.organizations`).
`tenantIdColumn`	yes	Stable column ID of the root’s identifier — the value your app passes at runtime.
`label`	yes	Human-readable name used in prompts and named placeholders (`Organization` → `:tenant_organization_ids`). The placeholder lowercases the label and turns each run of characters other than ASCII letters and digits into `_`, so `Sub agency` gives `:tenant_sub_agency_ids`; a label with no ASCII letter or digit (for example Cyrillic or CJK) uses the root’s table name instead (`Клиент` on `table:public.clients` → `:tenant_clients_ids`). Each root needs a different placeholder: `Agency` and `agency`, or `Sub-Agency` and `Sub Agency`, fail to load. To get a root’s placeholder in code, call `placeholderForTenantRoot(root)` from `@askdb/core`.
`parent.root`, `parent.foreignKey`	no	When this root is a child of another root: the parent’s table ID and the FK column linking to it.

scopedTables[] — operational tables constrained by a tenant:

Field	Required	Meaning
`id`	yes	Stable table ID.
`scopeThrough`	yes	One or more paths from this table to a root. Each path has `root` plus exactly one of `column` or `join`.
`scopeThrough[].column`	one of	Stable column ID of a direct tenant FK on this table (`table:public.projects#organization_id`).
`scopeThrough[].join`	one of	A list of `{ from, to }` stable-column-ID steps when the table reaches its root through other tables (e.g. `appointments → clients`).

A table may declare several scopeThrough paths (e.g. scoped directly by agency_id and through client_id → clients); the check passes when any one path has its tenant predicate. For a column path, that’s the column compared with its root’s placeholder. For a join path, each step must be joined on its own from and to columns, on the reads joined (a.client_id = c.id, in WHERE or an inner join’s ON, or col IN (SELECT col …)), up to a read whose column carries the IDs of the path’s root or one of its ancestors (the root’s tenantIdColumn, or a child root’s foreign key to it), filtered with that root’s placeholder. The steps may be listed in any order, but they must run from the table to a root’s table: a table along the way counts only through the path’s next step, and a path that stops short of a root proves nothing. A column path counts only through its own column.

polymorphicTables[] — type + id discriminator pairs:

Field	Required	Meaning
`id`	yes	Stable table ID.
`typeColumn`	yes	Stable column ID of the discriminator (e.g. `table:public.notes#owner_type`).
`idColumn`	yes	Stable column ID of the polymorphic FK (e.g. `table:public.notes#owner_id`).
`mapping`	yes	Maps each literal `typeColumn` value to a tenant root’s table ID (`agency: table:public.agencies`).

The full contract — including hierarchy validation rules and the five scoping patterns these fields express — lives in docs/contracts/tenant-policy.md on GitHub.

When the policy and the schema disagree

The policy names tables and columns by stable ID. After you rename a column or re-introspect, it can name IDs that schema.json no longer has, and a slip can name a column on the wrong table. loadSchema() checks every ID when it loads the policy, and enforcement decides what happens:

Problem	`warn`	`strict`
A column or foreign key ID that `schema.json` doesn’t have	Warning	`loadSchema()` throws
A column that `schema.json` has on another table than the field names, such as a `scopeThrough` column of another table (`misplaced_column_id`)	Warning	`loadSchema()` throws
A cycle in `hierarchy` or `parent`	Warning	`loadSchema()` throws
A root in `parent`, `hierarchy`, `scopeThrough` or a polymorphic `mapping` that `roots` doesn’t declare	Warning	`loadSchema()` throws
A table that `schema.json` doesn’t have	Warning	Warning

Under strict, the SchemaParseError names tenant-policy.md and each ID. Update the policy to match schema.json: until you do, the schema doesn’t load anywhere, Studio included.

A warning loads, and you can find it in five places:

schema.tenantPolicy.warnings: one TenantPolicyWarning per problem. formatTenantPolicyWarning() from @askdb/core describes one in a line.
Your logger: the first ask() that gets the loaded schema with a logger logs an askdb.tenant.policy_warnings event at warn level, once per loaded schema.
askdb introspect --out: when the output directory already holds a tenant-policy.md, it checks the policy against the schema.json it just wrote and prints what no longer matches.
askdb ask and askdb bundle: each prints one Warning: tenant-policy.md: … line per warning on stderr and keeps its exit code.
Studio’s Tenancy page.

import { formatTenantPolicyWarning, loadSchema } from "@askdb/core";

const schema = loadSchema("./askdb/my-app.schema");
for (const warning of schema.tenantPolicy?.warnings ?? []) {
  console.warn(formatTenantPolicyWarning(warning));
}

A table removed from the schema is one warning in both modes: remove its entry from the policy.

What the tenant check requires

AskDB checks the model’s SQL before it binds the tenant IDs, while the :tenant_<root>_ids placeholders are still in place. The check covers every tenant-scoped table the query mentions:

Queries only. With any scope but global, every statement must start with SELECT or WITH, after any comments and opening parentheses. A statement ends at each ;, and on SQL Server also at a statement verb such as EXEC, which needs no ; before it. A DO block, CALL, EXPLAIN or SHOW is reported (NOT_SELECT_OR_WITH), because the check can’t read what it touches. This applies under every dialect, a custom AskDialect included. Under sqlserver, code SQL Server may split into tokens differently is reported the same way, as the read-only check refuses it: a character outside plain code (such as a non-breaking space), a bare money sign (such as $ or €), a \, a number written against a word, or a ? written against a name.
No calls it can’t read through. A call that runs a query or reads a table named in a string (query_to_xml, table_to_xml, crosstab), runs SQL over another connection (dblink, OPENQUERY), or sets a value row-level security may read (set_config) is reported (UNCHECKABLE_FUNCTION), under every dialect. The check knows 32 functions, each built into Postgres or SQL Server or from the tablefunc, dblink or xml2 extension; a function from any other extension, such as pgRouting’s, or one of your own, passes. It refuses a listed name anywhere in the SQL, so a column, alias or parameter named like one (crosstab, set_config) is refused too. A pivot written with CASE or FILTER passes when its tenant predicate is in WHERE; one written with crosstab doesn’t.
A real predicate. The table’s tenant column is compared with its root’s placeholder: agency_id = :tenant_agency_ids, agency_id IN (:tenant_agency_ids) or agency_id = ANY(:tenant_agency_ids). Write the tenant column as the schema spells it. On Postgres and CockroachDB, which compare a quoted name exactly and fold an unquoted one to lower case, "AGENCY_ID" is another column than agency_id, and on a camelCase schema an unquoted agencyid is another column than "agencyId": write it quoted, as c."agencyId", and quote a column with an upper-case non-ASCII letter too, since Postgres keeps that letter unquoted and CockroachDB lower-cases it. On SQL Server, the name must be written as the schema spells it, since the database’s collation may compare names case-sensitively; on MySQL, MariaDB and SQLite, a non-ASCII letter must be in the schema’s case. With a custom dialect or none, all of these apply. The same goes for a polymorphic table’s discriminator column. A quoted name spelled like the placeholder (":tenant_agency_ids") is a name, not the placeholder.
No renamed columns. On Postgres and CockroachDB, a column list after a table’s alias (orders t (agency_id, x)) renames the table’s first columns, so the check can’t tell which column a predicate on t filters. The read is reported (MISSING_TENANT_PREDICATE). So is every read inside a parenthesized join or derived table with a column list after its alias ((orders o JOIN lookup_states s ON true) AS j (a1, a2, agency_id)).
ANDed into the filter. The predicate sits in a WHERE clause, an inner join’s ON clause, or a HAVING clause on a column the SELECT groups by, written the same way (GROUP BY agency_id … HAVING agency_id = :tenant_agency_ids; GROUP BY o.agency_id counts only for HAVING o.agency_id = …); an outer join’s ON doesn’t filter the preserved side, so it doesn’t count, and in a SELECT with an outer join an inner join’s ON doesn’t either, since the outer join keeps rows that ON never saw: put the predicate in WHERE. A join is outer when LEFT, RIGHT, FULL or OUTER is among its words, whatever join hint is beside it (LEFT HASH JOIN, LEFT STRAIGHT JOIN). The predicate can’t be next to an OR at its own level, under NOT, or in the select list. agency_id = :tenant_agency_ids AND (status = 'a' OR status = 'b') is fine; agency_id = :tenant_agency_ids OR 1 = 1 is rejected, and so is the upper bound of a BETWEEN (0 BETWEEN 0 AND agency_id = :tenant_agency_ids).
No literal IDs. agency_id = 2 doesn’t satisfy the rule, even when 2 is in the caller’s scope: AskDB binds the IDs.
Each read, in its own SELECT. Every read of the table needs its own predicate in the SELECT it appears in. A CTE body, a derived table, a subquery and each UNION branch are each their own.
- A qualified predicate (o.agency_id) counts only for the read it names, written table, table alias or table AS alias, followed by what ends the source (for example a join, ON, WHERE, GROUP BY, a comma or a closing parenthesis). After any other clause, only an unqualified predicate filters the read.
- An alias after parentheses around a read hides the read’s own alias ((orders x) AS y, (orders x JOIN s ON …) AS j), so only an unqualified predicate filters it.
- A predicate in an ON, qualified or not, counts only for the reads that join puts together: not for a table joined after it, before a comma in FROM, or outside the parentheses around the join.
- When a SELECT reads the table more than once, each read needs a distinct plain alias (orders a JOIN orders b), outside parentheses, and its own predicate qualified with that alias.
- On MySQL, MariaDB and SQL Server, write an alias the same way each time. A qualifier in another case doesn’t count.
- A read inside a derived table or CTE also passes when every reference to that source is filtered on the tenant column. The source must pass the column through unrenamed (SELECT * over one table, or the column itself).
- The source must also compute nothing across its rows: no aggregate, window, GROUP BY, HAVING, QUALIFY, LIMIT, OFFSET, FETCH or TOP.
- A set operation in the source’s body, or a column list on it or on a reference to it, fails closed.
Root tables too. A query on the root table itself (agencies) needs the predicate on its tenant ID column, or a join on it to a column that holds its IDs and is filtered: a.id = o.agency_id … o.agency_id = :tenant_agency_ids.
Joins on the declared columns. A table scoped through a join path needs each hop joined on the columns the policy declares (a.client_id = c.id, either way round, in ON or WHERE), up to a read filtered with the path’s root’s placeholder or an ancestor’s. A join on another column, or none, doesn’t count, and in a SELECT with an outer join no ON does: write JOIN when WHERE filters the joined side. Each joined table counts only spelled as the schema spells it, except that on Postgres, CockroachDB and SQLite an unquoted name may be in any ASCII case: "CLIENTS" can be another table than clients.
SQLite’s quoted table names. SQLite reads a '…' in a table’s place as the table’s name, in parentheses too, so under sqlite, a custom AskDialect or no dialect, SELECT * FROM 'orders' and SELECT * FROM ('orders') need the predicate for orders. A '…' right after a table, or after its parentheses, is its alias, so in FROM sub_agencies 'o', orders the qualifier o names sub_agencies.
Plain names. On Postgres, a Unicode-escaped name such as U&"\006Frders" (which reads orders) is refused as UNICODE_ESCAPED_IDENTIFIER, whatever the predicates, because the check can’t tell what it names. Write names unquoted or in plain double quotes. See TenantGuardrailError.
Names the schema spells. Under sqlserver, a custom AskDialect or no dialect, a name with a character outside printable ASCII is refused as UNICODE_ESCAPED_IDENTIFIER unless the schema spells it exactly. SQL Server’s collation ignores some characters and reads others as ASCII letters, so [ｏｒｄｅｒｓ] reads orders. The rule covers aliases and CTE names too, so write those in printable ASCII.
Names no engine folds into another. Under every dialect, a name with İ (U+0130), ı (U+0131), ſ (U+017F) or the Kelvin sign (U+212A), or one not in Unicode normalization form C, as written or, unquoted, once lower-cased, is refused as UNICODE_ESCAPED_IDENTIFIER unless the schema spells it exactly, since some engines read those letters as ASCII ones (CockroachDB and MySQL read clİents as clients). Unquoted, the schema’s spelling counts only for a name that is lower-case and in NFC: CockroachDB reads an unquoted İTEMS as items, so quote it ("İTEMS"). On Postgres and CockroachDB, quote an alias or qualifier with an upper-case non-ASCII letter: unquoted, the two engines read it differently, so it ties a predicate to no read.
No continued E'…' strings. On Postgres, an E'…' string continued on a later line is refused as STRING_CONTINUATION, whatever the predicates: Postgres reads the continuation with backslash escapes, so a table after it could be hidden from the check. Join the parts with ||.
No escaped b'…' byte strings. A b'…' byte string with a backslash, or continued on a later line, is refused as ESCAPED_BYTE_STRING: CockroachDB reads it with backslash escapes, so a table after it could be hidden from the check. Write the bytes as a hex string cast to bytea ('\x00'::bytea).

It’s a heuristic over the SQL’s tokens, not a parser. A join on any other pair of columns than the policy declares doesn’t count (#400). Use it as defense in depth, together with database-side row-level security and a read-only role: see Enforce tenancy in the database and Run safely in production. The full rules are in docs/contracts/tenant-policy.md.

SQL output modes

Mode	When to use
`sql-params` (default for built-in dialects)	Tenant values bound through your dialect’s driver markers — `$1` (Postgres, CockroachDB), `?` (MySQL, MariaDB, SQLite), `@p0` (SQL Server). The tenant IDs never enter the SQL text.
`sql-only`	Tenant values inlined in `sql` as escaped literals (`unboundSql` still binds them), for a tool that takes one SQL string, or for SQL you read directly. A tenant ID must be printable ASCII without a backslash, or it’s refused (`UNESCAPABLE_TENANT_ID`): any other literal can read differently under the server’s string settings or connection charset.

In sql-only mode, ask() re-checks the sql it returns after inlining the tenant IDs, as a rebind does, and refuses a statement that reads differently once rendered with SqlValidationError (a Postgres or CockroachDB string that continues past the inlined literal, say).

A custom AskDialect has no default mode, because AskDB doesn’t know which driver it targets: with a tenant scope, ask() throws TENANT_SQL_MODE_REQUIRED until you set tenantSqlMode. "sql-params" gives $N markers; if your driver uses ? or @p0 markers (on SQL Server, $1 is a money constant), set "sql-only". A value other than these two throws the same error, on every dialect.

In sql-params mode ask() returns two executable pairs:

Run	With	Notes
`result.sql`	`result.tenantParams`	Business values inlined as literals; tenant markers start at the first slot.
`result.unboundSql`	`result.params`	Present when `parameterize` is on (default). Every value is bound and `params` already includes the tenant IDs in marker order.

Don’t concatenate params and tenantParams. parameters[].indices point into params.

AskDB only substitutes placeholders that sit in SQL code — placeholder text inside a string literal is left alone. If the generated SQL references a tenant root your scope has no IDs for, ask() throws (UNRESOLVED_TENANT_PLACEHOLDER) instead of returning SQL with a raw :tenant_… token. Placeholders are case-sensitive: :TENANT_ORGANIZATION_IDS isn’t substituted, so it throws the same error. With several IDs, = :p becomes IN (…) and != :p becomes NOT IN (…); a comparison like <= :p has no list form and throws UNSUPPORTED_TENANT_PREDICATE.

Without a policy

If tenant-policy.md is absent from the schema artifact, AskDB generates SQL without any tenant-scoping predicates. That’s the right default for single-tenant deployments. Add the policy when (and only when) you go multi-tenant.

If you deploy a bundle (askdb bundle), the policy travels inside it as the tenantPolicy field. A bundle built with askdb 1.0.0-beta.43 or earlier, or with bundleSchemaDirectory() from @askdb/enrich 0.2.0-beta.14 or earlier, left it out: see Rebuild schema bundles that have a tenant policy.