Assignment Dispatch
Data Entity
Description
Records each delivery attempt and state transition for an assignment, forming an immutable audit trail from initial dispatch through delivery confirmation and acknowledgement. Drives the 10-day auto-reminder logic and coordinator status dashboards.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key for this dispatch event record, generated server-side on insert | PKrequiredunique |
assignment_id |
uuid |
Foreign key referencing the parent assignment this dispatch event belongs to. Multiple dispatch records accumulate per assignment as it progresses through its lifecycle. | required |
status |
enum |
The assignment lifecycle state captured by this immutable dispatch record. Each insert represents one state transition in the audit trail. | required |
previous_status |
enum |
The assignment status before this transition, captured for full audit trail reconstruction without requiring temporal queries across rows. | - |
actor_id |
uuid |
The user who triggered this state transition. NULL for system-automated transitions such as delivery webhook callbacks, cron-triggered reminders, and auto-expiry. References users.id. | - |
dispatched_at |
datetime |
Timestamp when this dispatch event record was created (i.e., when the state transition occurred). Immutable after insert. Used as the reference clock for 10-day reminder threshold calculations. | required |
delivery_confirmed_at |
datetime |
Timestamp when FCM/APNs delivery was confirmed for this dispatch attempt. Populated only when status is delivery_confirmed or later. NULL for failed and reminder_sent records. | - |
transition_reason |
text |
Human-readable or system-generated explanation for this state transition. Required for failed and reminder_sent status values. Optional for standard lifecycle transitions. Stored for coordinator audit views. | - |
fcm_message_id |
string |
The FCM message ID returned by the Push Notification Gateway on dispatch. Used to correlate delivery webhook callbacks from FCM to this specific dispatch record. NULL for non-push transitions. | - |
reminder_count |
integer |
Running count of how many auto-reminders have been sent for the parent assignment as of this dispatch record. Populated only when status is reminder_sent. Prevents unbounded reminder escalation. | - |
created_at |
datetime |
Server-set creation timestamp for this record. Identical to dispatched_at for application-layer inserts; may differ by milliseconds for trigger-generated records. | required |
Database Indexes
idx_assignment_dispatches_assignment_id
Columns: assignment_id
idx_assignment_dispatches_assignment_status
Columns: assignment_id, status
idx_assignment_dispatches_dispatched_at
Columns: dispatched_at
idx_assignment_dispatches_status
Columns: status
idx_assignment_dispatches_actor_id
Columns: actor_id
idx_assignment_dispatches_status_dispatched_at
Columns: status, dispatched_at
Validation Rules
assignment_id_exists
error
Validation failed
status_not_null
error
Validation failed
dispatched_at_not_future
error
Validation failed
delivery_confirmed_at_ordering
error
Validation failed
transition_reason_required_for_failure
error
Validation failed
fcm_message_id_format
warning
Validation failed
reminder_count_non_negative
error
Validation failed
actor_id_references_valid_user
error
Validation failed
Business Rules
immutability
Assignment dispatch records are strictly append-only. No UPDATE or DELETE operations are permitted after insert. Each state change creates a new row, preserving the complete audit trail for compliance and coordinator visibility. The only exception is cascade delete via assignments.id foreign key when an assignment is removed.
valid_status_transition
Status transitions must follow the valid assignment lifecycle DAG: dispatched → delivery_confirmed → read → acknowledged (happy path); dispatched → failed (push failure); dispatched or delivery_confirmed → reminder_sent (auto-reminder); dispatched → expired (10-day threshold with no response). Transitions that skip mandatory intermediate states are rejected.
single_terminal_state
Once an assignment reaches a terminal dispatch status (acknowledged, expired), the auto-reminder service must not create further reminder_sent records for that assignment. The reminder scan queries for the latest dispatch status per assignment_id before enqueuing.
ten_day_reminder_threshold
Assignments whose most recent dispatch record has status dispatched or delivery_confirmed and whose dispatched_at timestamp is older than 10 days must trigger a reminder_sent dispatch record. The reminder-cron-edge-function invokes auto-reminder-service daily to enforce this Blindeforbundet business requirement.
actor_required_for_manual_transitions
State transitions triggered by a human actor (coordinator dispatching, peer mentor acknowledging) must supply a non-null actor_id. System-automated transitions (FCM delivery callback, cron reminder, auto-expiry) must set actor_id to NULL to distinguish automated from human actions in audit views.
realtime_coordinator_notification
On each new dispatch record insert, Supabase Realtime must broadcast the change to all coordinator clients subscribed to the assignments channel for the relevant organization. This drives live coordinator status dashboard updates without polling.
reminder_count_cap
The reminder_count field on reminder_sent records must not exceed 3. Once three reminders have been sent without acknowledgement, the assignment transitions to expired rather than continuing to issue reminders, preventing notification fatigue for the peer mentor.
rls_scope_enforcement
Supabase RLS policies restrict read access to dispatch records to: (1) the assignment recipient (peer mentor), (2) the coordinator who owns the assignment, and (3) organization admins within the same org scope. Cross-organization reads are blocked at the database layer.
CRUD Operations
Storage Configuration
Entity Relationships
Each dispatch event may generate a read receipt when the recipient views the decrypted content
An assignment accumulates immutable dispatch records tracking each delivery state transition