Impact Summary
Data Entity
Description
Computed annual or half-year impact summary for a peer mentor, capturing aggregated metrics such as total hours contributed, unique contacts helped, and milestone achievements. Presented as a Spotify Wrapped-style narrative to recognise and motivate volunteers.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — auto-generated UUID for the impact summary record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing users.id — the peer mentor whose impact is summarised | required |
period_type |
enum |
Indicates whether this is an annual or half-year summary period | required |
year |
integer |
Calendar year this summary covers (e.g., 2025). For half-year summaries this combined with half_index identifies the exact period. | required |
half_index |
integer |
Half-year index: 1 = January–June, 2 = July–December. NULL when period_type is 'annual'. | - |
total_hours |
decimal |
Total volunteer hours contributed by the peer mentor during the period, aggregated from activity_logs.duration_minutes | required |
contacts_helped |
integer |
Count of unique contacts assisted during the period, derived from distinct contact_id values across activity_logs | required |
activities_count |
integer |
Total number of completed activities registered during the period | required |
assignments_completed |
integer |
Total number of formal assignments completed during the period, sourced from assignment_honorarium_records | required |
milestones |
json |
Array of milestone objects achieved during the period. Each milestone has: { milestone_key: string, label: string, achieved_at: ISO-timestamp, threshold_value: number }. Examples: first_activity, 100_hours, 50_contacts. | - |
metrics_json |
json |
Extended metrics payload for the animated story slides. Structured as: { activity_type_breakdown: {type_id: count}, top_activity_type: string, longest_streak_days: number, most_active_month: number, avg_session_duration_minutes: number, new_contacts_vs_returning: {new: number, returning: number} } | - |
headline |
string |
AI-generated or template-based personalised headline for the impact story, e.g. 'You gave 120 hours of your time this year'. Max 140 characters. | - |
status |
enum |
Processing state of the summary record. 'generating' while the calculation job runs; 'ready' once complete; 'error' if the job failed. | required |
error_message |
text |
Diagnostic message when status is 'error'. NULL otherwise. | - |
notification_sent |
boolean |
Whether the push notification prompting the peer mentor to view this summary has been dispatched | required |
share_count |
integer |
Number of times the peer mentor has shared this summary via the platform share sheet | required |
generated_at |
datetime |
UTC timestamp when the calculation job successfully completed and produced this record | required |
created_at |
datetime |
UTC timestamp when this record row was first inserted (may precede generated_at during async jobs) | required |
updated_at |
datetime |
UTC timestamp of the last modification to this record (updated by trigger on any column change) | required |
Database Indexes
idx_impact_summaries_user_id
Columns: user_id
idx_impact_summaries_unique_period
Columns: user_id, period_type, year, half_index
idx_impact_summaries_status
Columns: status
idx_impact_summaries_generated_at
Columns: generated_at
idx_impact_summaries_notification
Columns: notification_sent, status
Validation Rules
non_negative_metrics
error
Validation failed
valid_year_range
error
Validation failed
milestones_schema_valid
warning
Validation failed
metrics_json_schema_valid
warning
Validation failed
headline_max_length
error
Validation failed
status_transition_valid
error
Validation failed
generated_at_after_period_end
warning
Validation failed
Business Rules
one_summary_per_user_per_period
Each peer mentor may have at most one impact summary per distinct period (period_type + year + half_index). The unique index on (user_id, period_type, year, half_index) enforces this at the database level. If a recalculation is needed, the existing record is updated rather than a new one inserted.
peer_mentor_only
Impact summaries are generated exclusively for users who hold the peer_mentor role. The calculation service checks the user's role before invoking aggregation. Coordinators and admins do not receive personal impact summaries.
period_must_be_closed
A summary may only be generated once the reporting period has ended. Annual summaries are triggered after 31 December; half-year summaries after 30 June or 31 December respectively. The trigger service validates current date against the period boundary before scheduling calculation.
system_generated_only
Impact summary records are created exclusively by the backend calculation pipeline. No client-side API endpoint accepts a direct insert. RLS policies on the impact_summaries table block INSERT operations from authenticated user JWT tokens; only service-role tokens used by Edge Functions may write.
notification_dispatched_once
After a summary transitions to status='ready', a push notification is sent exactly once (notification_sent flag). The trigger service checks the flag before dispatching and sets it atomically to prevent duplicate notifications on retries.
half_index_required_for_half_year
When period_type is 'half_year', half_index must be 1 or 2. When period_type is 'annual', half_index must be NULL. Enforced via CHECK constraint and application-level validation.
feature_flag_gate
The yearly impact summary feature can be disabled per organization via the impact-summary-trigger-config. When disabled, no summaries are generated or notified for users affiliated with that organization.
share_count_increment_only
The share_count field may only be incremented; it cannot be decremented or set to an arbitrary value. Updates are performed via an atomic RPC increment to prevent race conditions when multiple shares occur in quick succession.
CRUD Operations
Storage Configuration
Entity Relationships
A peer mentor receives annual and half-year impact summaries recognizing their volunteer contributions