NORM Scoping

Scoping

An always-on equality filter merged into every read and write of a scoped database handle: the tenant-scoping and default-filter primitive. db.scope({ '@orgId': 42 }) makes every find, count, update, and delete carry orgId = 42 and every insert fill it in, so a forgotten tenant filter cannot leak across tenants.

Table of Contents

• Creating a scoped handle
• Reads
• Writes
• Typed inserts
• Graceful across a mixed registry
• The scoped envelope field
• Rules and limits
• Scoping and temporal / audit tables
• Scoping vs views
• Pairing with pact tenant checks

Creating a scoped handle

db.scope(input) returns a new NormDb that shares the same connection, runtime, and any active transaction; only the implicit filter is added. It is typically created per request:

import { Column, Entity, Norm, Schema } from '@tundralibs/norm';

const App = Schema('App', {
  Tickets: Entity('tickets', {
    id: Column.integer(),
    orgId: Column.integer(),
  }, { pk: ['id'] }),
});
const norm = new Norm({ database: { dialect: 'sqlite', path: './data' } });

const db = norm.use(App);

function handler(req: Request, orgId: number) {
  const orgDb = db.scope({ '@orgId': orgId });
  // every orgDb.repo(...) call is confined to this org
}

Scopes compose. Chaining merges them, and the later value wins on a collision:

const scoped = db.scope({ '@orgId': 42 }).scope({ '@region': 'EU' });
// every operation carries orgId = 42 AND region = 'EU'

Reads

find, findOne, getByPK, and count AND the scope into the WHERE:

await orgDb.repo('Tickets').find({ '@status': 'open' });
// WHERE orgId = 42 AND status = 'open'

await orgDb.repo('Tickets').count();
// WHERE orgId = 42

Writes

insert fills in the scope value. You may omit it, or pass it, in which case it must match or the insert is rejected:

await orgDb.repo('Tickets').insert({ title: 'Bug' });
// orgId = 42 is filled in automatically

await orgDb.repo('Tickets').insert({ title: 'Bug', orgId: 99 });
// throws NormQueryError — a scoped insert can't write into another scope

update and delete constrain the WHERE, and update additionally rejects a payload that would move a row out of scope:

await orgDb.repo('Tickets').update({ status: 'closed' }, { '@id': 7 });
// WHERE orgId = 42 AND id = 7 — can only close this org's ticket #7

await orgDb.repo('Tickets').update({ orgId: 99 }, { '@id': 7 });
// throws — cannot reassign a row to a different scope

await orgDb.repo('Tickets').delete({ '@id': 7 });
// WHERE orgId = 42 AND id = 7

upsert enforces the scope exactly like insert and update: it fills in the scope value (you may omit it), rejects a payload that contradicts the scope, and, the part specific to upsert, can never adopt or overwrite another tenant's row:

await orgDb.repo('Tickets').upsert(
  { extKey: 'T-1', title: 'Bug' }, // orgId auto-filled
  { conflictKeys: ['extKey'] },
);
// INSERT … ON CONFLICT (extKey) … — the target is emitted as spelled

await orgDb.repo('Tickets').upsert(
  { extKey: 'T-1', title: 'Bug', orgId: 99 },
  { conflictKeys: ['extKey'] },
);
// throws NormQueryError — a scoped upsert can't write into another scope

How the cross-scope guarantee is enforced

A conflict target alone cannot carry it. Postgres and SQLite infer the arbiter index from the ON CONFLICT list and reject a list no index covers (42P10, "does not match any PRIMARY KEY or UNIQUE constraint"), so norm cannot simply append the scope column to it; and MariaDB's ON DUPLICATE KEY UPDATE ignores the target entirely, matching on any unique key. So the guarantee is enforced one level up, identically on every dialect:

• Pre-flight scope probe (always, every dialect). Before the statement runs, upsert asks the database whether any row this write could collide with lives outside the active scope: the conflict target plus every declared PRIMARY KEY / unique: group the payload supplies, which is exactly what MariaDB would match on. If one does, the call throws NormQueryError (SCOPE_VIOLATION) and no SQL is written. This costs one extra SELECT round-trip on each scoped upsert, so a scoped upsert is two statements (probe plus write) where an unscoped one is a single write. The probe is skipped only when the payload gives it nothing to check: every candidate key is either already inside the scope (so it can only ever match in scope) or not fully supplied by the row (a server-generated primary key you omit, say). It still fires on the recommended per-scope unique shape below whenever the payload also carries a primary key or other declared key the engine could match on.
• Scoped conflict target (when the schema supports it). If the entity declares a unique group covering the scope column(s) plus the conflictKeys, norm folds the scope into the ON CONFLICT list. The list then names a real index, and the conflict can only ever match inside the scope, at the engine level:

const Tickets = Entity('tickets', {
  id: Column.integer(),
  orgId: Column.integer(),
  extKey: Column.varchar(64),
  title: Column.varchar(120),
}, { pk: ['id'], unique: { orgExt: ['orgId', 'extKey'] } });

await orgDb.repo('Tickets').upsert(
  { extKey: 'T-1', title: 'Bug' },
  { conflictKeys: ['extKey'] },
);
// INSERT … ON CONFLICT (orgId, extKey) … — matches only within org 42,
// and two tenants may hold the same extKey

This is the recommended multi-tenant shape: it makes the isolation a schema invariant that nothing can violate, not even a concurrent writer racing the probe, and it lets two scopes carry the same business key. Without it the upsert still works and is still guarded; it refuses loudly instead of partitioning.

Two limits worth knowing. The probe reasons about the constraints the entity declares (the Migrator creates the schema from those same declarations, so they agree in a norm-managed database; a unique index added by hand outside norm is invisible to it). And it is a check-then-act: a conflicting row inserted by another scope in the instant between the probe and the statement is not caught, so that write then proceeds and may overwrite the racing row or, when the conflict key is globally unique and the engine matches on it alone, adopt it into the active scope (the auto-filled scope value is set onto the matched row). This is a residual race, not a routine outcome, since it needs a concurrent cross-scope writer on the same key in that narrow window, but it is real on every dialect, including document stores, which match on the conflict key alone. The per-scope unique above closes both limits: it folds the scope into the conflict target, so the isolation becomes a schema invariant no concurrent writer can race, and the probe on the conflict key falls away.

truncate refuses on a scoped handle. TRUNCATE carries no WHERE, so honouring it would empty every scope's rows, an unscopeable and irreversible cross-scope wipe. Use delete({}), which is scoped, to clear only this scope, or call truncate() on an unscoped handle for a true table truncate:

await orgDb.repo('Tickets').truncate();
// throws NormQueryError — cannot truncate from a scoped handle

await orgDb.repo('Tickets').delete({}); // clears only org 42's rows
await db.repo('Tickets').truncate(); // unscoped handle — empties the table

As with reads, a scope column an entity does not have is skipped, so truncate() on such an entity is not a scoped call for it and proceeds.

Typed inserts

The scoped handle is typed: db.scope({ '@orgId': X }) makes orgId optional in that handle's insert() and upsert(), because the scope fills it. The base handle still requires it, and genuinely required non-scope columns stay required:

const orgDb = db.scope({ '@orgId': 42 });

await orgDb.repo('Tickets').insert({ title: 'Bug' }); // ok — orgId optional
await orgDb.repo('Tickets').insert({ title: 'Bug', orgId: 42 }); // ok — may pass it
await orgDb.repo('Tickets').upsert(
  { extKey: 'T-1', title: 'Bug' }, // ok — orgId optional here too
  { conflictKeys: ['extKey'] },
);

await db.repo('Tickets').insert({ title: 'Bug' }); // type error — orgId required
await orgDb.repo('Tickets').insert({ orgId: 42 }); // type error — title required

Graceful across a mixed registry

A scope column that an entity does not have is skipped for that entity, which is queried unscoped. One scoped handle can therefore span a whole registry where only some tables carry the partition column:

const orgDb = db.scope({ '@orgId': 42 });

await orgDb.repo('Tickets').count(); // WHERE orgId = 42  (Tickets has orgId)
await orgDb.repo('Countries').count(); // unscoped  (Countries has no orgId)

The scoped envelope field

Every result from a scoped operation carries the scope that was applied under result.scoped, keyed by @column, for audit logging. It is absent when nothing applied (the graceful case above):

const r = await orgDb.repo('Tickets').count();
r.scoped; // { '@orgId': 42 }

const c = await orgDb.repo('Countries').count();
c.scoped; // undefined

Rules and limits

• Equality only. A scope value is a bare primitive ({ '@orgId': 42 }). Operators, arrays, and relation refs are rejected: a scope is an identity partition, not a query, and that is what makes it safe to fill in on insert.
• A plain .encrypt() scope column is rejected. IV-randomized ciphertext never compares equal, so a scope on an encrypt-only column could never match; it throws rather than silently matching nothing. An .encrypt().hash() column, however, is a valid scope column: the scope matches via its deterministic <col>_hash digest sibling on reads (and in upsert's scope probe), and a scoped write stores the value as ciphertext with the digest sibling populated, so scoped reads find the row.
• raw() and query() bypass the scope. They run below the filter layer. raw() emits a warning event on every call, scoped or not, so an audit can see the escape hatch was used; query() does not and bypasses the scope silently.

Scoping and temporal / audit tables

A temporal entity is a plain table under the hood, so scope applies exactly as documented above, including on a virtual @AsOf read: the @AsOf range predicate and the scope's equality predicate are ANDed into the same WHERE.

An audit replica inherits its source table's columns, so a scope column the source declares (orgId, say) exists on the replica too, and a scoped handle's reads confine it exactly like any other entity, skipped if the source did not declare the column. The replica only exposes find, findOne, and count (it is read-only; norm owns its writes), so only the read side of scoping applies to it. See Temporal and Audit.

Scoping vs views

Use scoping for runtime, per-request partitions: the tenant id that changes with every request. For a static read model (only active rows, only published posts), define a VIEW instead. It is explicit, appears in the schema and the migration plan, and can be joined. Scoping and views are complementary, not competing.

Pairing with pact tenant checks

@tundralibs/pact checks tenant-scoped permissions with keys like 42::Tickets (see Pact-Tenants). Scope the handle with the same tenant value you checked. Checking one tenant and scoping another is a confused-deputy bug that neither library can detect.

import { Column, Entity, Norm, Schema } from '@tundralibs/norm';

// The shape of a pact bound principal's assert().
declare const principal: {
  assert(key: string, permission: string): Promise<void>;
};

const db = new Norm({ database: { dialect: 'sqlite', path: './data' } })
  .use(Schema('App', {
    Tickets: Entity('tickets', {
      id: Column.integer(),
      orgId: Column.integer(),
    }, { pk: ['id'] }),
  }));

async function listTickets(orgId: number) {
  await principal.assert(`${orgId}::Tickets`, 'READ');
  return await db.scope({ '@orgId': orgId }).repo('Tickets').find();
}
console.log(listTickets);

---

← Back to NORM