What I built
I built an independent Python and SQLite reconciliation sample informed by my finance and accounting experience. It compares synthetic activation ledgers and invented tiered contract schedules against synthetic partner settlement statements, isolates underpayments and invalid clawbacks, and calculates their illustrative margin impact.
In high-volume partner programs, settlement files rarely fail with a syntax error—they fail silently through business-rule drift. A partner continues paying base-tier commissions after monthly volume crosses a retroactive tier threshold, deducts a deactivation clawback outside the contractual 90-day chargeback window, or omits a promotional co-op rebate. This sample turns those silent margin leaks into deterministic, row-level dispute packets.
Explore the reconciliation walkthrough
The browser walkthrough follows four representative settlement lines, including three disputes totaling $295.00. Select a line by its displayed discrepancy rule, inspect the contract vs. settlement math, stage a proposed batch recovery packet, and download the three-row CSV dispute queue; staging does not change recorded payouts or represent money recovered.
The repository’s separate 240-activation synthetic batch produces $3,140 recoverable across 36 flagged disputes. Those larger-batch results are not the browser example or its CSV export.
All partners, SKUs, contract rates, and dollar figures are invented fixtures running in your browser.
Architecture and decisions
- Separate contract rules from ledger facts. Tier thresholds, promotional credits, and clawback windows live in explicit contract tables rather than hardcoded SQL expressions, making retroactive tier recalculations repeatable.
- Distinguish valid chargebacks from out-of-window clawbacks. Deactivation deductions are cross-checked against original activation dates; deductions beyond the 90-day contractual window are routed to the recoverable dispute queue rather than absorbed into net revenue.
- Measure margin impact alongside dollar variance. Each reconciled line computes expected vs. settled gross margin in basis points (
bps) so FP&A and commercial leads can see which SKU families suffer structural margin compression. - Export audit-ready CSV and HTML packets. Beyond loading SQLite tables (
reconciled_settlementsanddispute_queue), the reporting module generates a row-level CSV exception file and an executive HTML summary grouped by partner and rule.
My contribution
I defined the reconciliation rules, contract tier schema, margin-waterfall math, and dispute queue structure—drawing on experience managing high-volume third-party telecom reconciliations and financial analysis—using the AI-assisted workflow described on the About page.
Scope and verification
Local validation passed 6 unit tests covering retroactive tier underpayments, 90-day clawback boundary enforcement, omitted promotional credits, duplicate settlement references, SQLite persistence, and the 240-row seeded generator with CSV/HTML export checks. The independent sample uses invented records and contract rules; its $3,140 discrepancy total is a synthetic result, separate from the historical recoveries described in my résumé. The pinned source revision is available locally and has not been published to GitHub.
Source availability
The reviewed implementation is not publicly available yet. Source publication will follow a separate review; use the synthetic browser walkthrough above in the meantime.
Source snapshot b92dcbb · Reviewed 2026-10-25. This case study describes that revision; development may continue separately.