FINDINGS - OPA Partial-Evaluation Row-Level Filtering POC

Date: 2026-05-26 · Spike status: complete · Verdict: GO for the single-table, attribute-scoped case · GO (with caveats) for conjunctive cross-table joins - now proven end-to-end for a 2-table join (parties/party_roles) and a 3-table diamond (policy_elements/policies/policy_transactions) drawn from the policy-admin + enrollment schemas (§7) · NEEDS MORE INVESTIGATION for disjunctive joins and large relationship sets. Cost of Rego evaluation measured in §8 (point check and single-table list filter ≈ 0.5 ms; 3-table join ≈ 1.4 ms; flat vs registry size).

This is the decision artefact required by Section 11 of the design doc. It records what was built, what passed, where the translatable boundary actually sits, the measured query plans, and an honest estimate of what it costs to extend.

1. Did all Section 10 success criteria pass?

All criteria pass. Evidence is from docker compose run --rm tester (captured in test-output.txt) plus the Rego unit suite (opa test) and the translate package unit tests.

#	Criterion	Result	Evidence
10.1	Equivalence / no-drift - list set == point-check set, ≥500 actors/role	PASS	`member`, `provider`, `employer_admin`: 500 actors each, exact set equality held for all. Oracle bound to the point policy by `TestPointCheckBindsToOracle` (CanView agrees with the oracle on a 200-claim sample per actor).
10.2	Isolation	PASS	member returns only its own rows; provider spans 20 tenants across 231 claims (correctly not tenant-scoped); employer_admin returns only its tenant; unknown role → `clause="false"`, 0 rows (fail-closed).
10.3	Custom-role correctness (runtime-defined, no Rego edit / restart)	PASS	`regional_auditor` created at runtime via `POST /roles` (writes `data.claim_access.role_rules`); exact set match over subsets of 1/3/5 tenants and an empty subset (0 rows). No Rego change, no restart.
10.4	Sargability	PASS	Zero Seq Scans across the entire run. Every role used a Bitmap Index Scan / BitmapOr on the relevant index - including `employer_admin` (2,516 rows ≈ 5%) and `regional_auditor` (7,913 rows ≈ 16%). Plans in §4.
10.5	Injection safety + fail-closed	PASS	Payload `' OR 1=1; DROP TABLE claim; --` → `clause="claim.member_id = $1::uuid"`, payload carried as a bound arg, table intact (50,000 rows). Untranslatable `>` residual → translator errors → `DenyAll` (0 rows), never "all rows".

Supporting suites: opa test 8/8; translate unit tests 13/13 (8 shape cases

5 fail-closed cases).

2. Library vs hand-rolled translation

Finding: there is no maintained, general-purpose Go library that converts an OPA compile-API residual into SQL.

The official github.com/open-policy-agent/opa/rego package exposes .Partial() to get a typed in-process AST - but using it means embedding OPA as a library, which contradicts the OPA-as-server design (raw-mode modules and the role registry are managed via the OPA REST API) and pulls a very large dependency tree.
opa-compile-response-parser is JavaScript only - unusable from Go.
Styra Enterprise OPA ships a Data Filters Compilation API that returns SQL/UCAST directly, but it is commercial and explicitly out of scope.

Decision: hand-roll a constrained JSON-AST walker against the REST /v1/compile response.

Cost: internal/translate/translate.go is 254 LOC (315 incl. comments) - comfortably under the design doc's ~350-LOC watch threshold. It stays small because the Rego is constrained to a data-driven eq/in interpreter, so the residual is always a disjunction of conjunctions of equality / membership tests. This is the core trade-off: the policy is deliberately narrow so the translator can stay small and provably fail-closed.

A useful empirical confirmation: the in operator over a known array does not produce an internal.member_2 node - OPA expands it into a disjunction of separate eq queries (with the literal on the LHS and the column on the RHS, i.e. reversed operands). The translator handles both operand orders and both the member_2 and disjunction encodings.

3. The translatable boundary (what does NOT translate - fails closed)

Probed via raw-Rego mode (POST /roles/raw) and the translate unit tests. Each of these returns an error from the translator, which the caller turns into DenyAll (no rows):

Construct	Example	Why it fails
Comparison operators other than `eq`/`in`	`input.claim.amount_pence > 100000`	Operator `gt`/`lt`/`gte`/`lte`/`neq` not in the supported set. (Verified live: `err=unsupported operator "gt"`.)
Negation	`not input.claim.status == "void"`	`negated` expressions rejected.
Non-allowlisted column	`input.claim.secret == ...`	Column not in the allowlist map.
Residuals needing support modules	recursive / set-generating rules	`support` array non-empty → rejected.
Pattern/string builtins	`startswith`, `regex.match`, `glob.match`	Not in the supported operator set.
Comparison between two unknown columns	`input.claim.a == input.claim.b`	Equality must have exactly one claim-column side and one literal side.

This is the headline limitation to carry into Phase 3: the moment a policy needs ranges, negation, text matching, or anything richer than column eq/in value, either the translator grows (and its fail-closed guarantees get harder to audit) or those rules must be enforced elsewhere.

4. EXPLAIN plans per role (50,000 rows, indexes on member/provider/tenant)

All plans are index-based; no Seq Scans.

Role	Generated WHERE	Plan	Rows	Exec time
`member`	`claim.member_id = $1::uuid`	Bitmap Index Scan `idx_claim_member`	5	0.09 ms
`provider`	`claim.provider_id = $1::uuid`	Bitmap Index Scan `idx_claim_provider`	253	0.25 ms
`employer_admin`	`claim.tenant_id = $1::uuid`	Bitmap Index Scan `idx_claim_tenant`	2,516	1.11 ms
`regional_auditor`	`claim.tenant_id = $1::uuid OR $2::uuid OR $3::uuid`	BitmapOr of 3 `idx_claim_tenant` scans	7,913	2.39 ms

The regional_auditor case is the important one: a disjunction over a low-cardinality column at ~16% selectivity still planned as a BitmapOr of index scans, not a Seq Scan. The explicit $N::uuid cast (applied to the constant, never the column) preserves index use.

Caveat for scale: the in/disjunction expands to one OR term per array element. A role scoped to a handful of tenants is fine; a role scoped to thousands of ids (see §5b) would generate a thousands-term OR - query bloat and planner-cost risk. That is a real Phase-3 concern, not a translation defect.

5. Effort to extend (honest estimates)

(a) The real claim schema - ~0.5 day. Add the filterable columns and their SQL types to the allowedColumns maps in translate and roles. No structural change. The only judgement is which columns are legitimately filterable and their cast types.

(b) Broker + partner-insurer roles - ~2-3 days, with a scaling caveat. These are relationship-scoped (broker → schemes → members). They can be modelled in the current data-driven form if the relationship is flattened into an actor attribute (e.g. actor.scheme_ids = [...]) and the rule is claim.scheme_id in actor.scheme_ids. That works - but a broker with thousands of schemes/members produces a thousands-term OR residual (§4 caveat). For small relationship sets it's fine; for large ones this approach does not scale and a join (see d) or a relationship engine is the right tool.

(c) A second table - ~1-2 days. Generalise the hard-coded claim. prefix and per-table column allowlist into a per-entity registry, and parametrise the unknown ref (input.<entity>). The walker itself is already entity-agnostic apart from those two spots.

(d) Joins / cross-table policies - the conjunctive single-hop case now works (see §7); deeper cases still ~1-2 weeks + maintenance risk. Originally flagged as the hard edge. A follow-up probe (§7) proved end-to-end that when two tables are declared unknown, OPA leaves the inter-table equality in the residual and a 271-LOC, schema-configurable translatejoin package assembles a correct, sargable JOINed query whose result matches the point check exactly. That covers the common "filter table A by a property of related table B" policy. Still open: disjunctive join residuals (currently fail closed), multi-hop chains beyond two tables (the assembler is written to chain but only 2 tables were tested), and large relationship sets (the in-list blow-up from §4 applies inside joins too). For those, still recommend a head-to-head with a Zanzibar-style system (SpiceDB / OpenFGA) before committing platform-wide.

6. Recommendation for the Phase 3 decision

GO - for single-table, attribute-scoped authorization (the member/provider/employer/regional shapes). It is genuinely no-drift (one policy drives both the point check and the list filter), sargable, injection-safe, fail-closed, and the translator is small (254 LOC). Custom roles are addable at runtime with no Rego change.

GO - for single-table, attribute-scoped authorization (the member/provider/employer/regional shapes). It is genuinely no-drift (one policy drives both the point check and the list filter), sargable, injection-safe, fail-closed, and the translator is small (254 LOC). Custom roles are addable at runtime with no Rego change. NEEDS MORE INVESTIGATION - for relationship-heavy roles (broker/partner-insurer) and cross-table joins. Two concrete risks: (1) large in-lists expand to oversized OR residuals; (2) join translation is a significant, higher-maintenance build. Run a SpiceDB/OpenFGA comparison for these before adopting partial-eval platform-wide.
NEEDS MORE INVESTIGATION - for relationship-heavy roles (broker/partner-insurer) and cross-table joins. Two concrete risks: (1) large in-lists expand to oversized OR residuals; (2) join translation is a significant, higher-maintenance build. Run a SpiceDB/OpenFGA comparison for these before adopting partial-eval platform-wide. Orthogonal, recommended regardless - keep Postgres RLS as the coarse tenant-isolation backstop. Partial-eval enforces at the application layer; RLS is the layer that survives an application bug. They compose; this POC does not replace that recommendation.
Orthogonal, recommended regardless - keep Postgres RLS as the coarse tenant-isolation backstop. Partial-eval enforces at the application layer; RLS is the layer that survives an application bug. They compose; this POC does not replace that recommendation. Cross-table joins (follow-up, §7) - GO with caveats for the conjunctive single-hop case. Proven against a mirror of policy-admin's parties/party_roles. Keep disjunctive joins, multi-hop chains, and large relationship sets in the "investigate / compare to Zanzibar" bucket.
Cross-table joins (follow-up, §7) - GO with caveats for the conjunctive single-hop case. Proven against a mirror of policy-admin's parties/party_roles. Keep disjunctive joins, multi-hop chains, and large relationship sets in the "investigate / compare to Zanzibar" bucket.

This POC is one input into the Phase 3 authorization-architecture decision, not the decision itself.

7. Follow-up: cross-table joins against the policy-admin schema

Question: does partial-eval → SQL still work when the policy spans two tables?

Setup. A simplified mirror of policy-admin's parties and party_roles (party_roles.party_id → parties.id). Policy party_access.rego declares bothinput.party_role and input.party unknown:

rego

allow if {
    input.party_role.party_id == input.party.id      # JOIN condition (two unknowns)
    input.party.type == "PROVIDER"                    # filter on joined table
    input.party.locator == input.actor.party_locator  # filter on joined table (actor literal)
}

What OPA emits. A single conjunctive residual in which the inter-table equality survives as a first-class condition. The translatejoin package classifies each equality - ref==ref across entities → JOIN; ref==literal → WHERE

and assembles:

sql

SELECT pr.id FROM party_roles pr JOIN parties pa ON pr.party_id = pa.id
WHERE pa.type = $1 AND pa.locator = $2

Results (all pass):

Check	Result
Translation shape	JOIN on `pr.party_id = pa.id` + filters on `pa.type`/`pa.locator`, fully parameterised
No-drift equivalence	200 provider actors, exact set equality vs an independent hand-written join oracle
Binds to point policy	every returned `party_role` passes the join point check; 100 non-returned all denied
Sargability	Nested Loop, 0.1 ms - `idx_parties_type` then `idx_party_roles_party_id` (`Index Cond: party_id = pa.id`); no Seq Scans
Fail-closed	disjunctive join residual (>1 query) returns an error → no rows

7.1 Harder case - 3-table diamond join (enrollment schema)

A more complex policy on a mirror of enrollment's policy_elements / policies / policy_transactions: an element is filtered through both its policy and its governing transaction, and the transaction must belong to the same policy - a diamond. Three unknowns, three join conditions (one of them between two already-joined tables), filters on two different joined tables:

sql

SELECT pe.id FROM policy_elements pe
  JOIN policies po              ON pe.policy_id = po.id
  JOIN policy_transactions tx   ON pe.transaction_id = tx.id
WHERE tx.policy_id = po.id           -- the diamond predicate (recovered, not dropped)
  AND po.broker_locator = $1 AND po.status = $2 AND tx.category = $3

The diamond exposed and fixed a real translator bug: an equality between two already-joined tables must become a WHERE/ON predicate, not be silently dropped (dropping it would have admitted elements whose transaction belongs to a different policy). Results:

Check	Result
Translation	2 JOINs + diamond predicate `tx.policy_id = po.id` + 3 filters, parameterised
No-drift equivalence	100 broker actors, exact set equality vs hand-written 3-join oracle
Binds to point policy	every returned element passes the 3-table point check; 200 non-returned all denied
Sargability	Nested-loop of Bitmap Index Scans (`idx_policies_broker` → `idx_txn_policy` → element index), diamond enforced as a Join Filter, 0.1 ms, no Seq Scans

Limitations of the join translator (by design, for the probe):

Single conjunctive query only. Disjunctive join residuals fail closed (a real gap to close, not a defect).
Entity → table/alias/column mapping is hand-configured - schema coupling, as the design doc's risk table anticipated.
Tested to 3 tables / diamond.buildFrom chains arbitrarily many JOINs and now recovers diamond predicates; chains beyond 3 tables are plausible but untested.
Large in-lists inside joins would blow up the same way as §4.

Takeaway. The cross-table case is not the wall the original FINDINGS feared - for the common "filter A by a property of related B" shape, including a 3-table diamond, it works cleanly and sargably. Remaining risk is concentrated in disjunctions and large relationship sets, which is where a relationship-native engine (SpiceDB/OpenFGA) should be compared head-to-head.

8. Cost of Rego evaluation

Measured end-to-end from the Go service to OPA over the local Docker network (so each figure includes one HTTP round trip - the number a service co-located with OPA as a sidecar would see). N as noted; warm OPA; 50k claims / 6k elements seeded.

Per-path latency:

Path	mean	p50	p95	p99
Point check - `eval`, 1 table	569 µs	501 µs	912 µs	1.18 ms
List filter - `compile`, 1 table	548 µs	506 µs	819 µs	1.39 ms
List filter - `compile`, 2-table join	757 µs	647 µs	1.26 ms	1.80 ms
List filter - `compile`, 3-table diamond	1.37 ms	1.43 ms	2.17 ms	2.83 ms

Latency vs registry size (member role, partial eval, N=200):

roles in registry	mean	p50	p95
3	681 µs	577 µs	1.08 ms
50	633 µs	541 µs	1.07 ms
200	524 µs	509 µs	630 µs
500	576 µs	521 µs	909 µs

Reading:

Partial eval is not more expensive than a point check for the simple case - compile (548 µs) ≈ eval (569 µs). The "compile a SQL filter" path costs about the same as "is this one row allowed".
Each join hop adds ~0.2-0.6 ms. 1-table ≈ 0.55 ms → 2-table ≈ 0.76 ms → 3-table ≈ 1.37 ms. Still low-single-digit-ms at p99.
Registry size is irrelevant. 3 → 500 roles shows no upward trend (partial eval only touches role_rules[input.actor.type]). Adding roles does not tax evaluation.
- JSON marshalling. Embedding OPA in-process (rego.PartialResult / preparing the query once) would cut the single-table paths toward the tens-of-µs range - at the cost of the OPA-as-server model. The translate step itself is pure-Go and negligible (µs).
The dominant cost is the HTTP round trip, not the evaluation. Most of these figures are network
Practical implication for the design's "lists never hit the vault" rule: a list request pays ~0.5-1.4 ms once for the filter, then a single SQL query - not per-row. This is comfortably within a request budget.

9. How to reproduce

bash

cd roles-poc
docker compose up -d --build          # Postgres + OPA + seed (50k claims) + server
open http://localhost:8088            # the four-panel harness
docker compose run --rm tester        # the full Section 10 suite

# component-level checks (no stack needed):
docker run --rm -v "$PWD/policy:/policy" openpolicyagent/opa:latest-static test /policy
docker run --rm -v "$PWD/service:/src" -w /src golang:1.23-alpine \
  sh -c "go test ./internal/translate/... -v"

Reproduce / source files

cd /root/roles-poc && docker compose up -d   # then run the harness (see README)

Source (raw):

FINDINGS - OPA Partial-Evaluation Row-Level Filtering POC ​

1. Did all Section 10 success criteria pass? ​

2. Library vs hand-rolled translation ​

3. The translatable boundary (what does NOT translate - fails closed) ​

4. EXPLAIN plans per role (50,000 rows, indexes on member/provider/tenant) ​

5. Effort to extend (honest estimates) ​

6. Recommendation for the Phase 3 decision ​

7. Follow-up: cross-table joins against the policy-admin schema ​

7.1 Harder case - 3-table diamond join (enrollment schema) ​

8. Cost of Rego evaluation ​

9. How to reproduce ​

Reproduce / source files ​