Automating Puerto Rico Law 95 reconciliation

Context

A mutual-aid health organization in Puerto Rico had to bill and reconcile collections each month under Puerto Rico Law 95, and the work was done by hand.

Problem

Monthly billing, data cleaning and financial reconciliation were done by hand every month, for individual members and for joint memberships shared by spouses.

Constraints

• Regulatory rules under Law 95.
• Financial figures that must match exactly.
• A monthly deadline.

Architecture

The diagram, step by step

1. Billing and payment files.
2. Monthly run started from an application screen.
3. Python ingestion and cleaning.
4. SQL matching rules.
5. Reconciliation report and exceptions list.
6. Finance review.

Key decisions

• Rules in SQL, steps in Python. Matching and reconciliation rules live in SQL, next to the data, where they are easy to read, test and audit; Python handles file intake, cleaning and the order of the steps. A rule can change without touching the pipeline, and the pipeline can change without touching a rule.
• Exceptions, not silence. The process takes several input files and produces several outputs: the monthly billing, the reconciliation report and a list of records that do not match, which go to review instead of being forced through.
• A person in the loop. The monthly cycle is started from an application screen, because a user reviews and confirms results along the way. The pipeline does the heavy work; the person keeps control of what gets billed.

Outcome

The monthly process went from fully manual to an automated pipeline.

My role

I designed and built the pipeline.

What I can’t share

The client’s name and any financial figures.