TL;DR — If a finance report computes its figures at read time from the current configuration, every change to that configuration silently rewrites every past month, including months you have already paid out against. Don’t freeze the totals. Freeze each order: one row per order holding every figure that has to agree with what was paid, computed once with the rule that applied when the order was placed. Rate rules go in an insert-only table keyed by an effective date, so a new rule never changes an old order. Reports SUM those rows. On a food-ordering platform this replaced three figures that were derived on every dashboard load. The production backfill wrote 115,920 rows with 0 errors, and a 5,000-order sample matched the old computation exactly.

Key takeaways

  • A figure derived at read time is only correct for as long as everything it reads stays the same. Rules, rates and settings all change, so a derived figure for last month changes with them.
  • Freeze per order, not per month. Monthly totals are only ever a GROUP BY on per-order rows, and per-order rows let you fix one order without reopening a month.
  • Make the rules table refuse UPDATE and DELETE in the database. An effective date is only worth something if nobody can edit an old row.
  • “Frozen” means frozen against configuration, not against facts. A refund after the fact still has to land somewhere, and you have to choose where.
  • Prove parity against the old computation before you switch reads over. Then compare inside one snapshot, or orders that arrive mid-check will produce false diffs.

The finance question that started it

The platform runs canteens and food outlets for corporate campuses. Each outlet gets a monthly statement: gross sales, the platform’s commission, GST, the payment-gateway fee and who bears it, and the net amount paid to the vendor. Those statements go to vendors and to their accountants.

The dashboard behind them computed some of those numbers on every page load. That worked until a configuration change landed. On 17 August 2026 a commission backfill rewrote the vendor’s share on orders that had already been paid out. Nothing errored, and nothing flagged that a paid-out month now said something new. The owner’s question afterwards was the obvious one: if a setting changes in December, does September change too?

Under that design, yes. The migration header I later wrote put it in one line: anything derived at read time “moves when the derivation or a rate changes, so a month already sent to a vendor or CA could silently show different numbers.”

Which figures drifted and which were already safe

Before designing anything I listed every figure on the statement and where it came from. Most were fine. The checkout code stored the price breakdown on the transaction when the order was placed, and a stored number does not move when a setting does. Three were not:

FigureWhere it came fromDrifts when config changes?
Item total, commission, convenience feeStored on the transaction at checkoutNo
GST the platform remits for the outletRead per order from a stored amount (resolved 100% of 98.7k Jun–Sep orders)No
Gateway fee on UPI-QR ordersRecomputed from the current fee rule, because the gateway’s transfer amount was never recorded for themYes
GST on packaging and deliveryDerived from current rulesYes
Outlet-funded discount and roundingDerived as a balancing figureYes

Two things here are worth stating plainly. First, the drift was narrower than “the dashboard is wrong”. Orders paid through gateway routing already had a recorded fee, and only UPI-QR payments fell back to recomputing it. Second, the rules being recomputed were not even stable over the period: the gateway-fee rule changed in the middle of it. The fee arithmetic itself is in the gross-up post, so I won’t derive it again here. This post is about when that arithmetic runs.

The design: frozen rows, insert-only rules, sums at read time

  order placed / status changes / refund
            │
            ▼
  ┌──────────────────────────┐      ┌──────────────────────────────┐
  │ settlement_rules          │      │ order_settlements             │
  │ (insert-only)             │─────▶│ one row per order             │
  │ effective_from → policy   │ rule │ every figure + settings used  │
  └──────────────────────────┘ at   │ upsert only IS DISTINCT FROM  │
                               order └──────────────┬───────────────┘
                               time                 │ BEFORE UPDATE
                                                    ▼
                                    ┌──────────────────────────────┐
                                    │ settlement_history            │
                                    │ previous row as JSON + reason │
                                    └──────────────────────────────┘
            reports: SELECT outlet, month, SUM(...) FROM order_settlements

  ▼  last month changes only when one of last month's orders changes

There are three pieces, and each one closes a different way the past could move.

Rules are insert-only and carry an effective date

The rule that decides who bears the gateway fee lives in a table with one row per policy and the moment it took effect. To find the rule for an order you take the latest row whose effective_from is at or before the order’s creation time. This is Martin Fowler’s Effectivity pattern, and the only part that takes care is the enforcement:

-- Illustrative.
CREATE TABLE settlement_rules (
  effective_from   timestamptz PRIMARY KEY,
  vendor_bears_fee boolean NOT NULL,
  note             text NOT NULL
);

CREATE FUNCTION refuse_rule_edits() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'settlement_rules is insert-only: add a row with a later effective_from';
END $$;

CREATE TRIGGER settlement_rules_insert_only
  BEFORE UPDATE OR DELETE ON settlement_rules
  FOR EACH ROW EXECUTE FUNCTION refuse_rule_edits();

-- The rule for one order:
SELECT * FROM settlement_rules
WHERE effective_from <= $1          -- the order's created_at
ORDER BY effective_from DESC
LIMIT 1;

The trigger is the important line. A comment saying “don’t edit old rules” would last until the first time someone needs an old rule fixed quickly. With the trigger, fixing a rule is one specific act, inserting a new row with a later date, and it is the only one the database allows.

There is no exclusion constraint over date ranges, on purpose. A rule has only a start, and the next rule’s start is its end, so there is no range that could overlap. A unique key on effective_from is enough.

This was not hypothetical. The fee policy really did flip on 18 August 2026. Before that, the outlet bore the gateway fee on the food portion only when it paid no commission and no convenience fee. From that commit on, the vendor bears all gateway charges in every case. Both rules went into the table as rows. When I checked them against about 33,000 real gateway transfers from June to September, the new rule matched essentially all of them and the old one about 96%. The remaining ~4% of older transfers fit no rule at all. I’m not going to invent a third rule to cover those. They are recorded as exceptions, and the stored amount is what counts for them.

One frozen row per order

Each order gets one settlement row: 51 figures, 4 bookkeeping columns, and a 4-column snapshot of the outlet’s settings at the time (commission percent, convenience-fee percent, TDS percent, and whether the platform collects GST for the outlet). The snapshot is for audit only. No amount is computed from it. It exists so that when someone asks in six months why an order had a 5% commission, the answer is on the row and not in a settings table that has changed three times since.

A subsidy engine I wrote about earlier does the same thing for program parameters on its ledger rows. It’s the same idea applied to a different table.

The write is an upsert that only touches the row when something actually differs:

-- Illustrative.
INSERT INTO order_settlements (order_id, gross, gateway_fee, vendor_net /* … */)
SELECT * FROM compute_settlement($1)
ON CONFLICT (order_id) DO UPDATE
  SET gross = EXCLUDED.gross, gateway_fee = EXCLUDED.gateway_fee /* … */
  WHERE (order_settlements.gross, order_settlements.gateway_fee /* … 50 columns */)
        IS DISTINCT FROM
        (EXCLUDED.gross, EXCLUDED.gateway_fee /* … */);

IS DISTINCT FROM treats two NULLs as equal, which <> does not, so a NULL-to-NULL “change” doesn’t produce a write. This makes recomputing safe to run as often as you like: an order whose figures haven’t changed produces no write and no history row. After the incident below, a recompute over 3,000 orders changed zero rows.

Every overwrite leaves the old version behind

A BEFORE UPDATE trigger on the settlement table copies the previous row, as JSON, into a history table along with a reason. So the settlement row is current and the history table holds every earlier version. “What did this order’s row say on the 5th?” is a query, not an argument.

What “frozen” doesn’t mean: late cancels

Here is the honest limit of the design. A refund processed on 3 October for a September order still has to show up somewhere.

There are two answers. The first is the accountant’s: leave September closed and post an adjustment into October. The second rewrites the order’s own row, so September changes, and the history table holds the version September showed before. The owner chose the second. “A late cancel or refund changes the order’s own month” was the decision on 22 September, with no prior-period adjustments.

That’s a defensible choice for a platform where most refunds arrive within days. It also means “frozen” in this post is precise: frozen against configuration and code changes. A change to the order itself, which is a fact, still moves its month. The piece that would make a month truly closed is a period lock per outlet per month. That was decided and has not been built, and I come back to it at the end.

Separate table or ~50 more columns on orders

The obvious alternative was to add the figures as columns on the orders table. I didn’t, and here is the reasoning, as a table so it’s easy to disagree with:

QuestionColumns on ordersSeparate order_settlements
Who writes it?Every path that writes an order, so every one of them has to know about settlementOne function, called by triggers
Can it be recomputed and kept?Recomputing overwrites the order row; history needs a trigger on the busiest tableIts own history trigger, on a table nobody else writes
PermissionsWhoever can read orders can read the marginsIts own, narrower access rules
Width of the hottest tableGrows by ~50 columnsUnchanged
CostNothing new to joinOne join on the reporting side, by primary key

The one real cost is the join, and reporting reads are where a join is cheapest to pay.

Trigger or application code

The settlement row has to exist for every order, whichever path created it. On this platform that means two API versions, the point-of-sale app, a third-party POS integration and SQL scripts run by hand. Putting the call in application code means remembering it in every one of those paths and in every path added later. A trigger on orders, transactions and refunds covers them all with no remembering. That was the decision, made on 22 September, and I’d make it again.

It had a cost, and the cost was paid at lunch on the first day. The trigger’s upsert was being re-planned on every call, and status updates went from 3 ms to about 400 ms. That story, along with the one-line fix, is in the post about the trigger being 100× slower in production. The short version: a trigger turns its cost into a tax on every write, so benchmark the writes that fire it.

Backfill, then prove parity before switching reads

Switching the dashboard from “derive at read time” to “sum the frozen rows” is only safe if the two agree for history. Otherwise the switch itself is the configuration change that rewrites the past.

The order of operations was:

  1. Backfill on a scratch copy. 107,706 orders in about 5 seconds.
  2. Parity on the scratch copy. I put 62 scenarios through both versions of the dashboard function. All 62 returned identical JSON. Underneath that, about 157,000 row-level consistency checks across 108,000 orders all passed.
  3. Backfill on production on 22 September: 115,920 rows, 0 missing, 0 errors, and a random sample of 5,000 orders identical to the old computation.
  4. Parity on production. 27 of 27 comparisons identical. A separate pass of 74,470 independent checks over 1,354 live rows also passed.

Step 4 failed the first time, and the failure is the useful lesson in this section. My first production comparison ran the old function and the new one as two separate queries, and it reported two differences. Neither was a bug. Orders had arrived in the gap between the two queries, so the two sides were summing different sets of orders. Comparing both inside one snapshot, a single transaction at REPEATABLE READ, made the difference go away. If you run parity checks against a live database, compare in one snapshot or expect false alarms.

What the frozen rows exposed

Once the figures were rows rather than a calculation, they could be read one order at a time, and a misconfiguration came out of them almost straight away.

One corporate client pays for its employees’ meals through payroll deduction: the employer pays the outlet directly and recovers the cost from salaries. Those orders carried the wrong settlement mode. The statement showed the employer as having paid ₹0 and the platform owing the outlet the full net amount, which the platform did not owe.

The fix re-stamped the right mode onto about 5,100 orders in production. Because every overwrite goes through the history trigger, each corrected row kept its previous version. I rehearsed it on a scratch copy first, and there the only columns that changed were the payer columns. No amount moved, and no other client’s rows were touched.

A total that comes out of a calculation looks authoritative, and it is easy to accept. A row with payer = platform next to a client everyone knows pays its outlets directly is much harder to miss.

What actually broke in production

  • The trigger’s cost, on day one. Covered above and in its own post. Status updates hit ~400 ms for most of an afternoon, until the plan-cache fix.
  • My pre-ship benchmark. I measured 0.75 ms per order on inserts in a warm local loop. The path that mattered was status updates, at 10.5 ms each locally before the fix.
  • The false parity diff. Two separate queries against a live database, summing two different sets of orders.
  • The rule that fits ~96%. The old fee policy explains most older transfers and not all of them. I’d rather say that plainly than claim the table reconstructs history perfectly. It doesn’t. It reconstructs it as well as the old data allows, and stored amounts win wherever they exist.

FAQ

Why do my past financial reports change when I change a setting? Because the report computes its figures when you read it, from the current settings. Any rate, rule or setting that the calculation reads applies to every past order as well as new ones. The fix is to store the computed figures per transaction at the time they happen, and have reports sum the stored figures instead of recomputing them.

Should I snapshot totals or individual transactions? Individual transactions. A monthly total is a GROUP BY over per-transaction rows, so you lose nothing by storing rows, and you gain the ability to correct one order, explain one order, and re-aggregate by any dimension later. A frozen monthly total can’t be corrected without reopening the whole month, and it can’t tell you which order is wrong.

How do I change a fee or tax rule without changing past orders? Store rules in a table keyed by an effective date and never update a row. To change the rule, insert a new row with a later date. Each order uses the latest rule at or before its own timestamp. Enforce the insert-only part in the database with a trigger that raises on UPDATE and DELETE, not with a convention.

How do refunds and late cancellations work if rows are frozen? You choose. Either the refund rewrites the order’s own row, so its original month changes and a history table keeps the old version, or the original month stays closed and the refund posts an adjustment into the current month. The first is simpler. The second is what accountants expect once a month has been reported, and it needs a period lock to enforce.

Should a settlement row be written by a trigger or by application code? If many code paths create or change orders, use a trigger. It covers every path, including scripts and integrations you forget about. Then benchmark the writes that fire it under production settings, because a trigger’s cost lands on every one of those writes.

How do I prove a backfill matches the old calculation? Run both versions over the same data and compare results exactly: whole report outputs for a set of scenarios, plus row-level checks for every order. Do it on a copy first, then on production inside one transaction at REPEATABLE READ, so new orders arriving mid-check can’t produce false differences.

Why use IS DISTINCT FROM in the upsert? It makes the recompute idempotent in effect: if no value changed, the row isn’t written, no history row is created and no lock is held longer than needed. Unlike <>, it treats two NULLs as equal, so nullable columns don’t generate phantom updates.

What I’d still improve

  • A period lock per outlet per month. This is what turns “a month only changes when its orders change” into “a month that has been reported cannot change”. It was decided and hasn’t been built.
  • Adjustments for anything after the lock. Once months can be locked, late refunds need to post forward as adjustment rows rather than rewrite the original month.
  • Record the gateway’s transfer amount for every payment mode. The UPI-QR gap is the reason a fee had to be recomputed at all. A number you are given should be stored, not re-derived.
  • A payment-tracking ledger, so the statement can show what was actually paid alongside what is owed. That’s deferred for now.

The one idea to take away

A number you have already reported is a fact about the past. Store it. Don’t recompute it.

Every recomputation is a quiet claim that the inputs haven’t changed, and for financial figures that claim is false the first time anyone edits a rate. Storing one row per order with the rule that applied costs a table, a trigger and a backfill. Not storing it costs a statement that disagrees with the payout someone already received, and no record of why.


I write about backend reliability, Postgres and payment correctness from work on food-tech and healthcare platforms. More on what I build and how I work, on reconciling two systems that both change the same data, and on the trigger behind this ledger. If your finance dashboard derives anything at read time, change one rate on a staging copy and diff last month.