SMS Orchestrator — Data Model

Status: populated Owner: Platform Engineering Last updated: 2026-04-18 Companion: DOMAIN_MODEL

Schema: orch. Owned exclusively by sms-orchestrator. No cross-service writes.

1. Tables

orch.sms_messages

CREATE TABLE orch.sms_messages (
  message_id        UUID PRIMARY KEY,
  tenant_id         UUID NOT NULL,
  account_id        UUID NOT NULL,
  to_msisdn         TEXT NOT NULL,             -- E.164
  from_sender       TEXT NOT NULL,
  body              TEXT NOT NULL,
  segment_count     SMALLINT NOT NULL,
  message_type      TEXT NOT NULL,             -- 'SMS' | 'FLASH_SMS'
  status            TEXT NOT NULL,             -- state machine
  attempt_count     SMALLINT NOT NULL DEFAULT 0,
  operator_id       TEXT,
  route_id          TEXT,
  idempotency_hash  TEXT,
  last_error        JSONB,
  metadata          JSONB,
  callback_url      TEXT,
  enqueued_at       TIMESTAMPTZ NOT NULL,
  status_updated_at TIMESTAMPTZ NOT NULL,
  processed_at      TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (enqueued_at);

-- Monthly partitions, 90d retention then archived

Indexes:

• ix_sms_messages_tenant_id_status on (tenant_id, status)
• ix_sms_messages_account_id_enqueued_at on (account_id, enqueued_at DESC)
• ix_sms_messages_idempotency_hash on (idempotency_hash) WHERE idempotency_hash IS NOT NULL
• ix_sms_messages_operator_id_status on (operator_id, status)

RLS:

ALTER TABLE orch.sms_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY sms_messages_tenant_isolation ON orch.sms_messages
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

orch.idempotency_keys

CREATE TABLE orch.idempotency_keys (
  hash             TEXT PRIMARY KEY,           -- sha256(accountId || ':' || key)
  account_id       UUID NOT NULL,
  message_id       UUID NOT NULL,
  response_body    JSONB NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at       TIMESTAMPTZ NOT NULL         -- created_at + 48h
);
CREATE INDEX ix_idempotency_keys_expires_at ON orch.idempotency_keys(expires_at);

Purge job (daily): DELETE FROM orch.idempotency_keys WHERE expires_at < now();.

2. Redis Keys

Key	TTL	Purpose
orch:idem:{messageId}	48h	In-pipeline dedupe (SET NX)
orch:submit-idem:{hash}	48h	HTTP-submit idempotency (SET NX + cached response body)

3. ID Prefixes

• msg_ for messageId (UUIDv4 shown as-is over the wire; prefix used in logs/diagnostics)
• ord_ bulk order id (future)

4. Partitioning & Retention

• Monthly range partitions on enqueued_at.
• Retention: 90 days hot; archive to cold storage (S3 parquet via analytics-service) after.
• Terminal rows (SENT, FAILED, DEAD_LETTER) eligible for archive; in-flight rows kept.

5. TypeScript Interfaces

export interface SmsMessageRow {
  messageId: string;
  tenantId: string;
  accountId: string;
  toMsisdn: string;
  fromSender: string;
  body: string;
  segmentCount: number;
  messageType: 'SMS' | 'FLASH_SMS';
  status: MessageStatus;
  attemptCount: number;
  operatorId: string | null;
  routeId: string | null;
  idempotencyHash: string | null;
  lastError: ProblemJson | null;
  metadata: Record<string, string> | null;
  callbackUrl: string | null;
  enqueuedAt: Date;
  statusUpdatedAt: Date;
  processedAt: Date | null;
}