Improving Message Delivery Reliability with the Outbox Pattern

Introduction

The previous article covered a race condition caused by enqueuing inside a transaction (hereafter TX) and the fix of moving the enqueue to after the commit. While post-commit enqueue eliminated the race condition, it left a new issue: when an enqueue fails, a PENDING record remains in the database with no automated recovery mechanism — manual intervention was the only option.

This article explains how introducing the Outbox pattern and compensating transactions resolved that issue.

Target audience: Backend engineers building processing pipelines with Go + GORM + a message queue.

Scope: This article covers separating external service writes via the Outbox pattern, ensuring idempotency via two-phase processing, and automatic recovery from enqueue failures using compensating transactions.

Terminology

The following table defines key terms used in this article. Some overlap with the previous article and are included again for reference.

TX Transaction — a unit that executes a series of database operations atomically

enqueue The operation of pushing a message onto a message queue

Consumer A service that dequeues messages from a queue and processes them

Outbox A table that temporarily holds write requests destined for an external service

Compensating transaction A TX that, upon detecting a failure, updates related records to a consistent failure state

Idempotency The property that performing the same operation multiple times produces the same result

PREPARE The preparation phase for an external write. Inserts Outbox records and enqueues an EXECUTE message

EXECUTE The execution phase for an external write. Retrieves pending Outbox records and writes to the external service

Remaining Issues with Post-Commit Enqueue

Post-commit enqueue, introduced in the previous article, left two issues unresolved.

Issue 1: PENDING Records Stuck After Enqueue Failure

With post-commit enqueue, the enqueue runs after the DB operations have been committed. If the enqueue fails, the DB operations have already been committed and cannot be rolled back. A record with an unprocessed status remains in the request table, but since no message was placed on the queue, the consumer never receives it.

The previous implementation only logged the enqueue failure; recovering PENDING records required manual effort.

Issue 2: Dual Writes from Sequential External Service Writes and DB Updates

The other issue was that external service writes and DB updates were executed sequentially within the same consumer handler.

[Consumer]
  │
  ├─ dequeue(message)
  ├─ Write to external service  ← succeeds
  ├─ UPDATE DB status           ← fails
  │
  └─ Retry
       ├─ Write to external service  ← second write (dual write)
       └─ UPDATE DB status

If the external service write succeeds but the subsequent DB update fails, the retry writes to the external service again. Unless the external service has its own idempotency mechanism, data gets created twice.

The Outbox pattern was introduced to solve both of these issues.

Overview of the Outbox Pattern

The Outbox pattern records external service write requests in an Outbox table in the database; a separate worker then reads pending records from the Outbox table and delivers them to the external service.

Because the DB operation and the Outbox INSERT happen within the same transaction, consistency between “DB state” and “external service write requests” is guaranteed. Since the actual write to the external service happens in a separate process, the dual-write problem described above can be eliminated using idempotency keys.

Outbox Table Schema

The Outbox table schema is as follows.

CREATE TABLE outboxes (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id      BIGINT UNSIGNED NOT NULL,
    target_id       BIGINT UNSIGNED NOT NULL,
    type            ENUM('EXTERNAL_SYNC') NOT NULL,
    idempotency_key VARCHAR(255) NOT NULL,
    completed_at    DATETIME NULL,
    created_at      DATETIME NOT NULL,
    updated_at      DATETIME NOT NULL
);

ColumnPurposerequest_idAssociates the record with a parent requesttarget_idIdentifies the write targettypeAn ENUM indicating the type of external serviceidempotency_keyThe idempotency key passed to the external service. Requests with the same key are treated as the same operationcompleted_atNULL means pending; a non-null value means the record has been processed

Pending records can be efficiently queried using completed_at IS NULL.

Splitting Processing into Two Phases

If the Outbox write and the external service write are executed sequentially in the same handler, Issue 2 (external write succeeds -> DB update fails -> dual write on retry) occurs. To prevent this, processing is split into two phases. The PREPARE phase creates Outbox records and idempotency keys, then enqueues an EXECUTE message. The EXECUTE phase handler dequeues that message and writes to the external service using the idempotency keys stored in the Outbox.

[PREPARE phase]
  ├─ Dequeue from queue ←──────────── Queue
  ├─ BEGIN TX
  ├─ Idempotency check (check for existing Outbox records)
  ├─ Generate idempotency keys
  ├─ Bulk INSERT into Outbox
  ├─ COMMIT
  └─ Enqueue EXECUTE message ──→ Queue

[EXECUTE phase]
  ├─ Dequeue from queue ←──────────── Queue
  ├─ Retrieve PENDING records from Outbox
  ├─ Bulk write to external service (idempotent via idempotency_key)
  ├─ BEGIN TX
  ├─ BulkComplete (update completed_at)
  ├─ UpdateFinalStatus (update final status)
  └─ COMMIT

PREPARE Phase

The PREPARE phase executes the following operations within a single TX.

func (s *Service) Prepare(ctx context.Context, tx *gorm.DB, requestID uint) error {
    // 1. Idempotency check: skip if Outbox records already exist
    var count int64
    if err := tx.Model(&Outbox{}).
        Where("request_id = ?", requestID).
        Count(&count).Error; err != nil {
        return err
    }
    if count > 0 {
        return nil // Already prepared
    }

    // 2. Retrieve targets
    var targets []Target
    if err := tx.Where("request_id = ?", requestID).
        Find(&targets).Error; err != nil {
        return err
    }
    // 3. Generate idempotency keys and create Outbox records
    outboxes := make([]Outbox, len(targets))
    for i, t := range targets {
        outboxes[i] = Outbox{
            RequestID:      requestID,
            TargetID:       t.ID,
            Type:           "EXTERNAL_SYNC",
            IdempotencyKey: fmt.Sprintf("%d-%d", requestID, t.ID),
        }
    }
    // 4. Bulk INSERT
    if err := tx.Create(&outboxes).Error; err != nil {
        return err
    }
    return nil
}

The idempotency check in the PREPARE phase prevents duplicate Outbox records from being created on retry. If Outbox records already exist, PREPARE completes without doing anything.

EXECUTE Phase

The EXECUTE phase retrieves pending Outbox records, writes to the external service, and reflects the results in the database.

func (s *Service) Execute(ctx context.Context, requestID uint) error {
    // 1. Retrieve PENDING Outbox records
    var outboxes []Outbox
    if err := s.db.
        Where("request_id = ? AND completed_at IS NULL", requestID).
        Find(&outboxes).Error; err != nil {
        return err
    }
    if len(outboxes) == 0 {
        return nil // No pending records
    }

    // 2. Bulk write to external service (idempotent via idempotency_key)
    results := s.externalClient.BulkWrite(ctx, outboxes)
    // 3. Run BulkComplete + UpdateFinalStatus in the same TX
    return s.db.Transaction(func(tx *gorm.DB) error {
        // Update completed_at for succeeded + permanently failed Outbox records.
        // Retryable errors remain with completed_at = NULL so they are picked up next time.
        completedIDs := append(results.SucceededIDs(), results.PermanentFailedIDs()...)
        if len(completedIDs) > 0 {
            if err := tx.Model(&Outbox{}).
                Where("id IN ?", completedIDs).
                Update("completed_at", time.Now()).Error; err != nil {
                return err
            }
        }
        // Update final status (determine success / partial failure / failure)
        if err := tx.Model(&Request{}).
            Where("id = ?", requestID).
            Update("status", determineFinalStatus(results)).Error; err != nil {
            return err
        }
        return nil
    })
}

BulkComplete (setting completed_at) and UpdateFinalStatus (updating the final status) are executed within the same TX. If they ran in separate TXs and one succeeded while the other failed, data inconsistency would arise. For example, if completed_at was updated but the final status was not, processed records would not be resent on retry while the status would remain as unprocessed.

Ensuring Idempotency

Idempotency is ensured at two levels: the application level and the external service level.

LevelMechanismEffectApplicationOutbox existence check skips re-execution of PREPAREPrevents duplicate Outbox creation for the same requestExternal serviceUpsert via idempotency_keyRequests with the same key are treated as the same operation

The idempotency_key is generated from the combination of requestID and targetID, so the same key is sent to the external service on retry. The external service performs an upsert using this key, preventing duplicate writes.

Retry and Idempotency Design

Native Queue Retry

Message queues use a lock period (visibility timeout). If a consumer does not complete processing of a message, the message is redelivered after the lock period expires. This provides automatic retry even when a consumer fails.

Determining Whether to Continue Retrying

On retry during the EXECUTE phase, the number of Outbox records with completed_at IS NULL determines whether a retry is needed.

// Retrieve PENDING Outbox records
var outboxes []Outbox
if err := s.db.
    Where("request_id = ? AND completed_at IS NULL", requestID).
    Find(&outboxes).Error; err != nil {
    return err
}
if len(outboxes) == 0 {
    return nil // All processed — no retry needed
}

If some Outbox records were marked as processed in a previous EXECUTE, only the still-pending records are targeted on retry. Processed records have completed_at set and are not resent.

Error Classification

Responses from the external service are classified to determine whether a retry is appropriate.

Status CodeClassificationAction200, 201SuccessUpdate completed_at to mark as processed429, 503RetryableLeave completed_at unset; resend in the next EXECUTE400Permanent errorUpdate completed_at to mark as processed; record as failed

For retryable errors (429: Too Many Requests, 503: Service Unavailable), the Outbox record remains pending. It is picked up again in the next EXECUTE phase and resent to the external service.

For permanent errors (400: Bad Request), the request content itself is problematic, so retrying will not succeed. The record is marked as processed by updating completed_at, and the failure is reflected in the final status.

Status Transitions and Error Handling

Request status transitions are as follows.

Pending ──→ Processing ──→ Succeeded
                        ├─→ Partial failure
                        └─→ Failed

Final Status Determination Logic

The final status is determined based on the results of the EXECUTE phase. The logic branches on the combination of success count and permanent error count.

ConditionFinal StatusMeaning0 permanent errorsSucceededAll Outbox records were processed successfully1+ successes and 1+ permanent errorsPartial failureSome Outbox records encountered permanent errors0 successesFailedAll Outbox records encountered permanent errors

In the partial failure case, both succeeded and permanently failed records have completed_at updated, so only the retryable-error records are targeted for resend on retry.

Compensating Transaction on Enqueue Failure

The “PENDING records stuck after enqueue failure” issue left open in the previous article is resolved with a compensating transaction.

Before: Logging Only

The previous implementation only logged the failure when an enqueue failed.

// Before: only log on enqueue failure
if err := s.queueRepo.Enqueue(ctx, body); err != nil {
    s.logger.WithField("request_id", req.ID).
        WithError(err).
        Error("failed to enqueue after commit")
    return nil
}

In this implementation, the request record remains unprocessed and the parent record’s status is never updated. The operations team had to monitor logs and recover manually.

After: Automatic Recovery via Compensating Transaction

The updated implementation detects an enqueue failure and executes a compensating transaction, automatically updating related records to a consistent failure state.

// After: execute compensating transaction on enqueue failure
if err := s.queueRepo.Enqueue(ctx, body); err != nil {
    s.logger.WithField("request_id", req.ID).
        WithError(err).
        Error("failed to enqueue after commit")

// Compensating transaction: update related records to failed state
if compErr := s.db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Model(&Request{}).
        Where("id = ?", req.ID).
          Update("status", statusFailed).Error; err != nil {
            return err
          }
    if err := tx.Model(&ParentRecord{}).
        Where("id = ?", req.ParentID).
          Update("status", statusFailed).Error; err != nil {
            return err
     }
        return nil
}); compErr != nil {
        s.logger.WithError(compErr).
            Error("failed to execute compensation transaction")
}
    return nil // Intentionally do not return an error to the caller
}

There are three key points about the compensating transaction.

1. Two UPDATEs in a single TX

The request status update and the parent record status update are executed within the same TX. If only one succeeds and the other fails, inconsistency arises — for example, the request is marked as failed but the parent record remains unprocessed.

2. Intentionally do not return an error to the caller

After executing the compensating transaction, no error is intentionally returned to the caller. The DB operations have already been committed, so returning an error and retrying would not improve the situation. If the compensating transaction itself fails, the failure is logged for operational follow-up.

3. Handles enqueue failure at both PREPARE and EXECUTE stages

In the Outbox pattern, enqueue occurs in two places: the initial PREPARE message enqueue and the EXECUTE message enqueue after the PREPARE phase. If either enqueue fails, the same compensating transaction updates related records to a failure state.

[Initial enqueue failure (cannot send PREPARE message)]
  Compensating TX: request = failed, parent record = failed

[PREPARE succeeds → enqueue failure (cannot send EXECUTE message)]
  Compensating TX: request = failed, parent record = failed

Test Changes

The introduction of compensating transactions changes the assertions in test cases for enqueue failure scenarios.

Before

Previously, the test verified that an enqueue failure returned an error and the DB was rolled back (in the post-commit enqueue case from the previous article, the test verified that no error was returned but the status remained PENDING).

{
    name: "enqueue failure does not return error and leaves PENDING record",
    setupMock: func(m *MockQueueRepo) {
        m.EXPECT().
            Enqueue(gomock.Any(), gomock.Any()).
            Return(errors.New("queue unavailable"))
    },
    assertDB: func(t *testing.T, db *gorm.DB) {
        var req Request
        db.First(&req)
        assert.Equal(t, statusPending, req.Status) // Remains unprocessed
    },
    wantErr: false,
},

After

Now, the test verifies that an enqueue failure does not return an error and that the compensating transaction updates the status to failed.

{
    name: "enqueue failure does not return error; compensating TX updates to failed",
    setupMock: func(m *MockQueueRepo) {
        m.EXPECT().
            Enqueue(gomock.Any(), gomock.Any()).
            Return(errors.New("queue unavailable"))
    },
    assertDB: func(t *testing.T, db *gorm.DB) {
        var req Request
        db.First(&req)
        assert.Equal(t, statusFailed, req.Status) // Updated to failed by compensating TX

        var parent ParentRecord
        db.First(&parent)
        assert.Equal(t, statusFailed, parent.Status) // Parent record also failed
    },
    wantErr: false,
},

There are two changes.

1. Status assertion change: Pending -> Failed. The compensating transaction now automatically updates the state to failed on enqueue failure.
2. Parent record verification added: Verifies that the compensating transaction atomically updates the parent record as well.

Potential Future Improvements

The design described in this article transitions a request to a failure state via a compensating transaction when an enqueue fails. This design is simple to implement but carries a structural constraint: processing cannot proceed if the queue is down.

One direction for relaxing this constraint is to treat the Outbox table as the system’s true source of truth and position the queue as a supplementary mechanism that accelerates processing.

Not Treating Enqueue Failure as “Failure”

The current design transitions a request to a failure state on enqueue failure, but as long as a record remains in the Outbox, it can potentially be processed later. Treating “not enqueued” as “not yet processed” rather than “failed” improves system recoverability.

Self-Recovery via Polling Worker

To implement this design, a polling worker (sweeper) that periodically scans for Outbox records with completed_at IS NULL is needed. Even if an enqueue fails or the queue is temporarily down, the sweeper picks up unprocessed records and advances processing. The queue becomes an optimization layer that provides real-time delivery under normal conditions, while the sweeper guarantees eventual processing completion.

Shifting the Role of Compensating Transactions

In this direction, the purpose of compensating transactions also changes. Currently, they are used to “finalize a failure,” but with a focus on recoverability, they would instead be used to “restore the system to a retryable state.” Compensation becomes not about “giving up” but about “ensuring the system can try again.”

This design sacrifices some simplicity but is effective when the goal is a system that eventually converges on success given enough time, even after failures.

Summary

The improvements from the previous article through this one can be organized in three stages.

StageIssue ResolvedRemaining IssueEnqueue inside TX — Race condition (consumer cannot see the record)Post-commit enqueueRace conditionPENDING records stuck on enqueue failureOutbox + compensating TXStuck PENDING records, dual writes to external service —

The key design points covered in this article are summarized below.

• Write separation via the Outbox pattern: By recording external service write requests in the Outbox table and delivering them through a separate worker, consistency between DB operations and external writes is maintained while preventing dual writes.
• Two-phase processing separation: The PREPARE phase prepares Outbox records and idempotency keys, and the EXECUTE phase uses those idempotency keys to write to the external service, preventing dual writes.
• Two-layer idempotency: Application-level Outbox existence checks combined with external-service-level idempotency keys prevent duplicate processing on retry.
• Automatic recovery via compensating transactions: By atomically updating related records to a failure state on enqueue failure, manual recovery of PENDING records is no longer necessary.