Notification
Data Entity
Description
In-app notification records delivered to users, covering assignment alerts, activity reminders, certificate expiry warnings, and gamification achievements. Notifications support deep links for direct navigation to the relevant feature screen.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, auto-generated UUID for each notification record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing users.id — the recipient of this notification | required |
category |
enum |
High-level domain category used for filtering, grouping, and user preference matching in push_notification_configs | required |
type |
enum |
Specific event type within the category, used by the scenario engine to select notification template and routing logic | required |
title |
string |
Short notification headline rendered in the notification list item and push banner. Maximum 120 characters. | required |
body |
text |
Full notification body text with contextual detail. Displayed in the notification list and optionally in the push payload. Maximum 500 characters. | required |
is_read |
boolean |
Whether the user has opened or explicitly marked this notification as read | required |
deep_link |
string |
App-internal route path (GoRouter location string) used to navigate directly to the relevant feature screen when the notification is tapped. Example: /assignments/abc123 | - |
related_entity_id |
uuid |
UUID of the related domain record (e.g. assignment ID, certificate ID, badge ID) enabling the UI to resolve a preview or perform targeted actions | - |
related_entity_type |
enum |
Identifies which domain table related_entity_id references, so the app router can resolve the deep link without fragile string parsing | - |
metadata |
json |
Supplementary key-value payload for scenario-specific data that does not warrant a dedicated column (e.g. threshold level crossed, badge name, expiry date string). Consumed by the notification item widget for dynamic rendering. | - |
created_at |
datetime |
UTC timestamp of when the notification record was inserted. Used for display ordering and archive TTL enforcement. | required |
read_at |
datetime |
UTC timestamp of when the user first marked this notification as read. Null if still unread. Used for unread-duration analytics. | - |
expires_at |
datetime |
Optional UTC timestamp after which this notification is considered stale and should be hidden or archived. Used for time-sensitive alerts such as certificate expiry warnings that become irrelevant once action is taken. | - |
organization_id |
uuid |
Foreign key referencing organizations.id to scope the notification to the correct organizational context. Required for RLS policies to restrict coordinator access to their own organization's notifications. | required |
Database Indexes
idx_notifications_user_id_created_at
Columns: user_id, created_at
idx_notifications_user_id_is_read
Columns: user_id, is_read
idx_notifications_user_id_category
Columns: user_id, category
idx_notifications_related_entity
Columns: related_entity_type, related_entity_id
idx_notifications_expires_at
Columns: expires_at
idx_notifications_organization_id
Columns: organization_id
Validation Rules
title_not_empty
error
Validation failed
body_not_empty
error
Validation failed
valid_category_type_combination
error
Validation failed
user_id_references_existing_user
error
Validation failed
related_entity_type_required_when_id_present
error
Validation failed
metadata_valid_json
error
Validation failed
read_at_requires_is_read_true
error
Validation failed
expires_at_must_be_future_on_create
error
Validation failed
Business Rules
user_scope_isolation
A user may only read, update, or delete their own notification records. Supabase RLS policy enforces auth.uid() = user_id on all SELECT, UPDATE, and DELETE operations. No user may access another user's notification inbox.
category_preference_gate
Before inserting a notification for a user, the scenario engine must check that the user has not disabled the notification category in push_notification_configs. If the category is disabled, no record is created and no push is dispatched.
unread_count_consistency
The unread notification count displayed in the bottom navigation bar badge must be derived from a live COUNT query on is_read=false for the current user, never from a cached integer field, to prevent stale counts after bulk operations.
mark_read_on_tap
When a user taps a notification item and navigates via its deep link, is_read must be set to true and read_at recorded atomically within the same transaction. Navigation may not be blocked waiting for the update.
deep_link_must_be_valid_route
The deep_link value must correspond to a registered GoRouter named route. The scenario engine validates the link against the router's route table before persisting. Invalid deep links are nulled rather than stored, preventing broken navigation.
no_duplicate_scenario_notifications
For scenario types that are idempotent by nature (e.g. certificate_expiry_60days for a given certificate), the scenario engine checks for an existing unread notification of the same type and related_entity_id before creating a new one. Duplicate scenario notifications within a 24-hour window are suppressed.
expired_notifications_hidden
Notifications with an expires_at value in the past must be excluded from the active notification list and the unread count. They are retained in the database for audit purposes but the repository applies a WHERE expires_at IS NULL OR expires_at > NOW() filter on all user-facing queries.
real_time_delivery
All notification inserts must trigger a Supabase Realtime event on the notifications table scoped to the recipient user_id channel. The mobile client subscribes to this channel to receive instant in-app badge updates without polling.
CRUD Operations
Storage Configuration
Entity Relationships
A user receives multiple in-app and push notifications across all scenario categories