Supabase Aggregation RPC Functions
Component Detail
Description
PostgreSQL functions deployed to Supabase that execute server-side aggregation logic, performing COUNT DISTINCT, SUM, and GROUP BY operations on activity and participant data. These functions are the performance-critical backend of the aggregation system, replacing client-side processing of potentially thousands of records.
supabase-aggregation-rpc
Summaries
The Supabase Aggregation RPC Functions are the performance engine behind every Bufdir grant report, executing complex statistical calculations — unique participant counts, hours delivered, geographic distributions — directly inside the database rather than shipping thousands of raw records to user devices. This approach means reports generate in seconds rather than minutes, even as activity volumes grow year over year. For the four member organizations collectively managing large participant datasets, this directly translates to staff time savings during each reporting cycle and eliminates the timeout failures and data inconsistencies that plagued previous client-side approaches. These functions are a long-term scalability asset that supports organizational growth without requiring infrastructure changes.
These PostgreSQL functions represent high-complexity backend work that must be deployed and versioned in Supabase separately from the Dart application code, introducing a deployment coordination challenge. The functions are performance-critical and require database-level testing with realistic data volumes to validate that COUNT DISTINCT and GROUP BY operations perform within acceptable latency budgets. Each function signature (org_id, period_start, period_end) must remain stable once the Dart client depends on it — schema changes require coordinated releases. The proxy-registration deduplication logic in get_unique_participants_excluding_proxy is particularly complex and requires dedicated test cases.
Plan for a Supabase migration script per function and include database review in the definition of done.
These are PostgreSQL PL/pgSQL or SQL functions registered in Supabase and exposed via the PostgREST RPC interface at /rest/v1/rpc/{function_name}. Each function accepts org_id UUID, period_start TIMESTAMPTZ, and period_end TIMESTAMPTZ as parameters and returns JSONB or typed composite types. The COUNT DISTINCT pattern for unique participants uses a subquery over the participants join table filtered by activity date range and org_id. Proxy deduplication in get_unique_participants_excluding_proxy excludes records where is_proxy_registration = true before applying DISTINCT.
Geographic distribution uses a two-level GROUP BY over region_id and chapter_id with LEFT JOINs to the geographic reference tables. All functions must include a WHERE org_id = $1 predicate as the first filter to leverage the org_id index. Deploy via Supabase migrations, not the dashboard, to maintain version control.
Responsibilities
- Execute COUNT DISTINCT on participant IDs per activity category
- Aggregate hours delivered with proxy-registration deduplication
- Perform geographic GROUP BY across region and chapter hierarchies
- Expose results via Supabase RPC interface callable from Dart client
Interfaces
get_bufdir_participant_counts(org_id, period_start, period_end)
get_bufdir_hours_delivered(org_id, period_start, period_end)
get_bufdir_geographic_distribution(org_id, period_start, period_end)
get_bufdir_activity_type_breakdown(org_id, period_start, period_end)
get_unique_participants_excluding_proxy(org_id, period_start, period_end)