Network Spine

The network spine defines every payer network that Clear Rates prices. Networks are built across 7 SQL files — one CREATE and five INSERTs by network type, plus a deduplication pass. Each network gets a stable network_id derived from a hash of (payer_id, network_name), making IDs deterministic and reproducible without a sequence.

The network_row Macro

All 7 SQL files use a shared network_row Jinja2 macro that computes the key network columns consistently:

• network_id — computed via xxhash64(CAST(payer_id || network_name AS varbinary)). Stable as long as payer_id and network_name don't change.
• network_state — ARRAY of states where this network is active; [NULL] for national networks.
• payer_data_network — ARRAY of plan name strings used to match hospital MRF plan_name values during Raw Data ingestion.
• network_class — broad classification: 'Commercial', 'Exchange', 'Medicare Advantage'.
• network_type — more specific type: 'PPO', 'HMO', 'NARROW', 'EXCHANGE', 'Medicare Advantage'.

network_row macro — key columns

-- Jinja2 macro (simplified)
{% macro network_row(payer_id, network_name, payer_data_network, states, network_class, network_type) %}
  from_big_endian_64(xxhash64(CAST({{ payer_id }} || {{ network_name }} AS varbinary)))
      AS network_id,
  {{ payer_id }}                              AS payer_id,
  {{ network_name }}                          AS network_name,
  ARRAY{{ payer_data_network }}               AS payer_data_network,   -- matched against hospital MRF plan_name
  ARRAY{{ states }}                           AS network_state,         -- [NULL] = national
  '{{ network_class }}'                       AS network_class,
  '{{ network_type }}'                        AS network_type
{% endmacro %}

Walkthrough: UHC Choice Plus PPO

UHC Choice Plus PPO — national PPO network

payer_id: '643' (UHC's Clear Rates payer ID)

network_name: 'UHC Choice Plus PPO'

payer_data_network: ['CHOICE PLUS', 'CHOICE PLUS NATIONAL'] — the exact normalized strings matched against hospital MRF plan_name during Raw Data ingestion

network_state: [NULL] — national network; not restricted to specific states

network_class / network_type: 'Commercial' / 'PPO'

network_id = from_big_endian_64(xxhash64(CAST('643' || 'UHC Choice Plus PPO' AS varbinary))). Stable across runs as long as payer_id and network_name are unchanged.

Network Types (7 SQL Files)

File	network_class / network_type	SQL Operation	Notes
network_base.sql	Commercial / PPO	CREATE TABLE	First file; establishes the schema
network_hmo.sql	Commercial / HMO	INSERT	Standard HMO networks
network_narrow.sql	Commercial / NARROW	INSERT	Narrow/tiered PPO variants
network_exchange.sql	Exchange / EXCHANGE	INSERT	ACA marketplace networks
network_custom.sql	Commercial / PPO	INSERT	Manual one-off networks not covered by standard types
network_medicare_advantage.sql	Medicare Advantage / Medicare Advantage	INSERT	Medicare Advantage plans
network_dedup.sql	(all)	Dedup pass	Removes duplicate rows introduced by overlapping keyword matches

Where to Make Changes

There are exactly two places to update when adding or modifying a network:

1. network_mappings.py — the source of truth. Defines payer_id → {network_name, payer_data_network, states, class, type} mappings. This file is imported by the SQL generation scripts. Edit here first.

2. variables_ppo.sql (and equivalent per type) — contains ppo_payer_keywords (and type-equivalent vars). Keywords must be written in ALLCAPS with no spaces, matching the normalized form of plan_name used in hospital MRF matching. For example: 'CHOICEPLUS' not 'Choice Plus'.

Computing network_id offline

You can compute network_id offline for any (payer_id, network_name) pair:

SELECT from_big_endian_64(xxhash64(CAST('643' || 'UHC Choice Plus PPO' AS varbinary)))

Useful for debugging rate assignment gaps without running the full pipeline.

payer_data_network and Raw Data Matching

During Raw Data ingestion, hospital MRF rates are matched to network spine rows by comparing hospital_rates.plan_name against payer_data_network. The match is an exact equality check against the normalized plan name — not a fuzzy keyword search.

The normalized form is ALLCAPS with spaces and special characters stripped. The payer_data_network strings in the spine must match this normalized form exactly for rates to be assigned to the correct network.

Renaming a network changes its network_id

Stability cuts both ways. The network_id hash is derived from network_name. Renaming a network changes its network_id — all historical rates keyed by the old network_id become orphaned. Treat network_name as immutable once a network is in production.