Separating Policy from Domain Logic in Fine-Grained Authorization

What CASL, Cerbos, OPA, and others taught us — and why we built our own

Summary: In an engineering college's administration platform, a single API endpoint must return 5,000 rows for a Dean, 400 for a department head, and 62 for a class advisor — server-filtered, per request, transparently. After evaluating CASL, Cerbos, OPA, Casbin, Oso Cloud, and Permify, none satisfied two Fine-Grained Authorization (FGA) requirements simultaneously: a live-database-configurable permission model (no code redeploy to change who can see what) and multi-hop relational join logic for domain-specific filtering rules. This article describes the three-question framework we built — what separates genuinely configurable policy from unavoidably custom domain code, what it costs to own, and when an existing tool would have been the right choice.

One Endpoint. Three Completely Different Result Sets.

Imagine three people at an engineering college, each opening the same web page: the student roster. The Dean clicks "Students" and sees 5,000 rows. The Head of the Computer Science Department clicks the same link and sees 400. A faculty member in the same department clicks it and sees 62 — her advisees, her course sections, her mentees. Same application. Same endpoint. Same database tables. Three completely different result sets, assembled in real time, transparently, without any of the three users knowing the other realities exist.

Same endpoint. Same tables. Same database. Results filtered server-side.

This is the problem we needed to solve. It sounds straightforward until you try to build it correctly.

Getting this wrong is not a UX problem — in an educational institution, it is a data privacy failure. A faculty member seeing another department's confidential exam results. A student seeing marks that aren't theirs. An administrator accessing mentoring notes that are meant to be private. Authorization errors in institutional software have real-world consequences, and the complexity only grows: our system manages 21 role types, roles that rotate every semester, and 15 distinct data resource categories each carrying different visibility rules.

The Open Web Application Security Project (OWASP) Top 10 2021 lists Broken Access Control as the most prevalent web application security risk — found in 94% of applications tested, with over 318,000 occurrences across the contributing dataset. In a domain-specific platform like academic institution management, the surface area is wide. Managing 315 possible (role, resource) permission combinations correctly — and keeping them correct as the institution changes — is the engineering challenge this article describes.

What Kind of Problem Is This?

Before diving in, a framing point: this is not a row-level security problem. It's a Fine-Grained Authorization (FGA) problem — also called FGAC (Fine-Grained Access Control) in enterprise contexts.

The distinction matters. Row-level security typically means "filter which rows of a specific table this user can see." What we needed was broader: define named resources — domain concepts like ATTENDANCE, MARKS, TIMETABLE, COURSE — that may span multiple joined tables, and define which roles can perform which actions (read, write, approve) on each resource type, with a scope that controls which instances of that resource are visible to each user.

A resource in our framework is a domain entity, not a database row. ATTENDANCE is a resource. An attendance record for a specific student in a specific session is an instance. The scope determines which instances each role can access.

The System Context

The application is a Node.js/TypeScript backend for academic institution management, structured as a monorepo with service boundaries sharing a single PostgreSQL database. Authentication is handled by Keycloak — an open-source identity and access management server that issues signed JSON Web Tokens (JWT) when users log in. Each token carries the user's roles as claims: DEAN, HOD (Head of Department), CLASS_ADVISOR, FACULTY, and so on. Every API request carries a valid JWT; the backend validates it and extracts those role claims.

Keycloak already answers one question well: "Is this person allowed to call this endpoint at all?" That coarse feature-level authorization is handled.

But Keycloak cannot answer: "For this resource type, which instances should this query return for this person?" That is the Fine-Grained Authorization problem this article is about.

The filtering must happen server-side, on every request, transparently. Client-side filtering — fetching all data and hiding some of it in the UI — is not security. It's theater.

Why Not Just Filter in the Code?

The first instinct when facing data visibility requirements is to handle it in the application layer. Let's trace through the obvious approaches and see where each breaks.

1. Fetch everything, filter in application code

Query all instances, then filter in Node.js based on the user's role. Simple to write, collapses under real load. During exam season, 300 faculty might be viewing dashboards simultaneously. Each request would load 5,000+ student records into memory, filter them, and discard the ones that don't apply. The database exists to filter data efficiently — pushing that work into application memory is the wrong direction.

2. Separate endpoints per role

Create /dean/students, /hod/students, /faculty/students. Works until the permission model grows. An institution with 21 role codes and 15 resource types would produce 315 endpoint variants — each with its own query logic. Adding a new role means touching every endpoint. There is no central place to see what any given role is allowed to do.

3. Hard-coded IF/ELSE in controllers

if (role === 'HOD') {
    query.where.deptId = user.deptId;
} else if (role === 'CLASS_ADVISOR') {
    query.where.groupId = { [Op.in]: user.groupIds };
}

Works for three roles and five resource types. By the time you reach eight roles, fifteen resource types, and multi-role users whose assignments rotate every semester, these trees become very hard to maintain consistently — authorization logic scattered across every controller and repository, with no central place to audit who can do what.

4. PostgreSQL views per role

Create dean_students, hod_students, faculty_students. Views are static — they cannot be parameterized by "which HOD's department." Schema explosion: 4 scope levels × 15 resource types = 60+ views. Role changes require schema migrations.

Note: the views introduced later in this article are a different category. dean_students encodes an identity in the schema — it cannot serve two different Deans. dept_student_reach encodes a join path; the caller provides dept_id as a runtime WHERE clause parameter. The anti-pattern dismissed here is identity-in-schema, not views as a SQL construct.

5. ORM default scopes (Sequelize / MikroORM)

Sequelize's defaultScope adds a WHERE clause to every model query. The problem: scopes are model-wide, not per-request. Injecting per-user context into a model-level global scope is fragile under concurrent requests. Beyond that, the correct scope for the same model differs by user, resource type, and request context — which is not what ORM scopes are designed for.

All naive approaches fail for one of two reasons: they filter at the wrong layer (application memory after fetching all rows), or they encode authorization logic redundantly across the codebase with no central place to manage it.

Evaluating the Authorization Landscape

Disclaimer: This section reflects our evaluation of these tools at the time we built this framework. Authorization tooling evolves rapidly — verify against current documentation. All limitations described are "in our evaluation, for our use case" — not absolute claims about what each tool fundamentally cannot do.

Having ruled out naive approaches, we looked at the established tools in the authorization landscape. There are now many. We'll work through the most relevant ones.

CASL — The Node.js FGA Standard

CASL is an isomorphic authorization library for JavaScript and TypeScript. It's arguably the closest thing to a "gold standard" for in-process FGA in the Node.js ecosystem — mature, well-maintained, and specifically designed for the problem of defining what a given role can do on a given resource type.

CASL's model maps directly onto the FGA problem:

// Define what a HOD can do
can('read', 'Attendance', { deptId: user.deptId });
can('write', 'Attendance', { deptId: user.deptId });
can('approve', 'Attendance', { deptId: user.deptId });

// Define what a Faculty member can do
can('read', 'Attendance', { groupId: { $in: user.groupIds } });
can('write', 'Attendance', { groupId: { $in: user.groupIds } });

What it does well: CASL's ability-building maps (role, resource, action) → conditions exactly as we need. The @casl/prisma package translates CASL conditions into Prisma query filters — which means it does push filtering to the database, not the application layer. Multi-action support is first-class. It's embedded — no external service.

Why it didn't fit our use case:

CASL does support storing rules in a database and building Ability instances from them at runtime — permissions can change without redeployment, taking effect on the next login or when ability.update() is called. That addresses the "no deploy for permission changes" requirement.

The limitation that ruled it out is more specific. CASL's condition model is attribute-based — field comparisons on resource instances ({ deptId: user.deptId }, { groupId: { $in: user.groupIds } }). With @casl/prisma (first-party), conditions can be nested relation paths — { program: { deptId: user.deptId } } — so multi-hop join conditions ARE expressible for Prisma users.

For Sequelize, CASL's v4 documentation shows a rulesToQuery helper pattern that bridges ability rules to Sequelize WHERE objects — but only for single-table attribute conditions. CASL's own documentation explicitly states: "as soon as you try to define permissions based on a related table, you are on your own." There is no first-party @casl/sequelize package (only @casl/prisma, @casl/mongoose, and @casl/objection are officially supported), and the rulesToQuery pattern has a known Sequelize v5+ operator alias incompatibility marked wontfix.

The second constraint that ruled it out: the anti-join exclusion rule ("students NOT exclusively in Chemistry's timetable cycle") is a correlated NOT EXISTS condition that doesn't map cleanly to CASL's condition syntax regardless of ORM.

CASL's scope conditions use Mongoose/Prisma query syntax. Our scope logic includes domain-specific rules that can't be expressed as field conditions: the first-year timetable ownership rule — which department owns the timetable shifts by semester (Physics in odd, Chemistry in even) requires a relational lookup, not a field equality check. The @casl/prisma bridge works well for simple attribute conditions; it breaks down when the condition itself requires a subquery with domain logic.

CASL does not natively model a ranked scope hierarchy (OWN → OWN_SECTION → DEPARTMENT → INSTITUTION) with conflict resolution for multi-role users — that logic must be built on top of its ability builder.

Cerbos — The Closest Structural Match

Of all the frameworks we evaluated, Cerbos is structurally the closest to what we built.

Cerbos defines resource policies — YAML files that map (principal_role, resource_type, action) → ALLOW/DENY with optional conditions. This is almost exactly our permission_policy table, expressed in a different medium:

resourcePolicy:
  resource: attendance
  rules:
    - actions: [read, write, approve]
      roles: [hod]
      condition:
        match:
          expr: request.resource.attr.dept_id == request.principal.attr.dept_id

    - actions: [read, write]
      roles: [class_advisor]
      condition:
        match:
          expr: request.resource.attr.group_id in request.principal.attr.group_ids

Cerbos's PlanResources API is the killer feature: rather than checking a single resource instance, it evaluates a policy against a principal and returns either "always allow," "always deny," or a conditional filter — an AST that the application translates to a database WHERE clause. This is query plan generation at the authorization layer.

What it does well: Typed resources with explicit actions, scope conditions, query plan generation, YAML-as-code policies that are versionable and readable, excellent Node.js SDK, and no mandatory cloud dependency (runs as a sidecar or embedded).

Why it didn't fit our use case:

Policy changes in Cerbos require deploying new YAML files (Cerbos Hub and remote bundle configurations support hot-reloading without a full redeploy — but in the self-hosted sidecar model, new policy files require a redeploy cycle). Our permission model is live data — the permission_policy table is writable at runtime, and role assignments in role_assignment change every semester without redeployment. The database is the policy store, which means any permission change takes effect immediately without a service restart.

Cerbos's condition model is attribute-based: the policy declares conditions as expressions over resource and principal attributes. Our domain-specific builders go further: the student filter is a UNION subquery across three independent membership paths (department enrollment, section assignment, mentoring), and the timetable filter resolves which department owns the timetable for a given semester via a relational join. These aren't expressible as attribute conditions against individual resource instances — they require query-time relational reasoning.

Entity-level grants (specific resource IDs granted to specific users or roles via resource_access_grant) would require a workaround in Cerbos, encoding grant lists as principal attributes and checking them in conditions.

PostgreSQL Row-Level Security (RLS)

RLS is PostgreSQL's built-in row filtering mechanism. Define a policy on a table, and the database automatically appends it to every query. We don't dismiss it — we already use it. For our Layer 3 concerns (disciplinary notes in disciplinary_record, mentoring session content), RLS is the correct tool. These are fields where no application-layer role should ever reach certain rows, and pushing that guarantee into the database is the right design.

For the FGA layer, RLS hit two walls. First: RLS policies are declarative rules evaluated by the Postgres query planner. Expressing our timetable ownership logic — which department owns which group's schedule in a given semester, a relational determination — would require calling a PL/pgSQL function from inside an RLS policy, testable only through the database executor's opaque logging, not in isolation. The SQL is possible; the debuggability cost is not acceptable for logic this complex.

Second: RLS policies are tied to tables, not domain resource types. ATTENDANCE as a resource spans a join across several tables. RLS applies to individual tables — a resource-spanning WHERE condition must be split across each table separately, which creates coupling between the authorization model and the schema.

On connection pooling: when using PgBouncer in transaction mode (common for microservices), bare SET commands leak context across transaction boundaries. SET LOCAL inside a transaction is safe — but it requires disciplined application code to always wrap session context setup in explicit transactions. One missed transaction boundary leaks context to the next pool client.

Casbin

Casbin is a well-established Role-Based Access Control / Attribute-Based Access Control (RBAC/ABAC) policy engine for Node.js. Its model supports (subject, object, action) tuples and can define role hierarchies, domain-scoped roles, and attribute conditions.

In our case, Casbin answers "can user X perform action Y on resource Z?" It does not generate SQL WHERE clauses. For row filtering, Casbin evaluates enforce() per resource instance — which is O(n) application-memory filtering, back to Approach 1. Casbin's "filtered policy loading" is for loading a subset of policy rules from storage, not for filtering application data.

If you only need access decisions (not bulk data filtering), Casbin is excellent. For generating database queries at request time, it requires custom code on top.

Oso Cloud

Oso Cloud was, conceptually, the second closest match to our need. Its Polar policy language supports partial evaluation: policies can be compiled into SQL WHERE clause fragments via evaluateLocalSelect() and evaluateLocalFilter(). This is actual SQL generation, not just boolean evaluation.

Two concerns pushed us away. Domain abstraction gap (same constraint as OPA): the timetable ownership rule — semester-dependent resolution of which department owns a group's schedule — requires context that must be pre-computed and injected into Polar. Oso ends up processing pre-built context rather than expressing the domain logic itself. Adapter gap: our Sequelize-with-raw-queries setup has no first-party Oso adapter; bridging would have been custom work regardless. On the HTTP question: evaluateLocalFilter() runs locally against a synced policy store — there is no per-query network round-trip for list queries. The external service dependency is for policy sync, not per-request evaluation.

We'd revisit Oso Cloud for a simpler permission model or a team without bandwidth to own a custom framework.

Open Policy Agent (OPA)

OPA's Rego language and Compile API are genuinely powerful. OPA's partial evaluation can produce SQL-usable filter logic through the Data Filters Compilation API — this goes further than generating an Abstract Syntax Tree (AST); it can produce SQL-like expressions.

What the Physics HOD rule looks like in Rego:

To illustrate the gap concretely, here is how the Physics HOD access rule — our most complex real-world rule — would be expressed in OPA's Rego:

package academics.students

# INSTITUTION scope — sees everything
allow if input.user.effectiveScope == "INSTITUTION"

# DEPARTMENT scope — student in dept's own programs
allow if {
  input.user.effectiveScope == "DEPARTMENT"
  some dept_id in input.user.deptIds
  data.students[input.student_id].program_dept_id == dept_id
}

# DEPARTMENT scope — student reachable via dept's courses or cycles
allow if {
  input.user.effectiveScope == "DEPARTMENT"
  some dept_id in input.user.deptIds
  input.student_id in data.dept_student_reach[dept_id]   # ← needs pre-computation
}

# EXCLUDE_DEPT_CYCLE — 1st-year, not exclusively in partner dept's cycle
allow if {
  some excl_dept in input.user.grantedItems.EXCLUDE_DEPT_CYCLE
  data.students[input.student_id].year_of_study == 1
  not student_in_cycle_dept(input.student_id, excl_dept)
}

student_in_cycle_dept(student_id, dept_id) if {
  data.cycle_dept_mapping[student_id][_] == dept_id   # ← needs pre-computation
}

The rules are readable. The problem is the two lines marked needs pre-computation.

data.dept_student_reach[dept_id] must contain the set of students reachable from each department via the four multi-hop join paths (program enrollment, curriculum course assignment, cycle ownership, cycle course teaching). OPA does not compute this — it must be pre-populated into OPA's data store from a background process. That background process runs the same multi-join SQL our framework runs inline at request time.

data.cycle_dept_mapping must contain the mapping of 1st-year students to their shared term departments. Same situation.

When OPA partial evaluation runs against this policy for the Physics HOD, it can SQL-ify the simple field check (year_of_study = 1) but produces opaque references for the set-membership checks (dept_student_reach, cycle_dept_mapping). Those opaque set references cannot be converted into SQL JOIN conditions by OPA's Compile API — they require a JOIN that OPA has no schema knowledge to construct.

The fundamental shape mismatch: Policy engines answer boolean decisions — "can user X see resource Y?" (one answer). Our student list problem is a set query — "which resources can user X see?" (a set answer). For simple attribute conditions (program_dept_id = <dept_id>), OPA partial evaluation maps neatly to SQL. For multi-hop join reach (dept → timetable_version → timetable_entry → shared_term_course → shared_term → dept), the partial evaluator produces a symbolic placeholder, not a WHERE clause.

The net result: OPA handles the policy interpretation layer, the team still writes the context assembly and JOIN logic, and in most Node.js deployments an OPA sidecar process is added to the system (OPA also supports embedded mode via WebAssembly (WASM), with some feature constraints). The join complexity doesn't go away — it moves to the data sync layer instead of the query layer.

OPA is the right choice when policy logic is complex, spans many services, or needs centralized management and audit trails. For list queries with multi-hop join paths in a single service with a domain-specific data model, the complexity trades don't justify the infrastructure addition.

Permify and OpenFGA (Zanzibar-inspired)

Both are built on Google's Zanzibar relationship-based authorization model. Zanzibar answers "does a relationship path exist from user X to object Y?" — a relationship check, not a filter generator.

Permify's Lookup API returns which entity IDs a user can access for a given action. The application must then translate that list into a SQL IN clause. For 5,000 students, this means fetching a list of potentially thousands of IDs from Permify and building a WHERE student_id IN (...) — which is feasible but not equivalent to a WHERE clause that can use indexes effectively.

These tools shine at Google-scale relationship graphs. Our institution has ~5,000 students and ~300 faculty. For our team, the operational overhead of a separate authorization service didn't justify the benefits at this scale and with a single-service permission model — though teams at similar scale with cross-service consistency or audit requirements may weigh this differently.

Keycloak Authorization Services

Keycloak's fine-grained authorization module (UMA 2.0 — User-Managed Access) defines resources, scopes, and policies. It operates at the API resource level — "can user X perform scope Y on resource type Z?" — and does not generate database query filters. Authorization decisions live in the Identity Provider (IDP), far from the query being constructed. JWT tokens cannot carry dynamic data filters without bloat and staleness problems.

The Comparison

In our evaluation, for a Node.js/Sequelize/PostgreSQL microservice with domain-specific FGA requirements and a live-configurable permission model:

¹ Caveats: PostgreSQL-only; startup cache requires a service restart for changes to take effect; identity resolution runs on every request with no caching; framework lives in a single service — other services bypass it entirely. See "The Real Costs" section.

Among the frameworks we reviewed, CASL and Casbin support DB-backed rule storage for simple attribute conditions. The deciding factor for our use case was not rule storage — it was condition expressibility: none of the frameworks could encode multi-hop relational join logic (four distinct paths from a department to its students) as a storable condition, regardless of whether the rules live in code or a database. That enforcement logic always requires custom code.

A clarification on the OPA row: OPA can generate SQL for simple conditions (attribute equality, field comparisons). The "Partial" rating reflects that multi-hop join paths — which account for the majority of our complex filtering — require pre-computation outside OPA and cannot be expressed in OPA's partial evaluation output as SQL JOINs. For a simpler domain with only attribute-based conditions, OPA's rating would improve.

Datascope: Database-Driven Policy, Domain-Specific Enforcement

The Three Questions

The framework answers three distinct questions, and keeping them separate is what makes it maintainable.

Who are you? — An identity question. Answered by combining JWT role claims with a single chained database query at request start: employee record, faculty record, active role assignments (time-bounded), assigned course IDs, and coordinated course IDs. One round-trip, one identity object.

What can you see? — A policy question. Answered by a database table (permission_policy) that maps (role_code, resource_type) → (scope_level, can_read, can_write, can_approve). INSERT a new row and the permission takes effect at next service startup — no code change required, only a restart.

Which instances qualify? — A domain question. Answered by code that encodes institutional business rules into SQL WHERE clauses. This is the part that cannot be generalized — it requires knowledge of the academic domain.

The three-layer architecture places the FGA layer between Keycloak (coarse API access) and PostgreSQL RLS (sensitive field isolation):

The Permission Model

21 role codes. 15 resource types. 4 scope levels. 3 action types. The cross product — every combination of role × resource × action × scope — is the permission model. It lives in permission_policy:

Sample rows:

Asymmetric read/write scopes are supported: read_scope_level and write_scope_level can differ per row. A Course Instructor might have DEPARTMENT-level read on MARKS but OWN_SECTION-level write — configured in one row, zero code change.

The Four Scope Levels

When a user holds multiple roles — which happens often (a person can simultaneously be HOD and a Course Coordinator) — the framework resolves to the highest-ranked scope level:

But as we'll see, that single winning scope level is not the whole story.

Request Flow

The scope context is assembled once per request and flows as a typed object. Controllers read permissions from it for access gates. Repositories consume the scope context to construct WHERE clauses. Neither layer needs to know how identity resolution works.

Inside the Framework: Two Layers

Looking inside the framework itself reveals a clean boundary and a messy one.

Layer 1 — Permission metadata (genuinely DB-driven)

Three tables define the policy:

• permission_policy: maps (role_code, resource_type) → scope_level + can_read/write/approve. Adding a new role's access to a resource is one INSERT. Zero code change.
• role_assignment: per-user role with scope context (dept_id, section_id, or resource-specific grants). Rotating HODs, assigning class advisors, granting EXCLUDE_DEPT_CYCLE — all database operations.
• resource_access_grant: role-wide entity grants — all holders of a role get specific IDs.

Layer 1 is three tables. Everything else is derived from them at request time.

This layer is genuinely configuration. It works well.

Layer 2 — Filter computation (where the domain lives)

This is where complexity lives. It has three distinct sub-cases:

The generic path works well for resources where scope maps to a single field: "does this record's dept_id match the user's dept?" But students are not reachable from a department via a single field — they are reachable through four distinct join paths:

Each path ends at the same student table but via a completely different join chain. A WHERE dept_id = X on any single table only catches path 1. The other three require joins that only the domain-specific builder knows to make. No generic field mapping can encode that. The custom SQL builder is necessary.

Understanding these two layers is critical for reasoning about extensibility. Adding a new role lives entirely in Layer 1: one INSERT, no code. Adding a new resource type with simple filtering also lives in Layer 1: add to permission_policy, point the generic builder at it, done. Adding a new resource type with complex multi-hop filtering requires a new Layer 2 builder — the SQL join logic that connects the user's identity to the instances they can see.

The Domain-Specific Builders — and the Honest Boundary of "Configuration"

The permission_policy table configures the policy: "HOD gets DEPARTMENT-level read and write on ATTENDANCE." What it cannot encode is the implementation: what "DEPARTMENT scope on ATTENDANCE" means in terms of actual SQL for this specific data model.

For attendance records, "DEPARTMENT scope" means: records for students enrolled in programs owned by your department, plus records for students in your department's timetable sections. That UNION is code, not configuration.

Three examples show why domain specificity was unavoidable:

The first-year timetable ownership problem. Engineering programs share Physics and Chemistry groups in the first two semesters. For timetabling purposes, these groups belong to the cycle department (Physics in odd semesters, Chemistry in even semesters) — not to the program's own department. A CS HOD can view first-year CS timetables but cannot edit them; edit access belongs to the Physics HOD. This semester-dependent ownership rule requires a relational join across student_section, shared_term, and dept to determine the effective owning department. It cannot be expressed as an attribute condition on a resource instance — it requires reasoning about the relationship between timetable groups and cycle departments at query time.

Multi-role student visibility. A user holding both CLASS_ADVISOR and HOD roles resolves to DEPARTMENT scope (highest rank). But their personal mentees — an OWN_SECTION-level relationship — must still appear in their student view. The student filter is always a UNION of branches: students in dept programs, students in assigned sections, personally mentored students, and students in their taught courses. The lower-scope branches are always included regardless of the winning scope level. This is multi-role correctness guaranteed by the builder, not derivable from the scope level alone.

The Physics HOD / Chemistry HOD cycle exclusion — a production case. Physics and Chemistry each own timetable cycles for first-year students. The Physics HOD should see all first-year students except those exclusively in the Chemistry cycle (those are the Chemistry HOD's concern). The Chemistry HOD gets the symmetric view.

This rule does not fit the standard DEPARTMENT scope model because the HOD's visibility crosses departmental lines — the Physics HOD needs to see first-year students enrolled in CSE, ECE, and every other program, as long as those students have a Physics cycle in their timetable. The relevant access control mechanism is a grant stored in role_assignment:

-- Physics HOD's grant: exclude students exclusively in Chemistry's cycle
INSERT INTO role_assignment
  (employee_id, role_code, resource_type, resource_id)
VALUES
  (<physics_hod_employee_id>, 'HOD', 'EXCLUDE_DEPT_CYCLE', <chemistry_dept_id>);

The intent of the rule, visualised:

┌──────────────────────────────────────────────────────────────────────┐
│                    All 1st-year students (~1,496)                    │
│                                                                      │
│   ┌──────────────────────────────────────┐                           │
│   │  Students exclusively in             │  ← Physics HOD does NOT  │
│   │  Chemistry's cycle                   │     see these             │
│   └──────────────────────────────────────┘                           │
│                                                                      │
│  Physics HOD sees: all 1st-year students outside the shaded area,    │
│  UNION Physics dept-scope students (upper years via curriculum, ~1)  │
└──────────────────────────────────────────────────────────────────────┘

A student enrolled in both Physics and Chemistry cycles still appears —
the rule excludes only those in Chemistry exclusively.

At request time, buildContext() picks this up and adds the Chemistry department's ID to grantedItems.EXCLUDE_DEPT_CYCLE. The student scope builder then emits:

-- DEPARTMENT branch: Physics programs + Physics shared term reach
SELECT student_id
FROM   dept_student_reach
WHERE  dept_id = ANY(ARRAY[<physics_dept_id>]::int[])

UNION

-- EXCLUDE_DEPT_CYCLE branch: all 1st-year students NOT in Chemistry's cycle
SELECT s3.id
FROM   student s3
WHERE  s3.year_of_study = 1
  AND  NOT EXISTS (
         SELECT 1
         FROM   cycle_dept_mapping cdm
         WHERE  cdm.student_id    = s3.id
           AND  cdm.term_dept_id = ANY(ARRAY[<chemistry_dept_id>]::int[])
       )

The dept_student_reach and cycle_dept_mapping are PostgreSQL views that encode the multi-hop join paths (explained in the next section). The resulting SQL is inlined directly into the student query WHERE clause — no separate service call, no pre-computed ID list, no N+1 pattern.

Among the frameworks we evaluated, none could express this rule as a first-class policy declaration without requiring pre-computation of the timetable ownership membership data outside the framework. The OPA Rego example earlier shows why: the "NOT IN partner's cycle" logic requires pre-computing cycle_dept_mapping outside OPA. In our framework, the logic lives in a queryable PostgreSQL view — inspectable and debuggable directly in psql, no separate process.

The implication for extensibility: Adding a new role to the system is one INSERT into permission_policy — zero code. Adding a new resource type requires writing a new SQL builder that handles all scope levels for that resource's data model. The configuration table tells the framework what is allowed. The builder tells the framework which rows implement that allowance. These are different concerns, and the builder is always code.

What the Framework Enables

Return to the three users from the opening.

The Dean loads the attendance dashboard. JWT carries DEAN. Identity resolution finds the institutional role. Context builder resolves INSTITUTION scope for ATTENDANCE. The clause builder generates no restriction — all attendance records. Five thousand students' worth.

The HOD loads the same page. JWT carries HOD. Identity resolution finds the HOD role assignment scoped to Computer Science. Context builder resolves DEPARTMENT scope, deptIds = [CS_ID]. The clause builder generates: attendance for students in CS programs UNION attendance in CS-assigned sections. Four hundred students' worth.

The Faculty member loads the same page. JWT carries CLASS_ADVISOR and FACULTY. Effective scope resolves to OWN_SECTION (CLASS_ADVISOR outranks base FACULTY). Context builder loads her group IDs and course assignments. The clause builder generates the UNION: her assigned sections, her timetable courses, her mentees. Sixty-two students' worth.

Same API call. Same endpoint. Correct, complete, server-filtered results. No client-side special casing.

Extensibility in practice: When the institution introduced a MENTOR_COORDINATOR role — someone with institution-wide visibility into mentoring sessions specifically, without gaining institution-level access to marks or timetables — the change was one INSERT:

INSERT INTO permission_policy (role_code, resource_type, scope_level, can_read, can_write, can_approve)
VALUES ('MENTOR_COORDINATOR', 'ACADEMICS_STUDENT', 'INSTITUTION', true, false, false);

The INSTITUTION branch in the student query builder already handles it. No new code.

The Evolution Path: Views Now, Strategy Pattern Later

Where the SQL Lives Matters

The original implementation embedded all multi-hop JOIN logic as TypeScript string templates. The Physics HOD EXCLUDE_DEPT_CYCLE subquery — six JOINs through section_enrollment → timetable_version → timetable_entry → shared_term_course → shared_term — was a multi-line string inside a TypeScript function. When a production issue appeared (the Physics HOD was not seeing 1st-year students), diagnosing it required reading TypeScript to understand the SQL, then mentally constructing the query without being able to run it in isolation.

The fix was to move the SQL into PostgreSQL views:

-- Replaces 6-join TypeScript string — now queryable directly in psql
CREATE VIEW cycle_dept_mapping AS
  SELECT DISTINCT se.student_id, st.dept_id AS term_dept_id
  FROM   student s
  JOIN   section_enrollment se ON se.student_id = s.id
  JOIN   timetable_version tv  ON tv.section_id = se.section_id AND tv.status != 'ARCHIVED'
  JOIN   timetable_entry te    ON te.timetable_version_id = tv.timetable_version_id
  JOIN   shared_term_course stc ON stc.shared_term_course_id = te.shared_term_course_id
  JOIN   shared_term st         ON st.term_id = stc.term_id
  WHERE  s.year_of_study = 1;

The TypeScript builder becomes thin:

// Was: 20-line inline SQL string with 6 JOIN aliases
// Now: readable reference to a named, queryable view
parts.push(`
  SELECT s3.id FROM student s3
  WHERE  s3.year_of_study = 1
    AND  NOT EXISTS (
           SELECT 1 FROM cycle_dept_mapping cdm
           WHERE  cdm.student_id    = s3.id
             AND  cdm.term_dept_id = ANY(ARRAY[${exclIds}]::int[])
         )`);

Three views cover all student scope paths:

• dept_student_reach(dept_id, student_id, reach_path) — all four department reach paths (program, curriculum_course, term_ownership, shared_term_course)
• section_student_reach(section_id, student_id) — OWN_SECTION membership
• cycle_dept_mapping(student_id, term_dept_id) — 1st-year shared term department mapping

The developer experience improvement is immediate. To diagnose "why doesn't Physics HOD see student X?":

-- Run directly in psql — no service restart, no log hunting
SELECT * FROM dept_student_reach WHERE student_id = 'abc-123';
SELECT * FROM cycle_dept_mapping WHERE student_id = 'abc-123';

The views approach improves where the SQL lives without changing how the framework dispatches to it. The TypeScript builder still contains the branching logic that decides which view to query for which scope level. That is appropriate for two complex-filter resources. It becomes painful at three or more.

The Strategy Pattern: A Direction for Scale

When a third resource type requires a complex SQL builder — say, LAB_BOOKING needs the same student-reach logic as ACADEMICS_STUDENT (only students physically accessible to this HOD should be bookable) — the current architecture requires:

1. A new TypeScript function with the reach SQL
2. The caller knowing to invoke it for LAB_BOOKING

This is exactly the pattern that becomes hard to maintain as resource types accumulate. A natural next direction — not yet validated in production at scale — is a strategy pattern with database dispatch: a scope_filter_type table declares which filter strategy applies to each (resource_type, scope_level) combination:

CREATE TABLE scope_filter_type (
  resource_type  VARCHAR(50),
  scope_level    VARCHAR(20),
  filter_key     VARCHAR(50),   -- names a registered handler
  PRIMARY KEY (resource_type, scope_level)
);

INSERT INTO scope_filter_type VALUES
  ('ACADEMICS_STUDENT', 'DEPARTMENT',  'DEPT_STUDENT_REACH'),
  ('ACADEMICS_STUDENT', 'OWN_SECTION', 'GROUP_MEMBERSHIP'),
  ('TIMETABLE',         'DEPARTMENT',  'DEPT_GROUP_REACH'),
  ('COURSE',            'DEPARTMENT',  'GENERIC_DEPT'),
  -- Third resource: reuses an existing strategy → zero TypeScript
  ('LAB_BOOKING',       'DEPARTMENT',  'DEPT_STUDENT_REACH');

The application registers handlers at startup, keyed by filter_key. Query construction becomes:

const filterKey = await scopeFilterTypeTable.lookup(resourceType, scopeLevel);
const handler   = filterRegistry.get(filterKey);          // runtime lookup
return handler(scope);                                     // returns SQL fragment

What this unlocks: LAB_BOOKING with DEPARTMENT scope needs the same student reach as ACADEMICS_STUDENT. Adding it is a single DB row — no TypeScript. The handler (DEPT_STUDENT_REACH) already exists, already uses the views, and is already tested through the student list flow.

ACADEMICS_STUDENT, scope=DEPARTMENT       LAB_BOOKING, scope=DEPARTMENT (new)
          ↓                                           ↓
scope_filter_type lookup                  scope_filter_type lookup
→ 'DEPT_STUDENT_REACH'                    → 'DEPT_STUDENT_REACH'  ← same key (1 DB row added)
          ↓                                           ↓
filterRegistry.get(...)                   filterRegistry.get(...)  ← same handler
          ↓                                           ↓
references dept_student_reach           references dept_student_reach  ← same view
          ↓                                           ↓
SQL fragment inlined into query           SQL fragment inlined into query

LAB_BOOKING: 1 DB row. No new TypeScript. Same tested handler and view.

The honest caveats: the dispatch table adds an indirection layer — tracing what SQL runs for a given request requires looking up the table, the handler registry, and the view, rather than reading one function. The handler registry must also stay in sync with the dispatch table; a filter_key in the DB with no registered TypeScript handler fails silently unless startup validates all registered keys. The pattern pays for itself when a third resource type reuses an existing reach strategy — at two complex-filter resources, the current TypeScript builders are simpler to reason about.

The strategy pattern is architectural overhead for two complex-filter resources. The views approach is the correct immediate step. The trigger for introducing the strategy table: when a third resource type needs its own complex filter, or when an existing filter strategy is needed by a second resource. The two approaches layer naturally — the views become the backing SQL for the strategy handlers; introducing the dispatch table does not require rewriting the views:

scope_filter_type table → filter_key → handler → references view → returns SQL fragment

The Real Costs of Owning This

1. "Configuration-driven" has a ceiling

permission_policy configures role-to-scope-and-action mappings. It does not configure WHERE clause logic. A new resource type always requires a new SQL builder. The accurate description: configuration-driven policy assignment, code-driven enforcement. With 15 resource types, there are 15 builders to write and maintain.

2. SQL and PostgreSQL only

The framework generates SQL WHERE clauses using PostgreSQL syntax. It cannot be applied to MongoDB, DynamoDB, Elasticsearch, or any non-relational store. Even within SQL, the clause syntax is PostgreSQL-specific. A full-text Elasticsearch search that needs scope filtering requires a parallel solution.

3. Startup-time rule loading — no hot-reload, no preview

permission_policy is loaded into an in-memory Map at service startup. Inserting a new row does not take effect until the service restarts. A DB migration that adds a new role's permissions will silently have no effect on the running service until the next deploy cycle. This has caught us once.

There is also a testability gap: after running the migration INSERT, there is no dry-run or preview mechanism to confirm the permission is correct before the service restarts. Correctness can only be verified after restart by exercising the affected endpoint. If the INSERT used the wrong scope_level or resource_type, you discover this in production. The diagnostic endpoint (GET /query/debug/user-scope) partially addresses this — it shows the resolved scope context immediately after restart — but there is no way to validate before the restart happens.

4. The effectiveScope hardcoding trap

resolveEffectiveScope() maps role names to scope levels via hardcoded TypeScript constants. A new role inserted into permission_policy silently does nothing until a developer also edits the TypeScript constant and deploys. A role that exists in the database but not in the TypeScript list is invisible to the framework.

The fix: derive effectiveScope from permission_policy at startup (the max scope_level across all resources for each role) rather than a hardcoded list. Until that change is made, new role additions require coordinated DB + code changes.

5. Framework lives in one service

Datascope lives in academics-service. Other services in the monorepo — admissions command, query service, reporting — bypass it entirely. A reporting query sees all data by default. There is no shared authorization middleware across the microservice boundary. Cross-service FGA consistency must be designed explicitly, not assumed.

6. Identity resolution on every request — no caching

The identity resolution query runs on every authenticated request. No per-user caching of scope context. During high-concurrency windows (exam submission, attendance close-out), this is a critical-path query running in parallel for every connected user.

7. "What does this user see?" is not directly queryable

Answering this requires running the full identity resolution + buildContext flow for that user. Diagnosing a "why can't this faculty member see this student?" issue means tracing the full authorization chain. We addressed this with a diagnostic endpoint (GET /query/debug/user-scope) that returns the fully resolved scope context — including the exact SQL subquery string that buildStudentScopeSubquery would emit — in a single authenticated API call. Without this kind of observability, scope bugs that run correctly are extremely hard to diagnose.

8. You own the complexity entirely

No community, no GitHub issues to search, no Stack Overflow thread for your specific rule. When a scope bug appears in production, diagnosis depends on institutional knowledge held by the team. New developers must understand the academic domain model before they can debug an authorization issue.

Choosing an Existing Framework: Questions Worth Asking

These assessments reflect our use case, our team, and our scale. Your domain constraints may make a different tool the obvious choice. Treat these as questions to apply to your own situation, not verdicts.

The frameworks that came closest for our use case: CASL for the Node.js ecosystem fit and TypeScript safety, and Cerbos for the structural match between its resource policy model and our permission_policy table. CASL does satisfy the live-DB-configurable requirement — rules can be stored in a database and reloaded at runtime without redeployment. Cerbos does not (YAML files require a deploy cycle). Neither handled multi-hop join conditions without custom code on top, and CASL's Sequelize bridge limitations meant the complex filtering logic had no first-party solution — which is why we ended up building our own.

Use CASL when your conditions are attribute-based and your ORM is Prisma. CASL supports DB-backed rules for runtime permission changes without redeployment. The @casl/prisma bridge gives genuine query-level filtering with TypeScript safety. For Sequelize projects, multi-hop join conditions require custom code on top.

Use Cerbos when you want policy-as-code with query plan generation, can manage YAML files as part of your deployment, and your filtering conditions map to attribute comparisons. Cerbos's PlanResources API is the most production-ready implementation of "generate a WHERE clause from authorization rules" available today.

Use OPA if policy logic is complex, spans many services, and your team needs centralized management with audit trails. Budget additional work for multi-hop join paths — the Compile API generates SQL for attribute conditions, not relational JOIN logic.

Use Permify or OpenFGA for large-scale relationship graphs with complex hierarchies and the bandwidth to operate a separate authorization service.

Build custom when: domain logic is specific enough that adapting a generic tool costs more than owning the code; the permission model must be configurable without redeployment; and the team that builds the system will also maintain it. The strategy pattern described above gives a custom framework a migration path toward reducing the TypeScript that must be written for each new resource type.

Two practical factors influenced our decision independently of the technical assessment. First, no prior production experience with any of these frameworks meant we couldn't assess real failure modes under our load patterns — a framework that looks correct on paper may have edge behavior that only surfaces under concurrent access or semester-rollover data volumes, and diagnosing that without institutional knowledge of the framework is a significant risk in a production system. Second, AI-assisted development has meaningfully reduced the cost of building custom code — the "adapting a framework costs more than owning the code" threshold is lower than it was a few years ago. Code that would have taken weeks to write and harden now takes days, which shifts the build-vs-buy calculus in favor of a solution the team fully understands and can debug without consulting external documentation.

The Takeaway

What changed for the product team and the institution: adding a new role type — from "the institution has hired Lab Coordinators" to "Lab Coordinators can view student attendance in their assigned labs" — is now a database operation, not a development sprint. The full permission matrix — who can read, write, and approve which resource types — is visible in a single table, auditable without reading code. When the question "why can't this faculty member see these students?" comes in from support, a single API call now answers it in seconds rather than requiring a debugging session across multiple codebases.

The trade-off is honest: the framework is PostgreSQL-specific, requires a service restart for permission changes to take effect, and still needs a developer to write SQL for each new complex resource type. It is not magic. It is a deliberate architectural choice that matched our team, our deployment model, and our institution's data privacy requirements.

The three questions define the framework:

Who are you? — Identity, resolved from JWT and database in a single query at request start.

What can you see? — Policy, stored in a database table: (role_code, resource_type) → (scope_level, can_read, can_write, can_approve). Takes effect at next service startup — no code change required.

Which instances qualify? — Domain logic, encoded as SQL WHERE clause builders that understand the institutional data model. For complex resources, those builders reference PostgreSQL views so the join paths are queryable and inspectable independent of the application.

The evolutionary architecture:

• Immediate (done): Views encode multi-hop SQL in migration files — queryable in psql, reviewable as SQL, independent of TypeScript changes.
• When the third complex-filter resource appears: Strategy pattern dispatch table — each new resource with an existing reach pattern is a single DB row, not new TypeScript.
• Future if policy spans services: Centralized policy engine (OPA or Cerbos) for boolean decisions; keep the views-based filter layer for list queries.

CASL answered the first two questions elegantly — rules can live in code or a database, with runtime updates supported. Cerbos answered them in YAML with query plan generation. Neither answered the third question — "which instances" — for a domain as specific as academic institution access control with semester-dependent timetable ownership and multi-role UNION semantics. That third question required code that knows the domain. The goal of the evolution path is to ensure that code, once written, can be reused across resource types through configuration — closing the gap between "add a new role" (one INSERT) and "add a new resource" (one INSERT, after the handler exists).

The Dean, the HOD, and the faculty member still don't know each other's view of the world. That's the point.

Built with Node.js, TypeScript, Sequelize, and PostgreSQL. The permission framework (Datascope) is a service-internal layer — not a standalone library. Production code is not shared here; the transferable artifact is the reasoning — problem decomposition, framework evaluation criteria, and architecture patterns applicable in any domain-driven application with similar FGA requirements. The domain-specific logic reflects an Indian engineering college's academic administration model, including semester-dependent timetable ownership of first-year student groups.