DLR Processor — Data Model

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

1. Schema Ownership

Schema	Owner service	Access mode
dlr	dlr-processor	Read + Write
orch	sms-orchestrator	Write limited to sms_messages columns only

2. dlr.delivery_receipts

CREATE TABLE dlr.delivery_receipts (
  receipt_id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  message_id          UUID        NOT NULL REFERENCES orch.sms_messages(message_id),
  account_id          UUID        NOT NULL,
  operator_id         UUID        NOT NULL,
  operator_message_id VARCHAR(64) NOT NULL,
  raw_stat            VARCHAR(32) NOT NULL,
  dlr_status          VARCHAR(16) NOT NULL, -- DlrStatus enum
  delivered_at        TIMESTAMPTZ NOT NULL,
  error_code          VARCHAR(32),
  correlated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX dlr_delivery_receipts_operator_msg_id_idx
  ON dlr.delivery_receipts (operator_message_id);

CREATE INDEX dlr_delivery_receipts_message_id_idx
  ON dlr.delivery_receipts (message_id);

CREATE INDEX dlr_delivery_receipts_account_id_idx
  ON dlr.delivery_receipts (account_id, created_at DESC);

Idempotency Strategy

The unique index on operator_message_id is the dedup key. Inserts use ON CONFLICT (operator_message_id) DO NOTHING — a conflict means the DLR was already processed; the NATS message is ACKed immediately without further side effects.

3. dlr.orphaned_receipts

CREATE TABLE dlr.orphaned_receipts (
  orphan_id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  operator_message_id     VARCHAR(64) NOT NULL,
  operator_id             UUID        NOT NULL,
  raw_stat                VARCHAR(32) NOT NULL,
  raw_payload             JSONB       NOT NULL,
  received_at             TIMESTAMPTZ NOT NULL,
  resolved_at             TIMESTAMPTZ,
  resolved_receipt_id     UUID        REFERENCES dlr.delivery_receipts(receipt_id),
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX dlr_orphaned_receipts_operator_msg_id_idx
  ON dlr.orphaned_receipts (operator_message_id);

CREATE INDEX dlr_orphaned_receipts_unresolved_idx
  ON dlr.orphaned_receipts (received_at DESC)
  WHERE resolved_at IS NULL;

4. orch.sms_messages — Updated Columns Only

dlr-processor performs a targeted UPDATE; it does not own this table.

-- Columns written by dlr-processor only:
UPDATE orch.sms_messages
SET
  status      = $1,   -- 'DELIVERED' | 'FAILED' | 'UNDELIVERED' | 'EXPIRED' | 'REJECTED'
  dlr_status  = $2,   -- mirrors DlrStatus canonical value
  dlr_received_at = $3,
  processed_at    = now()
WHERE message_id = $4
  AND status NOT IN ('DELIVERED','FAILED','UNDELIVERED','EXPIRED','REJECTED');
-- Guard: do not overwrite terminal state

5. Flyway Migration Naming

V1__create_dlr_schema.sql
V2__create_delivery_receipts.sql
V3__create_orphaned_receipts.sql
V4__add_dlr_status_check_constraint.sql
V5__add_delivered_at_index.sql

6. Retention Policy

Table	Retention	Mechanism
dlr.delivery_receipts	90 days	pg_partman monthly partition + DROP
dlr.orphaned_receipts	30 days (resolved) / indefinite (unresolved)	Scheduled job