🏠 Overview › ✅ Implementation Tasks ›✅ epic-bufdir-reporting-export-processing-services-task-001

Implement Bufdir Activity Query Service core data access

epic-bufdir-reporting-export-processing-services-task-001 — Build the core BufdirActivityQueryService that queries activities from the Supabase database with org-scoped filtering, date range parameters, and multi-chapter deduplication logic. Implement the primary query method that accepts org ID, reporting period, and chapter scope, returning deduplicated activity records ready for export processing.

critical priority high complexity backend pending backend specialist Tier 0

Acceptance Criteria

BufdirActivityQueryService class is implemented as a singleton or injectable service with a clearly defined public API
Primary query method accepts orgId (String), reportingPeriod (DateRange), and chapterScope (List<String>) parameters and returns Future<List<BufdirActivityRecord>>
Activities are filtered by the provided date range using Supabase PostgREST gte/lte operators on the activity date column
Query only returns activities belonging to the specified org and its child chapters, never activities from other organizations
Returned records include all fields required for Bufdir export: peer mentor ID, activity type, date, duration, participant count, chapter ID
Service handles empty result sets gracefully, returning an empty list rather than throwing
Service propagates Supabase errors as typed exceptions (BufdirQueryException) with meaningful messages
Query execution time for a typical reporting period (3 months, up to 500 activities) is under 3 seconds
Service is covered by unit tests using a mocked Supabase client with at least 5 scenario-based test cases
All public methods and parameters are documented with Dart doc comments

Technical Requirements

frameworks
Flutter
Dart
Supabase Dart SDK
Riverpod
apis
Supabase PostgREST REST API
Supabase auth.uid() for RLS context
data models
Activity
PeerMentor
Chapter
Organization
BufdirActivityRecord
performance requirements
Query must complete within 3 seconds for up to 500 activity records over a 3-month period
Use Supabase select() with explicit column lists — never select(*) — to minimize payload size
Apply server-side filtering (date range, org scope) in the PostgREST query rather than client-side to reduce data transfer
Implement pagination support (limit/offset) for future-proofing against large orgs
security requirements
All queries must execute within an authenticated Supabase session — never use service role key on the client
Rely on Supabase RLS policies as the primary access control layer; the service must never bypass RLS
Validate that orgId parameter matches the authenticated user's org before issuing the query
Never log raw activity records containing personal data (names, contact info)

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Model the service as a plain Dart class (not a Flutter widget) placed in lib/features/bufdir/data/services/. Inject the SupabaseClient via constructor for testability — do not call Supabase.instance.client directly inside the service. Use a BufdirReportingPeriod value object (startDate, endDate) rather than raw DateTime parameters to enforce invariants (start must be before end). The Supabase query should join activities with chapters and peer_mentors tables in a single PostgREST call using embedded resource syntax (select('*, chapter:chapters(*), peer_mentor:peer_mentors(*)')) to avoid N+1 queries.

Define BufdirActivityRecord as an immutable Dart class (or freezed data class) that maps exactly to the fields Bufdir requires, keeping it decoupled from the internal Activity database model. This separation is critical because Bufdir's required fields may not 1:1 match internal schema. Consider placing the mapping logic in a BufdirActivityMapper class. Avoid using .execute() legacy API — use the modern chained Supabase Dart SDK pattern (.from().select().eq().gte().lte()).

Testing Requirements

Write unit tests using flutter_test with a mocked SupabaseClient (using Mockito or manual mock). Test scenarios must cover: (1) successful query returning multiple activities across chapters, (2) empty result set for a valid org with no activities in the period, (3) single-chapter org returning only its own activities, (4) multi-chapter org aggregating all child chapter activities, (5) Supabase error (network failure) propagated as BufdirQueryException. Integration tests against a Supabase staging environment should verify RLS enforcement by testing that a user from org A cannot retrieve org B activities even with a crafted query. Aim for 90%+ line coverage on the service class.

Component
Bufdir Activity Query Service
service high
Epic Risks (3)
high impact medium prob technical

NHF contacts can belong to up to five local chapters simultaneously. If the deduplication logic in the activity query service incorrectly attributes cross-chapter activities, organisations will either under-report or over-report to Bufdir, which could trigger grant clawback or compliance investigations.

Mitigation & Contingency

Mitigation: Implement deduplication using the existing multi-chapter membership service as the source of truth for chapter affiliation. Write test fixtures covering all known multi-chapter edge cases and validate outputs against manually prepared reference exports from NHF.

Contingency: If deduplication cannot be made deterministic for complex hierarchies before release, gate the export behind an org-level feature flag and require NHF to validate a preview export against their manual Excel before enabling in production.

medium impact medium prob dependency

Server-side Dart libraries for Excel generation are less mature than equivalents in Node.js or Python. The chosen library may lack support for Bufdir-required formatting features (merged cells, data validation, specific date formats), requiring significant workaround effort or a library switch mid-implementation.

Mitigation & Contingency

Mitigation: Evaluate the top two Dart xlsx libraries (excel, spreadsheet_decoder) against a Bufdir template sample file before committing. Identify all required formatting features and verify library support in a spike.

Contingency: If no Dart library meets requirements, implement the Excel generation as a Supabase Edge Function in TypeScript using the well-supported ExcelJS library, exposing it to the Dart backend via an internal RPC call.

medium impact medium prob integration

The attachment bundler must retrieve documents from Supabase Storage that were uploaded by the document attachments feature. If storage paths, RLS policies, or signed URL expiry have not been standardised across features, the bundler may fail to retrieve attachments at export time.

Mitigation & Contingency

Mitigation: Audit the document attachments feature's storage schema and RLS policies before implementing the bundler. Agree on a stable internal service-account access pattern for cross-feature storage reads.

Contingency: If cross-feature storage access cannot be made reliable, implement the bundler to include only attachments that can be retrieved successfully and produce a manifest listing any attachments that could not be bundled, rather than failing the entire export.

Quick Links
All Tasks Execution Plan