Milestone
Data Entity
Description
Represents a significant achievement threshold reached by a peer mentor (10th session, 50th session, 100 hours volunteered). Milestones are detected dynamically by the annual stats aggregation and badge evaluation pipelines rather than stored as individual table rows. When detected, they appear in the annual Wrapped summary and may trigger badge awards.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the detected milestone record. Generated server-side on first detection. | PKrequiredunique |
peer_mentor_id |
uuid |
Foreign key referencing the peer mentor who reached this milestone. Maps to the peer_mentors table. | required |
organization_id |
uuid |
Foreign key referencing the organisation the peer mentor belongs to. Required for RLS data isolation and multi-tenant scoping. | required |
milestone_type |
enum |
Category of achievement tracked by this milestone. Determines which stat counter is compared against the threshold_value. | required |
threshold_value |
integer |
The numeric boundary that was crossed when this milestone was triggered. E.g., 10 for the 10th session, 50 for the 50th session, 100 for 100 hours volunteered. | required |
reached_at |
datetime |
UTC timestamp at which the milestone was first detected by the aggregation pipeline. Set once and never updated. | required |
period |
string |
The reporting or summary period in which this milestone was detected. Format: 'YYYY' for annual periods, 'YYYY-H1' or 'YYYY-H2' for half-year periods. Used to scope the milestone to the correct Wrapped summary. | required |
detected_stat_value |
integer |
The actual cumulative stat value at the moment of detection (e.g., 52 sessions when the 50-session threshold was crossed). Provides context for the Wrapped summary narrative. | required |
badge_awarded |
boolean |
Whether the badge evaluation pipeline has already processed this milestone and awarded a corresponding badge. Prevents duplicate badge awards across reruns. | required |
badge_id |
uuid |
Foreign key referencing the EarnedBadge record created as a result of this milestone, if any. Null when badge_awarded is false or when the milestone type does not map to a badge definition. | - |
displayed_in_wrapped |
boolean |
Whether this milestone has been surfaced in at least one Wrapped summary rendering. Used to determine the 'new milestone' animation in the MilestoneBadgeWidget. | required |
detection_run_id |
uuid |
Identifier of the aggregation pipeline run that produced this milestone. Enables idempotent re-runs: if a run with this ID already created a record for this (peer_mentor_id, milestone_type, threshold_value, period), no duplicate is inserted. | - |
created_at |
datetime |
UTC timestamp when the database row was inserted. Automatically set by Supabase default. May differ slightly from reached_at if ingestion is batched. | required |
Database Indexes
idx_milestone_peer_mentor
Columns: peer_mentor_id
idx_milestone_peer_mentor_type_threshold
Columns: peer_mentor_id, milestone_type, threshold_value, period
idx_milestone_org_period
Columns: organization_id, period
idx_milestone_reached_at
Columns: reached_at
idx_milestone_badge_awarded
Columns: badge_awarded
idx_milestone_detection_run
Columns: detection_run_id
Validation Rules
peer_mentor_id_references_valid_mentor
error
Validation failed
period_format_is_valid
error
Validation failed
reached_at_is_not_in_the_future
error
Validation failed
detected_stat_value_gte_threshold_value
error
Validation failed
threshold_value_is_positive_integer
error
Validation failed
badge_id_set_only_when_badge_awarded_true
error
Validation failed
milestone_type_is_valid_enum
error
Validation failed
Business Rules
unique_milestone_per_mentor_per_period
A peer mentor can only have one milestone record per (milestone_type, threshold_value, period) combination. Duplicate detection during re-runs must be prevented via the unique index and detection_run_id. Idempotent inserts must use ON CONFLICT DO NOTHING.
milestone_triggers_badge_evaluation
When a new milestone record is created, the badge evaluation pipeline must be invoked for the affected peer mentor and organisation. The badge_awarded flag is set to true and badge_id populated after the badge is persisted.
milestone_visible_in_wrapped_only_for_relevant_period
A milestone with period 'YYYY' should appear in the annual Wrapped summary for that year only. Half-year milestones ('YYYY-H1', 'YYYY-H2') appear in periodic summaries. The displayed_in_wrapped flag is set to true on first rendering to enable the 'new' animation.
reached_at_must_not_precede_first_activity
The reached_at timestamp must be on or after the date of the peer mentor's first registered activity. Detection pipelines must derive reached_at from the activity that caused the threshold to be crossed, not from the pipeline run time.
organisation_data_isolation
All milestone reads and writes must be scoped to the requesting user's organisation_id via Supabase RLS policies. Cross-organisation milestone queries are prohibited.
no_milestone_deletion
Milestone records are immutable achievement history and must never be deleted, even when a peer mentor is deactivated or paused. Deletion is not an allowed operation.
threshold_value_must_match_defined_thresholds
Only threshold values defined in the ScenarioEvaluationConfiguration are valid (e.g., 10, 50, 100 for session_count; 100, 250 for hours_volunteered). Ad-hoc thresholds must not be persisted.