User Achievement
Data Entity
Description
Records earned achievement badges for individual users, linking the achievements catalog to specific users with earned timestamps and optional metadata about how the achievement was unlocked.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key | PKrequiredunique |
user_id |
uuid |
Foreign key to users table — the peer mentor who earned this achievement | required |
achievement_id |
uuid |
Foreign key to achievements catalog — which badge was earned | required |
earned_at |
datetime |
UTC timestamp when the achievement was awarded | required |
trigger_value |
integer |
The numeric value that triggered the award (e.g. activity count, streak length). Null for manual awards. | - |
trigger_context |
json |
Optional structured context about the trigger event (e.g. {period: '2024', activity_ids: [...]}). Used for annual-summary and threshold achievements. | - |
awarded_by_user_id |
uuid |
Foreign key to users — coordinator or admin who manually awarded this badge. Null for system-awarded achievements. | - |
is_visible |
boolean |
Whether this award is displayed on the user's badge wall. Users can hide individual awards. | required |
notified_at |
datetime |
UTC timestamp when the push notification for this award was sent. Null if not yet notified. | - |
organization_id |
uuid |
The organization context in which the achievement was earned. Enables org-scoped badge queries for coordinators. | required |
created_at |
datetime |
Row creation timestamp | required |
Database Indexes
idx_user_achievements_user_achievement
Columns: user_id, achievement_id
idx_user_achievements_user_id
Columns: user_id
idx_user_achievements_achievement_id
Columns: achievement_id
idx_user_achievements_org_earned
Columns: organization_id, earned_at
idx_user_achievements_notified_at
Columns: notified_at
Validation Rules
user_id_exists
error
Validation failed
achievement_id_exists
error
Validation failed
earned_at_not_future
error
Validation failed
trigger_value_non_negative
error
Validation failed
trigger_context_valid_json
error
Validation failed
Business Rules
no_duplicate_award
A user can earn each achievement at most once. The composite unique index on (user_id, achievement_id) enforces this at the database level. achievement-service must check before inserting.
inactive_achievement_no_new_award
Achievements with is_active=false in the achievements catalog must not be newly awarded. achievement-service checks is_active before creating a record. Existing records for inactive achievements remain visible.
manual_award_requires_coordinator
If awarded_by_user_id is set, the awarding user must hold the Coordinator or Org Admin role within the same organization. achievement-service validates role via role-guard-service.
notification_on_award
Immediately after a new row is created, achievement-service publishes an event so notification-channel-service sends a push notification. notified_at is set once the notification is dispatched.
organization_scope_match
The organization_id on the award must match the user's active organization context. Cross-org awards are not permitted.