Skip to content
ES
2026 In production

Replacing a 20-year-old commission engine

A money-critical MLM commission engine, replaced on a cold cutover with no parallel run — and the four months of reconciliation that followed.

The commission engine's cycle screen: three steps — process, reverse and submit a cycle — over a table of end-of-period runs listing stage, cycle number, begin and end dates, status and the date each was run.
Cycle control and the end-of-period run history — the rows reading “Commit failed” are part of the record, not an exception hidden from it. Nothing on this screen is client data: no names, no amounts, no contact details.
Breaking-rank evaluation diagram: a director at the root with breaking rank EM, and three branches of the downline. A branch is drawn solid and ticked while each rank stays below the breaking rank, and dashed with a cross from the first rank that equals or exceeds it.
The rule that took finance to define, not the old SQL to read: a rank below the breaking rank passes, a rank equal to or above it stops the branch. Drawn for the rewrite.

The problem. Twenty years of commission rules living in accumulated SQL, with nested conditionals whose execution path depended on the year a plan had been configured. The source was readable; the intent was not. The person who designed it was unavailable.

The constraint that was not negotiable. A cold cutover. The old engine was switched off at 23:59 on the 20th of the month and the new one went live the same night, with no parallel run — so the first two biweekly periods were part old engine, which could no longer be queried, and part new engine, which did not yet encode every rule.

What did not work. Reading the old SQL to recover the rules. We stopped writing code and sat with the client's finance department to define the terms instead: what a generation is, what makes a director active, what happens when someone changes level mid-month. The hardest case was multi-level commissions paid to directors over successive generations below them, on a hierarchy that changes over time — a slowly changing dimension, with almost no clean history to validate against.

The outcome, stated honestly. About four months of cycles with errors, reconciled with finance, each discrepancy traced to a specific plan rule rather than written off as a system fault. Residual error at the end: on the order of $100 in total, across payments summing to hundreds of thousands of dollars distributed among thousands of consultants. Written four-handed with Alejandro Cespón.

The number worth quoting here is not the one that sounds good. Four months of errors is the honest headline; the ~$100 residual is what the four months bought.

Docs →