{"id":2824,"date":"2026-08-28T08:00:34","date_gmt":"2026-08-28T02:30:34","guid":{"rendered":"https:\/\/www.smsgatewaycenter.com\/blog\/?p=2824"},"modified":"2026-08-28T08:00:37","modified_gmt":"2026-08-28T02:30:37","slug":"outbound-message-table-schema-design","status":"publish","type":"post","link":"https:\/\/www.smsgatewaycenter.com\/blog\/outbound-message-table-schema-design\/","title":{"rendered":"The Outbound Message Table: Designing the Schema Everything Else Depends On"},"content":{"rendered":"\n

Retries, idempotency, delivery reconciliation, cost attribution and every alert you will ever build all read from one table. Here is how to design it correctly the first time, using the real identifier, timestamp and status shapes the SMSGatewayCenter API actually returns.<\/p>\n\n\n\n

\"Diagram<\/a>
Every reliability behaviour in a messaging system reads from and writes to one table. Design it for two clocks, not one.<\/figcaption><\/figure>\n\n\n\n

Table of Contents<\/h2>\n\n\n\n
    \n
  1. Executive Summary<\/a><\/li>\n\n\n\n
  2. TL;DR<\/a><\/li>\n\n\n\n
  3. Why This Table Is Different From an Outbox<\/a><\/li>\n\n\n\n
  4. The Minimum Viable Schema<\/a><\/li>\n\n\n\n
  5. Three Levels of Identity, Not One<\/a><\/li>\n\n\n\n
  6. Store Provider Identifiers as Text, Always<\/a><\/li>\n\n\n\n
  7. The String Zero Is Not a Value<\/a><\/li>\n\n\n\n
  8. Two-Level Key Design: Business Key and Attempt Key<\/a><\/li>\n\n\n\n
  9. The State Column and Its Legal Transitions<\/a><\/li>\n\n\n\n
  10. Modelling the Batch Response Fan-Out<\/a><\/li>\n\n\n\n
  11. When There Is No Provider Identifier At All<\/a><\/li>\n\n\n\n
  12. One Table or Many? The OTP Question<\/a><\/li>\n\n\n\n
  13. Timestamps: Four Different Formats, One Column Type<\/a><\/li>\n\n\n\n
  14. The Columns Money Depends On<\/a><\/li>\n\n\n\n
  15. The Columns Observability Depends On<\/a><\/li>\n\n\n\n
  16. Indexing for the Four Access Patterns<\/a><\/li>\n\n\n\n
  17. Scheduled and Campaign Sends Are Different Rows<\/a><\/li>\n\n\n\n
  18. Partitioning, Retention and the Report Window<\/a><\/li>\n\n\n\n
  19. Migrating an Existing Naive Sends Table<\/a><\/li>\n\n\n\n
  20. Building the Table in Ten Steps<\/a><\/li>\n\n\n\n
  21. Decision Matrix<\/a><\/li>\n\n\n\n
  22. Pre-Production Checklist<\/a><\/li>\n\n\n\n
  23. Ten Mistakes That Show Up Later<\/a><\/li>\n\n\n\n
  24. What This Article Deliberately Does Not Claim<\/a><\/li>\n\n\n\n
  25. Frequently Asked Questions<\/a><\/li>\n<\/ol>\n\n\n\n
    \n\n\n\n

    Executive Summary<\/h2>\n\n\n\n

    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<\/code>. 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.<\/p>\n\n\n\n

    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.<\/p>\n\n\n\n

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

    \n\n\n\n

    TL;DR<\/h2>\n\n\n\n

    The answer in one paragraph:<\/strong> 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<\/code> that your application computes before it calls anything, and a nullable provider_message_id<\/code> that arrives later. Store every provider identifier as TEXT<\/code>, never as an integer type. Model state as a forward-only enum whose critical value is unknown<\/code>, and make the transition from unknown<\/code> back to pending<\/code> structurally impossible. Record two independent timestamps, submitted_at<\/code> and delivered_at<\/code>, because your system runs on two clocks. Store cost as NUMERIC<\/code>, 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<\/code>, 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.<\/p>\n\n\n\n

    \n\n\n\n

    Why This Table Is Different From an Outbox<\/h2>\n\n\n\n

    The transactional outbox is a good pattern and it is well documented. Milan Jovanovic’s walkthrough of the outbox pattern<\/a> and the Event-Driven.io explanation of outbox, inbox and delivery guarantees<\/a> 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.<\/p>\n\n\n\n

    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.<\/p>\n\n\n\n

    First, submission is not delivery.<\/strong> 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<\/code> column cannot express the state where submission succeeded and delivery failed. That state is where the majority of real messaging incidents live.<\/p>\n\n\n\n

    Second, you are billed at the earlier of the two events.<\/strong> The published billing terms on the SMSGatewayCenter pricing pages<\/a> 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<\/a>.<\/p>\n\n\n\n

    Third, one request is many messages.<\/strong> The batch send endpoint accepts a comma-separated recipient list and returns exactly one top-level transactionId<\/code> 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.<\/p>\n\n\n\n

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

    Fifth, at least one send path issues no identifier.<\/strong> On the one-time password endpoints, the documented sample responses return transactionId<\/code> as the literal string \"0\"<\/code> 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.<\/p>\n\n\n\n

    Those five facts, not any general database principle, are what the design below is shaped by.<\/p>\n\n\n\n

    \n\n\n\n

    The Minimum Viable Schema<\/h2>\n\n\n\n

    Start here. Every column below earns its place, and the reasoning for each appears in a later section.<\/p>\n\n\n\n

    CREATE TYPE message_state AS ENUM (\n    'pending',      -- intent recorded, nothing sent\n    'claimed',      -- a worker has taken ownership, send not yet attempted\n    'submitted',    -- provider accepted; BILLABLE FROM HERE\n    'rejected',     -- provider refused before submission; not billable\n    'delivered',    -- delivery receipt confirms handset receipt\n    'failed',       -- delivery receipt confirms permanent failure\n    'unknown',      -- outcome genuinely indeterminate; NEVER retried blind\n    'expired'       -- validity window passed with no receipt\n);\n\nCREATE TYPE message_channel AS ENUM ('sms', 'whatsapp', 'voice', 'rcs', 'telegram');\n\nCREATE TABLE outbound_message (\n    -- identity\n    id                  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    business_key        TEXT        NOT NULL,\n    attempt_key         UUID        NOT NULL DEFAULT gen_random_uuid(),\n\n    -- routing\n    tenant_id           TEXT        NOT NULL,\n    channel             message_channel NOT NULL,\n    message_class       TEXT        NOT NULL,   -- 'otp' | 'transactional' | 'promotional'\n    recipient           TEXT        NOT NULL,   -- E.164, no plus sign, normalised on write\n    sender_id           TEXT,                   -- alphanumeric header, 6 chars in India\n\n    -- content\n    body                TEXT        NOT NULL,\n    encoding            TEXT        NOT NULL,   -- 'GSM7' | 'UCS2'\n    segments            SMALLINT    NOT NULL,\n    dlt_entity_id       TEXT,\n    dlt_template_id     TEXT,\n\n    -- state machine\n    state               message_state NOT NULL DEFAULT 'pending',\n    state_reason        TEXT,\n    attempt_count       SMALLINT    NOT NULL DEFAULT 0,\n\n    -- provider identity, all TEXT, all nullable\n    provider_txn_id     TEXT,                   -- transactionId \/ uuId, one per REQUEST\n    provider_message_id TEXT,                   -- msgId, one per RECIPIENT\n    provider_campaign_id TEXT,                  -- campaignid, one per CAMPAIGN\n    provider_status_code TEXT,\n    provider_cause      TEXT,\n\n    -- two clocks\n    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),\n    claimed_at          TIMESTAMPTZ,\n    submitted_at        TIMESTAMPTZ,\n    delivered_at        TIMESTAMPTZ,\n    reconciled_at       TIMESTAMPTZ,\n    scheduled_for       TIMESTAMPTZ,\n    expires_at          TIMESTAMPTZ,\n\n    -- money\n    billable_units      SMALLINT,\n    cost                NUMERIC(12,4),\n    cost_currency       CHAR(3),\n\n    -- audit\n    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()\n);<\/code><\/pre>\n\n\n\n
    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)<\/code> declared at the table level rather than inline. Decide on partitioning before you create the table, because retrofitting that change is a full rewrite.<\/p>\n\n\n\n
    The uniqueness constraint is separate and deliberate:<\/p>\n\n\n\n
    CREATE UNIQUE INDEX uq_outbound_business_key\n    ON outbound_message (tenant_id, business_key);<\/code><\/pre>\n\n\n\n
    That constraint is the whole idempotency mechanism. It is discussed at length in message idempotency at scale<\/a>, 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.<\/p>\n\n\n\n
    \n\n\n\n
    Three Levels of Identity, Not One<\/h2>\n\n\n\n
    \"Illustration<\/a><\/figure>\n\n\n\n
    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.<\/p>\n\n\n\n
    Level one, the campaign.<\/strong> The campaign read endpoint<\/a> at https:\/\/unify.smsgateway.center\/SMSApi\/campaign\/read<\/code> takes a campaignid<\/code> and describes it in the documentation as fetching the details of a split<\/em> campaign. That word matters. A campaign is a container that the platform may divide into more than one transaction. The response returns a campaignList<\/code> array whose elements each wrap a campaign<\/code> object containing uuId<\/code>, status<\/code>, total<\/code>, timestamp<\/code>, scheduledTimestamp<\/code> and lastupdatedTimestamp<\/code>. So a campaign identifier and a transaction identifier are not the same thing, and one campaign can map to several transactions.<\/p>\n\n\n\n
    Level two, the transaction.<\/strong> This is transactionId<\/code> in the send response and uuId<\/code> 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<\/a> later.<\/p>\n\n\n\n
    Level three, the per-recipient message.<\/strong> This is msgId<\/code>, which appears in the reports_dlrList<\/code> 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.<\/p>\n\n\n\n
    The mistake this produces is storing the transaction identifier in a column named message_id<\/code> 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<\/code> is ever unique per row.<\/p>\n\n\n\n
    There is a fourth case, which is the absence of any of these. It is covered below.<\/p>\n\n\n\n
    \n\n\n\n
    Store Provider Identifiers as Text, Always<\/h2>\n\n\n\n
    Look at two real documented sample values. The send endpoint returns \"transactionId\":\"6305583318236810379\"<\/code>. The campaign and schedule read endpoints return \"uuId\": \"5718742519829975000\"<\/code>.<\/p>\n\n\n\n
    Both are nineteen digits. JavaScript’s Number.MAX_SAFE_INTEGER<\/code> is 9,007,199,254,740,991, which is sixteen digits. Any of these values passed through a plain JSON.parse<\/code> 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.<\/p>\n\n\n\n
    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<\/code> is tidier, and adds a cast. Postgres BIGINT<\/code> 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.<\/p>\n\n\n\n
    Three rules, no exceptions.<\/p>\n\n\n\n
      \n
    1. Column type is TEXT<\/code> or VARCHAR<\/code>, never BIGINT<\/code>, NUMERIC<\/code> or INTEGER<\/code>.<\/li>\n\n\n\n
    2. 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<\/code> preserves them as strings already because they are quoted; the danger there is a well-meaning int()<\/code> cast in a normalisation function.<\/li>\n\n\n\n
    3. Compare them with string equality. Never with numeric equality, and never after trimming leading zeros.<\/li>\n<\/ol>\n\n\n\n
      The same rule applies to dlt_template_id<\/code>, which is a nineteen-digit-class identifier in practice and is registration-bound, and to dlt_entity_id<\/code>. Treat every externally issued identifier as an opaque string. You never do arithmetic on any of them.<\/p>\n\n\n\n
      \n\n\n\n
      The String Zero Is Not a Value<\/h2>\n\n\n\n
      This is the subtlest trap in the whole data model and it appears in at least two places in the documented responses.<\/p>\n\n\n\n
      On the one-time password path, both the generate and verify sample responses return \"transactionId\":\"0\"<\/code>. That is not a transaction identifier. It is a placeholder meaning no identifier was issued.<\/p>\n\n\n\n
      On the campaign and schedule read responses, \"lastupdatedTimestamp\": \"0\"<\/code> appears for a record that has never been updated. That is not the Unix epoch of 1 January 1970. It means never.<\/p>\n\n\n\n
      If you store those verbatim you have created two categories of bug at once. Your reconciliation query WHERE provider_txn_id IS NOT NULL<\/code> 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.<\/p>\n\n\n\n
      Normalise on write, in exactly one place:<\/p>\n\n\n\n
      SENTINEL_NULLS = {\"\", \"0\", \"null\", \"NULL\", \"-\"}\n\ndef clean_id(value):\n    \"\"\"Provider identifiers: return None for documented sentinel placeholders.\"\"\"\n    if value is None:\n        return None\n    v = str(value).strip()\n    return None if v in SENTINEL_NULLS else v\n\ndef clean_epoch_ms(value):\n    \"\"\"Millisecond epoch strings: '0' means never, not 1970.\"\"\"\n    if value is None:\n        return None\n    v = str(value).strip()\n    if v in SENTINEL_NULLS:\n        return None\n    return datetime.fromtimestamp(int(v) \/ 1000, tz=timezone.utc)<\/code><\/pre>\n\n\n\n
      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.<\/p>\n\n\n\n
      A useful hardening step is a check constraint that makes the mistake impossible to store:<\/p>\n\n\n\n
      ALTER TABLE outbound_message\n    ADD CONSTRAINT ck_no_sentinel_ids CHECK (\n        (provider_txn_id     IS NULL OR provider_txn_id     NOT IN ('0','')) AND\n        (provider_message_id IS NULL OR provider_message_id NOT IN ('0','')) AND\n        (provider_campaign_id IS NULL OR provider_campaign_id NOT IN ('0',''))\n    );<\/code><\/pre>\n\n\n\n
      Constraints are better than conventions because constraints survive the departure of the person who knew about the convention.<\/p>\n\n\n\n
      \n\n\n\n
      Two-Level Key Design: Business Key and Attempt Key<\/h2>\n\n\n\n
      The table needs two identifiers of its own, and they answer different questions.<\/p>\n\n\n\n
      The business key answers: should this message exist at all?<\/strong> 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:<\/p>\n\n\n\n
      def business_key(tenant, event_type, event_id, recipient, template_id=None):\n    parts = [tenant, event_type, str(event_id), normalise_msisdn(recipient)]\n    if template_id:\n        parts.append(str(template_id))\n    return hashlib.sha256(\"|\".join(parts).encode()).hexdigest()<\/code><\/pre>\n\n\n\n
      Two properties matter. It is stable across process restarts, redeploys and queue redeliveries, which is what makes INSERT ... ON CONFLICT DO NOTHING<\/code> a real duplicate guard. And it deliberately excludes the channel<\/strong>. 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.<\/p>\n\n\n\n
      The attempt key answers: which physical send is this?<\/strong> 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:<\/p>\n\n\n\n
      CREATE UNIQUE INDEX uq_outbound_business_key_active\n    ON outbound_message (tenant_id, business_key)\n    WHERE state <> 'superseded';<\/code><\/pre>\n\n\n\n
      If you add a superseded<\/code> value to the enum for this purpose, add it as a terminal state with no outgoing transitions.<\/p>\n\n\n\n
      Note carefully what the business key is not<\/strong>. It is not the platform’s duplicatecheck<\/code> 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<\/strong>. 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.<\/p>\n\n\n\n
      \n\n\n\n