Drizzle

permdock/drizzle takes the portable condition returned by permdock.where(permission) and turns it into a Drizzle SQL expression bound to a table. The same conditions feed permdock rls generate --target drizzle, which emits pgPolicy(...) entries next to the table definition so drizzle-kit owns the migration.

PurposeCopy Anchor Link

A condition such as where: { authorId: principal.id } is evaluated in memory by can, serialised into snapshots for the client, and must also filter a list query in the database, or the UI and the API disagree about which rows exist. CASL proved that one condition AST can drive an in-memory interpreter and a where builder, and that priority must be respected when flattening allows and denies (landscape). permdock/drizzle is the Drizzle interpreter for PermDock's conditions, and Drizzle is the primary RLS compile target because pgPolicy and drizzle-orm/supabase already model policies as code (Postgres RLS).

APICopy Anchor Link

import { toWhere } from "permdock/drizzle";
import { posts } from "./schema";

const rows = await db
  .select()
  .from(posts)
  .where(toWhere(permdock.where(permissions.post.read), posts));

// combine with app filters: toWhere returns a Drizzle SQL expression
await db
  .select()
  .from(posts)
  .where(
    and(
      toWhere(permdock.where(permissions.post.read), posts),
      eq(posts.published, true),
    ),
  );

// column mapping when schema field names differ from Drizzle column names
toWhere(condition, posts, { columns: { authorId: posts.author_id } });

// graph grants: related nodes compile to a subquery over the relation tables
toWhere(permdock.where(permissions.doc.read), docs, {
  relations: { closure: "permdock.permdock_closure" },
});

// one row: not found, or found with the decision
const check = await checkRow(
  db,
  posts,
  permdock.where(permissions.post.update),
  eq(posts.id, id),
);

// run a transaction as the subject, so RLS policies see it
await withSubject(db, permdock, (tx) => tx.select().from(posts), {
  dialect: "guc",
});

• toWhere(condition, table, options?) returns a Drizzle SQL and maps each condition field to a column of table by name (or via options.columns), and each operator (eq, ne, in, notIn, gt, gte, lt, lte, isNull, contains, and, or, not) to the Drizzle operator of the same meaning. table is any Drizzle table object; no cast is needed. options.columns accepts only columns of that table or SQL, so a column of another table is a type error.
• options.relations ({ tables?, closure? }) lets a related node compile: one subquery over the edge tables, the closure table when named, otherwise a recursive walk of the resource's own table bounded by the grant's depth (relationships). Without it a related node throws non-portable-condition.
• checkRow(db, table, condition, key, options?) selects the rows matching key with the filter as a boolean column and returns { found: false } or { found: true, granted }, so a handler can answer 404 and 403 apart with one query. More than one row matching key throws.
• withSubject(db, permdock, fn, options?) opens a transaction, sets the local role and the claim settings for the frozen subject in one select set_config(…) statement, then calls fn(tx). options are dialect (supabase, guc, neon), role (default authenticated with a principal, anon without, false to leave the role), gucPrefix, tenantClaim and extra claims. They must match what permdock rls generate was configured with; withSubject does not read the config. drizzle-orm is an optional peer; tests can inject options.operators. The call stays two-step (permdock.where then toWhere) so the adapter never needs the instance.
• contains on an array column (dataType: 'array') is value = any(column); on a string it is like with %, _ and \ escaped; any other value is @>.
• NULL follows the in-memory evaluator: a comparison with a NULL row value or an unresolved reference never matches, and not keeps NULL rows (not (x = 1) compiles to x <> 1 or x is null).
• memberOf compiles from the frozen subject when options.memberships is omitted: tenant scope with an active principal.tenant is equality on that id, otherwise inArray over held tenants; team scope is inArray of team ids (dropping memberships whose tenant is not the active one); resource scope is inArray on the row field plus or over parents, a keyed parent ({ field, resource }) using only memberships on that resource. With options.memberships, it emits exists (select 1 from <table> m ...): one bound parameter per role (the role predicate is skipped when the list is empty), expires_at > now with now bound in whole Unix seconds (rounded up, so a membership expires up to a second early, never late), the active tenant on tenant and team scopes, and m.<resource> = '<name>' when the table has a resource column. A keyed parent hop uses memberships.resource[<parent>] and fails closed when that resource has no table.
• principal.<field> and context.<key> references are resolved to bound parameters from the request-scoped PermDock before compilation; the result contains no user-controlled SQL.
• When nothing is granted, toWhere returns a constant false SQL expression so the query yields no rows (fail closed).
• Closure grants cannot compile; permdock.where reports them as { portable: false } and toWhere throws PermDockValidationError with the offending grant.

RLS generation reuses the same compiler:

// emitted by: permdock rls generate --target drizzle --dialect supabase
import { sql } from "drizzle-orm";
import { pgPolicy } from "drizzle-orm/pg-core";
import { authenticatedRole, authUid } from "drizzle-orm/supabase";
import * as schema from "./schema";

export const postReadMember = pgPolicy("post_read_member", {
  as: "permissive",
  for: "select",
  to: authenticatedRole,
  using: sql`("author_id" = ${authUid})`,
}).link(schema.posts);

Each policy is linked to its table with .link, so the table definition stays untouched and drizzle-kit picks the policies up from the schema. The guc and neon dialects declare pgRole('authenticated').existing() in the file instead of importing from drizzle-orm/supabase. Grants, column privileges, enable row level security and the helper functions have no Drizzle form, so they go to <out>.migration.sql next to the policies file; apply it with your migrations. rls.drizzle.schema sets the import path and rls.drizzle.exports maps a table to its export name when it is not the table name in camelCase (RLS).

Request lifecycleCopy Anchor Link

1. The request-scoped PermDock is created from the policy and subject.
2. permdock.where(permission) flattens the grants for that permission: each allow condition is ANDed with the negation of all higher-priority denies, and the results are ORed. Unconditional allow yields true; no grant yields false.
3. toWhere walks the resulting tree and produces a Drizzle SQL expression bound to the table's columns, with subject values as parameters.
4. The app composes the expression with its own filters and runs the query.
5. on('decision') fires once with outcome derived from whether anything was granted, plus permdock.filter-style metadata for observability.

For RLS, the same tree is compiled by the CLI at build time instead of per request; principal.id becomes (select auth.uid()) under the supabase dialect, a GUC read under guc, and auth.user_id() under neon (see RLS).

What it validatesCopy Anchor Link

• Every field in the condition exists on the table (or in options.columns); a missing column throws at compile time in tests and at first use in production with the field name.
• Operator and column type compatibility is left to Drizzle's own typing; toWhere is typed so condition must come from the resource whose schema the table was declared for when drizzle-zod schemas are used in the definition.
• Subject values are always parameters, never interpolated.
• Nothing about the rows themselves: where runs before rows exist, and rows coming back from the database are trusted (validate: 'boundary' does not run on them).

How denials surfaceCopy Anchor Link

• No grant: a constant false expression and an empty result; the caller sees zero rows, not an error. Combine with assert(permissions.post.list) beforehand to distinguish "forbidden" from "not found" (CASL issue #794 describes the ambiguity).
• Non-portable grant: PermDockValidationError with reason: 'non-portable-condition' and the grant's role, so it is caught in tests rather than at runtime.
• In RLS mode: a USING policy filters silently; a WITH CHECK policy raises 42501; permdock rls verify classifies both.

Example appCopy Anchor Link

apps/examples/drizzle: a Hono API over an in-process PGlite database. GET /posts selects through the list filter, behind the Hono permdock() middleware. PATCH /posts/:id runs protect with a loader, then updates through the update filter; a denial is a 403 and a missing row a 404, both Problem Details. tests/integration/src/orm-parity.test.ts compares filter in memory with toWhere results on node-postgres and PGlite for every operator, NULL and negation case, reference, memberOf scope, parent hop and expired membership (ormParity in permdock/testing), and checkRow with can() for each row. orm-graph.test.ts does the same for graph grants, with-subject.test.ts runs withSubject against generated RLS as a non-owner role, and rls-orm-targets.test.ts applies the generated pgPolicy file to Postgres.

WhyCopy Anchor Link

• A hand-written Standard Schema wins over a drizzle-zod schema. rls import --from drizzle writes a separate // @generated module whose resources reference the tables' drizzle-zod schemas. A resource the application defines by hand keeps its own schema, and its entry in the generated module is left out of the merge: the hand-written schema is the boundary contract (invariant 9) and usually narrower than the table, for example without server-set columns. One resource is never defined with two schemas. Both are Standard Schemas, so toWhere and validation treat them the same, and permdock usage reports a condition on a field neither declares (usage).

Related standardsCopy Anchor Link

• Postgres RLS: policy semantics that the generated pgPolicy entries follow, Drizzle pgPolicy, drizzle-orm/supabase helpers and crudPolicy for Neon.
• Conditions: the portable operator set.
• RLS adapter: generate, import, verify.