SQL on FHIR data

For joins, aggregates, and other complex SQL manipulation, drop to SQL. This page covers the materialized view Payerbox uses for prior-auth analytics, the raw EOB table underneath it, and how to expose SQL through the FHIR API.

The materialized view

sof.pdex_prior_authorization_item is materialized from the ViewDefinition of the same name. Same columns, just persisted as a Postgres relation:

SELECT eob_id, business_identifier, status, outcome, created, disposition
FROM   sof.pdex_prior_authorization_item
WHERE  status = 'active' AND outcome = 'complete'
LIMIT  10;

Reads are plain column access - the expensive JSONB extraction ran once at refresh time.

Refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY sof.pdex_prior_authorization_item;

CONCURRENTLY keeps the view readable while it refreshes (requires a unique index on the view). Cadence is whatever your dashboard tolerates. For ~30M EOBs: ~20 min from-scratch build, a few minutes for refresh.

The raw EOB table

Every FHIR resource lives in a lowercase Postgres table - ExplanationOfBenefit is at public.explanationofbenefit. The whole FHIR payload sits in the resource jsonb column, with id, cts, ts, and a few other columns alongside. The full layout is in Database schema in the Aidbox docs.

For analytics, prefer the materialized view; reach for the raw table when you need fields the materialized view doesn't expose.

SQL through the FHIR API

AidboxQuery packages SQL as a named REST endpoint with typed parameters, defaults, and an optional total-count query. Define once with PUT - the example below exposes denial rate by month as a callable endpoint, parameterized by start date:

PUT /AidboxQuery/denial-rate-since
Content-Type: application/json

{
  "params": {"since": {"isRequired": true}},
  "query": "SELECT date_trunc('month', created::date)::date AS month, round(100.0 * count(*) FILTER (WHERE outcome = 'error') / nullif(count(*), 0), 1) AS denial_pct FROM sof.pdex_prior_authorization_item WHERE created::date >= {{params.since}}::date GROUP BY 1 ORDER BY 1 DESC",
  "count-query": "SELECT count(*) FROM sof.pdex_prior_authorization_item WHERE created::date >= {{params.since}}::date"
}

Call it as GET or, for long parameter lists, POST:

GET  /$query/denial-rate-since?since=2025-05-01
POST /$query/denial-rate-since
{"params": {"since": "2025-05-01"}}

Full parameter spec, format strings, and access control in AidboxQuery in the Aidbox docs.

Connecting BI tools

BI tools (Tableau, Looker, Metabase, Power BI) connect to sof.pdex_prior_authorization_item over Postgres. Freshness follows your REFRESH MATERIALIZED VIEW schedule.