How a Reconciliation Run Works (Technical)

This page is the technical companion to the Running a Reconciliation guide. It explains what actually happens under the hood when a coordinator clicks Run in the dashboard — and, most importantly, how and when CieTrade data flows into a run.

The live app is the standalone FastAPI + React service at portal-recon.politegrass-38e79dd2.eastus.azurecontainerapps.io.

1. The crux: two independent data flows

The app compares portal submissions (the files coordinators upload) against CieTrade AR (the system of record for what's actually been billed). Those two sides come from two entirely separate pipelines running on different schedules:

Pipeline	What it does	When it runs
CieTrade → PostgreSQL	Syncs CieTrade AR, billing-sheet charges, and billing-sheet headers into the `cietrade` schema	Scheduled — a Prefect ETL on separate infrastructure, on its own clock
A reconciliation run	Reads whatever CieTrade data is already in Postgres, parses uploaded portal files, and compares them	On demand — the moment a coordinator uploads files and clicks Run

Clicking "Run" does NOT refresh CieTrade data

A reconciliation run reads the CieTrade snapshot as of the last Prefect sync — it does not pull anything fresh from CieTrade. If the CieTrade data in Postgres is stale or lagging, a fresh run alone will not fix it. The upstream ETL has to sync first, then you re-run.

   Prefect ETL (scheduled, upstream)          Coordinator (on demand)
              │                                        │
              ▼                                        ▼
  ┌───────────────────────────┐          ┌────────────────────────────┐
  │  CieTrade  →  Postgres     │          │  Portal export files        │
  │  cietrade.* schema         │          │  (Ariba, Coupa, FM Pilot…)  │
  │  (AR daily, billsheet inc) │          │  uploaded via React UI      │
  └───────────────┬───────────┘          └───────────────┬────────────┘
                  │       reads the snapshot that         │
                  │       already exists at run time      │
                  └────────────────┬─────────────────────┘
                                   ▼
                       run_reconciliation()
                                   ▼
                    Reports + rollup + snapshot
                    persisted to portal_recon.*

2. The CieTrade ETL (scheduled, upstream)

A Prefect ETL (defined in the separate prefect-azure-infrastructure repo, run on an Azure ACI push pool) populates the cietrade schema in the wasteology_dev PostgreSQL database. This is the authoritative billed-invoice source. Three tables matter:

Table	Contents	Sync behaviour
`cietrade.accounts_receivable`	Accounts Receivable — the open-balance ledger	Refreshed daily as a full snapshot. Holds only OPEN receivables (`balance > 0`). Paid invoices are purged — they simply disappear from the table once settled.
`cietrade.billing_sheet_charges`	Full POSTED billing history, including already-paid charges	Synced incrementally — can lag AR.
`cietrade.billing_sheets`	Billing-sheet headers (including `posting_date`)	Synced with the charges.

Why "paid invoices are purged" matters

Because AR drops invoices the instant they're paid, you can't reconstruct a fully-billed group's total from AR alone — a partially-paid group would look short. That's why group totals are summed from POSTED billing_sheet_charges history instead. See Group Number Matching.

3. How a run is triggered

A run can start two ways. Both execute the same reconciliation logic.

Dashboard (the normal path)

The React UI's Run action posts to POST /api/v1/upload/run (api/routers/upload.py). The flow:

The coordinator uploads the portal export files (uploader-allowlisted users only).
trigger_run reads all file bytes immediately — before any background work — because the request's temporary file handles aren't safe to hold once the request context tears down.
It schedules run_reconciliation as a FastAPI BackgroundTask and returns 202 {job_id} right away.
The client polls GET /api/v1/jobs/{job_id} for live status and, on completion, the results.

CLI (headless)

uv run python reconcile.py --portal-dir <dir> --ar-file <path>

Same reconciliation engine, but the portal files must already be on disk.

Uploaded files are not persisted

Each uploaded file is staged to a temporary path (_staged_upload), parsed, and then deleted. Only the parsed records are stored — never the raw files. There is no "re-run last upload" button: starting a new run requires the source files again.

(The temp path deliberately keeps the original filename, because a few adapters — notably Coupa CSV — derive the customer name from the filename stem.)

4. What `run_reconciliation` does, in order

This is the pipeline that executes in the background task:

Step 1 — Load CieTrade data live at run time

CieTrade is queried at the moment the run executes, via portal_reconciliation/cietrade.py, wrapped in a per-process 5-minute cache (api/cache.py) so concurrent viewers don't re-hit the DB. Four loaders run:

Loader	Returns	Notes
`load_billing_charges()`	Open AR invoices	Selected directly from `accounts_receivable` (`balance > 0`), gated by third-party account + a per-portal service-date window. The billing-sheet LEFT JOIN only enriches `service_period` — it does not gate which invoices are included.
`load_needs_billing_charges()`	Not-yet-posted revenue	`billing_sheet_charges` in `APPROVED`/`COMPLETED`/`OPEN` status — billed work that hasn't posted to AR yet.
`load_group_ct_totals()`	Per-group CT total	A hybrid: AR ∪ billsheet-only, so partially-paid groups still total correctly (AR alone would undercount).
`load_wo_invoice_map()`	VAWS WO# → `invoice_no`	Bridges VAWS work-order numbers to CieTrade invoices.

Step 2 — Parse each uploaded portal file

Each file runs through detect_portal → the matching adapter's .load(), producing the common NormalizedInvoice type. Eight portals are supported: Ariba, Coupa, FM Pilot, Corrigo, Mercado, Oracle, VendorCafe, VAWS.

Step 3 — De-duplicate

dedupe_exact_portal_records() removes exact-duplicate portal rows, and Coupa draft rows are dropped — before reconciling and before persistence, so stored rows and group sums stay clean.

Step 4 — Reconcile

The engine (portal_reconciliation/reconciler.py + rollup.py) matches records with priority direct invoice # → Group # → VAWS WO# and produces:

Unbilled WOs — work orders with no matching CieTrade invoice
Invoices Needing Action — open AR (and needs-billing) invoices to work
Amount Discrepancies — matched invoices whose amounts differ
a per-customer rollup and the flagged submissions list

(For the full matching rules, see Matching Logic.)

Step 5 — Persist the run

ReconRunStore (storage.py) writes to the portal_recon schema in wasteology_dev:

Table	Contents
`recon_runs`	Run metadata + aggregate counts, plus `snapshot_json` — a full, re-renderable JSONB snapshot of the whole run
`recon_results`	Flagged items (unbilled / unsubmitted / discrepancy)
`recon_portal_invoices`	Every normalized portal record
`recon_customer_summary`	The per-customer rollup output

5. Every run is a frozen snapshot

Every run writes a point-in-time snapshot with all values baked in. The dashboard renders that stored snapshot — it does not recompute on the fly. Two consequences:

Code or data changes only appear after a NEW run. A deploy alone changes nothing on screen.
Stale upstream data stays stale until the ETL syncs and you re-run.

The golden rule

Deploy → run. Any bug fix or CieTrade re-sync must be followed by a fresh reconciliation run before it shows up in the dashboard.

6. Integrations at a glance

Integration	Direction	Mechanism	Cadence
CieTrade → `cietrade.*` (`wasteology_dev` Postgres)	inbound	Prefect ETL (ACI push pool)	Scheduled — AR daily full snapshot; billsheet incremental
Portal files → app	inbound	Coordinator upload via React UI (`POST /upload/run`)	On demand
App → `portal_recon.*` (`wasteology_dev` Postgres)	outbound (persist)	`ReconRunStore.save_run`	Per run
Auth	—	Microsoft Entra ID (MSAL); uploader allowlist gates who can run	—
Hosting	—	Azure Container App `portal-recon` (RG `wdp-palantiri-rg`), image in `wdppalantiriacr`	—

7. Recent changes (2026-08-05)

Three coordinator-reported bugs were fixed and deployed:

#	Fix
#204	Group-invoice variance no longer counts voided/cancelled portal submissions toward the group total.
#205	Coupa invoice totals now include the per-line "Total tax" column.
#206	Corrigo "Disputed" invoice status now surfaces correctly.

These need a fresh run to appear

Per §5, these fixes only show up on a new reconciliation run — existing snapshots are unchanged.

1. The crux: two independent data flows​

2. The CieTrade ETL (scheduled, upstream)​

3. How a run is triggered​

Dashboard (the normal path)​

CLI (headless)​

4. What run_reconciliation does, in order​

Step 1 — Load CieTrade data live at run time​

Step 2 — Parse each uploaded portal file​

Step 3 — De-duplicate​

Step 4 — Reconcile​

Step 5 — Persist the run​

5. Every run is a frozen snapshot​

6. Integrations at a glance​

7. Recent changes (2026-08-05)​