# The transactional outbox in practice with Postgres and Python: no lost events, and no duplicates when the receiver dedups

> An order is saved but the webhook to the customer fails, or the webhook goes out but the order rolls back. One table in your own database fixes both.

Original: https://fdetimes.net/en/guides/transactional-outbox-postgres-python/

Picture this code at a customer site: save an order to Postgres, commit, then call a webhook on the customer's ERP. If the process dies between those two lines, the order exists and the ERP never hears about it. Swap the order and the risk flips: the ERP receives an order that your database has rolled back.

This is the dual-write problem. The Debezium blog puts it bluntly: there is no shared transaction that spans both a service's database and Apache Kafka. The same is true of a customer's system, and it is harder still, because you do not control it.

For an FDE, this is the classic bug in an integration that "works in the demo". This guide builds a transactional outbox from scratch with Postgres and Python, ready to take straight to a customer.

## What you will build, and what you need first

As microservices.io describes it, the outbox answers exactly one question: how to update the database and send a message atomically. The service first stores the message in the database, in the same transaction as the business data, and a relay sends it on afterwards.

You need Postgres running locally, Python 3 and a Postgres driver. The code below is written in the style of psycopg 3 with an HTTP client such as httpx. It is a stripped-down version for learning: it has no logging, retry backoff or configuration, which you should add before production.

## Step 1: the outbox table lives in your own database

The core idea, as Debezium describes it, is to put an "outbox" table inside the service's own database. No intermediate broker, no separate queue.

```sql
CREATE TABLE orders (
  id          uuid PRIMARY KEY,
  customer_id text NOT NULL,
  status      text NOT NULL
);

CREATE TABLE outbox (
  id           uuid PRIMARY KEY,
  aggregate_id uuid NOT NULL,
  event_type   text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  sent_at      timestamptz
);

CREATE INDEX outbox_unsent ON outbox (created_at)
  WHERE sent_at IS NULL;
```

Check: `\d outbox` in psql should show `outbox_unsent`, a partial index that covers only unsent rows. The `id` column is the event ID, which the receiver will later use to dedup.

## Step 2: write the order and the event in one transaction

Everything else in the pattern rests on this step. The two INSERT statements must sit inside the same transaction block.

```python
import uuid, json

def create_order(conn, customer_id):
    order_id = uuid.uuid4()
    event_id = uuid.uuid4()
    with conn.transaction():
        conn.execute(
            "INSERT INTO orders (id, customer_id, status) VALUES (%s, %s, 'created')",
            (order_id, customer_id),
        )
        conn.execute(
            "INSERT INTO outbox (id, aggregate_id, event_type, payload) "
            "VALUES (%s, %s, 'order_created', %s)",
            (event_id, order_id, json.dumps({"order_id": str(order_id), "customer_id": customer_id})),
        )
    return order_id
```

Test it by deliberately raising an exception between the two INSERTs. The correct result is that both `orders` and `outbox` are empty. An order without an event, or the reverse, means you have two transactions rather than one.

Note that this function makes no network call. It touches only your database, so it is fast and never hangs because the customer's API is slow.

## Step 3: a polling relay with SKIP LOCKED

Microservices.io lists two ways to build the relay: transaction log tailing (CDC) or a polling publisher. With plain Python and Postgres, polling is a sensible starting point because it needs no extra infrastructure.

To run several workers, you need `SKIP LOCKED`. The Postgres documentation explains that with this option, rows that cannot be locked immediately are skipped, and that it can be used to avoid lock contention when several consumers read a queue-like table.

```python
def relay_once(conn, http, url, batch=50):
    with conn.transaction():
        rows = conn.execute(
            "SELECT id, event_type, payload FROM outbox "
            "WHERE sent_at IS NULL ORDER BY created_at "
            "LIMIT %s FOR UPDATE SKIP LOCKED",
            (batch,),
        ).fetchall()
        for event_id, event_type, payload in rows:
            resp = http.post(
                url,
                json={"id": str(event_id), "type": event_type, "data": payload},
                headers={"Idempotency-Key": str(event_id)},
                timeout=10,
            )
            resp.raise_for_status()
            conn.execute("UPDATE outbox SET sent_at = now() WHERE id = %s", (event_id,))
```

Run this function in a loop with a short `sleep`. Check: open two terminals running two workers, create 200 orders, then count on the receiving side. Each worker should receive a different share, and neither should sit waiting for the other.

This is a deliberate simplification: the whole batch sits in one long transaction, holding row locks across every HTTP call. The Postgres documentation also warns that `SKIP LOCKED` gives an inconsistent view of the data, so use it only for queue-like tables such as the outbox, not for ordinary queries.

## Why "no lost events" still means "possible duplicates"

Walk through a concrete case with the version above. A batch holds 50 events; the first 30 POST successfully and the 31st returns a 500 error. `raise_for_status` throws, the transaction rolls back, and the 30 earlier UPDATEs are lost with it.

On the next poll, all 30 events the customer already received are sent again. A process killed between the POST and the UPDATE produces the same outcome. Microservices.io spells out the consequence: the relay might publish a message more than once, so the consumer must be idempotent, for example by recording the IDs of messages it has already processed.

**Key point:** The outbox only guarantees that no event is lost; to avoid duplicates, the receiver must dedup on its own.

That is why the `Idempotency-Key` header carries the event ID itself. You cannot delete a duplicate delivery, but you can make it harmless.

## A better version: one transaction per event

Duplicates are unavoidable, but resending 30 events because of one error is wasteful. The simplest fix is to commit per event: each transaction locks exactly one row, sends it, marks it and commits.

```python
def relay_one(conn, http, url):
    with conn.transaction():
        row = conn.execute(
            "SELECT id, event_type, payload FROM outbox "
            "WHERE sent_at IS NULL ORDER BY created_at "
            "LIMIT 1 FOR UPDATE SKIP LOCKED"
        ).fetchone()
        if row is None:
            return False
        event_id, event_type, payload = row
        resp = http.post(
            url,
            json={"id": str(event_id), "type": event_type, "data": payload},
            headers={"Idempotency-Key": str(event_id)},
            timeout=10,
        )
        resp.raise_for_status()
        conn.execute("UPDATE outbox SET sent_at = now() WHERE id = %s", (event_id,))
    return True
```

Rerun the earlier scenario: the first 30 events have committed `sent_at`, and only the 31st goes back to the queue. A kill between the POST and the UPDATE now duplicates exactly one event. Note that with a plain `ORDER BY created_at`, the failed event is retried immediately, so you need to add backoff.

This version still holds a lock on one row while it waits for HTTP. If the customer's API is slow, the next step is "claim first": a short transaction marks the row as claimed with a timestamp and commits, the send happens outside the transaction, and only then is `sent_at` updated. You then have to add your own column and a rule to reclaim rows that have been claimed for too long, so do this only when you really need it.

## Step 4: the receiver dedups by event ID

In Debezium's example, the first thing the handler does is check whether the event with that UUID has already been processed. If you also write the receiving endpoint, or the customer lets you put a buffer layer in front of their system, do this:

```sql
CREATE TABLE processed_events (
  event_id     uuid PRIMARY KEY,
  processed_at timestamptz NOT NULL DEFAULT now()
);
```

```python
def handle_event(conn, event):
    with conn.transaction():
        cur = conn.execute(
            "INSERT INTO processed_events (event_id) VALUES (%s) ON CONFLICT DO NOTHING",
            (event["id"],),
        )
        if cur.rowcount == 0:
            return  # already processed, skip
        apply_business_change(conn, event)
```

The crucial detail is that the dedup INSERT and the business change share one transaction. Split them and you have built yourself a miniature dual-write problem.

Rerun the scenario above: kill the relay right after the POST. Once the relay restarts, `processed_events` still holds only one row for that event, and the business change has been applied only once.

## When the customer already has an idempotent API, learn from Stripe

Often you cannot touch the customer's code. In that case, ask whether their API supports idempotency keys. Stripe is a good reference: the client generates the key so the server can recognise retries, Stripe suggests using V4 UUIDs, and keys can be up to 255 characters long.

Stripe stores the first response for each key and returns that same response on retries, including 500 errors. Keys are pruned after 24 hours, and if an old key comes back with different parameters, Stripe returns an error to prevent accidental misuse.

Those two details give you two questions to put to the customer. How long do they keep keys? If your relay can be down for longer than that window, resent events will be treated as new. And never generate a new key for each retry: the key belongs to the event, not to the request.

## Common mistakes

The most common mistake is calling the API inside `create_order` "to save time" and treating the outbox as a fallback. That is dual-write again. The second is using an auto-incrementing request ID as the idempotency key, so every retry produces a new key.

The third is letting the outbox table grow forever. The partial index keeps the polling query fast, but you still need a periodic job to clear sent rows, for example:

```sql
DELETE FROM outbox WHERE sent_at < now() - interval '7 days';
```

The seven days here is only an example. The rule is that your retention must be longer than the receiver's key retention, so that you still have data to compare against when investigating a duplicate delivery.

## At a customer site, the outbox starts with a complaint

It usually sounds like this: "every now and then an order doesn't make it into the accounting system". The first thing to do is find every place where code writes to the database and then immediately calls out, and ask the customer how their endpoint handles repeated requests.

When reading job descriptions for FDE or integration engineer roles, look for phrases such as "at-least-once", "idempotency", "webhook" and "CDC". On a CV, a line like "replaced dual-write with a transactional outbox, with kill-mid-flight tests proving no lost events and no duplicate processing" is far more convincing than a generic "event-driven".

A small repo containing exactly these four steps, both relay versions and a failure-simulation script is worth bringing to an interview. The interviewer will ask what happens when the relay dies halfway, and you will have the answer ready in code.

**Try this week:**

- Create the orders, outbox and processed_events tables on a local Postgres, run the order-creation function, then check that both tables either gain a row together or stay empty together.
- Kill the relay right after the POST and before the UPDATE, then confirm the receiver processes that event only once.
- Open a live integration at a customer site and look for code that writes to the database and then calls an API straight away: that is your first candidate for an outbox.

## Sources

- [Pattern: Transactional outbox](https://microservices.io/patterns/data/transactional-outbox)

- [Reliable Microservices Data Exchange With the Outbox Pattern](https://debezium.io/blog/2019/02/19/reliable-microservices-data-exchange-with-the-outbox-pattern/)

- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)

- [Idempotent requests | Stripe Documentation](https://docs.stripe.com/idempotency)