Implement org-scoped RLS filtering for activity queries
epic-bufdir-reporting-export-processing-services-task-003 — Add Row Level Security compatible filtering to the activity query service ensuring each organization can only access their own activity data. Implement org hierarchy resolution so coordinator-level queries aggregate data from all child chapters within their organizational scope without cross-org data leakage.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Implement org hierarchy resolution using a Supabase SQL function get_org_chapter_ids(org_id UUID) RETURNS TABLE(chapter_id UUID) that uses a recursive CTE to walk the chapter parent hierarchy. The RLS policy on activities can then reference this function: CREATE POLICY 'org_scoped_select' ON activities FOR SELECT USING (chapter_id IN (SELECT chapter_id FROM get_org_chapter_ids(get_user_org_id(auth.uid())))). This approach keeps all hierarchy logic in the database. The Dart service does not need to resolve the hierarchy itself — it simply passes the top-level orgId and lets RLS + the SQL function handle scoping.
Store the SQL functions and RLS policies in versioned Supabase migration files under supabase/migrations/ and never apply them ad hoc. For NHF specifically (1400 local chapters, 9 regions, 12 national associations), test the recursive CTE against deep hierarchies to ensure it does not hit the PostgreSQL recursion depth limit (default 100). If it does, restructure using a closure table pattern instead of adjacency list.
Testing Requirements
Integration tests against a Supabase staging instance are required for this task — unit tests with mocked clients cannot verify RLS. Test matrix: (1) peer mentor user queries activities → receives only own records, (2) coordinator queries → receives all chapter activities within their org, (3) org admin queries → receives all activities across all chapters in org, (4) user from org A queries with org B's orgId → receives empty list, (5) unauthenticated request → rejected with 401. Write these as Dart integration tests using a dedicated test Supabase project with seeded test data. Additionally, verify existing tests in the app still pass after RLS policy changes (regression suite).
Document the RLS policies in a SQL migration file with inline comments explaining the policy logic.
NHF contacts can belong to up to five local chapters simultaneously. If the deduplication logic in the activity query service incorrectly attributes cross-chapter activities, organisations will either under-report or over-report to Bufdir, which could trigger grant clawback or compliance investigations.
Mitigation & Contingency
Mitigation: Implement deduplication using the existing multi-chapter membership service as the source of truth for chapter affiliation. Write test fixtures covering all known multi-chapter edge cases and validate outputs against manually prepared reference exports from NHF.
Contingency: If deduplication cannot be made deterministic for complex hierarchies before release, gate the export behind an org-level feature flag and require NHF to validate a preview export against their manual Excel before enabling in production.
Server-side Dart libraries for Excel generation are less mature than equivalents in Node.js or Python. The chosen library may lack support for Bufdir-required formatting features (merged cells, data validation, specific date formats), requiring significant workaround effort or a library switch mid-implementation.
Mitigation & Contingency
Mitigation: Evaluate the top two Dart xlsx libraries (excel, spreadsheet_decoder) against a Bufdir template sample file before committing. Identify all required formatting features and verify library support in a spike.
Contingency: If no Dart library meets requirements, implement the Excel generation as a Supabase Edge Function in TypeScript using the well-supported ExcelJS library, exposing it to the Dart backend via an internal RPC call.
The attachment bundler must retrieve documents from Supabase Storage that were uploaded by the document attachments feature. If storage paths, RLS policies, or signed URL expiry have not been standardised across features, the bundler may fail to retrieve attachments at export time.
Mitigation & Contingency
Mitigation: Audit the document attachments feature's storage schema and RLS policies before implementing the bundler. Agree on a stable internal service-account access pattern for cross-feature storage reads.
Contingency: If cross-feature storage access cannot be made reliable, implement the bundler to include only attachments that can be retrieved successfully and produce a manifest listing any attachments that could not be bundled, rather than failing the entire export.