Score match quality and load results into Postgres

This is Lesson 5, the final lesson, of the ERP and CRM reconciliation pipeline series, adapted from the published documentation for the erp-crm-reconciliation-pipeline repository.

What you’ll build

In this tutorial, you score the reconciliation run from Lesson 4 against the ground_truth.json answer key from Lesson 1, computing precision, recall, and F1. You also optionally load the same run into a Postgres target schema, an additional persistence layer alongside the JSON files reconcile already writes.

Before you begin

You need a dataset generated with --with-ground-truth and already reconciled. For the optional Postgres section, you also need Docker.

See it work

Score a reconciled run against its ground truth:

uv run python -m eval.score data --threshold 0.75

The scorer reruns the pipeline at the requested customer threshold, then reports precision, recall, and F1 for its invoice and payment matches. eval/score.py is the only module in this project allowed to read ground_truth.json; reconcile itself never consults it, so scoring can never leak into, or bias, the pipeline’s own matching logic.

For the committed eight-customer fixture, the exact output at source commit 54c0e15 is:

invoices   precision=100.0%  recall=84.2%  f1=91.4%
payments   precision=100.0%  recall=75.0%  f1=85.7%
combined   precision=100.0%  recall=80.0%  f1=88.9%

For the seed-42 200-customer dataset generated in Lesson 1, the same command produces:

invoices   precision=100.0%  recall=79.8%  f1=88.7%
payments   precision=100.0%  recall=74.5%  f1=85.4%
combined   precision=100.0%  recall=77.6%  f1=87.4%

Treat these as regression baselines for this source commit, seed, configuration, and threshold. If your output differs, compare generation_config.yaml before changing the resolver.

Why scoring is a separate, eval-only step

Keeping evaluation separate from the pipeline is a deliberate design choice, not an oversight. If reconcile could see the answer key, there’d be no way to trust that its match rate reflects real entity-resolution quality rather than the pipeline quietly consulting the answer during a run. Separating the two means:

• src/pipeline/ never imports anything from eval/.
• Precision, recall, and F1 are computed after the fact, purely from what reconcile already wrote to the crosswalk and quarantine log.
• You can swap in a different scoring threshold, or a different ground-truth dataset, without touching the pipeline at all.

Score the pipeline against the ground truth

1. Confirm your dataset was generated with --with-ground-truth (Lesson 1) and already reconciled (Lesson 4).

2. Run:

   uv run python -m eval.score data --threshold 0.75

3. Note the reported precision, recall, and F1.

4. Rerun with a different --threshold value and observe how a stricter or looser customer-match threshold shifts the trade-off between precision and recall.

Interpret precision, recall, and F1

• Precision answers: of the matches the pipeline reported, how many were actually correct? Low precision means the pipeline is matching customers, invoices, or payments that shouldn’t be linked.
• Recall answers: of the matches that should exist according to ground_truth.json, how many did the pipeline find? Low recall means real matches are ending up as orphans or in quarantine instead.
• F1 is the harmonic mean of the two, a single number for comparing runs after you change a threshold or a messiness ratio from Lesson 3.

The measured results show a conservative resolver: both runs achieve 100.0% precision, so every predicted pair is correct, while recall leaves some true pairs unresolved after cleaning and validation remove unusable records. For the 200-customer run, combined recall of 77.6% means the quality opportunity is recovering more valid pairs without sacrificing that precision.

Don’t assume every threshold change produces a new trade-off. The repository’s documented 500-customer calibration sweep found combined F1 flat at its 86.6% maximum from thresholds 0.10 through 0.95. It fell to 85.4% only at 0.99-1.00, where an exact-match-only gate rejected genuine near-identical names. The default 0.75 threshold sits inside the stable region, away from that edge.

Load a run into Postgres (optional)

The JSON crosswalk and quarantine log remain the source of truth reconcile reads back on every run. --postgres-dsn additionally loads the same run into a Postgres target schema:

docker compose up -d postgres
uv run reconcile data --postgres-dsn postgresql://reconcile:reconcile@localhost:5433/reconcile

This creates the customers, invoices, payments, crosswalk, and quarantine_log tables if they don’t already exist, and loads the run with the same idempotency semantics as the JSON files.

Confirm idempotent Postgres loads

Run the same --postgres-dsn command again. crosswalk upserts without reassigning an existing canonical_id, using Postgres’s UNIQUE NULLS NOT DISTINCT so two null values in erp_key or crm_key compare equal, matching the JSON crosswalk’s tuple-keyed semantics. quarantine_log is replaced in full each run, matching quarantine_log.json’s overwrite behavior.

Verify your work

• Run uv run python -m eval.score data --threshold 0.75 and confirm it prints precision, recall, and F1.
• If you ran the optional Postgres section, confirm all five tables exist and rerunning the load doesn’t reassign any canonical_id.
• State, in your own words, why reconcile is never allowed to read ground_truth.json directly.

Troubleshooting

Next steps

That completes the series: you generated a reproducible, deliberately messy synthetic dataset, compared its ERP and CRM field mappings, tuned its messiness, reconciled the two exports into a single source of truth, and scored the result against a known-correct answer key. Read the full erp-crm-reconciliation-pipeline repository, or its published documentation, for the module-level API reference this series didn’t cover.