Integration test ExportDataQueryBuilder with live data
epic-bufdir-report-export-foundation-task-017 — Run integration tests for ExportDataQueryBuilder against the Supabase test database seeded with realistic activity data spanning multiple chapters in an NHF-like hierarchy. Verify that query results for a region-level scope include exactly the expected activity count, that category mappings are correctly applied, and that the query completes within 3 seconds for a 12-month period with 5000 activities. Document any slow query patterns for index optimisation.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Use a dedicated Supabase test project (different URL/anon key than dev/prod) to avoid polluting real data. Write a Dart seed script that can be run before the test suite and torn down after. Structure the hierarchy to mirror NHF: national → 9 regions → ~156 chapters per region subset. When measuring performance, run each query 3 times and use the median time to avoid cold-start skew.
Use EXPLAIN ANALYZE via a raw SQL call or Supabase RPC to capture query plans for slow paths. Document findings in a `docs/query-performance-findings.md` file. Pay attention to JOIN strategy: if ExportDataQueryBuilder uses recursive CTEs for hierarchy traversal, these are common bottlenecks — consider materialised paths or ltree extension if available on the Supabase plan.
Testing Requirements
This task IS the testing task. Write integration tests using flutter_test with the integration_test package targeting a live Supabase test database. Seed script should insert: (a) 5000 activities distributed across 3 regions, 12 chapters each; (b) activity categories covering all Bufdir category codes. Test cases must include: correct region isolation, correct category mapping, performance under 3s, empty-scope edge case, leap-year boundary, and multi-chapter aggregation.
Capture timing with Dart Stopwatch and fail the test if any query exceeds the 3s SLA. Generate a structured test report (JSON or Markdown) as a CI artifact. Aim for 100% coverage of ExportDataQueryBuilder public methods.
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.