I work on IntelliLogic, a medical device distribution software platform that schedules surgical cases, tracks consignment inventory, produces charge sheets and calculates rep commissions on one shared data model. Last year, before that model existed, a commission rate changed in June and every statement the distributor had issued for March changed with it.
Nobody noticed for two months. Then accounting ran a reconciliation, found that paid commissions did not match calculated commissions, and spent a week discovering that nothing had been paid wrong. The inputs had been overwritten after the fact. March was recalculated with June’s rate because the old spreadsheet had one rate column, and someone had typed a new number into it. That bug is not specific to medical device distribution software; it is what happens in any system where a rate, a price or a rule is stored as a single value with no memory.
If you have built anything with a price, a rate, a tax band or a rule that changes over time, you have either hit this bug or you are about to. This post is about the pattern that prevents it, using a real domain as the example.
The domain, briefly
A medical device distributor supplies implants to hospitals, sends a sales rep to the surgery, and pays the rep a commission. The commission is a percentage of a percentage: the manufacturer pays the distributor a rate on each line of the charge sheet, and the distributor pays the rep a share of that. Both rates differ by manufacturer, both change over time, and a rep only earns on a line if the surgeon on the case is assigned to them. The commissions module is where this pattern lives in the product, but nothing below is specific to it.
The naive schema looks like this:
create table manufacturer (
id bigint primary key,
name text not null,
company_rate numeric(5,4) not null -- 0.3000 means 30%
);
create table rep_manufacturer_rate (
rep_id bigint references rep(id),
manufacturer_id bigint references manufacturer(id),
rep_rate numeric(5,4) not null,
primary key (rep_id, manufacturer_id)
);
It is obvious, it is what most first versions ship, and it is wrong, because company_rate is a single value with no memory. Update it and every historical calculation that joins to it changes.
The pattern: rates are rows, not columns
An effective-dated record replaces the value with a history of values, each with the date it took effect and, optionally, the date it stopped.
create table manufacturer_rate (
id bigint primary key,
manufacturer_id bigint not null references manufacturer(id),
rate numeric(5,4) not null,
effective_from date not null,
effective_to date, -- null = still in effect
created_at timestamptz not null default now()
);
create unique index one_open_rate_per_manufacturer
on manufacturer_rate (manufacturer_id)
where effective_to is null;
The partial unique index is doing real work: at most one open-ended rate per manufacturer. Ending a rate means setting effective_to; starting a new one means inserting a row. Nothing is ever updated except that one column, and the old row keeps its value forever.
The rep’s rate gets the same treatment, keyed on the rep and the manufacturer:
create table rep_rate (
id bigint primary key,
rep_id bigint not null references rep(id),
manufacturer_id bigint not null references manufacturer(id),
rate numeric(5,4) not null,
effective_from date not null,
effective_to date
);
Resolving a rate as of a date
The question the application asks is never “what is the rate”. It is “what was the rate on this date”. That is one query:
select rate
from manufacturer_rate
where manufacturer_id = $1
and effective_from <= $2
and (effective_to is null or effective_to > $2)
limit 1;
Note the half-open interval: effective_from is inclusive, effective_to is exclusive. A rate ending on June 1 and a rate starting on June 1 do not overlap, and a line dated June 1 gets the new rate. Pick the convention once and write it in the column comments, because the off-by-one at the boundary is the second most common bug after the overwrite.
In TypeScript, the resolver is small:
type DatedRate = { rate: number; effectiveFrom: string; effectiveTo: string | null };
function rateAsOf(history: DatedRate[], date: string): number | null {
const hit = history.find(r =>
r.effectiveFrom <= date && (r.effectiveTo === null || r.effectiveTo > date)
);
return hit ? hit.rate : null;
}
Returning null rather than 0 matters. No rate on that date is a different fact from a rate of zero, and the caller should decide what to do about it. In our domain the answer is that the rep earns nothing on that line, and the UI says why.
Which date?
Here is the part that is specific to the domain but generalizes to anything with a lifecycle. A charge sheet line has several dates: the surgery happened on March 28, the purchase order arrived April 3, the facility paid May 14. Which date do you resolve the rate against, and which period does the commission fall into?
There is no universally right answer, so the system does not pick one. A company-level setting chooses the basis - paid date, surgery date or PO date - and a per-rep override can differ from it, because a salaried rep is often on surgery date while an independent rep is on paid date. The resolver just takes a date; the policy decides which date to hand it.
type Basis = "paid" | "surgery" | "po";
function periodDate(line: ChargeSheetLine, basis: Basis): string {
return basis === "paid" ? line.paidDate
: basis === "surgery" ? line.surgeryDate
: line.poDate;
}
The worked example
A line for 2,900 dollars, surgery date March 28. The manufacturer’s rate history has 30 percent from January 1 with no end. The rep’s rate for that manufacturer is 10 percent from January 1. The rep is assigned to the surgeon.
Resolve both rates as of March 28: 0.30 and 0.10. Commission is 2900 * 0.30 * 0.10 = 87.00.
Now the manufacturer renegotiates. On June 1 someone ends the 30 percent rate and inserts 22 percent from June 1. Re-run March: the resolver still finds the 30 percent row, because March 28 falls inside its interval. The March statement is 87.00 forever. A line dated June 15 resolves to 0.22 and pays 63.80.
That is the whole point. History did not change because history was never a single cell.
Things that bit us
Late entries. Sometimes the new rate is entered two weeks after it actually started. Because effective_from is data, not created_at, you backdate it, and the resolver does the right thing for lines in between. Keep created_at anyway; the audit trail of when someone recorded a change is separate from when the change took effect.
Gaps. Ending a rate without starting another leaves a gap. The resolver returns null, which is correct, and the UI should say “no rate in effect” rather than silently paying zero. We added a report that lists manufacturers with no open rate.
Overlaps. The partial unique index prevents two open-ended rows, but it does not prevent two closed rows that overlap. A check at insert time, or an exclusion constraint on a date range if your database supports it, closes that hole.
The eligibility rule is also dated. Which surgeon is assigned to which rep changes too. We modeled that assignment the same way, with an effective date, so a case performed before a handover stays with the rep who held the surgeon then. Once you have the pattern, you start seeing everything that should use it.
When not to do this
Not every value needs a history. A product’s display name does not. The test is whether a calculation for a past period will ever be re-run and expected to reproduce its original result. If yes, the input needs an effective date. If the value is only ever read for “now”, a column is fine.
The cost is real but small: one more table per dated value, a resolver function, and discipline about never updating a rate row. The benefit is that the meeting on the fifth of the month, where a rep and the office argue about a number, becomes a meeting about the cases instead of the arithmetic.
If you have solved this differently - bitemporal tables, event sourcing, a temporal database - I would like to hear how it went.
Disclosure: this post was drafted with AI assistance and reviewed and edited by the author, in line with DEV’s guidelines for AI-assisted articles.
Top comments (0)