Activity Statistics
Data Entity
Description
Pre-aggregated activity statistics per user and time period, derived from the activities table. Supports personal dashboards for peer mentors and team-level reporting for coordinators. Recomputed on activity change events to avoid expensive real-time aggregation on read.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key | PKrequiredunique |
user_id |
uuid |
The peer mentor or coordinator whose statistics this row represents. Foreign key to users. | required |
organization_id |
uuid |
Organization scope for this statistics row. Required for multi-tenant isolation and coordinator team roll-ups. | required |
period_type |
enum |
Granularity of the aggregation window. | required |
period_start |
datetime |
Inclusive start of the aggregation window (UTC). Null only for all_time rows. | - |
period_end |
datetime |
Exclusive end of the aggregation window (UTC). Null only for all_time rows. | - |
total_activities |
integer |
Total number of completed activities logged by the user in the period. | required |
total_duration_minutes |
integer |
Sum of duration_minutes across all activities in the period. | required |
unique_contacts_reached |
integer |
Count of distinct contacts the user had activities with in the period. | required |
activity_type_breakdown |
json |
Map of activity_type_id → count for the period. E.g. {"home-visit": 12, "phone-call": 8}. Keys are valid activity_type IDs. | - |
home_visit_count |
integer |
Denormalized count of home-visit activities for fast dashboard rendering without JSON parsing. | required |
phone_call_count |
integer |
Denormalized count of phone-call activities. | required |
group_activity_count |
integer |
Denormalized count of group activities (events, workshops, group meetings). | required |
proxy_activity_count |
integer |
Activities registered by a coordinator on behalf of this user (proxy reporting). Used to distinguish self-reported vs proxy-reported contributions. | required |
last_activity_date |
datetime |
Timestamp of the most recent activity in the period. Used to determine recency on dashboard. | - |
streak_days |
integer |
Consecutive days with at least one logged activity as of period_end. Drives gamification streak display. | - |
bufdir_eligible_count |
integer |
Number of activities in this period that qualify for Bufdir grant reporting (meet minimum duration and contact requirements). Cached to avoid re-evaluation during export. | required |
computed_at |
datetime |
Timestamp when this row was last recomputed. Used to detect stale aggregations. | required |
created_at |
datetime |
Row creation timestamp. | required |
updated_at |
datetime |
Row last-update timestamp. | required |
Database Indexes
idx_activity_statistics_user_period
Columns: user_id, period_type, period_start
idx_activity_statistics_user_id
Columns: user_id
idx_activity_statistics_org_period
Columns: organization_id, period_type, period_start
idx_activity_statistics_computed_at
Columns: computed_at
Validation Rules
period_start_before_end
error
Validation failed
counts_non_negative
error
Validation failed
type_breakdown_keys_valid
warning
Validation failed
computed_at_not_future
error
Validation failed
user_belongs_to_organization
error
Validation failed
Business Rules
recompute_on_activity_change
Any create, update, or delete on activities must trigger recomputation of activity_statistics rows for the affected user_id and all period_type buckets that contain the activity's date. This keeps the derived table consistent without requiring full-table scans on read.
one_row_per_user_period
Each combination of (user_id, period_type, period_start) must be unique. Upsert semantics: recompute replaces the existing row rather than appending.
all_time_row_always_present
Every user must always have exactly one period_type='all_time' row with null period_start/period_end. Created on first activity, updated on every subsequent activity change.
org_scoped_team_stats
Coordinator team reports must only aggregate statistics rows for users within the same organization_id as the requesting coordinator. Cross-organization data access is prohibited.
bufdir_eligibility_recalculation
bufdir_eligible_count must be recalculated using the current Bufdir eligibility rules whenever an activity is modified. Eligibility criteria (minimum duration, contact presence, approved status) are applied from activity data, not cached from a previous state.
proxy_activity_attribution
Activities registered by a coordinator on behalf of a peer mentor (proxy_registered_by IS NOT NULL in activities) must be counted under the peer mentor's statistics, not the coordinator's. proxy_activity_count is incremented separately to preserve visibility into self- vs proxy-reported ratios.