Achievements
Data Entity
Description
Records of digital badges and milestone achievements earned by peer mentors throughout their volunteering journey, tracking when badges were awarded, progress toward incomplete milestones, and the triggering events that unlocked each achievement.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the achievement record | PKrequiredunique |
user_id |
uuid |
Reference to the peer mentor or coordinator who earned this achievement | required |
achievement_type_id |
uuid |
Reference to the achievement type definition that was earned | required |
organization_id |
uuid |
Organization context in which the achievement was earned, ensuring tenant isolation | required |
status |
enum |
Current state of the achievement — in_progress while working toward it, earned once unlocked, revoked if withdrawn | required |
progress_current |
integer |
Current progress count toward the achievement threshold (e.g., 7 out of 10 activities) | required |
progress_target |
integer |
Target count required to earn the achievement, copied from achievement_type at creation for snapshot consistency | required |
earned_at |
datetime |
Timestamp when the achievement was earned, null while in_progress | - |
trigger_event_type |
string |
The type of event that triggered the achievement award (e.g., activity_completed, assignment_completed, course_completed) | - |
trigger_event_id |
uuid |
Reference to the specific entity instance that triggered the final award (e.g., the activity ID that pushed count over threshold) | - |
notified |
boolean |
Whether the user has been notified about earning this achievement via push notification | required |
seen_at |
datetime |
Timestamp when the user first viewed the earned badge in the badges screen, null if not yet seen | - |
revoked_at |
datetime |
Timestamp when the achievement was revoked, null if not revoked | - |
revocation_reason |
text |
Explanation for why the achievement was revoked, required when status is revoked | - |
metadata |
json |
Additional context about the achievement such as snapshot statistics at time of earning (total hours, contact count), used for display in badges screen | - |
created_at |
datetime |
Timestamp when the achievement tracking record was first created | required |
updated_at |
datetime |
Timestamp of the last update to this record (progress increment or status change) | required |
Database Indexes
idx_achievements_user_id
Columns: user_id
idx_achievements_achievement_type_id
Columns: achievement_type_id
idx_achievements_user_type_unique
Columns: user_id, achievement_type_id
idx_achievements_organization_id
Columns: organization_id
idx_achievements_status
Columns: status
idx_achievements_earned_at
Columns: earned_at
idx_achievements_user_status
Columns: user_id, status
idx_achievements_org_earned
Columns: organization_id, earned_at
Validation Rules
valid_user_reference
error
Validation failed
valid_achievement_type_reference
error
Validation failed
valid_organization_reference
error
Validation failed
progress_within_bounds
error
Validation failed
earned_at_required_when_earned
error
Validation failed
revoked_at_required_when_revoked
error
Validation failed
trigger_event_type_format
warning
Validation failed
metadata_valid_json
error
Validation failed
unique_user_type_combination
error
Validation failed
Business Rules
one_achievement_per_type_per_user
Each user can have at most one achievement record per achievement_type_id. Progress is tracked on the single record rather than creating duplicates.
progress_triggers_earned_status
When progress_current reaches or exceeds progress_target, the status must transition to 'earned' and earned_at must be set to the current timestamp.
earned_achievements_are_immutable
Once an achievement has status 'earned', its progress_current, progress_target, earned_at, and trigger fields cannot be modified. Only status can change to 'revoked'.
revocation_requires_reason
Setting status to 'revoked' requires a non-empty revocation_reason and sets revoked_at to the current timestamp.
tenant_isolation
All queries must be scoped by organization_id. Users can only see achievements within their own organization context.
push_notification_on_earned
When an achievement transitions to 'earned', a push notification must be dispatched to the user's devices and the notified flag set to true.
progress_monotonic_increase
progress_current can only increase (or stay the same) while status is 'in_progress'. It cannot decrease except through revocation.
user_must_be_active
Achievements can only be created or progressed for users with an active account status. Deactivated users do not accumulate progress.