List filters

A list endpoint never loads everything and filters in memory, and never paginates before filtering. It asks Cerbos for a plan, the condition under which a record of a kind would be allowed, and translates it into SQL that goes in the WHERE clause.

plan, err := a.Plan(ctx, s, accessgen.LeaveRequestRead)
f, err := sqlfilter.Translate(plan, columns, sqlfilter.Options{StartAt: 2})
// where lr.org_id = $1 and <f.SQL> order by ... limit ...

const plan = await access.plan(s, P.LEAVE_REQUEST_READ);
db.select().from(leaveRequests).where(and(eq(leaveRequests.orgId, s.orgId), drizzleFilter(plan, columns)));

Plans

For someone holding leave.request:read for their team (path /u_1/u_42/) and for the fin org unit, the translation is (from the shared conformance fixtures):

(lr.org_id = $1 AND ((lr.owner_path LIKE $2 ESCAPE '\' AND lr.owner_id <> $3) OR lr.org_unit_id = $4))
-- args: org, '/u\_1/u\_42/%', 'u_42', 'fin'

The org check is in the plan too; keep your own org_id = $1 anyway, so an index on it is always used.

Column maps

The map says which SQL expression holds each attribute:

var leaveColumns = sqlfilter.Columns{
	"orgId": "lr.org_id", "ownerId": "lr.owner_id", "ownerPath": "lr.owner_path",
	"orgUnitId": "lr.org_unit_id", "status": "lr.status", "days": "lr.days",
}

• Map every declared attribute a policy can reference. A plan that uses an unmapped attribute is an error (no column mapped for attribute "x"), never a wider query.
• Column expressions are trusted SQL: constants in code, never user input. Values always go in parameters.
• With Drizzle, map to columns (leaveRequests.ownerPath); drizzleFilter binds them.

Parameters

sqlfilter and planToSql number parameters from StartAt / startAt (default 1), so the filter fits after your own. planToSql can emit ? placeholders instead (placeholder: "question"). Go returns {SQL, Args}, TypeScript { sql, params }.

Operators

The translators handle exactly what generated policies produce and refuse everything else:

! becomes IS NOT TRUE rather than NOT, so a NULL column keeps a row exactly when Cerbos's own check would (a comparison with null is false; its negation is true). Org rules rely on it: their deny joins the plan as a negation.

A condition with another operator (a function call, arithmetic) fails translation with operator "x" is not supported. Rewrite it, or keep that rule out of list endpoints.

Escaping

Prefix scopes match paths. Without escaping, _ in /u_ceo/u_1/ is a LIKE wildcard, so the pattern would also match /u_ceo/uX1/, someone else's record. The translators escape %, _ and \ and add ESCAPE '\'. Test it: put a look-alike path next to a real one in your fixtures (see Testing). It's why zynactra/access has its own translator instead of @cerbos/orm-drizzle (ADR 5).

Indexes

Plans are plain predicates on your columns, so ordinary indexes work: (org_id, owner_id), (org_id, org_unit_id), and for prefix scopes a text_pattern_ops index on owner_path so LIKE 'prefix%' uses it.

Counts and pages

Use the same filter for the count and the page, and put ORDER BY and LIMIT after it. Cursor pagination works unchanged: add your cursor predicate next to the filter.