Assignment Delivery
Data Entity
Description
Tracks the delivery, read confirmation, and threshold counting of encrypted assignments dispatched to peer mentors. Supports honorarium tier triggers (3rd and 15th assignment per accounting period) and auto-reminder logic for unread assignments.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key | PKrequiredunique |
assignment_id |
uuid |
Foreign key to the parent encrypted assignment | required |
peer_mentor_id |
uuid |
Foreign key to the peer mentor user who received the assignment | required |
organization_id |
uuid |
Tenant isolation — organization owning this delivery record | required |
delivery_status |
enum |
Lifecycle state of the delivery: pending (queued), delivered (push sent), opened (peer mentor viewed), read_confirmed (explicit acknowledgement), expired (no response within retention window) | required |
delivered_at |
datetime |
Timestamp when the assignment was dispatched to the peer mentor's inbox | - |
opened_at |
datetime |
Timestamp when the peer mentor first opened the assignment detail screen. Must be >= delivered_at. | - |
read_confirmed_at |
datetime |
Timestamp of explicit read confirmation by the peer mentor. Immutable once set. Must be >= opened_at. | - |
accounting_period |
string |
Accounting period key used for honorarium threshold counting, e.g. '2025-H1'. Format: YYYY-H[1|2] for half-year periods. | required |
delivery_sequence_number |
integer |
The peer mentor's cumulative assignment count within the accounting_period at the moment of delivery. Used to determine honorarium tier eligibility without re-counting. | required |
honorarium_tier |
enum |
Honorarium tier triggered by this delivery. none = below threshold; tier_1 = 3rd assignment in period (office honorarium); tier_2 = 15th assignment in period (higher rate). | required |
read_receipt_required |
boolean |
Whether explicit read confirmation is required for this delivery (set by coordinator at dispatch time) | required |
reminder_sent_at |
datetime |
Timestamp of the most recent auto-reminder notification sent. Null if no reminder has been sent. | - |
reminder_count |
integer |
Number of auto-reminders sent for this delivery. Auto-reminder fires after 10 days of no contact establishment. | required |
delivery_method |
enum |
Channel used to notify the peer mentor of the new assignment | required |
coordinator_id |
uuid |
Foreign key to the coordinator who dispatched the assignment | required |
expires_at |
datetime |
Optional expiry timestamp after which the delivery is marked expired if unconfirmed | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Last modification timestamp | required |
Database Indexes
idx_assignment_deliveries_assignment_id
Columns: assignment_id
idx_assignment_deliveries_peer_mentor_id
Columns: peer_mentor_id
idx_assignment_deliveries_assignment_peer_mentor
Columns: assignment_id, peer_mentor_id
idx_assignment_deliveries_accounting_period
Columns: peer_mentor_id, accounting_period
idx_assignment_deliveries_status
Columns: delivery_status
idx_assignment_deliveries_org_period
Columns: organization_id, accounting_period
Validation Rules
opened_at_after_delivered_at
error
Validation failed
read_confirmed_at_after_opened_at
error
Validation failed
accounting_period_format
error
Validation failed
peer_mentor_role_required
error
Validation failed
delivery_sequence_positive
error
Validation failed
assignment_must_be_active
error
Validation failed
Business Rules
immutable_after_read_confirmed
Once read_confirmed_at is set, the delivery record becomes append-only. No field except updated_at may be changed. Ensures audit integrity for honorarium calculations.
honorarium_tier_assignment
At the moment of delivery, compute delivery_sequence_number by counting existing deliveries for the peer mentor in the same accounting_period. Set honorarium_tier to tier_1 if sequence == 3, tier_2 if sequence == 15, otherwise none.
auto_reminder_after_ten_days
If opened_at is still null 10 days after delivered_at, trigger a reminder notification to the peer mentor and increment reminder_count. Sets reminder_sent_at.
one_delivery_per_assignment_per_mentor
Each peer mentor may have at most one delivery record per assignment. Enforced via unique index on (assignment_id, peer_mentor_id).
status_lifecycle_ordering
delivery_status transitions must follow the defined lifecycle: pending → delivered → opened → read_confirmed. Transitions may not skip states or move backwards except to expired from any non-confirmed state.
coordinator_owns_dispatch
Only a coordinator or org admin belonging to the same organization as the peer mentor may create a delivery record. Cross-organization dispatch is prohibited.