Implement parameterized RPC call wrappers

epic-bufdir-data-aggregation-data-layer-task-003 — Implement typed Dart function signatures in the Aggregation Query Builder that wrap each Supabase RPC function call (activity counts, contact counts, event counts, geographic distribution, participant deduplication). Each wrapper must accept a reporting period, organization filter, and optional category filter, then return the appropriate strongly typed Dart model. Apply the org-isolation filter on every call.

critical priority high complexity backend pending backend specialist Tier 2

Acceptance Criteria

Five typed wrapper methods are implemented: `fetchActivityCounts`, `fetchContactCounts`, `fetchEventCounts`, `fetchGeographicDistribution`, `fetchParticipantDeduplication`

Each method signature accepts: `DateTimeRange reportingPeriod` (required), `OrgFilter orgFilter` (required), and `String? categoryFilter` (optional)

Each method returns a `Future<T>` where T is the appropriate strongly typed model: ActivityRecord, ContactRecord, EventRecord, GeographicDistributionResult, or ParticipantCount

The org-isolation filter (`applyOrgFilter`) is called inside every wrapper before the Supabase RPC call is dispatched — verified by unit test mock assertion

RPC parameter construction maps Dart method arguments to Supabase RPC parameter names exactly as defined in the database function signatures

Supabase error responses are caught and re-thrown as typed domain exceptions (e.g., `AggregationQueryException`) with the original error message preserved

Null RPC responses (empty result sets) are handled gracefully: return a zero-value model instance rather than throwing a null pointer exception

All DateTime values sent to Supabase RPCs are serialized in ISO 8601 UTC format

Unit tests verify each wrapper calls the correct RPC function name as a string literal

Unit tests verify that the org filter is applied on every call even when a category filter is omitted

The implementation compiles cleanly with `dart analyze` with zero warnings

Technical Requirements

frameworks

flutter_test

Supabase Flutter SDK (`supabase_flutter`)

Mockito or mocktail for Supabase client mocking

apis

Supabase RPC: `activity_counts(org_id, period_start, period_end, category?)`

Supabase RPC: `contact_counts(org_id, period_start, period_end)`

Supabase RPC: `event_counts(org_id, period_start, period_end, category?)`

Supabase RPC: `geographic_distribution(org_id, period_start, period_end)`

Supabase RPC: `participant_deduplication(org_id, period_start, period_end)`

data models

ActivityRecord

ContactRecord

EventRecord

GeographicDistributionResult

ParticipantCount

AggregationQueryException

OrgFilter

DateTimeRange

performance requirements

Each RPC call must complete within a 10-second timeout — implement `timeout(Duration(seconds: 10))` on each Future

No N+1 patterns: each method must issue exactly one RPC call regardless of org count

security requirements

RPC parameter values must be passed as named parameters, never interpolated into SQL strings

categoryFilter input must be validated against an allowlist of known Bufdir category codes before being passed to the RPC to prevent injection

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Define the five wrapper methods as public async methods on `AggregationQueryBuilder`. Use a private helper `_callRpc(String rpcName, Map params, T Function(dynamic) deserializer)` to eliminate repetition in the RPC invocation pattern — each public wrapper only needs to build its params map, call `_callRpc`, and return the deserialized result. This makes the org-filter application and error handling consistent across all wrappers. Map `DateTimeRange` to `period_start` and `period_end` ISO 8601 strings inside `_callRpc` to keep the public API clean.

For the `categoryFilter`, define an `allowlist` constant Set of valid Bufdir category code strings and validate at the top of each wrapper that accepts a category. Define `AggregationQueryException` in the shared exceptions file with fields: `String rpcName`, `String message`, `Object? cause`. Note that Supabase RPCs return `List` — if the list has exactly one element use `list.first`, if empty return a zero-value model, if more than one log a warning and use `list.first`.

Testing Requirements

Unit tests using `flutter_test` and mocktail must cover: (1) Happy-path test for each of the five wrappers: stub the Supabase client mock to return a valid JSON list, assert the returned Dart model equals the expected deserialized value. (2) Org filter application: verify `applyOrgFilter` was called exactly once per wrapper invocation using mock verification. (3) RPC name correctness: verify the exact RPC function name string is passed to `supabase.rpc()` for each wrapper. (4) Category filter pass-through: verify the category parameter is included in the RPC params when provided and omitted when null.

(5) Empty result handling: stub the mock to return an empty list, assert the method returns a zero-value model without throwing. (6) Error handling: stub the mock to throw a `PostgrestException`, assert the wrapper rethrows as `AggregationQueryException` with the original message. (7) Timeout behavior: stub the mock to never complete, assert the Future completes with a timeout error within 11 seconds. Test coverage target: 100% of all five wrapper method branches.

Component

Aggregation Query Builder

data high

Dependencies (2)

Create strongly typed Dart model classes for all aggregation-related data structures: activity records, event records, contact records, metric snapshots, geographic distribution results, and participant counts. Include fromJson/toJson serialization, equality operators, and copyWith methods. These models form the contract between the query builder and the rest of the system. epic-bufdir-data-aggregation-data-layer-task-001 Build the org-isolation filter layer inside the Aggregation Query Builder that automatically appends organization_id constraints to every RPC call and Supabase view query. Must support single-org and multi-org contexts, integrate with the tenant session store, and throw a typed exception if no org context is active. This filter must be applied before any query is dispatched to Supabase. epic-bufdir-data-aggregation-data-layer-task-002

Epic Risks (2)

high impact medium prob technical

Supabase RPC functions return JSON with PostgreSQL numeric types (bigint, numeric) that do not map cleanly to Dart int/double. Silent truncation or JSON parsing errors could corrupt participant counts in the final Bufdir submission without any runtime exception.

Mitigation & Contingency

Mitigation: Define explicit Dart fromJson factories for all RPC result models with type-safe parsing and assertion checks. Add a contract test that compares raw RPC JSON output against expected Dart model values using a known seed dataset.

Contingency: If type mismatches are found in production metrics, expose a validation endpoint in BufdirMetricsRepository that re-fetches and compares raw RPC output against the persisted snapshot, flagging any discrepancies before export proceeds.

medium impact high prob scope

Persisted metric snapshots can become stale if additional activities are registered after the snapshot is saved but before the export is finalized. Coordinators might unknowingly export data that does not reflect the latest activity registrations.

Mitigation & Contingency

Mitigation: Store a snapshot_generated_at timestamp and a record_count_at_generation field in the snapshot. When the coordinator views cached results, compare the current activity count for the period against the snapshot value and display a 'Data updated since last aggregation — re-run?' warning if counts differ.

Contingency: Add a mandatory staleness check before the export confirmation dialog can proceed: if the snapshot is more than 24 hours old or the record count has changed, require the coordinator to re-run aggregation before the export button is enabled.

Quick Links

All Tasks Execution Plan