Export Data Query Builder
Component Detail
Description
Constructs parameterised Supabase queries for fetching activity data within the selected scope and period. Encapsulates complex join logic across activities, peer mentors, and organisational hierarchy tables.
export-data-query-builder
Summaries
The Export Data Query Builder is the engine that pulls together all activity records across an organisation's hierarchy — from individual peer mentors up to regional or national scope — within a chosen reporting period. Without this component, producing accurate Bufdir reports would require manual data collation across multiple systems, introducing error and consuming significant staff time. By centralising complex data retrieval logic, this component ensures every export reflects the complete, correct picture of organisational activity, directly supporting funding compliance and reducing the risk of under-reporting or over-reporting to government bodies.
This is the highest-complexity component in the export pipeline and carries the most delivery risk. It requires deep knowledge of the organisational hierarchy schema, activity table structure, and Supabase query optimisation. Dependencies on peer mentor and organisation tables mean schema changes elsewhere can break this component — establish a schema freeze policy before development begins. Allocate at least one full sprint for implementation plus a dedicated sprint slice for performance testing with production-scale data volumes.
Integration tests must cover multi-level rollup scenarios, empty date ranges, and large paginated datasets. This component blocks the edge function and serializer from being fully tested.
Encapsulates all Supabase query construction for activity data export. buildActivityQuery() and buildMentorQuery() accept arrays of scope IDs and apply WHERE IN filters with parameterised bindings. buildRollupQuery() performs multi-level aggregation using Supabase's RPC or chained select with group-by — choose RPC for complex rollups to keep logic server-side. paginate() wraps any query with range() calls for cursor-based pagination to avoid offset performance degradation on large tables.
execute() centralises query execution and maps errors to domain exceptions. Use query explain plans during development; add composite indexes on (org_id, activity_date) and (scope_id, status) as minimum. This component must never embed business logic — it is purely a query factory.
Responsibilities
- Build scope-filtered activity queries
- Join peer mentor and org hierarchy data
- Apply date range filters
- Optimise queries for large datasets
Interfaces
buildActivityQuery(scopeIds, dateRange)
buildMentorQuery(scopeIds)
buildRollupQuery(scopeId, level)
execute(query)
paginate(query, page, size)