An event log that is not an event store

Answering “what happened to this order” took twenty minutes, three tables and a certain amount of inference. The state was stored and the transitions that produced it were not, which is the normal condition of a database and is a problem the moment anybody asks a historical question.

The symptom

a support ticket: "why was order 8814 refunded twice?"

what had to be assembled:

  orders                 status='refunded', updated_at
  payments               two rows, 11 minutes apart
  the application log    grepped for 8814, 40,000 lines,
                         retained 14 days — and this was
                         21 days ago
  a support person's
    memory               "I think somebody clicked twice"

time to answer: 20 minutes, and the answer was a guess.

The application log would have had it and the retention had expired. That is the recurring shape: the information exists somewhere with a lifetime chosen for a different purpose, and the historical question arrives after it has gone.

Why it happens

A row records the current state because that is what the application needs to serve a request. Every transition is an update that overwrites the previous answer, and reconstructing the sequence afterwards is archaeology.

The fix

Append-only, in the same transaction

CREATE TABLE domain_events (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject_type VARCHAR(64) NOT NULL,
  subject_id   VARCHAR(64) NOT NULL,
  type         VARCHAR(96) NOT NULL,
  payload      JSON NOT NULL,
  actor        VARCHAR(96) NULL,
  occurred_at  DATETIME(6) NOT NULL,
  KEY idx_subject (subject_type, subject_id, id)
) ENGINE=InnoDB;

DB::transaction(function () use ($order, $amount, $actor) {
    $order->refund($amount);
    $order->save();

    $this->events->record(
        subject: $order,
        type: 'order.refunded',
        payload: ['amount_cents' => $amount->cents, 'reason' => $reason],
        actor: $actor->identifier(),
    );
});

Writing the event in the same transaction as the state change is what makes the log trustworthy — an event that can exist without the change, or a change without the event, is a log that has to be treated as approximate. The single index on subject and id is the only one needed, because every read is a timeline.

What goes in the payload, and what must not

in:
  what changed, and to what
  the values needed to explain the change
  who did it, and through what interface

not in:
  a snapshot of the whole entity — it doubles the
    storage and goes stale in meaning as the schema
    changes
  anything derivable from the subject row
  personal data beyond an identifier. this table is
    kept longer than the data it describes.

our payloads average 180 bytes. an entity snapshot
would have averaged 4 KB.

Excluding personal data is not fastidiousness — this table has an eighteen-month retention while a customer may exercise a deletion right before that. Storing an identifier and nothing else means the log survives a deletion without becoming a copy of the thing that was deleted.

Not rebuilding state from it

the line that keeps this cheap: the orders table is the
truth, and domain_events describes how it got there.

ruled out:
  replaying to reconstruct an entity, correcting state
  with a compensating event, any projection something
  reads at runtime

permitted:
  a timeline for a human, an audit answer, a one-off
  analysis run by hand

every one of those is a read by a person, which means
the log can be wrong in a way an event store cannot.

This is the whole distinction and it is worth being explicit about, because the temptation to project from it arrives about a month in. An event store is the source of truth and every event must be complete and correct forever; an event log is a description and a missing event is an inconvenience.

The support view

Route::get('/admin/orders/{order}/timeline', function (Order $order) {
    $events = DomainEvent::query()
        ->where('subject_type', 'order')
        ->where('subject_id', $order->id)
        ->orderBy('id')
        ->get();

    return view('admin.timeline', compact('order', 'events'));
})->middleware('can:view-audit');

order 8814, rendered:

  09:41:02  order.placed          web    customer@…
  09:41:04  payment.captured      system
  14:02:11  order.refunded  £49   admin   alice@…
            reason: "customer request"
  14:13:40  order.refunded  £49   admin   alice@…
            reason: "customer request"

11 minutes apart, same actor, same reason. the second
was a double submission on a form with no idempotency
key — which is now the ticket.

Twenty minutes and a guess became thirty seconds and a diagnosis, and the diagnosis was a missing idempotency key on an admin form rather than the “somebody clicked twice” that had been assumed. The log turned a support question into an engineering ticket.

Retention, and the archive job

-- 18 months hot; monthly, into a compressed archive
SELECT * FROM domain_events
WHERE occurred_at < DATE_SUB(NOW(), INTERVAL 18 MONTH)
INTO OUTFILE '/tmp/events-2022-02.tsv';

DELETE FROM domain_events
WHERE occurred_at < DATE_SUB(NOW(), INTERVAL 18 MONTH)
LIMIT 50000;   -- batched

Eighteen months covers every question anybody has actually asked, and the archive covers the ones nobody has asked yet at a hundredth of the cost. The batched delete is the same pattern as everywhere else, and this table would be a candidate for partitioning if it were larger.

Verifying it worked

$ mysql -e "SELECT type, COUNT(*) FROM domain_events
            GROUP BY type ORDER BY 2 DESC LIMIT 5"
order.placed        41,208
payment.captured     40,914
order.shipped        38,102
order.refunded        1,204
order.cancelled         918

# the assertion that matters: no order changed state
# without an event
$ mysql -e "SELECT COUNT(*) FROM orders o
            WHERE o.status='refunded' AND NOT EXISTS (
              SELECT 1 FROM domain_events e
              WHERE e.subject_id = o.id
                AND e.type = 'order.refunded')"
0

# support time to answer a history question
  before  ~20 minutes    after  ~30 seconds

The orphan check is the test that the recording is actually in every path — running it monthly caught one route in October that updated a status directly, added by somebody who had not seen this pattern.

What this costs

A table that grows forever, or at least for eighteen months, at the rate of the business. Forty thousand orders a month is a hundred and twenty thousand events, which is small — a system with per-field change tracking would be an order of magnitude larger and the retention decision would be harder.

The larger cost is the temptation to project from it. It is a complete, ordered, append-only record of everything that happened, sitting next to the application, and the first time somebody needs a report the obvious move is to build it from here. That is the road to event sourcing without any of the guarantees, and the only defence is the sentence in the decision record saying the orders table is the truth.