All chapters
  1. Start hereMemory is what you keep. A checkpoint is where you stopped
  2. Memory · 1The stateless request: why anything has to be stored at all
  3. Memory · 2Five kinds of memory: working, episodic, semantic, procedural and preference
  4. Memory · 3Conversation history, summaries, retrieval and durable facts
  5. Memory · 4Files as memory in coding agents: instructions, journals, artifacts and checkpoints
  6. Memory · 5Database stores: tables, JSON, documents, objects and vectors
  7. State · 6Run identity: run_id, thread_id, trace_id, correlation_id and task_id
  8. State · 7State machines, checkpoints, event sourcing and where replay must stop
  9. State · 8Failure and resume: idempotency keys, retry policy and the exactly-once illusion
  10. State · 9The transactional outbox: side effects, compensation and deduplication
  11. Policy · 10Memory write policy: extraction, confidence, provenance, expiry and deletion
  12. Policy · 11Retrieval and ranking: relevance, recency, salience and scope
  13. Policy · 12Shared memory between agents: consistency, isolation, ownership and races
  14. Policy · 13Security: tenant separation, personal data, secrets, consent and retention
  15. Practice · 14Evaluation: memory correctness, stale facts, contamination and recovery tests
  16. Practice · 15Frameworks and standards: what each one stores for you
  17. Practice · 16The insurance lab: one run that fails, resumes and remembers, end to end
  18. Practice · 17Advanced: migration, conflict resolution, forgetting, summary drift and disaster recovery
  19. ReferenceGlossary
  20. AboutAbout this site and its sources
State · 9

The transactional outbox: side effects, compensation and deduplication

A database row and a message cannot be written in one step. The outbox pattern, the consumer that ignores a repeat, and how to undo what cannot be rolled back.

What you will understand
The dual write
Why a database row and a message to another system cannot be committed together.
Outbox and inbox
Write the message as a row in the same transaction; send it later; let the consumer ignore a repeat.
What cannot be undone
Compensation: a second action that corrects the first, and where sagas come in.

Run run-9a2c for SUB-1001 has written its recommendation. Step 7 stored a decline citing R-ROOF-01, with its idempotency key, as the previous page required. Step 8 has one job: tell the underwriter cockpit that a recommendation is ready, so that it appears in an underwriter's queue.

The worker commits the recommendation row. Then, before it sends the message to the cockpit, it dies.

When the run resumes, the row is there and the checkpoint of step 7 may or may not be. Suppose it is. The resume starts at step 8 and sends the message: fine. Now suppose the code was written differently, so that the row and the send happen inside one step, record_and_notify, with one checkpoint after both. The resume sees that step as unfinished, runs it again, the idempotency key stops a second row, and the code, finding the row already there, skips the send as well. The recommendation exists. The cockpit never hears of it. No underwriter ever sees it. The submission waits, and nobody knows it is waiting.

This is case UW-M05, one of this site's own cases. The agent here is a scripted stand-in, so the results on this page show how the mechanism works, not how good any model is.

Two systems, no shared commit#

The trouble is that two different systems must both change. The store holds the recommendation. The cockpit is another system with its own storage, reached by a message. A dual write is code that writes to two systems one after the other and hopes both succeed. For example: insert the row, then send the message.

A database transaction makes several writes inside one database all-or-nothing. It cannot include the cockpit. So whatever order the two writes are in, there is a moment between them, and a crash at that moment leaves one written and the other not.

OrderCrash between the twoResult
Row, then messageAfter the commit, before the sendA recommendation no underwriter sees
Message, then rowAfter the send, before the commitThe cockpit lists a recommendation the store does not have

Neither order is safe. Retrying does not help either, because after a restart nothing records that a message is owed.

The transactional outbox#

The repair is to stop writing to two systems at once. The transactional outbox pattern writes the message as a row in an outbox table, in the same database transaction as the change the message is about. Either both rows commit or neither does. For example: the recommendation row and the outbox row "recommendation ready for run-9a2c" commit together.

The message has not reached the cockpit yet. A separate process does that. A message relay reads unsent outbox rows, sends each one, and marks it sent. If the worker dies right after the commit, the outbox row is still there, and the relay sends it whenever it next runs.

This is the outbox write in the example, record_with_outbox in outbox.py. What it demonstrates is the single transaction around both inserts; a successful run proves that the two rows commit together in SQLite, nothing about a real message broker.

def record_with_outbox(conn: sqlite3.Connection, run_id: str, submission_id: str, recommendation: str, at: str) -> str:
    """The outbox write: the recommendation row and the message row in ONE transaction."""
    key = f"{run_id}:record_recommendation"
    with transaction(conn):
        conn.execute("INSERT INTO recommendations (run_id, submission_id, attempt_number, idempotency_key, recommendation, body_json, "
                     "policy_version, created_by, created_at) VALUES (?, ?, 1, ?, ?, '{}', '2026.2', 'underwriting agent', ?)",
                     (run_id, submission_id, key, recommendation, at))
        return enqueue_message(conn, run_id, {"run_id": run_id, "submission_id": submission_id, "recommendation": recommendation},
                               f"{run_id}:notify_cockpit", at)

Notice the second key, run-9a2c:notify_cockpit. enqueue_message uses INSERT OR IGNORE, so a resumed step that enqueues the same message again adds nothing. The outbox row is itself idempotent.

The relay can send twice, so the consumer remembers#

The relay has its own gap. It sends a message, then marks the row sent. If it dies between the send and the mark, it finds the row unsent after its restart and sends it again. The relay is at-least-once, for the same reason every step is: it does something in one place and records it in another.

So the consumer must recognize a repeat. An inbox table is a table on the consumer's side that records the id of every message it has accepted; a message whose id is already there is ignored. That is deduplication: dropping repeats of something already handled. This is the cockpit's receive method in the example, from the class Cockpit in outbox.py:

    def receive(self, message: dict[str, Any], at: str) -> bool:
        """Accept a message, or ignore it when its id is already in the inbox table."""
        self.received += 1
        if self.dedup:
            cur = self.conn.execute("INSERT OR IGNORE INTO inbox (message_id, consumer, received_at) VALUES (?, ?, ?)",
                                    (message["message_id"], CONSUMER, at))
            if cur.rowcount == 0:
                self.ignored.append(message["message_id"])
                return False
        self.items.append(message)
        if self.on_item:
            self.on_item(message)
        return True

The diagram puts the four parts together: the worker's transaction, the outbox row, the relay and the consumer's inbox.

Diagram: Outbox, relay and inbox · each arrow may repeat, the queue shows one item

Read left to right. The worker writes the recommendation and the outbox row in one transaction. A separate relay sends unsent rows and marks them sent; if it dies between the two it sends again. The cockpit checks its inbox table and ignores a message id it has already accepted, so the underwriter sees one item.

  1. 1 · One transaction
    1. Recommendation row: The change itself, stored once thanks to its idempotency key.
    2. Outbox row: The message owed to the cockpit, committed together with the row. Either both exist or neither.
  2. 2 · Send later
    1. Relay: A separate process sends unsent rows and marks them sent. A crash between send and mark means a second send.
  3. 3 · Drop the repeat
    1. Inbox table: The consumer records each accepted message id and ignores a repeat.
  4. 4 · What a person sees
    1. Underwriter cockpit: The queue the underwriter works from. The person decides.

Read it left to right. The worker writes two rows in one transaction and is done. The relay, a separate process, sends what is unsent and marks it. The cockpit checks its inbox table before it shows anything. Each arrow can fail and be repeated; no repeat produces a second item in the underwriter's queue.

UW-M05, run three ways#

The runner plays UW-M05 with the dual write and with the outbox plus a deduplicating consumer. Here is its output.

python run_memory.py --case UW-M05
UW-M05  The worker dies between the database commit and the message to the cockpit
  outbox_dedup  row stored True · messages on the wire 2 · cockpit items 1 · duplicates ignored 1 · failure none
  dual_write    row stored True · messages on the wire 0 · cockpit items 0 · duplicates ignored 0 · failure lost_message
  expected (built on UW-001): PASS · 9 of 9 checks

With the dual write the row is stored and no message ever leaves: lost_message. With the outbox and a deduplicating consumer, the relay sent the message twice and the cockpit showed one item. The full grid of patterns and crash points comes from the pure function outbox_outcome, run for every combination:

PatternNo crashCrash after the commitCrash after the send, before the mark
Dual write1 message, 1 item0 messages, 0 items: lost1 message, 1 item
Outbox1 message, 1 item1 message, 1 item2 messages, 2 items: duplicate
Outbox and deduplicating consumer1 message, 1 item1 message, 1 item2 messages, 1 item

Only the last row is right in every column. The outbox alone fixes the lost message and creates the duplicate; the inbox table fixes the duplicate. They are two halves of one design. Try the combinations, including a redelivery by the relay, in the lab.

Exactly-once is at-least-once plus deduplication. The relay gives at-least-once delivery; the inbox table gives the deduplication; the underwriter sees exactly one item.

For a backend engineer. This is the outbox pattern as used with Kafka and other brokers. The relay can be a polling job, as in the example, or a reader of the database's change log. Consumers of at-least-once topics keep a processed-message table for the same reason the cockpit does. What the agent adds is only the setting: the "message" here is "a recommendation is ready for a person", and losing it means a submission no person ever looks at.

When something cannot be rolled back: compensation#

The outbox makes sure a message is sent. Sometimes the problem is the reverse: a message was sent, an effect happened, and later the run decides it should not have.

Suppose the broker withdraws SUB-1001 an hour after the cockpit listed the recommendation. The cockpit item exists; an underwriter may already have opened it. Nothing in the store can roll that back, because it happened in another system. The repair is compensation: a second action that corrects the effect of the first. Here it is a message "recommendation for run-9a2c withdrawn: submission cancelled by the broker", sent through the same outbox, which the cockpit handles by removing the item from the queue and noting why.

A compensation is a new effect, not an undo. It needs its own idempotency key, it can arrive after the underwriter has already acted, and it is recorded in the event log like any other effect. Designing it means deciding, for each side effect, what its correcting action is, and what happens if the correction arrives late.

A run with several steps in several systems, each with a compensation if a later step fails, is called a saga. Deciding when to run the compensations and in which order is a control decision. This site stores what a saga needs: the effects done so far, their keys, and the compensations owed.

What this is not. Coordinating a saga, deciding the next step and which compensations to run, is orchestration: orchestrate.sammuthu.com. Retrying failed sends, backoff and dead-letter handling are reliability engineering: reliable.sammuthu.com.

The failure, shown#

The opening scene was a dual write that lost a message. Its mirror image is the outbox without a deduplicating consumer: the relay dies after a send, sends again after its restart, and the cockpit lists the same recommendation twice. Two underwriters may each pick one up. Both failures are silent: the run's status is fine, the store is fine, and only a person looking at a queue would notice. That is why the Evaluation page tests the end state across systems, not only the store.

Framework implementation#

Message brokers, workflow engines and agent frameworks differ in what they offer here: some provide an outbox or a change-log reader, some deliver at least once and document that consumers must deduplicate, some do neither. What a named product documents is on Frameworks. The design question is the same for all of them: where is the message written in the same transaction as the change, and where is the repeat dropped?

A checklist for Monday#

  1. Find every place a step writes to the database and then calls another system. Each one is a dual write.
  2. Is each outgoing message written to an outbox table in the same transaction as the change it announces?
  3. Does every consumer keep an inbox table, or an equivalent check, keyed by a message id that stays the same on resend?
  4. For each side effect that cannot be rolled back, what is its compensation, and does the compensation have its own key?
  5. Run the crash after the commit and the crash after the send in a test. Count the items the consumer shows.
Advanced: relay designs, ordering and retention

A polling relay is simple: a job selects unsent rows, sends them and marks them. It adds latency equal to its interval and load on the table. A change-log relay reads the database's own log of committed changes and needs no polling, at the cost of depending on the database's replication features and their operational care.

Ordering is the next question. If the cockpit must see "recommendation ready" before "recommendation withdrawn", the relay must send in commit order per run, and a relay that runs several workers in parallel can break that. Partitioning by run_id restores per-run order without serializing everything.

Both tables grow. Sent outbox rows can be deleted after a retention period; inbox rows must be kept at least as long as a redelivery can arrive, or a late repeat is accepted as new. The inbox's retention is therefore set by the relay's worst-case redelivery delay, which ties the two tables' settings together.

Advanced: when the consumer can be made idempotent instead

If the consumer can apply a message by a natural key, for example "set the queue item for run-9a2c to ready", a repeat is harmless without an inbox table, because the operation is idempotent by nature. That is the cheaper design when it fits. It does not fit when the consumer's effect is an add (a notification sent to a person, an item appended to a queue, a counter increased), and those are exactly the effects that matter in a human review queue. The inbox table is the general answer; a natural-key upsert is the special one.

In one breath#

A database row and a message to another system cannot be committed together, so writing both one after the other loses one of them in a crash. The transactional outbox writes the message as a row in the same transaction, a relay sends it later and may send it twice, and the consumer's inbox table ignores the repeat. When an effect in another system must be undone, a compensation corrects it with a new effect. Exactly-once is at-least-once plus deduplication, again.

The same idea for a coding agent: a commit and a comment on the pull request are two systems; the agent should record "comment owed" in its journal with the commit, and the bot that posts comments should skip a comment it has already posted.

The run's state is now safe: it resumes, it writes once, it tells the cockpit once. The second half of the site turns to the other kind of stored information: what the agent should remember for later runs, and who decides. Memory write policy.