Database & storage

The system uses four storage types, each for a different purpose. Knowing "what lives where" makes every domain easier to read.

Four stores

Store	Used for	Examples
Azure SQL	Relational, transactional data	Subscription, Enrollment, Invoice, NotificationScheduler, AutomationScheduler, MessagingJob
Azure Table	High-volume partitioned key-value records	NotificationJob, OutgoingMessage, UserMessage, EntityHistory
Azure Blob	Large payloads/files	history snapshot, campaign payload (messaging-job container)
Azure Queue	Event & background-work transport	entity-event-queue

PartitionKey / RowKey in Azure Table

Each Table row has a (PartitionKey, RowKey) pair that determines partitioning and lookup. The system's general philosophy:

• PartitionKey = where the user expects to open history (for example notification entity), not an internal id.
• RowKey prefers deterministic values (derived from event identity) to prevent duplicate writes on retry; random GUIDs are only used for action-derived flows without stable identity.

Example (see details in Communication):

NotificationJob (event-derived):  PartitionKey = entity.Guid ; RowKey = deterministic sentinel
OutgoingMessage:                  PartitionKey = NotificationJob.RowKey
                                  RowKey = {RecipientKeyHash}_{DeliveryMethod}
EntityHistory:                    RowKey = HistoryEventKey (deterministic) + InsertOrReplace

Naming conventions (SQL)

Convention	Meaning
PascalCase	Current table/column names.
int + TypeId/StatusId	Enum/type/status (do not use bit for expandable state).
bit + Is/Has/Can/Allow	Only for pure booleans.
*Date vs *DateTime	*Date = date only; *DateTime = date + time.
*On vs *At	*On = audit/lifecycle timestamp; *At = event/integration timestamp.
*ById vs *By	*ById = FK/user id; *By = display/source/legacy text.

💡 These conventions explain many fields you see: CutOffMinute (int), TypeId=1 (BillingOnly), Invoice.Date (date-only), CreatedOn (audit timestamp), etc.

Two history mechanisms — do not confuse them

	EntityEventLog	EntityHistory
What it is	Raw event audit	Entity history projection (display timeline)
Read path	Different	Different
Merge?	No — they remain separate mechanisms

Also, Booking has its own BookingHistory (not merged into EntityHistory).

Event payload and trace keys

Data	Store	Current rule
Large EntityEvent snapshot payload	Azure Blob	Store non-empty payload when the complete queue message exceeds about 45 KiB; the envelope keeps BlobRef.
Notification SourceKey	Azure Table (NotificationJob, OutgoingMessage)	Connect message rows to invoice/account/entity at message grain; it does not replace idempotency keys.

There is no new wide SQL link table for traceability. For investigation, read by partition/source key and the scheduler support views already available.

Real-world example: A large history snapshot stays complete in Blob while EntityHistory stores only the projection needed for the timeline.

Next: Conventions & principles.