Write Integration Tests for RLS and RPC Pipeline

epic-bufdir-data-aggregation-foundation-task-009 — Write integration tests that verify the complete multi-tenant isolation and aggregation pipeline. Tests must confirm: (1) org A cannot read org B data under RLS, (2) generate_bufdir_report returns correct COUNT DISTINCT participant counts with proxy deduplication, (3) category mapping resolves correctly for active mapping version, (4) the Dart SupabaseAggregationRpc client parses all response shapes correctly. Use a test Supabase instance with seeded data for NHF, HLF, and Blindeforbundet scenarios.

critical priority high complexity testing pending testing specialist Tier 5

Acceptance Criteria

RLS isolation test: authenticated session for org NHF receives zero rows when querying org HLF participant data — verified with seeded cross-org rows

RLS isolation test: authenticated session for org HLF receives zero rows when querying org Blindeforbundet data

RLS isolation test: authenticated session for org Blindeforbundet receives zero rows when querying org NHF data

RPC test: generate_bufdir_report called for NHF returns participant_count equal to COUNT DISTINCT of participant_id, not raw row count

RPC test: generate_bufdir_report correctly deduplicates proxy-registered participants (is_proxy_registered=true) so they are counted once regardless of number of activity entries

RPC test: generate_bufdir_report returns category breakdowns matching the active bufdir_category_mappings version — not a deprecated version

RPC test: when two mapping versions exist, only the version with is_active=true is used for category resolution

Dart client test: SupabaseAggregationRpc.parseResponse() correctly deserialises all known RPC response shapes including empty results, single-org results, and multi-category results

Dart client test: SupabaseAggregationRpc.parseResponse() throws a typed AggregationParseException when the RPC returns an unexpected schema

All tests run against an isolated test Supabase instance — no shared state with staging or production

Seed data covers all four organisation tenants: NHF, HLF, Blindeforbundet, and Barnekreftforeningen

Test suite completes within 60 seconds in CI

All tests are tagged with group('bufdir_rls') and group('bufdir_rpc') for selective execution

Technical Requirements

frameworks

flutter_test

supabase_flutter

bloc

apis

Supabase RPC generate_bufdir_report

Supabase REST API for RLS verification

Supabase Auth API for multi-tenant session setup

data models

participants

activities

bufdir_category_mappings

organisations

aggregation_report

performance requirements

Full integration test suite must complete in under 60 seconds

Each individual test must complete in under 5 seconds

Seed data setup and teardown must complete in under 10 seconds per test group

security requirements

Test Supabase credentials must be stored in environment variables, never hardcoded

Test instance must be isolated from staging and production

Service role key used only for seeding — all RLS tests must use organisation-scoped JWTs

Test data must not contain real personal data — use synthetic Norwegian-style names and IDs

Execution Context

Execution Tier

Tier 5

Tier 5 - 253 tasks

Can start after Tier 4 completes

View Full Execution Plan

Implementation Notes

Use Supabase's anon key with org-scoped JWTs to simulate per-org authenticated sessions — do not use the service role key for RLS tests as it bypasses RLS entirely. Create a test helper that mints a JWT with the correct org_id claim matching the RLS policy. Seed at minimum 3 participants per org with overlapping activity dates to verify deduplication is working. For the is_proxy_registered deduplication test, seed one participant with is_proxy_registered=true who has 5 activity rows — assert the RPC returns participant_count=1 for that participant.

For category mapping version tests, insert two mapping rows for the same source category: one with is_active=false and one with is_active=true — assert only the active mapping's target category appears in results. Wrap all seeding and teardown in try/finally blocks to prevent test pollution if a test throws unexpectedly.

Testing Requirements

Integration tests only — no unit mocks for Supabase. Use a dedicated test Supabase project. Seed data via the service role key in setUpAll(), tear down in tearDownAll(). Group tests into: (1) rls_isolation — cross-org read attempts, (2) rpc_correctness — participant count accuracy and deduplication, (3) category_mapping — active version resolution, (4) dart_client_parsing — all RPC response shape permutations.

Achieve 100% coverage of the SupabaseAggregationRpc public interface. Run in CI on every PR targeting the main branch.

Component

Supabase Aggregation RPC Functions

infrastructure high

Dependencies (3)

Implement the ActivityCategoryMappingConfig Dart class that reads the bufdir_category_mappings table, exposes a lookup method (getBuffirCode(internalTypeId, mappingVersion)) and caches results in memory. Include a version negotiation method that resolves which mapping version applies to a given reporting period. Provide a Riverpod provider for injection into aggregation services. epic-bufdir-data-aggregation-foundation-task-006 Implement the MultiOrgDataIsolator Dart class that sets the app.current_org_id session variable on every Supabase request, exposes an org-scoped query builder, and enforces that no query can execute without an active org context. Integrate with Riverpod so the active org context flows from TenantContextService into every repository call automatically. epic-bufdir-data-aggregation-foundation-task-003 Implement the SupabaseAggregationRpc Dart class that wraps the generate_bufdir_report Supabase RPC call with typed parameters and result parsing. Parse the JSONB response into strongly-typed BufdirReportResult models. Include error handling for RPC failures, timeout management for large datasets, and streaming progress callbacks. Expose as a Riverpod provider for use by higher-level aggregation services. epic-bufdir-data-aggregation-foundation-task-008

Epic Risks (3)

high impact medium prob security

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.

high impact medium prob dependency

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.

high impact low prob technical

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.

Quick Links

All Tasks Execution Plan