Unity Notifications Service - Entity-Relationship Diagrams

Overview

The Notifications Service uses a dual-storage architecture: SQL Server for SMS message lifecycle data and AWS DynamoDB for email audit trails. The SQL Server database (UnitySMSMessaging) contains 8 tables and 8 stored procedures.

---

SMS Messaging (SQL Server — UnitySMSMessaging)

erDiagram
    %% ==========================================
    %% SMS Message Lifecycle
    %% ==========================================

    SmsMessages {
        INT SmsMessageID PK "IDENTITY"
        VARCHAR_16 ConcatenationRef "Multi-part message reference"
        VARCHAR_20 ExternalPhoneNumber "E.164 format"
        VARCHAR_10 DirectSupplySenderID "Sender identifier"
        BIT Outbound "true=sent, false=received"
        UNIQUEIDENTIFIER DSMessageID "Cross-system correlation ID"
        DATETIME2 MessageSentWhenUtc
    }

    SmsMessageParts {
        INT SmsMessageID PK "FK to SmsMessages"
        TINYINT MessagePartNbr PK "Part sequence number"
        VARCHAR_MAX Message "Message content"
    }

    SmsMessageDeliveryReceipts {
        INT SmsMessageID PK "FK to SmsMessages"
        TINYINT StatusCodeID FK "FK to StatusCodes"
        TINYINT ErrorCodeID FK "FK to ErrorCodes"
        DATETIME2 UpdatedWhenUtc
    }

    SmsDeliveryReceiptStatusCodes {
        TINYINT StatusCodeID PK
        VARCHAR_64 Name "Delivered|Expired|Failed|Rejected|Accepted|Buffered|Unknown|Historical"
        VARCHAR_500 Description
    }

    SmsDeliveryReceiptErrorCodes {
        TINYINT ErrorCodeID PK
        VARCHAR_64 Name "16 error codes (0-99)"
        VARCHAR_500 Description
    }

    %% Message Relationships
    SmsMessages ||--o{ SmsMessageParts : "SmsMessageID"
    SmsMessages ||--o| SmsMessageDeliveryReceipts : "SmsMessageID"
    SmsDeliveryReceiptStatusCodes ||--o{ SmsMessageDeliveryReceipts : "StatusCodeID"
    SmsDeliveryReceiptErrorCodes ||--o{ SmsMessageDeliveryReceipts : "ErrorCodeID"

    %% ==========================================
    %% Phone Number Preferences
    %% ==========================================

    SmsPhoneNumberPreferences {
        VARCHAR_20 PhoneNumber PK "E.164 format"
        TINYINT StatusID FK "FK to PhoneNumberStatuses"
        DATETIME2 UpdatedWhenUTC
        VARCHAR_64 UpdatedWho
    }

    SmsPhoneNumberStatuses {
        TINYINT StatusID PK
        VARCHAR_64 Name "1=Opted In, 2=Opted Out"
    }

    SmsPhoneNumberStatuses ||--o{ SmsPhoneNumberPreferences : "StatusID"

---

Domain Groupings

SMS Message Lifecycle

Core message tracking from send through delivery. - SmsMessages — Individual SMS messages (inbound and outbound) with E.164 phone numbers and correlation IDs - SmsMessageParts — Multi-part message content (for messages exceeding single SMS limits) - SmsMessageDeliveryReceipts — Delivery status tracking per message

Delivery Status Reference

Lookup tables for delivery tracking. - SmsDeliveryReceiptStatusCodes — 8 statuses: Delivered (0), Expired (1), Failed (2), Rejected (3), Accepted (4), Buffered (5), Unknown (6), Historical Message (7) - SmsDeliveryReceiptErrorCodes — 16 error codes: Delivered (0), Unknown (1), Absent Subscriber - Temporary (2), through General Error (99)

Phone Number Preferences

Opt-in/opt-out management for SMS compliance. - SmsPhoneNumberPreferences — Current preference state per phone number - SmsPhoneNumberStatuses — 2 statuses: Opted In (1), Opted Out (2)

Legacy

• SmsConversations — Legacy conversation table (migrated to SmsMessages). PK: (ExternalPhoneNumber, MessageSentWhenUtc)

---

History Tables

SmsPhoneNumberPreferencesHist

Audit trail for phone preference changes, maintained by trigger TRG_SmsPhoneNumberPreferences_Hist.

Column	Type	Description
PhoneNumber	VARCHAR(20)	PK (composite)
RetiredWhenUTC	DATETIME2	PK — 12/31/9999 = active record
UniqueifierID	INT (IDENTITY)	PK — disambiguates concurrent changes
CreatedWhenUTC	DATETIME2	When record was created
StatusID	TINYINT	Preference status at this point in time
UpdatedWhenUTC	DATETIME2	When preference was last updated
UpdatedWho	VARCHAR(64)	Who made the change

---

DynamoDB Table

unity-notifications-ses-email-sent

Attribute	Type	Key	Description
MessageId	String	Partition Key (Hash)	AWS SES Message ID
SentWhen	String	—	ISO 8601 timestamp
To	List<String>	—	Recipient addresses
From	String	—	Sender address
Subject	String	—	Email subject line
MessageTags	Map<String, String>	—	Application-specific tracking tags

Purpose: Audit trail for all emails sent via AWS SES. Enables correlation of bounces/complaints back to original sends via MessageId.

---

Stored Procedures

Procedure	Purpose	Parameters
usp_SmsMessages_M	Insert/update SMS messages and parts	Message details + parts TVP
usp_SmsMessages_S	Get conversation history by phone	@ExternalPhoneNumber
usp_SmsMessages_D	Delete message by ID	@SmsMessageID
usp_SmsPhoneNumberPreferences_M	Upsert phone preferences	@PhoneNumber, @StatusID, @UpdatedWho
usp_SmsPhoneNumberPreferences_S	Get phone preferences	@PhoneNumber
usp_SmsMessageDeliveryReceipts_M	Upsert delivery receipts	@SmsMessageID, @StatusCodeID, @ErrorCodeID
usp_SmsMessageDeliveryReceiptStatusCodes_S	List status codes	(none)
usp_SmsMessageDeliveryReceiptErrorCodes_S	List error codes	(none)

---

Key Schema Patterns

1. Dual Storage: SQL Server for structured SMS data, DynamoDB for high-throughput email audit logs.
2. Multi-Part Messages: SMS messages support concatenation via SmsMessageParts with part numbers for reassembly.
3. GUID Correlation: DSMessageID (uniqueidentifier) enables cross-system message tracking between RabbitMQ, SQL Server, and AWS services.
4. History Trigger: TRG_SmsPhoneNumberPreferences_Hist automatically tracks all preference changes with temporal markers (RetiredWhenUTC pattern).
5. E.164 Phone Format: All phone numbers stored in international E.164 format for AWS Pinpoint compatibility.