Transactional outbox for SMS: avoiding lost notifications and duplicates

A common integration first saves an order and then calls an SMS platform over HTTP or SMPP. If the process stops between those actions, the order has no notification. If the platform accepted the request but its response was lost, a retry may create a duplicate. The transactional outbox removes this gap between business data and the submission task.

Why a normal dual write fails

Consider both operation orders.

Database first, SMS second. The transaction commits, but the process fails before calling the platform. The business event exists; the submission task does not.

SMS first, database second. The platform accepts the message, but the database transaction rolls back. The user receives a notification about an event the system did not preserve.

A distributed transaction with an external SMS platform is normally unavailable and would be operationally expensive. The outbox keeps atomicity inside the application’s own database.

The basic outbox flow

In one transaction, the application:

1. changes the business object;
2. inserts a row into notification_outbox;
3. commits both records together.

A separate worker selects new jobs, submits them to the SMS platform and records the outcome. A rollback leaves neither the business change nor its task. A stopped worker leaves durable rows that can be processed after recovery.

Useful columns include:

• a unique business event_id;
• event type and schema version;
• recipient and template reference instead of uncontrolled final text;
• template parameters in a governed format;
• priority, TTL and scheduled time;
• status, attempt count and next_attempt_at;
• platform identifier and last error;
• created_at, sent_at and updated_at.

Do not put secrets or unnecessary personal data in the outbox. Restrict read access and align retention with the journal’s purpose and internal data-handling rules.

Idempotency starts with the business event

A unique key should mean “one notification for this event and recipient”, perhaps order_id + status + recipient + template_version. A random UUID generated for every attempt does not protect against producing the same event twice.

Enforce the chosen key with a unique database constraint. If the SMS API accepts an idempotency key, pass the same stable identifier, but do not assume its semantics without the documentation and tests for that interface.

Idempotency must still allow legitimate repeated notifications. Include a reminder step or time bucket in the key, and use a new authentication-attempt identifier for a fresh OTP.

How workers claim jobs

Several workers may read the table concurrently. Prevent two workers from submitting the same row by locking selected records or changing their state atomically. The mechanism depends on the database, but the claim should be brief and recoverable after a process failure.

Do not keep a database transaction open during a network request. A practical sequence is:

1. claim a small batch and mark it processing with a lease deadline;
2. commit the claim;
3. call the platform;
4. record the result in another short transaction;
5. return an expired lease to the queue if its worker disappeared.

Match batch size and concurrency to SMS throughput and TPS limits. Reading the whole table without control merely moves overload from the application to the SMS route.

Statuses and retry policy

A minimum state model distinguishes:

• pending — ready for processing;
• processing — leased by a worker;
• accepted — the platform returned an identifier;
• retry — a temporary failure with a scheduled next attempt;
• failed — a permanent error;
• expired — the TTL has elapsed;
• cancelled — the business event was cancelled before submission.

Retry only temporary failures, such as a network timeout, rate limit or short outage. An invalid recipient, forbidden sender ID or template error needs corrected data rather than an endless retry.

Use exponential backoff with jitter, a maximum attempt count and a DLQ or separate manual-review state. Check TTL before every attempt: an expired OTP should not reach the user when a queue recovers.

Accepted is not delivered

A successful HTTP response or submit_sm_resp means the platform accepted the request. Final handset delivery arrives later through a webhook or DLR. The outbox may therefore finish its role at accepted, while a separate delivery journal continues the message lifecycle.

Persist the internal event_id, request identifier and platform message_id. The status consumer must also be idempotent because a DLR can be repeated or arrive out of order. See the guide to reliable SMS DLR webhooks.

Reconciliation closes rare gaps

An indeterminate result remains possible: the platform accepted the request, but the worker did not receive its response. Retrying automatically risks a duplicate. Give such records an unknown state and reconcile them with a stable identifier, platform journal or an explicit business decision.

A periodic reconciliation job finds:

• records stuck in processing beyond their lease;
• accepted records without a platform identifier;
• messages lacking a final state past the expected window;
• multiple tasks sharing one business key;
• differences between business-event and notification counts.

Observability and recovery

Monitor queue depth and age by message class, processing rate, attempt distribution, temporary and permanent errors, the share of unknown, time to platform acceptance and time to final DLR. Alert on lack of movement as well as absolute size: a small frozen queue can be more serious than a large queue that is draining quickly.

Test backup recovery too. Restoring business tables and the outbox to different points in time can repeat old notifications or lose new ones. The recovery policy should treat them as one consistent dataset.

Practical conclusion

The transactional outbox does not promise magical exactly-once delivery: the external network and SMS platform remain separate systems. It provides something more useful—atomic persistence of the business event and the intent to notify, with rare indeterminate cases made visible and manageable.

For a QuickTel integration, agree the stable identifier, error classes, rate limits, TTL and reconciliation option. Then inject a process failure at every state transition and verify recovery without omissions or a duplicate burst.