Notifications Database Schema Overview
Table of Contents
- Introduction
- Schema Structure
- Entity Relationships
- Schema Details
- Data Flow
- Technical Implementation
- Business Logic
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 recordsEntity Relationships
Schema Details
notifications Table
The notifications table represents individual notification messages sent to users throughout the system.
Fields
| Field | Type | Description | Constraints |
|---|---|---|---|
| id | integer | Primary key identifier | PK, Generated |
| userId | integer | ID of the user receiving the notification | FK to users.id, Not Null, Cascade |
| senderId | integer | ID of the user who triggered the notification (optional) | FK to users.id, Null allowed |
| recordId | integer | ID of the record this notification is about (optional) | Null allowed |
| type | text | Type classification of the notification | Null allowed |
| message | text | Content of the notification message | Null allowed |
| isRead | boolean | Flag indicating if the notification has been read by the recipient | Default: false, Not Null |
| metadata | json | Additional structured data related to the notification | Null allowed |
| createdAt | timestamp | Timestamp when the notification was created | Audit field |
| updatedAt | timestamp | Timestamp when the notification was last updated | Audit field |
| createdBy | integer | ID of the user who created the notification | Audit field |
| updatedBy | integer | ID of the user who last updated the notification | Audit 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:
- Creation Phase: A new notification is created when a system event or user action occurs that requires notifying a user
- Delivery: The notification is stored in the database with its recipient specified by
userId - Status Tracking: The
isReadflag tracks whether the recipient has seen the notification - 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)
- 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:
- System Notifications: Automated messages from the system about maintenance, updates, or other system-level events
- Activity Notifications: Updates about activities, tasks, or actions that require attention
- Review Notifications: Alerts about pending or completed reviews (e.g., PPP360 reviews)
- Mention Notifications: Alerts when a user is mentioned or tagged in a comment or document
- Assignment Notifications: Notifications about new assignments or responsibilities
Notification Lifecycle
- Creation: A notification is created when a relevant event occurs in the system
- Delivery: The notification is stored with a reference to its intended recipient
- Reading: When a user views the notification, its
isReadstatus is updated - Action: The user may take action based on the notification, often navigating to a related record
- Archiving: Over time, notifications may be archived or removed based on age or relevance
Data Constraints and Rules
- Required Fields: Every notification must have a recipient (
userId), though sender is optional - Read Status: All notifications begin with
isRead = false - Related Records: The
recordIdfield can store a reference to any related entity without enforcing a foreign key constraint, allowing flexible connections to various record types - Metadata: The
metadataJSON field can store arbitrary structured data specific to each notification type - Cascading Deletion: When a user is deleted, all their notifications are automatically removed
Common Queries
Common query patterns for notifications include:
- User's Notifications: Retrieve all notifications for a specific user
- Unread Count: Count unread notifications for notification badges/indicators
- Filtered View: Filter notifications by type for categorized notification displays
- Latest Notifications: Retrieve the most recent notifications for a user