From database changes to event messages

A database schema is normalised for the use case the owning service implements. The events that service publishes serve a different set of readers, so the shape that suits the table rarely suits the message.

Flatten the relationship

When a record with a one-to-many relationship is published to a broker, the usual move is to de-normalise it: emit one message per child, repeating the parent’s fields on each.

An order that holds an order number, a location, an order date and a payment reference, with three line items each holding a name, a price and a quantity, becomes three messages. Each message carries the order-level fields together with one item’s fields — a single flat record, no nesting.

That costs repetition and buys independence. A consumer that aggregates by item name, a consumer that aggregates by location, and a consumer that simply stores every record can all read the same topic without any of them having to understand the parent-child structure, or agree with each other about how to walk it. Adding a fourth consumer with a fourth aggregation key needs no change to the producer.

The cost is real, so it is worth stating: repeated fields inflate the topic, and any parent-level correction has to be applied to every message derived from that parent. Where the consumer set is small and stable and the nesting is shallow, keeping the structure is defensible.

Publishing the change is part of the write

Emitting an event on every database change — change data capture — is how other systems are notified and how analytics stores are fed. The trap is the obvious implementation: write to the database, then publish to the broker. If the process dies between the two, the state has changed and nobody has been told. Reversing the order produces the opposite failure: an event announcing something that never happened.

A transaction spanning both the database and the broker is not the answer either. Distributed transactions over a broker are either unavailable or expensive, and they couple the availability of the write path to the availability of the broker.

The event must instead be committed in the database, in the same local transaction as the business write, by inserting it into an outbox table. A separate relay — a poller over that table, or a CDC connector reading the database log — publishes from the outbox afterwards and marks each row as sent. The write and the record of the event now succeed or fail together, and delivery becomes an at-least-once retry problem rather than a consistency problem. Consumers must therefore be idempotent.

Where the consumer side also writes to Kafka, Spring for Apache Kafka can bind the two: a listener container configured with a KafkaTransactionManager starts a Kafka transaction around the listener invocation, so the records the listener produces and the offsets it commits are committed together. See Apache Kafka for the details.

Events are immutable

An event records something that has already happened, so it is never edited. A correction is a new event. Design the consumers on that basis: state is rebuilt by replaying the sequence, and a mistake is repaired by appending, not by rewriting history.

Further reading

• SNS and the outbox pattern — the handbook’s worked implementation on AWS
• Pattern: Transactional outbox — the pattern, its variants and its failure modes
• Spring for Apache Kafka — transactions