Fixing royalty statements that paid authors the wrong amount

The problem

Author royalty statements were wrong in several independent ways. Every error changed what an author was paid, and one inflated statements, so some authors were overpaid. Correctness mattered more than speed.

What I built

Settlement queries had no start date, so unsettled transactions from earlier periods inflated current statements. I fixed it by clamping every ledger read to the statement period, on by default.

That fix created an edge case. Pre-orders placed before a new title’s first period became unreachable. I lifted the floor only for the opening period of newly published titles, and kept it for backlist titles and titles with unknown pub dates. Backlist history from before migration was already paid in the legacy royalty system, so lifting the floor there would pay it twice.Chose:Err toward the correctable mistake. Under-paying can be corrected on a later statement; over-paying means clawing money back from an author. Stet, left as it was:The floor for backlist and unknown-date titles.

Discount bands that touch, such as 50–55% and 55–100%, both matched a 55% sale, and first-match lookup chose the wrong rate. Neither band is more specific than the other, so which one owns the boundary is a product decision. A shared boundary now goes to the higher band.Chose:One function decides the band for both the ledger math and the statement display, so the two can’t disagree. Rejected:First match.

The discounted-sales table had its own bug. The code overwrote each row’s displayed rate as it processed entries, so the last write won, and a row could show a list price as a percentage. I split the table into one row per format, applied rate and calculation method.Chose:NetProfit and ProfitSharing statements keep one row per format, because their math re-derives amounts from that single row.

Discount configurations that came in through a publisher’s royalty import were silently inert, because a threshold was never set. I fixed every write path and wrote a read-only diagnostic script to find the rows already affected. The backfill was applied to 188 agreement/format rows for that publisher. A dry run found about 206 more in other live accounts, which are still waiting.

I added calculation methods per step, with a clear resolution order: step, then format, then agreement. No migration was needed.

Outcome

Statements now read only their own period, except that a new title’s first statement also picks up its pre-orders. That exception took one agreement’s opening statement from $66.18 to $3,277.88, with December 2024 pre-orders the date floor had been hiding. Touching discount bands resolve to a single rate, and discounted rows show the rate that was actually applied. The backfill repaired 188 agreement/format rows for the imported publisher.

Stack

• TypeScript
• Node.js
• MySQL
• AWS Lambda
• Pug
• Jest