Assignment Status Log
Data Entity
Description
Immutable audit trail recording every status transition for encrypted assignments dispatched to peer mentors. Captures who triggered the change, when it occurred, and contextual metadata — enabling delivery confirmation, read acknowledgement tracking, automatic reminder scheduling, and threshold-based honorarium calculations.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key for the log entry | PKrequiredunique |
assignment_id |
uuid |
Foreign key referencing the assignment whose status changed | required |
status |
enum |
The new status recorded by this log entry | required |
previous_status |
enum |
The status immediately before this transition, null for the initial dispatch entry | - |
changed_by_user_id |
uuid |
User who triggered the transition; null when the change is system-generated (e.g., automatic reminder, expiry job) | - |
changed_at |
datetime |
UTC timestamp of when the status transition occurred | required |
is_system_generated |
boolean |
True when the entry was created by an automated job (reminder scheduler, expiry check) rather than a user action | required |
trigger_source |
enum |
The system component or interaction channel that caused this status change | required |
device_platform |
enum |
Mobile platform from which a user-initiated transition originated; null for system-generated entries | - |
notification_delivery_id |
string |
Provider message ID from the push notification gateway confirming delivery, populated only for 'delivered' status entries | - |
metadata |
json |
Arbitrary structured context for the transition: reminder sequence number, expiry deadline, coordinator notes, etc. | - |
ip_address |
string |
Originating IP address of the actor for user-initiated transitions; supports security and compliance auditing | - |
Database Indexes
idx_assignment_status_logs_assignment_id
Columns: assignment_id
idx_assignment_status_logs_assignment_changed_at
Columns: assignment_id, changed_at
idx_assignment_status_logs_changed_at
Columns: changed_at
idx_assignment_status_logs_status
Columns: status
idx_assignment_status_logs_changed_by_user_id
Columns: changed_by_user_id
Validation Rules
assignment_id_exists
error
Validation failed
changed_at_not_future
error
Validation failed
status_enum_valid
error
Validation failed
notification_delivery_id_only_on_delivered
warning
Validation failed
ip_address_format
error
Validation failed
metadata_valid_json
error
Validation failed
Business Rules
immutable_audit_record
Log entries must never be updated after creation. The table is append-only; corrections are made by appending a new corrective entry, not modifying existing rows.
valid_status_transition
Status transitions must follow the defined state machine: dispatched → delivered → opened → read → in_progress → completed. reminder_sent and expired are side-state entries that do not block the primary flow. cancelled can only be set by a coordinator.
previous_status_matches_latest
When creating a new log entry, previous_status must equal the current latest status entry for the same assignment_id. Prevents race-condition gaps in the audit chain.
system_entries_have_no_user
If is_system_generated is true, changed_by_user_id must be null. If is_system_generated is false, changed_by_user_id must be set.
reminder_after_10_days
If no status change has moved an assignment past 'dispatched' within 10 days of dispatch, the reminder_job creates a reminder_sent log entry and triggers a push notification to the assigned peer mentor.
threshold_honorarium_trigger
When a 'completed' entry is created, threshold-tracking-service is notified to re-evaluate the peer mentor's cumulative completed count. Completing the 3rd assignment triggers office honorarium; the 15th triggers the higher-rate honorarium.
coordinator_only_cancel
A 'cancelled' status entry may only be created when changed_by_user_id belongs to a coordinator or org admin role. Peer mentors cannot cancel assignments themselves.