Reproducing an Invoice From a Table That Only Knows Today's Price
A mid-market SaaS vendor bills business customers annually in advance, per seat, across three pricing tiers. Its warehouse holds a Product table with a single current_unit_price and current_tier column, overwritten each night by the CRM synchronisation job. When a price changes in the CRM, the previous value is gone.
Finance has lost two invoice disputes. In both, the customer was moved to a promotional tier mid-contract; the invoice applied the new tier to the whole year rather than from the change date, and the warehouse can no longer show what the price was on the invoice date. Auditors additionally require that the company reproduce, for any past date, the exact invoice it issued, including corrections, because CRM price fixes are sometimes backdated by up to 45 days after the original effective date. Sales wants mid-term upgrades and downgrades with proration, and customers on seat-based contracts add and remove seats several times a year.
The data modeller has one release cycle to change the Product and Billing tables.
1. Specify the historisation for pricing: the grain, the pattern, and why a plain effective-dated type-2 table is still not sufficient for this requirement.
Read the worked answer
Grain: one row per product, tier and currency, per version of that price. Pattern: type-2 with a surrogate key and a validity interval, never an in-place update of current_unit_price.
A single effective-dated interval is not enough because corrections are backdated up to 45 days after the original effective date. If you only store business time (effective_from, effective_to), a backdated correction silently rewrites what the warehouse reported last month, and nobody can prove which version was in force when a given invoice was produced. You need bitemporal storage: business time (when the price applied) plus system time (when the warehouse learned it), for example recorded_from and recorded_to alongside the effective interval, with is_current flags on the system-time axis only.
Operationally: a correction closes the existing row's system-time interval and inserts a new version; it never updates the old row. Enforce non-overlap of effective intervals per product/tier/currency within a system-time window using a constraint plus an ETL check, and reject a load that would produce an overlap rather than picking a winner silently.
For reproducibility, store the price_version_id on each invoice line at issue time, not just the calculated amount. That durable pointer means a later correction cannot retroactively change an issued invoice; corrections produce adjustment documents against a named prior version. Store the amount as well, because price plus proration logic reproduced later is never as trustworthy as the number actually billed.
2. How should the model represent mid-term upgrades, downgrades and seat changes with proration? Identify the entities and the traps.
Read the worked answer
Separate the catalogue from the commitment from the billing output; the current design collapses all three into Product.
Entities: price_version (catalogue, bitemporal as above); subscription (account, term start and end, status); subscription_item (one row per product/tier/quantity, with its own start and end date and a price_version_id); invoice; invoice_line (service period start and end, quantity, unit price, amount, price_version_id).
A mid-term upgrade is not an update to subscription_item. It closes the existing item (end date = change date) and inserts a new item from that date. Proration is then a calculation performed at billing time over the covered periods of each item, so the rule lives in billing logic, not inside a stored price. Seat changes follow the same pattern with quantity rather than price as the changing attribute.
Traps to design against: (1) a backdated change arriving after an invoice was issued must generate an adjustment line, not an edit to the issued invoice; (2) partial-period arithmetic needs a documented day-count convention, because two conventions give two different totals and finance and the customer will disagree; (3) downgrades create credits, so the model must allow negative lines rather than assuming amount > 0; (4) an item must never overlap another item for the same subscription and product, which needs a constraint, not vigilance.
Test the model by replaying a known contract with one mid-term tier change and one seat change and comparing to the archived invoice to the cent.
3. No price history exists today. What do you do about the past, given that auditors will ask about invoices already issued?
Read the worked answer
You cannot invent history, and an invented effective date is worse than a documented gap because it looks authoritative and will be used in a dispute.
Prioritise sources by evidentiary strength. The invoice archive or ERP is the strongest: it records what was actually billed, which is precisely what auditors ask you to reproduce. Load invoice lines as the authoritative historical record, linked to price versions reconstructed from them where the amounts are internally consistent. The CRM audit log is second, and can supply change timestamps where it has been enabled long enough. Signed quotes and order forms in the document store are third. Anything derived by assumption must carry a provenance flag and a source column, and be labelled inferred in the semantic layer so no report presents it as recorded fact.
Where nothing exists, record a known-limitation note with the date range affected, and do not fabricate effective dates. Agree with finance and audit in writing that reproducibility is guaranteed from the go-live date forward, and that prior periods are covered by the invoice archive.
Then prove the model rather than assert it: replay the billing calculation over the reconstructed history and compare against archived invoices for the last 24 months, targeting zero variance and investigating every exception. That reconciliation is also the acceptance test for the new schema, and it is the artefact you show the auditor.