Peer Mentor Stats
Data Entity
Description
Aggregated statistical snapshot of a peer mentor's performance used for badge evaluation, gamification scoring, and coordinator dashboard display. Derived from activity records and includes current streak length, total session count, total hours, and training completion count. Recomputed on each relevant activity event via a database trigger or edge function.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key for the stats record | PKrequiredunique |
peer_mentor_id |
uuid |
Foreign key referencing the peer_mentor entity. Each peer mentor has exactly one stats snapshot record. | requiredunique |
organization_id |
uuid |
Foreign key referencing the organization this stats snapshot belongs to. Scopes the stats to a specific tenant. | required |
total_sessions |
integer |
Total number of completed activity sessions registered by this peer mentor across all time. Incremented on each approved activity insert. | required |
total_hours |
decimal |
Total volunteered hours derived by summing activity durations (in hours, 2 decimal places). Recomputed from activity records on each update. | required |
current_streak_days |
integer |
Current consecutive-day streak of activity registration. A streak day is counted when at least one activity is registered on a calendar day. Resets to 0 if the mentor goes more than 1 calendar day without any activity. | required |
longest_streak_days |
integer |
Historical peak streak in days. Updated when current_streak_days exceeds this value. Never decreases. | required |
training_completions |
integer |
Count of training or certification completion events logged for this peer mentor. Used as a badge criterion (e.g., first certification badge). Incremented on each certification renewal or course completion. | required |
total_unique_contacts |
integer |
Count of unique contact individuals this peer mentor has interacted with across all registered activities. Used in the annual Wrapped summary and gamification scoring. | required |
current_year_sessions |
integer |
Total sessions logged in the current calendar year. Reset to 0 at the start of each year by a scheduled job. Supports annual Wrapped summary and year-based badge criteria. | required |
current_year_hours |
decimal |
Total hours volunteered in the current calendar year. Reset to 0 at the start of each year. Supports Wrapped summary and year-scoped gamification thresholds. | required |
last_activity_date |
datetime |
Timestamp of the most recently registered activity. Used to evaluate streak continuity — if this is more than 1 day ago, current_streak_days resets on next recompute. | - |
last_computed_at |
datetime |
UTC timestamp of the most recent recompute triggered by an activity event or edge function invocation. Used for cache invalidation and monitoring staleness. | required |
computed_version |
integer |
Monotonically increasing version counter incremented on each recompute. Enables optimistic concurrency detection to prevent stale overwrites from concurrent edge function invocations. | required |
Database Indexes
idx_peer_mentor_stats_peer_mentor_org
Columns: peer_mentor_id, organization_id
idx_peer_mentor_stats_organization_id
Columns: organization_id
idx_peer_mentor_stats_last_computed_at
Columns: last_computed_at
idx_peer_mentor_stats_current_streak_days
Columns: organization_id, current_streak_days
idx_peer_mentor_stats_total_sessions
Columns: organization_id, total_sessions
Validation Rules
non_negative_counts
error
Validation failed
non_negative_counts_on_update
error
Validation failed
last_computed_at_not_future
error
Validation failed
last_computed_at_not_future_on_update
error
Validation failed
current_streak_lte_longest_streak
error
Validation failed
current_streak_lte_longest_streak_on_update
error
Validation failed
peer_mentor_id_references_valid_mentor
error
Validation failed
organization_id_matches_peer_mentor
error
Validation failed
current_year_fields_lte_totals
warning
Validation failed
Business Rules
one_record_per_peer_mentor
Each peer mentor may have exactly one stats snapshot record per organization. The combination of peer_mentor_id and organization_id is unique. A UPSERT pattern is used on recompute — if a record does not exist it is created; if it exists it is updated in place.
recompute_on_activity_event
The stats record must be recomputed whenever an activity is inserted, updated, or deleted for the associated peer mentor. The recompute is triggered by a Supabase database trigger or edge function invocation. All aggregate fields must be derived from the live activity table — no manual edits are permitted.
streak_reset_on_inactivity
If the difference between the current date and last_activity_date exceeds 1 calendar day at the time of recompute, current_streak_days must be reset to 0. A streak day is defined as a calendar day on which at least one activity was registered.
longest_streak_never_decreases
longest_streak_days may only be updated when the new current_streak_days value exceeds the existing longest_streak_days. It must never be set to a lower value than its current stored value.
annual_fields_year_scoped
current_year_sessions and current_year_hours are scoped to the current calendar year. A scheduled nightly job resets these fields to 0 on the first day of each new year (January 1). The all-time totals (total_sessions, total_hours) are never reset.
derived_fields_not_directly_editable
All aggregate fields (total_sessions, total_hours, current_streak_days, etc.) are computed values derived from the activities table. No UI flow or service may directly write these values other than the designated peer-mentor-stats-aggregator component. Direct writes outside of the aggregator are rejected.
optimistic_concurrency_on_recompute
Before writing a recomputed snapshot, the computed_version of the in-memory result is compared against the stored version. If the stored version is higher (a concurrent recompute already wrote a newer result), the write is discarded to prevent stale overwrites.
badge_evaluation_triggered_after_recompute
After each successful stats recompute, the badge-evaluation-service must be invoked with the updated PeerMentorStats snapshot to detect newly earned badges. Badge evaluation must not run against a stale snapshot.
CRUD Operations
Storage Configuration
Entity Relationships
A peer mentor has one stats snapshot record that is recomputed on each activity event for gamification and dashboard display