Skip to main content

Notification Indexes and Constraints

Summary​

The schema declares 14 indexes — 4 unique and 10 non-unique — plus 7 primary keys. It declares no foreign keys, no check constraints and no default constraints.

Audience​

Engineers, architects, QA, support and DevOps engineers.

Reference Content​

The verified reference material for this topic is set out in the sections below.

Unique indexes​

IndexTableColumns
UX_Templates_Tenant_Key_Channel_Versiontemplatestenant, key, channel, version
UX_Recipients_Tenant_Type_Refrecipientstenant, type, external reference
UX_Preferences_Scopepreferencestenant, type, external reference, event type, channel
UX_ProcessedEvents_EventIdidempotency ledgerevent

These four are the only structural data-integrity guarantees in the schema. Three of them include a nullable tenant column, so their behavior for global rows depends on the database's treatment of nulls in unique indexes — a characteristic of the platform, not of application code.

Non-unique indexes​

IndexTableColumns
IX_Templates_Key_Channeltemplateskey, channel
IX_Messages_Recipient_Statusmessagesrecipient, status
IX_Messages_Tenant_Channelmessagestenant, channel
IX_Messages_EventIdmessagesevent
IX_DeliveryAttempts_MessageIdattemptsmessage
IX_Audit_EventIdauditevent
IX_Audit_MessageIdauditmessage
IX_Audit_Correlationauditcorrelation
IX_Audit_OccurredOnauditoccurrence
IX_ProcessedEvents_Correlationidempotency ledgercorrelation

All index names are assigned explicitly with HasDatabaseName, so the database names are stable and do not depend on the tooling convention.

Distribution​

TableIndexes
Audit log4
Messages3
Templates2
Idempotency ledger2
Recipients1
Preferences1
Delivery attempts1

The audit table is the most indexed; the tables that carry the highest write volume during a fan-out are also the ones with the most index maintenance cost.

Primary keys​

Seven, one per table, each a single GUID column assigned client-side. No table uses a composite or database-generated key.

Query coverage gaps​

Three read paths have no index leading with their filter column:

  • The failed-queue endpoint filters messages on status alone; the only status index is led by recipient.
  • The three status counts in the statistics endpoint filter on status alone.
  • The inbox orders messages by creation time descending; no message index includes that column.

Absent constraint types​

  • No foreign keys. The migration issues no foreign-key statement; see relationships.
  • No check constraints. Nothing constrains status, channel, stage or outcome columns to their defined constant sets, so any string within the length limit is storable.
  • No default constraints. Defaults exist only as C# property initialisers.
  • No filtered or partial indexes, no included columns, no covering index, no full-text index and no partitioning.

Classification​

Implemented.

Requires confirmation​

Whether check constraints on the enumerated string columns and covering indexes for the uncovered reads are intended requires confirmation.

See Also​

Keywords​

  • Notification database
  • Indexes & Constraints
  • Draft database documentation

Source References​

  • microservices/src/notification-service/Infrastructure/NotificationDbContext.cs
  • microservices/src/notification-service/Infrastructure/Migrations/20260705113059_InitialNotificationSchema.cs
  • microservices/src/notification-service/Infrastructure/Migrations/NotificationDbContextModelSnapshot.cs

Revision Information​

  • Status: Draft
  • Last reviewed: 2026-07-21
  • Review cycle: Quarterly