Build ExportDataQueryBuilder with multi-table join logic
epic-bufdir-report-export-foundation-task-005 — Implement the query builder service that encapsulates all complex join logic needed for Bufdir export data. Queries must join activities, activity_type_configuration, org_hierarchy, user_roles, and contact tables for a given org scope and date range. Expose buildExportQuery(orgIds, periodStart, periodEnd) returning a structured ExportDataSet. This is the single source of truth for what data enters every export — correctness here is critical for Bufdir compliance.
Acceptance Criteria
Technical Requirements
Implementation Notes
Implement the core join logic as a Supabase RPC (PostgreSQL function) rather than chaining multiple PostgREST calls from Flutter — this keeps network round trips to one and allows the database query planner to optimise the join. The Dart service layer calls `.rpc('build_export_dataset', params: {...})` and deserialises the JSON response into ExportDataSet. Define ExportDataSet as a freezed data class with fromJson/toJson. Keep the RPC function under version control in `supabase/functions/` or a migrations SQL file so schema changes are tracked.
The user_roles join should snapshot the role at `activity.created_at` — query the `user_role_history` table (or audit log) if available, otherwise use current role with a documented assumption. Add a `warnings` list to ExportDataSet metadata to surface unmapped activity types, which BufdirCategoryMapper (task-007) will also populate. This shared warnings channel avoids duplicate alerting logic.
Testing Requirements
Unit tests (flutter_test) for ExportDataQueryBuilder covering: (1) correct SQL/PostgREST filter construction for single and multi-org scopes; (2) inclusive boundary date handling; (3) graceful empty result; (4) missing activity_type_configuration mapping produces warning in metadata not exception; (5) invalid orgIds returns empty dataset. Integration tests against a Supabase local dev instance seeded with known fixture data, asserting row counts and field values. Performance test with a fixture of 1,400 org IDs and 50,000 activities must complete under 3 seconds. No mocking of the Supabase client in integration tests — use the real client against the local instance.
NHF's three-level hierarchy (national / region / chapter) with 1,400 chapters may have edge cases such as chapters belonging to multiple regions, orphaned nodes, or missing parent links in the database. Incorrect scope expansion would silently under- or over-report activities, which could invalidate a Bufdir submission.
Mitigation & Contingency
Mitigation: Obtain a full hierarchy fixture export from NHF before implementation begins. Write exhaustive unit tests covering boundary cases: single chapter, full national roll-up, chapters with no activities, and chapters assigned to multiple regions. Validate resolver output against a known-good manual count.
Contingency: If hierarchy data quality is too poor for automated resolution at launch, implement a manual scope override in the coordinator UI that allows the coordinator to explicitly select org units from a tree picker, bypassing the resolver.
The activity_type_configuration table may not cover all activity types currently in use, leaving a subset unmapped at launch. Bufdir submissions with unmapped categories will be incomplete and may be rejected by Bufdir.
Mitigation & Contingency
Mitigation: Run a query against production activity data before implementation to enumerate all distinct activity type IDs. Cross-reference with Bufdir's published category schema (request from Norse Digital Products). Flag every gap as a known issue and build the warning surface into the preview panel.
Contingency: Implement a fallback 'Other' category bucket for unmapped types and surface a prominent warning in the export preview requiring coordinator acknowledgement before proceeding. Log unmapped types for post-launch cleanup.
Supabase RLS policies on generated_reports and the storage bucket must enforce strict org isolation. A misconfigured policy could allow a coordinator from one organisation to read another organisation's export files, creating a serious data breach with GDPR implications.
Mitigation & Contingency
Mitigation: Write RLS integration tests that attempt cross-org reads with explicitly different JWT tokens and assert that all attempts return empty sets or 403 errors. Include RLS policy review in the pull request checklist. Use Supabase's built-in policy tester during development.
Contingency: If a policy gap is discovered post-deployment, immediately revoke all signed URLs for affected exports, audit the access log for unauthorised reads, and issue a coordinated disclosure to affected organisations per GDPR breach notification requirements.