Organizational data scoping

Organizational data scoping confines what rows a request can see, derived from the authenticated principal (roles, groups, claims) rather than hand-written into every query. It is the row-level complement to multi-tenancy: where tenancy isolates whole tenants, scoping restricts a caller to their organizational reach within a tenant — their department, their region, their own records, or the subtree of org units they manage.

It builds on three existing subsystems:

The principal model (tesseraql-security Principal) — roles, groups, permissions, and raw claims, populated identically by every authentication mechanism (JWT, OIDC, SAML, API keys, mTLS). Under an acting role the principal a scope arm evaluates is the active view — the tab’s one activated application role plus the stack-wide roles — so a multi-role user’s rows follow the capacity they act in, not the union of all they hold.
2-way SQL — a scope predicate is injected at a site the author chooses, parameterized, and stays runnable in a plain SQL tool. No query is rewritten behind the author’s back.
Field policies / masking — the field-policy masking step keys off the same principal that drives scoping to decide whether a field is shown, masked, or hidden (see row-level masking below).

It deliberately mirrors the tenant-predicate mechanism used in multi-tenant deployments, where every scoped table carries a tenant column (tenant.id binds plus the TQL-TENANT-3001 lint): same shape, one level deeper.

The model

A scope is a named, reusable row-level predicate, declared once and applied to many queries. At execution time TesseraQL resolves the scope against the principal and renders a parameterized SQL predicate at the point the author marked with a /*%scope ... */ directive.

Three invariants hold:

SQL-first, no hidden generation. The author writes /*%scope name */ where the predicate belongs; the engine never rewrites a WHERE clause or adds a table to the FROM clause. A scope that needs a join is written as a correlated subquery (EXISTS), never a top-level join — so row cardinality cannot change behind the query.
Deny-by-default. A principal that matches no arm of a scope sees nothing (1=0), never everything. Seeing all rows requires an explicit apply: all arm.
Machine-checkable. Scopes carry their own lint rules (TQL-SCOPE-30xx) and a data-scope coverage kind.

Declaring a scope

A scope is a kind: scope document under scope/ in the app tree (see application layout). It is an ordered list of match arms: each arm pairs a principal condition (when, a Policy-style role/permission/claim matcher) with an effect — apply: all, apply: none, or a 2-way SQL predicate file (with its params).

version: tesseraql/v1
id: orders_scope
kind: scope
match:
  - when: { role: org-admin }                 # sees everything
    apply: all
  - when: { role: region-manager }            # sees their region(s)
    file: by_region.sql
    params:
      regions: principal.claim.regions
  - when: { permission: orders:read-own }     # sees only their own rows
    file: own_rows.sql
    params:
      uid: principal.subject
  # no matching arm ⇒ 1=0 (deny-by-default)

Each file is a 2-way SQL boolean predicate that stays runnable in a SQL tool:

-- scope/by_region.sql
$.region in /* regions */ ('R1','R2')

-- scope/own_rows.sql
$.created_by = /* uid */ 'u'

when reuses the authorization matcher used by route policy: and field masking (the Policy.Rule shape): { role: … }, { permission: … }, or { claim: name, equals: value }. It intentionally does not use the 2-way expression language, which has no role-membership or function-call syntax — role/permission/claim matching is the matcher’s job, deny-by-default. An arm with no when matches every principal.

Composition is additive (OR)

When a principal matches more than one arm, the matching predicates are combined with OR: a caller sees a row if any of their roles’ scopes would show it — the same additive grant model as Policy.permits (anyOf). An apply: all short-circuits to all rows; no match yields 1=0.

principal	rendered (`/%scope orders_scope on o /`)	binds
`org-admin`	`(1=1)`	`[]`
`region-manager` (regions R1,R2)	`((o.region in (?, ?)))`	`[R1, R2]`
`read-own` only	`((o.created_by = ?))`	`[uid]`
both manager and read-own	`((o.region in (?)) or (o.created_by = ?))`	`[R1, uid]`
neither	`(1=0)`	`[]`

The `/%scope ... /` directive and joins

The author marks the injection site, so the engine never has to guess where the predicate goes. The directive is a 2-way SQL comment (/*% … */, sibling to /*%if … */) followed by a parenthesized dummy predicate — in a plain SQL tool it reads as (1=1); at render time the resolved scope predicate replaces the dummy.

select o.id, o.amount, c.name
from orders o
join customers c on c.id = o.customer_id
where o.status = /* status */ 'OPEN'
  and /*%scope orders_scope on o */ (1=1)     -- in a SQL tool this reads `and (1=1)`
order by o.id

Alias parameterization. A fragment refers to its target table with the $ sentinel ($.region); the call site supplies the alias with on <alias>, so one fragment is reusable across queries that alias the table differently. With no on, $. resolves to nothing (single-table queries). The alias must be a valid SQL identifier — it is the only string substitution, validated by the parser and lint, and is author-supplied at build time, never request input.
The scoped column may live in any joined table. Place the directive and pass on <thatAlias>.
Two scoped tables ⇒ two directives, each on its alias (possibly different scopes); they combine with AND.

Scopes that need a join are correlated subqueries, not top-level joins:

-- scope/orders_in_my_subtree.sql
exists (
  select 1 from tql_org_closure cl
  where cl.descendant_id = $.owner_unit
    and cl.ancestor_id  in /* my_units */ ('U1')
)

Because the directive sits where a WHERE predicate goes, an accidental top-level join is a SQL syntax error in a plain tool — the 2-way “runs in a SQL tool” property enforces invariant 1 for free.

Writes are scoped the same way: a /*%scope ... */ in the WHERE of an UPDATE/DELETE confines the write to authorized rows. This is how an approval workflow state transition carries its row authority.

Org-unit hierarchy — a shared foundation

“My department and everything under it” needs an org-unit graph. Like identity — which offers a managed identity realm and a SQL-contract realm — the org-unit model has two modes, selected by tesseraql.orgunit.mode:

managed — the runtime provisions and maintains two managed tables: tql_org_unit (units and their parent_id links) and tql_org_closure (the transitive closure — every ancestor/descendant pair, depth 0 being the unit itself). The OrgUnitStore SPI maintains the closure: upsert/delete units, then rebuildClosure() recomputes the closure from the parent graph (in Java, so it is dialect-agnostic — no recursive CTE). A subtree scope is then a plain, portable SELECT against the closure:
```
-- scope/orders_subtree.sql
$.owner_unit in (select descendant_id from tql_org_closure
                 where ancestor_id in /* my_units */ ('U1'))
```
```
# scope/orders_subtree.yml — everyone is subtree-scoped; an org-admin bypasses
id: orders_subtree
kind: scope
match:
  - when: { role: org-admin }
    apply: all
  - file: orders_subtree.sql           # unconditional arm: applies to every principal
    params:
      my_units: principal.claim.org_unit
```
The principal’s home unit(s) ride a claim (principal.claim.org_unit); the closure turns them into the full subtree. A principal with no unit claim resolves to an empty in (…) and sees nothing.
app (default) — the application owns its own organization tables; a subtree scope is written against them with the scope directive, exactly as above but joining the app’s own closure or a recursive view. Nothing managed is provisioned, so an existing app gains no tables until it opts in.

This org-unit model is deliberately factored as a shared foundation, not a scoping-private table: approval workflow consumes the same graph unchanged.

Relationship to approval workflow

Approval workflow consumes the same org graph in the opposite direction: scoping maps a principal to a predicate over data rows, while assignee resolution maps a document to the set of principals who may act on it. In practice, a workflow task inbox is a /*%scope ... */ applied to the task table, and a state transition is a scoped write whose UPDATE is confined to the documents the caller has authority over (the transition guard checks state-machine legality; the scope checks row authority).

Row-level masking

Column-level, role-conditional masking already works through FieldPolicy.policy (the field is shown only when the principal satisfies a Policy). Scoping adds row-level masking: a field is masked in rows outside the caller’s scope. Rather than evaluate a predicate per row in Java, the query selects the scope predicate as a boolean flag (the as boolean directive renders it as a portable case when … then 1 else 0 end) and the field policy keys off it with unmaskWhen; the flag column is stripped from the response:

select o.id, o.salary,
       /*%scope payroll_scope on o as boolean */ (1=1) as _in_scope
from   payroll o

response:
  json:
    fields:
      salary: { mask: fixed, unmaskWhen: _in_scope }   # masked unless the row is in scope

This keeps masking SQL-first and reuses the field-policy masking step’s existing resolution order.

Governance and testing

Lint catches a misdeclared or unreferenceable scope before it ships:

Code	Severity	Meaning
`TQL-SCOPE-3011`	error	a `/%scope name /` directive names a scope not declared under `scope/`
`TQL-SCOPE-3012`	error	a scope definition is malformed: an arm declares neither/both of `apply` and `file`, an unknown `apply` value, a missing fragment file, or a `when` setting more than one of role/permission/claim (or a `claim` with no `equals`)
`TQL-SCOPE-3013`	error	a directive’s `on <alias>` is not a valid SQL identifier
`TQL-SCOPE-3020`	error	`tesseraql.orgunit.mode` is set to something other than `managed` or `app`
`TQL-SEC-4100`	warning	an `UPDATE`/`DELETE` writes a scope-governed table (one the app scopes elsewhere with `/%scope … /`) but carries no scope predicate of its own — confirm the write cannot reach rows outside the caller’s scope

The runtime fails closed: a directive rendered without a scope resolver configured is TQL-SQL-2106, and a directive naming an undeclared scope is TQL-SQL-2107 — a scope can never silently no-op.

TQL-SEC-4100 warns when a write bypasses a governed scope: a table the app scopes on reads is UPDATE/DELETEd without a /*%scope … */ predicate. It is a defense-in-depth nudge, not a hard rule — the set of scope-governed tables is inferred from where scope directives are actually used, so it fires only on a genuine read/write inconsistency within one app and stays a warning (non-blocking; a deliberately-unscoped admin write is answered by confirming the intent).

Still planned but not currently implemented: the read-side symmetry TQL-SCOPE-3001 (a scope-governed table queried with no scope predicate, mirroring TQL-TENANT-3001) and TQL-SCOPE-3010 (a future route-level scope declaration naming an undeclared scope).

The data-scope coverage kind declares one item per scope under scope/; a scope counts as covered when a declarative suite exercises a route (or consumer) whose SQL applies it through a /*%scope name */ directive — the same SQL-file basis as route coverage. An app with no scopes reports a 1.0 ratio. Gate it with coverage.thresholds.data-scope. (Per-role-path coverage — <scopeId>#<role> — is planned but not currently supported.)

A suite exercises scoped SQL by declaring the principal each case runs as (testing): the runner resolves the directive through the app’s scope/ declarations with the production resolver, so one case per role asserts each arm’s rows and the deny-by-default posture against a real database.

multi-tenancy.md — isolating whole organizations rather than rows.
authentication.md — where the principal comes from.