To send an SMS from inside a database transaction without losing or double-sending it, don't call the SMS API in the transaction. Insert a row into an outbox table in the same transaction as your business change, then let a separate relay worker send each row and use the row's id as the idempotency key. The row commits or rolls back with your data, and the key makes the retries safe.

We make Ready, so the worker below calls the Ready REST API with only the request shape we document. The pattern works with any provider that supports idempotency keys. It also fits a platform that texts on behalf of its customers, where a lost or duplicated text lands on someone else's customer.

The problem with sending inside the transaction

Say an order is marked shipped and you want to text the buyer. There are two obvious ways to write it, and both are wrong.

If you call the API before the commit, the text goes out and then the commit fails. The buyer is told their order shipped, the database says it didn't, and a retry of the whole request sends a second text. If you call the API after the commit, a crash between the commit and the call loses the text for good, and nothing in your system knows it was owed.

The two writes are to different systems, so no transaction can cover both. The outbox pattern turns it into one write to one system. The intent to send is data, saved with the change that caused it.

Design: three pieces

  1. An outbox table that holds one row per text you owe.
  2. Business code that inserts a row in the same transaction as the change.
  3. A relay worker that reads due rows, calls the SMS API, and records the result.

The delivery guarantee is at least once from the relay, because a worker can crash after sending and before it records success. The idempotency key turns at least once into effectively once. This is why the key has to be the outbox row's id: it's stable across every attempt and it exists before the first one.

The outbox table

Here's a Postgres schema. It keeps the phone number and body on the row, so the text is fixed at the moment the business event happened.

CREATE TABLE sms_outbox (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dedupe_key      text NOT NULL UNIQUE,
  to_number       text NOT NULL,
  body            text NOT NULL,
  status          text NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'sent', 'failed')),
  attempts        integer NOT NULL DEFAULT 0,
  next_attempt_at timestamptz NOT NULL DEFAULT now(),
  last_status     integer,
  created_at      timestamptz NOT NULL DEFAULT now(),
  sent_at         timestamptz
);

CREATE INDEX sms_outbox_due
  ON sms_outbox (next_attempt_at)
  WHERE status = 'pending';

The partial index keeps the relay's query fast as sent rows pile up. The dedupe_key is separate from the id and guards against a different problem: your own business code running twice for the same event, such as a replayed message from a queue. It's built from the event, for example order-shipped:1042.

Write the row in the same transaction

Your business change and the outbox insert commit together or not at all.

BEGIN;

UPDATE orders SET status = 'shipped', shipped_at = now() WHERE id = 1042;

INSERT INTO sms_outbox (dedupe_key, to_number, body)
VALUES ('order-shipped:1042', '+12125550199', 'Your order #1042 has shipped.')
ON CONFLICT (dedupe_key) DO NOTHING;

COMMIT;

If the transaction rolls back, there's no row and no text. If it commits, the row exists, and a crash an instant later can't lose it. ON CONFLICT DO NOTHING makes the insert safe to run twice.

Only create rows for people who agreed to receive texts. The API request will carry consent_attested: true, which is your statement that the recipient agreed, so the check belongs where you decide to enqueue, not in the worker.

The relay worker

The relay claims a batch of due rows, sends each one, and records the outcome. The claim step does two jobs. It lets several workers run at once without taking the same row, and it sets a lease, so a worker that dies mid-batch doesn't strand rows.

UPDATE sms_outbox
SET attempts = attempts + 1,
    next_attempt_at = now() + interval '2 minutes'
WHERE id IN (
  SELECT id FROM sms_outbox
  WHERE status = 'pending' AND next_attempt_at <= now()
  ORDER BY created_at
  LIMIT 20
  FOR UPDATE SKIP LOCKED
)
RETURNING id, to_number, body;

FOR UPDATE SKIP LOCKED makes concurrent workers skip rows another worker holds. Pushing next_attempt_at two minutes out is the lease. If the worker crashes, the row becomes due again after two minutes and another worker picks it up. Here's the worker in Python with psycopg 3 and httpx.

import os

import httpx
import psycopg

CLAIM = """
UPDATE sms_outbox
SET attempts = attempts + 1, next_attempt_at = now() + interval '2 minutes'
WHERE id IN (
  SELECT id FROM sms_outbox
  WHERE status = 'pending' AND next_attempt_at <= now()
  ORDER BY created_at LIMIT 20 FOR UPDATE SKIP LOCKED
)
RETURNING id, to_number, body
"""

DONE = "UPDATE sms_outbox SET status = %s, last_status = %s, sent_at = now() WHERE id = %s"


def relay_once(conn: psycopg.Connection, http: httpx.Client) -> int:
    with conn.transaction():
        rows = conn.execute(CLAIM).fetchall()

    for row_id, to_number, body in rows:
        try:
            resp = http.post(
                "/v1/messages",
                json={"to": to_number, "body": body, "consent_attested": True},
                headers={"Idempotency-Key": str(row_id)},
            )
        except httpx.TransportError:
            continue  # outcome unknown: the lease expires and the same key is retried

        if resp.status_code == 429:
            continue  # slow down: retried after the lease, same key
        status = "sent" if resp.is_success else "failed"
        conn.execute(DONE, (status, resp.status_code, row_id))
    return len(rows)


if __name__ == "__main__":
    with psycopg.connect(os.environ["DATABASE_URL"], autocommit=True) as conn, httpx.Client(
        base_url="https://api.tryready.com",
        headers={"X-API-Key": os.environ["READY_API_KEY"]},
        timeout=httpx.Timeout(15.0, connect=5.0),
    ) as http:
        while relay_once(conn, http):
            pass

The claim runs in its own short transaction, so row locks aren't held while the HTTP calls happen. The send itself is outside any database transaction, which is the point. A loop that polls every few seconds when relay_once returns zero is enough for most platforms. Add a time.sleep there.

What happens when something fails

Walk through each crash point. The key is the row id every time.

FailureResult
Crash before the business transaction commitsNo row, no text, no change. Consistent.
Crash after commit, before any sendThe row is still pending and gets sent by the relay. Not lost.
Request times out, outcome unknownLease expires, retry with the same key. A second text isn't sent.
Send succeeds, worker dies before marking sentLease expires, retry with the same key. The provider recognizes it.
429 from the APIRow retried after the lease, same key.
A 4xx for a bad number or a refusalMarked failed with the status stored for review.

Anything other than success, a 429 or a transport error ends as failed here, with last_status saved. That's deliberate. A 5xx might mean the text was or wasn't accepted, and the right move depends on the code, so read the errors reference and look the message up with GET /v1/messages before you resend. Our posts on retries and failure handling and idempotency keys go through the cases. A key only helps while the provider still remembers it, so don't let rows sit pending for days. Check the docs for how long keys last.

A refused send is a new decision, not a retry. If the API answers 409 QUIET_HOURS_BLOCK, the text wasn't sent because the recipient's local time is inside quiet hours. You can pass hold_until_allowed: true on the request to have it held until the window opens, or enqueue a fresh row later. A fresh row gets a new id, and so a new key.

Operating the outbox

A few habits keep the table healthy.

  • Alert on age. The oldest pending row's age is your best health number. If it climbs, the relay is stuck or the API is refusing.
  • Prune. Delete or archive sent rows after a retention period you choose, so the table stays small.
  • Cap attempts. The attempts column lets you stop after a limit and mark the row failed instead of retrying forever.
  • Mind ordering. The relay sends roughly in creation order, but concurrent workers don't guarantee strict order. If two texts to one person must arrive in order, serialize them by recipient, or send them as one message.

Our post on SMS queue design for platforms covers queues, per-customer fairness and throughput when you outgrow one table.

Wrapping up

The outbox moves the send out of the transaction and turns the risk of a lost or duplicate text into a table you can query. Write the row with the change, send from a relay, and use the row id as the idempotency key so every retry is safe. It adds a table and a worker, and in return a crash stops being something you need to explain to a customer.

To try the request the relay makes, the API quickstart covers the key and a first send.