Assignment Read Receipt
Data Entity
Description
Immutable record confirming that a peer mentor has opened and viewed the decrypted assignment content. Prevents duplicate receipt writes and provides coordinators with confirmation that sensitive personal information has been received and read.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key generated server-side (gen_random_uuid()). Never reused. | PKrequiredunique |
assignment_dispatch_id |
uuid |
Foreign key referencing the specific dispatch event (assignment_dispatches.id) that this read receipt acknowledges. A dispatch may have at most one read receipt per user. | required |
user_id |
uuid |
Foreign key referencing the peer mentor (users.id) who opened and viewed the decrypted assignment content. Must match the recipient recorded on the parent assignment. | required |
read_at |
datetime |
UTC timestamp at which the peer mentor's device rendered the decrypted assignment content, set server-side at write time via now(). Never client-supplied. | required |
device_platform |
enum |
Platform identifier of the device on which the assignment was read. Used for audit trail completeness and future support diagnostics. | - |
app_version |
string |
Semantic version string of the Flutter app build that rendered the decrypted content (e.g. '1.4.2+42'). Captured from package_info_plus at write time for audit reproducibility. | - |
created_at |
datetime |
Database-level insertion timestamp (default now()). Redundant with read_at by design — read_at represents the application-layer event, created_at the persistence event. Both are immutable. | required |
Database Indexes
idx_assignment_read_receipts_pkey
Columns: id
idx_assignment_read_receipts_dispatch_user_unique
Columns: assignment_dispatch_id, user_id
idx_assignment_read_receipts_dispatch_id
Columns: assignment_dispatch_id
idx_assignment_read_receipts_user_id
Columns: user_id
idx_assignment_read_receipts_read_at
Columns: read_at
Validation Rules
assignment_dispatch_id_exists
error
Validation failed
user_id_exists
error
Validation failed
read_at_not_in_future
error
Validation failed
device_platform_valid_enum
error
Validation failed
app_version_format
warning
Validation failed
no_duplicate_insert_race
info
Validation failed
Business Rules
immutable_after_insert
Once a read receipt row is inserted it MUST NOT be updated or deleted by application code. It is an append-only audit record. Supabase RLS policies must deny UPDATE and DELETE for all non-service roles. The only permissible delete path is cascade-delete triggered by removal of the parent assignment_dispatches row, which itself requires admin-level authority.
one_receipt_per_dispatch_per_user
A single peer mentor may generate at most one read receipt per assignment dispatch. The UNIQUE constraint on (assignment_dispatch_id, user_id) enforces this at the database level. read-receipt-service must also guard against race conditions by performing an existence check before INSERT, or relying on INSERT … ON CONFLICT DO NOTHING semantics.
recipient_identity_match
The user_id written to a read receipt must equal auth.uid() (the authenticated Supabase session) and must match the recipient_user_id recorded on the parent assignment. RLS SELECT policy on assignment_dispatches coupled with a CHECK constraint or RLS INSERT policy prevents a peer mentor from writing a receipt on behalf of another user.
dispatch_must_be_delivered
A read receipt may only be created for a dispatch whose status is 'delivered' or 'acknowledged'. Creating a receipt against a dispatch in 'pending' or 'failed' state indicates a logic error; the application layer must verify delivery status before invoking recordReadReceipt.
coordinator_read_only_access
Coordinator and admin roles may SELECT read receipts scoped to assignments within their organizational hierarchy for dashboard and status tracking purposes. They must never be granted INSERT, UPDATE, or DELETE privileges on this table.
read_at_server_supplied
The read_at timestamp must be set by the database server (DEFAULT now()) and must never be accepted from client payloads. This prevents backdating or manipulation of the audit trail.
CRUD Operations
Storage Configuration
Entity Relationships
Each dispatch event may generate a read receipt when the recipient views the decrypted content