Multi-tenancy

Tenant-scoped generation

Tell AskDB how your schema represents tenancy. From then on, every generated query against a scoped table includes the tenant predicate — or it’s rejected before being returned. Your app supplies the tenant scope per request.

How it works

Tenant policy is a file (tenant-policy.md) in your schema artifact. It declares the tables that belong to a tenant, which column carries the tenant ID, and which tables are global.

AskDB doesn’t execute queries — but it does rewrite generated SQL to guarantee the tenant filter is present. Your application supplies the tenant ID per call. AskDB validates and binds it.

Tenant policy concepts

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. Generated SQL always includes the join or predicate.
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.

Authoring the policy

The recommended path is Studio’s Tenancy view, which guides you through declaring tenant roots, scoped tables, and global tables in a form. Studio can also AI-draft a policy from a plain-language description of your tenancy model, for your review.

You can also write tenant-policy.md by hand. 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. Example for a simple SaaS schema:

---
schemaId: my-app
enforcement: strict

roots:
  - id: table:public.organizations
    tenantIdColumn: table:public.organizations#id
    label: Organization

scopedTables:
  - id: table:public.projects
    scopeThrough:
      - root: table:public.organizations
        column: table:public.projects#organization_id
  - id: table:public.documents
    scopeThrough:
      - root: table:public.organizations
        column: table:public.documents#organization_id

globalTables:
  - table:public.plan_tiers
  - table:public.countries
---

# Tenant Policy

Organizations are the only tenant level. Projects and documents carry a
direct `organization_id`; plan tiers and countries are shared lookups.

The markdown body below the front-matter is free-form business context; the Hierarchy, Scope rules, and Sensitive interactions H2 sections are recognized and fed into prompt assembly.

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	`strict` rejects any query whose tenant filter can’t be proven; `warn` returns the SQL with tenant warnings instead. Start with `strict`.
`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.
`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 — they trigger warnings, and in strict mode block queries that touch them. 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`).
`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 validator accepts any path that satisfies the caller’s scope.

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.

Asking with a tenant scope

When the policy exists, every ask() call must include a tenantScope. AskDB validates the scope against the policy and binds it into the generated SQL. With enforcement: strict, a query whose tenant filter can’t be proven is rejected; with enforcement: warn, the SQL is returned together with tenantWarnings for your application to act on.

import { ask } from "@askdb/core";

const { sql, tenantParams } = await ask({
  question: "How many documents did we create this month?",
  schema,
  dialect: "postgres",
  model,
  tenantScope: {
    access: {
      kind: "ids",
      tenantRoot: "table:public.organizations",
      ids: ["org_abc123"],
    },
  },
  tenantSqlMode: "sql-params",
});

// Execute with the tenant parameters bound:
const result = await pool.query(sql, tenantParams);

access.kind selects the scope shape: ids (exact tenant IDs on one root — the common case), subtree (a root’s IDs plus all descendants: { kind: "subtree", tenantRoot, rootIds, includeDescendants: true }), multi_root (IDs across several roots), or global (deliberately unscoped, with a reason string for the audit trail). The optional context field carries advisory metadata (role, department) into the prompt, and tenantFilters lets the host pre-resolve polymorphic scope.

SQL output modes

Mode	When to use
`sql-only`	Tenant values inlined as literals. Useful for development inspection or ad-hoc queries you read directly.
`sql-params`	Tenant values extracted as positional `$N` parameters. Use this in production with parameterized execution.

What gets rewritten

Without policy, a question like “how many documents this month?” might generate:

SELECT COUNT(*) FROM documents WHERE created_at >= NOW() - INTERVAL '1 month';

With the policy above and an access scope of { kind: "ids", tenantRoot: "table:public.organizations", ids: ["org_abc123"] }, the same question generates:

SELECT COUNT(*) FROM documents
WHERE organization_id = $1 AND created_at >= NOW() - INTERVAL '1 month';

The tenant predicate is present in the SQL AskDB returns — it can’t be forgotten by the caller, and it can’t be removed by a malformed question.

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.

Testing in Studio

Studio’s Ask panel includes tenant scope controls — provide a scope as JSON before generating SQL to see how predicate injection works:

{
  "access": {
    "kind": "ids",
    "tenantRoot": "table:public.organizations",
    "ids": ["org_abc123"]
  }
}

Switch between sql-only and sql-params to compare the two output forms.