Assignment
Data Entity
Description
Represents the formal pairing of a peer mentor to a contact for ongoing support. Tracks status (open, pending, completed) and the date of last recorded contact, which drives follow-up reminder scheduling. In Blindeforbundet, cumulative assignment counts trigger honorarium threshold events at the 3rd and 15th assignments.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the assignment record, auto-generated on insert. | PKrequiredunique |
peer_mentor_id |
uuid |
Foreign key referencing the peer mentor assigned to support the contact. Immutable after creation — reassignment requires closing this record and creating a new one. | required |
contact_id |
uuid |
Foreign key referencing the contact (service recipient) being supported. Immutable after creation. | required |
organization_id |
uuid |
Foreign key referencing the organization this assignment belongs to. Required for Supabase RLS multi-tenant data isolation — all queries are scoped by this field. | required |
status |
enum |
Current lifecycle status of the assignment. 'open' means active and ongoing. 'pending' means awaiting initial contact or a specific coordinator follow-up action. 'completed' means the assignment has formally concluded. Status may only advance forward — regression is rejected. | required |
last_contact_date |
datetime |
UTC timestamp of the most recently recorded activity or contact interaction for this assignment. Updated by the activity recording pipeline whenever a new activity linked to this assignment is saved. Null if no contact has been recorded yet. Drives the reminder-scheduler-service's nightly evaluation: assignments where (NOW - last_contact_date) exceeds the org-configured threshold trigger follow-up notifications. | - |
mentor_assignment_sequence |
integer |
The ordinal sequence number of this assignment for the peer mentor within their organization, computed atomically at insert time as (count of all prior assignments for this peer_mentor_id + organization_id) + 1. Drives Blindeforbundet honorarium threshold events at ordinal 3 and 15. Must be a positive integer. | required |
created_at |
datetime |
UTC timestamp when the assignment record was created. Set automatically by the database on insert and never modified. | required |
closed_at |
datetime |
UTC timestamp when the assignment was formally closed (status transitioned to 'completed'). Null for all active assignments. Set atomically alongside the status update and immutable thereafter. | - |
created_by |
uuid |
User ID of the coordinator or org admin who created the assignment. Used for audit trail and coordinator-scoped queries. Nullable for legacy or system-created records. | - |
closed_by |
uuid |
User ID of the coordinator or org admin who closed the assignment. Null until status reaches 'completed'. | - |
notes |
text |
Optional free-text coordinator notes about the assignment context, referral reason, or background information. Not encrypted. Max 2000 characters. | - |
honorarium_threshold_triggered |
json |
JSONB record of honorarium threshold events triggered by this assignment's mentor_assignment_sequence. Tracks which milestones have already been dispatched to prevent duplicate notifications. Example: {"threshold_3": "2025-03-01T10:00:00Z", "threshold_15": null}. Blindeforbundet-specific; null for other organizations. | - |
Database Indexes
idx_assignment_peer_mentor_id
Columns: peer_mentor_id
idx_assignment_contact_id
Columns: contact_id
idx_assignment_organization_id
Columns: organization_id
idx_assignment_org_status
Columns: organization_id, status
idx_assignment_peer_mentor_status
Columns: peer_mentor_id, status
idx_assignment_status_last_contact
Columns: status, last_contact_date
idx_assignment_peer_mentor_org_sequence
Columns: peer_mentor_id, organization_id, mentor_assignment_sequence
Validation Rules
peer_mentor_must_be_active_on_create
error
Validation failed
contact_and_mentor_belong_to_same_organization
error
Validation failed
status_enum_constraint
error
Validation failed
last_contact_date_not_in_future
error
Validation failed
closed_at_requires_completed_status
error
Validation failed
peer_mentor_id_immutable
error
Validation failed
contact_id_immutable
error
Validation failed
mentor_assignment_sequence_positive
error
Validation failed
notes_max_length
error
Validation failed
Business Rules
honorarium_threshold_3rd_assignment
When mentor_assignment_sequence reaches 3 for a peer mentor within a Blindeforbundet organization, a honorarium threshold event must be triggered and recorded in honorarium_threshold_triggered.threshold_3. The event drives a coordinator notification and potential payout action. Idempotent — fires only once per assignment record.
honorarium_threshold_15th_assignment
When mentor_assignment_sequence reaches 15 for a peer mentor within a Blindeforbundet organization, a higher-rate honorarium threshold event must be triggered and recorded in honorarium_threshold_triggered.threshold_15. Idempotent — fires only once per assignment record.
last_contact_date_updated_on_activity
Whenever a new activity is recorded that is associated with this assignment's peer_mentor_id and contact_id combination, last_contact_date must be updated to the activity's recorded date. This update resets the reminder countdown clock.
reminder_dispatched_after_contact_threshold
The nightly cron job evaluates all open assignments. If (NOW - last_contact_date) exceeds the organization's configured reminder threshold (default 10 days for Blindeforbundet), a follow-up notification is dispatched to the peer mentor. If a secondary escalation threshold is also exceeded, the coordinator is notified. Threshold is configurable per organization via reminder_config.
closed_at_set_atomically_on_completion
When status transitions to 'completed', closed_at must be set to the current UTC timestamp and closed_by to the acting user's ID in the same database transaction. Once set, neither field may be modified.
status_forward_only_progression
Assignment status may only advance in one direction: open → pending → completed, or open → completed. Any attempted regression (e.g., completed → open) is rejected at the service layer. Reassignment after completion requires creating a new assignment record.
mentor_sequence_computed_atomically
mentor_assignment_sequence must be computed as (SELECT COUNT(*) FROM assignments WHERE peer_mentor_id = $1 AND organization_id = $2) + 1, executed atomically within the insert transaction using a row-level lock or serializable isolation to prevent race conditions in concurrent assignment creation.
organization_rls_isolation
All read and write operations on the assignments table are governed by Supabase RLS policies that scope results to the authenticated user's organization_id. Cross-organization access is prohibited at the database level.
CRUD Operations
Storage Configuration
Entity Relationships
A contact may be assigned to peer mentors sequentially or simultaneously depending on organizational rules
A peer mentor holds multiple active and historical assignments to contacts across their service tenure