Three screens, three revenue numbers, all correct

The problem: three screens, three revenue numbers

A studio owner opens their dashboard and sees a revenue tile. They open the sales report for the same period and see a different figure. They ask the in-product assistant and get a third.

All three are correct. That is what makes it a serious problem.

The dashboard tile summed completed payment transactions by capture date and cached the result for two hours. The sales report summed order totals by purchase date. The assistant used the same query as the tile. Each had made a defensible decision about four things — whether refunds are netted or excluded, whether tax counts as revenue, whether a sale belongs to the day it was captured or the day it was initiated, and how fresh the number needs to be — and no two had made the same decisions.

This is the predictable end state of a reporting system that grew one report at a time. Nineteen report endpoints had accreted in this platform since April 2021, each a bespoke command joining live operational tables at query time: payments, orders, line items, package instances, profiles, reservations. When a feature shipped and someone needed a number about it, a report was added next to that feature. The routes file still shows the seams, with comment headers grouping reports by the product area that prompted them.

Every one of those reports encoded its own answer to the four questions above, in a hand-written join. There was no shared definition of revenue to disagree with, because there was no shared definition at all.

So the fix was not a faster query or a better chart library. It was to build the thing the reports had been improvising — one record of what money moved, when, and against what — and then to migrate five years of live reporting onto it without ever being wrong in front of a customer.

This is the fifth write-up from this platform, after the workflow automation engine, the AI notification templates, the agentic assistant and the in-app CMS. It is the least glamorous of the five and the one the other four depend on, because every one of them either moves money or reports on it.

Act one: the ledger

One row per thing that happened

The revenue ledger is an append-only fact table. One row is one entry type, for one payment transaction, for one order line item. A sale of three items on a single payment writes three rows. Refunding one of them appends a fourth row rather than touching the first three.

There are exactly three entry types — sale, refund and gift-card redemption — and the absence of a fourth is the design. There is no void, no adjust, no correct. Every correction is expressed as a new entry of an existing type, because the alternative is editing history.

The reporting dimensions are denormalised onto the row itself: location, staff member, client, product and product type, payment method, card brand, and the channel the sale came through. This is a star-schema fact table living inside a transactional PostgreSQL database, and it is why a question like “sales by location by month” is now a single-table aggregate rather than a five-table join. Composite indexes carry the shape (business_account_id, <dimension>, event_at), which is not a coincidence — it is every report the product offers, expressed as an index.

The most important column is the one that is easy to miss

Two timestamps, deliberately. event_at records when the money moved. created_at records when the row was written. They are different columns because they answer different questions, and every report filters and buckets on event_at.

Without that separation, everything in the next section is impossible: replaying a two-year-old payment would file its revenue under the day of the replay, and a rebuilt ledger would be worthless. Worth crediting where this came from — the old dashboard metric already carried a comment explaining why it used capture time rather than creation time, because recurring payments would otherwise all land in their first month. The insight existed before the ledger. The ledger generalised it into schema.

Immutable, enforced twice

The model raises on update and on destroy. That is the polite version. The real one is a PostgreSQL trigger, because a guarantee that lives only in application code is a convention rather than a guarantee — it does not survive a console session, a raw UPDATE, or a future developer with a deadline.

RAISE EXCEPTION 'revenue_ledger_entries is append-only:
                 UPDATE and DELETE are prohibited';

The best three minutes in the project’s history

Three migrations, all dated 8 March 2026, timestamped one second apart. One working session.

The first creates the table with the trigger above, raising on both statements. The second adds a second unique index, for reasons in the deep dive. The third is named AllowDeleteOnRevenueLedgerEntries and relaxes the trigger to block UPDATE only.

Read that sequence again, because it is the whole architecture in three files. They built the table as immutably as PostgreSQL allows, and within hours hit the contradiction: a table you may never clear is a table you can never rebuild. If the derivation logic is ever wrong, or a new dimension is ever needed, absolute immutability makes the error permanent.

So the position they landed on is more precise than “immutable”, and better:

UPDATE is prohibited forever, at the database, for everyone. DELETE is permitted, so that the entire table can be discarded and re-derived from the source records.

No row is ever edited. The whole table is disposable. Those are compatible, and together they are stronger than immutability alone — the ledger is not a precious artefact to be protected, it is a projection that can always be regenerated from the operational records that produced it.

The migration is reversible, and its down restores the stricter trigger, so both intents stay in the file. The rebuild task’s header comment states the reasoning out loud, including that a bulk delete bypasses the model callback and that the trigger deliberately does not cover DELETE. The escape hatch is documented, not accidental.

UNIQUE (user_transaction_id, order_line_item_id, entry_type)
UNIQUE (user_transaction_id, entry_type) WHERE order_line_item_id IS NULL

sorted = raw.sort_by { |r| -(r[:raw] - r[:floored]) }
sorted.each do |r|
  break if remainder <= 0
  r[:floored] += BigDecimal("0.01")
  remainder   -= BigDecimal("0.01")
end

Act two: migrating five years of reporting without a cutover

A new table is the easy half. The hard half is moving nineteen live reports onto it in a system where being wrong once costs more trust than being late by a quarter.

The obvious approach is to build a new reporting stack on the new table and cut over. That was not the approach.

Because the API contract never moved, the comparison was apples to apples: the same request, two derivations, one account moved forward at a time, and the old path one boolean away for as long as it took to trust the new one. No customer was the test subject for an architecture change they had not asked for.

Step 4 deserves its own note. Three reports completed the full cycle — built beside, bridged behind a flag, rebuilt on a new surface, then deleted along with their own migration apparatus. Scaffolding that is not removed becomes architecture by default, and the commit that removed it names the bridge services explicitly.

The shim, and why it is not a redirect

V2’s URLs were reorganised mid-build, from flat actions to per-family nesting. Twenty-six aliases keep the old ones working, generated in a loop, with a comment that explains itself: these are real route aliases and not 301 redirects, because API clients do not all follow redirects and a 30x reads as an error in most JSON consumers. Each legacy URL invokes the same controller action as its nested counterpart.

The comment also carries its own removal condition — delete this block once all known consumers have migrated — and a rule stopping it becoming a dumping ground: do not add new endpoints here. Small thing, but this is what maintainable looks like in practice.

What the reporting product actually does

V1 was one endpoint per report, returning chart and table and export together. V2 decomposes a report into the widgets it is made of, each fetched independently, and the endpoint names are the product design.

Those raw-row endpoints return the same orders and transactions that the console’s search box indexes — reporting aggregates them, search finds them one at a time, and both are downstream of what the payment path wrote.

The practical consequence is that a slow raw-transactions query no longer delays the KPI cards above it — and an export of a widget cannot disagree with the widget, because both are rendered from the same computed payload. The V1 exporters iterated the database independently, which is exactly how an export comes to contradict the screen it came from.

Charts are bucketed in SQL, in the tenant’s timezone. The frequency presets map onto hourly, daily, weekly and monthly grouping, each passed the tenant’s zone; month-on-month comparison buckets three calendar years at the database. Comparison periods are first class — previous equal-length window and year-on-year — with percentage change returning nothing rather than infinity when the prior period is zero.

Timezones are the quiet correctness win. V1 parsed report dates with a bare conversion and no tenant timezone anywhere. V2 resolves the account’s configured zone and threads it through every bucketing call. In a multi-tenant reporting product this is not a detail; it decides which day a 9pm sale belongs to, and therefore whether a month closes correctly.

Pagination totals are computed across the whole result set, not the current page — the grouped count runs first and the page is fetched separately. A small thing that is wrong in a lot of reporting code.

“Insights” means comparison mathematics and ranking, and it is worth being precise about that. There are KPI cards with previous-period and year-on-year deltas, top and bottom leaderboards, cohort retention and no-return analysis, an end-of-month revenue pace projection, and membership conversion ratios. There is no forecasting, no anomaly detection and no model-written narrative in the reports UI. Anyone claiming otherwise about a system like this is describing a roadmap.

How fresh the numbers are, stated precisely

Reports read live, uncached queries. There are no materialised views, no nightly rollup tables, no scheduled aggregation jobs and no cache in front of the report services. Ask for this month’s sales and the database aggregates this month’s ledger rows, now.

One qualifier, and it should be stated rather than glossed: ledger entries are written by a background worker enqueued after the payment commits, not inside the payment’s own transaction. So the ledger converges on the payment records rather than moving in lockstep with them. Between a sale completing and its worker finishing, that sale is not yet in a report — normally sub-second, and as long as the queue under backlog.

That was the right trade. Deriving entries inline would put proportional allocation, rounding and a validation pass inside the payment transaction, where a bug declines a customer’s card instead of delaying a number on a dashboard. A daily reconciliation job on a dedicated queue re-checks for payment transactions that never produced entries, and any of the backfill tasks can fill a gap without touching the rest of the table.

So: not event sourcing, and not eventually-consistent-by-the-hour. Live queries over a table that trails payments by queue latency, with a scheduled check that the trailing ever finishes.

Results

Query latency, ledger row counts and rebuild duration on production data are the client’s to publish rather than ours to estimate. What is structural, and does not need a figure attached, is that the platform now has one place where the question “what counts as revenue” is answered — and a table that can be thrown away and rebuilt when the answer needs to improve.

What we would do next

Finish the definition. The V2 financial families read the ledger. The dashboard metric tiles and the older business-analytics endpoint still compute revenue their own way — one from completed payment transactions with a two-hour cache, one from order totals — and the in-product assistant uses the former. The ledger exists precisely to be the one answer, and until those surfaces move, the platform has one canonical definition and two legacy ones still on screen. This is the highest-value remaining work and it is mostly deletion.

Make the reconciliation job say something useful. It runs daily on its own queue and correctly anti-joins payments against entries, but when it finds a discrepancy it emits a static log line — no count, no ids, no tenant, no metric. The good pattern already exists twenty lines away in the write-time validator, which reports the transaction id and the exact delta. The daily check should do the same, and it should compare amounts rather than only presence, because today a derivation bug that produces an entry with the wrong value passes reconciliation silently.

Get the append-only trigger into CI. Rails does not dump triggers to its Ruby schema file, and this project does not use the SQL schema format, so databases created for tests have the table without the trigger. The strongest guarantee in the design is the one the test suite cannot currently verify.

Move exports to background generation. They are synchronous, written to a temporary file and streamed back inside the request, with row caps standing in for streaming. A large export on a large tenant is a request holding a worker. Generating in a job and delivering a signed link is well-understood work and would remove the caps entirely.

Retire the rest of V1, or decide not to. Fifteen reports still run on the old architecture with naive date handling, which means they can disagree with V2 about which day a sale belongs to. Either they earn a V2 equivalent or they should be explicitly declared legacy. The current middle state is the one that generates support tickets, and it is the honest reason we would not call this migration finished.

If this sounds like your system

The shape recurs in any product old enough to be useful. Reports accreted next to the features that prompted them, each with its own definition of the number everyone quotes; a dashboard and a report and an export that do not agree; and nobody willing to touch it, because the one thing worse than a slow report is a wrong one.

The transferable decisions are these. Fix the semantics before the performance, because indexes make a disputed number arrive faster. Separate when-it-happened from when-you-recorded-it, or you can never replay anything. Prohibit UPDATE and permit DELETE, so history is never edited but the projection stays disposable. Make the rebuild call the same code as the live write path, or the two will drift. And migrate the numbers rather than the endpoints — two derivations behind a per-account flag under an unchanged contract is how you re-platform anything whose output people audit.

This is the unglamorous half of platform work, and the half that decides whether the rest of a product can be trusted. If you have a number that three screens disagree about, or a reporting layer nobody dares rewrite, tell us about it — it is a problem with a known shape.