🏠 Overview › ✅ Implementation Tasks ›✅ epic-bufdir-data-aggregation-foundation-task-003

Implement Multi-Org Data Isolator Dart Service

epic-bufdir-data-aggregation-foundation-task-003 — 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.

critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

MultiOrgDataIsolator sets app.current_org_id via a Supabase session variable on every query invocation before execution proceeds
Any attempt to execute a query without an active org context throws an OrgContextMissingException with a descriptive message
Org-scoped query builder wraps the Supabase query builder and injects org context transparently — callers do not manually set org_id
Riverpod provider for MultiOrgDataIsolator depends on TenantContextService and rebuilds when active org changes
All repositories consuming MultiOrgDataIsolator automatically receive the correct org context without additional configuration
Switching active org (via TenantContextService) propagates to all in-flight and subsequent queries within 1 request cycle
Unit tests confirm that queries are blocked when org context is null
Integration test confirms that two simultaneous org contexts in separate isolates do not cross-contaminate
No raw SupabaseClient references exist in repository layer — all queries go through MultiOrgDataIsolator

Technical Requirements

frameworks
Flutter
Riverpod
Supabase Dart SDK
apis
Supabase REST API
Supabase RPC (set_config / app.current_org_id session variable)
data models
TenantContext
OrgContextMissingException
OrgScopedQueryBuilder
performance requirements
Session variable injection must add less than 5ms overhead per query
Riverpod provider must not trigger unnecessary rebuilds when org_id is unchanged
OrgScopedQueryBuilder must be stateless and safe for concurrent use
security requirements
app.current_org_id must be set via parameterized Supabase set_config to prevent SQL injection
OrgContextMissingException must never leak internal org_id values in its message
Provider must clear org context immediately when user logs out or switches org
RLS policies on the Supabase side must remain the authoritative guard; this service is a defense-in-depth layer

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Use Supabase's ability to set session-level configuration via `SELECT set_config('app.current_org_id', $1, true)` before each query. Wrap SupabaseClient in an OrgScopedQueryBuilder that executes the set_config call in the same transaction/request. In Riverpod, use a `Provider` (not `FutureProvider`) for synchronous access — the active org_id from TenantContextService should already be resolved before any repository is accessed. Guard all public query methods with an `assert(currentOrgId != null)` in debug mode and a hard throw in release mode.

Consider using Dart's `Zone` or a middleware pattern if Supabase SDK supports interceptors, to avoid repetitive boilerplate in each repository method. Document clearly that this class is NOT a replacement for Supabase RLS — it is a client-side enforcement layer for developer ergonomics and early failure detection.

Testing Requirements

Unit tests (flutter_test): (1) test that OrgContextMissingException is thrown when no org is active, (2) test that query builder correctly prepends set_config call, (3) test Riverpod provider rebuilds on org change and not on unrelated state changes. Integration tests: spin up a Supabase test instance, verify that queries from org A cannot retrieve rows belonging to org B even if the Dart-side guard is bypassed (RLS verification). Mock TenantContextService to simulate rapid org switching and confirm no race conditions. Target 90%+ line coverage on MultiOrgDataIsolator class.

Component
Multi-Organization Data Isolator
data medium
Dependencies (1)
Implement the PostgreSQL migration file that creates and enables Row Level Security policies on all relevant tables (activities, user_stories, components, etc.). Include USING and WITH CHECK clauses tied to app.current_org_id, grant policies per role (peer_mentor, coordinator, admin), and include a rollback strategy. Validate that no SELECT, INSERT, UPDATE, or DELETE can cross organization boundaries. epic-bufdir-data-aggregation-foundation-task-002
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