skip to content

What columns belong in a well-designed outbox table, and what does each one enable?

level: juniorimportance: should knowfreq 48%

answer

  1. id (UUID) = event id + dedup key
  2. aggregate_type -> topic; aggregate_id -> Kafka key/order
  3. type -> event name/header; payload -> value
  4. created_at -> order + cleanup
  5. published flag for polling; CDC can delete-after-insert

basics

~20 s

Typical columns: a unique id (UUID), aggregate type (which topic), aggregate id (the Kafka key/ordering), event type, payload (the message body, often JSON), a timestamp, and optionally a published flag or sequence. These let the relay route, key, order, and dedup events.

solid answer

~50 s

A solid outbox row carries everything the relay needs to produce a correct Kafka message without touching business tables. Core columns: **id** (UUID primary key — also the dedup/event id consumers use); **aggregate_type** (e.g. 'order' — maps to the destination topic); **aggregate_id** (e.g. order id — becomes the Kafka message key, giving per-aggregate ordering); **type** (event name like 'OrderCreated'); **payload** (the serialized event body, often JSON/Avro); **created_at** (ordering/auditing/cleanup). For polling relays add a **published** boolean or **processed_at** to track sent rows, and an indexed monotonic **id/sequence** to publish in order with `FOR UPDATE SKIP LOCKED`. For CDC/Debezium, the **Outbox Event Router SMT** expects exactly these aggregate_type/aggregate_id/type/payload columns to route, key, and shape the message, and rows can be deleted immediately (the change is already captured from the log). You may also store headers/trace context for observability.

go deeper

for a junior

List the core columns: id, aggregate type/id, event type, payload, timestamp, published flag.

for a middle

Map each column to a relay job: routing (aggregate_type), keying/ordering (aggregate_id), dedup (id), polling (published).

for a senior

Tailor the schema to the relay (polling flag+index vs CDC delete-after-insert) and align columns with the Debezium SMT.

for a principal

Define an org-wide outbox schema + event envelope (id, type, trace context) so relays and consumers stay generic across services.

## Purpose of the table The outbox table is a staging area: each row is a fully-described event waiting to become a Kafka message. A good schema lets the **relay** route, key, order, serialize, and dedup events **without reading business tables**. Here is a canonical layout and what each column buys you. ```sql CREATE TABLE outbox ( id UUID PRIMARY KEY, -- unique event id; doubles as consumer dedup key aggregate_type VARCHAR NOT NULL, -- 'order' -> destination topic aggregate_id VARCHAR NOT NULL, -- '42' -> Kafka message KEY (ordering) type VARCHAR NOT NULL, -- 'OrderCreated' -> event name / header payload JSONB NOT NULL, -- the serialized event body created_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- ordering / audit / cleanup published BOOLEAN NOT NULL DEFAULT false -- polling relays only ); CREATE INDEX ON outbox (published, id); -- efficient poll of unsent rows in order ``` ### Column-by-column - **id (UUID PK)**: globally unique, stable across redeliveries -> perfect as the **dedup id** the consumer records in its inbox/processed table. Also gives a monotonic-ish claim order if you prefer a BIGINT identity instead. - **aggregate_type**: the *kind* of entity. Debezium's **Outbox Event Router** uses it to choose the **topic** (e.g. `outbox.event.order`). Keeps routing data-driven. - **aggregate_id**: the entity instance. Becomes the **Kafka key**, so all events for one entity share a partition -> **per-aggregate ordering**. - **type**: the specific event (`OrderCreated` vs `OrderShipped`), usually placed in a Kafka **header** so consumers can branch without deserializing the body. - **payload**: the message value — JSON, Avro, or Protobuf bytes. Should be self-contained so consumers don't call back into the producer. - **created_at**: enables time-ordered processing, auditing, and **cleanup** (purge rows older than N days). - **published / processed_at**: needed by **polling** relays to know what's been sent; combined with `FOR UPDATE SKIP LOCKED` and an index for safe concurrent claiming. With **CDC**, you often skip this and even delete the row right after insert — Debezium already captured it from the transaction log (a delete-after-insert keeps the table tiny). - Optional: **headers / trace_context** (correlation id, tracing) for end-to-end observability. ### Why this matters Keeping all of routing/keying/ordering/serialization in the row means the relay is generic and the producer never has to special-case event types. It also makes the CDC SMT mapping trivial because the column names line up with what Debezium expects.

  • Why does the outbox id make a good consumer-side dedup key?
    It's globally unique and stable: the same logical event keeps the same id across every redelivery. The consumer can store it in a processed-messages table and reject any id it has already applied, achieving idempotency.
  • With CDC/Debezium, why can you delete the outbox row immediately after inserting it?
    Debezium captures the insert from the database transaction log, not by querying the table. Once committed, the change is already in the log, so the row can be deleted right away (or marked tombstone), keeping the outbox table small while the event still reaches Kafka.

saying these in an interview costs you the question

  • Omitting an aggregate_id, so events can't be keyed for ordering.
  • Storing only a foreign key instead of a self-contained payload (forces consumer callbacks).
  • Reusing the business primary key as the event id (collides across multiple events per aggregate).
  • Adding a published flag but never indexing/cleaning it (poll queries slow down, table bloats).
  • Assuming CDC needs a published flag — it reads the log, not the flag.

context

resolving links…