/add-network

Add new payer networks to the CLD sub-DAG from a spec xlsx file. Updates payer.sql, network_mappings.py, and the three plan bridge variables files.

Usage

/add-network <path-to-spec.xlsx>

Provide the path to a spec Excel file containing a "Payer Networks" sheet. The skill reads the sheet, identifies the relevant columns automatically, and applies all changes to the sub-DAG source files.

Spec File Format

The spec is an Excel workbook (.xlsx) with a sheet named "Payer Networks". Each row is one network. The sheet should have these columns:

Column	Required	Notes
Payer ID	Yes	Integer ID for the payer (e.g. 388)
Network Type	Yes	e.g. PPO, HMO, Exchange, Medicare Advantage
Product Network Label	Yes	One or more labels as they appear verbatim in the MRF data — ALL CAPS. Use a comma-separated list if the network maps to multiple labels (e.g. CAPITAL HEALTH PPO, CAPITAL HEALTH PREFERRED). Leave blank if unknown.
Display Name	Yes	The human-readable network name shown in the product (e.g. Capital Health PPO)
States	No	Comma-separated 2-letter state codes (e.g. NJ, PA). Write NATIONAL for nationwide. Defaults to FL if left blank.
Hospital Plan Keywords	No	Used to generate keyword filters for plan matching. Leave blank if not applicable.

Example rows:

Payer ID	Network Type	Product Network Label	Display Name	States	Hospital Plan Keywords
999	PPO	CAPITAL HEALTH PPO, CAPITAL HEALTH PREFERRED	Capital Health PPO	NJ, PA	Capital Health Commercial
999	HMO	CAPITAL HEALTH HMO	Capital Health HMO	NJ	Capital Health HMO Select
388	Exchange	GARDEN STATE EXCHANGE	Garden State Exchange	NJ

A payer can appear on multiple rows if it has multiple network types. The skill handles each row independently and deduplicates automatically.

What It Does

The skill reads the "Payer Networks" sheet from the provided Excel file and applies four categories of changes:

1. Adds new payer IDs to payer.sql — any payer IDs not already in the allowlist are appended as quoted strings.
2. Adds network mapping tuples to network_mappings.py — each row in the sheet is routed to the correct *_NETWORK_MAPPINGS list based on its Network Type (PPO/EPO/POS, HMO, Exchange, Narrow, or Medicare Advantage). Duplicate (payer_id, display_name) pairs in the same list are skipped; a payer with a different display name gets a new entry.
3. Adds plan bridge keyword entries — for each new payer ID, keywords derived from the Hospital Plan Keywords column are added to variables_ppo.sql, variables_hmo.sql, and/or variables_exchange.sql based on which network types the payer has. Payers already present in the dict are skipped.
4. Prints a summary of all changes grouped by file.

If states are not specified in the spec, ['FL'] is used as the default.

Files Modified

• sql/reference/spines/payer.sql — new payer IDs appended to the payer_ids list
• utils/network_mappings.py — new tuples added to the appropriate *_NETWORK_MAPPINGS list
• sql/raw/plan_bridge/variables_ppo.sql — new payer keyword entries (PPO/EPO/POS payers only)
• sql/raw/plan_bridge/variables_hmo.sql — new payer keyword entries (HMO payers only)
• sql/raw/plan_bridge/variables_exchange.sql — new payer keyword entries (Exchange payers only)

Reference

Add Network: Reference

File Paths

All paths relative to dags/data_science/cld/core_licensable_data_sub_dag/:

File	Purpose
sql/reference/spines/payer.sql	Payer ID allowlist
utils/network_mappings.py	Network mapping tuples
sql/raw/plan_bridge/variables_ppo.sql	PPO plan bridge keyword matching
sql/raw/plan_bridge/variables_hmo.sql	HMO plan bridge keyword matching
sql/raw/plan_bridge/variables_exchange.sql	Exchange plan bridge keyword matching

---

payer.sql — Payer ID List

Structure:

{% set payer_ids = [
    '169',
    '42',
    ...
] %}

• IDs are strings (quoted).
• Append new IDs at the end of the list.

---

network_mappings.py — Mapping Tuple Format

('payer_id_str', ['PRODUCT NETWORK LABEL'], 'Display Name', ['STATE_CODES'])

Field	Type	Notes
payer_id_str	str	Always a string, e.g. '388'
['PRODUCT NETWORK LABEL']	list[str]	Raw MRF label(s), ALL CAPS as they appear in the data. Empty list [] is valid.
'Display Name'	str	CLD-facing network name shown in the product
['STATE_CODES']	list[str]	2-letter state codes or ['NATIONAL']. Default ['FL'] if unspecified.

Network Type Routing

Network Type in Spec	Target List
PPO, EPO, POS, Open Access	PPO_NETWORK_MAPPINGS
HMO, HMO/POS	HMO_NETWORK_MAPPINGS
Exchange, Marketplace, ACA	EXCHANGE_NETWORK_MAPPINGS
Narrow, Tiered, Local	NARROW_NETWORK_MAPPINGS
Medicare Advantage, MA	MEDICARE_ADVANTAGE_NETWORK_MAPPINGS

Duplicate Rules

• Same payer_id + same display_name in the same list → skip, already exists.
• Same payer_id + different display_name → add new entry, it's a distinct network.
• Same payer_id in different lists → allowed (a payer can have both PPO and HMO networks).

---

Plan Bridge Variables — Keyword Dicts

Structure (same pattern in all three files)

{% set ppo_payer_keywords = {
    7: ['COMM', 'AETNA'],
    76: ['OAP', 'OPENACCESSPLUS'],
    ...
    <new_payer_id_int>: ['KEYWORD1', 'KEYWORD2'],
} %}

• Key is an integer (no quotes).
• Value is a list of keyword strings (uppercase, no spaces).
• An empty list [] is valid — means "include this payer with no additional keyword filter."

Keyword Derivation

Keywords come from the Hospital Plan Keywords column in the spec sheet:

1. Take each distinct keyword for the payer.
2. Convert to UPPERCASE.
3. Remove all spaces.
4. Use the result as one keyword string.

Example:

• "Open Access Plus" → 'OPENACCESSPLUS'
• "Blue Choice PPO" → 'BLUECHOICEPPO'
• "National OAP" → 'NATIONALOAP'

If Hospital Plan Keywords is blank or absent, use [].

Which File Gets the Entry

A payer is added to a variables file if it has at least one network of that type in the spec:

Payer has...	Add to
Any PPO / EPO / POS network	variables_ppo.sql → ppo_payer_keywords
Any HMO network	variables_hmo.sql → hmo_payer_keywords
Any Exchange network	variables_exchange.sql → exchange_payer_keywords

A payer can appear in multiple files if it has multiple network types.

Skip the entry if the payer ID is already present as a key in the dict.

---

Example: Adding a Single New Payer

Spec row:

Payer ID: 999
Network Type: PPO
Product Network Label: CAPITAL HEALTH PPO
Display Name: Capital Health PPO
States: NJ, PA
Hospital Plan Keywords: Capital Health Commercial

Changes:

payer.sql — add '999' to the payer_ids list.

network_mappings.py — add to PPO_NETWORK_MAPPINGS:

('999', ['CAPITAL HEALTH PPO'], 'Capital Health PPO', ['NJ', 'PA']),

variables_ppo.sql — add to ppo_payer_keywords:

999: ['CAPITALHEALTHCOMMERCIAL'],