Why do emails get lost or sent for rolled-back work?

Every monolith that sends transactional email, such as signup confirmations, password resets and order receipts, has to coordinate two systems that cannot share a transaction: your database and an email provider. Your database can roll back. A sent email cannot be recalled. The two naive approaches each fail at a different point in that gap.

Sending inside the transaction. The handler inserts the order, calls the email API, then commits. If the commit fails because of a constraint violation, a deadlock or a timeout, the customer still gets a receipt for an order that does not exist. The API call also holds the transaction open while it waits on network latency, so row locks last longer and contention under load gets worse.

Sending after commit. The handler commits first, then sends the email from an after-commit hook or by pushing a job onto an external queue. The data is now correct, but the send is no longer guaranteed. If the process is killed during a deploy, runs out of memory, or the queue or provider call fails after the commit returns, the user never gets their reset link. Nothing records that an email was owed, so nothing retries it.

Approach Email for rolled-back work Email lost on crash or failure
Send inside transaction Yes Rarely, but the send can fail and abort the business transaction
Send or enqueue after commit No Yes, with no record left to retry from

The fix is to record the intent to send in the same transaction as the business data. That record is what an outbox table provides.

import sqlite3

def send_email(to: str, subject: str) -> None:
    # Placeholder for an HTTP call to an email provider. Once it returns,
    # the message is out and cannot be recalled.
    ...

def place_order_send_inside(conn: sqlite3.Connection, email: str, total: int) -> None:
    with conn:  # commits if the block succeeds, rolls back if it raises
        cur = conn.execute(
            "INSERT INTO orders (email, total) VALUES (?, ?)", (email, total))
        # Delivered now, while the transaction is still open and holding locks.
        send_email(email, f"Receipt for order {cur.lastrowid}")
        # If this statement or the commit fails, the order rolls back,
        # but the customer already has a receipt for it.
        conn.execute(
            "UPDATE inventory SET stock = stock - 1 WHERE sku = ?", ("SKU-123",))

def place_order_send_after(conn: sqlite3.Connection, email: str, total: int) -> None:
    with conn:
        cur = conn.execute(
            "INSERT INTO orders (email, total) VALUES (?, ?)", (email, total))
    # The order is committed. If the process is killed here (deploy, OOM),
    # or send_email raises, no row records that a receipt is still owed,
    # so nothing will ever retry it.
    send_email(email, f"Receipt for order {cur.lastrowid}")

Two naive handlers using Python's sqlite3: the first can email a receipt for an order that later rolls back, and the second can commit the order and then lose the email with nothing left to retry from.

Who should use the outbox pattern?

The outbox pattern is for teams whose application writes business state to a single transactional database and sends email as a consequence of that state. It fits best when these conditions hold:

  • A missing or wrong email has a real cost. Password resets, verification codes, order receipts, invoices, and security or account-change notices fall into this group. A user who never gets a reset link files a support ticket. A receipt for a rolled-back order creates a billing dispute.
  • Email is triggered by a database write. The pattern needs the business change and the outbox insert to commit in one transaction. A monolith or service that owns its own relational database, such as PostgreSQL or MySQL, can do this.
  • You deploy, restart or scale processes regularly. Every deploy kills processes that may be between a commit and a send. The more often you deploy, the more often the after-commit gap is exercised.
  • You don't want to add infrastructure just for reliable email. The outbox needs one table and one worker process, both using the database you already run.

It is a poor fit for teams whose email is purely best-effort, whose business data does not live in a single transactional store, or who already have a job queue that enqueues inside the same database transaction. The section "When should you avoid an outbox" covers these cases in more detail.

What is the outbox pattern and how does it work?

The outbox pattern adds one table to the database that already holds your business data. When a request changes state, for example creating an order, the same transaction inserts a row into outbox describing the email to send: recipient, template, the payload needed to render it, and a status of pending. Both writes commit together or roll back together. A rolled-back order leaves no outbox row, and a committed order always has one. The database's own atomicity guarantee replaces the coordination you could not get between the database and the email provider.

How does it work, step by step?

Delivery happens later, in a separate process:

  1. Request and transaction. The handler writes the business rows and the outbox row, then commits. The request does not wait on the email provider and does not fail because of it.
  2. Poll or wake-up. A dispatcher worker finds pending rows, either by polling on a short interval or by waking on a notification and then querying. A notification only reduces latency. The query is still the source of truth, so a missed signal delays an email but never loses it.
  3. Send. The worker calls the provider with the stored payload.
  4. Status update. On success, the worker marks the row sent in its own small transaction. On failure, it records the error and schedules a retry.

Each step depends only on durable state. If the handler crashes before commit, neither row exists and nothing is sent. If it crashes after commit, the outbox row is waiting for the dispatcher. If the dispatcher crashes at any point, the row is still in the table, and the next poll picks it up after its lease expires. The only state that must survive is the database, and the database is already built to survive.

The design guarantees at-least-once delivery, not exactly-once. Suppose the worker crashes after the provider accepts the message but before the sent update commits. The row is still pending, so the worker sends it again. Every later section assumes duplicates can happen and treats suppressing them as a deliberate part of the design.

+--------+  request   +--------------------------------------------+
| Client | ---------> | Handler: BEGIN                             |
+--------+            |   INSERT INTO orders ...                   |
                      |   INSERT INTO outbox (status='pending')    |
                      | COMMIT: both rows commit or roll back      |
                      +---------------------+----------------------+
                                            | 1. one transaction
                                            v
+------------------------------------------------------------------+
| Database                                                         |
|   +-----------+    +------------------------------------------+  |
|   | orders    |    | outbox: recipient, template, payload,    |  |
|   +-----------+    |         status = pending or sent         |  |
|                    +------------------------------------------+  |
+------------------------------------------------------------------+
        |                          ^                     ^
        | wake-up (hint only)      | 2. poll pending     | 4. UPDATE
        v                          |    rows             |    status
+------------------------------------------------------------------+
| Dispatcher worker (separate process)                             |
|   on success: mark row 'sent' in its own small transaction       |
|   on failure: record error, schedule retry                       |
+------------------------------------------------------------------+
                                   | 3. send stored payload
                                   v
                        +----------------------+
                        | Email provider       |
                        +----------------------+
Crash after provider accepts but before 'sent' commits: row stays pending,
so it is sent again. Delivery is at-least-once, not exactly-once.

The handler writes the order and its outbox row in one transaction, then a separate dispatcher polls for pending rows (a wake-up signal only cuts latency), sends through the provider, and marks each row sent, which gives at-least-once delivery.

How should you design the outbox table schema and indexes?

Start with one table that holds everything the dispatcher needs to send a message without consulting other state:

  • id: a bigint or UUID primary key.
  • recipient: the destination address.
  • template: a template name such as password_reset.
  • payload: JSON holding template variables.
  • idempotency_key: unique, derived from the business event. An example is order:4812:confirmation.
  • status: pending, sent or failed.
  • attempt_count: an integer, default 0.
  • next_attempt_at: defaults to the insert time.
  • created_at: the insert time.
  • claimed_until: an optional, nullable lease timestamp.

The unique constraint on idempotency_key turns an accidental double enqueue into a constraint violation instead of a second email.

The dispatcher's hot query asks for pending rows that are due. A partial index such as CREATE INDEX ON email_outbox (next_attempt_at) WHERE status = 'pending' stays small, because sent rows drop out of it. In PostgreSQL that is enough. If your database lacks partial indexes, use a composite index on (status, next_attempt_at).

The insert has to run on the same connection and inside the same transaction as the business write. For example, inside with db.transaction():, you insert the order and then INSERT INTO email_outbox (...) before the block exits. Avoid a separate session or a helper that opens its own connection, because either one brings back the gap the outbox exists to close.

What goes in payload?

You have two options, and each has a cost:

  • Store the rendered message or personal data. The email then reflects exactly the state at commit time. The outbox becomes a store of personal data, so you need a retention policy and a purge job, and it must be covered by deletion requests.
  • Store only references, such as order_id. The table stays lean. The catch is that rendering happens at send time, so if a customer edits the order a second after commit, the email shows the new values.

A common compromise is to store references plus the few fields whose commit-time value matters, and to purge sent rows on a schedule.

-- PostgreSQL: one row holds everything the dispatcher needs to send.
CREATE TABLE email_outbox (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  recipient       text        NOT NULL,
  template        text        NOT NULL,           -- e.g. 'order_confirmation'
  payload         jsonb       NOT NULL,           -- template variables
  idempotency_key text        NOT NULL UNIQUE,    -- double enqueue fails instead of double send
  status          text        NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'sent', 'failed')),
  attempt_count   integer     NOT NULL DEFAULT 0,
  next_attempt_at timestamptz NOT NULL DEFAULT now(),
  created_at      timestamptz NOT NULL DEFAULT now(),
  claimed_until   timestamptz                     -- optional lease; NULL = unclaimed
);

-- Partial index: only due-able rows are indexed, so it stays small as rows are sent.
-- Without partial index support, use (status, next_attempt_at) instead.
CREATE INDEX email_outbox_pending_due_idx
  ON email_outbox (next_attempt_at)
  WHERE status = 'pending';

-- Enqueue in the SAME transaction as the business write:
-- both rows commit together or neither does.
BEGIN;

INSERT INTO orders (id, customer_id, total_cents)
VALUES (4812, 77, 5999);

-- Reference-style payload: only the order id, rendered at send time.
-- Storing the rendered body or personal data instead freezes commit-time state,
-- but makes this table subject to retention and deletion requirements.
INSERT INTO email_outbox (recipient, template, payload, idempotency_key)
VALUES ('customer@example.com', 'order_confirmation',
        '{"order_id": 4812}', 'order:4812:confirmation');

COMMIT;

A PostgreSQL outbox table with a unique idempotency key and a partial index on pending rows by next_attempt_at, plus an order insert and its outbox row written in one transaction.

How do you build a dispatcher worker that claims rows safely?

The dispatcher polls for pending rows in small batches, typically tens rather than thousands, so a crash or slow provider strands little work. Claiming a row and sending it are separate steps. In a short transaction, select due rows with SELECT ... FOR UPDATE SKIP LOCKED, mark them sending with a claimed_until timestamp, and commit. Concurrent workers skip rows that are already locked instead of blocking on them. On databases without SKIP LOCKED, an atomic UPDATE ... WHERE status = 'pending' OR claimed_until < now() that sets a lease gives the same result.

Then call the email API outside any transaction, and mark the row sent in a second short transaction. Holding a lock across the call ties a database connection to the provider's latency. If the provider slows down, the connection pool drains and request traffic stalls along with email.

Concern SKIP LOCKED claim Lease column only
Contention between workers Locked rows are skipped Losers of the conditional update get zero rows
Crashed worker Lease expiry frees the row Lease expiry frees the row
Duplicate send risk A slow send can outlive its lease Same

Expired leases are the main source of duplicates. If a send takes longer than the lease, a second worker reclaims the row and sends it again. Set the lease well above the provider timeout. When marking a row sent, check that the claim token is still the one this worker wrote.

Multiple workers and retries also break ordering. When order matters, such as "order shipped" arriving before "order placed", serialize per key by claiming only the oldest unsent row for each aggregate_id. This costs throughput, because one slow or failing row blocks every later message for that key.

-- Tx 1 (PostgreSQL): claim up to 20 due rows, then commit right away.
-- $1 is a fresh claim token (UUID) generated by this worker per batch.
WITH due AS (
  SELECT id FROM email_outbox o
  WHERE (o.status = 'pending'
         OR (o.status = 'sending' AND o.claimed_until < now()))  -- expired lease
    -- Per-key ordering: skip a row if an older unsent row exists for its key.
    -- Costs throughput: one stuck row blocks every later row for that key.
    AND NOT EXISTS (
      SELECT 1 FROM email_outbox older
      WHERE older.aggregate_id = o.aggregate_id
        AND older.status <> 'sent'
        AND older.id < o.id)
  ORDER BY o.id
  LIMIT 20
  FOR UPDATE SKIP LOCKED          -- other workers skip these rows instead of waiting
)
UPDATE email_outbox t
SET status        = 'sending',
    claim_token   = $1,
    claimed_until = now() + interval '5 minutes',  -- well above the provider timeout
    attempts      = t.attempts + 1
FROM due
WHERE t.id = due.id
RETURNING t.id, t.recipient, t.subject, t.body;
-- COMMIT here. Call the email API outside any transaction.

-- Tx 2: mark sent only if this worker still owns the claim.
-- Zero rows updated means the lease expired and another worker reclaimed the row.
UPDATE email_outbox
SET status = 'sent', sent_at = now(), claim_token = NULL
WHERE id = $2 AND claim_token = $1;

A PostgreSQL claim query that uses FOR UPDATE SKIP LOCKED, a lease, and per-key ordering, followed by a separate mark-sent update that checks the claim token so a worker whose lease expired cannot record the row as sent.

How do you handle retries, duplicates and permanent failures?

Each failed send needs a decision: retry or give up. For transient errors, set next_attempt_at to now plus an exponential delay (for example, base × 2^attempts, capped at an hour) with random jitter. Without jitter, rows that failed together during an outage retry together and hit the provider in synchronized waves. Retrying a permanent error only wastes attempts and can hurt your sender reputation, so classify the provider response before rescheduling.

Error class Examples Action
Transient Timeouts, connection resets, rate limits, 5xx responses Increment attempts, reschedule with backoff
Permanent Invalid or suppressed address, rejected content, 4xx validation errors Mark dead immediately
Exhausted Transient failures past max_attempts Mark dead, raise an alert

A dead row should trigger an alert. Rows that sit in a table nobody watches are lost email.

Duplicates come from one unavoidable gap. If the worker crashes after the provider accepts the message but before the sent update commits, the lease expires and another worker sends it again. To reduce the impact, pass the outbox row ID as an idempotency key or message identifier where the provider supports deduplication. Also make resends harmless: render from the stored payload, and never mint new tokens or codes at send time. Then a duplicate is identical rather than contradictory.

Acceptance is not delivery. Bounces and complaints arrive later through separate webhooks or reports. Match them back to the message identifier, then suppress the affected addresses.

Should you use after-commit hooks, and what are the alternatives?

An after-commit hook is useful once the outbox exists, but it cannot be the only path. The process can crash, be redeployed, or lose its connection between the commit and the hook firing, and nothing will ever retry that hook. Use the hook to shorten latency instead: wake the dispatcher, or try an immediate send of the row that was just committed. That send must still go through the normal claim-and-mark path, so a send from the hook and a send from the poller cannot both happen. The poller remains the guarantee and picks up anything the hook missed.

Approach Reliability Latency Operational cost and complexity
Send inside the transaction Sends for rolled-back work; slow providers hold locks Immediate Low, but couples commit time to the provider
After-commit hook alone Loses messages on crash after commit Immediate Low
External queue after commit Loses messages if enqueue fails after commit Low Another system to run and monitor
Change-data-capture relay Strong; reads the committed log Low High: connector, log retention, schema coupling
Outbox + poller (+ hook) Strong Poll interval, or near-immediate with hook Moderate: one table, one worker

The polling interval trades latency against database load. Each poll is a query, and the number of idle queries grows with worker count multiplied by frequency. A partial index on pending rows keeps each poll cheap. With a hook handling the fast path, you can set the poller to tens of seconds without users noticing.

How can an outbox save engineering time?

Most of the time saved comes from work that no longer happens: investigating missing emails, writing one-off repair scripts, and coordinating around deploys and provider outages.

  • Debugging becomes a query instead of a log search. Without an outbox, "I never got my reset email" means correlating application logs, provider logs and deploy timestamps to work out whether a send was ever attempted. With an outbox, one row shows whether the email was enqueued, how many attempts were made, when the next attempt is due, and the last error. If no row exists, the business transaction never committed, and that is a different bug with a clear starting point.
  • Recovery is a status change, not a script. After a provider outage or a bad template deploy, the affected rows are already in the table. Fixing the cause and resetting those rows to pending replays them through the normal path, with the same idempotency keys and payloads. No one has to reconstruct who should have received what from business tables.
  • Provider outages stop being incidents. Because requests no longer call the provider, a provider slowdown does not exhaust request threads or database connections. Emails queue up and drain when the provider recovers, and the on-call engineer watches a backlog metric instead of firefighting request latency.
  • Deploys don't need special handling. Killing a process between commit and send no longer loses anything, so there is no need for graceful-drain logic aimed at in-flight email work.
  • One mechanism covers every transactional email. Once the table and dispatcher exist, adding a new email is an insert in the relevant transaction plus a template. Retry, backoff, dead-lettering, alerting and deduplication are written and tested once rather than per feature.
  • Tests are deterministic. Request-handler tests can assert that an outbox row was written, without mocking the email provider or waiting on asynchronous delivery. The dispatcher is tested separately against a fake provider.

To decide whether this matters for your team, compare the one-time build cost with how often you currently investigate or repair lost and wrong emails. As an illustrative estimate with assumed inputs: if a team handles an assumed four email-related investigations a month at an assumed three engineer-hours each, that is twelve hours a month. If the outbox cuts each investigation to an assumed thirty minutes of checking row status, the time spent falls to about two hours a month. These figures are not measurements. Replace them with your own ticket and incident history.

When should you avoid an outbox, and what does it cost to run?

When to skip it

Not every message needs this machinery. If a lost "someone liked your post" notification costs nothing, a fire-and-forget send after commit is fine. The pattern also assumes the business data and the outbox share one transactional database. If they don't, you are back to a dual write. And if your queue already supports enqueueing inside the same database transaction, that queue already is your outbox.

What it costs

The upfront work is a table, a worker, and tests, usually a few days. The savings come later. Investigating a lost email means checking one row's status and attempt history instead of reading logs. Support can replay a message by resetting its row to pending, with no ad hoc script.

Operating it

Alert on two metrics: backlog depth (pending row count) and the age of the oldest pending row. Age matters more, because a stuck row is invisible in a healthy-looking count. Delete or archive sent rows after a fixed retention window. This table takes constant inserts and updates in your primary database, so dead tuples pile up quickly. Tune autovacuum for this table, and batch deletes so cleanup doesn't compete with production traffic.

Proving the guarantees

Write three tests:

  • Rollback: roll back a transaction and assert that no outbox row exists.
  • Crash recovery: kill the worker between send and mark-sent, then assert the provider received one logical email under the idempotency key.
  • Concurrency: run two workers concurrently and assert that no row is ever claimed twice.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to send email after transaction commit, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.