ecw-charge-validation/docs/superpowers/plans/2026-09-15-payer-group-categorization.md

Payer Group Categorization Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: After the bot exports the Medcor Ready claim report, automatically add a Group Name column (Commercial / Medicaid / Medicare / Dentaquest / Program / …) based on each row's PAYER, writing the result to a new file so the existing pipeline's file layout is untouched.

Architecture: A new core/payer_groups.py does an exact-string lookup against a hand-editable payer_groups.json. A new core/spreadsheet.write_xlsx() (stdlib only, mirroring the existing reader) writes the categorized rows to Output\Medcor Ready_<date>.xlsx. flows/export_medcor_ready.py wires the two together as a new, non-fatal step right after the existing claim-id read-back.

Tech Stack: Python 3.13, stdlib only (zipfile, xml.etree, json, xml.sax.saxutils) — no new third-party dependencies, matching the project's existing "no openpyxl" constraint.

Spec: docs/superpowers/specs/2026-09-15-payer-group-categorization-design.md

Global Constraints

• No new third-party dependencies (project has none beyond playwright; spreadsheet.py's whole existence is to avoid openpyxl).
• Tests are hand-rolled case_* functions collected via globals() and run by a main() — no pytest, matching every existing file under tests\.
• Input\Claim Report_<date>.xlsx must never be modified by this feature — claim_file.py and the charge-entry phase depend on its exact column layout.
• Payer matching is exact string, case-sensitive, no fuzzy/substring logic — an unmapped payer must be flagged, never guessed (see spec's "Unknown payer handling").
• Every new step must be non-fatal to the run — matches the existing philosophy in flows/export_medcor_ready.py (_read_back_claim_ids, _read_claim_form).

---

Task 1: Initialize git

This project currently has no git history at all (verified: git status outside a repo). The plan's commit-per-task workflow needs a repo to commit into, so this task creates one and takes a single baseline commit of the existing code before any feature work starts. This is additive only — no existing file is changed.

Files:

• Create: D:\Ecw Process\.git\ (via git init)

Interfaces: none — infrastructure only.

• Step 1: Confirm there is no existing git repo

Run: git -C "D:\Ecw Process" status Expected: fatal: not a git repository (or any of the parent directories): .git

• Step 2: Initialize the repository

Run: git -C "D:\Ecw Process" init Expected: Initialized empty Git repository in D:/Ecw Process/.git/

• Step 3: Check .gitignore already covers the right things

Run: cat "D:\Ecw Process\.gitignore" Expected: it already excludes .env, Logs\, Screenshots\, Output\, Input\ (per README.md's Layout section, all four are described as gitignored). If any of those four is missing, add it before continuing — this baseline commit must not include patient data or credentials.

• Step 4: Stage and commit the current codebase as a baseline

cd "D:\Ecw Process"
git add -A
git status

Read the git status output before committing: confirm nothing under Input\, Output\, Logs\, Screenshots\, or .env is staged. If any of those appear, fix .gitignore and re-run git add -A.

git commit -m "chore: initialize git repository (baseline)"

---

Task 2: write_xlsx() in core/spreadsheet.py

Files:

• Modify: src/ecw_bot/core/spreadsheet.py
• Test: tests/test_spreadsheet_write.py (new)

Interfaces:

• Produces: spreadsheet.write_xlsx(path, rows: list[list]) -> None — rows is a header row plus data rows, each a list of values (any type, written as str()). Raises on I/O failure. Creates parent directories if needed.

• Consumes: nothing new — sits next to the existing read_rows(path) -> list.

• Step 1: Write the failing round-trip test

Create tests/test_spreadsheet_write.py:

"""
write_xlsx(): the write side of core/spreadsheet.py.

    python tests\\test_spreadsheet_write.py

Round-trips through the existing `read_rows()` reader rather than asserting
on raw XML — what matters is that a file this writes is a file this project's
own reader (and real Excel) can open, not the exact bytes.
"""

import sys
import pathlib
import tempfile

sys.path.insert(0, str(pathlib.Path(__file__).resolve().parent.parent / "src"))

from ecw_bot.core import spreadsheet                 # noqa: E402

TMP = pathlib.Path(tempfile.mkdtemp())


def case_write_then_read_round_trips():
    rows = [
        ["CLAIMS#", "PAYER", "Group Name"],
        ["9107000", "Medicaid", "Medicaid"],
        ["9106000", "DentaQuest", "Dentaquest"],
    ]
    path = TMP / "roundtrip.xlsx"
    spreadsheet.write_xlsx(path, rows)
    back = spreadsheet.read_rows(path)
    assert back == rows, back


def case_special_characters_are_escaped():
    rows = [["PATIENT", "NOTE"],
            ["Smith & Sons", "A < B > C"],
            ["O'Brien", '"quoted"']]
    path = TMP / "escaped.xlsx"
    spreadsheet.write_xlsx(path, rows)
    back = spreadsheet.read_rows(path)
    assert back == rows, back


def case_wide_row_uses_correct_column_letters():
    """27 columns exercises the single-letter -> AA rollover."""
    header = [f"col{i}" for i in range(27)]
    data = [str(i) for i in range(27)]
    path = TMP / "wide.xlsx"
    spreadsheet.write_xlsx(path, [header, data])
    back = spreadsheet.read_rows(path)
    assert back[1][26] == "26", back[1]        # column AA (index 26)
    assert back[1] == data, back[1]


def case_creates_parent_directory():
    path = TMP / "nested" / "new_dir" / "file.xlsx"
    assert not path.parent.exists()
    spreadsheet.write_xlsx(path, [["A"], ["1"]])
    assert path.exists()


def case_non_string_values_are_stringified():
    rows = [["N"], [5], [3.5]]
    path = TMP / "numbers.xlsx"
    spreadsheet.write_xlsx(path, rows)
    back = spreadsheet.read_rows(path)
    assert back == [["N"], ["5"], ["3.5"]], back


CASES = [v for k, v in sorted(globals().items()) if k.startswith("case_")]


def main() -> int:
    failures = 0
    for fn in CASES:
        name = fn.__name__[5:].replace("_", " ")
        try:
            fn()
            print(f"  ok    {name}")
        except AssertionError as exc:
            print(f"  FAIL  {name}: {exc}")
            failures += 1
        except Exception as exc:
            print(f"  ERROR {name}: {type(exc).__name__}: {exc}")
            failures += 1
    print(f"\n{len(CASES) - failures}/{len(CASES)} passed")
    return 1 if failures else 0


if __name__ == "__main__":
    sys.exit(main())

• Step 2: Run it to verify it fails

Run: python tests\test_spreadsheet_write.py Expected: ERROR on every case with AttributeError: module 'ecw_bot.core.spreadsheet' has no attribute 'write_xlsx'

• Step 3: Implement write_xlsx() in core/spreadsheet.py

Add these imports at the top of src/ecw_bot/core/spreadsheet.py (alongside the existing import csv / re / zipfile block):

from xml.sax.saxutils import escape

Append this to the end of src/ecw_bot/core/spreadsheet.py:

# =============================================================================
# Writing (the categorization step's output)
# =============================================================================
# The read side above has no dependency on openpyxl; the write side keeps that
# constraint too. What follows is the minimal set of OOXML parts real Excel
# actually requires to open a file without a "repair" prompt — more than the
# reader above needs (it only ever looks at xl/worksheets/sheet*.xml), because
# this file is meant to be opened by a person, not just this codebase.

_CONTENT_TYPES = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
    '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
    '<Default Extension="xml" ContentType="application/xml"/>'
    '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
    '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
    '</Types>'
)

_ROOT_RELS = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
    '<Relationship Id="rId1" '
    'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" '
    'Target="xl/workbook.xml"/>'
    '</Relationships>'
)

_WORKBOOK = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
    'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
    '<sheets><sheet name="Sheet1" sheetId="1" r:id="rId1"/></sheets>'
    '</workbook>'
)

_WORKBOOK_RELS = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
    '<Relationship Id="rId1" '
    'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" '
    'Target="worksheets/sheet1.xml"/>'
    '</Relationships>'
)


def _col_letter(idx: int) -> str:
    """0 -> 'A', 25 -> 'Z', 26 -> 'AA'. Inverse of `_col_index` above."""
    letters = ""
    idx += 1
    while idx > 0:
        idx, rem = divmod(idx - 1, 26)
        letters = chr(65 + rem) + letters
    return letters


def write_xlsx(path, rows: list) -> None:
    """
    Write `rows` (a header row plus data rows, each a list of values) as a
    minimal single-sheet xlsx.

    Inline `t="str"` cells, no sharedStrings — the same shape `_read_xlsx`
    above already parses, and what eCW's own export already sends (see the
    module docstring). Every value is written through `str()`, so callers can
    pass numbers or None without pre-formatting.
    """
    path = Path(path)
    body = []
    for r, row in enumerate(rows, start=1):
        cells = []
        for c, value in enumerate(row):
            text = escape(str(value))
            cells.append(f'<c r="{_col_letter(c)}{r}" t="str"><v>{text}</v></c>')
        body.append(f'<row r="{r}">{"".join(cells)}</row>')
    sheet = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        f'<sheetData>{"".join(body)}</sheetData></worksheet>'
    )

    path.parent.mkdir(parents=True, exist_ok=True)
    with zipfile.ZipFile(path, "w", zipfile.ZIP_DEFLATED) as zf:
        zf.writestr("[Content_Types].xml", _CONTENT_TYPES)
        zf.writestr("_rels/.rels", _ROOT_RELS)
        zf.writestr("xl/workbook.xml", _WORKBOOK)
        zf.writestr("xl/_rels/workbook.xml.rels", _WORKBOOK_RELS)
        zf.writestr("xl/worksheets/sheet1.xml", sheet)

• Step 4: Run the test to verify it passes

Run: python tests\test_spreadsheet_write.py Expected: 5/5 passed

• Step 5: Sanity-check the file actually opens in Excel/WPS

This machine has Excel/WPS installed (seen in the video). Manually:

python -c "import sys; sys.path.insert(0,'src'); from ecw_bot.core import spreadsheet as s; s.write_xlsx('Output/_manual_check.xlsx', [['A','B'],['1','2']])"

Open Output\_manual_check.xlsx by hand and confirm it opens with no "repair" prompt and shows the two cells. Delete the file afterward — it's a throwaway check, not a fixture.

• Step 6: Commit

cd "D:\Ecw Process"
git add src/ecw_bot/core/spreadsheet.py tests/test_spreadsheet_write.py
git commit -m "feat: add write_xlsx() to core/spreadsheet.py"

---

Task 3: core/payer_groups.py + seed payer_groups.json

Files:

• Create: src/ecw_bot/core/payer_groups.py
• Create: src/ecw_bot/payer_groups.json
• Test: tests/test_payer_groups.py (new)

Interfaces:

• Produces: payer_groups.load_mapping(path=None) -> dict[str, str], payer_groups.classify(payer: str, mapping: dict) -> str | None.

• Consumes: nothing from Task 2.

• Step 1: Write the failing tests

Create tests/test_payer_groups.py:

"""
Payer -> Group Name lookup.

    python tests\\test_payer_groups.py

Exact-string match only, on purpose: eCW's own PAYER values already differ by
case and spacing for what is really the same carrier (`United Healthcare` /
`United Health Care` / `United healthcare` all appear in one real export), so
"close enough" matching would have to guess which of several real, distinct
strings a new one is closest to. Billing data does not get a guess — an
unrecognized string must come back None and get a human's attention.
"""

import sys
import json
import pathlib
import tempfile

sys.path.insert(0, str(pathlib.Path(__file__).resolve().parent.parent / "src"))

from ecw_bot.core import payer_groups                # noqa: E402

TMP = pathlib.Path(tempfile.mkdtemp())

MAPPING = {
    "Medicaid": "Medicaid",
    "DentaQuest": "Dentaquest",
    "Anthem Blue Cross Blue Shield": "Commercial",
}


def case_exact_match_returns_group():
    assert payer_groups.classify("Medicaid", MAPPING) == "Medicaid"
    assert payer_groups.classify("DentaQuest", MAPPING) == "Dentaquest"


def case_unmapped_payer_returns_none():
    assert payer_groups.classify("Some New Payer LLC", MAPPING) is None


def case_case_sensitive_no_fuzzy_matching():
    """Different case is a different string, on purpose — see module docstring."""
    assert payer_groups.classify("medicaid", MAPPING) is None
    assert payer_groups.classify("MEDICAID", MAPPING) is None


def case_blank_or_none_payer_is_unmapped():
    assert payer_groups.classify("", MAPPING) is None
    assert payer_groups.classify(None, MAPPING) is None


def case_load_mapping_reads_json_file():
    path = TMP / "mapping.json"
    path.write_text(json.dumps({"Medicaid": "Medicaid"}), encoding="utf-8")
    loaded = payer_groups.load_mapping(path)
    assert loaded == {"Medicaid": "Medicaid"}, loaded


def case_load_mapping_missing_file_raises():
    try:
        payer_groups.load_mapping(TMP / "does_not_exist.json")
    except FileNotFoundError as exc:
        assert "does_not_exist.json" in str(exc), exc
        return
    raise AssertionError("expected FileNotFoundError")


def case_seed_config_loads_and_covers_medicaid_and_dentaquest():
    """The real config file this ships with, not a fixture."""
    mapping = payer_groups.load_mapping()
    assert mapping.get("Medicaid") == "Medicaid", mapping.get("Medicaid")
    assert mapping.get("DentaQuest") == "Dentaquest", mapping.get("DentaQuest")


CASES = [v for k, v in sorted(globals().items()) if k.startswith("case_")]


def main() -> int:
    failures = 0
    for fn in CASES:
        name = fn.__name__[5:].replace("_", " ")
        try:
            fn()
            print(f"  ok    {name}")
        except AssertionError as exc:
            print(f"  FAIL  {name}: {exc}")
            failures += 1
        except Exception as exc:
            print(f"  ERROR {name}: {type(exc).__name__}: {exc}")
            failures += 1
    print(f"\n{len(CASES) - failures}/{len(CASES)} passed")
    return 1 if failures else 0


if __name__ == "__main__":
    sys.exit(main())

• Step 2: Run it to verify it fails

Run: python tests\test_payer_groups.py Expected: ERROR on every case — ModuleNotFoundError: No module named 'ecw_bot.core.payer_groups'

• Step 3: Create the seed config, src/ecw_bot/payer_groups.json

{
  "Medicaid": "Medicaid",
  "DentaQuest": "Dentaquest",
  "CHP+ DENTAQUEST": "Dentaquest",
  "AARP Medicare Advantage": "Medicare",
  "Aetna Medicare ADV": "Medicare",
  "OUTREACH PROGRAM": "Program",
  "Anthem Blue Cross Blue Shield": "Commercial",
  "Cigna HealthCare Of Colorado": "Commercial",
  "Cigna Healthcare of CO": "Commercial",
  "Cigna Salud Employee": "Commercial",
  "Cigna": "Commercial",
  "Cypress Benefit Admin / Cigna PPO": "Commercial",
  "United Healthcare": "Commercial",
  "United Health Care": "Commercial",
  "United healthcare": "Commercial",
  "Unitedhealth One": "Commercial",
  "Oxford Health Insurance UHC": "Commercial",
  "UMR": "Commercial",
  "Aetna": "Commercial",
  "Aetna Alaska Care": "Commercial",
  "AETNA HEALTH AND LIFE INSURANCE": "Commercial",
  "BIND BENEFITS INC": "Commercial",
  "Select Health": "Commercial"
}

This is the "Mapped (seeded)" table from the spec, byte-for-byte. The 17 payers in the spec's "Left unmapped" list are deliberately absent — classify() returning None for them, and the flow logging/flagging that, is the intended behavior until a human adds them.

• Step 4: Implement core/payer_groups.py

"""
Payer -> Group Name lookup for the categorization step.

Exact-string match against a small hand-edited table (`payer_groups.json`,
next to this package) — no fuzzy or substring matching. eCW's own PAYER
values already differ by case and spacing for what is really the same
carrier (see `tests/test_payer_groups.py`'s docstring), so "close enough"
matching has no safe way to pick which known string a new one is closest to.
An unrecognized payer must come back as None and get a human's attention —
never a guess, on billing data.

Adding a payer is a one-line edit to `payer_groups.json`, no code change.
"""

import json
from pathlib import Path

_CONFIG_PATH = Path(__file__).resolve().parent.parent / "payer_groups.json"


def load_mapping(path=None) -> dict:
    """The payer -> group table. `path` defaults to the shipped config."""
    p = Path(path) if path else _CONFIG_PATH
    if not p.exists():
        raise FileNotFoundError(
            f"{p} not found — the payer group table is missing")
    return json.loads(p.read_text(encoding="utf-8"))


def classify(payer: str, mapping: dict) -> str:
    """The Group Name for this exact PAYER string, or None if unmapped."""
    return mapping.get((payer or "").strip())

• Step 5: Run the tests to verify they pass

Run: python tests\test_payer_groups.py Expected: 7/7 passed

• Step 6: Commit

cd "D:\Ecw Process"
git add src/ecw_bot/core/payer_groups.py src/ecw_bot/payer_groups.json tests/test_payer_groups.py
git commit -m "feat: add payer_groups lookup module and seed config"

---

Task 4: Wire the categorization step into the export flow

Files:

• Modify: src/ecw_bot/settings.py
• Modify: .env.example
• Modify: src/ecw_bot/flows/export_medcor_ready.py
• Test: tests/test_categorize_by_payer.py (new)

Interfaces:

• Consumes: spreadsheet.read_rows, spreadsheet.write_xlsx (Task 2); payer_groups.load_mapping, payer_groups.classify (Task 3).

• Produces: export_medcor_ready._categorize_by_payer(saved: Path | None) -> Path | None — writes Output\Medcor Ready_<date>.xlsx and returns its path, or None if the step could not run (never raises).

• Step 1: Add the new setting

In src/ecw_bot/settings.py, immediately after the SHOW_CLAIM_IDS setting (currently ending around line 311, just before the # ── Per-claim processing ── section header), add:

# After categorizing by payer, tell the operator about any PAYER value with
# no entry in payer_groups.json — those rows are saved with a blank Group
# Name rather than a guess. Informational only, same reasoning as
# NOTIFY_AFTER_EXPORT above. Set 0 for unattended runs; the full list still
# goes to the log either way.
NOTIFY_UNMAPPED_PAYERS = _env_bool("NOTIFY_UNMAPPED_PAYERS", True)

• Step 2: Add the matching line to .env.example

Open .env.example, find the block documenting SHOW_CLAIM_IDS (search for that key), and add directly after it:

# After categorizing the export by payer, show a box listing any PAYER value
# with no entry in src/ecw_bot/payer_groups.json (those rows get a blank
# Group Name, not a guess). 1 = show it, 0 = log only.
NOTIFY_UNMAPPED_PAYERS=1

Match whatever 1/0 convention the surrounding boolean settings in that file already use — check SHOW_CLAIM_IDS's line right above it and mirror its exact style so the two files can't drift the way the earlier OTP_WAIT_SECONDS mismatch did.

• Step 3: Write the failing test for _categorize_by_payer

Create tests/test_categorize_by_payer.py:

"""
export_medcor_ready._categorize_by_payer(): the payer -> Group Name step.

    python tests\\test_categorize_by_payer.py

Builds a real xlsx (same helper shape as test_claim_file.py's `xlsx()`) so
this exercises the actual read -> classify -> write path, not a mock of it.
"""

import sys
import zipfile
import pathlib
import tempfile

sys.path.insert(0, str(pathlib.Path(__file__).resolve().parent.parent / "src"))

from ecw_bot.flows import export_medcor_ready as flow   # noqa: E402
from ecw_bot.core import spreadsheet                     # noqa: E402
from ecw_bot import settings as cfg                       # noqa: E402

TMP = pathlib.Path(tempfile.mkdtemp())
NS = 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'


def xlsx(header, data_rows, name="t.xlsx") -> pathlib.Path:
    """A minimal real xlsx: header + data rows, both plain string lists."""
    rows = [header] + data_rows
    body = []
    for r, row in enumerate(rows, start=1):
        cells = "".join(
            f'<c r="{chr(65 + c)}{r}" t="str"><v>{v}</v></c>'
            for c, v in enumerate(row))
        body.append(f'<row r="{r}">{cells}</row>')
    sheet = (f'<?xml version="1.0"?><worksheet {NS}><sheetData>'
             f'{"".join(body)}</sheetData></worksheet>')
    path = TMP / name
    with zipfile.ZipFile(path, "w") as z:
        z.writestr("xl/worksheets/sheet1.xml", sheet)
    return path


HEADER = ["CLAIMS#", "PAYER", "CHARGES"]


def case_categorizes_known_payers():
    saved = xlsx(HEADER, [
        ["9107000", "Medicaid", "275.00"],
        ["9106000", "DentaQuest", "117.00"],
    ], name="known.xlsx")
    out = flow._categorize_by_payer(saved)
    assert out is not None and out.exists(), out
    rows = spreadsheet.read_rows(out)
    assert rows[0] == HEADER + ["Group Name"], rows[0]
    assert rows[1] == ["9107000", "Medicaid", "275.00", "Medicaid"], rows[1]
    assert rows[2] == ["9106000", "DentaQuest", "117.00", "Dentaquest"], rows[2]


def case_unmapped_payer_gets_blank_group_not_a_guess():
    saved = xlsx(HEADER, [
        ["9108000", "Some Brand New Payer", "50.00"],
    ], name="unmapped.xlsx")
    out = flow._categorize_by_payer(saved)
    rows = spreadsheet.read_rows(out)
    assert rows[1][-1] == "", rows[1]     # blank, not a guessed group


def case_none_input_is_a_no_op():
    assert flow._categorize_by_payer(None) is None


def case_missing_payer_column_does_not_raise():
    saved = xlsx(["CLAIMS#", "CHARGES"], [["9108000", "50.00"]],
                 name="nopayer.xlsx")
    assert flow._categorize_by_payer(saved) is None    # skipped, not a crash


CASES = [v for k, v in sorted(globals().items()) if k.startswith("case_")]


def main() -> int:
    failures = 0
    for fn in CASES:
        name = fn.__name__[5:].replace("_", " ")
        try:
            fn()
            print(f"  ok    {name}")
        except AssertionError as exc:
            print(f"  FAIL  {name}: {exc}")
            failures += 1
        except Exception as exc:
            print(f"  ERROR {name}: {type(exc).__name__}: {exc}")
            failures += 1
    print(f"\n{len(CASES) - failures}/{len(CASES)} passed")
    return 1 if failures else 0


if __name__ == "__main__":
    sys.exit(main())

• Step 4: Run it to verify it fails

Run: python tests\test_categorize_by_payer.py Expected: ERROR on every case — AttributeError: module 'ecw_bot.flows.export_medcor_ready' has no attribute '_categorize_by_payer'

• Step 5: Implement _categorize_by_payer in flows/export_medcor_ready.py

Change the import line near the top (currently from ..core import guard, runlog, msgbox) to:

from ..core import guard, runlog, msgbox, spreadsheet, payer_groups

Add this new function directly after _read_back_claim_ids (which ends at the return info around line 85), before _read_claim_form:

def _categorize_by_payer(saved):
    """
    Add a Group Name column (Commercial / Medicaid / Medicare / Dentaquest /
    Program / ...) next to the saved report, based on each row's PAYER, and
    write it to Output\\Medcor Ready_<date>.xlsx.

    Never touches `saved` itself: claim_file.py and everything after it reads
    that file's columns by position (see claim_file.py's module docstring),
    so this always writes a SEPARATE file rather than editing it in place.

    Never fatal. An unmapped payer, a missing PAYER column, or a write
    failure is worth reporting loudly, not worth losing the already-saved
    report over — same reasoning as `_read_back_claim_ids` above.
    """
    if saved is None:
        return None
    try:
        rows = spreadsheet.read_rows(saved)
    except Exception as exc:
        print(f"[CATEGORIZE] Could not read {saved.name}: {exc}")
        return None
    if not rows:
        print(f"[CATEGORIZE] {saved.name} has no rows — skipping")
        return None

    header, data = rows[0], rows[1:]
    if "PAYER" not in header:
        print(f"[CATEGORIZE] No PAYER column in {saved.name} — skipping. "
              f"Header reads: {header}")
        return None
    payer_col = header.index("PAYER")

    try:
        mapping = payer_groups.load_mapping()
    except Exception as exc:
        print(f"[CATEGORIZE] Could not load payer_groups.json: {exc}")
        return None

    out_rows = [header + ["Group Name"]]
    unmapped = {}
    mapped_count = 0
    for row in data:
        payer = row[payer_col] if payer_col < len(row) else ""
        group = payer_groups.classify(payer, mapping)
        if group:
            mapped_count += 1
        else:
            unmapped[payer] = unmapped.get(payer, 0) + 1
        out_rows.append(list(row) + [group or ""])

    out_path = cfg.ROOT / "Output" / f"Medcor Ready_{cfg.TODAY}.xlsx"
    try:
        spreadsheet.write_xlsx(out_path, out_rows)
    except Exception as exc:
        print(f"[CATEGORIZE] Could not write {out_path.name}: {exc}")
        return None

    print(f"[CATEGORIZE] {mapped_count} of {len(data)} row(s) categorized "
          f"-> {out_path.name}")
    if unmapped:
        listed = sorted(unmapped.items(), key=lambda kv: -kv[1])
        print(f"[CATEGORIZE] {sum(unmapped.values())} row(s) with no group "
              f"in payer_groups.json:")
        for payer, count in listed:
            print(f"[CATEGORIZE]   {count:4d}  {payer!r}")
        if cfg.NOTIFY_UNMAPPED_PAYERS:
            body = (f"{sum(unmapped.values())} row(s)-ku Group Name "
                     f"kidaikkala.\n\n"
                     f"{out_path.name} file-la '' (blank) Group Name-oda "
                     f"irukkum. Idha payer_groups.json-la add pannunga:\n\n"
                     + "\n".join(f"  {p}  ({n} row)" for p, n in listed[:15]))
            if len(listed) > 15:
                body += f"\n  ... innum {len(listed) - 15}"
            msgbox.info_box("eCW Bot — Group Name podala", body)

    return out_path

Add the call into _claims_phase — change:

    saved = claim_report.export_claims_report(page)
    info  = _read_back_claim_ids(saved)
    if info:
        _process_claims(page, info)

to:

    saved = claim_report.export_claims_report(page)
    info  = _read_back_claim_ids(saved)
    _categorize_by_payer(saved)
    if info:
        _process_claims(page, info)

Finally, update the module's own flow docstring at the top of the file — change:

      → Input\Claim Report_<MM-DD-YYYY>.xlsx
      → read the file back, pull the CLAIMS# column
      → search each claim id on its own          (CLAIM_PROCESS_LIMIT)

to:

      → Input\Claim Report_<MM-DD-YYYY>.xlsx
      → read the file back, pull the CLAIMS# column
      → categorize by payer → Output\Medcor Ready_<MM-DD-YYYY>.xlsx
      → search each claim id on its own          (CLAIM_PROCESS_LIMIT)

• Step 6: Run the test to verify it passes

Run: python tests\test_categorize_by_payer.py Expected: 4/4 passed

• Step 7: Run the full existing test suite to confirm nothing else broke

python tests\test_settings.py
python tests\test_guard.py
python tests\test_dead_end.py
python tests\test_claim_file.py
python tests\test_claim_form.py
python tests\test_progress_note.py
python tests\test_input_archive.py
python tests\test_spreadsheet_write.py
python tests\test_payer_groups.py
python tests\test_categorize_by_payer.py

Expected: every one reports all cases passed. test_settings.py in particular asserts the module graph stays acyclic and that no selector leaks into settings — confirm it still passes since this task touched settings.py.

• Step 8: Commit

cd "D:\Ecw Process"
git add src/ecw_bot/settings.py .env.example src/ecw_bot/flows/export_medcor_ready.py tests/test_categorize_by_payer.py
git commit -m "feat: categorize Medcor Ready export by payer after each run"

---

Task 5: Document it

Files:

• Modify: README.md
• Modify: CHANGELOG.md

Interfaces: none — documentation only.

• Step 1: Add a short section to README.md

Find the "### Reading the claim ids back" section (the last subsection under "## Claim lookup", ending just before "## Known gaps"). Add a new subsection directly after it:

### Categorizing by payer

Right after the claim ids are read back, every row of the just-saved report
gets a `Group Name` (`Commercial`, `Medicaid`, `Medicare`, `Dentaquest`,
`Program`, ...) based on its `PAYER`, and the result is saved to
`Output\Medcor Ready_<MM-DD-YYYY>.xlsx`. The original `Input\` file is never
touched — `claim_file.py` and the charge-entry phase depend on its exact
column layout.

The mapping is `src\ecw_bot\payer_groups.json` — a flat, hand-editable
`{"exact PAYER string": "Group Name"}` table. **Exact match only, no fuzzy
matching**: eCW's own PAYER values already differ by case and spacing for
what is really the same carrier (`United Healthcare` / `United Health Care`
/ `United healthcare` are three separate real values), so a PAYER string
with no entry gets a **blank** Group Name rather than a guess. Unmapped rows
are logged and, unless `NOTIFY_UNMAPPED_PAYERS=0`, shown in a message box —
adding one is a one-line edit to the JSON file, no code change.

Never fatal: a missing PAYER column, an unreadable report, or a write
failure is logged and the run continues — the export already succeeded by
the time this step runs.

• Step 2: Add a CHANGELOG entry

Append to the end of CHANGELOG.md:

---

## Day 4 — 2026-09-15

**Automated the payer categorization step.** Reviewed against
`Salud-Charge Entry_Medcor Ready.mp4` (2:45-10:00): after the Medcor Ready
export, an operator was filtering the report by `PAYER` and hand-filling a
`Group Name` column (`Commercial`, `Medicaid`, `Medicare`, `Dentaquest`,
`Program`, ...) — the same manual, repetitive, rule-based step every other
phase of this bot exists to remove.

`core/payer_groups.py` does the lookup: exact string match against
`payer_groups.json`, on purpose — eCW's own PAYER values already differ by
case and spacing for what is really the same carrier (`United Healthcare` /
`United Health Care` / `United healthcare` are three separate values in one
real export), so anything looser has no safe way to pick which known string
a new one is closest to. An unrecognized payer gets a **blank** Group Name,
not a guess, and is logged and message-boxed — this is billing data, and
wrong is worse than blank.

`core/spreadsheet.py` gained a write side (`write_xlsx()`) to go with its
read side, built the same way — stdlib only, no openpyxl — but with the full
minimal OOXML part set (`[Content_Types].xml`, both `.rels` files,
`workbook.xml`) the reader doesn't need but real Excel does, since this file
is meant to be opened by a person.

The categorized output is a **new** file, `Output\Medcor Ready_<date>.xlsx`
— `Input\Claim Report_<date>.xlsx` is never modified, because `claim_file.py`
and the charge-entry phase read its columns by position.

**Seeded, not complete.** The mapping table ships with 23 payers covering
roughly 95% of a real export's row volume (`Medicaid`, `DentaQuest`, and the
clearly-commercial carriers), derived from the actual current export rather
than guessed from the video. 17 payers (`BH COLORADO ACCESS`, the Delta
Dental variants, `Tricare`, `Devoted`, several TPA names, ...) are
deliberately left out — first run will flag them, and filling them in is a
one-line JSON edit, no redeploy.

**Out of scope for this round:** splitting rows into the `Patient` /
`Pending with Error` / `Program` sibling workbooks the video also shows, and
writing anything to the network `Charge\Allocation\...` folder — both stay
manual for now.

• Step 3: Commit

cd "D:\Ecw Process"
git add README.md CHANGELOG.md
git commit -m "docs: document the payer categorization step"

---

Self-review notes

• Spec coverage: architecture (Task 2-4), components (payer_groups.py Task 3, write_xlsx Task 2, flow wiring Task 4), data flow (Task 4 Step 5), error handling (unmapped payer / missing file / write failure, all in Task 4 Step 5 and covered by tests), testing (every task has its own test_*.py), seed mapping table (Task 3 Step 3, copied verbatim from the spec). All covered.
• Placeholder scan: no TBD/TODO; every step has real, complete code.
• Type consistency: classify(payer, mapping) signature is identical everywhere it's called (Task 3 tests, Task 4's _categorize_by_payer); load_mapping(path=None) likewise.
• Scope: single subsystem (categorization only), matches the spec's explicit out-of-scope list — no split-into-3-files logic anywhere in this plan.