Notification
Data Entity
Description
A push notification or in-app alert record delivered to a specific user, covering assignment dispatches, approval status changes, certification expiry warnings, inactivity reminders, and threshold-crossing alerts. Tracks read/unread state and delivery status for the notification history screen.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, immutable unique identifier for the notification record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the users table — the recipient of this notification | required |
title |
string |
Short notification title displayed as the headline in push banners and the notification history list. Max 100 characters. | required |
body |
text |
Full notification body text shown in the expanded notification view and notification history screen. Max 500 characters. | required |
notification_type |
enum |
Classifies the business scenario that triggered this notification, used for routing, filtering, and icon selection in the UI | required |
entity_type |
enum |
The domain entity type this notification references, used for deep-link navigation to the relevant detail screen | - |
entity_id |
uuid |
The ID of the referenced entity (e.g., assignment ID, reimbursement ID). Combined with entity_type to construct the deep-link navigation target. | - |
deep_link_path |
string |
Pre-computed app deep-link path for direct navigation on tap (e.g., /assignments/abc-123, /reimbursements/xyz-456). Null for informational notifications with no target screen. | - |
priority |
enum |
FCM message priority level controlling delivery urgency and battery impact on Android devices | required |
channel |
enum |
Delivery channel for this notification — push sends via FCM, in_app shows only in notification history, both delivers via FCM and persists in history | required |
read_at |
datetime |
Timestamp when the user explicitly read or dismissed this notification. Null indicates unread. Set by the notifications screen on item tap or by markAllAsRead. | - |
delivered_at |
datetime |
Timestamp when FCM confirmed successful delivery to the device. Null if delivery is pending or failed. Set by the notification infrastructure on FCM callback. | - |
failed_at |
datetime |
Timestamp when FCM delivery failed permanently (e.g., invalid token, device unregistered). Null if not failed. | - |
failure_reason |
string |
FCM error code or internal error description when delivery failed. Null if delivery succeeded or is pending. | - |
metadata |
json |
Flexible key-value payload for notification-type-specific context data (e.g., assignment sender name, threshold tier, certification expiry date). Passed as FCM data payload for client-side rendering. | - |
organization_id |
uuid |
Foreign key to the organization that generated this notification. Enables org-scoped notification analytics and multi-tenant data isolation. | required |
sender_user_id |
uuid |
Optional user ID of the person who triggered this notification (e.g., coordinator who dispatched an assignment or approved a reimbursement). Null for system-generated notifications. | - |
is_sensitive |
boolean |
Marks notifications containing sensitive content (e.g., encrypted assignment dispatches). When true, the FCM payload omits body text and clients fetch content via authenticated API on tap. | required |
expires_at |
datetime |
Optional TTL after which the notification is no longer relevant and should be hidden from the history screen. Used for time-bound reminders and alerts. | - |
created_at |
datetime |
Timestamp when the notification record was created in the database. Immutable after insert. | required |
updated_at |
datetime |
Timestamp of the last modification to this record (e.g., when read_at or delivered_at is set). Auto-updated by trigger. | required |
Database Indexes
idx_notification_user_id_created_at
Columns: user_id, created_at
Primary query pattern: paginated notification history per user, ordered by creation time
idx_notification_user_id_read_at
Columns: user_id, read_at
Unread count queries: WHERE user_id = ? AND read_at IS NULL — powers the badge count on the bottom nav
idx_notification_entity_type_entity_id
Columns: entity_type, entity_id
Reverse lookup: find all notifications referencing a given entity (e.g., all notifications for a specific assignment)
idx_notification_notification_type
Columns: notification_type
Filter queries by scenario type for analytics and bulk management
idx_notification_organization_id_created_at
Columns: organization_id, created_at
Org-scoped notification analytics and admin oversight queries
idx_notification_delivered_at
Columns: delivered_at
Monitor pending delivery: WHERE delivered_at IS NULL AND failed_at IS NULL AND created_at < now() - interval '1 hour'
idx_notification_expires_at
Columns: expires_at
TTL cleanup job: find and hide expired notifications WHERE expires_at < now()
Validation Rules
title_not_empty
error
Validation failed
body_not_empty
error
Validation failed
user_id_must_exist
error
Validation failed
entity_id_requires_entity_type
error
Validation failed
expires_at_must_be_future
error
Validation failed
metadata_must_be_valid_json
error
Validation failed
sensitive_flag_required_for_assignment_dispatch
error
Validation failed
read_at_cannot_precede_created_at
error
Validation failed
delivered_at_cannot_precede_created_at
error
Validation failed
notification_type_valid_enum
error
Validation failed
Business Rules
sensitive_notifications_omit_fcm_body
When is_sensitive is true, the FCM push payload must NOT include the title or body fields. The mobile client receives only the notification ID and type, then fetches content via authenticated REST API on tap. This prevents sensitive assignment data (epikrise excerpts, personal details) from appearing in OS notification banners or lock screens.
notification_recipient_must_belong_to_organization
The user referenced by user_id must have an active membership in the organization referenced by organization_id. Cross-tenant notification delivery is forbidden.
read_at_immutable_once_set
Once read_at is set to a non-null timestamp, it cannot be reverted to null. Notifications cannot be marked as unread after being read.
delivered_at_set_only_by_infrastructure
Only notification-infrastructure may set the delivered_at and failed_at fields via FCM delivery callbacks. Client-side code must not write these fields directly.
badge_count_reflects_unread_only
The unread badge count shown on the bottom nav Notifications tab must reflect only notifications where read_at IS NULL and (expires_at IS NULL OR expires_at > now()). Expired notifications must not contribute to the badge count.
mark_all_read_resets_badge
When markAllAsRead is invoked (user visits Notifications Screen), all unread notifications for the user must have read_at set to the current timestamp in a single atomic UPDATE, and the badge count must drop to zero immediately before the API round-trip completes (optimistic update).
notification_type_determines_deep_link
The deep_link_path must be consistent with the notification_type and entity_type/entity_id combination. Assignment dispatch notifications must link to /assignments/:entity_id; reimbursement notifications to /reimbursements/:entity_id; certification warnings to /certificates/:entity_id.
inactivity_reminder_deduplication
Only one active inactivity_reminder notification may exist per user at any given time. Before creating a new inactivity_reminder, the system must verify no unread inactivity_reminder exists for that user. Duplicate reminders are discarded silently.
expired_notifications_hidden_from_history
Notifications where expires_at < now() must be excluded from the paginated notification history returned to the client, even if they have not been explicitly read or deleted.
CRUD Operations
Storage Configuration
Entity Relationships
A user receives many notifications over their session lifetime