Author RLS SQL migration scripts for unit-scoped tables
epic-organizational-hierarchy-management-core-services-task-011 — Write Supabase migration SQL files that enable Row Level Security on all organization-unit-scoped tables (organization_units, unit_assignments, activities, contacts, user_roles). Each table requires ENABLE ROW LEVEL SECURITY and initial USING policies that restrict read access to rows matching the user's unit membership, derived from JWT claims.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Use Supabase CLI migration workflow: supabase migration new {name} generates the timestamped file. Author policies in a single migration file per table for clarity, or one migration per feature (enabling RLS + initial policies). For the activities table, the USING clause should use EXISTS (SELECT 1 FROM unit_assignments ua WHERE ua.unit_id = activities.organization_unit_id AND ua.user_id = auth.uid()) — this is more reliable than JSON array contains checks for join-based relationships. For the jsonb array contains approach on simpler tables, use (auth.jwt() -> 'unit_ids') @> to_jsonb(organization_unit_id) — the @> operator is supported with GIN indexes.
Document the migration order explicitly: task-011 creates base policies → task-012 sets up JWT injection → task-013 adds role-differentiated write policies. Migrations must be checked into the repository under supabase/migrations/ and reviewed before applying to any environment.
Testing Requirements
SQL-level integration tests using Supabase's pgTAP or manual set_config('request.jwt.claims', ...) approach: (1) Assert that a user with unit_id=['unit-A'] cannot SELECT rows belonging to unit-B from each table. (2) Assert that a global admin JWT can SELECT all rows. (3) Assert that the activities join-based policy correctly filters activities by unit membership. (4) Run migrations against a local Supabase instance (supabase start) and execute EXPLAIN ANALYZE on representative SELECT queries to confirm index usage (no Seq Scan on large tables).
(5) Confirm rollback scripts restore the original state (no policies, RLS disabled). (6) Run supabase db lint on all migration files. These tests must be automated and runnable in CI via supabase db test.
Injecting all unit assignment IDs into JWT claims for users assigned to many units (up to 5 for NHF peer mentors, many more for national coordinators) may exceed JWT size limits, causing authentication failures.
Mitigation & Contingency
Mitigation: Store unit IDs in a Supabase session variable or a dedicated Postgres function rather than embedding them directly in the JWT payload. Use set_config('app.unit_ids', ...) within RLS helper functions querying the assignments table at policy evaluation time.
Contingency: Fall back to querying the unit_assignments table directly within RLS policies using the authenticated user ID, accepting a small per-query overhead in exchange for removing the JWT size constraint.
Rendering 1,400+ nodes in a recursive Flutter tree widget may cause jank or memory pressure on lower-end devices used by field peer mentors, degrading the admin experience.
Mitigation & Contingency
Mitigation: Implement lazy tree expansion — only the root level is rendered on initial load. Child nodes are rendered on demand when the parent is expanded. Use const constructors and ListView.builder for all node lists to minimize rebuild scope.
Contingency: Add a search/filter bar that scopes the visible tree to matching nodes, reducing the visible node count. Provide a 'flat list' fallback view for administrators who prefer searching over browsing the tree.
Requirements for what constitutes a valid hierarchy structure may expand during NHF sign-off (e.g., mandatory coordinator assignments per chapter, minimum member counts per region), requiring repeated validator redesign.
Mitigation & Contingency
Mitigation: Design the validator as a pluggable rule engine where each check is a discrete, independently testable function. New rules can be added without changing the core validation orchestration. Surface all rules in a configuration table per organization.
Contingency: Defer non-blocking validation rules to warning-level feedback rather than hard blocks, allowing structural changes to proceed while flagging potential issues for admin review.
Deploying RLS policy migrations to a shared Supabase project used by multiple organizations simultaneously could lock tables or interrupt active sessions, causing downtime during production migration.
Mitigation & Contingency
Mitigation: Write all RLS policies as CREATE POLICY IF NOT EXISTS statements. Schedule migrations during off-peak hours. Use Supabase's migration preview environment to validate policies against production data shapes before applying.
Contingency: Prepare rollback migration scripts for every RLS policy. If a migration causes issues, execute the rollback immediately and re-test the policy logic in staging before reattempting.