User Badge
Data Entity
Description
A join record representing an awarded badge for a specific user, including the timestamp of earning, optional metadata about the earning context (e.g., which activity triggered it), and any coordinator-nomination details. Supports the gamification and recognition system.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key, uniquely identifies this award record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who earned the badge | required |
badge_id |
uuid |
Foreign key referencing the badge definition that was awarded | required |
earned_at |
datetime |
UTC timestamp of when the badge was awarded, either automatically by the system or manually by a coordinator | required |
award_type |
enum |
Indicates whether the badge was awarded automatically by rule evaluation or via coordinator nomination | required |
earned_context |
json |
Optional structured metadata about the trigger event. For automatic awards: activity_id, milestone threshold, streak count, or period boundary. For nominations: reason text and any supporting notes. | - |
nominated_by |
uuid |
Foreign key to the user (coordinator) who submitted the nomination. Null for automatically awarded badges. | - |
nomination_reason |
text |
Human-readable reason text entered by the nominating coordinator. Only populated for coordinator_nominated award_type. | - |
period_label |
string |
Optional label identifying the reporting period associated with this award (e.g., '2025-H1', '2025'). Used for half-year and annual summary badges. | - |
is_visible |
boolean |
Controls whether this badge award is displayed in the peer mentor's badge screen. Allows hiding awards without deleting them. | required |
notified_at |
datetime |
UTC timestamp of when the push notification for this badge award was sent. Null if not yet notified. | - |
created_at |
datetime |
UTC timestamp of record creation | required |
updated_at |
datetime |
UTC timestamp of last record update | required |
Database Indexes
idx_user_badge_user_id
Columns: user_id
idx_user_badge_badge_id
Columns: badge_id
idx_user_badge_user_badge_unique
Columns: user_id, badge_id, period_label
idx_user_badge_earned_at
Columns: earned_at
idx_user_badge_nominated_by
Columns: nominated_by
idx_user_badge_award_type
Columns: award_type
idx_user_badge_notified_at
Columns: notified_at
Validation Rules
valid_user_id
error
Validation failed
valid_badge_id
error
Validation failed
valid_nominated_by_reference
error
Validation failed
earned_at_not_future
error
Validation failed
valid_award_type_enum
error
Validation failed
period_label_format
error
Validation failed
nomination_reason_max_length
error
Validation failed
earned_context_valid_json
error
Validation failed
Business Rules
no_duplicate_badge_per_period
A user cannot be awarded the same badge more than once within the same period_label. For non-periodic badges (period_label IS NULL), a user can only hold one award of each badge. Enforced by the unique index on (user_id, badge_id, period_label).
nomination_requires_coordinator_role
Only users with Coordinator or higher role may submit coordinator-nominated badge awards. The nominated_by field must reference a user with a valid coordinator or admin role in the same local association as the recipient.
nomination_reason_required_for_nominated_type
When award_type is 'coordinator_nominated', nomination_reason must be provided and non-empty. When award_type is 'automatic', nominated_by and nomination_reason must both be null.
badge_must_be_active
A badge award can only be created for an active badge definition. Archived or disabled badge definitions must not receive new awards.
push_notification_on_award
Every new badge award triggers a push notification to the recipient peer mentor. The notified_at field is set after successful dispatch. Notification delivery failure must not block badge record creation.
half_year_period_boundary
Half-year summary badges must be evaluated and awarded exactly at June 30 and December 31 cutoffs. The period_label must follow the format '{YEAR}-H1' or '{YEAR}-H2'. The badge evaluation scheduler is responsible for triggering evaluation at these boundaries.
cascade_delete_on_user_deactivation
When a user is permanently deactivated and deleted from the system, all their user_badge records are cascade-deleted. Soft deactivation (status change) must not delete badge records.