Create bufdir_category_mappings Table Migration
epic-bufdir-data-aggregation-foundation-task-004 — Write the PostgreSQL migration that creates the bufdir_category_mappings table. The table maps internal activity type IDs to Bufdir's official taxonomy codes with versioning support (mapping_version column), effective_from/to date range, and a unique constraint on (internal_type_id, mapping_version). Include seed data for all currently known activity types used by NHF, Blindeforbundet, HLF, and Barnekreftforeningen.
Acceptance Criteria
Technical Requirements
Implementation Notes
Coordinate with the product team or Bufdir documentation to confirm the complete list of official bufdir_codes before writing seed data — incorrect codes will cause Bufdir submission rejections. Use `ON CONFLICT DO NOTHING` in the seed INSERT statements to make seeding idempotent. If the Bufdir taxonomy is expected to evolve, consider a CHECK constraint that can be updated rather than an ENUM type (which requires DDL changes to extend). effective_to = NULL means 'currently active'.
The mapping_version column should be a simple incrementing integer per org/taxonomy cycle, not a semver string, to keep comparisons simple in the RPC function. Document each seed row with a comment explaining which org uses that activity type.
Testing Requirements
SQL-level tests: (1) verify UNIQUE constraint rejects duplicate (internal_type_id, mapping_version) pairs, (2) verify RLS blocks non-service_role writes, (3) verify seed data is complete and all bufdir_codes are non-null. Run `supabase db reset` in CI to confirm migration applies cleanly from scratch. Verify rollback migration restores the database to pre-migration state. Test that a JOIN between activities and bufdir_category_mappings on internal_type_id returns the correct bufdir_code for each org's activity types.
Supabase RLS policies may not propagate correctly into RPC function execution context, causing org-scoping predicates to be silently ignored when the function is invoked with service_role key. This could lead to cross-org data exposure in production without any obvious error.
Mitigation & Contingency
Mitigation: Invoke all RPCs using the anon/authenticated key rather than service_role, write explicit WHERE org_id = auth.uid()::org_id predicates inside the RPC body as a secondary control, and include automated cross-org leakage tests in the CI pipeline from day one.
Contingency: If RLS bypass is discovered post-deployment, immediately revoke service_role usage in all aggregation paths and hotfix with explicit org_id parameters passed as function arguments validated server-side.
Bufdir may update its official reporting category taxonomy between the mapping configuration being defined and the annual submission deadline. If the ActivityCategoryMappingConfig is compiled as a static Dart constant, it cannot be updated without an app release, potentially causing mapping failures that block submission.
Mitigation & Contingency
Mitigation: Store the mapping as a remote-configurable table (bufdir_category_mappings) in Supabase with a version field rather than as a hardcoded Dart constant. Fetch the current mapping at aggregation time so updates can be pushed without a new app release.
Contingency: If a mapping mismatch is detected during an active reporting cycle, coordinators can be temporarily directed to the manual Excel fallback while an emergency mapping update is pushed to the Supabase table.
For large organisations like NHF with 1,400 local chapters and potentially tens of thousands of activity records per reporting period, the Supabase RPC aggregation query may exceed the default PostgREST statement timeout, causing the aggregation to fail with a 503 error.
Mitigation & Contingency
Mitigation: Add partial indexes on (organization_id, created_at) and (organization_id, activity_type_id) to the activities table before writing the RPC. Profile the query plan against a realistic fixture of 50,000 records during development and increase the statement_timeout setting for the RPC role if needed.
Contingency: Implement chunked aggregation fallback: split the period into monthly sub-ranges and aggregate each chunk client-side, merging results with UNION-style Dart logic before assembling the final payload.