Write the outbox row in the same Postgres transaction as the business row

A welcome email, a webhook fan-out, and a search-index update are the same problem. The route has decided the business row. Another system still has to hear about it. Commit the user, then publish to the broker, and a broker that is down, or a process that dies, leaves a row with no event.

That gap is a dual write. The database committed. The queue did not. Retrying the HTTP request can insert a second user, and it still may not send the first email. An owned API stores idempotency keys in Postgres and replays the first response. That row remembers the HTTP result. It does not publish an event you never wrote down.

Write the outbox row in the same Postgres transaction as the business row. Commit both, or roll both back. A relay reads unpublished rows after commit and publishes them. The request does not talk to the broker. This is the transactional outbox. The route is a Next.js handler. The database is Supabase Postgres.

The loss is the gap after commitCopy Anchor Link

Two writes that are not one transaction can succeed in either order, or only one of them.

Publish first, then commit. The broker has the event and the transaction rolls back. Consumers act on a user who does not exist.

Commit first, then publish. The user exists. The publish throws, times out, or never runs because the process was killed after commit. The client may already have a 201. Nothing in the database says the email is still owed.

Holding the transaction open while you call the broker pins a session on the network. A broker error then rolls back a signup you were ready to keep. Do that only when you would rather fail the request.

The outbox is the other choice. The business write must stick even if the broker is down, and the event must still leave later.

Write the event in the same transactionCopy Anchor Link

The handler builds one unit of work: insert or update the business row, insert the outbox row, commit. supabase-js sends each statement as its own request. Those calls do not share a transaction. Put both writes in one Postgres function and call that function once, or use a client that holds a single transaction. If either insert fails, neither remains.

Copy into payload what the consumer must see as of this commit. A later update can change the user before the relay runs. A snapshot of the address belongs in the row. An event that means "read the user now" should say so in the type. Mixing the two indexes the wrong version.

One event of a given type per aggregate gets a unique pair. A signup emits one user.created for that user. A retried transaction hits the unique constraint and does not queue a second welcome. A stream of many events, such as every order change, uses its own id and does not use that pair.

The outbox tableCopy Anchor Link

published_at null means the relay still owes the broker this row. claimed_at is a lease, not a receipt. attempts counts how many times a relay has taken the row.

create table outbox (
  id uuid primary key,
  aggregate_type text not null,
  aggregate_id uuid not null,
  event_type text not null,
  payload jsonb not null,
  created_at timestamptz not null default now(),
  published_at timestamptz,
  claimed_at timestamptz,
  attempts integer not null default 0,
  unique (aggregate_id, event_type)
);

create index outbox_unpublished_idx
  on outbox (created_at)
  where published_at is null;

id is the event id the consumer will dedupe. Generate it in the route and store the same value in payload. The partial index is the relay's queue. Published rows stay out of that index.

The unique pair is the signup case. Drop it when the same event type can fire more than once, and keep id as the primary key.

Poll with a claim, wake with notifyCopy Anchor Link

The relay is a process that stays up. A Next.js route returns. It should not sit on LISTEN. LISTEN needs a session that stays open. The durable record is the table. NOTIFY only wakes a relay that is already listening. In the default configuration the payload must be shorter than 8000 bytes, so it cannot carry the event. A notify sent while the relay is down is gone. The next poll still sees published_at is null.

Claim a batch with FOR UPDATE SKIP LOCKED so two relays do not take the same row. The lease lets a crashed claim be taken again. Thirty seconds in the sketch is a lease you choose, not a measured optimum. Set published_at after the broker accepts the message. A relay that dies after publish and before that update will publish again. That is at least once.

with due as (
  select id
  from outbox
  where published_at is null
    and (claimed_at is null or claimed_at < now() - interval '30 seconds')
  order by created_at
  for update skip locked
  limit 20
)
update outbox as o
set claimed_at = now(),
    attempts = o.attempts + 1
from due
where o.id = due.id
returning o.id, o.event_type, o.payload, o.attempts;

Polling on a timer is enough when a short delay is fine. NOTIFY from the function that inserts the row, using the event id and not the body, shortens the wait when a listener is up. It does not replace the poll. A relay that only listens loses every event emitted while it was restarting.

At least once, then the consumerCopy Anchor Link

The broker and the consumer will see duplicates. Publish, crash, lease expires, publish again. The handler must treat id as already done once it has run. Store that id the way an owned API stores an idempotency key and replays the first response. Record id before the mailer call, or the second delivery sends a second email. How you hash a request body, and how long you keep the key, belong on that post.

Do not set published_at before the broker accepts the message. If the update that sets it fails, the duplicate is the consumer's problem. That is why the id was in the message.

This table does not order events across aggregates. One relay can publish a single aggregate in created_at order. Two relays, or parallel handlers, will not. If user.updated must follow user.created, the consumer serializes on aggregate_id.

When the relay keeps failingCopy Anchor Link

A payload the broker rejects, or a bug in the publisher, increments attempts forever if every failure only drops the lease. Cap the attempts. Past the cap, stop selecting the row.

Parking it, with the reason and enough of the payload to replay, is a dead-letter decision. Park failed jobs in a DLQ with the reason, the snapshot, and a replay. This post does not build that queue. The outbox's job ends when a person, or a replay tool, can see the event that did not leave.

The relay is a loop and a publish. Background jobs for AI features covers queues and retries for that worker. Use the outbox when you must not lose the fact that the business row committed. Use an ordinary job when the caller may fail the request instead of owing an event.

When a sync enqueue is enoughCopy Anchor Link

Skip the outbox when there is no second system. A column update that nothing else consumes is one write. An outbox row would publish a message nobody reads.

Skip it when you would rather fail the signup than accept a user with no event. Publish to the broker before commit. If that call fails, roll back and return an error. The client retries the whole command. You still need the idempotency key so the retry is not a second user, and a broker outage is an outage of the route. That trade is right when the event is part of success, not a follow-up you can owe.

Skip it when a miss is repaired without a queue. A search index that rebuilds from the table, or a welcome email the user resends from a button, does not need a durable event. The outbox is for an event you cannot rebuild, or cannot afford to miss until someone notices.

A publish after commit, with no outbox row, is the dual write again. It is enough only where a miss is repaired by retrying the request, by a rebuild, or by a button. It is not enough for a 201 returned while the broker was down.

OTF kits do not ship an outbox table or a relay yet. The route you own still commits the business row and the event in one transaction, and a process that stays up still publishes what that transaction left behind.

SourcesCopy Anchor Link

• Transactional outbox
• SELECT, including FOR UPDATE SKIP LOCKED
• LISTEN
• NOTIFY
• Idempotency keys on an owned API
• Park failed jobs in a DLQ
• Background jobs for AI features