Activity Approvals
Data Entity
Description
Tracks the approval lifecycle of activity registrations submitted by peer mentors. Each approval record links one-to-one with an activity and captures the reviewer's decision (approve/reject), review timestamp, notes, and optimistic locking version to prevent concurrent modification conflicts.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the approval record | PKrequiredunique |
activity_id |
uuid |
Foreign key referencing the activity being reviewed | requiredunique |
status |
enum |
Current approval status of the activity | required |
reviewer_id |
uuid |
Foreign key referencing the user (coordinator or org admin) who reviewed the activity. Null while pending. | - |
reviewed_at |
datetime |
Timestamp when the approval decision was made. Null while pending. | - |
reviewer_notes |
text |
Optional notes from the reviewer explaining the decision, particularly required for rejections | - |
rejection_reason |
text |
Structured reason for rejection, required when status is rejected | - |
organization_id |
uuid |
Foreign key to the organization for tenant isolation and scoped queries | required |
local_association_id |
uuid |
Foreign key to the local association the activity belongs to, used for coordinator-scoped queries | - |
auto_approval_rule_id |
uuid |
Reference to the auto-approval rule that triggered automatic approval, null for manual decisions | - |
version |
integer |
Optimistic locking version counter to prevent concurrent modification conflicts during approval | required |
submitted_at |
datetime |
Timestamp when the activity was submitted for review | required |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Record last update timestamp | required |
Database Indexes
idx_activity_approvals_activity_id
Columns: activity_id
idx_activity_approvals_status_org
Columns: organization_id, status
idx_activity_approvals_reviewer
Columns: reviewer_id, reviewed_at
idx_activity_approvals_association_status
Columns: local_association_id, status
idx_activity_approvals_submitted_at
Columns: organization_id, submitted_at
Validation Rules
activity_exists
error
Validation failed
reviewer_exists_on_decision
error
Validation failed
reviewed_at_set_on_decision
error
Validation failed
version_positive_integer
error
Validation failed
status_enum_valid
error
Validation failed
reviewer_notes_length
error
Validation failed
no_self_approval
error
Validation failed
Business Rules
one_to_one_activity_approval
Each activity can have exactly one approval record. The activity_id column has a unique constraint enforcing this relationship.
reviewer_must_have_oversight_role
Only users with Coordinator or Organization Admin role can approve or reject activities. Peer Mentors cannot review their own or others' activities.
reviewer_scoped_to_local_association
Coordinators can only review activities within their own local association scope. Org Admins can review across the entire organization.
rejection_requires_reason
When status is set to rejected, the rejection_reason field must be populated with a non-empty explanation.
optimistic_locking_on_decision
The version field must match the current database value when updating status. If another reviewer has already acted, the update is rejected to prevent concurrent modification.
status_transition_constraints
Valid transitions: pending_review → approved, pending_review → rejected, pending_review → flagged, pending_review → auto_approved, flagged → approved, flagged → rejected. No transitions from approved or rejected back to pending.
auto_approval_sets_rule_reference
When an activity is auto-approved by a threshold rule, the auto_approval_rule_id must reference the specific rule that triggered the automatic approval and status must be auto_approved.
audit_trail_on_decision
Every approval or rejection action must generate an audit log entry recording the actor, action, activity ID, and decision details.
tenant_isolation
All queries against activity_approvals must include organization_id in the WHERE clause to enforce multi-tenant data isolation.
approved_activity_counts_for_bufdir
Only activities with status approved or auto_approved are included in Bufdir report aggregation and KPI calculations.