User Achievement
Data Entity
Description
Records the association between a user and an achievement they have earned. Tracks when badges were awarded, how they were triggered, whether the user has been notified and acknowledged the award, and any contextual metadata captured at the moment of award (e.g. activity count, date range). Supports both system-triggered and manually granted awards, and allows soft revocation with a reason.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key for the award record. | PKrequiredunique |
user_id |
uuid |
FK → users.id. The peer mentor or coordinator who earned the achievement. | required |
achievement_id |
uuid |
FK → achievements.id. The badge/achievement that was awarded. | required |
awarded_at |
datetime |
Timestamp when the achievement was awarded. Defaults to now() on insert. | required |
award_trigger |
enum |
Mechanism that caused the award: automatic rule evaluation, manual grant by coordinator/admin, or import from legacy system. | required |
awarded_by_user_id |
uuid |
FK → users.id. Populated only when award_trigger is manual_coordinator or manual_admin. NULL for automatic awards. | - |
context_snapshot |
json |
Immutable snapshot of the criteria state at award time (e.g. {"activity_count": 50, "period": "2025", "activity_type": "home_visit"}). Used in the Annual Summary (Wrapped) and badge detail screens. | - |
notified_at |
datetime |
Timestamp when the push notification for this award was dispatched. NULL if notification has not yet been sent. | - |
acknowledged_at |
datetime |
Timestamp when the user opened the badge on the Badges Screen, acknowledging the award. Drives unread-badge indicators. | - |
is_revoked |
boolean |
Soft-delete flag. TRUE if the award has been revoked by an admin. Revoked records are retained for audit purposes. | required |
revoked_at |
datetime |
Timestamp of revocation. NULL when is_revoked = false. | - |
revoked_by_user_id |
uuid |
FK → users.id. Admin who performed the revocation. | - |
revocation_reason |
text |
Free-text explanation for revocation, stored for audit trail. | - |
display_order |
integer |
Optional integer allowing manual ordering of badges on the Badges Screen. NULL means default chronological order. | - |
Database Indexes
idx_user_achievements_user_id
Columns: user_id
idx_user_achievements_achievement_id
Columns: achievement_id
idx_user_achievements_user_achievement_active
Columns: user_id, achievement_id
idx_user_achievements_awarded_at
Columns: user_id, awarded_at
idx_user_achievements_unacknowledged
Columns: user_id, acknowledged_at
Validation Rules
user_id_exists
error
Validation failed
achievement_id_exists
error
Validation failed
awarded_at_not_future
error
Validation failed
revoked_at_after_awarded_at
error
Validation failed
manual_award_requires_granter
error
Validation failed
context_snapshot_valid_json
warning
Validation failed
Business Rules
no_duplicate_active_award
A user may not hold the same achievement twice simultaneously. The unique index on (user_id, achievement_id) prevents duplicate rows. If an achievement is designed to be re-earnable (e.g. annual badges), the existing record must be revoked before a new one is created, or the achievement must carry a separate year/cycle identifier.
notify_on_award
Every new award must trigger a push notification to the user via push-notification-service within the same transaction boundary (or enqueued for immediate delivery). notified_at is set once the notification job confirms dispatch.
revocation_requires_reason
Setting is_revoked = true requires revocation_reason to be non-null and revoked_by_user_id to reference a valid admin user. Partial revocations (missing reason or actor) must be rejected.
context_snapshot_immutable
context_snapshot must not be modified after the row is created. It captures the exact state of criteria at award time and serves as an audit record used in the Annual Summary (Wrapped) generation.
revoked_awards_excluded_from_display
All read queries for badges displayed to the user (Badges Screen, Annual Summary) must filter WHERE is_revoked = false. Revoked awards are accessible only through admin audit views.
org_scoped_achievement_award
Achievement criteria may be organisation-specific (e.g. a badge only available in HLF). badge-award-service must verify the achievement's owning organisation matches the user's active organisation context before creating the award record.