Aggregation Query Builder
Component Detail
Description
Constructs the Supabase RPC calls and view queries that perform server-side aggregation, leveraging PostgreSQL's aggregation capabilities to minimize data transfer to the client. Encapsulates all raw SQL logic behind typed Dart function calls.
aggregation-query-builder
Summaries
The Aggregation Query Builder directly protects the organization's reporting accuracy and operational efficiency by ensuring all statistical summaries — participant counts, hours delivered, geographic breakdowns — are computed on the database server rather than downloaded and processed on user devices. This reduces data transfer costs, eliminates the risk of incorrect manual aggregations, and ensures that Bufdir grant reports reflect precisely accurate figures. Inaccurate grant reporting can jeopardize funding relationships worth millions of kroner annually, making this component a direct safeguard against financial and reputational risk across all four member organizations.
This high-complexity backend component centralizes all Supabase RPC and view query construction behind typed Dart function calls, meaning any change to reporting logic requires modifying only this component rather than scattered query strings across the codebase. Development effort is substantial due to the need to handle pagination for large datasets, apply correct org-scoping on every query, and maintain typed interfaces for each aggregation type. Testing requires a realistic Supabase environment with seeded multi-org data. It has a hard dependency on the Multi-Org Data Isolator, so that component must be stable before integration testing begins.
Delays here directly block the Bufdir export pipeline.
Aggregation Query Builder wraps all Supabase RPC invocations and PostgreSQL view queries behind strongly typed Dart methods, preventing raw SQL strings from leaking into business logic layers. Each builder method accepts orgId and periodId, delegates org-scoping to the multi-org-data-isolator via applyOrgScope(), and returns a constructed query or RPC call ready for execution. The executeRpc() method handles the Supabase PostgREST RPC invocation pattern. Pagination is handled within buildActivityCountQuery() for large activity datasets using Supabase's range() API.
All query parameters are injected as named params to prevent SQL injection. Evolution note: when new Bufdir reporting categories are added, corresponding builder methods must be added here in sync with new RPC functions deployed to Supabase.
Responsibilities
- Build parameterized RPC calls for Supabase aggregation functions
- Construct Supabase view queries filtered by org and period
- Handle query pagination for large activity datasets
- Apply organization isolation filters on every query
Interfaces
buildActivityCountQuery(orgId, periodId, activityType)
buildParticipantSumQuery(orgId, periodId)
buildHoursDeliveredQuery(orgId, periodId)
buildGeographicBreakdownQuery(orgId, periodId)
buildUniqueParticipantRpcCall(orgId, periodId)
buildProxyAwareParticipantQuery(orgId, periodId)
executeRpc(functionName, params)
Relationships
Dependents (4)
Components that depend on this component
Related Data Entities (2)
Data entities managed by this component