SMS Gateway Center Desk
28/08/2026
No Comments
25 Views

Executive Summary
TL;DR
Why This Table Is Different From an Outbox
The Minimum Viable Schema
Three Levels of Identity, Not One
Store Provider Identifiers as Text, Always
The String Zero Is Not a Value
Two-Level Key Design: Business Key and Attempt Key
The State Column and Its Legal Transitions
Modelling the Batch Response Fan-Out
When There Is No Provider Identifier At All
One Table or Many? The OTP Question
Timestamps: Four Different Formats, One Column Type
The Columns Money Depends On
The Columns Observability Depends On
Indexing for the Four Access Patterns
Scheduled and Campaign Sends Are Different Rows
Partitioning, Retention and the Report Window
Migrating an Existing Naive Sends Table
Building the Table in Ten Steps
Decision Matrix
Pre-Production Checklist
Ten Mistakes That Show Up Later
What This Article Deliberately Does Not Claim
Frequently Asked Questions

Executive Summary

If you are building anything that sends messages at scale, the single highest-leverage design decision you will make is the shape of one table. Call it outbound_message. Retry logic reads it to decide what is safe to send again. Idempotency reads it to decide whether a send already happened. Delivery reconciliation writes to it when receipts arrive minutes or hours later. Cost reporting sums a column in it. Every alert worth having is a query against it. Get it wrong and you do not get a bug, you get a category of bugs that reappear in different disguises for years.

The reason the standard advice does not work here is that almost all published guidance on this problem is the transactional outbox pattern, and the outbox pattern assumes the destination is a message broker that gives you a synchronous acknowledgement. A messaging provider does not behave like a broker. It gives you a submission receipt on one clock and a delivery receipt on a second, much slower clock, it bills you at submission rather than at delivery, it returns one identifier for a request that may contain ten thousand recipients, and on at least one send path it returns no usable identifier at all. A schema built on the assumption of a single acknowledgement cannot represent any of that, so teams end up bolting on side tables, nullable columns with contradictory meanings, and reconciliation jobs that quietly guess.

This article gives you the full design. It uses the real request and response shapes documented on the SMSGatewayCenter developer API, including the batch send endpoint, the delivery report endpoint, the campaign read endpoint and the one-time password endpoints, because a schema designed against imagined field shapes is a schema you will migrate within a quarter.

TL;DR

The answer in one paragraph: design the outbound message table around one row per recipient per attempt, never one row per API request. Give it two keys: a deterministic business_key that your application computes before it calls anything, and a nullable provider_message_id that arrives later. Store every provider identifier as TEXT, never as an integer type. Model state as a forward-only enum whose critical value is unknown, and make the transition from unknown back to pending structurally impossible. Record two independent timestamps, submitted_at and delivered_at, because your system runs on two clocks. Store cost as NUMERIC, and store billable units as recipients multiplied by segments rather than as a request count, because that is what you are actually charged for. Index for exactly four access patterns: claim the next row, find rows stuck in unknown, compute the age of the oldest unresolved row, and correlate by recipient plus sender plus time window for the paths where no provider identifier exists.

Why This Table Is Different From an Outbox

The transactional outbox is a good pattern and it is well documented. Milan Jovanovic’s walkthrough of the outbox pattern and the Event-Driven.io explanation of outbox, inbox and delivery guarantees are both worth reading before you design anything here, because the transactional half of the problem, writing your business change and your intent to send inside the same database transaction, is genuinely solved by them.

The part they do not solve is what happens after the relay picks the row up. In a classic outbox, the relay publishes to a broker, the broker acknowledges, the row is marked sent, and the story ends. Five things break that story when the destination is a messaging provider.

First, submission is not delivery. A successful HTTP response means the provider accepted your message. It does not mean a handset received it. The delivery receipt arrives later, through a completely separate mechanism, either a webhook push or a poll against the reports endpoint. Your row therefore has two independent completions, and a schema with a single processed_at column cannot express the state where submission succeeded and delivery failed. That state is where the majority of real messaging incidents live.

Second, you are billed at the earlier of the two events. The published billing terms on the SMSGatewayCenter pricing pages state that credits are non-refundable once an SMS is successfully submitted to the operator. That single sentence has more schema consequences than any other fact in this article. It means a row that reaches submitted has already cost money whether or not it ever reaches delivered, which means the cost column must be populated at submission time and must never be recomputed from delivery state. It also means an over-eager retry is not a correctness problem that you can clean up later, it is a billing event that has already happened. The economics are unpacked in more depth in what actually drives bulk SMS cost in India.

Third, one request is many messages. The batch send endpoint accepts a comma-separated recipient list and returns exactly one top-level transactionId for the whole request. If you model one row per request, you have no place to record that recipient 4,712 out of 10,000 failed. If you model one row per recipient, you need somewhere to record that all ten thousand of them share a submission identifier.

Fourth, some recipients are rejected inside a successful response. The documented sample success response for the batch endpoint includes an invalidMobile field alongside a status of success. Recipients enumerated there were not accepted. A naive parser that branches only on the top-level status marks all ten thousand rows submitted and then spends a week wondering why a few hundred never produced a delivery receipt.

Fifth, at least one send path issues no identifier. On the one-time password endpoints, the documented sample responses return transactionId as the literal string "0" on both generate and verify. There is no per-send correlation key on that path. Any schema whose reconciliation strategy assumes a provider identifier always exists cannot represent OTP traffic at all, and OTP traffic is usually the most business-critical traffic in the system.

Those five facts, not any general database principle, are what the design below is shaped by.

The Minimum Viable Schema

Start here. Every column below earns its place, and the reasoning for each appears in a later section.

CREATE TYPE message_state AS ENUM (
    'pending',      -- intent recorded, nothing sent
    'claimed',      -- a worker has taken ownership, send not yet attempted
    'submitted',    -- provider accepted; BILLABLE FROM HERE
    'rejected',     -- provider refused before submission; not billable
    'delivered',    -- delivery receipt confirms handset receipt
    'failed',       -- delivery receipt confirms permanent failure
    'unknown',      -- outcome genuinely indeterminate; NEVER retried blind
    'expired'       -- validity window passed with no receipt
);

CREATE TYPE message_channel AS ENUM ('sms', 'whatsapp', 'voice', 'rcs', 'telegram');

CREATE TABLE outbound_message (
    -- identity
    id                  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    business_key        TEXT        NOT NULL,
    attempt_key         UUID        NOT NULL DEFAULT gen_random_uuid(),

    -- routing
    tenant_id           TEXT        NOT NULL,
    channel             message_channel NOT NULL,
    message_class       TEXT        NOT NULL,   -- 'otp' | 'transactional' | 'promotional'
    recipient           TEXT        NOT NULL,   -- E.164, no plus sign, normalised on write
    sender_id           TEXT,                   -- alphanumeric header, 6 chars in India

    -- content
    body                TEXT        NOT NULL,
    encoding            TEXT        NOT NULL,   -- 'GSM7' | 'UCS2'
    segments            SMALLINT    NOT NULL,
    dlt_entity_id       TEXT,
    dlt_template_id     TEXT,

    -- state machine
    state               message_state NOT NULL DEFAULT 'pending',
    state_reason        TEXT,
    attempt_count       SMALLINT    NOT NULL DEFAULT 0,

    -- provider identity, all TEXT, all nullable
    provider_txn_id     TEXT,                   -- transactionId / uuId, one per REQUEST
    provider_message_id TEXT,                   -- msgId, one per RECIPIENT
    provider_campaign_id TEXT,                  -- campaignid, one per CAMPAIGN
    provider_status_code TEXT,
    provider_cause      TEXT,

    -- two clocks
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    claimed_at          TIMESTAMPTZ,
    submitted_at        TIMESTAMPTZ,
    delivered_at        TIMESTAMPTZ,
    reconciled_at       TIMESTAMPTZ,
    scheduled_for       TIMESTAMPTZ,
    expires_at          TIMESTAMPTZ,

    -- money
    billable_units      SMALLINT,
    cost                NUMERIC(12,4),
    cost_currency       CHAR(3),

    -- audit
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

One caveat on the primary key as written: if you adopt the monthly partitioning described later, the primary key must include the partition column, so it becomes PRIMARY KEY (id, created_at) declared at the table level rather than inline. Decide on partitioning before you create the table, because retrofitting that change is a full rewrite.

The uniqueness constraint is separate and deliberate:

CREATE UNIQUE INDEX uq_outbound_business_key
    ON outbound_message (tenant_id, business_key);

That constraint is the whole idempotency mechanism. It is discussed at length in message idempotency at scale, which is the companion article to this one and remains the canonical treatment of duplicate prevention. This article is about the table those techniques require; that one is about the techniques.

Three Levels of Identity, Not One

Most teams discover this the hard way, usually during an incident, so it is worth stating plainly. The platform models message identity at three levels, and your table needs a column for each.

Level one, the campaign. The campaign read endpoint at https://unify.smsgateway.center/SMSApi/campaign/read takes a campaignid and describes it in the documentation as fetching the details of a split campaign. That word matters. A campaign is a container that the platform may divide into more than one transaction. The response returns a campaignList array whose elements each wrap a campaign object containing uuId, status, total, timestamp, scheduledTimestamp and lastupdatedTimestamp. So a campaign identifier and a transaction identifier are not the same thing, and one campaign can map to several transactions.

Level two, the transaction. This is transactionId in the send response and uuId in the report responses. There is exactly one per API request. Ten thousand recipients in a single call share one value. This is your submission-side correlation key and it is what you pass to the by-transaction report endpoint later.

Level three, the per-recipient message. This is msgId, which appears in the reports_dlrList array returned by the delivery report endpoint. This is the only identifier that is one-to-one with a physical message to a handset, and it is the only one you can safely mark unique per recipient row.

The mistake this produces is storing the transaction identifier in a column named message_id and putting a unique constraint on it. The first batch of more than one recipient blows up the constraint, someone drops the constraint under time pressure, and the table loses its integrity guarantee permanently. Three columns, three meanings, and only provider_message_id is ever unique per row.

There is a fourth case, which is the absence of any of these. It is covered below.

Store Provider Identifiers as Text, Always

Look at two real documented sample values. The send endpoint returns "transactionId":"6305583318236810379". The campaign and schedule read endpoints return "uuId": "5718742519829975000".

Both are nineteen digits. JavaScript’s Number.MAX_SAFE_INTEGER is 9,007,199,254,740,991, which is sixteen digits. Any of these values passed through a plain JSON.parse in Node.js, or through a JSON layer in any language that maps JSON numbers to IEEE 754 doubles, is silently corrupted. Note that the campaign sample value ends in three zeros, which is precisely the signature of a value that has already been through a lossy numeric conversion somewhere.

Fortunately the API quotes them, so they arrive as strings. The failure mode is entirely self-inflicted: a developer sees a numeric-looking string, decides BIGINT is tidier, and adds a cast. Postgres BIGINT tops out around 9.22 x 10^18 so the value technically fits, which makes the bug worse rather than better, because it works in the database and breaks only when a JavaScript service reads the row back and re-serialises it.

Three rules, no exceptions.

Column type is TEXT or VARCHAR, never BIGINT, NUMERIC or INTEGER.
Never let a JSON parser coerce these fields. In Node.js use a parser configured to keep large integers as strings, or extract them with a string-preserving path before generic parsing. In Python, json.loads preserves them as strings already because they are quoted; the danger there is a well-meaning int() cast in a normalisation function.
Compare them with string equality. Never with numeric equality, and never after trimming leading zeros.

The same rule applies to dlt_template_id, which is a nineteen-digit-class identifier in practice and is registration-bound, and to dlt_entity_id. Treat every externally issued identifier as an opaque string. You never do arithmetic on any of them.

The String Zero Is Not a Value

This is the subtlest trap in the whole data model and it appears in at least two places in the documented responses.

On the one-time password path, both the generate and verify sample responses return "transactionId":"0". That is not a transaction identifier. It is a placeholder meaning no identifier was issued.

On the campaign and schedule read responses, "lastupdatedTimestamp": "0" appears for a record that has never been updated. That is not the Unix epoch of 1 January 1970. It means never.

If you store those verbatim you have created two categories of bug at once. Your reconciliation query WHERE provider_txn_id IS NOT NULL matches every OTP row and finds nothing. Your dashboard shows a fleet of messages last updated in 1970. Your oldest-item-age alert, which is the best leading indicator any messaging system has, computes an age of fifty-six years and either fires constantly or gets muted.

Normalise on write, in exactly one place:

SENTINEL_NULLS = {"", "0", "null", "NULL", "-"}

def clean_id(value):
    """Provider identifiers: return None for documented sentinel placeholders."""
    if value is None:
        return None
    v = str(value).strip()
    return None if v in SENTINEL_NULLS else v

def clean_epoch_ms(value):
    """Millisecond epoch strings: '0' means never, not 1970."""
    if value is None:
        return None
    v = str(value).strip()
    if v in SENTINEL_NULLS:
        return None
    return datetime.fromtimestamp(int(v) / 1000, tz=timezone.utc)

Every provider response passes through those two functions before it touches the table. Not some responses. All of them. The moment a second parsing path exists, one of them will forget.

A useful hardening step is a check constraint that makes the mistake impossible to store:

ALTER TABLE outbound_message
    ADD CONSTRAINT ck_no_sentinel_ids CHECK (
        (provider_txn_id     IS NULL OR provider_txn_id     NOT IN ('0','')) AND
        (provider_message_id IS NULL OR provider_message_id NOT IN ('0','')) AND
        (provider_campaign_id IS NULL OR provider_campaign_id NOT IN ('0',''))
    );

Constraints are better than conventions because constraints survive the departure of the person who knew about the convention.

Two-Level Key Design: Business Key and Attempt Key

The table needs two identifiers of its own, and they answer different questions.

The business key answers: should this message exist at all? It is deterministic, computed by your application before any network call, and derived entirely from the business event. A workable composition is tenant, event type, event identifier, normalised recipient and template identifier:

def business_key(tenant, event_type, event_id, recipient, template_id=None):
    parts = [tenant, event_type, str(event_id), normalise_msisdn(recipient)]
    if template_id:
        parts.append(str(template_id))
    return hashlib.sha256("|".join(parts).encode()).hexdigest()

Two properties matter. It is stable across process restarts, redeploys and queue redeliveries, which is what makes INSERT ... ON CONFLICT DO NOTHING a real duplicate guard. And it deliberately excludes the channel. If order 8841’s shipping notification goes out over WhatsApp and the fallback chain later re-attempts it over SMS, that is the same business message reaching the customer twice. Including the channel in the key would let both through and the customer would receive two notifications for one event. Exclude it.

The attempt key answers: which physical send is this? It is a UUID, generated fresh per row, and it is what you carry in logs, traces and structured log correlation. If a business message legitimately produces a second physical send after a permanent failure and a manual replay, that is a second row with the same business key intent but a new attempt key. Whether you allow that at all is a policy decision, and if you do, the unique index on the business key needs a partial predicate excluding superseded rows:

CREATE UNIQUE INDEX uq_outbound_business_key_active
    ON outbound_message (tenant_id, business_key)
    WHERE state <> 'superseded';

If you add a superseded value to the enum for this purpose, add it as a terminal state with no outgoing transitions.

Note carefully what the business key is not. It is not the platform’s duplicatecheck parameter. That optional parameter on the batch send endpoint is documented as enabling the removal of duplicate mobile numbers and defaults to true. It performs recipient deduplication within a single request. There is no cross-request time window and it is not a retry guard. Relying on it as one is a documented mistake that has appeared in the wild, and it is worth being explicit because the parameter name invites the wrong reading.

The State Column and Its Legal Transitions

State is a single enum column, not a scatter of booleans. Booleans permit contradictions: is_sent = true alongside is_failed = true is representable and therefore will eventually be represented. An enum makes it unrepresentable.

The transitions are forward only. Here is the complete legal set.

From	To	Trigger	Billable at this point
`pending`	`claimed`	Worker takes ownership	No
`pending`	`expired`	Scheduled window passed unclaimed	No
`claimed`	`pending`	Worker released the claim before any HTTP call was made	No
`claimed`	`submitted`	Provider returned a success response	Yes
`claimed`	`rejected`	Provider refused, for example template mismatch or invalid header	No
`claimed`	`unknown`	Read timeout, connection reset after send, ambiguous response	Assume yes
`submitted`	`delivered`	Delivery receipt reports success	Yes
`submitted`	`failed`	Delivery receipt reports permanent failure	Yes
`submitted`	`expired`	Validity window passed with no receipt	Yes
`unknown`	`submitted`	Reconciliation found a matching record	Yes
`unknown`	`failed`	Reconciliation proved nothing was submitted	No

And here is the transition that does not exist, which is more important than any of the ones that do.

unknown never returns to pending.

The claimed to pending transition in the table above is narrowly permitted, and only when the worker can prove no HTTP request was ever issued, for example a validation error thrown before the client call. If there is any chance a request left the process, the correct destination is unknown.

The reason is arithmetic. An ambiguous outcome means the request may have been submitted. If it was, it has already been billed. Sending it back to pending puts it in the claim queue again, and it will be sent again, and billed again. The correct behaviour is that a row in unknown is only ever resolved by reading, never by writing: a reconciliation sweep queries the delivery report endpoint and moves the row forward based on evidence. Where there is no evidence to be had, as on the OTP path, the row stays unknown and a human looks at it. That is the correct outcome. A permanently unresolved row is cheaper than a duplicate send, and infinitely cheaper than a duplicate OTP.

Enforce it in the database, not in a code comment:

CREATE OR REPLACE FUNCTION enforce_message_state_transition()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.state = 'unknown' AND NEW.state = 'pending' THEN
        RAISE EXCEPTION
          'illegal transition unknown -> pending on outbound_message id=%', OLD.id;
    END IF;
    IF OLD.state IN ('delivered','failed','expired','rejected')
       AND NEW.state <> OLD.state THEN
        RAISE EXCEPTION
          'terminal state % cannot transition to % on id=%',
          OLD.state, NEW.state, OLD.id;
    END IF;
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_message_state_transition
    BEFORE UPDATE ON outbound_message
    FOR EACH ROW EXECUTE FUNCTION enforce_message_state_transition();

That trigger has caught more real bugs in review than any test suite, because it fails loudly in staging the first time somebody writes a well-intentioned requeue job.

The relationship between state and retry policy is the subject of the SMS API retry strategy guide, which classifies failures into retryable and permanent categories. This table is where that classification is recorded and acted on.

Modelling the Batch Response Fan-Out

The batch endpoint returns a single response for many recipients. The documented sample looks like this:

{
  "status": "success",
  "mobile": "919999999999",
  "invalidMobile": "",
  "transactionId": "6305583318236810379",
  "statusCode": "200",
  "reason": "success"
}

Two things in that response drive schema decisions.

transactionId is one value for the whole request. Write it to every row in the batch. Do not make it unique. It is your handle for the by-transaction report lookup later, and it is the only correlation you have until per-recipient msgId values start arriving in delivery reports.

invalidMobile can be non-empty while status reads success. Those recipients were not accepted. They are rejected, not submitted, and critically they are not billable. A parser that keys only on the top-level status inflates your cost attribution and creates a permanent population of rows that will never receive a delivery receipt, which then poisons your pending-message alert.

The fan-out therefore looks like this:

def apply_batch_response(conn, batch_row_ids, resp):
    txn_id  = clean_id(resp.get("transactionId"))
    invalid = {normalise_msisdn(m) for m in
               (resp.get("invalidMobile") or "").split(",") if m.strip()}

    now = datetime.now(timezone.utc)

    with conn.transaction():
        # rejected recipients first, so they can never be counted as billable
        conn.execute("""
            UPDATE outbound_message
               SET state = 'rejected',
                   state_reason = 'invalidMobile',
                   provider_txn_id = %s,
                   updated_at = now()
             WHERE id = ANY(%s) AND recipient = ANY(%s) AND state = 'claimed'
        """, (txn_id, batch_row_ids, list(invalid)))

        # everything else in the batch was accepted, and is now billable
        conn.execute("""
            UPDATE outbound_message
               SET state = 'submitted',
                   provider_txn_id = %s,
                   provider_status_code = %s,
                   submitted_at = %s,
                   billable_units = segments,      -- one recipient per row
                   updated_at = now()
             WHERE id = ANY(%s) AND state = 'claimed'
        """, (txn_id, str(resp.get("statusCode")), now, batch_row_ids))

The ordering is deliberate. Rejections are written first so that a crash between the two statements leaves rejected rows correctly marked rather than optimistically marked submitted. Both statements are inside one transaction anyway, but the ordering documents the intent for the next reader.

Note the state = 'claimed' predicate on both updates. That makes the fan-out itself idempotent. If the same response is applied twice, perhaps because a worker crashed after the provider call and a reconciliation sweep replayed it, the second application matches zero rows.

billable_units is set to segments here because each row is one recipient. If you ever denormalise to one row per batch, this column becomes recipients multiplied by segments, and the reason that formula matters, rather than a request count, is that it is what determines the number on your invoice.

When There Is No Provider Identifier At All

The one-time password path returns transactionId as the string "0" on both generate and verify. There is no per-send correlation key. This is not an oversight you can work around by asking for one; it is the shape of the interface, and the schema has to accommodate it.

The only correlation available is reconstruction from three facts you already control: the recipient, the sender identifier, and a tight window around your own submission timestamp. Those three fields are all present in the reports_dlrList array returned by the delivery report endpoint, as mobileNo, senderName and submitTime.

Reconstruction has three possible outcomes, not two, and the schema has to be able to record all three.

CREATE TYPE correlation_result AS ENUM ('matched', 'not_found', 'ambiguous');

ALTER TABLE outbound_message
    ADD COLUMN correlation_result correlation_result,
    ADD COLUMN correlation_window_ms INTEGER;

def reconcile_by_reconstruction(row, dlr_records, window_seconds=90):
    """Correlate a row that has no provider identifier.

    Returns one of 'matched', 'not_found', 'ambiguous'.
    NEVER guesses between two candidates.
    """
    lo = row.submitted_at - timedelta(seconds=window_seconds)
    hi = row.submitted_at + timedelta(seconds=window_seconds)

    candidates = [
        r for r in dlr_records
        if normalise_msisdn(r["mobileNo"]) == row.recipient
        and r["senderName"] == row.sender_id
        and lo <= parse_submit_time(r["submitTime"]) <= hi
    ]

    if len(candidates) == 1:
        return "matched", candidates[0]
    if len(candidates) == 0:
        return "not_found", None
    return "ambiguous", None   # two OTPs to one recipient inside the window

The ambiguous branch is not a rare edge case. It is the resend case, and resends are the single most common thing a user does with an OTP. Two codes to the same number ninety seconds apart is normal traffic, not an anomaly. A reconciler that picks the closest candidate when there are two is not reconciling, it is guessing, and it will attribute a delivery failure to the wrong attempt roughly half the time it fires.

The correct handling is to record ambiguous, leave the row’s state as unknown, and never retry it. This is the sharpest practical instance of the general rule from the idempotency article: an ambiguous outcome is resolved by evidence or not at all.

Two consequences follow for the OTP path specifically, and both are schema-visible.

Never batch OTP. A batch shares one transaction identifier, and on the OTP path there is no transaction identifier to share, so batching removes the last shred of correlation. One recipient per request, one row per request.

Keep the correlation window tight and store it. correlation_window_ms is stored on the row rather than being a global constant because you will tune it, and when you tune it you need to know which rows were reconciled under which window before you trust a historical comparison.

One Table or Many? The OTP Question

This is the design question that has no obvious answer, so here is the reasoning rather than just the verdict.

The case for one polymorphic table is that everything in the reliability layer is shared. Business key, state machine, claim-before-send, reconciliation, oldest-item-age, cost attribution and every alert you will build apply identically to an OTP and to a promotional blast. Splitting them means writing all of that twice, and the second copy will drift from the first within two quarters. It also means every cross-channel query becomes a UNION, and the oldest-item-age gauge, which needs a single global answer, becomes a query that somebody will forget to extend when a third table appears.

The case for a separate table is that OTP carries state nothing else carries: a code hash, an expiry, a verification attempt counter, a single-use consumed flag. Those are security-sensitive, they have a much shorter lifetime than the message row, and they justify different access controls and a much more aggressive retention policy.

The recommended shape is one table plus a sidecar. Keep outbound_message polymorphic and complete for everything in the reliability layer. Put the verification state in a narrow adjacent table that references it:

CREATE TABLE otp_verification (
    outbound_message_id BIGINT PRIMARY KEY
        REFERENCES outbound_message(id) ON DELETE CASCADE,
    code_hash        BYTEA       NOT NULL,   -- never the code itself
    code_length      SMALLINT    NOT NULL,
    code_type        TEXT        NOT NULL,   -- 'num' | 'alpha' | 'alphanum'
    expires_at       TIMESTAMPTZ NOT NULL,
    retry_after      TIMESTAMPTZ,            -- from the documented retryAfter field
    verify_count     SMALLINT    NOT NULL DEFAULT 0,
    consumed_at      TIMESTAMPTZ,
    purpose          TEXT        NOT NULL,   -- bind the code to what it authorises
    session_binding  TEXT
);

CREATE INDEX idx_otp_unconsumed
    ON otp_verification (expires_at)
    WHERE consumed_at IS NULL;

This resolves cleanly. Reliability logic reads one table. Security logic reads a narrow, short-lived, separately governed table. You can purge otp_verification aggressively without losing the delivery and cost history in outbound_message, which you need for reporting. And ON DELETE CASCADE runs in the direction you want: message retention drives verification retention, never the reverse.

Two notes on the sidecar. code_hash is a hash, not the code, and not an encrypted code. You never need to read the code back, only to compare a submitted value against it, so a hash is strictly correct and strictly safer. And purpose exists because a code generated to authorise a login must not be accepted to authorise a payment. Binding is a property of your application; the API does not do it for you.

Timestamps: Four Different Formats, One Column Type

The platform returns time in at least four different shapes across endpoints, and every one of them needs to land in a TIMESTAMPTZ column in UTC.

Source	Documented format	Example shape	Conversion note
OTP generate response	Millisecond epoch as a quoted string	`"createTime":"1700117933163"`	Divide by 1000, treat `"0"` as null
Campaign and schedule read	Millisecond epoch as a quoted string	`"timestamp": "1562974594713"`	Same, and `lastupdatedTimestamp` of `"0"` means never
By-transaction report	Human-readable text	`"July 13, 2019 03:44:48"`	Not ISO, not an epoch. Parse with an explicit format string, and confirm the timezone with the provider rather than assuming
Send request `scheduleTime`	`YYYY-MM-DD HH:MM:SS`	Local wall-clock string	Convert from your stored UTC value at request time
WhatsApp `scheduletime`	`YYYY-MM-DD HH:MM`	Lowercase `t`, no seconds	A different parameter name casing and a different precision from the SMS equivalent

That last row deserves emphasis because it is a genuine cross-channel inconsistency: the SMS parameter is scheduleTime with a capital T and second precision, and the WhatsApp parameter is scheduletime all lowercase with minute precision. A shared scheduling helper that formats one way for both channels produces a silent parameter rejection on one of them.

Store one truth and convert at the edges:

-- One canonical column. Every read converts INTO it, every write converts OUT of it.
scheduled_for TIMESTAMPTZ,

def fmt_schedule(dt_utc, channel, tz):
    local = dt_utc.astimezone(tz)
    if channel == "whatsapp":
        return local.strftime("%Y-%m-%d %H:%M")      # no seconds
    return local.strftime("%Y-%m-%d %H:%M:%S")

Never store a local wall-clock string in the database. The one situation where teams are tempted is a scheduled campaign that must fire at 10:00 local time regardless of a daylight-saving shift. If you have that requirement, store the UTC instant and the IANA timezone name in a separate column, and recompute. Do not store the string.

The Columns Money Depends On

Three columns, and each has a common mistake attached.

cost NUMERIC(12,4), never FLOAT or REAL. Per-message rates in India are quoted in fractions of a rupee, and a hundred thousand of them summed in floating point will not tie out against an invoice. Four decimal places accommodates sub-paisa rates. The preview endpoint returns an amount field as a float and the length and cost endpoint returns an integer credit; those are two different fields with two different meanings, and conflating them is a reporting bug rather than a rounding bug.

billable_units SMALLINT, populated as recipients multiplied by segments. Not as a request count. The single most common cost surprise in messaging is a team that alerts on requests per minute, sees a flat line, and gets a bill that tripled because message bodies crossed a segment boundary after a copy change. The segment arithmetic itself is documented in the text messages chart: 160 GSM-7 characters per segment, 153 when concatenated, 70 for Unicode and 67 when concatenated. Store the computed value on the row at submission time. Do not recompute it later from the body, because the body may be edited, redacted or truncated in your own retention process, and the historical unit count must not move.

cost and billable_units are written at submission and are then immutable. This follows directly from the published billing terms, which state that credits are non-refundable once the SMS is successfully submitted to the operator. A message that submits and then fails delivery cost you exactly the same as one that succeeded. If your reporting recomputes cost from delivered rows, you are under-reporting spend by precisely your failure rate, which is the number you least want to be wrong about.

A useful guard is a trigger that refuses to change either column once set. It is a five-line function and it will save an audit at some point.

The Columns Observability Depends On

The four columns you will regret omitting, in the order you will regret them.

submitted_at and delivered_at as separate columns. The gap between them is submit-to-delivery latency, which is the single most useful health metric a messaging pipeline has, and it cannot be computed if you overwrote one timestamp with the other. A schema with a single completed_at column has destroyed this metric permanently and no amount of downstream cleverness recovers it.

message_class. This is the dimension every meaningful query groups by. A global delivery rate hides an OTP failure completely, because OTP is usually a single-digit percentage of total volume, so a total collapse of OTP delivery moves the global number by less than normal daily variance. It is invisible in the aggregate by construction, not by accident. The DLR summary endpoint supports a groupby of senderid, which is the provider-side equivalent of this dimension and a good cross-check against your own numbers, provided you use distinct sender identifiers per class.

state_reason and provider_cause as two columns, not one. state_reason is your classification, provider_cause is their verbatim text. Keep them separate. When the provider adds a new failure cause, your classifier will bucket it as unknown, and the only way to find out what it actually was is to have kept the original string. The vocabulary of causes is explained in the knowledge base entries on understanding delivery reports and DLR status meanings.

Nothing high-cardinality as a metric label. This is a schema-adjacent rule that belongs here because the table is where the temptation originates. Recipient, transaction identifier, message identifier and body text are all legitimate table columns and all catastrophic as monitoring labels. Query the table for those; label the metric with message_class, channel, state and sender_id only.

Two derived queries pay for the whole design. First, the age of the oldest unresolved row, which encodes queue depth, drain rate and stalls in one number:

SELECT COALESCE(
         EXTRACT(EPOCH FROM (now() - MIN(created_at)))::int,
         0
       ) AS oldest_pending_seconds
  FROM outbound_message
 WHERE state IN ('pending','claimed');

COALESCE to zero, not to null, is deliberate. A null becomes a gap in the time series, and a gap never triggers an alert, so an empty queue and a dead exporter look identical to your alerting rules. Return a real zero.

Second, the pending ratio, which is the alarm for a dead delivery-receipt pipeline:

SELECT message_class,
       count(*) FILTER (WHERE state = 'submitted')::numeric
         / NULLIF(count(*) FILTER (WHERE state IN
             ('submitted','delivered','failed','expired')), 0) AS still_pending_ratio
  FROM outbound_message
 WHERE created_at > now() - interval '1 hour'
 GROUP BY message_class;

The denominator is everything that was submitted, not delivered plus failed. If you use delivered plus failed and receipts stop arriving entirely, the ratio freezes at whatever it was and looks perfectly healthy while nothing is being confirmed. The denominator bug is the reason delivery dashboards are so often the last thing to notice an incident.

Indexing for the Four Access Patterns

Do not index by intuition. There are exactly four hot query shapes, and each gets one index.

Pattern one, claim the next batch of work. High frequency, must not scan.

CREATE INDEX idx_outbound_claim
    ON outbound_message (channel, message_class, created_at)
    WHERE state = 'pending';

The partial predicate is what makes this small. A table with a hundred million historical rows has perhaps a few thousand pending at any moment, and the index only contains those.

The claim itself uses FOR UPDATE SKIP LOCKED, which is what allows multiple workers to drain the same queue without contending:

WITH claimed AS (
    SELECT id FROM outbound_message
     WHERE state = 'pending'
       AND channel = 'sms'
       AND (scheduled_for IS NULL OR scheduled_for <= now())
     ORDER BY created_at
     FOR UPDATE SKIP LOCKED
     LIMIT 500
)
UPDATE outbound_message m
   SET state = 'claimed', claimed_at = now(), attempt_count = attempt_count + 1
  FROM claimed c
 WHERE m.id = c.id
RETURNING m.*;

The critical operational rule around this query is not an indexing rule: the HTTP call to the provider must happen outside the transaction. Holding a database transaction open across a network call to a third party couples your connection pool lifetime to their latency, and one slow upstream then exhausts the pool and takes down everything else sharing the database.

Pattern two, find rows stuck in unknown. Runs every minute or two.

CREATE INDEX idx_outbound_unresolved
    ON outbound_message (submitted_at)
    WHERE state = 'unknown';

Pattern three, oldest item age. Served by the claim index above, since created_at is its trailing column and the predicate overlaps. Verify with EXPLAIN rather than assuming.

Pattern four, correlate by recipient when there is no provider identifier. This is the OTP reconciliation path.

CREATE INDEX idx_outbound_correlate
    ON outbound_message (recipient, sender_id, submitted_at)
    WHERE provider_txn_id IS NULL AND state IN ('unknown','submitted');

Plus two lookup indexes that are not hot but are needed for support queries and for reconciliation by identifier:

CREATE INDEX idx_outbound_txn ON outbound_message (provider_txn_id)
    WHERE provider_txn_id IS NOT NULL;

CREATE UNIQUE INDEX uq_outbound_provider_msg ON outbound_message (provider_message_id)
    WHERE provider_message_id IS NOT NULL;

That last one is the only unique constraint on a provider identifier in the whole design, and it is safe precisely because msgId is per-recipient rather than per-request. Being able to enforce it is a good test that you have the three identity levels straight.

Indexes not to create: anything on body, anything on state alone with no partial predicate, and any index whose leading column is tenant_id when you already have the composite unique index. Each unnecessary index is a write cost on the hottest table in the system.

Scheduled and Campaign Sends Are Different Rows

A scheduled send is not a pending send with a future timestamp bolted on, and the API makes that concrete.

The schedule read endpoint at https://unify.smsgateway.center/SMSApi/schedule/read returns each schedule as an object containing uuId, status, total, timestamp, scheduledTimestamp and lastupdatedTimestamp. Two operational facts follow.

The schedule lives on the provider side, keyed by uuId. Once you submit with a scheduleTime, the provider holds it. Cancelling or amending it later requires that uuId, so if you did not persist the transaction identifier at submission you cannot cancel your own scheduled campaign. Persist it before you need it.

The schedule read endpoint takes no filter parameters. Its documented parameter list is authentication and output only. There is no date range, no pagination and no status filter, so it returns whatever it returns and you cannot page it. Treat it as a reconciliation cross-check, not as a source of truth. Your table is the source of truth for what you intended to schedule; theirs is the record of what they are holding.

Model the state accordingly: a scheduled row sits in pending with scheduled_for set until you submit it, then moves to submitted with provider_txn_id populated and scheduled_for retained for reference. The expired state exists for the case where the scheduled window passed while the row was still pending, which happens when a worker was down over the window, and which you want to be able to count rather than to silently send late.

Campaigns add the third identity level. Because the campaign read endpoint describes fetching details of a split campaign, a campaign identifier can correspond to more than one uuId. Store provider_campaign_id on every row of the campaign, and never assume it maps one-to-one to a transaction. If you need per-campaign aggregation, group by provider_campaign_id in your own table rather than trusting that a single transaction lookup covers the campaign.

Partitioning, Retention and the Report Window

This table grows without bound and it is the hottest table in the system, so partitioning is not premature optimisation, it is the default.

CREATE TABLE outbound_message (
    -- ... columns as above ...
) PARTITION BY RANGE (created_at);

CREATE TABLE outbound_message_2026_08 PARTITION OF outbound_message
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Monthly range partitioning on created_at is the right default for three reasons. Detaching an old partition is a metadata operation rather than a mass delete, so retention becomes instant rather than an hours-long vacuum problem. The hot partition stays small enough that the claim index fits comfortably in cache. And your reporting queries almost always carry a date predicate, so partition pruning does real work.

Note that partitioning on created_at means the primary key and every unique index must include created_at. Plan for that at creation time, because retrofitting it is a full table rewrite.

On retention, one honest constraint. The provider-side delivery report retention window is not documented publicly. The zipped delivery report endpoint at https://unify.smsgateway.center/SMSApi/report/day retrieves a report for a single date in YYYY-MM-DD format, one day per call, and the documentation describes it as covering past campaigns without stating how far back that goes. Because you cannot rely on an undocumented window, the correct posture is that your table is the archive. Keep your own rows for as long as your finance and compliance requirements demand, and treat the provider’s reports as a reconciliation source with an unknown horizon rather than as your history of record.

The day-granular shape of that endpoint has a practical consequence worth building for: a backfill after an outage is a loop over dates, one HTTP call per day, not a single ranged query. Write the backfill job that way from the start and give it a checkpoint so it resumes rather than restarting.

Migrating an Existing Naive Sends Table

Most readers already have a table. It usually looks something like sends(id, phone, message, sent boolean, response text, created_at). Here is the expand-and-contract path that gets you from there to the design above without a maintenance window.

Step one, add the new columns as nullable. business_key, attempt_key, state, provider_txn_id, provider_message_id, submitted_at, delivered_at, billable_units, cost. All nullable, no constraints yet. This is a metadata-only change on modern Postgres and is safe on a live table.

Step two, dual-write. Change the application to populate both the old and the new columns. Read still comes from the old ones. Run this for at least one full business cycle, which usually means a week, so that weekly batch jobs exercise the new path.

Step three, backfill in batches. Derive state from the old boolean plus the stored response text. Be conservative: any row where the old sent flag is true but no provider identifier can be extracted from the stored response becomes unknown, not submitted. Backfilling optimistically is the same mistake as retrying optimistically, one migration removed. Batch it by primary key range with a sleep between batches so you do not saturate replication.

UPDATE outbound_message
   SET state = CASE
        WHEN provider_message_id IS NOT NULL THEN 'delivered'
        WHEN provider_txn_id     IS NOT NULL THEN 'submitted'
        WHEN sent IS TRUE                    THEN 'unknown'
        ELSE 'pending'
       END
 WHERE state IS NULL
   AND id BETWEEN :lo AND :hi;

Step four, add the constraints. Unique index on (tenant_id, business_key), the state transition trigger, the sentinel check constraint. Build indexes with CREATE INDEX CONCURRENTLY. Expect the unique index build to fail the first time and reveal genuine historical duplicates. That is the migration doing its job. Resolve them by marking the later rows superseded rather than deleting them, because they represent messages that really were sent and really were billed.

Step five, cut reads over, then drop the old columns. Leave a full release between the read cutover and the drop, so a rollback does not require a restore.

The one thing not to do is a big-bang table swap. The outbound message table is written to by every send path in the system, and the paths you forget about are always the ones that matter, typically an old cron job and something a colleague wrote in a different language.

Building the Table in Ten Steps

Enumerate your message classes before writing any DDL. OTP, transactional and promotional at minimum. This drives message_class, and it drives every alert threshold you will set later.
Define the business key composition and write down which fields it includes. Confirm it excludes channel, then confirm again, because the fallback chain is where that decision is tested.
Create the enums first, message_state and message_channel, so the table definition cannot invent ad hoc string states.
Create the table partitioned by created_at, with all provider identifier columns typed TEXT and nullable, and with cost as NUMERIC.
Add the unique index on (tenant_id, business_key) and prove idempotency with a concurrency test: two workers inserting the same business key simultaneously against a real database, asserting exactly one row.
Add the state transition trigger and write a test that asserts the unknown to pending transition raises. That test is the most valuable one in the suite.
Add the four access-pattern indexes and run EXPLAIN (ANALYZE, BUFFERS) on each of the four queries against a table seeded with realistic volume. An index that is not used is a write tax.
Write the response normalisation layer, including clean_id and clean_epoch_ms, and route every provider response through it. Add a test asserting that a transactionId of "0" produces NULL.
Write the reconciliation sweep with the three-valued outcome, and assert in a test that two candidate delivery records inside the window yield ambiguous rather than a pick.
Wire the two derived queries, oldest item age and pending ratio, into your monitoring before you send a single production message. If you add them after the first incident, the first incident is the one you will not have data for.

Decision Matrix

Design question	Choose this	When the alternative is right
Row granularity	One row per recipient per attempt	Never. One row per request loses per-recipient outcomes permanently
Provider identifier column type	`TEXT`	Never anything numeric. Nineteen-digit values break JavaScript numeric parsing
State representation	Single enum column	Never booleans. Booleans permit contradictory states
Business key includes channel	No	Only if two channels genuinely represent two separate business notifications
OTP storage	Shared table plus narrow sidecar	A fully separate table if regulatory isolation of authentication data is mandated
`unknown` resolution	Reconcile by reading, or leave unresolved	Never by requeueing. Requeueing bills twice
Cost column type	`NUMERIC(12,4)`	Never float. Sums must tie to an invoice
Cost written at	Submission	Never at delivery. Billing fires at submission
Partitioning	Monthly range on `created_at`	Weekly if volume exceeds roughly fifty million rows per month
Retention horizon	Driven by your own finance and compliance needs	Not by the provider report window, which is undocumented
Batch OTP sends	Never	No exception. There is no transaction identifier to share
Correlation when no identifier exists	Three-valued matched, not found, ambiguous	Never two-valued. Two-valued forces a guess on resends
Scheduled sends	Same table, `scheduled_for` set, provider `uuId` persisted	A separate table only if scheduling logic is genuinely independent of sending
Provider `msgId` uniqueness	Unique partial index	Safe because it is per-recipient, unlike `transactionId`

Pre-Production Checklist

Identity and keys

business_key is deterministic and computed before any network call
business_key excludes the channel
Unique index exists on (tenant_id, business_key) and is enforced, not advisory
attempt_key is a fresh UUID per row and appears in every log line
Three separate columns exist for campaign, transaction and per-recipient identifiers
Only provider_message_id carries a unique constraint
All provider identifier columns are TEXT
A test asserts that a nineteen-digit identifier survives a full write and read round trip unchanged

Sentinels and parsing

clean_id maps "0" and empty string to NULL
clean_epoch_ms maps "0" to NULL, not to 1970
A check constraint rejects sentinel values at the database level
Every provider response passes through exactly one normalisation function
statusCode is parsed defensively; it is quoted on some endpoints and unquoted on others
Report field names containing spaces are mapped explicitly, not by automatic binding

State machine

state is an enum, not a set of booleans
A database trigger blocks unknown to pending
A database trigger blocks all transitions out of terminal states
rejected is distinguished from failed, and is not billable
expired exists for scheduled rows whose window passed unclaimed
Recipients listed in invalidMobile are written as rejected, not submitted

Two clocks

submitted_at and delivered_at are separate columns
Submit-to-delivery latency is computable from stored data alone
All timestamps are TIMESTAMPTZ in UTC
Wall-clock strings are formatted at request time, never stored
The WhatsApp minute-precision scheduling format is handled separately from the SMS second-precision format

Money

cost is NUMERIC, not float
billable_units is recipients multiplied by segments, not a request count
Cost is written at submission and is immutable thereafter
Reporting sums cost over submitted rows, not over delivered rows

Reconciliation

The sweep is a read-only operation against the report endpoints
The correlation outcome is three-valued
An ambiguous result never resolves to a guess
correlation_window_ms is stored on the row
Backfill after an outage loops one day at a time against the day-granular report endpoint, with a resumable checkpoint

Indexes and operations

Four partial indexes exist, matching the four access patterns
EXPLAIN confirms each is used
Claiming uses FOR UPDATE SKIP LOCKED
The provider HTTP call happens outside the claiming transaction
The table is partitioned by created_at
Oldest-item-age returns zero, not null, on an empty queue
The pending ratio uses submitted as its denominator

Ten Mistakes That Show Up Later

One row per API request. Discovered the first time a customer asks why one person in a ten thousand recipient batch did not get the message, and there is no row to point at.
Storing the transaction identifier in a uniquely constrained message_id column. The constraint breaks on the first real batch, gets dropped under pressure, and never comes back.
Parsing provider identifiers as numbers. Works in every test with short synthetic values, corrupts silently in production where they are nineteen digits.
Persisting the string "0". Produces reconciliation queries that match everything and find nothing, plus dashboards full of 1970.
Requeueing rows from unknown. The single most expensive bug in this domain, because every occurrence is a duplicate billed message and, on the OTP path, a duplicate code that confuses the user about which one is valid.
A single completed_at column. Destroys submit-to-delivery latency permanently. No downstream fix exists.
Computing cost from delivered rows. Under-reports spend by exactly the failure rate, which is the number you least want wrong.
Including the channel in the business key. The fallback chain then delivers the same notification twice over two channels and both look correct in the data.
Trusting the top-level status field alone. Recipients in invalidMobile get marked submitted, never receive receipts, and permanently inflate the pending-message alert until somebody mutes it.
No partitioning. Fine for a year, then the retention delete takes six hours, holds locks, and turns a routine cleanup into an incident.

What This Article Deliberately Does Not Claim

This section exists because the honest boundary of what is documented is more useful than a confident guess.

It does not state the provider-side delivery report retention window. The day-granular report endpoint is documented as covering past campaigns; how far back is not published. The design treats your own table as the archive precisely because of this.
It does not state any throughput or transactions-per-second ceiling. No numeric TPS figure appears in the developer documentation, so none appears here.
It does not claim a maximum batch size. The batch endpoint accepts a comma-separated recipient list; the practical ceiling depends on account configuration and is not published as a single number.
It does not claim the outbound webhook push payload field names. The webhook registration endpoint is documented, but the schema of what is pushed to your receiver is not, so the reconciliation design in this article is built on the pollable report endpoints, whose field names are documented.
It does not claim a timezone for the human-readable timestamps in the by-transaction report. The format is documented; the zone is not stated, and assuming it is a bug waiting to happen at the daylight-saving boundary.
It does not claim what a campaign split is triggered by. The documentation describes fetching details of a split campaign, which establishes that splits exist. It does not say what causes one, so the design simply tolerates the many-to-one relationship rather than predicting it.
It does not claim whether an OTP resend inside the validity window returns the same code or a new one. The generate response includes a type field documented only with the value new; other values are not enumerated.
It does not state rate limits on the reporting, summary or dashboard endpoints. None are published, so the reconciliation sweep in this article is designed to be paced conservatively rather than to a documented budget.
It does not recommend a specific retention period in days. That is a finance and compliance decision specific to your jurisdiction and contracts, not a technical one.

Frequently Asked Questions

Is this just the transactional outbox pattern with more columns?
No. The outbox pattern solves writing your business change and your send intent atomically, and you should still use it for that. This table solves what happens after: a second asynchronous completion on a different clock, billing that fires at the earlier completion, one identifier covering many recipients, and at least one path with no identifier at all. The outbox pattern has nothing to say about any of those because a broker acknowledgement resolves the whole story in one step.

Should the outbox table and the outbound message table be the same table?
They can be, and for most teams they should be. Keep one table, let pending be the outbox state, and let the relay be the claim query. Two tables means a hand-off between them, and hand-offs are where messages get lost or duplicated. If you already have a separate outbox for other event types, keep messages in this table and let the outbox emit an intent that inserts here.

Why must unknown never go back to pending?
Because an ambiguous outcome means the message may already have been submitted, and submission is the billing event. Returning it to the queue guarantees a second submission if the first one landed. The only safe resolutions are evidence from a read of the delivery reports, or leaving it unresolved for a human. An unresolved row costs nothing; a duplicate costs money and, for an OTP, costs user trust.

How do I reconcile a message that has no provider identifier?
Reconstruct from recipient, sender identifier and a tight window around your own submission timestamp, matched against the delivery report list. Accept three outcomes: exactly one candidate is a match, zero candidates is not found, and two or more is ambiguous. Never pick between candidates. Two codes to one number ninety seconds apart is the resend case and it is common.

What window should I use for that reconstruction?
There is no documented figure to anchor on, so treat it as a tunable and store the value you used on each row. Start narrow. A window wide enough to never miss is also wide enough to be ambiguous constantly, which is worse, because a not-found result tells you to investigate while an ambiguous result tells you nothing at all.

Can I use the duplicatecheck parameter instead of a business key?
No. It is documented as removing duplicate mobile numbers, which is deduplication of recipients within a single request. It has no cross-request memory and no time window, so it cannot prevent the case that actually hurts you, which is the same request arriving twice after a timeout and a retry.

Should I store the message body?
Yes, at least for a retention period that covers your dispute and compliance needs, because reconstructing what was sent from a template plus variables is unreliable once the template has been edited. Do budget for it: bodies dominate the storage of this table, and they are the first thing to redact or truncate in an older partition if space becomes a problem. Never store them in a monitoring label.

How do I handle a message that spans multiple segments?
Store segments and encoding on the row, computed at write time with a counter that is correct at code point level rather than at UTF-16 code unit level. Set billable_units from it. Do not recompute segments later from the stored body, because a redaction or truncation in your own retention process would silently change a historical billing figure.

Do I need a separate table per channel?
No, and it will hurt you. Every reliability behaviour is channel-independent, so per-channel tables mean writing the state machine, the reconciler and the alerting several times, and the copies will diverge. Use one table with a channel column and a small per-channel adapter layer at the API boundary, where the actual differences live: different parameter names, different scheduling precision, different response field names.

What is the right primary key?
A generated BIGINT identity column, with the business key enforced by a separate unique index. Do not make the business key the primary key; it is wide, it is a hash, and every foreign key and every index leaf then carries the whole thing. If you partition by created_at, remember that the primary key must include the partition column.

How do I detect that delivery receipts have stopped arriving?
Alert on the ratio of rows still in submitted to all rows that were ever submitted in the window, grouped by message class, and separately alert on seconds since the last receipt of any kind. The second one is three lines of SQL and is the highest-value alert in the entire system, because a dead receipt pipeline looks perfectly healthy on any dashboard built from delivered over delivered plus failed.

Should I store one row per retry attempt or update the existing row?
Update the row and increment attempt_count for transport-level retries that never resulted in a submission. Create a new row only when a genuinely new physical message is sent, which for a resent OTP is the correct model, since each code is a separate message with its own delivery outcome and its own cost. The distinction is whether the provider was billed. Billed means a new row.

How large does this table get?
Volume multiplied by retention, dominated by the body column. At a hundred thousand messages a day with twelve months of retention you are in the tens of millions of rows, which Postgres handles comfortably when partitioned monthly and indexed with partial predicates as described above. The failure mode is not row count, it is unpartitioned retention deletes and indexes without partial predicates.

Is any of this specific to India?
The DLT columns are, dlt_entity_id and dlt_template_id, along with the requirement that the body match an approved template exactly. Everything else is general. If you operate only outside India, keep those columns nullable rather than dropping them, because template-bound messaging is spreading to other regulatory regimes and a nullable column is far cheaper than a later migration. Template mismatch handling is covered in DLT template rejected: reasons and fixes.

Build it against a real API, not a mock. Every field name, response shape and identifier format in this article comes from the live SMSGatewayCenter developer documentation. Create an account, activate the sandbox, and run your schema against real responses before you write the second half of your pipeline. If you are migrating an existing messaging system and want the identifier and reconciliation semantics confirmed for your specific account configuration, get in touch with the team.