E9 Option B — CQL→SQL pushdown transpiler (research-grade epic)

[Taleef7]

What this is

The build half of the E9 fork. The decision half shipped 2026-07-08 (ADR-025, closing #78): measure execution is pluggable behind the MeasureExecutor seam — fhirNativeExecutor is the default and the correctness oracle, and sqlPushdownExecutor (backend-ts/src/engine/measure-executor.ts) is an inert stub selected only by an explicit WORKWELL_MEASURE_EXECUTOR=sql-pushdown opt-in. This epic is filling that stub with a real transpiler. Research-grade, self-paced, off the critical path — a side project that can run alongside the MIE integration work without blocking or being blocked by it (except for the triggers below).

Why it could matter

Per-subject FHIR-native CQL evaluation costs ~68 ms/eval (measured, #253 N=5000 Neon proof). A full 120k-subject × 14-measure population is ~1.68M evaluations ≈ ~30h single-threaded, order-of-hours with the worker pool (#256). A SQL pushdown turns a population evaluation into a bounded set of DB queries — O(seconds-to-minutes) instead of O(hours) — which is what makes daily full-population recompute at WebChart scale plausible. Complementary to #263 (delta-eval cuts how many subjects you evaluate; pushdown cuts the cost of each).

Trigger conditions (do NOT start the production-facing phases before these)

Recorded when #78 closed; the gates are unchanged:

1. A confirmed WebChart schema / data-shape answer from MIE (the same gate as E12 PR-2c residuals — Send the MIE unblock package (WebChart API contract questions) + record answers #254 A-section answers) — you can't push computation into a database whose shape is unconfirmed.
2. A concrete high-volume measure where FHIR-native evaluation is demonstrably the bottleneck (real volume numbers = Q C16).
3. Incremental/delta batch evaluation (design gated on MIE change-signal answer) #263 delta-eval is insufficient — if status-boundary caching + copy-forward gets daily recompute cheap enough, pushdown may never be needed. Check that math first.

Phases 0–2 below are trigger-exempt — they're pure research + a parity harness over the synthetic Postgres ceiling, safe to do any time as a side project.

The key insight that makes this tractable here

Do not start by transpiling arbitrary CQL/ELM (that's the research tarpit every prior effort dies in). This repo's measures are already structured above CQL: the E11 rule→CQL codegen (ADR-015) means every codegen-backed measure is described by typed rule params in exactly two shapes:

• windowed-recency (days-since-last-event vs a window, + exemption/enrollment conditions, + grace) — audiogram, TB, flu, HEDIS wellness…
• series-completion (N valid doses from a code set, min intervals, multi-alternative OR, titer) — MMR, varicella, Hep B.

Transpile from the rule params, not from CQL text — rule params → parameterized SQL — and use CQL (via fhirNativeExecutor) purely as the golden-parity oracle. That sidesteps CQL's three-valued logic/interval-semantics problem for v1 entirely, because the semantics are defined by the rule shape, and equivalence is proven empirically per measure rather than argued formally.

Phased plan

• Phase 0 — prior-art survey (a weekend). What exists and why none of it fits directly: HL7 SQL-on-FHIR v2 (view definitions — relevant as a target layer), CSIRO Pathling, Google fhir-data-pipes (analytics-on-views), OHDSI/OMOP CQL efforts, MITRE fqm-execution (JS, not SQL). Deliverable: a short memo in docs/superpowers/specs/ — what layer to target (raw wc_miehr_* MariaDB vs a FHIR-shaped view layer vs WorkWell's own Postgres), and the CQL-semantics hazards list (nulls, interval boundaries, timezones, 'most recent' ties).
• Phase 1 — transpile the two rule shapes. generateSql({rule, bindings}) next to the existing generateCql — same input, second backend. Pure, dependency-free string templating (the repo's codec pattern). Target dialect: Postgres first (the ceiling we control), parameterized, one query per (measure, population) returning (subject_id, outcome_status).
• Phase 2 — golden-parity harness. For every codegen-backed measure: evaluate N synthetic subjects via fhirNativeExecutor AND via the SQL, assert byte-identical outcome sets. Reuse the [owner-ops] Roll back fabricated scale seed + N=5000 real-eval proof run + profile #253 batch machinery for scale runs. Parity must hold across the golden regression corpus (spike/synthetic, all scenarios) + a randomized-subject soak. A measure that has never passed parity can never be served by SQL (ADR-025 rule).
• Phase 3 — wire it (trigger-gated). Fill sqlPushdownExecutor: per-measure allowlist (only parity-proven measures), WORKWELL_MEASURE_EXECUTOR=sql-pushdown opt-in unchanged, automatic fallback to FHIR-native for any non-allowlisted measure, and a shadow mode (run both, log divergence, serve the oracle) before ever serving SQL results.
• Phase 4 — stretch, likely never. ELM-subset transpiler for hand-written CQL beyond the two shapes. Explicitly out of scope until Phases 1–3 prove value. The CMS eCQMs (multi-library QICore) stay on the fqm/native path permanently — they are not a pushdown target.

Hard guardrails (non-negotiable, from ADR-008/ADR-025)

• CQL remains the sole compliance authority. The SQL path serves only measures with proven golden parity, and any detected divergence disables that measure's SQL path (fail back to the oracle, loudly).
• Deployed default stays byte-identical: no env opt-in ⇒ fhirNativeExecutor, always.
• No schema changes without owner sign-off; the transpiler itself is pure code.
• Every state change on any eventual write path stays audited (n/a for v1 — evaluation is read-only).

Acceptance criteria (for calling the epic done at Phase 3)

• Phase-0 memo committed (docs/superpowers/specs/)
• generateSql covers both rule shapes incl. multi-alternative series + grace + titer
• Golden parity: 100% outcome-set equality vs the oracle across the full synthetic corpus + a ≥5k randomized soak, per allowlisted measure
• Measured speedup on the N=5000 Neon benchmark (target: ≥50× vs ~68 ms/eval for allowlisted measures)
• Shadow mode + divergence alarm before any serving
• ADR documenting what shipped and what stays FHIR-native forever

Links

• ADR-025 (docs/DECISIONS.md) — the seam + the golden-parity rule
• docs/superpowers/specs/2026-06-30-e9-cql-sql-bridge-decision-memo.md — the original decision memo (hybrid pluggable executors)
• E9 — CQL to SQL bridge (WebChart) — spike / decision memo only #78 (closed) — the decision-phase issue this succeeds
• [owner-ops] Roll back fabricated scale seed + N=5000 real-eval proof run + profile #253 — the ~68 ms/eval profiling baseline · worker_threads pool for seed:scale --mode evaluate (batch CLI only, bounded) #256 worker pool · Incremental/delta batch evaluation (design gated on MIE change-signal answer) #263 delta-eval (complementary)
• backend-ts/src/engine/cql/codegen/ — the rule→CQL codegen this piggybacks on