AlMuraba.Notify.Domain.Shared 0.1.0

Database Documentation with Data Examples

This document provides an overview of the Notification System database schema along with example data for each table.

Tables and Relationships

1. NotificationTemplate

   • Description: This table stores the templates for notifications.
   • Columns:
     • Id (PK, long): The primary key for the template.
     • Name (Text, 250): The name of the template.
   • Example Data: | Id | Name | |-----|---------------------| | 1 | Account Alert | | 2 | Weekly Summary |

2. NotificationTemplateContent

   • Description: This table holds the content for each notification template.
   • Columns:
     • Id (PK, long): The primary key for the template content.
     • TemplateId (FK, long): Foreign key referencing NotificationTemplate.Id.
     • Provider (Text, 8): The provider of the template content.
     • Content (Text, 10,000): The actual content of the template.
   • Example Data: | Id | TemplateId | Provider | Content | |-----|------------|----------|-------------------------------------| | 1 | 1 | Email | "Hello {username}, Your account has a new {severity} {alertType} alert." | | 2 | 2 | Email | "Hello {username}, Here is your weekly summary." |

3. Notification

   • Description: This table records each notification instance.
   • Columns:
     • Id (PK, long): The primary key for the notification.
     • UserId (Guid): The ID of the user receiving the notification.
     • TemplateId (FK, long): Foreign key referencing NotificationTemplate.Id.
     • TopicId (FK, long): Foreign key referencing NotificationTopic.Id.
     • IsRead (Bool): Indicates if the notification has been read.
     • ReadTime (DateTime): The time the notification was read.
     • ActionUrl (Text, 500): The URL for any action associated with the notification.
     • DataSourceId (long): Foreign key referencing NotificationDataSource.Id.
   • Example Data: | Id | UserId | TemplateId | TopicId | IsRead | ReadTime | ActionUrl | DataSourceId | |-----|--------------------------------------|------------|---------|--------|----------------------|-----------------------------|--------------| | 1 | 123e4567-e89b-12d3-a456-426614174000 | 1 | 1 | TRUE | 2024-07-22 10:00:00 | http://example.com/action1 | 1 | | 2 | 123e4567-e89b-12d3-a456-426614174001 | 2 | 2 | FALSE | NULL | http://example.com/action2 | 2 |

4. NotificationContent

   • Description: This table contains the content for each notification.
   • Columns:
     • Id (PK, long): The primary key for the notification content.
     • NotificationId (FK, long): Foreign key referencing Notification.Id.
     • Provider (Text, 8): The provider of the notification content.
     • Content (Text, 10,000): The actual content of the notification.
     • IsSent (Bool): Indicates if the notification has been sent.
     • SentTime (DateTime): The time the notification was sent.
     • IsFailed (Bool): Indicates if sending the notification failed.
     • LastFailTime (DateTime): The last time sending the notification failed.
     • RetryCount (Int): The number of retries attempted to send the notification.
   • Example Data: | Id | NotificationId | Provider | Content | IsSent | SentTime | IsFailed | LastFailTime | RetryCount | |-----|----------------|----------|-------------------------------------|--------|--------------------|----------|--------------------|------------| | 1 | 1 | Email | "Hello Haitham, Your account has a new high security alert." | TRUE | 2024-07-22 09:55:00| FALSE | NULL | 0 | | 2 | 2 | Email | "Hello Ali, Here is your weekly summary." | FALSE | NULL | TRUE | 2024-07-22 09:50:00| 3 |

5. NotificationTopic

   • Description: This table manages the topics for notifications.
   • Columns:
     • Id (PK, long): The primary key for the topic.
     • Name (Text, 250): The name of the topic.
     • Slug (Text, 250): A URL-friendly identifier for the topic.
   • Example Data: | Id | Name | Slug | |-----|---------------------|------------------| | 1 | Security Alerts | security-alerts | | 2 | Weekly Updates | weekly-updates |

6. NotificationDataSource

   • Description: This table manages data sources referenced by notifications.
   • Columns:
     • Id (PK, long): The primary key for the data source.
     • Reference (Text, 100): A reference string for the data source.
     • ReferenceId (Text, 250): A reference ID for the data source.
     • ReferenceDataJson (Text, 5,000): JSON data related to the data source.
   • Example Data: | Id | Reference | ReferenceId | ReferenceDataJson | |-----|------------|------------------------|-------------------------------------------| | 1 | Alert | 100021 | {"alertType": "security", "severity": "high"} | | 2 | Update | 300211 | {"updateType": "weekly", "content": "summary"} |

7. UserNotificationTopic

   • Description: This table maps users to notification topics they are subscribed to.
   • Columns:
     • Id (PK, long): The primary key for the user-topic subscription.
     • UserId (Guid): The ID of the user.
     • TopicId (FK, long): Foreign key referencing NotificationTopic.Id.
     • Provider (Text, 8): The provider of the notification topic.
     • IsSubscribed (Bool): Indicates if the user is subscribed to the topic.
   • Example Data: | Id | UserId | TopicId | Provider | IsSubscribed | |-----|--------------------------------------|---------|----------|--------------| | 1 | 123e4567-e89b-12d3-a456-426614174000 | 1 | Email | TRUE | | 2 | 123e4567-e89b-12d3-a456-426614174001 | 2 | Email | FALSE |

8. UserNotificationIdentifier

   • Description: This table manages identifiers for users in various notification systems.
   • Columns:
     • Id (PK, long): The primary key for the user notification identifier.
     • UserId (Guid): The ID of the user.
     • Provider (Text, 8): The provider of the identifier.
     • Value (Text, 250): The value of the identifier.
   • Example Data: | Id | UserId | Provider | Value | |-----|--------------------------------------|----------|---------------| | 1 | 123e4567-e89b-12d3-a456-426614174000 | Email | user1@example.com | | 2 | 123e4567-e89b-12d3-a456-426614174001 | Email | user2@example.com |

Relationships

• NotificationTemplate has a one-to-many relationship with NotificationTemplateContent.
• NotificationTemplate has a one-to-many relationship with Notification.
• Notification has a one-to-many relationship with NotificationContent.
• Notification has a many-to-one relationship with NotificationTopic.
• Notification has a many-to-one relationship with NotificationDataSource.
• NotificationTopic has a one-to-many relationship with UserNotificationTopic.
• UserNotificationTopic has a many-to-one relationship with NotificationTopic.
• UserNotificationTopic has a many-to-one relationship with UserNotificationIdentifier.