User Achievement
Data Entity
Description
Junction entity recording which achievements a specific user has earned, including when the badge was awarded and any associated metadata such as progress toward the achievement threshold.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who earned the achievement | required |
achievement_id |
uuid |
Foreign key referencing the achievement definition | required |
earned_at |
datetime |
Timestamp when the achievement was awarded | required |
progress_value |
integer |
Current progress count toward the achievement threshold (e.g., number of activities completed). Stored even after earning to support display in Annual Summary. | - |
notified_at |
datetime |
Timestamp when the push notification for this achievement was sent. Null if not yet notified. | - |
is_featured |
boolean |
Whether the user has pinned this badge to their profile for prominent display | required |
source_context |
json |
Optional snapshot of the triggering event context (e.g., activity_id, period, count) for audit and Annual Summary generation | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Record last-updated timestamp | required |
Database Indexes
idx_user_achievements_user_id
Columns: user_id
idx_user_achievements_achievement_id
Columns: achievement_id
idx_user_achievements_user_achievement
Columns: user_id, achievement_id
idx_user_achievements_earned_at
Columns: user_id, earned_at
idx_user_achievements_unnotified
Columns: notified_at
Validation Rules
valid_user_ref
error
Validation failed
valid_achievement_ref
error
Validation failed
earned_at_not_future
error
Validation failed
progress_value_non_negative
error
Validation failed
Business Rules
no_duplicate_award
A user may earn each achievement at most once. The unique index on (user_id, achievement_id) enforces this at the database level; AchievementService must check before inserting.
notify_on_award
When a new user_achievement row is created, a push notification must be dispatched via PushNotificationService and notified_at set on success.
progress_non_regressive
progress_value may only increase or remain the same; it must never decrease once set.
org_module_gate
Achievement awarding is only active when the achievements-gamification module is enabled for the user's organization. AchievementService checks the module registry before evaluating thresholds.
annual_summary_inclusion
All user_achievements rows with earned_at within the calendar year are included in the AnnualSummary generation. AnnualSummaryService reads them directly.