Notifications
Data Entity
Description
Stores all notification records dispatched to users across push, email, and SMS channels. Each record tracks the notification lifecycle from creation through delivery and read status, supporting the platform's engagement strategy for peer mentors and coordinators.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the notification record | PKrequiredunique |
user_id |
uuid |
Foreign key to the recipient user | required |
organization_id |
uuid |
Foreign key to the organization context for multi-tenant isolation | required |
title |
string |
Short headline displayed in notification inbox and push banner | required |
body |
text |
Full notification message content | required |
channel |
enum |
Delivery channel used for this notification | required |
notification_type |
string |
Category/scenario key identifying the notification rule that triggered this (e.g. assignment_new, activity_approved, expense_rejected, event_reminder) | required |
priority |
enum |
Delivery priority level affecting ordering and visual treatment | required |
status |
enum |
Current lifecycle status of the notification | required |
entity_type |
string |
Type of the linked entity for deep-link routing (e.g. activity, assignment, event, expense, course) | - |
entity_id |
string |
ID of the linked entity for deep-link navigation on tap | - |
deep_link_url |
string |
Constructed deep-link URL for in-app navigation when notification is tapped | - |
rule_id |
string |
Identifier of the notification rule that generated this notification, used for deduplication and audit | - |
template_id |
string |
Reference to the message template used for email/SMS rendering | - |
payload |
json |
Additional structured data included in push payload for client-side processing (badge count, sound, custom data) | - |
read_at |
datetime |
Timestamp when the user opened or acknowledged the notification | - |
delivered_at |
datetime |
Timestamp when the notification was confirmed delivered by the channel provider | - |
sent_at |
datetime |
Timestamp when the notification was dispatched to the delivery provider | - |
failed_at |
datetime |
Timestamp when the notification delivery failed | - |
failure_reason |
string |
Error description when delivery fails (invalid token, provider error, rate limit) | - |
retry_count |
integer |
Number of delivery retry attempts made for this notification | required |
expires_at |
datetime |
Expiration timestamp after which undelivered notifications are discarded | - |
created_at |
datetime |
Timestamp when the notification record was created | required |
updated_at |
datetime |
Timestamp of the last status update on this notification | required |
Database Indexes
idx_notifications_user_id
Columns: user_id
idx_notifications_user_status
Columns: user_id, status
idx_notifications_user_created
Columns: user_id, created_at
idx_notifications_org_id
Columns: organization_id
idx_notifications_notification_type
Columns: notification_type
idx_notifications_status_created
Columns: status, created_at
idx_notifications_entity_lookup
Columns: entity_type, entity_id
idx_notifications_rule_dedup
Columns: rule_id, user_id, entity_id, created_at
idx_notifications_expires_at
Columns: expires_at
Validation Rules
valid_user_reference
error
Validation failed
valid_organization_reference
error
Validation failed
title_not_empty
error
Validation failed
body_not_empty
error
Validation failed
valid_channel_enum
error
Validation failed
valid_status_transition
error
Validation failed
valid_notification_type
warning
Validation failed
entity_pair_consistency
error
Validation failed
expires_at_future
error
Validation failed
retry_count_non_negative
error
Validation failed
payload_valid_json
error
Validation failed
read_at_after_created_at
error
Validation failed
Business Rules
tenant_isolation
All notification queries must be scoped to the user's organization_id. Users must never see notifications belonging to another organization.
opt_out_respect
Before creating a notification, the system must check the user's notification_settings to verify they have not opted out of the notification_type and channel combination. GDPR requires explicit opt-in for SMS.
deduplication_window
The same rule_id + user_id + entity_id combination must not produce a duplicate notification within a configurable time window (default 1 hour) to prevent notification spam.
channel_selection_cascade
Push is the primary channel. If push delivery fails (invalid token, disabled), the system falls back to email. SMS is reserved for urgent and high-priority notifications only and requires explicit GDPR opt-in.
retry_limit
Failed notifications may be retried up to 5 times with exponential backoff. After 5 failures the status is permanently set to failed and no further retries are attempted.
invalid_token_cleanup
When a push delivery returns an invalid-token error from FCM/APNs, the corresponding push_token record must be deregistered to prevent repeated failures on stale tokens.
expiration_discard
Notifications with an expires_at timestamp in the past and status still pending or sent must not be delivered. Background jobs should mark them as failed with reason 'expired'.
deep_link_required_for_entity
When entity_type and entity_id are provided, a valid deep_link_url must be constructed to enable in-app navigation when the notification is tapped.
append_only_audit
Notification records are never deleted. Status transitions are forward-only: pending → sent → delivered → read, or pending → sent → failed. No reverse transitions allowed.
rate_limiting
Email and SMS channels enforce rate limits per user (max 10 emails/hour, max 5 SMS/day) to comply with Norwegian ePrivacy regulation and prevent provider throttling.