/add-codes

Add billing codes to the CLD codeset SQL files from a spec xlsx. Routes each row to the correct SQL file based on bill_type and provider_type, inserting new codes into the manual additions array.

Usage

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

Provide the path to an Excel file containing the codes to add. The skill reads the sheet, identifies columns automatically, and routes each row to the correct SQL file.

Spec File Format

The xlsx should have a single sheet with at least these columns:

Column	Notes
billing_code	Numeric string (e.g. 29826)
billing_code_type	e.g. HCPCS
bill_type	e.g. Outpatient, Inpatient
provider_type	Hospital, Laboratory, or ASC

Example rows:

billing_code	billing_code_type	bill_type	provider_type
29827	HCPCS	Outpatient	Hospital
43240	HCPCS	Outpatient	Hospital
93225	HCPCS	Outpatient	Laboratory
29827	HCPCS	Outpatient	ASC

What It Does

1. Parses the spec — reads the xlsx and identifies the relevant columns.

2. Routes rows to SQL files — each row is mapped to a target file based on provider_type and bill_type:

   provider_type	bill_type	Target file
   Hospital	Outpatient	outpatient_codeset.sql
   Laboratory	(any)	hospital_lab_codes.sql
   ASC	(any)	asc_codeset.sql

   Rows that don't match any rule are skipped and reported as Unrouted.

3. Updates each SQL file — finds the manual additions array (identified by comment markers like -- manual HCPCS additions or -- Non-CLFS codes requested), checks for duplicates, and appends new codes.

4. Prints a summary grouped by file: codes added, codes skipped as duplicates, and unrouted rows.

Files Modified

All paths relative to dags/data_science/cld/core_licensable_data_utils/sql/spines/:

• outpatient_codeset.sql — Hospital outpatient codes appended to -- manual HCPCS additions array
• hospital_lab_codes.sql — Laboratory codes appended to -- Non-CLFS codes requested array
• asc_codeset.sql — ASC codes appended to -- manual HCPCS additions array

Reference

Add Codes: Reference

File Paths

All SQL files are in dags/data_science/cld/core_licensable_data_utils/sql/spines/:

File	Provider Type	Bill Type
outpatient_codeset.sql	Hospital	Outpatient
hospital_lab_codes.sql	Laboratory	any
asc_codeset.sql	ASC	any

---

Routing

provider_type	bill_type	Target file
Hospital	Outpatient	outpatient_codeset.sql
Laboratory	(any)	hospital_lab_codes.sql
ASC	(any)	asc_codeset.sql

Any row that does not match a rule above is skipped and reported as Unrouted.

---

SQL Array Format

Each file has a dedicated comment marker followed by a UNION ALL block containing an unnest/UNNEST array of quoted billing codes. New codes are appended inside the array.

outpatient_codeset.sql — -- manual HCPCS additions

-- manual HCPCS additions
SELECT
    'Outpatient' as bill_type,
    'HCPCS' as billing_code_type,
    billing_code,
    NULL as revenue,
    CAST(NULL AS Boolean) as facility
FROM (
    SELECT billing_code
    FROM unnest(array[
        '29826', '22558', '43775', '43644', '27487', '43845', '52597',
        '<new_code>'
    ]) t(billing_code)
) AS manual_codes

hospital_lab_codes.sql — -- Non-CLFS codes requested

-- Non-CLFS codes requested
SELECT billing_code
FROM UNNEST(ARRAY[
    '93225', '93226', '93229',
    '93242', '93243', '93246', '93247',
    '93270', '93271',
    '<new_code>'
]) t(billing_code)

asc_codeset.sql — -- manual HCPCS additions

-- manual HCPCS additions
SELECT
    'Outpatient' as bill_type,
    'HCPCS' as billing_code_type,
    billing_code,
    NULL as revenue,
    CAST(NULL AS Boolean) as facility
FROM (
    SELECT billing_code
    FROM unnest(array['29826', '22558', '43775', '43644', '27487', '43845', '93655', '93656', '93657',
        '<new_code>'
    ]) t(billing_code)
) AS manual_codes

---

Duplicate Check

A code is considered a duplicate if its exact string value already appears anywhere inside the target array literal. String comparison is case-sensitive; billing codes are numeric strings (e.g. '29826').