Periodic Summary
Data Entity
Description
A half-year or quarterly aggregated activity summary used for home screen progress cards and coordinator team overview dashboards. Includes year-over-year delta values, outlier classification (underactive or overloaded thresholds), and triggers a push notification when a new summary becomes available at period boundaries.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the periodic summary record, generated server-side at creation time. | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user (peer mentor or coordinator) this summary belongs to. | required |
organisation_id |
uuid |
Foreign key referencing the organisation to enforce tenant-scoped data isolation on all queries. | required |
period_type |
enum |
Indicates whether this is a half-year or quarterly summary period. Determines which period boundary fields are relevant. | required |
year |
integer |
Calendar year this summary covers, e.g. 2025. Combined with period_type, quarter, and half to identify a unique period. | required |
quarter |
integer |
Quarter number (1–4) when period_type is 'quarterly'. Must be NULL when period_type is 'half_year'. | - |
half |
integer |
Half-year number (1 = Jan–Jun, 2 = Jul–Dec) when period_type is 'half_year'. Must be NULL when period_type is 'quarterly'. | - |
period_start |
datetime |
Inclusive UTC start timestamp of the aggregated period window used for activity queries. | required |
period_end |
datetime |
Inclusive UTC end timestamp of the aggregated period window. Must be after period_start. | required |
total_sessions |
integer |
Total number of activity sessions logged by the user within the summary period. | required |
total_hours |
decimal |
Total volunteered hours (sessions Ă— duration) within the summary period, stored with 2 decimal places. | required |
yoy_delta_sessions |
integer |
Year-over-year change in total sessions compared to the same period in the prior year. NULL when no prior-year data exists. | - |
yoy_delta_hours |
decimal |
Year-over-year change in total hours compared to the same period in the prior year. NULL when no prior-year data exists. | - |
yoy_delta_percent |
decimal |
Year-over-year percentage change in sessions ((current - prior) / prior * 100). NULL when no prior-year data or prior-year total is zero. | - |
prior_year_total_sessions |
integer |
Total sessions from the equivalent prior-year period, stored for audit and display purposes. NULL when prior-year data does not exist. | - |
prior_year_total_hours |
decimal |
Total hours from the equivalent prior-year period, stored for audit and display purposes. NULL when prior-year data does not exist. | - |
outlier_status |
enum |
Classification of the user's activity level for the period relative to organisation-configured thresholds. 'underactive' is below the lower threshold, 'overloaded' is above the upper threshold, 'normal' is within bounds. | required |
underactive_threshold_sessions |
integer |
The lower session count threshold used for outlier classification at generation time. Stored so the classification is reproducible even if thresholds change later. | required |
overloaded_threshold_sessions |
integer |
The upper session count threshold used for outlier classification at generation time. Must be greater than underactive_threshold_sessions. | required |
generated_at |
datetime |
UTC timestamp when this summary record was computed and persisted by the generation job. | required |
notification_sent_at |
datetime |
UTC timestamp when the push notification announcing this summary was dispatched. NULL until notification is sent. | - |
coordinator_id |
uuid |
When this summary is a team-level aggregate for a coordinator's roster, this field holds the coordinator's user_id. NULL for individual peer mentor summaries. | - |
Database Indexes
idx_periodic_summary_user_period
Columns: user_id, period_type, year, quarter, half
idx_periodic_summary_organisation
Columns: organisation_id
idx_periodic_summary_user_id
Columns: user_id
idx_periodic_summary_coordinator_period
Columns: coordinator_id, period_type, year
idx_periodic_summary_outlier_status
Columns: organisation_id, period_type, year, outlier_status
idx_periodic_summary_generated_at
Columns: generated_at
Validation Rules
period_type_enum_valid
error
Validation failed
quarter_null_for_half_year
error
Validation failed
half_null_for_quarterly
error
Validation failed
year_within_valid_range
error
Validation failed
non_negative_totals
error
Validation failed
overloaded_threshold_exceeds_underactive
error
Validation failed
period_end_after_period_start
error
Validation failed
outlier_status_consistent_with_thresholds
error
Validation failed
Business Rules
unique_summary_per_user_per_period
Only one periodic summary record may exist per (user_id, period_type, year, quarter, half) combination. If the scheduler runs again for the same period, the existing record is overwritten rather than duplicated.
generation_at_period_boundaries_only
Half-year summaries are generated at the start of January (covering the prior H2) and July (covering the prior H1). Quarterly summaries are generated at the start of each quarter month. The scheduler must not generate summaries mid-period.
yoy_delta_requires_prior_year_data
Year-over-year delta fields (yoy_delta_sessions, yoy_delta_hours, yoy_delta_percent) must be NULL when no prior-year summary or activity data exists for the same period type. They must never be zero-filled as a substitute for missing data.
outlier_classification_uses_snapshot_thresholds
The outlier_status classification must use the organisation's threshold values at generation time, stored in underactive_threshold_sessions and overloaded_threshold_sessions. Changing thresholds later must not retroactively alter existing records.
push_notification_sent_on_new_summary
A push notification must be dispatched to the user whenever a new periodic summary is generated. notification_sent_at must be updated after successful dispatch. Failure to send must be logged but must not block summary persistence.
organisation_data_isolation
All queries against periodic_summary must be scoped to the requesting user's organisation_id. Cross-organisation reads are prohibited and must be blocked at the RLS policy level.
coordinator_team_summary_distinct_from_personal
When coordinator_id is set, the summary represents the team aggregate for that coordinator's roster, not the coordinator's own activity. user_id in this case still references the coordinator, but the record must be distinguished in queries by the non-null coordinator_id.
CRUD Operations
Storage Configuration
Entity Relationships
A peer mentor has periodic summaries generated for each half-year and quarterly period they are active
A periodic summary is computed for and owned by a specific user (peer mentor or coordinator) for the relevant period