@aiquants/auth-directory-drizzle

Drizzle (SQL Server) adapter for @aiquants/auth-directory-core. It owns the identity-side tables and the four surfaces that operate on them.

Why these tables are not in @aiquants/authz-drizzle

Nothing in the authorization query path reads them — only authentication and user administration do. Beyond the separation of concerns there is a practical reason: @aiquants/authz-drizzle is a shared, published package, and adding a column or a table to it forces a DDL migration on every deployment that uses it. Drizzle puts every declared column in its INSERT column list, so a column the physical table does not have fails with Invalid column name (SQL Server 207) on the very next write. Deployments that never touch an external directory should not be dragged into that migration.

Tables

| Table | tenant_id | Notes | | --- | --- | --- | | TDGroupExternalLink | yes (denormalized) | One link per group. The column duplicates TMGroup.tenant_id so UNIQUE (tenant_id, provider, external_id) is expressible, and is also used as a predicate; writes must prove it matches the parent, and reads re-verify it against the parent | | TDGroupMemberEmail | no — inherited | Every member observed upstream, including people with no local user row. resolved_user_id is nullable and carries no foreign key: rows that cannot be resolved are the reason this table exists | | TDTenantMembershipGroup | yes (it is the key) | Groups a tenant accepts as proof of membership. At least one row must be is_break_glass and locally managed | | TDAllowlistGroup | yes (owner, not a filter) | Groups whose members may sign in | | TDDirectorySyncRequest | yes | The "sync now" queue. group_id is NULL for a whole-tenant run | | TMAllowlist | yes (owner, not a filter) | Moved here from @aiquants/authz-drizzle, now tenant-scoped |

No table declares a drizzle .default() on tenant_id, and the new tables carry no physical DEFAULT either. A default makes tenantId optional in the insert type, so a write that forgot the tenant type-checks and lands in whichever tenant the DBA happened to name.

"Owner" is not a filter

TMAllowlist.tenant_id and TDAllowlistGroup.tenant_id answer who may edit this row, not who may sign in. Authentication happens before a tenant is resolved, so the login decision is the union over every tenant this deployment serves. isEmailAllowedByGroup deliberately emits no tenant predicate — a regression test asserts its absence, because adding one silently breaks every first sign-in.

Surfaces

| Export | Role | | --- | --- | | defineAuthDirectorySchema | Table factory (schema name and referenced tables injected) | | createDirectoryLinkStore | Links, tenant membership proof, group allowlist, sync queue, health | | createDirectorySyncRepository | Storage operations for one sync run; removals go through the authz lockout guard | | assertMembershipMode | The single reader of membership_mode — no reader may default it | | promoteLedgerForUser | First-sign-in promotion of ledger rows to real memberships | | withExternalGroupGuards / assertGroupMayBeLinked | Keep administrative roles off externally-sourced groups, in both directions |

Removals go through the lockout guard, and one refusal does not stop the group

Group membership reaches roles through TDGroupRole, so one upstream departure can take a tenant's last administrator with it. Every projection removal is written inside createAdminLockoutGuard(...).guardedWrite(...) — the same serializable transaction the authorization admin UI uses. Removals are issued one at a time because AdminRemoval takes a single descriptor; adding a batch form to the authorization core would breach the package boundary, and the count is already bounded by the reconciler's circuit breaker.

Every transaction goes through one seam. All nine of them are opened by transactionRunner in db.ts, which runs the work again when — and only when — the server rolled it back (see @aiquants/mssql — only the deadlock victim 1205 is run again, and the closure is wrapped in capture so the ORM cannot destroy that evidence). Calling db.transaction directly at each site means touching nine places to change the policy, and one of them eventually gets left behind without saying so. Configure with transactionRetry on any factory's options; the closure must keep its side effects inside the database, because a retry runs it from the start.

A refusal does not abort the run. Stopping at the first refused removal leaves the additions committed, the earlier removals committed and the ledger untouched — and every later run recomputes the same plan and dies at the same index, so one legitimately-guarded row blocks the whole group's convergence forever. Refusals are collected into ApplyReport.refusals, the remaining removals proceed, and the caller records that the group has not converged. The refused person keeps both their membership and their ledger row: revoking sign-in while leaving the role would produce a state that makes sense from neither side.

Ledger deletions are chunked. SQL Server binds one parameter per value in an IN list against a 2,100-parameter statement limit; a group large enough to exceed it would fail with error 8003 after the memberships were already removed, and every subsequent run would fail identically — the state does not self-heal.

Deactivation is not a one-way door

A group missing upstream is deactivated only after vanishedThreshold consecutive absences, so a propagation window or a scope change does not disable a live group. Deactivated groups keep being polled, and a successful read reactivates them; setGroupActive gives an operator the same exit.

Deactivation revokes: is_active is part of the login gate and of the tenant-membership evidence, not only of the sync query. The reverse — enforced for syncing, ignored for access — leaves a frozen ledger granting sign-in to departed members while the only process that could clean it is switched off.

Promotion never reaches the directory

promoteLedgerForUser matches an identity against the ledger and inserts the missing membership rows. That is entirely local work, so the web tier can grant group permissions during a first sign-in without holding the directory credential — which stays with the sync job alone. Do not import a DirectoryProvider here; the placement is the point.

Testing the shape of the SQL

This adapter accepts drizzle through structural types, so tsc checks neither the order of the builder calls nor the presence of a tenant predicate: putting .innerJoin() before .from(), dropping .output(), or forgetting WHERE tenant_id = ? all type-check. link-store.spec.ts and sync-repository.spec.ts therefore record the actual call sequence against a fake builder and walk each condition for the columns it references.

sync-repository.ts is the only module that deletes rows, so it is tested directly rather than through the orchestration's fake repository — an orchestration test proves nothing about whether the real writer goes through the lockout guard. Fifteen mutations are each caught by at least one test, including: replacing guardedWrite with a bare db.delete, excluding deactivated groups from the sync query, pinning membership_mode to a constant, removing the IN-list chunking, dropping a tenant predicate, skipping reactivation, omitting the audit actor, opening the third escalation direction, ignoring appKey when deciding what is administrative, dropping is_active from the login gate, collapsing the identity precedence back to an OR, treating a removal-only run as "unchanged", and skipping the resolver completeness check.

Install

pnpm add @aiquants/auth-directory-drizzle @aiquants/auth-directory-core @aiquants/auth-core @aiquants/authz-core @aiquants/authz-drizzle drizzle-orm zod

All five @aiquants/* peers are value imports and every one is listed in this package's tsup external, so omitting any of them yields ERR_MODULE_NOT_FOUND at runtime rather than a type error. zod is required transitively by @aiquants/authz-core.