User Badge
Data Entity
Description
Records the award of a specific achievement badge to a user, including timestamp and organizational context. Enables the achievements screen to display earned and locked badges with progress tracking toward the next tier.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key for the award record, generated server-side on insert. | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the users table. Identifies the peer mentor, coordinator, or admin who earned the badge. | required |
badge_id |
uuid |
Foreign key referencing the achievement_badges table. Identifies which badge definition was awarded. | required |
awarded_at |
datetime |
UTC timestamp of when the badge was awarded. Set server-side at insert time; immutable after creation. | required |
organization_id |
uuid |
Foreign key to the organizations table capturing the organizational context in which the qualifying milestone was reached. Required for multi-org users to scope badge display and avoid cross-org reporting pollution. | required |
is_visible |
boolean |
Controls whether this badge award is displayed on the user's achievements screen. Defaults to true. Users may toggle visibility for personal reasons. | required |
awarded_by |
enum |
Indicates what triggered the award: automatic system evaluation, a coordinator action, or an admin override. | required |
notification_sent |
boolean |
Tracks whether the badge award push notification has been dispatched to the user. Set to false on insert and updated to true once the push notification service confirms dispatch. Prevents duplicate notifications on retry. | required |
progress_snapshot |
json |
Optional JSON snapshot of the qualifying metric values at the time of award (e.g., {"assignment_count": 15, "activity_hours": 47.5}). Enables the achievements screen to show contextual award descriptions without re-querying live statistics. | - |
tier_level |
integer |
Integer tier at which this specific award was granted (e.g., 1 = bronze, 2 = silver, 3 = gold). Null for non-tiered badges. Allows the achievements screen to render the correct badge illustration variant and progress ring toward the next tier. | - |
created_at |
datetime |
Row creation timestamp, set by the database on insert. Used for audit purposes and differs from awarded_at only when records are migrated from legacy systems. | required |
Database Indexes
idx_user_badges_user_id
Columns: user_id
idx_user_badges_badge_id
Columns: badge_id
idx_user_badges_user_badge_org
Columns: user_id, badge_id, organization_id
idx_user_badges_org_id
Columns: organization_id
idx_user_badges_awarded_at
Columns: awarded_at
idx_user_badges_notification_sent
Columns: notification_sent
Validation Rules
user_id_must_exist
error
Validation failed
badge_id_must_exist
error
Validation failed
organization_id_must_exist
error
Validation failed
awarded_at_not_future
error
Validation failed
tier_level_positive_integer
error
Validation failed
progress_snapshot_valid_json
error
Validation failed
awarded_by_valid_enum
error
Validation failed
Business Rules
unique_badge_per_user_per_org
A given badge may only be awarded once per user per organization. The unique index on (user_id, badge_id, organization_id) enforces this at the database level. For tiered badges, each tier increment creates a new row only if tier_level is strictly greater than any existing award for the same (user_id, badge_id, organization_id) triple.
org_scope_matches_badge_definition
The organization_id on the award record must match a valid organization scope for the referenced achievement_badges record. A badge defined for organization A must not be awarded in the context of organization B.
notify_on_award
When a new user_badges record is created, a push notification must be dispatched to the user informing them of the badge award. The notification_sent flag is set to true only after the push notification service confirms dispatch. If dispatch fails, the award record is retained and a retry is attempted.
award_only_to_active_users
Badges must not be awarded to deactivated or suspended user accounts. The achievement service must verify user active status before inserting an award record.
tier_level_monotonically_increasing
For tiered badges, a new award for the same (user_id, badge_id, organization_id) triple is only valid if tier_level is strictly greater than any existing tier_level for that combination. Downgrading tiers is not permitted.
visibility_toggle_restricted_to_owner
Only the owning user (matched by user_id) or an admin may toggle the is_visible flag. Coordinators may read but not modify visibility of another user's badge awards.
CRUD Operations
Storage Configuration
Entity Relationships
An achievement badge definition may be awarded to many users who meet its quantifiable criteria
Each user badge award is associated with the specific user who earned it