Assignment
Data Entity
Description
Encrypted task dispatched by a coordinator to a peer mentor, containing sensitive personal information such as name, address, and medical summaries (epikrise). Assignments track their full lifecycle from dispatch through delivery, read receipt, and completion, with 10-day auto-reminders.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, generated server-side via gen_random_uuid(). Immutable after creation. | PKrequiredunique |
coordinator_id |
uuid |
Foreign key referencing users.id. The coordinator who created and dispatched this assignment. Immutable after creation. | required |
recipient_peer_mentor_id |
uuid |
Foreign key referencing peer_mentors.id. The target peer mentor who is the intended recipient of the encrypted payload. Immutable after creation. | required |
organization_id |
uuid |
Foreign key referencing organizations.id. Scopes the assignment to an organization for RLS enforcement, Bufdir reporting isolation, and honorarium counting. | required |
encrypted_payload |
text |
Base64-encoded ciphertext of the sensitive assignment content (name, address, medical summary/epikrise). Encrypted with the recipient peer mentor's RSA/EC public key via libsodium before storage. Never stored in plaintext. | required |
encryption_algorithm |
string |
Identifier of the encryption algorithm used for the payload (e.g., 'libsodium_box', 'x25519_xchacha20_poly1305'). Required for forward-compatible decryption. | required |
recipient_public_key_fingerprint |
string |
SHA-256 fingerprint of the recipient's public key used during encryption. Allows the recipient's device to identify the correct private key for decryption when multiple key versions exist. | required |
status |
enum |
Current lifecycle state of the assignment. Transitions must follow the valid state machine: draft → dispatched → delivered → read → acknowledged → completed. Cancelled is a terminal state reachable from any non-completed state. | required |
title |
string |
Non-sensitive descriptive label for the assignment visible to the coordinator in the status dashboard. Must not contain any personally identifiable information (PII) as it is stored in plaintext. | - |
assignment_type |
enum |
Categorical type of the assignment task. Drives UI presentation and honorarium counting rules per organization configuration. | required |
dispatched_at |
datetime |
Timestamp when the assignment transitioned to 'dispatched' status and the encrypted payload was submitted to Supabase. Null if assignment is still in draft. Used as the reference timestamp for 10-day auto-reminder calculations. | - |
last_state_change_at |
datetime |
Timestamp of the most recent status transition. Updated by the assignment tracking service on every state change. Used by Auto Reminder Service to identify stale assignments exceeding the 10-day inactivity threshold. | required |
reminder_sent_at |
datetime |
Timestamp when the most recent 10-day auto-reminder push notification was dispatched. Null if no reminder has been sent. Prevents duplicate reminder notifications during the same reminder cycle. | - |
reminder_count |
integer |
Cumulative count of auto-reminder notifications sent for this assignment. Used to determine escalation behavior if multiple reminder cycles pass without acknowledgement. | required |
honorarium_eligible |
boolean |
Indicates whether completing this assignment contributes to the recipient peer mentor's honorarium threshold count. Set by organization configuration at dispatch time. Drives honorarium calculation service logic. | required |
counted_for_honorarium |
boolean |
Indicates whether this assignment has already been counted in the peer mentor's honorarium threshold tally. Set to true by the assignment count trigger on acknowledgement. Prevents double-counting on reprocessing. | required |
coordinator_notes |
text |
Optional plaintext internal notes from the coordinator, visible only to coordinator-role users. Must not contain PII — all sensitive information must be in encrypted_payload only. | - |
is_urgent |
boolean |
Coordinator-set urgency flag. When true, the assignment is highlighted in the recipient's inbox and the coordinator's status dashboard. Does not alter the 10-day reminder threshold but may trigger additional push notification priority. | required |
payload_integrity_hash |
string |
HMAC-SHA256 hash of the encrypted_payload, computed before storage to detect tampering. Validated by Task Encryption Service during decryption to ensure payload integrity. | required |
created_at |
datetime |
Record creation timestamp, set server-side by Supabase default. Immutable after insert. | required |
updated_at |
datetime |
Record last-update timestamp, maintained by a Supabase trigger on every row mutation. Used for optimistic concurrency checks. | required |
Database Indexes
idx_assignments_coordinator_id
Columns: coordinator_id
idx_assignments_recipient_peer_mentor_id
Columns: recipient_peer_mentor_id
idx_assignments_organization_id
Columns: organization_id
idx_assignments_status
Columns: status
idx_assignments_organization_status
Columns: organization_id, status
idx_assignments_last_state_change_at
Columns: last_state_change_at
idx_assignments_coordinator_status
Columns: coordinator_id, status
idx_assignments_recipient_status
Columns: recipient_peer_mentor_id, status
idx_assignments_dispatched_at
Columns: dispatched_at
idx_assignments_honorarium_eligible_counted
Columns: recipient_peer_mentor_id, honorarium_eligible, counted_for_honorarium
Validation Rules
coordinator_id_references_valid_coordinator_role
error
Validation failed
recipient_peer_mentor_must_be_active
error
Validation failed
encrypted_payload_not_empty
error
Validation failed
payload_integrity_hash_must_match
error
Validation failed
encryption_algorithm_must_be_supported
error
Validation failed
assignment_type_required_and_valid
error
Validation failed
title_must_not_contain_pii_keywords
warning
Validation failed
organization_id_matches_coordinator_membership
error
Validation failed
organization_id_matches_recipient_membership
error
Validation failed
reminder_count_non_negative
error
Validation failed
Business Rules
payload_must_be_encrypted_before_storage
The encrypted_payload field must contain only ciphertext produced by the Task Encryption Service using the recipient's public key. Plaintext personal data (name, address, epikrise) must never reach the database layer. Enforcement is the responsibility of the Encrypted Message Service before any repository call.
only_coordinators_may_create_assignments
Assignment creation is restricted to users holding coordinator or admin roles within the target organization. Supabase RLS policies enforce this at the database layer by checking the authenticated user's JWT role claim against the organization_id.
organization_scoped_rls_enforcement
All SELECT, INSERT, UPDATE, and DELETE operations on assignments are restricted to rows where organization_id matches the authenticated user's active organization context from their JWT claims. Coordinators see only their org's assignments; peer mentors see only their own.
status_machine_must_follow_valid_transitions
Status transitions must respect the defined state machine: draft → dispatched → delivered → read → acknowledged → completed. Backwards transitions (e.g., acknowledged → dispatched) are forbidden. The cancelled state may be reached from any non-completed state. All transitions are recorded as immutable events in assignment_dispatches.
ten_day_auto_reminder_on_inactivity
If an assignment remains in 'dispatched' or 'delivered' status without progressing to 'read' or 'acknowledged' within 10 days of last_state_change_at, the Auto Reminder Service dispatches a push notification to the peer mentor and alerts the coordinator. reminder_count is incremented and reminder_sent_at is updated to prevent duplicate sends.
honorarium_threshold_counted_on_acknowledgement
When an assignment transitions to 'acknowledged' or 'completed' and honorarium_eligible is true and counted_for_honorarium is false, the Assignment Count Trigger increments the peer mentor's assignment count in assignment_honorarium_records and sets counted_for_honorarium to true. Threshold evaluation runs immediately after count update.
read_receipt_recorded_on_first_payload_decryption
The first successful decryption of the encrypted_payload by the peer mentor's device triggers an immediate read receipt write to assignment_read_receipts and advances status from 'delivered' to 'read'. Duplicate receipt writes are blocked by the Read Receipt Service idempotency check.
delivery_confirmation_recorded_on_payload_download
When the peer mentor's device downloads the encrypted_payload (regardless of decryption), a delivery confirmation event is written to assignment_dispatches and status advances from 'dispatched' to 'delivered'. The coordinator is notified in real time via Supabase Realtime channel.
coordinator_notes_must_not_contain_pii
The coordinator_notes field is stored in plaintext and must not contain personally identifiable information. All PII (name, address, medical data) must be placed exclusively in the encrypted_payload. This is a policy rule enforced by documentation and UI guidance — the Task Assignment Screen labels the notes field clearly as non-sensitive only.
recipient_public_key_must_exist_before_dispatch
An assignment cannot be dispatched if the recipient peer mentor has not registered a public key in their Supabase user profile. The Key Management Service verifies key existence before the Task Encryption Service proceeds with payload encryption.
CRUD Operations
Storage Configuration
Entity Relationships
An assignment accumulates immutable dispatch records tracking each delivery state transition
Assignments are scoped to an organization for RLS enforcement and reporting
Each assignment targets a specific peer mentor as the encrypted payload recipient
Driver honorariums may be linked to the specific assignment that required the driving engagement