Imagine an online store processing a customer order.
The application saves the order in SQLite, updates the inventory, and publishes an event notifying other services that a new order has been placed.
Everything appears straightforward.
The order is saved. The inventory is updated. The event is published.
But what happens if the application crashes after saving the order and before publishing the event?
The database contains the new order, but the notification never reaches the payment service, warehouse, or shipping system.
Now consider the opposite situation.
The application publishes the event successfully, but crashes before committing the database transaction.
Other services receive a notification about an order that does not exist in the database.
Both situations create the same fundamental problem: the database and the messaging system disagree about what happened.
In our previous articles, we explored durable job queues, safe retries, and backpressure. Those techniques help applications preserve work, recover from failures, and control overload.
But they do not automatically guarantee consistency between a database transaction and a message published to an external system.
That requires another architectural pattern.
The Transactional Outbox Pattern solves this problem by recording business changes and outgoing events together in a single SQLite transaction.
Instead of attempting to update the database and publish a message as two separate operations, the application first makes both intentions durable locally.
A background publisher then delivers the event.
In this article, we’ll build a transactional outbox using SQLite, examine its failure-recovery behavior, implement safe event claiming and retries, and explore how to maintain reliable event delivery in production.
The Dual-Write Problem
A dual write occurs when one business operation must update two independent systems.
For example:
Insert an order into SQLite.
Publish an
OrderCreatedevent to a message broker.
A typical implementation might look like this:
BEGIN TRANSACTION
Insert Order
COMMIT
Publish OrderCreated EventAt first glance, this looks reasonable.
The database transaction guarantees that the order is stored correctly.
But the message publication happens outside that transaction.
If the application crashes between the commit and the publish operation, the event is lost.
What If We Publish First?
We could reverse the sequence:
Publish OrderCreated Event
BEGIN TRANSACTION
Insert Order
COMMITThis creates a different problem.
If publishing succeeds but the database transaction fails, other services may act on an event describing an order that was never committed.
What If We Publish Inside the Transaction?
Another approach is to keep the SQLite transaction open while publishing:
BEGIN TRANSACTION
Insert Order
Publish OrderCreated Event
COMMITUnfortunately, this still does not provide atomicity across the two systems.
The broker may accept the message, followed by a database commit failure.
Or the broker may accept the message but the publisher may lose the acknowledgment and mistakenly assume delivery failed.
SQLite cannot roll back an event that an independent messaging system has already accepted.
Holding the write transaction open during network communication also increases lock contention.
The problem is not the order of operations.
The problem is that SQLite and the external message broker do not share one atomic transaction.
How the Transactional Outbox Pattern Works
The transactional outbox introduces a local table for outgoing events.
Instead of publishing an event immediately, the application stores it alongside the business change.
Both records are committed in one SQLite transaction.
Business Operation
|
v
SQLite Transaction
|
+---- Update Business Data
|
+---- Insert Outbox Event
|
v
COMMIT
|
v
Background Publisher
|
v
External Message Broker
|
v
Receiving ServicesThis changes the failure model.
If the transaction fails, neither the business change nor the outbox event is committed.
If the transaction succeeds, both are durable.
If the application crashes after committing, the outbox event remains available for later publication.
The external publication is still a separate operation, but it can now be retried without losing the original event.
This is the foundation of the pattern.
Designing the Business Tables
Let’s build an example around an order-processing application.
The business database contains two tables.
Example:
CREATE TABLE Orders (
OrderID INTEGER PRIMARY KEY,
CustomerID INTEGER NOT NULL,
TotalAmount INTEGER NOT NULL,
Status TEXT NOT NULL
CHECK (Status IN (
'created',
'paid',
'cancelled'
)),
CreatedAt INTEGER NOT NULL
);For this example, TotalAmount stores the amount in the currency’s smallest unit, such as cents.
The inventory table tracks available quantities.
CREATE TABLE Inventory (
ProductID INTEGER PRIMARY KEY,
AvailableQuantity INTEGER NOT NULL
CHECK (AvailableQuantity >= 0)
);When a customer places an order, the application must:
Confirm sufficient inventory.
Reduce the available quantity.
Insert the order.
Record an
OrderCreatedevent.
All four actions belong to one logical business operation.
The outgoing event should only exist if the business operation commits successfully.
Creating the SQLite Outbox Table
The outbox table stores messages awaiting publication.
Example:
CREATE TABLE OutboxEvents (
EventID TEXT PRIMARY KEY,
AggregateType TEXT NOT NULL,
AggregateID TEXT NOT NULL,
EventType TEXT NOT NULL,
Payload TEXT NOT NULL
CHECK (json_valid(Payload)),
Status TEXT NOT NULL DEFAULT 'pending'
CHECK (Status IN (
'pending',
'publishing',
'published',
'failed'
)),
CreatedAt INTEGER NOT NULL,
NextAttemptAt INTEGER NOT NULL,
AttemptCount INTEGER NOT NULL DEFAULT 0,
MaxAttempts INTEGER NOT NULL DEFAULT 10,
LeaseOwner TEXT,
LeaseToken TEXT,
LeaseExpiresAt INTEGER,
PublishedAt INTEGER,
LastError TEXT
);The important fields are:
Column Purpose
EventID Stable, globally unique event identifier
AggregateType Business entity category
AggregateID Business entity associated with the event
EventType Type of event being published
Payload Serialized event data
Status Current publication state
NextAttemptAt Earliest time the event can be retried
AttemptCount Number of publication attempts
LeaseToken Identifies the current publishing claim
LeaseExpiresAt Allows recovery from abandoned claims
PublishedAt Time successful publication was recordedThe example uses Unix timestamps in milliseconds.
The application should generate EventID once, when the event is created, and preserve it across every retry.
A UUID is a suitable choice.
The stable event identifier is essential because the same event may be published more than once.
Saving Business Data and the Event Together
Now let’s implement the actual transactional outbox operation.
Assume a customer orders two units of product 501.
The application begins a SQLite write transaction.
BEGIN IMMEDIATE;First, reduce inventory only if sufficient stock exists.
UPDATE Inventory
SET AvailableQuantity = AvailableQuantity - 2
WHERE ProductID = 501
AND AvailableQuantity >= 2;The application must verify that exactly one row was updated.
If no row was updated, the order cannot proceed.
The transaction should be rolled back.
Next, insert the order.
INSERT INTO Orders (
CustomerID,
TotalAmount,
Status,
CreatedAt
)
VALUES (
1201,
4999,
'created',
:now
)
RETURNING OrderID;The application captures the returned OrderID.
Then it inserts the outgoing event.
INSERT INTO OutboxEvents (
EventID,
AggregateType,
AggregateID,
EventType,
Payload,
CreatedAt,
NextAttemptAt
)
VALUES (
:event_id,
'Order',
:order_id_text,
'OrderCreated',
json_object(
'eventId', :event_id,
'orderId', :order_id,
'customerId', 1201,
'totalAmount', 4999
),
:now,
:now
);Finally:
COMMIT;The application must treat the complete sequence as one transaction.
If inventory validation fails, the order insert fails, or the outbox insert fails, the transaction must not commit.
If the transaction succeeds, the order and its outgoing event are committed together.
This gives us a crucial guarantee:
A committed order always has a corresponding committed outbox event, provided every order-creation path follows this protocol.
Notice what the transaction does not guarantee.
It does not guarantee that the external message has already been published.
It guarantees that the application has durably recorded the responsibility to publish it.
That distinction is central to understanding the transactional outbox.
Why the Outbox Must Share the Same Database Transaction
The outbox table must participate in the same atomic transaction as the business change.
The transaction and locking principles covered in Mastering Transactions and Concurrency in SQLite explain how SQLite keeps related database changes consistent. These same guarantees allow the transactional outbox to commit business records and outgoing events together, ensuring that neither is saved independently.
Storing the business record in one SQLite database and the outbox event in an unrelated database connection or separate transaction would lose this guarantee.
For example:
orders.db
|
+---- Commit Order
events.db
|
+---- Commit Outbox EventIf these commits happen independently, the application still has a dual-write problem.
For the architecture in this article, both tables live in the same SQLite database and are updated through one connection and one transaction.
SQLite then provides the local atomicity that the pattern depends on.
Building the Background Outbox Publisher
Once the business transaction commits, a separate publisher processes pending events.
The publisher can run:
Inside the application as a background service.
In a dedicated worker process.
In multiple coordinated worker processes.
Its responsibilities are straightforward:
Find events ready for publication.
Claim an event safely.
Publish it to the external system.
Record successful publication.
Retry temporary failures.
Recover events abandoned by crashed workers.
The publisher must be restart-safe.
If it stops unexpectedly, committed events must remain available for recovery.
Claiming Events Without Duplicate Workers
When multiple publishers share one SQLite database, they must coordinate event claims.
Otherwise, two workers may select the same pending event simultaneously.
We can use a short SQLite write transaction to claim one event.
Example:
BEGIN IMMEDIATE;
UPDATE OutboxEvents
SET
Status = 'publishing',
LeaseOwner = :worker_id,
LeaseToken = :new_lease_token,
LeaseExpiresAt = :lease_expires_at,
AttemptCount = AttemptCount + 1
WHERE EventID = (
SELECT EventID
FROM OutboxEvents
WHERE Status = 'pending'
AND NextAttemptAt <= :now
ORDER BY CreatedAt, EventID
LIMIT 1
)
RETURNING
EventID,
EventType,
Payload,
LeaseToken;
COMMIT;This approach uses SQLite’s RETURNING clause, available in SQLite 3.35.0 and later.
The claim transaction should be committed before the worker performs any network operation.
The publisher then sends the event to the broker.
The transaction must not remain open while waiting for an external acknowledgment.
The lease identifies the worker currently responsible for that publication attempt.
It does not guarantee that an older worker can no longer send a message to the broker. External publication may still overlap after a lease expires, so consumers must tolerate duplicates.
Publishing Events and Recording Success
After claiming an event, the publisher sends its payload to the external broker.
A typical publisher workflow looks like this:
Claim event in SQLite
|
v
Commit claim transaction
|
v
Publish event to broker
|
v
Wait for broker acknowledgment
|
+---- Success ----> Mark published
|
+---- Failure ----> Schedule retryThe publisher must use the broker’s documented acknowledgment mechanism. Sending bytes to a network socket is not sufficient evidence that the broker durably accepted the message.
After a successful acknowledgment, the publisher records completion.
Example:
UPDATE OutboxEvents
SET
Status = 'published',
PublishedAt = :now,
LeaseOwner = NULL,
LeaseToken = NULL,
LeaseExpiresAt = NULL,
LastError = NULL
WHERE EventID = :event_id
AND Status = 'publishing'
AND LeaseToken = :lease_token
AND LeaseExpiresAt > :now;The application must check that exactly one row was updated.
If no row was updated, the publisher may have lost its lease. It must not overwrite another worker’s state.
The event may already have reached the broker, so a subsequent retry can produce a duplicate.
That behavior is expected in an at-least-once delivery design.
Understanding the Most Important Failure Scenario
Consider this sequence:
1. Worker claims event
2. Worker publishes event
3. Broker accepts event
4. Worker crashes
5. SQLite still says "publishing"The event has reached the broker, but SQLite does not know that publication succeeded.
When another worker recovers the event, it publishes the same event again.
The broker may receive two copies.
The transactional outbox cannot eliminate this ambiguity because the broker acknowledgment and the SQLite completion update are separate operations.
This is why the pattern provides durable publication attempts with at-least-once delivery, rather than automatic exactly-once delivery.
The receiving service must recognize duplicates.
Making Event Consumers Idempotent
A receiving service should process the same event only once, even when delivery is repeated.
In Designing Idempotent Operations with SQLite, we explored how stable identifiers and database constraints prevent repeated requests from producing duplicate business effects. The same principles apply to event consumers, allowing them to recognize previously processed events and safely handle messages delivered more than once.
One approach is to maintain a table of processed event identifiers.
Example:
CREATE TABLE ProcessedEvents (
EventID TEXT PRIMARY KEY,
ProcessedAt INTEGER NOT NULL
);When a consumer receives an event, it begins a transaction.
BEGIN IMMEDIATE;
INSERT INTO ProcessedEvents (
EventID,
ProcessedAt
)
VALUES (
:event_id,
:now
)
ON CONFLICT(EventID) DO NOTHING;The consumer checks whether the insert affected one row.
If it did, this is the first recorded processing attempt. The consumer performs its local business updates within the same transaction.
If it affected zero rows, the event was already processed, so the consumer can skip the duplicate.
Finally:
COMMIT;The consumer acknowledges the broker message only after the database transaction commits.
This is important because the processed-event marker and the business effect must succeed or fail together.
If the consumer records the marker but crashes before applying the business change, the next delivery could be incorrectly skipped.
For consumers that call external services, a local transaction is not enough to make those external effects atomic. Such consumers need an additional idempotency strategy, such as a stable request key supported by the downstream service.
The transactional outbox and idempotent consumer patterns work together to make repeated delivery safe. This is also a central recommendation in AWS Prescriptive Guidance on the transactional outbox pattern.
Recovering Abandoned Publishing Attempts
Suppose a worker claims an event and crashes before publishing it.
We addressed a similar recovery challenge in Building Durable Job Queues with SQLite, where worker leases and expiration timestamps help recover tasks abandoned after a crash. Applying these techniques to an outbox publisher ensures that events claimed by failed workers can return to the publication queue instead of remaining stuck indefinitely.
Without recovery, the event could remain in the publishing state indefinitely.
A lease provides a recovery mechanism.
For example:
Event claimed at: 10:00:00
Lease expires at: 10:00:30
Worker crashes at: 10:00:05
Recovery worker checks at: 10:00:35The recovery worker identifies the expired lease and makes the event eligible for another attempt.
Example:
UPDATE OutboxEvents
SET
Status = 'pending',
LeaseOwner = NULL,
LeaseToken = NULL,
LeaseExpiresAt = NULL,
NextAttemptAt = :now
WHERE Status = 'publishing'
AND LeaseExpiresAt <= :now
AND AttemptCount < MaxAttempts;Events that have exhausted their allowed attempts need a separate policy.
They may be moved to failed for investigation, or retried under an operator-controlled recovery procedure.
An exhausted retry count should not cause a required business event to be silently deleted.
Also, lease expiration does not prove that the original worker has stopped running. An old worker may still publish after its lease expires.
A lease token protects the SQLite state transition, but duplicate-tolerant consumers remain necessary.
Implementing Retry Backoff
Not every publication failure requires immediate intervention.
Temporary failures include network interruptions, broker unavailability, and rate limits.
These failures can be retried.
However, immediate repeated retries can overwhelm an already unhealthy messaging service.
Use exponential backoff with jitter.
For example:
Attempt Maximum randomized delay
1 5 seconds
2 10 seconds
3 20 seconds
4 40 seconds
5 80 secondsThe application calculates a new NextAttemptAt value.
After a confirmed failed publication attempt, it can update the event:
UPDATE OutboxEvents
SET
Status = 'pending',
NextAttemptAt = :retry_at,
LastError = :error,
LeaseOwner = NULL,
LeaseToken = NULL,
LeaseExpiresAt = NULL
WHERE EventID = :event_id
AND Status = 'publishing'
AND LeaseToken = :lease_token
AND LeaseExpiresAt > :now;If the failure is permanent, the publisher should record that condition rather than retrying indefinitely without intervention.
For example, an invalid event schema may require a code or data correction.
The publisher should also limit concurrent network requests and respect broker rate limits.
Backpressure remains important even after the outbox has made publication durable.
Preserving Event Ordering
Ordering introduces another challenge.
Imagine an order changing state three times:
OrderCreated
|
v
OrderPaid
|
v
OrderShippedThese events have a meaningful business sequence.
If several publishers operate concurrently, OrderShipped might reach the broker before OrderPaid.
Sorting pending events by creation time is not sufficient to guarantee publication order when multiple workers are active, retries occur, or delivery is delayed.
Use Aggregate Versions
One approach is to assign a version number to every change of a business aggregate.
For example:
AggregateID Version Event
Order-501 1 OrderCreated
Order-501 2 OrderPaid
Order-501 3 OrderShippedThe version should be assigned transactionally when the business state changes.
A useful schema extension is:
ALTER TABLE OutboxEvents
ADD COLUMN AggregateVersion INTEGER;For a new deployment, the version can be made mandatory in the original schema, together with a uniqueness constraint on (AggregateType, AggregateID, AggregateVersion).
The publisher can then enforce per-aggregate sequencing.
However, a version column alone does not guarantee ordering. The scheduling logic must prevent a later event from being published before an earlier event is acknowledged.
This may reduce concurrency for events belonging to the same aggregate, while allowing unrelated aggregates to publish independently.
Consumers should also use aggregate versions when they need to detect stale or out-of-order events.
Indexing the Outbox for Efficient Publication
A growing outbox may contain thousands or millions of records.
The publisher should not repeatedly scan the entire table.
A partial index can help identify pending events efficiently.
Example:
CREATE INDEX idx_outbox_pending
ON OutboxEvents (
NextAttemptAt,
CreatedAt,
EventID
)
WHERE Status = 'pending';Another index supports expired-lease recovery:
CREATE INDEX idx_outbox_expired_leases
ON OutboxEvents (
LeaseExpiresAt
)
WHERE Status = 'publishing';These indexes support the corresponding filtering conditions.
The best index still depends on actual query patterns and data distribution.
Use EXPLAIN QUERY PLAN to examine the publisher’s queries under realistic workloads.
Avoid indexing every column. Additional indexes increase storage usage and write costs, which matter when business transactions are continuously inserting outbox events.
Preventing the Outbox from Growing Indefinitely
Successfully published events should not remain in the active outbox forever unless a retention requirement demands it.
A retention policy might preserve published events for seven days, then delete them in small batches.
For example:
DELETE FROM OutboxEvents
WHERE EventID IN (
SELECT EventID
FROM OutboxEvents
WHERE Status = 'published'
AND PublishedAt < :cutoff
ORDER BY PublishedAt
LIMIT 500
);Run cleanup in controlled batches rather than deleting millions of rows in one transaction.
A supporting index may improve retention queries:
CREATE INDEX idx_outbox_published
ON OutboxEvents (PublishedAt)
WHERE Status = 'published';Retention must distinguish successfully published events from events that still require attention.
Never delete pending events merely because they are old.
Likewise, permanently failed events may need to remain available for investigation or manual replay.
Remember that deleting rows from SQLite does not necessarily reduce the database file size immediately. Database file maintenance and free-page reuse are separate concerns.
Monitoring Outbox Health
A transactional outbox can appear healthy while events are accumulating silently.
Useful metrics include:
Number of pending events
Age of the oldest pending event
Publication attempts per minute
Successfully published events per minute
Retry rate
Number of expired leases
Permanently failed events
Average time from business commit to broker acknowledgment
Outbox table growth
Available disk space
For example:
SELECT
Status,
COUNT(*) AS EventCount
FROM OutboxEvents
GROUP BY Status;This gives a quick view of the publication lifecycle.
To inspect delayed events:
SELECT
EventID,
EventType,
CreatedAt,
AttemptCount,
LastError
FROM OutboxEvents
WHERE Status = 'pending'
AND NextAttemptAt <= :now
ORDER BY CreatedAt
LIMIT 20;A high pending count is not automatically a failure.
But steadily increasing event age, repeated publication errors, or exhausted retry attempts indicate that the system is not keeping up.
Monitoring should focus on whether committed business events are being delivered within the application’s required time limits.
What the Transactional Outbox Does Not Solve
The transactional outbox is powerful, but it has clear boundaries.
It does not provide exactly-once delivery. Duplicate publication remains possible after crashes and acknowledgment failures.
It does not automatically preserve ordering. Applications requiring ordered events need explicit sequencing rules.
It does not make external operations part of a SQLite transaction. It records the responsibility to perform them reliably.
It does not guarantee immediate delivery. Publication is asynchronous, so receiving services may observe business changes later.
It does not replace consumer idempotency. Consumers must handle repeated messages safely.
It does not remove capacity limits. A broker outage can cause the outbox to grow, making backpressure and storage monitoring necessary.
Understanding these boundaries prevents developers from confusing reliable event publication with stronger guarantees that the architecture does not provide.
When Should You Use the Transactional Outbox Pattern?
The pattern is particularly useful when an application must update SQLite and notify another system as part of the same business operation.
Examples include:
E-commerce: Save an order and publish an event for payment or fulfillment.
Financial applications: Record a transaction and notify a reporting or reconciliation service.
IoT platforms: Persist device events locally and publish them when connectivity becomes available.
Microservices: Update service-owned data and notify other services about committed changes.
Audit workflows: Record important state changes and publish corresponding audit events.
Notification systems: Save an application event and reliably schedule email, SMS, or push notification processing.
For simple applications that never communicate business changes to another system, an outbox may introduce unnecessary complexity.
But when database changes and outgoing messages must remain connected through crashes and retries, it provides a practical and well-defined reliability boundary.
Best Practices for a Production-Ready SQLite Outbox
Store business changes and outgoing events in the same SQLite transaction.
Generate a stable, unique identifier for every event.
Keep transactions short and avoid network calls while holding SQLite write locks.
Claim events atomically before publication.
Use leases and recovery logic for abandoned publication attempts.
Record successful publication only after the broker acknowledges acceptance.
Assume duplicate delivery and make consumers idempotent.
Use backoff and jitter for temporary publication failures.
Enforce ordering explicitly when business requirements demand it.
Index pending events and expired leases for efficient processing.
Retain failed events for investigation and controlled replay.
Monitor delivery latency, retries, backlog growth, and storage capacity.
Test crashes before publication, after publication, and before recording acknowledgment.
Document the guarantees provided by both the publisher and the external broker.
Closing Thoughts
The transactional outbox addresses one of the most difficult reliability problems in event-driven applications.
A business transaction may succeed while its corresponding message fails to publish.
Or a message may reach another system even though the application cannot confirm that publication succeeded.
Trying to make these independent operations behave like one distributed transaction creates fragile assumptions.
The transactional outbox takes a different approach.
It uses SQLite’s local transaction guarantees to save business data and the intention to publish an event together.
A background publisher then handles external delivery, retries, crashes, and recovery.
The result is a system in which committed business changes do not lose their associated publication records, even when the application stops unexpectedly.
The most important principle is:
Make the event durable in the same transaction as the business change, then make delivery retryable and duplicate-safe.
This pattern connects naturally with everything we have explored in the series: durable queues, idempotent operations, backpressure, worker leases, and failure recovery.
Together, these techniques provide the foundation for reliable event-driven applications built around SQLite.
But there is another performance challenge waiting beneath the surface.
Every business transaction, outbox insertion, index update, retry, and cleanup operation changes data stored on disk.
And the amount of physical writing performed by SQLite can be much larger than the amount of application data being changed.
Understanding that difference is essential when optimizing write-heavy applications.
Coming Next: Understanding SQLite Write Amplification
Exploring how small database changes create additional disk writes, and how to reduce unnecessary I/O in write-intensive SQLite applications.
Subscribe Now
Build Event-Driven SQLite Applications That Never Lose Track of Important Events
Reliable applications do more than save data. They ensure that important events are recorded, published, and recovered safely, even when services crash or networks fail.
Subscribe to SQLite Forum for practical tutorials on transactional outbox patterns, event-driven architecture, idempotent processing, durable job queues, and production-ready SQLite systems.
Subscribe Now and keep building SQLite applications that deliver reliable results, even when things go wrong.