Communication — Data & read/write tables

Database view for Communication: artifacts live in multiple stores (SQL / Azure Table / Blob / Queue). Convention: 📖 READ, ✏️ WRITE (insert/update).

Related concepts from other domains

Concept	What it is	Role in Communication	Owning domain
EntityEvent	Event saying "something changed".	Input to event-derived path → creates NotificationJob.	Platform
NotificationSetting / NotificationOption	Notification type config by BU + subscribers.	Scheduler step evaluates: is this type enabled, who receives it.	Org/Settings
MessageTemplate	Content template with placeholders.	Generator renders template.	Messaging
ContactGroup	Recipient group.	Campaign targets groups (MessageJob_ContactGroup).	Attendee/Contact
Account / Attendee / Employee	Recipients.	Resolve recipients (customer/staff).	Account/Staff
ReportSubscription / ReportSubscriber	Recurring report subscription.	Source of report path (see Report Subscription).	Reporting
EmployeeCompliance / Invoice	Reminder source data.	Trigger provider calculates expiry / due date.	Staff / Finance

Storage map

Store	Artifact	Note
Azure SQL	NotificationScheduler, NotificationOption, NotificationSetting(+_Account/_Employee), MessageTemplate, MessagingJob, MessagingJobScheduler, MessageJob_ContactGroup, ReportSubscription, ReportSubscriptionScheduler, ReportSubscriber, ReminderStateLog, NotificationBanner, SmsUsage	Queue, configuration & SMS billing ledger.
Azure Table	NotificationJob, OutgoingMessage, UserMessage, AutomationJob	High-volume records, partitioned by entity/recipient.
Azure Blob	container messaging-job (campaign/report payload)	Large content; SQL only keeps blob name.
Azure Queue	queues between workers	Work transport.
Redis Stream	stream aimy:{environment}:user-message-inbox:events	Realtime metadata fanout/replay; not the Inbox data source.
Azure SQL (automation)	AutomationScheduler	Coordinates due-work reminder/report (see Automation).
Traceability	SourceKey + source-keyed OutgoingMessage	Read by source partition; no new SQL reverse index, no historical row rewrite.

💡 NotificationJob/OutgoingMessage/UserMessage are Azure Table, not SQL tables — that's why they use PartitionKey/RowKey instead of PK/FK.

UserMessage Inbox — storage and querying

Primary row and projections

Component	Where it is stored	Key / prefix	Role
Metadata + recipient state	Azure Table UserMessage on Camel storage	Canonical PartitionKey: Account_{id}, Employee_{id}, EnterpriseUser_{id}	Primary data source for list, detail and state.
Legacy metadata	Same UserMessage Table	Customer_{UserId}	Compatibility read and duplicate prevention only; not the target partition for new delivery.
Message body	Azure Blob container message-content	MessageBodyBlobName; some producers use sha256-{hash} names	Private HTML/text; no Blob URL is returned to the client.
Unread counter	Same UserMessage Table	RowKey _InboxUnread, _InboxUnread_Account_{AccountId} in the owner partition	Fast badge reads; initialized from a bounded unread scan when absent.
Retention index	Same UserMessage Table	PK _InboxUserMessageMaintenance_{yyyyMM}, RK SHA-256 of `sourcePartition	sourceRow`
Retention state	Same UserMessage Table	_InboxMaintenanceState / UserMessageBackfillV1	Watermark for maintenance-index backfill.
Redis cache namespace	Azure Managed Redis, DB 0/default	tux:{environment}:	Isolates keys between environments sharing one logical database.
Inbox realtime stream	Azure Managed Redis, DB 0/default	aimy:{environment}:user-message-inbox:events; consumer group tux-api:user-message-inbox:*	Realtime replay/fanout metadata, default retention 24 hours/100,000 entries; no body.

Contents of a UserMessage

• Identity/scope: UserMessageId, UserId, role context, owner ids, BusinessUnitId, SiteId.
• Source: category, source type, event/entity coordinates, CampaignGuid, NotificationJobPartitionKey/RowKey.
• Display/action: Title, bounded MessagePreview, provider/attendee/site labels, ActionUrl.
• Private body: MessageBodyBlobName; fetched separately through the API after authorization.
• Recipient state: StatusId, ReadOn, ArchivedOn, SavedOn, Reply*, ETag.
• Idempotency: CompatibilityHash is a SHA-256 of immutable content/action and source fields; a different retry hash returns VERSION_CONFLICT.

PartitionKey, RowKey and query behavior

Case	PartitionKey	RowKey
New delivery to Account	Account_{AccountId}	Producer-deterministic; commonly inbox_{sourcePartitionKey}_{hash} or reverseTicks-deterministicKey.
New delivery to Employee	Employee_{EmployeeId}	Same; the producer keeps the same identity on retry.
New delivery to EnterpriseUser	EnterpriseUser_{EnterpriseUserId}	Same; user id alone is not the access-control key.
Legacy Incident/Customer data	Customer_{UserId}	{reverseTicks} or {reverseTicks}-{deterministicRowKey}

PartitionKey is not taken directly from row metadata to grant access. The API resolves portal identity to authorized scopes first, then queries the correct partition. Lists use PartitionKey eq ... and include only Unread/Read; category, Account, Business Unit and Site are filtered server-side.

The Inbox API defaults to 20 items and caps at 100. With multiple owner partitions, each scope keeps an Azure Table continuation token; the server merges heads by CreatedOn and returns all state in an opaque NextCursor. The client must send the cursor back unchanged and must not decode or modify it.

Detail is a point read by PartitionKey + RowKey; the body is read from message-content after authorization. Read, Archive, Save and Unsave use conditional ETag Replace; Save sets SavedOn, Unsave clears it, without copying the row or automatically changing Read. Redis/SSE only signals invalidation; REST/Table + Blob remain authoritative.

Real-world example: Staff Portal has one permitted Employee and two permitted EnterpriseUser scopes. → The API queries three owner partitions and merges them with per-scope cursors. → A row outside the scope is returned as if it does not exist.

Tables in each flow

Send flow (Scheduler → Generator → Distributor)

Step	Tables	Purpose
Scheduler	NotificationScheduler, MessagingJob 📖 READ; NotificationSetting/NotificationOption 📖 READ	Fetch Ready work; evaluate settings.
Generator	MessageTemplate 📖 READ; NotificationJob 📖 READ	Render + resolve recipients.
Generator (output)	UserMessage ✏️ WRITE; OutgoingMessage ✏️ WRITE	Create in-app message + outbound message (per recipient/channel).
Distributor	OutgoingMessage ✏️ UPDATE (claim → Sending → Sent)	"Claim before send".
Distributor (accept)	SmsUsage ✏️ WRITE	SMS billing ledger when provider accepts (idempotent per outgoing message).
Webhook & Tracker	OutgoingMessage ✏️ UPDATE (delivered/opened/bounce)	Update state from provider.

Realtime Inbox flow

Step	Artifact	Purpose
After creation	UserMessage ✏️ WRITE	Inbox data source and recipient-owned state.
Publish signal	Redis Stream ✏️ WRITE	Writes inbox.changed with minimal metadata after persistence.
Fanout	Redis Stream 📖 READ + ACK; SSE connection	Sends the event to Tuke connections for the matching JWT subject.
UI convergence	UnreadCount / Inbox first page 📖 READ	Tuke revalidates through REST; the event carries no body or access rights.

The Redis Stream has configurable retention/max length. A lost or expired event does not lose the UserMessage; the client syncs again through REST.

Automation/reminder flow

Step	Tables	Purpose
Create work	AutomationScheduler ✏️ WRITE (ensure/deactivate)	Due-work schedule (SchedulerKey UNIQUE).
Due execution	AutomationScheduler 📖 READ + claim (Version)	Worker claims processing rights.
Trace mirror	AutomationJob ✏️ WRITE (Azure Table)	Thin trace copy.
State log	ReminderStateLog ✏️ WRITE	Record reminder state.
Delivery	NotificationScheduler ✏️ WRITE (through dispatch guard)	Bridge to send pipeline.

Campaign flow

Step	Tables	Purpose
Create campaign	MessagingJob ✏️ WRITE; blob messaging-job ✏️ WRITE; MessageJob_ContactGroup ✏️ WRITE	Coordinates large payload + recipient groups.
Schedule	MessagingJobScheduler, NotificationScheduler ✏️ WRITE	Enters send pipeline.

Notification Settings and Report Subscription flows

Flow	Artifact	Purpose
Notification toggle	NotificationSetting + BU/site contact settings ✏️ WRITE	Group/batch enable plus Site/BU copy; mixed state is read-only state.
Report run	ReportSubscriptionScheduler ✏️ WRITE/UPDATE; stored report Blob ✏️ WRITE	Scheduled/manual run, history and file independent of attachment.
Delivery trace	SourceKey 📖 READ; OutgoingMessage ✏️ WRITE/UPDATE	Partition by resolved source, claim lease and provider status.

Related

• Architecture — send pipeline & key policy.
• History & observability — audit & trace.

Real-world example: One notification job contains several invoices. → Each OutgoingMessage traces its own invoice SourceKey; no new SQL reverse index is required.