Implement Supabase view query wrappers

epic-bufdir-data-aggregation-data-layer-task-004 — Add typed query builder methods for Supabase view-based queries (as opposed to RPC calls): raw activity records, raw event records, and raw contact records for a given org and date range. Apply org-isolation filters, support pagination where needed, and return typed list results. These methods complement the RPC wrappers for scenarios where view queries are more appropriate than RPCs.

high priority medium complexity backend pending backend specialist Tier 3

Acceptance Criteria

Three typed view query methods are implemented: `fetchRawActivities`, `fetchRawEvents`, `fetchRawContacts`

Each method accepts `DateTimeRange reportingPeriod` (required) and returns `Future<List<T>>` with the appropriate typed model list

Org-isolation filter is applied on every view query — the `organization_id` constraint is always appended before dispatch

Date range filtering is applied via Supabase `.gte()` and `.lte()` on the appropriate timestamp column for each view

Pagination is implemented using `.range(offset, limit)` with a default page size of 500 records; callers can pass optional `int offset` and `int limit` parameters

All three methods return an empty list (not null or exception) when no records match the query criteria

Supabase query errors are caught and rethrown as `AggregationQueryException` with the view name and original error message

Unit tests verify the correct Supabase view name is queried for each method

Unit tests verify that the org filter constraint is present in the query for every method

Unit tests verify pagination parameters are correctly passed to the Supabase query chain

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 view: `v_raw_activities` (org_id, activity_date, category, participant_count, ...)

Supabase view: `v_raw_events` (org_id, event_date, location, participant_count, ...)

Supabase view: `v_raw_contacts` (org_id, contact_date, contact_type, ...)

data models

ActivityRecord

EventRecord

ContactRecord

AggregationQueryException

OrgFilter

DateTimeRange

performance requirements

Default page size of 500 records prevents oversized responses on large datasets

Each view query must include a 10-second Future timeout to match RPC wrapper behavior

Date range index columns must be the ones used in `.gte()`/`.lte()` filters — confirm with the database schema owner that indexes exist on those columns

security requirements

View queries must use parameterized filter values via the Supabase SDK — no string interpolation into query expressions

The org-isolation filter must be applied before any other filter to ensure it cannot be accidentally omitted in future filter additions

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

Implement the three methods as public async methods on `AggregationQueryBuilder` alongside the existing RPC wrappers. Use a private helper `_queryView(String viewName, Map filters, T Function(Map) deserializer, {int offset = 0, int limit = 500})` to centralize the Supabase query chain construction, org filter application, pagination, timeout, and error wrapping — keeping the three public methods thin. Inside the helper, apply org filter first by calling `applyOrgFilter(filters)`, then build the Supabase query as: `supabase.from(viewName).select().match(orgConstraint).gte(dateCol, start).lte(dateCol, end).range(offset, offset + limit - 1)`. Confirm with the backend team the exact column names used for date filtering in each view (`activity_date`, `event_date`, `contact_date`) — these must match the view schema exactly.

For large datasets in production, callers should loop with incrementing offsets until a returned list is shorter than the page size — document this pattern in the method's doc comment. These view wrappers are complementary to RPC wrappers: use RPCs for pre-aggregated counts and views for raw record access needed by the deduplication chain.

Testing Requirements

Unit tests using `flutter_test` and mocktail must cover: (1) Happy-path for each of the three methods: stub the Supabase query chain mock to return a valid list of JSON maps; assert the returned Dart list matches the expected deserialized models. (2) View name correctness: verify the exact Supabase view name string is passed to `supabase.from()` for each method. (3) Org filter application: verify the `organization_id` equality filter is applied before date range filters. (4) Date range filters: verify `.gte()` and `.lte()` are called with the correct column names and ISO 8601 date strings.

(5) Pagination: verify `.range(0, 499)` is called by default and that custom offset/limit values are correctly forwarded. (6) Empty result: stub the mock to return an empty list; assert the method returns `[]` without throwing. (7) Error handling: stub the mock to throw a `PostgrestException`; assert `AggregationQueryException` is thrown with the view name included. Test coverage target: 100% of all three method branches.

Component

Aggregation Query Builder

data high

Dependencies (2)

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 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. epic-bufdir-data-aggregation-data-layer-task-003

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