Every failure comes with the rows behind it.

Engineering generates commission data, what insurance carriers owe agents and agencies, as six CSV files per carrier, market and statement month. The data team has to prove every file is right before anyone is paid. I turned the checks into a Python script that Claude runs in a workspace I set up, then designed the dashboard and Teams summary that show pass, warning or fail and where each failure is.

An internal tool. The screens are my wireframes redrawn with made-up numbers. Carrier and agency names are real; every dollar figure is changed.

Six files, and every one has to agree

Each statement month produces six CSV files per carrier and market: a one line summary, the exceptions, a calculation file with one row per policy, and three payment files. All six have to agree, on top of a set of general checks and each carrier’s own.

Done by hand in Excel, that took hours, and engineers spent a long time just finding where a file failed. The need was real: of 40 runs on the final version of the checks, 20 failed and 16 came back with warnings.

• DATA TEAMValidates every file before anyone is paid.
• ENGINEERSFix what fails, so they need to know exactly where.
• EVERYONE ELSETeammates who are not engineers, and stop at check names.

The bug no total could see

On Sentara Health Plans every total tied to the penny, and agencies were still paid wrong. Leon Partners and Marketplace were each paid exactly 80% of their contract rate. The missing money stayed in the house override, so every total still added up and no existing check could see it.

NEW CHECK

Reads back what each agency was paid per member per month and holds it against the CRM rate. A mismatch is a fail, because it is real money.

DIAGNOSIS

The ratio was exactly 0.80 on every row, so I treated it as a formula: the house share was taken out twice. Solving it predicted the correct rate for nine agencies.

PROOF

When engineering shipped the fix, all nine landed on the predicted rate.

Show what passed, not only what broke

PROBLEM

Priority Health’s first run was clean: 35 checks passed and 2 small warnings. The Teams summary listed only the two warnings, so the engineer could not tell the run was good.

PICKED

Every summary ends with a Passed list, and a run with only warnings says outright that it is safe to release.

WHY

A report that lists only problems reads like a bad run, even when it is a good one.

Two readers, one screen, and rules everyone reads the same way

PROBLEM

Engineers read the dashboard fluently. Anyone else hit check names like Override_Amt decomposition and stopped.

OPTIONS

Rework the layout for both groups, or leave it as engineers knew it and add a layer on top.

PICKED

Nothing moved. Hovering any check, status or file answers what it looked at, why it matters and what to do next. Terms like NPN get a dotted underline and a one line definition.

MY RULES

Missing data is a fail, never a warning: a blank agent ID means someone does not get paid. A new check starts as a warning until a carrier’s data confirms it. A run with only warnings is safe to release.

A spec instead of a script

I handed engineering a 14 section rebuild spec rather than my Python file: every rule and the reasoning behind it, with no real names, IDs or dollar figures, ending with the design debt to fix rather than copy. Engineering built the checks into their own internal tools.

• HOURS TO MINUTESReview time, compared with checking by hand in Excel.
• 27 CARRIERSCovered at handoff, across ACA and Medicare.
• 24 TO 56 CHECKSGeneral checks plus each carrier’s own.
• 60+ RUNSOn the dashboard, May to August 2026.