Reminder
Data Entity
Description
A scheduled notification record generated by the scenario-based reminder engine. Covers automatic follow-up reminders (10-day no-contact), certification expiry alerts at configurable lead times, and coordinator inactivity warnings. The Reminder Service evaluates scenario rules on each cron cycle and writes records with scheduled delivery timestamps.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key. Uniquely identifies a scheduled reminder record. | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the users table. The recipient of this reminder notification. | required |
scenario_type |
enum |
Identifies the business scenario that triggered this reminder. Drives display copy, routing logic, and deduplication. | required |
entity_type |
enum |
The domain entity type this reminder is about. Used together with entity_id to link the reminder to its source record for deep-linking and deduplication. | required |
entity_id |
uuid |
The primary key of the source entity referenced by entity_type. Enables deep-linking from the notification to the relevant record and deduplication (one reminder per scenario+entity combination). | required |
scheduled_at |
datetime |
UTC timestamp at which this reminder should be delivered by the cron consumer. Set at creation time by the Reminder Service based on scenario rule lead times. | required |
delivered_at |
datetime |
UTC timestamp when the FCM push notification was successfully dispatched. NULL means pending or cancelled. Set by Scheduled Notification Infrastructure after successful delivery. | - |
cancelled_at |
datetime |
UTC timestamp when this reminder was explicitly cancelled before delivery. Set when the triggering condition resolves (e.g., contact made, certification renewed, assignment read). | - |
cancellation_reason |
enum |
Machine-readable reason explaining why this reminder was cancelled before delivery. | - |
payload |
json |
Structured JSON containing the notification title, body copy, deep-link target, and any entity-specific context data (e.g., contact name, expiry date, days elapsed). Consumed directly by Scheduled Notification Infrastructure when building FCM payloads. | required |
delivery_attempts |
integer |
Count of FCM delivery attempts made for this reminder. Incremented on each attempt by Scheduled Notification Infrastructure. Used to enforce a maximum retry ceiling. | required |
last_attempt_at |
datetime |
UTC timestamp of the most recent FCM delivery attempt. Used with delivery_attempts to implement exponential backoff logic. | - |
notification_id |
uuid |
Foreign key referencing the notifications table entry created upon successful delivery. NULL until the reminder is delivered. Enables bidirectional linkage between the scheduled record and the delivered notification. | - |
created_at |
datetime |
UTC timestamp when this reminder record was created by the Reminder Service. | required |
updated_at |
datetime |
UTC timestamp of the last status update (delivery, cancellation, or retry). Maintained by the application layer. | required |
Database Indexes
idx_reminder_user_id
Columns: user_id
idx_reminder_scheduled_at
Columns: scheduled_at
idx_reminder_due_pending
Columns: scheduled_at, delivered_at, cancelled_at
idx_reminder_entity
Columns: entity_type, entity_id
idx_reminder_dedup
Columns: user_id, scenario_type, entity_id, delivered_at, cancelled_at
idx_reminder_scenario_type
Columns: scenario_type
Validation Rules
scheduled_at_must_be_future_on_create
error
Validation failed
entity_id_required_with_entity_type
error
Validation failed
payload_schema_valid
error
Validation failed
user_id_references_active_user
error
Validation failed
cancellation_reason_required_when_cancelled
error
Validation failed
delivery_attempts_non_negative
error
Validation failed
scenario_type_entity_type_compatibility
error
Validation failed
Business Rules
no_duplicate_pending_reminder
At most one undelivered, uncancelled reminder may exist per (user_id, scenario_type, entity_id) combination at any time. The Reminder Service checks for an existing pending record before inserting a new one on each cron cycle. Prevents reminder flooding when a condition persists across multiple evaluation cycles.
auto_cancel_on_condition_resolved
When the triggering condition for a pending reminder is resolved (e.g., activity registered for the contact, certification renewed, assignment marked as read), the Reminder Service must immediately set cancelled_at and cancellation_reason on all matching pending reminders for that (scenario_type, entity_id) pair. Prevents delivery of stale alerts.
certification_expiry_lead_times
Certification expiry reminders must be scheduled at exactly two lead times: 30 days and 7 days before the certification's expiry_date. The Certificate Expiry Scheduler creates both records during each nightly evaluation pass, subject to the no-duplicate rule.
no_contact_window_10_days
A no_contact_10_days reminder must only be scheduled when no activity with the referenced contact has been recorded by the peer mentor within the past 10 days. Evaluated on each cron tick by the Reminder Service.
max_delivery_attempts
If delivery_attempts reaches 5 without a successful delivery, the reminder must be marked cancelled with cancellation_reason = 'superseded_by_newer_reminder' and the failure logged. No further delivery attempts should be made.
respect_notification_settings
Before scheduling or delivering a reminder, the Reminder Service must verify that the target user has not opted out of the corresponding scenario_type in their notification_settings record. Opt-out suppresses both scheduling and delivery.
delivered_and_cancelled_mutually_exclusive
A reminder record must not have both delivered_at and cancelled_at set. Once delivered, it cannot be cancelled; once cancelled, it cannot be delivered. Enforced at the service layer before any status update.
CRUD Operations
Storage Configuration
Entity Relationships
A certification generates expiry reminder records at configured lead times (30 days, 7 days before expiry)
A user has many scheduled reminders generated by the scenario evaluation engine