Notification Schema Reference

Summary

The authoritative schema reference. 7 tables in the extracted notification schema and 3 tables in the monolith's shared schema. Each table appears exactly once, here and nowhere else in this section.

Audience

Engineers, architects, QA, support, DevOps engineers and security reviewers.

Reference Content

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

Conventions

Every extracted table has a single GUID primary key named Id, assigned client-side. Lengths are the mapped maximums. "Required" means the mapping marks the property required; every other string column is nullable. No table declares a foreign key, check constraint, default constraint or concurrency token.

Extracted schema — notification

1. NotificationTemplates

Column	Type	Notes
Id	GUID	Primary key
TenantId	string(80)	Null denotes a global default
TemplateKey	string(160)	Required
Channel	string(40)	Required
Version	int	Monotonic per tenant, key and channel
SubjectTemplate	string(1000)
BodyTemplate	text	Unbounded
Locale	string(35)
IsActive	bool
Description	string(500)
CreatedOnUtc	timestamp
UpdatedOnUtc	timestamp

Indexes: UX_Templates_Tenant_Key_Channel_Version (unique), IX_Templates_Key_Channel.

2. NotificationRecipients

Column	Type	Notes
Id	GUID	Primary key
TenantId	string(80)
RecipientType	string(40)	Required
ExternalRef	string(200)	Required
DisplayName	string(300)
Email	string(320)
Phone	string(40)
CreatedOnUtc	timestamp
UpdatedOnUtc	timestamp

Indexes: UX_Recipients_Tenant_Type_Ref (unique).

3. NotificationMessages

Column	Type	Notes
Id	GUID	Primary key
TenantId	string(80)
EventId	GUID?	Null for API-originated sends
EventType	string(160)
CorrelationId	string(160)
TemplateKey	string(160)	Required
TemplateId	GUID?	Unenforced reference
TemplateVersion	int
Channel	string(40)	Required
RecipientId	GUID	Unenforced reference
ToAddress	string(320)
Subject	string(1000)
Body	text	Unbounded
Status	string(40)	Required
Attempts	int
LastError	string(2000)
CreatedOnUtc	timestamp
SentOnUtc	timestamp?
ReadOnUtc	timestamp?

Indexes: IX_Messages_Recipient_Status, IX_Messages_Tenant_Channel, IX_Messages_EventId.

4. NotificationDeliveryAttempts

Column	Type	Notes
Id	GUID	Primary key
NotificationMessageId	GUID	Unenforced reference
AttemptNumber	int	One-based
Channel	string(40)	Required
Provider	string(60)	Required
Status	string(40)	Required
Error	string(2000)	Truncated in application code
StartedOnUtc	timestamp
CompletedOnUtc	timestamp
DurationMs	long

Indexes: IX_DeliveryAttempts_MessageId. The only table with no tenant column.

5. NotificationPreferences

Column	Type	Notes
Id	GUID	Primary key
TenantId	string(80)
RecipientType	string(40)	Required
ExternalRef	string(200)	Required
EventType	string(160)	Required; * means all events
Channel	string(40)	Required
Enabled	bool
CreatedOnUtc	timestamp
UpdatedOnUtc	timestamp

Indexes: UX_Preferences_Scope (unique, five columns). No runtime write path exists.

6. NotificationAuditLog

Column	Type	Notes
Id	GUID	Primary key
TenantId	string(80)
EventId	GUID?
NotificationMessageId	GUID?	Null for event-level stages
EventType	string(160)
Stage	string(40)	Required
Detail	string(2000)	Truncated in application code
CorrelationId	string(160)
TraceId	string(64)
OccurredOnUtc	timestamp

Indexes: IX_Audit_EventId, IX_Audit_MessageId, IX_Audit_Correlation, IX_Audit_OccurredOn.

7. ProcessedIntegrationEvents

Column	Type	Notes
Id	GUID	Primary key
EventId	GUID	Uniquely indexed
EventType	string(160)	Required
TenantId	string(80)
CorrelationId	string(160)
SourceService	string(120)
Outcome	string(40)	Required
MessagesCreated	int
ProcessedOnUtc	timestamp

Indexes: UX_ProcessedEvents_EventId (unique), IX_ProcessedEvents_Correlation.

Monolith schema — legacy notification tables

8. Notifications

Integer identity key. Columns: user, employee and labor references, event key (max length 120), title (max length 220), message, action URL (max length 500), read flag, creation timestamp and a nullable client tenant. Index on client tenant, user, read flag and creation timestamp. Tenant query filter applied with a super-admin bypass.

9. UserNotifications

Integer identity key. Columns: user reference, title, message, a type value defaulting to an informational literal, action URL, read flag, creation timestamp and a nullable client tenant. Tenant query filter applied with a super-admin bypass.

10. NotificationTemplates (legacy)

Integer identity key. Columns: event key (max length 120), title and message templates, email subject and body templates, email-enabled and in-app-enabled flags, nullable client tenant and an active flag. Index on client tenant and event key. Tenant query filter applied with a super-admin bypass that additionally admits null-tenant rows.

This table shares a name with the extracted template table but has an unrelated shape.

Totals

Metric	Extracted	Monolith
Tables	7	3
Primary keys	7 GUID, client-assigned	3 integer identity
Indexes	14	2
Unique constraints	4	0
Foreign keys	0	0
Query filters	0	3

Classification

Transitional.

Requires confirmation

Whether the two template models are to be reconciled, and the target state for the legacy tables, require confirmation.

Related Articles

• Indexes and constraints
• Entity mappings
• Relationships

See Also

• Database limitations
• Migrations

Keywords

• Notification database
• Schema Reference
• Draft database documentation

Source References

• microservices/src/notification-service/Infrastructure/NotificationDbContext.cs
• microservices/src/notification-service/Domain/NotificationEntities.cs
• microservices/src/notification-service/Infrastructure/Migrations/20260705113059_InitialNotificationSchema.cs
• microservices/src/notification-service/Infrastructure/Migrations/NotificationDbContextModelSnapshot.cs
• Data/AppDbContext.cs
• Models/Notification.cs
• Models/UserNotification.cs
• Models/NotificationTemplate.cs

Revision Information

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