An outbox table, and the relay that reads it

Publishing to a broker inside a database transaction is two systems and one commit, which cannot be made atomic.

CREATE TABLE outbox (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  aggregate_id BINARY(16) NOT NULL,
  type         VARCHAR(64) NOT NULL,
  payload      JSON NOT NULL,
  published_at DATETIME(6) NULL,
  created_at   DATETIME(6) NOT NULL,
  KEY idx_unpublished (published_at, id)
) ENGINE=InnoDB;

The index on (published_at, id) with nulls first is what makes the relay poll cheap — it reads the head of the index rather than scanning. The relay publishes and marks, in that order, which means a crash between the two produces a duplicate rather than a loss. That is the correct direction and it is why the consumer has to be idempotent regardless.