Activity Aggregation Repository
Component Detail
Description
Executes aggregation queries over the activity table to compute session counts, total hours, and other metrics for a given peer mentor or coordinator team within a date range. All queries include an organisation-scoped row-level-security filter to enforce data isolation.
activity-aggregation-repository
Summaries
The Activity Aggregation Repository is the foundational data intelligence layer that powers all reporting, coaching insights, and outlier detection across the platform. By computing session counts, total hours, and trend metrics at both the individual mentor and coordinator-team level, it enables the organisation to demonstrate measurable programme impact to leadership and funding bodies. The built-in organisation-scoped data isolation ensures that sensitive activity data from one institution is never accessible to another, which is critical for multi-tenant enterprise contracts, compliance audits, and client trust. As a shared component, it maximises return on development investment by serving multiple features from a single, well-tested implementation.
As a shared, dependency-free data component, the Activity Aggregation Repository sits at the critical path of multiple downstream features including outlier detection, period summaries, and coordinator dashboards. It must be delivered and stabilised early in the sprint sequence to unblock dependent teams. Its medium complexity stems from the need to handle variable date ranges, prior-period comparisons, and multi-level aggregation (mentor, team, organisation). Performance testing against large activity tables is essential before launch, particularly for organisation-wide queries.
Row-level security (RLS) logic must be reviewed by a security-aware engineer and tested with cross-organisation data fixtures to prevent data leakage, which is a key compliance risk.
The Activity Aggregation Repository executes SQL aggregation queries directly against the `activity` table, exposing four interfaces: `getMentorAggregates`, `getTeamAggregates`, `getOrganisationAggregates`, and `getPriorPeriodAggregates`. All queries must include an `organisation_id` filter that maps to a Supabase Row Level Security policy to enforce data isolation at the database level — application-layer filtering alone is insufficient. Aggregations should use `COUNT(*)` for session counts and `SUM(duration_minutes)` for hours, grouped by the appropriate foreign key. Index coverage on `(mentor_id, organisation_id, started_at)` is essential for query performance.
Prior-period queries require careful date arithmetic; use parameterised start/end dates rather than computed offsets inside SQL to keep queries portable and testable. This component has no side effects and is safe to call concurrently.
Responsibilities
- Query total sessions and hours for a mentor within a date range
- Query team-level aggregates for a coordinator within a date range
- Apply organisation-level data isolation on every query
- Return results suitable for period comparison and outlier detection
Interfaces
getMentorAggregates(mentorId, startDate, endDate, organisationId)
getTeamAggregates(coordinatorId, startDate, endDate, organisationId)
getOrganisationAggregates(organisationId, startDate, endDate)
getPriorPeriodAggregates(mentorId, priorStart, priorEnd, organisationId)
Relationships
Dependents (3)
Components that depend on this component
Related Data Entities (2)
Data entities managed by this component