Da Vinci PAS Metrics with SQL on FHIR in Aidbox

Prior authorization is a persistent friction point in US healthcare, and the pressure to measure it keeps growing: payers want to see where it stalls, and reporting it is on track to become a regulatory requirement. That means measuring submission volume, error rate, time to a decision.

The Da Vinci PAS IG names ten operational metrics and ships a PASMetricData logical model that suggests the data the metrics draw on.

Computing them usually means copying the data into a separate analytics system and rebuilding each metric there, custom work that gets redone at every site and drifts from the source. We wanted the opposite: define each metric once, run it next to the data, and read it with the BI tools you already have. Because the transformation is declarative and stays in the database, nothing is copied out to keep in sync and there is no second analytics stack to build. Aidbox already had a way to do this: built-in SQL on FHIR.

SQL on FHIR in one minute

SQL on FHIR standardizes the flattening, instead of every vendor inventing its own way to query nested resources. Your metrics become portable, standard FHIR artifacts, interoperable analytics, explained in great detail here. The pieces:

1. A ViewDefinition is a FHIR resource that describes how one resource type flattens into a table, its columns, row filters, and array unnesting, expressed as a few functions in a minimal subset of FHIRPath.
2. A SQLView composes and joins those flat views into a reusable, named virtual table that other artifacts read. It is a shareable, versioned FHIR resource that declares its inputs (relatedArtifact: depends-on) and takes no parameters.
3. A SQLQuery is the sibling you run, like a SQLView but it can take parameters, for example a reporting date range.
4. Standard operations ($viewdefinition-run, $sqlquery-run) run them and return rows.

Aidbox implements this against the same Postgres that backs the live FHIR API, so the data and the analytics live in one database.

Five layers, raw FHIR to dashboard

PAS data is awkward to flatten. It is X12-derived, with responses that arrive over several cycles, and metrics that span Claim, ClaimResponse, and the provider and coverage around them. No single view gives you a metric row, so the pipeline is built in layers, each one reading from the one below, and the graph of those layers is the lineage.

Stacking flat views into a shared model, then metrics on top, is the general analytics pattern described in detail in SQL on FHIR: Interoperable Analytics; here we apply it to PAS:

The IG defines ten metrics:

We will go over the first, submission volume, since it is the most straightforward. We trace it layer by layer, starting at Layer 1, the raw resources.

Layer 1: take two preauthorization claims, c1 with two line items and c2 with one. Here is c1 as nested JSON:

{
  "resourceType": "Claim",
  "use": "preauthorization",
  "created": "2026-02-01",
  "provider": { "reference": "Practitioner/p1" },
  "item": [ { "sequence": 1, ... }, { "sequence": 2, ... } ]
}

Layer 2: a ViewDefinition does the flattening. A where keeps the preauthorization claims, and forEach over item turns the array into one row per item. Every column is a FHIRPath expression, getResourceKey() and getReferenceKey() for ids and references, ordinary paths for the rest, even values buried deep in PAS extensions, with no custom parsing:

{
  "resourceType": "ViewDefinition",
  "name": "ClaimView",
  "resource": "Claim",
  "where": [{ "path": "use = 'preauthorization'" }],
  "select": [
    { "column": [
      { "name": "claim",    "path": "getResourceKey()" },
      { "name": "created",  "path": "created" },
      { "name": "provider", "path": "provider.getReferenceKey()" }
    ]},
    { "forEach": "item", "column": [{ "name": "item", "path": "sequence" }] }
  ]
}

Across both claims, that gives one row per item:

Layer 3: a SQLView, ClaimWithProviderView, joins ClaimView with PractitionerView to add the provider NPI:

-- depends on: ClaimView, PractitionerView
select c.*, pr.npi as provider_npi
from "ClaimView" c
left join "PractitionerView" pr on pr.id = c.provider

which gives:

Layer 4: the model.

The response side, ResponseResultView, is built like the claim side: a ViewDefinition flattens each ClaimResponse item and a SQLView exposes its claim, item, and result. PASMetricDataView left-joins each claim item to its response, so each row carries both the request and its outcome, the PASMetricData model realized here as one row per line item:

-- depends on: ClaimWithProviderView, ResponseResultView
select c.claim   as exchange_id,
       c.item    as item_sequence,
       c.created as request_time,
       r.result
from "ClaimWithProviderView" c
left join "ResponseResultView" r
  on r.claim = c.claim and r.item = c.item

which gives the shared model every metric runs against:

Layer 5: submission volume is an aggregate over that model, a SQLQuery. It is an adoption metric, so it counts submissions and items per reporting year, which a dashboard can filter to whichever year it needs:

-- SubmissionVolume, by reporting year
-- depends on: PASMetricDataView
select extract(year from request_time)::int as reporting_year,
       count(distinct exchange_id) as submission_count,
       count(*) as service_item_count
from "PASMetricDataView"
group by reporting_year

As a SQLQuery resource it carries a canonical URL, its depends-on (PASMetricDataView), and the SQL. The parameters slot is empty here, but it is where a filter like a reporting date range might plug in:

Run it, and for our two claims, both from 2026: two distinct exchanges, c1 and c2, so 2 submissions, and three item rows, so 3 service items:

Lineage for free

In most stacks lineage is extra work: a separate tool, or a diagram someone keeps updating by hand. Aidbox derives it. Because each SQLView and SQLQuery names its inputs with relatedArtifact: depends-on, the moment the artifacts load Aidbox knows the dependency graph and draws it, from raw resource type up to the metric.

Every node runs in place, so debugging is concrete: run a single ViewDefinition, SQLView, or SQLQuery on its own, see what it returns, and trace a number on a chart back to the FHIRPath that produced it.

Here is the lineage Aidbox draws for the submission-volume metric. Claim, Practitioner, and ClaimResponse flatten into ViewDefinitions, which feed the ClaimWithProviderView and ResponseResultView SQLViews, then the PASMetricDataView model, then the SubmissionVolume query:

The real PAS graph is larger: the same model (PASMetricDataView) feeds the other nine metrics, each declaring its inputs the same way.

Run it anywhere, serve any BI tool

$sqlquery-run runs a metric on the spot against live data, which is handy while you are building one or answering an ad hoc question.

For dashboards, each metric becomes a Postgres materialized view, precomputed and refreshed in place. Metabase, Tableau, or Power BI connect to a plain table and never see the FHIR underneath. Nothing is exported and there is no warehouse to sync; the analytics sit in the same Postgres as the FHIR data.

What you end up with

The metrics end up as a handful of ordinary FHIR resources, ViewDefinitions, SQLViews, and SQLQueries, not a custom pipeline. That suits PAS, which is still moving: PASMetricData is a reference shape rather than a fixed one, and the metrics around it keep changing. When they do, you edit one artifact and the rest of the graph follows. None of this is specific to PAS either: any IG that defines metrics, or just a logical model, can be built the same way.

Because they are resources, they version, bundle into a package, and install with one upload. In the lineage view you can run, debug, or modify any node ad hoc, and $sqlquery-run executes whatever slice you point it at: a single view, one metric's path back to the source, or the whole model at once.

Want to run PAS metrics on your own data? Try a view in the ViewDefinition builder, or build the full pipeline yourself in Aidbox.

Payers facing CMS-0057-F prior authorization reporting can get Da Vinci PAS support packaged and ready to run with Payerbox, Health Samurai's CMS-0057-F solution.

Talk to our team to get started.

See also:

• SQL on FHIR: Interoperable Analytics, the general pattern behind this pipeline

References

• Da Vinci PAS IG and its metrics page
• PASMetricData logical model
• SQL on FHIR specification and the ViewDefinition resource
• "SQL on FHIR: Interoperable Analytics", Nikolai Ryzhikov
• "SQL on FHIR: tabular views of FHIR data using FHIRPath", npj Digital Medicine
• FHIRPath
• ViewDefinition builder