Annual Summary
Data Entity
Description
A computed yearly summary of a peer mentor's contributions, powering the Spotify Wrapped-inspired annual impact feature. Contains total hours volunteered, unique individuals helped, total session count, activity type distribution, and detected milestones. Generated on demand and cached locally for offline display.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key generated at summary creation time | PKrequiredunique |
peer_mentor_id |
uuid |
Foreign key referencing the peer_mentor whose contributions this summary aggregates. Drives all query scoping. | required |
organization_id |
uuid |
Foreign key to the organization, used for RLS policy enforcement and multi-tenant data isolation. | required |
year |
integer |
Calendar year the summary covers, e.g. 2024. Combined with period_type to define the exact date window. | required |
period_type |
enum |
Granularity of the summary period. full_year covers Jan–Dec; first_half covers Jan–Jun; second_half covers Jul–Dec. Determines which date window was aggregated. | required |
total_hours |
decimal |
Aggregate volunteered hours across all activity sessions in the period, stored with 2 decimal places. Computed as sum(duration_minutes) / 60. | required |
unique_contacts_helped |
integer |
Count of distinct contact_id values across all activities in the period. Represents unique individuals the peer mentor engaged with. | required |
total_sessions |
integer |
Total number of registered activity sessions in the period. Used as the denominator for activity_type_breakdown percentages. | required |
activity_type_breakdown |
json |
JSONB object mapping activity_type_id keys to session counts for that type within the period. Example: {"activity-type-uuid-1": 12, "activity-type-uuid-2": 7}. Used by ActivityTypeBreakdownWidget to render the donut chart. | required |
milestones |
json |
JSONB array of detected milestone objects for this period. Each entry includes milestone_id, threshold, achieved_at, and is_new_this_period. Populated by milestone-detection-service at computation time. Example: [{"milestone_id": "first-50-sessions", "threshold": 50, "achieved_at": "2024-06-15", "is_new": true}]. | - |
period_start_date |
datetime |
Inclusive start timestamp of the aggregation window. Derived from year + period_type but stored explicitly for query efficiency and audit clarity. | required |
period_end_date |
datetime |
Inclusive end timestamp of the aggregation window. Derived from year + period_type but stored explicitly for audit and comparison queries. | required |
cached_at |
datetime |
UTC timestamp when this summary record was last computed and written. Used by summary-offline-cache to determine staleness (default max age: 24 hours). Reset on every recomputation. | required |
cache_version |
integer |
Monotonically incrementing counter bumped on each recomputation. Enables optimistic concurrency checks when writing from multiple clients and allows the UI to detect when a fresher version is available. | required |
is_stale |
boolean |
Set to true when an activity is recorded or deleted within the period after the summary was last computed. Signals wrapped-summary-bloc and annual-summary-repository that recomputation is needed before display. | required |
created_at |
datetime |
Immutable UTC timestamp of first record insertion. | required |
updated_at |
datetime |
UTC timestamp of the most recent update to any mutable field, maintained by a Supabase trigger. | required |
Database Indexes
idx_annual_summary_mentor_year_period
Columns: peer_mentor_id, year, period_type
idx_annual_summary_peer_mentor_id
Columns: peer_mentor_id
idx_annual_summary_organization_year
Columns: organization_id, year
idx_annual_summary_is_stale
Columns: is_stale
idx_annual_summary_cached_at
Columns: cached_at
Validation Rules
total_hours_non_negative
error
Validation failed
unique_contacts_non_negative
error
Validation failed
total_sessions_non_negative
error
Validation failed
period_dates_consistent_with_year_and_type
error
Validation failed
activity_type_breakdown_valid_json
error
Validation failed
milestones_valid_schema
warning
Validation failed
peer_mentor_id_must_exist
error
Validation failed
cache_version_increment_only
error
Validation failed
Business Rules
one_summary_per_mentor_year_period
Each peer mentor may have at most one annual_summary record per (peer_mentor_id, year, period_type) combination. Subsequent computation requests must UPDATE the existing record rather than INSERT a new one, bumping cache_version and resetting cached_at.
derived_read_only_fields
total_hours, unique_contacts_helped, total_sessions, activity_type_breakdown, and milestones are computed values. They must never be set by UI layer components directly; only annual-stats-aggregation-service and milestone-detection-service may write them.
stale_flag_on_activity_change
Whenever an activity is inserted, updated, or deleted for a peer mentor, if a corresponding annual_summary exists whose period_start_date ≤ activity.date ≤ period_end_date, the is_stale flag must be set to true via a Supabase database trigger. This ensures wrapped-summary-bloc recomputes before displaying.
year_cannot_be_future
A summary may only be generated for a year that has at least partially elapsed. The year field must be ≤ the current calendar year. A request to generate a full_year summary for the current year is allowed (partial-year summary), but no future years are permitted.
organization_scoped_rls
All read and write operations on annual_summary rows must be scoped to the requesting user's organization_id via Supabase RLS policies. A peer mentor may only read their own records (peer_mentor_id = auth.uid()). Coordinators may read records for peer mentors within their assigned chapters.
activity_type_breakdown_totals_sessions
The sum of all values in activity_type_breakdown must equal total_sessions. Enforced at computation time by annual-stats-aggregation-service before persistence.
offline_cache_max_age
The local device cache written by summary-offline-cache is considered stale after 24 hours (configurable). On app foreground, wrapped-summary-bloc checks isStale(key, maxAge: 24h). If stale and network is available, recomputation is triggered. If offline, the cached (stale) version is displayed with a visual staleness indicator.
CRUD Operations
Storage Configuration
Entity Relationships
A peer mentor has one annual summary record per year for the Wrapped impact feature, supporting multiple period types per year