remindersms
Table: remindersms
The remindersms table stores SMS reminders sent to recipients.
It tracks the schedule, content, status, and associations with events, users, and reminders.
Columns
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| rsmsRecordNum | int(11) | NOT NULL, PRIMARY KEY, AUTO_INCREMENT | Unique identifier for each reminder SMS |
| createdDate | datetime | DEFAULT NULL | Timestamp when the SMS record was created |
| dayNumber | smallint(6) | NOT NULL | Number of days from the event for this SMS |
| description | varchar(255) | DEFAULT NULL | Optional description of the SMS |
| dueDate | datetime | NOT NULL | Scheduled date and time to send the SMS |
| hoursDifference | int(11) | DEFAULT NULL | Optional difference in hours relative to dueDate |
| lastEditedDate | datetime | DEFAULT NULL | Timestamp of last edit |
| originator | varchar(20) | DEFAULT NULL | Sender identifier |
| recipient | varchar(20) | DEFAULT NULL | Recipient phone number |
| referenceNumber | varchar(255) | UNIQUE | Unique reference number for the SMS |
| reminderId | smallint(6) | DEFAULT NULL | Linked reminder ID |
| reminderStatus | varchar(20) | DEFAULT NULL | Status of the SMS (e.g., SCHEDULED, SENT) |
| sentDate | datetime | DEFAULT NULL | Timestamp when SMS was sent |
| smsCancelReason | varchar(255) | DEFAULT NULL | Reason for cancellation, if any |
| smsCancelReasonOther | varchar(255) | DEFAULT NULL | Additional explanation for cancellation |
| text | varchar(2000) | DEFAULT NULL | SMS message content |
| vaccinationRecordNum | int(11) | DEFAULT NULL | Related vaccination record, if applicable |
| createdByUserId | int(11) | DEFAULT NULL | User who created the SMS record |
| lastEditedByUserId | int(11) | DEFAULT NULL | User who last edited the SMS record |
| womenAnalysisRecordNum | int(11) | DEFAULT NULL | Linked women analysis record |
Indexes
- PRIMARY: Primary key on
rsmsRecordNum - UNIQUE:
referenceNumber remindersms_reminderId_reminder_reminderId_FK: Index onreminderIdremindersms_lastEditedByUserId_user_mappedId_FK: Index onlastEditedByUserIdremindersms_createdByUserId_user_mappedId_FK: Index oncreatedByUserIdremindersms_vaccRecNumIndex: Index onvaccinationRecordNumwomenanalysis: Index onwomenAnalysisRecordNumidx_remindersms_dueDate_reminderStatus: Composite index on (dueDate,reminderStatus) for scheduled SMS queries
Foreign Key Relations
createdByUserId→user.mappedIdlastEditedByUserId→user.mappedIdreminderId→reminder.reminderId
Usage Notes
- Stores all SMS reminders generated for events like vaccination, appointments, or system notifications.
reminderStatustracks SMS lifecycle (SCHEDULED, SENT, CANCELLED).- Supports linking with vaccination records and women analysis records for reporting.
- Composite index (
dueDate,reminderStatus) improves query performance for scheduled SMS counts.