Notification
Data Entity
Description
Represents a single in-app or push notification delivered to a user. Notifications carry a type (assignment_reminder, certificate_expiry, pause_status_change, scenario_prompt, badge_earned, etc.), a JSONB deep-link payload for navigation, and read/unread state. The notifications table is subscribed to via Supabase Realtime to drive live badge count updates.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, auto-generated UUID for each notification record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who is the recipient of this notification. RLS policies enforce that users can only read their own notifications. | required |
organization_id |
uuid |
Foreign key referencing the organization context in which this notification was generated. Used for multi-tenant data isolation and org-scoped RLS enforcement. | required |
type |
enum |
Categorizes the notification by the triggering business event. Drives icon selection, deep-link resolution, and role-based visibility filtering. | required |
title |
string |
Short display title for the notification shown in the notification centre and push banner. Maximum 120 characters. Must be in Norwegian (nb-NO) matching the user's locale. | required |
body |
text |
Full notification message body providing context for the event. Displayed in the notification list item and detail view. Maximum 500 characters. | required |
payload |
json |
JSONB deep-link payload used by notification-deep-link-handler to navigate the user to the relevant in-app entity. Structure varies by type, e.g. {"entity_type": "assignment", "entity_id": "<uuid>"} or {"route": "/peer-mentor/<id>"}. Must be valid JSON; null is allowed for general notifications with no specific navigation target. | - |
source_entity_type |
string |
Identifies the domain entity that triggered this notification (e.g. 'assignment', 'certificate', 'peer_mentor', 'expense_claim'). Used alongside source_entity_id for contextual display and audit linkage. | - |
source_entity_id |
uuid |
UUID of the specific domain entity record that triggered this notification. Combined with source_entity_type to allow deep-linking and contextual display without requiring payload parsing. | - |
read_at |
datetime |
UTC timestamp set when the user marks the notification as read. NULL indicates unread state. Once set, this field is immutable — a notification cannot be reverted to unread. Used for unread badge count queries via Supabase Realtime. | - |
dismissed_at |
datetime |
UTC timestamp set when the user explicitly dismisses (clears) the notification from the notification centre. Dismissed notifications may be excluded from the default feed but retained for audit. NULL means the notification is still present in the feed. | - |
created_at |
datetime |
UTC timestamp of when the notification record was inserted. Auto-set by the database on INSERT. Used for chronological ordering in the notification centre and for badge count Realtime subscriptions. | required |
Database Indexes
idx_notification_user_id
Columns: user_id
idx_notification_user_created_at
Columns: user_id, created_at
idx_notification_user_unread
Columns: user_id, read_at
idx_notification_organization_id
Columns: organization_id
idx_notification_type
Columns: type
idx_notification_source_entity
Columns: source_entity_type, source_entity_id
Validation Rules
type_must_be_valid_enum
error
Validation failed
title_non_empty
error
Validation failed
body_non_empty
error
Validation failed
user_id_references_valid_user
error
Validation failed
payload_valid_json
error
Validation failed
read_at_after_created_at
error
Validation failed
dismissed_at_after_created_at
error
Validation failed
organization_id_not_null
error
Validation failed
Business Rules
user_scoped_access
A notification is strictly scoped to its recipient user_id. Supabase RLS policies must enforce that SELECT, UPDATE, and DELETE operations on the notifications table are filtered to records where user_id matches the authenticated session's user ID. No user may read or modify another user's notifications.
read_at_immutable_once_set
Once read_at is set to a non-null timestamp (notification marked as read), the field must not be cleared or overwritten. A notification cannot be reverted to unread state. Any UPDATE attempt that sets read_at back to NULL must be rejected.
preference_gate_before_dispatch
Before creating a notification of any type, the notification-trigger-service must consult notification-preference-service to verify the recipient user has not opted out of the notification category. If opted out, no notification record is inserted and no push is dispatched.
realtime_insert_triggers_badge_update
Every INSERT into the notifications table must be published to the Supabase Realtime channel scoped to user_id so that supabase-realtime-subscription-service can propagate unread count changes to notification-badge-widget and notification-tab-badge without a full page reload.
payload_type_consistency
The payload JSON structure must conform to the schema expected by the notification type. For assignment_reminder, payload must include entity_type='assignment' and a valid entity_id UUID. For certificate_expiry, payload must include entity_type='certificate' and mentor_id. Mismatched payload shapes cause deep-link failures.
organization_data_isolation
Every notification must have a valid organization_id. Notification queries used in coordinator dashboards, admin portals, and batch operations must always include an organization_id filter to prevent cross-tenant data exposure in the multi-tenant Supabase environment.
role_aware_visibility
Certain notification types are only visible to specific roles. For example, assignment_escalation notifications are visible only to coordinators and org admins, not to peer mentors. role-aware-notification-filter must apply role-based predicates when loading the notification feed.
CRUD Operations
Storage Configuration
Entity Relationships
A user receives many notifications over their lifetime across all notification types