User Achievement
Data Entity
Description
Records the award of a badge to a specific user, capturing when and why the achievement was earned, notification state, and user interaction with the award. Acts as the join table between users and badges with rich audit metadata.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, generated server-side | PKrequiredunique |
user_id |
uuid |
FK to users. The peer mentor or coordinator who earned the achievement. | required |
badge_id |
uuid |
FK to badges. The badge type that was awarded. | required |
organization_id |
uuid |
FK to organizations. Scopes the achievement to the organization context in which it was earned. Supports multi-tenant badge queries. | required |
awarded_at |
datetime |
Timestamp when the badge was awarded. Set server-side on creation. | required |
awarded_by |
enum |
What mechanism triggered the award. Differentiates automated system awards from manual coordinator or admin grants. | required |
trigger_event |
string |
Slug identifying the event that caused the award, e.g. 'activity_count_10', 'annual_summary_2024', 'first_login'. Used for deduplication and audit. | required |
context_data |
json |
Snapshot of the metric values at award time, e.g. { activity_count: 10, period: '2024' }. Immutable after creation. | - |
is_featured |
boolean |
User-controlled flag. When true, badge appears prominently on the user's profile and share card. | required |
notification_sent |
boolean |
Whether the push notification for this achievement has been dispatched. Set to true by the notification pipeline after dispatch. | required |
seen_at |
datetime |
Nullable. Set the first time the user opens the Badges Gallery or the award detail screen. Used to show 'new' indicators. | - |
created_at |
datetime |
Row creation timestamp, managed by the database. | required |
updated_at |
datetime |
Last-modified timestamp. Updated on is_featured or seen_at changes. | required |
Database Indexes
idx_user_achievements_user_id
Columns: user_id
idx_user_achievements_badge_id
Columns: badge_id
idx_user_achievements_user_badge
Columns: user_id, badge_id
idx_user_achievements_user_org
Columns: user_id, organization_id
idx_user_achievements_awarded_at
Columns: awarded_at
idx_user_achievements_notification_sent
Columns: notification_sent
Validation Rules
user_id_exists
error
Validation failed
badge_id_exists
error
Validation failed
awarded_at_not_future
error
Validation failed
trigger_event_format
error
Validation failed
seen_at_after_awarded_at
error
Validation failed
context_data_valid_json
error
Validation failed
Business Rules
one_badge_per_user
A user may not receive the same badge more than once. The unique index on (user_id, badge_id) enforces this at the database level; badge-award-service checks for an existing record before insert and silently skips if already awarded.
badge_must_be_active
A badge can only be awarded if its record in the badges table is active. Awarding a retired or soft-deleted badge is rejected.
user_must_be_active
Achievements are only awarded to active (non-deactivated) users. Deactivated accounts do not accumulate new badges.
notify_on_award
Whenever a new user_achievement row is created, a push notification must be dispatched. notification_sent is flipped to true after confirmed dispatch. If push delivery fails, the record persists and the notification pipeline retries.
context_data_immutable
context_data is a point-in-time snapshot of the metric that triggered the award. It must not be modified after creation. Updates to other fields (is_featured, seen_at, notification_sent) must not touch context_data.
org_scoped_award
The organization_id on a user_achievement must match the user's current active organization membership at award time. This ensures badges are scoped to the correct tenant context and prevents cross-org data leakage.
annual_badge_yearly_uniqueness
For badges with category 'annual_summary', the trigger_event must include the year (e.g. 'annual_summary_2024'). badge-award-service verifies no existing record for that user + badge + year-scoped trigger_event before creating.