The read model that finally earned its place

In 2023 a read model was deleted for being built before anybody needed it, at a cost of eight months of maintaining a second copy of the truth for a dashboard that was never scheduled. In 2025 the dashboard exists, the query joins six tables and takes eleven seconds, and the projection is back.

The symptom

SELECT c.id, c.name,
       COUNT(DISTINCT o.id)            AS orders,
       SUM(ol.quantity * ol.unit_minor) AS revenue,
       MAX(o.placed_at)                 AS last_order,
       COUNT(DISTINCT r.id)             AS refunds
FROM customers c
LEFT JOIN orders o        ON o.customer_id = c.id
LEFT JOIN order_lines ol  ON ol.order_id = o.id
LEFT JOIN refunds r       ON r.order_id = o.id
LEFT JOIN invoices i      ON i.order_id = o.id
LEFT JOIN payments p      ON p.invoice_id = i.id
WHERE o.placed_at >= ?
GROUP BY c.id, c.name
ORDER BY revenue DESC LIMIT 100;

-- 11.4s. 12.1M order_lines rows examined.

A query that was fine at four hundred thousand rows is not fine at twelve million, which is not a design failure — it is the same query meeting a different amount of data. The multiplication across two one-to-many joins is where the row count comes from.

Why it happens

An aggregate over several one-to-many relationships is inherently expensive, and no index makes a twelve-million-row grouping cheap. The options are to compute it less often or to compute it in advance, and both are a second copy of the data.

The fix

Four cheaper things, tried first

  1  a covering index
     helped the customers scan. the grouping is the
     cost. 11.4s → 9.8s.

  2  splitting into two queries and joining in PHP
     removes the row multiplication. 9.8s → 4.1s, and
     it is now two queries either of which can be slow.

  3  a shorter date range
     the dashboard defaults to 90 days. reducing it to
     30 gives 1.2s and a dashboard that answers a
     different question.

  4  caching the result for 15 minutes
     works, and the first request after every
     invalidation still takes 4 seconds — which is the
     request a person is waiting on.

all four are improvements. none of them gets under a
second, which is the requirement.

Documenting the four attempts is what distinguishes this from the 2023 version, where the projection was the first idea rather than the fifth. Two of the four are still in place — the covering index and the query split — and the projection sits on top of them.

The projection

CREATE TABLE customer_summary (
  customer_id     BIGINT UNSIGNED NOT NULL,
  period_start    DATE NOT NULL,
  orders          INT UNSIGNED NOT NULL,
  revenue_minor   BIGINT NOT NULL,
  refunds         INT UNSIGNED NOT NULL,
  last_order_at   DATETIME(6) NULL,
  computed_at     DATETIME(6) NOT NULL,
  PRIMARY KEY (customer_id, period_start),
  KEY idx_revenue (period_start, revenue_minor DESC)
) ENGINE=InnoDB;

Storing per customer per month rather than per customer per query is what makes the date range a sum over rows instead of a recomputation, and it means one table serves every window the dashboard offers. The descending index on revenue is what makes the top-hundred query an index scan of a hundred rows.

Rebuild rather than incremental

the 2023 version kept it in sync with a subscriber on
every domain event. that produced three bugs in eight
months, all of them a missed event.

this version recomputes:

  the current month    every 15 minutes
  the previous month   nightly, once, then frozen
  older months         never — they cannot change

  full rebuild         11 minutes, and it is a
                       supported operation rather than
                       an emergency

what this gives up: the current month is up to 15
minutes stale. what it removes: every class of
synchronisation bug.

A projection that can be rebuilt from source in eleven minutes is a cache rather than a second source of truth, which is the distinction the 2023 version failed to hold. Incremental updates buy freshness and cost correctness, and freshness turned out to be negotiable in a way correctness was not.

The staleness budget, agreed rather than assumed

asked the three people who read this dashboard daily:

  "how out of date can this be before it is wrong?"

  one said an hour
  one said a day
  one said "it should be live" and, when asked what
    decision they make from it, said none that day

agreed: 15 minutes for the current month, and the
timestamp displayed on the page.

the timestamp is the part that mattered — the
complaint was never about staleness, it was about not
knowing whether it was stale.

Displaying the computation time removed the objection entirely, which suggests the requirement had never been freshness. A number with no provenance invites suspicion and a number labelled “as of 14:32” is one somebody can reason about.

The comparison job

// nightly, and it is the thing that keeps this honest
public function compare(): array
{
    $drift = [];

    foreach ($this->sampleCustomers(200) as $id) {
        $fromSource     = $this->expensiveQuery($id);
        $fromProjection = $this->projection($id);

        if ($fromSource != $fromProjection) {
            $drift[$id] = ['source' => $fromSource, 'projection' => $fromProjection];
        }
    }

    return $drift;
}

Sampling two hundred customers rather than comparing all of them keeps the job to ninety seconds and is sufficient to detect a systematic error, which is the only kind that matters. It has found one drift in four months — a refund recorded against a deleted order, which the projection counted and the source query excluded.

Verifying it worked

$ ./bin/time-dashboard --range=90d
  0.14s          # was 11.4s

$ ./bin/rebuild-projection --full
  customers: 8,104   months: 74
  duration:  11m 20s

$ ./bin/projection-drift --since=30d
  comparisons: 6,000
  drift:           1   (a refund on a deleted order)

$ ./bin/dashboard-staleness --p95
  7m 40s         # against a 15-minute budget

The single drift in six thousand comparisons is the number that says the rebuild approach is sound, and it was a genuine inconsistency in the source data rather than a projection error. Finding it took four months and a job that costs ninety seconds a night.

What this costs

A second copy of the truth, which is exactly what 2023 was avoiding. The difference is a measured query rather than an anticipated one, a rebuild rather than incremental synchronisation, and a comparison job that would have caught the 2023 version’s three bugs in a night each.

It is also a table people will find and query directly, which is how a projection becomes a source of truth. Two people have already written a report against it, both of them correct today and both of them dependent on a schema that exists to serve one dashboard — and the only defence is a comment in the schema saying so.