@aiquants/authz-drizzle

Drizzle (mssql) adapter for @aiquants/authz-core. Generic schema factory + effective-permission query + row/column scope application. mssql-only (drizzle-orm/mssql-core: nvarchar/bigint/bit/datetime2).

Install

Monorepo (already a pnpm workspace package):

// consumer package.json
"dependencies": {
  "@aiquants/authz-core": "workspace:*",
  "@aiquants/authz-drizzle": "workspace:*"
}

External projects:

pnpm add @aiquants/authz-drizzle drizzle-orm   # peer: drizzle-orm >=1.0.0-beta.4 <2

drizzle-orm is a peer dependency; @aiquants/authz-core is pulled in transitively. Build / test: pnpm run build (tsup → dist) / pnpm run test (vitest).

Required database schema (DDL)

defineAuthzSchema() maps the tables below but does not create them — run this DDL once (MSSQL). Adjust the schema name (dbo_authz) and the cross-schema FK at the end to point at your user table.

IF SCHEMA_ID('dbo_authz') IS NULL EXEC('CREATE SCHEMA dbo_authz');

CREATE TABLE dbo_authz.TMRole (
  id BIGINT IDENTITY(1,1) NOT NULL,
  tenant_id NVARCHAR(64) NOT NULL CONSTRAINT DF_authz_TMRole_tenant_id DEFAULT (N'default'),
  role_key NVARCHAR(100) NOT NULL,
  name NVARCHAR(200) NOT NULL,
  description NVARCHAR(1000) NULL,
  is_active BIT NOT NULL CONSTRAINT DF_authz_TMRole_is_active DEFAULT (1),
  created_at DATETIME2 NULL, created_by NVARCHAR(50) NULL,
  updated_at DATETIME2 NULL, updated_by NVARCHAR(50) NULL,
  CONSTRAINT PK_authz_TMRole PRIMARY KEY CLUSTERED (id)
);
CREATE TABLE dbo_authz.TMResource (
  id BIGINT IDENTITY(1,1) NOT NULL,
  tenant_id NVARCHAR(64) NOT NULL CONSTRAINT DF_authz_TMResource_tenant_id DEFAULT (N'default'),
  app_key NVARCHAR(100) NOT NULL,
  resource_key NVARCHAR(200) NOT NULL,
  name NVARCHAR(200) NOT NULL,
  description NVARCHAR(1000) NULL,
  created_at DATETIME2 NULL, created_by NVARCHAR(50) NULL,
  updated_at DATETIME2 NULL, updated_by NVARCHAR(50) NULL,
  CONSTRAINT PK_authz_TMResource PRIMARY KEY CLUSTERED (id)
);
CREATE TABLE dbo_authz.TDRolePermission (
  id BIGINT IDENTITY(1,1) NOT NULL,
  role_id BIGINT NOT NULL,
  resource_id BIGINT NOT NULL,
  action NVARCHAR(20) NOT NULL
    CONSTRAINT CK_authz_TDRolePermission_action CHECK (action IN ('read','create','update','delete')),
  row_scope    JSON NULL,   -- SQL Server 2025+ native json. Older servers: NVARCHAR(MAX) (+ optional ISJSON CHECK).
  column_scope JSON NULL,
  created_at DATETIME2 NULL, created_by NVARCHAR(50) NULL,
  updated_at DATETIME2 NULL, updated_by NVARCHAR(50) NULL,
  CONSTRAINT PK_authz_TDRolePermission PRIMARY KEY CLUSTERED (id)
);
CREATE TABLE dbo_authz.TDUserRole (
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL,
  created_at DATETIME2 NULL, created_by NVARCHAR(50) NULL,
  updated_at DATETIME2 NULL, updated_by NVARCHAR(50) NULL,
  CONSTRAINT PK_authz_TDUserRole PRIMARY KEY CLUSTERED (user_id, role_id)
);

-- uniqueness (the adapter relies on the DDL owning these).
-- ⚠️ tenant_id LEADS every unique index on a tenant-scoped table: without it two tenants cannot use the
-- same role_key / resource_key, which is the same thing as having one shared tenant.
CREATE UNIQUE INDEX UQ_authz_TMRole_role_key ON dbo_authz.TMRole (tenant_id, role_key);
CREATE UNIQUE INDEX UQ_authz_TMResource_app_key_resource_key ON dbo_authz.TMResource (tenant_id, app_key, resource_key);
CREATE UNIQUE INDEX UQ_authz_TDRolePermission_role_resource_action ON dbo_authz.TDRolePermission (role_id, resource_id, action);

-- referential integrity
ALTER TABLE dbo_authz.TDRolePermission ADD CONSTRAINT FK_authz_TDRolePermission_role_id     FOREIGN KEY (role_id)     REFERENCES dbo_authz.TMRole (id);
ALTER TABLE dbo_authz.TDRolePermission ADD CONSTRAINT FK_authz_TDRolePermission_resource_id FOREIGN KEY (resource_id) REFERENCES dbo_authz.TMResource (id);
ALTER TABLE dbo_authz.TDUserRole       ADD CONSTRAINT FK_authz_TDUserRole_role_id           FOREIGN KEY (role_id)     REFERENCES dbo_authz.TMRole (id);
-- cross-schema FK → YOUR user table (placeholder: dbo_identity.Users). Adjust to your app:
ALTER TABLE dbo_authz.TDUserRole       ADD CONSTRAINT FK_authz_TDUserRole_user_id           FOREIGN KEY (user_id)     REFERENCES dbo_identity.Users (id);

defineAuthzSchema(schemaName, { userTable }) must be given the same schema name and a drizzle handle to the same user table the FK points at. row_scope / column_scope are stored as JSON text either way — the adapter reads them as strings and parses via @aiquants/authz-core.

Optional (recommended for larger datasets): non-unique secondary indexes on the FK columns — TDUserRole(user_id), TDRolePermission(role_id), TDRolePermission(resource_id). They affect performance only, not correctness.

Bootstrap (seed an initial admin and reserved roles)

So the first person can manage authorization, grant one role full access to a self-management resource and assign it to a real user (user_id = a row in your user table). We also seed the reserved roles @authenticated and @anonymous.

INSERT INTO dbo_authz.TMRole (role_key, name, description) VALUES ('admin', 'Administrator', 'Full system administrator (including authorization management)');
INSERT INTO dbo_authz.TMRole (tenant_id, role_key, name, description) VALUES (N'default', '@authenticated', 'Authenticated Users', 'All logged-in authenticated users (implicit assignment)');
INSERT INTO dbo_authz.TMRole (tenant_id, role_key, name, description) VALUES (N'default', '@anonymous', 'Anonymous Users / Guests', 'All users including unauthenticated guests (implicit assignment)');

INSERT INTO dbo_authz.TMResource (tenant_id, app_key, resource_key, name) VALUES (N'default', 'myapp', 'authz_admin', 'Authorization admin');

INSERT INTO dbo_authz.TDRolePermission (role_id, resource_id, action, row_scope, column_scope)
SELECT r.id, res.id, a.action, NULL, NULL                       -- NULL/NULL = all rows / all columns
FROM dbo_authz.TMRole r
CROSS JOIN dbo_authz.TMResource res
CROSS JOIN (VALUES ('read'),('create'),('update'),('delete')) AS a(action)
WHERE r.role_key = 'admin' AND res.app_key = 'myapp' AND res.resource_key = 'authz_admin';

INSERT INTO dbo_authz.TDUserRole (user_id, role_id)
SELECT 123, id FROM dbo_authz.TMRole WHERE role_key = 'admin';  -- 123 = your user id

Usage

// 1. Define the schema (schema name + the host app's user table)
const tables = defineAuthzSchema("dbo_authz", { userTable: Users })

// 2. Resolve a user's effective permission for one (tenant, app, resource).
//    `tenantId` is REQUIRED and non-empty; `userId` can be null for anonymous guests.
const perm = await getEffectivePermissions(db, tables, { userId, tenantId, appKey: "quants", resourceKey: "daily_report" })

// 2b. …or for many resources at once — ONE round-trip instead of N (this is what a framework adapter wires up).
const perms = await getEffectivePermissionsMany(db, tables, { userId, tenantId, appKey: "quants", resourceKeys: ["daily_report", "sales"] })
perms.sales.actions   // every requested key is present; a resource with no grants has zero actions

// 3. Apply the merged scope to a query + result (per-endpoint columnMap + the tenant of YOUR table)
const columnMap = { dept: Report.dept, amount: Report.amount }
const scope = perm.scopeByAction.read           // MergedScope | undefined
// ⚠️ no scope = no grant. Never write `scope?.rowScope ?? null` — `null` means ALL rows, so the fallback would open it up.
const rows = await applyRowScope(db.select().from(Report), scope ? scope.rowScope : { anyOf: [] }, columnMap, { column: Report.tenantId, id: tenantId })
return maskColumns(rows, scope ? scope.columnScope : { mode: "allow", columns: [] })   // drop columns the user may not see

Tenant contract

tenantId is a required, non-empty string on every entry point of this package. A missing or blank tenant is an error — it never means "no filter" or "every tenant", which is the inversion that turns a scoping bug into a cross-tenant data leak.

• TMRole, TMResource and TMGroup each carry a NOT NULL tenant_id, and each of their unique indexes leads with it: TMRole(tenant_id, role_key), TMResource(tenant_id, app_key, resource_key), TMGroup(tenant_id, group_key). The link tables carry no tenant — they inherit it from the row they point at.
• The permission query filters all three of those tables and projects the roles' and resources' real tenant_id into each grant row, so buildEffectivePermission verifies ownership against the database value rather than against an echo of the argument.
• Every AuthzAdminStore method takes tenantId as its first argument. Reads are filtered by it, by-id writes match on it (an id from another tenant addresses nothing), and a write that references another row (role / resource / group) proves that row lives in the same tenant first. When a payload names a tenant too, the two are reconciled with assertTenantMatch — a mismatch is rejected, never resolved in favour of one side.
• The last-administrator lockout guard is evaluated per tenant: another tenant's administrators can no longer make this tenant's last administrator removable.
• applyRowScope / buildRowScopeWhere take a required { column, id } tenant and AND it into the predicate, so a host cannot forget to scope its own business query. maskColumns takes no tenant: it only removes columns from rows a tenant-scoped query already returned, and cannot widen a result set.
• The physical DEFAULT (N'default') on tenant_id exists for hand-written SQL only. Every code path here supplies the tenant explicitly, so the default is never exercised.

Breaking changes in this release

| Before | Now | | --- | --- | | applyRowScope(qb, scope, columnMap, existingWhere?) | applyRowScope(qb, scope, columnMap, tenant, existingWhere?) — tenant: { column, id } is required, and the builder is always narrowed (return type is ReturnType<QB["where"]>, never the untouched builder), because the tenant predicate always applies. | | buildRowScopeWhere(scope, columnMap) → SQL \| undefined | buildRowScopeWhere(scope, columnMap, tenant) → SQL. An "all rows" scope now yields the tenant predicate alone instead of undefined. | | getEffectivePermissions*({ …, tenantId? }) | tenantId is required and non-empty; a blank value throws AuthzTenantError before any SQL is issued. The predicate is applied to TMRole, TMResource and TMGroup. | | listResourceKeys(db, tables, appKey) | listResourceKeys(db, tables, appKey, tenantId). | | AuthzAdminStore methods took no tenant | every method takes tenantId first (listRoles(tenantId), createGrant(tenantId, input, actor), getSelfAdminContext(tenantId, actorUserId), …), and guardedWrite(tenantId, removal, op, write) is part of the port. | | createAdminLockoutGuard(...).guardedWrite(removal, op, write) | guardedWrite(tenantId, removal, op, write). | | defineAuthzSchema(schemaName, { userTable, enableTenant }) | the enableTenant option is deleted — it was accepted and never read. tenant_id is unconditional. | | tenantId omitted from an insert | the drizzle column declares no .default(), so this is now a type error. The physical DEFAULT (N'default') still exists (it is what lets the column be added to a populated table) but is generated by buildAuthzDdlStatements alone and is unreachable from library code. | | createAuthzAdminStore({ userTenantColumn }) | replaced by the required userScope discriminated option. An omitted column used to mean "no tenant filter", so listUsers — the admin UI's only ownership proof for TDUserRole writes — returned every tenant's users. | | seedAuthzTables(db, schemaName, { appKey, … }) | seedAuthzTables(db, schemaName, { tenantId, appKey, … }) — seeded rows name their tenant instead of relying on the physical default. | | LockoutOp was declared here | re-exported from @aiquants/authz-core (single declaration). | | getEffectivePermissions was the only loader | still here (a thin wrapper), but a framework adapter should wire getEffectivePermissionsMany — one round-trip for N resources. | | userId was coerced with Number() | a userId that is not an integer (or a digit-only string) throws a TypeError. "0x10" no longer becomes user 16. |

Programmatic Setup and Seeding (Optional/Testing)

For testing or automated database setup scripts, the package exports helper utilities to programmatically build the schema and seed initial data:

import { ensureAuthzTablesExist, seedAuthzTables } from "@aiquants/authz-drizzle"

// 1. Create the schema, tables, indices, and foreign keys if they do not exist (DDL)
await ensureAuthzTablesExist(db, "dbo_authz", "dbo_identity.Users")

// 2. Bootstrap initial admin roles, resources, and assignments (DML, idempotent) into ONE named tenant
await seedAuthzTables(db, "dbo_authz", {
  tenantId: "default",
  appKey: "myapp",
  adminEmails: ["[email protected]"],
  userTable: Users,
})

API

• defineAuthzSchema(schemaName, { userTable }) → { TMRole, TMResource, TDRolePermission, TDUserRole, TMGroup, TDGroupRole, TDGroupMember }. There is no option to disable the tenant column. Schema name is an argument (no business names baked in); the cross-schema FKs TDUserRole.user_id → userTable.id and TDGroupMember.user_id → userTable.id are wired via drizzle's foreignKey(). UNIQUE constraints are owned by the DDL side.
• getEffectivePermissionsMany(db, tables, { userId, tenantId, groupIds, appKey, resourceKeys }) → effective permission per key. Joins active roles once for the whole set and routes each grant row back to its own resource, so N resources cost one round-trip (chunked against the MSSQL 2100-parameter budget). Duplicate keys collapse; every requested key is present in the result (no grants → an empty permission, never a missing key); the result never carries a key you did not ask for. How a row is matched back to your key depends on collation (see the note below).
• getEffectivePermissions(db, tables, { userId, tenantId, groupIds, appKey, resourceKey }) → EffectivePermission (joins active roles, handles reserved @authenticated and @anonymous roles, and delegates scope merge to core. userId can be string | number | null). Thin single-resource wrapper over getEffectivePermissionsMany, so both share one query path. Misuse fails loudly: a non-array resourceKeys (a bare string would iterate per character) or a non-string member throws a TypeError naming the package and the offending index, before any SQL is issued.
• listResourceKeys(db, tables, appKey, tenantId) → string[] of every resource key registered in that tenant, ascending. Feeds the framework adapter's "show me all my permissions" port.

Resource keys and collation. SQL compares resource_key under the column's collation (the MSSQL default ignores case and trailing spaces), so the key a row comes back with need not be spelled the way you asked for it. A single-key request trusts SQL outright — whatever the collation, the answer is identical to the old one-query-per-resource behaviour. A multi-key request routes each row by exact match first, then by comparison form (trimmed + lower-cased); when two requested keys share that form the row is dropped rather than guessed (fail-close). Property order of the returned record is not part of the contract — iterate the keys you asked for.

• buildRowScopeWhere(mergedRowScope, columnMap, tenant) → SQL — the tenant predicate AND the row scope (three-valued: null→tenant only / {anyOf:[]}→1=0 / non-empty→OR-of-AND). Unknown field → 1=0 (fail-close). null in values → IS NULL/IS NOT NULL. A blank tenant.id throws AuthzTenantError.
• applyRowScope(qb, mergedRowScope, columnMap, tenant, existingWhere?) → qb.where(...) convenience. .where() is always called, so the tenant predicate cannot be omitted by accident.
• maskColumns(rows, mergedColumnScope) → field mask applied after fetch (allow & deny forms).
• ensureAuthzTablesExist(db, schemaName, userTableFkTarget, objectNamePrefix?) → Promise<void>. Checks schema, tables, indexes and constraints, and creates them if missing. Every statement is generated from defineAuthzSchema — columns, types, identity, defaults, primary keys, unique index columns and foreign keys all come from the model, so there is no second, hand-written copy to drift. It also adds tenant_id to a table that predates the tenant migration, and drops a superseded tenant-less unique index once the composite one exists. Existence is detected by the exact intended column SET — never by object name, and never by a column count, so running it against an already-migrated database can neither duplicate a constraint nor re-create the single-column unique index that would destroy tenant isolation. objectNamePrefix (default: the schema name) only affects the names of objects this function creates — set it to match your DDL scripts' naming.
• buildAuthzDdlStatements(schemaName, userTableFkTarget, objectNamePrefix?) → string[]. The same statements ensureAuthzTablesExist executes, returned for review or for running them yourself.
• createAuthzAdminStore(db, tables, { userTable, userScope, adminAppKey, adminResourceKey?, userActiveColumn?, ensureConnection?, lockoutMessage? }) → an AuthzAdminStore. userScope is required and must be either { kind: "global" } (one shared user table) or { kind: "per-tenant", tenantColumn } (users partitioned by that column); omitting it throws TypeError. There is no default, because assuming one silently decides whether a foreign tenant's users are listed, and whether they can be given this tenant's roles. Every write that references a user (assignUserRole / removeUserRole) proves the user first, exactly as the role, resource and group references are already proven.
• Deadlocked transactions are run again. SERIALIZABLE buys "the proof a write depends on does not change between the read and the write" at the price of contention: overlapping key ranges can deadlock, and SQL Server resolves a deadlock by rolling one side back. Nothing was committed there and the retry gets through once the other side is gone, so re-reading, re-deciding and re-writing is correct.
  • Which failures qualify — only the deadlock victim (1205), never a mere EABORT — lives in @aiquants/mssql, and each boundary wraps its closure in capture so the ORM cannot destroy the cause the decision needs.
  • Applied at every transaction boundary (serialized, withTransaction, createAdminLockoutGuard.guardedWrite), never when already inside an ambient transaction. Configure with transactionRetry (default 3 attempts, doubling with jitter; { attempts: 1 } disables). Pass onRetry — a silent retry hides that contention is rising.
• createAdminLockoutGuard(db, tables, opts) → { guardedWrite(tenantId, removal, op, write) }. Applies the same last-administrator check to destructive writes made outside this store (e.g. group membership edited by a separate user-admin surface). Check and write run inside one serializable transaction — there is deliberately no "check only" API, since splitting them lets two concurrent operations both pass. Like the store's own guard, it refuses when any tenant would lose its last administrator.
• seedAuthzTables(db, schemaName, { tenantId, appKey, adminEmails, userTable }) → Promise<void>. Idempotently inserts the default admin role, reserved roles (@authenticated, @anonymous), authz_admin resource, permissions, and assigns the admin role to users matched by the provided emails.

columnMap: Record<field, Column> is supplied per endpoint (the core layer does not know a table's columns).

Group-aware authorization

• getEffectivePermissions unions direct grants (TDUserRole) with group-derived grants (TDGroupRole). The TDGroupRole join is added only when groupIds is non-empty — joining unconditionally multiplies rows by the group table.
• @anonymous is always a candidate role, but @authenticated is included only when a user id resolved. Admitting it for an unauthenticated caller that merely carries group ids would be a fail-open.
• The lockout guard counts administrators from both paths, and (when userActiveColumn is supplied) excludes deactivated users — otherwise disabling the last administrator would go undetected. It evaluates every tenant the removal touches, not only the acting one: deactivateUserId names a host-side user id and the user table carries no tenant, so the write removes admin paths in every tenant at once. The refusal message names the tenants that would be left with none.
• The group path also joins TMGroup, filters its tenant_id, and projects it so the returned tenant is re-checked byte-exactly in memory — SQL compares under the column collation (case-insensitive by default), which is a weaker rule than the tenant contract. TMRole and TMResource tenants are projected and re-checked the same way.

Two rules that will bite you (drizzle/MSSQL)

1. Always mark identity columns. drizzle lists every schema column in an INSERT, so an id without .identity() emits insert into … ([id], …) values (default, …) and the database rejects it.
2. Declare only columns that exist. For the same reason, a column present in the schema but absent from the table fails every insert with Invalid column name. Tables that are only ever inserted into and deleted from therefore use a created_*-only audit set rather than the full created_*/updated_* one.

MIT