Prior Authorization Data Modeling: End-to-End Architecture Guide

Introduction

Prior authorization is operationally expensive — it consumes an estimated $3.2 billion in administrative cost annually according to AMA estimates, and generates more provider and member complaints than almost any other payer process. Yet most health plan data architectures treat prior authorization as an operational system concern, not a data modeling problem. The result: PA data is scattered across utilization management platforms, claim systems, and appeals databases, with no unified model that supports longitudinal tracking, CMS compliance, or outcome analytics.

This guide provides a production-grade data model for the full prior authorization lifecycle — from initial request through determination, appeal, and expiration — along with the CMS interoperability context that makes this more than a theoretical exercise.

---

The PA Lifecycle as a Data Model

Prior authorization follows a defined lifecycle with discrete states:

[Submitted] → [In Review] → [Approved] ──────────────────→ [Active] → [Expired]
                          → [Denied]  → [Appeal Submitted] → [Appeal Determination]
                          → [Pended]  → (additional info requested) → [In Review]
                          → [Cancelled]

Each state transition is an event that must be captured with a timestamp, a user/system ID, and the clinical or business reason. The data model must support both point-in-time state queries ("what is the current status of PA #12345?") and longitudinal analysis ("what is the average time from submission to determination for orthopedic surgical PAs?").

---

Core Entities

PA Request (Header)

CREATE TABLE utilization_mgmt.pa_request (
  pa_request_id         VARCHAR(36)   NOT NULL,
  enterprise_member_id  VARCHAR(36)   NOT NULL,
  requesting_provider_npi CHAR(10)    NOT NULL,
  servicing_provider_npi  CHAR(10),
  service_type          VARCHAR(20)   NOT NULL,  -- INPATIENT, OUTPATIENT, DME, RX, PROCEDURE
  primary_cpt_code      VARCHAR(10),
  primary_icd10_dx      VARCHAR(10),
  requested_service_desc VARCHAR(500),
  requested_start_date  DATE,
  requested_end_date    DATE,
  requested_units       INT,
  urgency               VARCHAR(10)   NOT NULL,  -- STANDARD, URGENT, EMERGENT
  submission_channel    VARCHAR(20),             -- PORTAL, FAX, PHONE, API, EDI
  submitted_at          TIMESTAMP     NOT NULL,
  current_status        VARCHAR(20)   NOT NULL,  -- SUBMITTED, IN_REVIEW, APPROVED, DENIED, PENDED, CANCELLED
  current_status_ts     TIMESTAMP     NOT NULL,
  plan_id               VARCHAR(36)   NOT NULL,
  cms_pa_id             VARCHAR(50),             -- CMS-assigned ID for interoperability rule compliance
  PRIMARY KEY (pa_request_id)
);

PA Status Event (State Machine)

CREATE TABLE utilization_mgmt.pa_status_event (
  event_id              BIGINT GENERATED ALWAYS AS IDENTITY,
  pa_request_id         VARCHAR(36)   NOT NULL,
  from_status           VARCHAR(20),
  to_status             VARCHAR(20)   NOT NULL,
  event_ts              TIMESTAMP     NOT NULL,
  user_id               VARCHAR(100),            -- reviewer ID or system ID
  reason_code           VARCHAR(50),             -- denial reason, pend reason
  reason_desc    VARCHAR(500),
  clinical_criteria_met BOOLEAN,
  evidence_basis        VARCHAR(100),            -- MCG, InterQual, custom criteria
  PRIMARY KEY (event_id),
  FOREIGN KEY (pa_request_id) REFERENCES utilization_mgmt.pa_request(pa_request_id)
);

Clinical Criteria Applied

CREATE TABLE utilization_mgmt.pa_clinical_criteria (
  criteria_id           BIGINT GENERATED ALWAYS AS IDENTITY,
  pa_request_id         VARCHAR(36)   NOT NULL,
  criteria_source       VARCHAR(50),             -- MCG, INTERQUAL, CMS_LCD, CUSTOM
  criteria_code         VARCHAR(50),
  criteria_version      VARCHAR(20),
  met_flag              BOOLEAN,
  reviewer_notes        VARCHAR(1000),
  evaluated_at          TIMESTAMP     NOT NULL,
  PRIMARY KEY (criteria_id)
);

PA Authorization (Approved Determinations)

CREATE TABLE utilization_mgmt.pa_authorization (
  auth_id               VARCHAR(36)   NOT NULL,
  pa_request_id         VARCHAR(36)   NOT NULL,
  enterprise_member_id  VARCHAR(36)   NOT NULL,
  authorized_cpt_code   VARCHAR(10),
  authorized_units      INT,
  authorized_start_date DATE          NOT NULL,
  authorized_end_date   DATE          NOT NULL,
  servicing_provider_npi CHAR(10),
  authorization_type    VARCHAR(20),             -- PROSPECTIVE, CONCURRENT, RETROSPECTIVE
  is_active             BOOLEAN       NOT NULL DEFAULT TRUE,
  issued_at             TIMESTAMP     NOT NULL,
  last_modified_at      TIMESTAMP,
  PRIMARY KEY (auth_id),
  FOREIGN KEY (pa_request_id) REFERENCES utilization_mgmt.pa_request(pa_request_id)
);

Appeal

CREATE TABLE utilization_mgmt.pa_appeal (
  appeal_id             VARCHAR(36)   NOT NULL,
  pa_request_id         VARCHAR(36)   NOT NULL,
  appeal_level          INT           NOT NULL,  -- 1=internal, 2=external, 3=state appeal
  appeal_type           VARCHAR(20),             -- MEMBER, PROVIDER, EXPEDITED
  submitted_at          TIMESTAMP     NOT NULL,
  submitted_by          VARCHAR(20),             -- MEMBER, PROVIDER, ATTORNEY
  grounds_desc   VARCHAR(1000),
  determination         VARCHAR(20),             -- UPHELD, OVERTURNED, PARTIAL
  determination_ts      TIMESTAMP,
  irb_reviewer_id       VARCHAR(100),            -- for external reviews
  PRIMARY KEY (appeal_id),
  FOREIGN KEY (pa_request_id) REFERENCES utilization_mgmt.pa_request(pa_request_id)
);

---

CMS Interoperability Rule Alignment

CMS Final Rule 0057-F (effective January 2026 for MA, Medicaid, and QHP plans) requires payers to:

1. Send PA decisions within 72 hours (standard) / 24 hours (urgent)
2. Provide specific denial reasons
3. Implement [FHIR](/terms/FHIR)-based PA APIs (PAS IG)
4. Share PA status with receiving providers via API

Your data model supports compliance tracking for these requirements:

-- CMS compliance monitoring: PA turnaround time by urgency
SELECT
  urgency,
  COUNT(*) AS total_requests,
  AVG(EXTRACT(EPOCH FROM (current_status_ts - submitted_at)) / 3600) AS avg_hours_to_decision,
  SUM(CASE
    WHEN urgency = 'STANDARD' AND EXTRACT(EPOCH FROM (current_status_ts - submitted_at)) / 3600 > 72 THEN 1
    WHEN urgency = 'URGENT'   AND EXTRACT(EPOCH FROM (current_status_ts - submitted_at)) / 3600 > 24 THEN 1
    ELSE 0
  END) AS cms_untimely_count,
  ROUND(100.0 * SUM(CASE
    WHEN urgency = 'STANDARD' AND EXTRACT(EPOCH FROM (current_status_ts - submitted_at)) / 3600 > 72 THEN 1
    WHEN urgency = 'URGENT'   AND EXTRACT(EPOCH FROM (current_status_ts - submitted_at)) / 3600 > 24 THEN 1
    ELSE 0
  END) / COUNT(*), 2) AS untimely_pct
FROM utilization_mgmt.pa_request
WHERE current_status IN ('APPROVED', 'DENIED')
  AND submitted_at >= CURRENT_DATE - 90
GROUP BY urgency;

---

Analytics Use Cases

Denial rate by service type and criteria: Which CPT/service type has the highest denial rate? Which clinical criteria are most often unmet?

Overturn rate: What percentage of denials are overturned on appeal? High overturn rates signal incorrect initial criteria application.

Turnaround time by channel: Are API-submitted PAs decided faster than fax submissions? (Yes, by a wide margin in most plans.)

Authorization utilization: For approved PAs, what percentage of authorized services are actually rendered? Underutilization data informs criteria calibration.

---

Key Takeaways

• Model PA as a state machine — the status event table is as important as the PA request table. Longitudinal state history is required for CMS audits and outcome analytics.
• Capture clinical criteria at the pa_clinical_criteria level, not as a free-text note. Structured criteria data enables overturn analysis and criteria calibration.
• The CMS 0057-F turnaround time requirements (72 hours standard, 24 hours urgent) require a real-time monitoring pipeline on the pa_status_event table.
• Authorization dates and units are separate from request dates and units — model them as distinct entities. A PA can be approved for fewer units or a narrower date range than requested.
• Use the DDL Converter to generate platform-specific DDL for this model in Snowflake, PostgreSQL, or BigQuery syntax.