The hardest number in ecommerce

Showing a merchant their revenue is easy. Every tool does it, because revenue is a single number that arrives in a single webhook. Showing a merchant their profit is a different problem entirely. Profit is what is left after cost of goods, ad spend across every platform you run, payment fees, shipping, handling, chargebacks, refunds that keep dribbling in for weeks, and tax you are only holding for the government. It is not one number. It is dozens of numbers from a dozen systems that never agree on a schedule, a currency, or a definition of "yesterday". And it has to be right to the cent, because an operator is going to make a real decision from it.

We have spent an unreasonable amount of time making that number correct, current, and fast at the same time. This is a note on why those three things fight each other, the approaches that fall apart at scale, the invariants we hold so a wrong number cannot ship, and the design we landed on. It has real code in it, because the details are what matter.

Why profit is a graph, not a sum#

The trouble is that a profit-and-loss statement is not a sum. It is a graph. Net profit depends on gross profit, which depends on net revenue and cost of goods, which depend on orders and refunds and per-variant costs, which depend on which of your connected sources owns a given order this week. Ad spend depends on every ad platform you run, each converting to your currency at a rate that itself depends on the day. Change any leaf and the root moves. You cannot cache the root and call it a day.

On top of that, the inputs keep changing after the fact. A Shopify order shows up the moment it is placed, but it is not final: an edit or a refund can restate it weeks or months later. Meta ad spend is worse, it keeps settling for weeks as the platform reconciles its own reporting. A refund can land against an order from two months ago. So the profit for "last Tuesday" is a live target that keeps moving for a while after Tuesday, and only stops once nothing new arrives for that day.

And here is the part that makes it genuinely stressful, rather than just annoying: a wrong profit number is worse than no profit number. If we show a merchant that a product loses money when it does not, they kill a winner. If we show a healthy margin that is actually underwater, they pour ad spend into a hole. "Roughly right" is a luxury a dashboard can afford and a P&L cannot. So every shortcut below is measured against one bar: it can be slower than we would like, it can even say "I do not know yet", but it can never be confidently wrong.

The obvious way, and why it falls over#

The first thing anyone tries is to compute profit on read. A merchant opens the dashboard, you scan their orders for the range, join the costs, subtract the spend, and hand back a number. It is always correct, because you are looking at the raw truth every time. For a store with ten thousand orders it is also instant.

Then the store grows. At a few million orders, an all-time view or a casual switch of the calendar becomes a scan of the entire history on every click. At thirty million orders it is not a feature anymore, it is an outage. The correctness was never the problem. The problem is that "read the whole world" is not a plan, it is the absence of one.

Precomputing everything, and why that is worse#

So you swing to the other extreme: precompute. Roll the whole P&L up every night, store the totals, serve those. Now every read is a lookup and the dashboard is fast at any size. You will feel very clever for about a day.

Because now the number is stale, and stale is its own kind of wrong. A merchant during Black Friday does not want last night's profit, they want the profit as of a minute ago, while it still means something. Worse, precomputing freezes the settling inputs at the moment you ran the job. Meta rewrites the last few weeks of spend after your rollup ran, and your cached total is now confidently out of date until tomorrow. "Fast" that lies is not a win. It is the compute-on-read problem again, only harder to notice.

Never invent a number#

Before the storage design, the rule, because the storage design only matters if the inputs are honest. We keep a money doctrine in the repo. It is not a style guide, it is enforced by linters and tests and it fails the build. The first rule is not negotiable:

A money value is authoritative and validated, or it is an error.
There is no third state, and a fallback chain may only end in Err,
never in a guess.

The first half of the doctrine is mechanical: money is a decimal string from the moment an API hands it to us, through Rust as an exact BigDecimal, into ClickHouse as a Decimal(18, 6) column per order, widened to Decimal(38, 6) for the daily sums so a huge shop-day cannot overflow, and back out. A floating-point number never touches an amount. Not once. If you have ever watched 0.1 + 0.2 come back as 0.30000000000000004, you know why: a tenth of a cent of drift, multiplied across a million orders, is a number an accountant will not sign.

The second half is about absence, and it is the part that separates a number an accountant will sign from one they will not. There is a world of difference between "this cost is genuinely zero" and "we do not have this cost yet", and collapsing the two is how tools quietly lie. A missing currency is not silently upgraded to USD. A missing timezone does not become UTC, because UTC would shift which calendar day counts as "today". It all rolls up into one ranked rule we design against:

1. Wrong and silent:    never acceptable
2. Absent and honest:   always acceptable
3. Right:               the goal

When zero is a lie#

The most dangerous input we handle is an innocent-looking one: an ad platform returns an empty response for yesterday's spend. Two things could be true. The campaigns genuinely spent nothing, or you have lost API access, or the platform is halfway through restating its own numbers. From the outside they are the same empty array. Treat it as "zero spend" and write it, and if it was really access loss, you have deleted real ad cost from the merchant's profit, and the next reconcile makes it permanent. This is the trap that silently wrecks profit tools, and it hides in every integration that fetches money. It is the kind of bug that sails through code review, because the code looks completely reasonable.

So no integration is trusted to answer it alone. Every ad engine routes through one shared gate before it is ever allowed to overwrite a day with zero, and the gate refuses unless the response is both real money and provably complete:

/// An empty success is indistinguishable from access loss, and a
/// partial success cannot tell a restated-away campaign-day from
/// one it simply did not fetch. So only a response that both
/// carries money and is provably complete may zero history.
pub fn may_zero_history(outcome: FetchOutcome) -> bool {
    outcome.carries_money && outcome.complete
}

And because a rule is only as strong as its coverage, a build-breaking test reads every ad engine's source and fails if any channel that can zero history is not wired through the gate. There is no way to add an integration that gets this wrong, because the build will not let one exist. That is the pattern for the whole system: each way we find that a money number can go wrong becomes an invariant plus a test, so that failure mode stops being a bug we hope to catch and becomes a state the build refuses to ship. We have not found all of them and do not pretend to, but the ones we have found cannot come back.

The live edge and the settled past#

Now the storage design. This is the part of the system I am most fond of, because it feels a little like cheating. It comes out of one property of profit: most of the past is quiet. A day from two years ago almost never changes, and when it does, it is because a single late refund or restatement landed against it, not because all of its orders moved. So instead of re-reading millions of raw orders for a date range, we keep one pre-summed row per day in a rollup, and we recompute only the specific days that late data actually touches. A day is never formally closed; it just stops changing once nothing new arrives for it. Only the last couple of days, the live edge, are read straight from the raw facts, because that is where almost all the movement is.

So we read hybrid. Closed days come out of the daily rollup tables, one row per day per stream. The live edge, today plus the shop-timezone tomorrow, comes straight from the raw facts. Both halves compute the same revenue, the rollup just has the settled sums done in advance. The revenue read looks like this:

SELECT sum(revenue) AS revenue
FROM (
    -- closed days: one row per day, from the daily rollup
    SELECT current_total + refunded_total + refund_discrepancy
             + returned_unrefunded
             - tips - current_duties - current_additional_fees
             - gift_card_sales AS revenue
    FROM pluto.shopify_orders_daily FINAL
    WHERE store_id = {store:String}
      AND event_date >= today() - 29
      AND event_date <  today() - 1
    UNION ALL
    -- live edge: today + shop-tz tomorrow, from raw facts
    SELECT sum(current_total + refunded_total + refund_discrepancy
             + greatest(-total_outstanding, 0)
             - tips - current_duties - current_additional_fees
             - gift_card_sales) AS revenue
    FROM pluto.shopify_orders FINAL
    WHERE store_id = {store:String}
      AND event_date >= today() - 1
      AND is_deleted = 0 AND is_test = 0
      AND financial_status NOT IN ('VOIDED', 'EXPIRED')
)

A couple of details in there are doing the real work. Both tables are ReplacingMergeTree read with FINAL, so a re-fetched order or a recomputed day collapses to exactly one row, the newest. FINAL is not free, it merges on read, so the trick is to never point it at the whole history: raw FINAL only ever touches the day or two of the live edge, and the settled past comes pre-collapsed out of the rollup. The raw facts are partitioned by month and the rollup by year, so even that bounded read only touches the partitions it needs instead of walking all of history. And event_date is the shop-timezone calendar day, written once at ingest and never re-derived, which is why the live-edge boundary can be trusted.

What this buys us is that rollup timing can never make the dashboard stale for today. People ask how often the rollups run, half expecting that to be the freshness ceiling. It is not. Today is always computed from raw facts, independent of any rollup, so a delayed or even a failed rollup can never make today wrong; the worst it can do is briefly hold back a correction to a past day, which the next run applies. And if the rollup tables are not primed at all, the read falls back to raw for the whole range. Raw is the source of truth the rollup is built from, so falling back to it costs speed, never correctness relative to what we have ingested. The rollup is a cache over the past, never a source of truth the present depends on.

Recomputing exactly the day that changed rests on one assumption: that every change announces itself. So we do not bet the numbers on it. A reconciliation pass re-derives the recent past straight from the raw facts on a schedule, with the same query the live read uses, and overwrites the rollup. A closed day that ever drifted from its raw truth, for any reason, heals itself on the next pass instead of staying quietly wrong. The fast path assumes the happy case; the slow path guarantees the number.

One computation path#

A profit engine ends up with several surfaces. There is the dashboard a merchant looks at, a public REST API their data team pulls, and an MCP server an AI agent queries to answer "how did last week go". The lazy way to build those is to let each one compute its own totals, and it is also how you end up with three subtly different definitions of net profit and a support queue full of "why does the API disagree with the dashboard".

We refuse to have a second profit formula. Every surface reads the same assembler through one internally-signed bridge: the public endpoints call the exact capability the dashboard renders from, over an HMAC-signed path, then reshape the response and pass the decimal strings through untouched. The signature covers the bare path and the store, and the store comes only from the authenticated key, never from a request parameter, so a tool argument can never point at another tenant's numbers.

That it stays true is not a matter of discipline, it is gated in CI. These assertions have to stay green:

facit_public_metrics_equals_dashboard_totals_to_the_cent
facit_public_metrics_bridges_to_the_dashboard_capability_path
facit_projection_reshapes_and_never_computes   // fails on any arithmetic
facit_metrics_module_has_no_profit_sql_of_its_own

The last two are the interesting ones. The public projection is allowed to rename and reshape the dashboard's response, but a test scans that file's source and fails the build if it finds so much as an arithmetic operator, because the moment the API does its own math it can drift. It is a blunt pin on top of the real guarantee, the to-the-cent equality test above, so there is nothing to keep in sync because there is only one thing that computes profit.

Foot it, or refuse#

The doctrine has one rule we lean on constantly: when a number is composed of parts, the parts have to sum to the whole, exactly, or we do not ship the breakdown. A statement whose lines do not add up to net profit is not a rounding curiosity, it is a bug.

The clearest example is the VAT-by-country split in our accountant export. Breaking net tax out by the country goods shipped to is useful for an EU merchant filing OSS returns, and it is also a place you could very easily be subtly wrong: a foreign currency here, a non-Shopify source without country data there. So we do not trust ourselves. We compute the per-country split and then check it against the tax total the dashboard already reports, to the cent, before we are willing to show it:

// the per-country nets must equal the dashboard's net tax to the
// cent, or we withhold the whole split: no partial, no drift.
let sum: BigDecimal = nets.values().fold(zero(), |acc, v| acc + v);
let want = parse(tax_net)?;
if round2(&sum) != round2(&want) {
    return Ok(VatSplit::unavailable(
        "tax spans multiple currencies or non-Shopify sources; \
         a country split is FX/source-dependent",
    ));
}

If it foots, you get the breakdown. If anything makes it not foot, you get an honest "unavailable" with the reason, never a partial or a drifted number. Both branches have tests. We would rather say "I do not know" than hand over a number we cannot stand behind.

The same care applies to data that has not arrived yet. Ad platforms report spend with a lag, so if a channel is a day behind, the most recent days are missing exactly that spend and a naive profit reads too high. The profit response carries which channels are behind, and the assistant says so before it draws any conclusion, so a number built on spend that has not landed is flagged as provisional instead of handed over as final.

At thirty million orders#

Two databases, two jobs. Postgres is the source of truth for the things created and updated one at a time: shops, connections, settings, costs, sync state. ClickHouse holds the history the reports scan: orders, ad performance, the daily profit facts. Rust sits between them and handles the volume with exact decimals in hand the whole way.

We measure the whole system against two deliberately extreme stores on every change. One holds three million orders over three years. The other holds thirty million orders, 60,003,878 order lines, 13.7 million customers, and a decade of history across 120 monthly partitions with a Black Friday spike. A harness fires 62 HMAC-signed endpoints, three cold and three warm each, subtracts the measured network floor, and fails the run if any warm p50 crosses a two-second budget.

Thirty million rows is not, honestly, a lot for ClickHouse. It is built for far more, and if the claim were that we can store the rows there would be nothing here worth writing down. The size is in the test for one reason, to show that the cost of a read does not grow with it: what would sink a naive design is the scan, not the storage, and the whole point of the rollup is that we never do the scan.

On the latest run the thirty-million-order store answers 59 of 62 endpoints inside that budget with zero errors: the 366-day dashboard read lands around 1.5 seconds, the 366-day products P&L around 1.2. Windowed reads stay flat from three million orders to thirty because the query prunes to the partitions it needs and scans one row per day, not per order. And every one of those numbers is checked against eighteen money identities that have to come back at zero violations before we trust it.

The one honestly hard aggregate is distinct customers, because you cannot pre-sum it the way you sum revenue. The tempting answer is a probabilistic sketch like HyperLogLog, which is fast and approximately right. We do not use it, because a merchant checks their unique-customer count against Shopify and "approximately" fails that check. We store an exact aggregate state instead:

-- uniqExact (not HLL): merchants verify the count.
customers_state  AggregateFunction(uniqExact, String)

The per-day states merge with a live-edge state exactly the way the revenue read does, so the unique-customer number stays correct and quick: on the thirty-million-order store the pre-merged states answer all-time in around 2.0 seconds, against roughly 3.5 for scanning the raw states directly. Both are exact and return the same count; pre-merging just makes it fast. None of this is exotic. It is a columnar store, partition pruning, exact aggregate states, and a refusal to ever recompute what has not changed. Boring, and exactly what you want under a number someone files taxes against.

The number has to be right#

You can feel the temptation, building this, to relax one of the three constraints. Let the number be a little stale and precomputing gets easy. Let it drift a fraction and float math gets easy. Let each surface do its own thing and shipping gets easy. Every one of those trades away the only property that matters: that an operator can look at their profit and act on it without checking it against a spreadsheet first.

So we did not relax any of them. The number is live at the edge and cheap over history, exact from the API to the report, and identical on every surface that shows it. Every safeguard in here turns a subtle way to be wrong into an invariant the build enforces. Profit is a graph, not a sum, and the whole job is making that graph tell the truth fast enough to act on.