Code Spine

The code spine defines every billing code that Clear Rates prices. It is a UNION ALL of 11 distinct sources — covering CPT, HCPCS, MS-DRG, APR-DRG, Revenue Code, NDC, and others — with metadata columns that control how codes flow through the rest of the pipeline (outlier bounds, drug/lab/surgery flags, provider type restrictions).

3-Layer Structure

Layer 1 — Prep CTEs: Each of the 11 sources is read and shaped into a common schema in a dedicated CTE. Source-specific filtering, renaming, and flag assignment happen here.

Layer 2 — all_codes UNION ALL: All 11 prep CTEs are unioned into a single all_codes relation. Every row has the same columns at this point.

Layer 3 — Final SELECT with metadata joins: all_codes is joined against external metadata tables (MS-DRG weights, MPFS schedules, APC groupings) to attach benchmark data and finalize code flags. The is_surg_code flag is resolved here from 3 independent sources.

Code Spine — 3-layer structure

-- code.sql (simplified)

-- Layer 1: Prep CTEs (one per source)
WITH cpt_codes AS (
  SELECT
      code,
      'CPT' AS bill_type,
      code_description,
      is_drug_code,
      is_lab_code,
      facility,
      NULL AS apr_drg_soi
  FROM tq_production.spines.cpt_reference
),

ms_drg_codes AS (
  SELECT
      code,
      'MS-DRG' AS bill_type,
      description AS code_description,
      FALSE AS is_drug_code,
      FALSE AS is_lab_code,
      TRUE AS facility,
      NULL AS apr_drg_soi
  FROM tq_production.spines.ms_drg_reference
  -- Exclude transplants (001-017, 019) and CAR-T/ECMO (650-652)
  WHERE code NOT IN ('001','002','003','004','005','006','007','008',
                     '009','010','011','012','013','014','015','016',
                     '017','019','650','651','652')
),

-- ... 9 more source CTEs

-- Layer 2: UNION ALL
all_codes AS (
  SELECT * FROM cpt_codes
  UNION ALL
  SELECT * FROM ms_drg_codes
  -- UNION ALL ... 9 more
),

-- Layer 3: Final SELECT with metadata joins
SELECT
  ac.code,
  ac.bill_type,
  ac.code_description,
  ac.facility,
  ac.apr_drg_soi,
  ac.is_drug_code,
  ac.is_lab_code,
  -- is_surg_code resolved from 3 sources:
  CASE
      WHEN surg_opg.code IS NOT NULL THEN TRUE   -- OPG surgical schedule
      WHEN LEFT(ac.code, 1) BETWEEN '1' AND '6'
           AND ac.bill_type = 'HCPCS' THEN TRUE  -- HCPCS prefix rule
      WHEN ac.bill_type = 'MS-DRG'
           AND ms_drg.drg_type = 'SURG' THEN TRUE
      ELSE FALSE
  END AS is_surg_code,
  COALESCE(dev.is_device_dependent, FALSE) AS is_device_dependent
FROM all_codes ac
LEFT JOIN surg_opg_codes surg_opg ON ac.code = surg_opg.code
LEFT JOIN ms_drg_reference ms_drg ON ac.code = ms_drg.code
LEFT JOIN device_dependent_codes dev ON ac.code = dev.code

The facility Flag and PG Code Duplication

Physician Group (PG) codes appear twice in the code spine — once with facility = True and once with facility = False. This is intentional: a physician billing the same CPT code may do so in a facility setting (hospital outpatient) or a non-facility setting (office), and payers negotiate different rates for each context.

CPT 99214 — office visit, appears twice

Row 1: code = '99214', bill_type = 'CPT', facility = True — priced as a facility-based physician service. MPFS benchmark uses facility RVU.

Row 2: code = '99214', bill_type = 'CPT', facility = False — priced as a non-facility (office) physician service. MPFS benchmark uses non-facility RVU.

ROID fan-out: Each row generates separate ROIDs in the Rate Object Space — a given PG provider × payer × network combination produces two ROIDs for 99214, one per facility flag.

The facility flag doubles the PG ROID count for shared codes. Rate selection picks the canonical rate independently for each row.

is_surg_code — 3 Sources

The is_surg_code flag drives outlier bound selection (surgical codes get wider bounds). It is set by any one of three independent sources:

1. OPG schedules: CPT codes appearing in UHC, Aetna, Cigna, or BCBS CA outpatient surgical schedules are flagged. These are authoritative for common surgical procedures.
2. HCPCS prefix rule: HCPCS codes with a leading digit 1–6 are flagged as surgical by convention (Category I procedure codes).
3. MS-DRG type: MS-DRGs with drg_type = 'SURG' in the MS-DRG reference table are flagged.

APR-DRG SOI Suffix Pattern

APR-DRGs carry a severity-of-illness (SOI) dimension (1–4). Each base APR-DRG code is expanded into 4 rows with suffixed SOI values.

APR-DRG 302 — knee replacement, 4 SOI levels

Source row: APR-DRG 302 (knee replacement) enters the code spine as a single row from the APR-DRG reference table.

SOI expansion: The prep CTE generates 4 rows: 302-1 (minor), 302-2 (moderate), 302-3 (major), 302-4 (extreme).

ROS impact: Each of the 4 suffixed codes generates its own set of ROIDs. Payers that negotiate APR-DRG rates typically do so at the base code level — the suffix is used to scope the rate to the appropriate SOI tier.

APR-DRG codes quadruple into 4 rows (one per SOI). The suffix format is always {base_code}-{soi} with a hyphen.

11 Sources

#	Source	Bill Type	Provider Types	Managed By
1	CPT reference	CPT	Hospital, ASC, PG, Imaging, UrgentCare	Hardcoded in code.sql
2	HCPCS reference	HCPCS	Hospital, ASC, PG, DME, Lab	Hardcoded in code.sql
3	MS-DRG reference	MS-DRG	Hospital	cld-utils DAG (date-stamped)
4	APR-DRG reference	APR-DRG	Hospital	cld-utils DAG (date-stamped)
5	Revenue codes	RC	Hospital	Hardcoded in code.sql
6	NDC (drug codes)	NDC	Hospital, PG	Hardcoded in code.sql
7	APC codes	APC	Hospital, ASC	Hardcoded in code.sql
8	MPFS fee schedule	CPT (PG-specific)	PhysicianGroup	cld-utils DAG (date-stamped)
9	ASC fee schedule	CPT (ASC-specific)	ASC	cld-utils DAG (date-stamped)
10	Dialysis codes	HCPCS subset	Dialysis	Hardcoded in code.sql
11	DME fee schedule	HCPCS subset	DME	cld-utils DAG (date-stamped)

MS-DRG Exclusions

Certain MS-DRGs are excluded from the code spine entirely:

• Transplants (001–017, 019): Excluded because transplant pricing is highly individualized, negotiated outside standard MRF frameworks, and would inflate outlier counts.
• CAR-T/ECMO (650–652): Excluded due to extreme cost variability and rare utilization making benchmark comparisons unreliable.
• CAR-T 018: Included but restricted to designated CAR-T treatment centers only. Not a blanket exclusion.

Code Flags

Flag	How Set	Pipeline Impact
is_drug_code	Set in prep CTEs for NDC codes and drug HCPCS J-codes	Triggers drug-specific accuracy bounds (0.8–10× Medicare for payer); CDF scoring disabled
is_lab_code	Set for lab HCPCS codes (P-codes, certain Q-codes)	Routes to lab-specific BRIT accuracy task with tight bounds (0.2–4.5×)
is_surg_code	Resolved from OPG schedules, HCPCS prefix rule, or MS-DRG type	Widens outlier bounds for surgical codes
is_device_dependent	Joined from a curated device-dependent code list	Flags codes where implant cost is bundled; affects imputation logic

cld-utils vs sub-DAG

Sources 3, 4, 8, 9, and 11 are date-stamped tables built by the cld-utils DAG (a separate pipeline that updates quarterly or as fee schedules are released). After a cld-utils rebuild, update the pointer in params.py to reference the new date-stamped table name. The code spine SQL itself does not need to change — only the param.