Achievement
Data Entity
Description
Records a specific badge or milestone award granted to a peer mentor user, linking them to an achievement type definition. Supports both automated threshold-based awards triggered by domain events and manually granted coordinator badges. Provides the data foundation for the Badges Screen, Annual Summary (Wrapped), and gamification features.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, uniquely identifies a single achievement award record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who earned this achievement | required |
achievement_type_id |
uuid |
Foreign key referencing the achievement type definition (badge name, description, SVG asset, threshold rules) | required |
organization_id |
uuid |
Foreign key to the organization providing tenant isolation. Populated from the user's organization at award time. Required for scoping custom org-specific badges. | required |
granted_at |
datetime |
Timestamp when the achievement was awarded. Used for display order on Badges Screen and Annual Summary (Wrapped) timeline. | required |
granted_by |
uuid |
User ID of the coordinator or org admin who manually granted this badge. Null for automated system-triggered awards. | - |
grant_source |
enum |
Indicates whether the award was issued automatically by the badge evaluation engine or manually by a coordinator | required |
trigger_event |
string |
The domain event type that triggered the automatic award evaluation. Null for manual grants. Examples: activity_saved, expense_submitted, certificate_issued, course_registered. | - |
trigger_entity_type |
string |
The entity type whose creation or update triggered the evaluation. Null for manual grants. Examples: activity, certification, course_registration. | - |
trigger_entity_id |
uuid |
The specific entity instance ID (e.g., activity ID or certification ID) whose event triggered the award. Null for manual grants. Provides audit traceability. | - |
threshold_value_at_grant |
integer |
The cumulative count (e.g., number of activities completed) that was reached at the time of the award. Provides a historical snapshot of the threshold crossing value, independent of future changes to achievement_type thresholds. | - |
is_visible |
boolean |
Controls whether the badge is shown on the user's Badges Screen. Allows soft-hiding without deletion, e.g., when an org admin revokes a manually granted badge without removing the audit record. | required |
notified_at |
datetime |
Timestamp when the push notification for this achievement was dispatched to the user. Null until the notification is sent. Used to prevent duplicate notifications on re-evaluation. | - |
metadata |
json |
Flexible JSON blob for storing additional context at award time, e.g., activity counts by type, coordinator notes for manual grants, or annual summary year reference. Schema varies by achievement type. | - |
created_at |
datetime |
Record creation timestamp, set by the database on insert | required |
updated_at |
datetime |
Record last-update timestamp, updated on every write | required |
Database Indexes
idx_achievement_user_id
Columns: user_id
idx_achievement_user_type
Columns: user_id, achievement_type_id
idx_achievement_org_id
Columns: organization_id
idx_achievement_granted_at
Columns: granted_at
idx_achievement_user_granted_at
Columns: user_id, granted_at
idx_achievement_notified_at_null
Columns: notified_at, is_visible
Validation Rules
user_id_exists
error
Validation failed
achievement_type_id_exists
error
Validation failed
organization_id_matches_user
error
Validation failed
grant_source_consistency
error
Validation failed
threshold_value_non_negative
error
Validation failed
granted_at_not_future
error
Validation failed
metadata_valid_json
error
Validation failed
Business Rules
no_duplicate_non_repeatable_badge
If the achievement type has is_repeatable=false, a user may only hold one active (is_visible=true) achievement record for that type. The badge evaluation job must query existing records before inserting and skip if one already exists. This prevents awarding the same milestone badge twice.
tenant_isolation
Achievement records must always carry organization_id derived from the user's organization at award time. Queries for badges on the Badges Screen and Annual Summary must include organization_id in the WHERE clause. Custom org-specific achievement types must not be visible to users in other organizations.
manual_grant_requires_authorized_role
When grant_source=manual, the granted_by user must hold the Coordinator or Organization Administrator role within the same organization as the recipient user. Achievement Service must validate the actor's role before persisting the record.
notification_dispatched_after_award
After a new achievement is created with is_visible=true, Achievement Service must trigger Push Notification Service to send the user a badge-awarded notification. The notified_at field is set to the dispatch timestamp. Notifications are only sent once per achievement record.
revocation_soft_hide_only
Achievements may not be hard-deleted unless the user account itself is being deleted. Coordinator revocations set is_visible=false to preserve the audit trail. Hard deletion is only permitted as a cascade from user deletion.
threshold_snapshot_immutable
Once an achievement record is created, threshold_value_at_grant must not be modified. It is a historical snapshot at the moment of award and must remain stable even if the achievement_type threshold configuration changes later.
annual_summary_inclusion
All achievements granted within a calendar year where is_visible=true must be included in the Annual Summary (Wrapped) computation for that year. Summary Aggregation Service queries achievements by user_id, organization_id, and granted_at year range.