Skip to content

Notifications Database Schema Overview ​

Table of Contents ​

Introduction ​

The Notifications database schema is designed to support the system-wide notification functionality within the application. It enables the storage, retrieval, and management of notifications sent to users from various system events and actions. The schema facilitates tracking read status, message content, and metadata associated with each notification.

Schema Structure ​

The Notifications schema consists of a single primary table:

notifications/
└── notifications          # User notification records

Entity Relationships ​

Schema Details ​

notifications Table ​

The notifications table represents individual notification messages sent to users throughout the system.

Fields ​

FieldTypeDescriptionConstraints
idintegerPrimary key identifierPK, Generated
userIdintegerID of the user receiving the notificationFK to users.id, Not Null, Cascade
senderIdintegerID of the user who triggered the notification (optional)FK to users.id, Null allowed
recordIdintegerID of the record this notification is about (optional)Null allowed
typetextType classification of the notificationNull allowed
messagetextContent of the notification messageNull allowed
isReadbooleanFlag indicating if the notification has been read by the recipientDefault: false, Not Null
metadatajsonAdditional structured data related to the notificationNull allowed
createdAttimestampTimestamp when the notification was createdAudit field
updatedAttimestampTimestamp when the notification was last updatedAudit field
createdByintegerID of the user who created the notificationAudit field
updatedByintegerID of the user who last updated the notificationAudit field

Relationships ​

  • Many-to-One with users (recipient): A notification is received by one user
  • Many-to-One with users (sender): A notification can optionally be sent by one user

Business Purpose ​

The notifications table serves as the central repository for all system notifications. It tracks who should receive each notification, the notification's content, its read status, and additional context. The type field allows categorization of notifications for filtering and display purposes, while the metadata field provides flexibility to store additional structured data specific to different notification types.

Data Flow ​

The data flow within the Notifications schema follows this general pattern:

  1. Creation Phase: A new notification is created when a system event or user action occurs that requires notifying a user
  2. Delivery: The notification is stored in the database with its recipient specified by userId
  3. Status Tracking: The isRead flag tracks whether the recipient has seen the notification
  4. Retrieval: Notifications are typically queried by:
    • User ID (to show a user their notifications)
    • Read status (to filter unread notifications)
    • Type (to filter by category)
  5. Cleanup: Older notifications may be archived or deleted based on age or status

Technical Implementation ​

The schema is implemented using Drizzle ORM with PostgreSQL in the TypeScript codebase, featuring:

  • PostgreSQL Table: Defined with appropriate types, constraints, and references
  • Relations Mapping: Establishes bi-directional relationships with the users table
  • Audit Fields: Standard audit trail fields (createdAt, updatedAt, createdBy, updatedBy)
  • Cascading Deletes: Configured to maintain referential integrity (e.g., deleting a user cascades to delete their notifications)
  • JSON Support: Utilizes PostgreSQL's native JSON type for flexible metadata storage

Business Logic ​

Notification Types ​

The system supports various notification types, which may include:

  1. System Notifications: Automated messages from the system about maintenance, updates, or other system-level events
  2. Activity Notifications: Updates about activities, tasks, or actions that require attention
  3. Review Notifications: Alerts about pending or completed reviews (e.g., PPP360 reviews)
  4. Mention Notifications: Alerts when a user is mentioned or tagged in a comment or document
  5. Assignment Notifications: Notifications about new assignments or responsibilities

Notification Lifecycle ​

  1. Creation: A notification is created when a relevant event occurs in the system
  2. Delivery: The notification is stored with a reference to its intended recipient
  3. Reading: When a user views the notification, its isRead status is updated
  4. Action: The user may take action based on the notification, often navigating to a related record
  5. Archiving: Over time, notifications may be archived or removed based on age or relevance

Data Constraints and Rules ​

  1. Required Fields: Every notification must have a recipient (userId), though sender is optional
  2. Read Status: All notifications begin with isRead = false
  3. Related Records: The recordId field can store a reference to any related entity without enforcing a foreign key constraint, allowing flexible connections to various record types
  4. Metadata: The metadata JSON field can store arbitrary structured data specific to each notification type
  5. Cascading Deletion: When a user is deleted, all their notifications are automatically removed

Common Queries ​

Common query patterns for notifications include:

  1. User's Notifications: Retrieve all notifications for a specific user
  2. Unread Count: Count unread notifications for notification badges/indicators
  3. Filtered View: Filter notifications by type for categorized notification displays
  4. Latest Notifications: Retrieve the most recent notifications for a user

Internal documentation — org members only