Bulk Registration
Data Entity
Description
Records a coordinator's batch activity registration on behalf of multiple peer mentors, capturing the shared activity template and the resulting individual activity records. Supports coordinators managing peer mentors who cannot or choose not to use the app directly.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — unique identifier for the bulk registration batch | PKrequiredunique |
coordinator_id |
uuid |
Foreign key referencing users.id — the coordinator who initiated the bulk registration | required |
organization_id |
uuid |
Foreign key referencing organizations.id — the organizational scope under which this batch was created, used to enforce RLS and Bufdir reporting attribution | required |
activity_template |
json |
Shared activity data applied to every peer mentor in the batch. Contains: activity_type_id (uuid), activity_date (date), start_time (time), duration_minutes (integer), summary (text), contact_id (uuid nullable), location (text nullable). This is the canonical template from which individual activities are derived. | required |
mentor_ids |
json |
JSON array of user UUIDs representing the peer mentors selected for this batch. Snapshot captured at submission time to ensure audit integrity even if mentor membership later changes. | required |
mentor_count |
integer |
Denormalized count of peer mentors selected at submission time. Equals array length of mentor_ids. Used for quick aggregation in reports without parsing JSON. | required |
status |
enum |
Lifecycle state of the bulk registration batch. pending = submitted but not yet processed; processing = Supabase RPC in flight; completed = all activities created successfully; partially_failed = some activities failed to create; failed = no activities were created due to a fatal error. | required |
success_count |
integer |
Number of individual activity records successfully created from this batch. Updated after RPC completes. Used to compute completion rate and displayed in batch progress UI. | - |
failure_count |
integer |
Number of individual activity creation attempts that failed within this batch. Updated after RPC completes. | - |
failure_details |
json |
Per-mentor failure information keyed by mentor user_id. Each entry contains: mentor_id (uuid), error_code (string), error_message (string). Null when no failures occurred. Used to render actionable error rows in Batch Submission Progress Widget. | - |
notes |
text |
Optional free-text coordinator annotation for this batch, e.g. 'Weekly Wednesday exercise group'. Not propagated to individual activity summaries. | - |
activity_date |
datetime |
Denormalized activity date extracted from activity_template.activity_date. Enables efficient date-range queries and Bufdir period filtering without JSON parsing. | required |
submitted_at |
datetime |
Timestamp when the coordinator confirmed and submitted the batch. Distinct from created_at to allow draft state in future iterations. | - |
completed_at |
datetime |
Timestamp when batch processing finished (either completed or partially_failed). Null while status is pending or processing. | - |
created_at |
datetime |
Record creation timestamp, set automatically by Supabase. Immutable after insert. | required |
updated_at |
datetime |
Last update timestamp, maintained by Supabase trigger on every row mutation. Used for cache invalidation and audit. | required |
Database Indexes
idx_bulk_registrations_coordinator_id
Columns: coordinator_id
idx_bulk_registrations_organization_id
Columns: organization_id
idx_bulk_registrations_coordinator_org
Columns: coordinator_id, organization_id
idx_bulk_registrations_status
Columns: status
idx_bulk_registrations_activity_date
Columns: activity_date
idx_bulk_registrations_org_activity_date
Columns: organization_id, activity_date
idx_bulk_registrations_created_at
Columns: created_at
Validation Rules
activity_date_not_excessively_future
error
Validation failed
duration_minutes_positive
error
Validation failed
activity_type_id_exists
error
Validation failed
mentor_ids_array_valid_uuids
error
Validation failed
mentor_ids_max_batch_size
error
Validation failed
summary_field_length
error
Validation failed
notes_field_length
error
Validation failed
organization_id_matches_coordinator_membership
error
Validation failed
no_duplicate_mentor_ids_in_batch
error
Validation failed
Business Rules
coordinator_role_required
Only users with coordinator or admin roles may create bulk registrations. Peer mentor role is explicitly denied. Enforced via RLS policy on the bulk_registrations table tied to auth.uid() role claim, and by Activity Delegation Service which verifies role before allowing proxy insert.
mentor_scope_enforcement
All peer mentors in mentor_ids must belong to the coordinator's organizational scope (same organization_id or a subordinate local association). A coordinator cannot register activities for peer mentors outside their scope even if they know the user IDs. Validated by Activity Delegation Service via checkDelegationAuthorization() before batch RPC is invoked.
minimum_one_mentor_selected
A bulk registration batch must include at least one peer mentor. Empty mentor_ids arrays are rejected at service layer before any database write occurs.
activity_template_completeness
The activity_template JSON must contain all mandatory fields: activity_type_id, activity_date, duration_minutes, and summary. Optional fields (contact_id, location) may be omitted. Incomplete templates are rejected before batch submission.
delegation_grant_creation
For every activity successfully created from a bulk batch, a corresponding delegation_grants record must be created linking coordinator_id, mentor user_id, and the resulting activity_id. This is an audit requirement — proxy registrations must always be traceable to the authorizing coordinator.
atomic_batch_transaction
The Supabase batch insert RPC that creates individual activities from the template must execute within a single database transaction. If the transaction fails entirely, bulk_registrations.status is set to 'failed'. Partial failures within the RPC update status to 'partially_failed' and populate failure_details.
status_forward_only_transitions
The status field can only advance forward: pending → processing → completed | partially_failed | failed. Reverting a completed or failed batch is not permitted. Re-submission requires creating a new bulk_registrations record.
duplicate_activity_check_before_batch
Before submitting the batch RPC, the Bulk Registration Service invokes the Duplicate Detection Service to check each mentor's candidate activity against existing records. Matches above the confidence threshold are surfaced to the coordinator via the Duplicate Warning Dialog before final submission is allowed.
mentor_count_denormalization
The mentor_count column must always equal the length of the mentor_ids JSON array at insert time. This constraint is enforced at the service layer and verified by a Supabase CHECK constraint to prevent inconsistency.
activity_date_denormalization
The activity_date column must be populated from activity_template.activity_date at insert time to enable efficient date-range index scans. Mismatch between the two is rejected by a CHECK constraint.
CRUD Operations
Storage Configuration
Entity Relationships
A bulk registration spawns individual activity records for each selected peer mentor
A coordinator creates bulk registrations on behalf of multiple peer mentors