Add affiliation count enforcement and RLS alignment
epic-multi-chapter-membership-handling-data-layer-task-003 — Ensure the 5-affiliation cap is enforced both at the repository layer (Dart) and via a Supabase check constraint or trigger on the contact_chapters table. Verify that Row Level Security policies on contact_chapters allow the authenticated coordinator to read and write only within their chapter scope. Write a Supabase migration file for the constraint if it does not already exist.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Use a PostgreSQL trigger function rather than a plain CHECK constraint if the count needs to span multiple rows — CHECK constraints cannot reference aggregate counts across rows. The trigger pattern: BEFORE INSERT ON contact_chapters, count existing rows for NEW.contact_id, raise exception if count >= 5. In the Dart repository, wrap the Supabase insert call in a try/catch for PostgrestException; inspect the code field ('P0001' for RAISE EXCEPTION from trigger, '23514' for CHECK). Map these to AffiliationLimitExceededException so BLoC can present a user-friendly message.
For RLS, create two policies: one FOR SELECT using EXISTS (SELECT 1 FROM coordinator_chapter_assignments WHERE user_id = auth.uid() AND chapter_id = contact_chapters.chapter_id), and an identical one FOR ALL (INSERT/UPDATE/DELETE). Add a separate USING (true) policy for the service_role. Test locally with supabase db reset and supabase db push before committing the migration.
Testing Requirements
Write unit tests in flutter_test for the Dart repository layer: (1) mock Supabase client returning a PostgrestException with code '23514' (CHECK violation) or '23505' and assert that the repository throws AffiliationLimitExceededException; (2) mock a successful insert for counts 1–5 and assert no exception. Write integration tests against a local Supabase instance (via supabase_test or Docker): verify the constraint fires on the 6th insert; verify a coordinator JWT can read only their chapter's rows and receives an empty result set for other chapters; verify an org admin JWT reads all rows. Test idempotency by running the migration SQL twice in CI.
The Cross-Chapter Activity Query must avoid N+1 fetches across chapters. If naively implemented as a per-chapter loop, it will cause severe performance degradation for contacts affiliated with 5 chapters on poor mobile connections.
Mitigation & Contingency
Mitigation: Design the query as a single PostgREST join of contact_chapters and activities on contact_id from the start. Add a query performance test with 5 affiliations and 100+ activities to the integration test suite and enforce a maximum execution time threshold.
Contingency: If a performance regression is detected post-merge, introduce a Supabase RPC function (stored procedure) to move the join server-side, bypassing any client-side N+1 pattern.
If the Duplicate Warning Event Logger write fails silently (network error, RLS denial), audit entries will be missing from the Bufdir compliance record without the user being aware.
Mitigation & Contingency
Mitigation: Implement the logger with a local fallback queue: if the Supabase write fails, persist the event locally and retry on next launch. Log all failures to a verbose output channel.
Contingency: Add a reconciliation job that compares locally queued events to Supabase entries and re-submits any gaps. Provide a data export of the local queue for manual audit if reconciliation fails.
Two coordinators simultaneously adding the 5th chapter affiliation for the same contact could bypass the maximum enforcement check if both reads occur before either write completes.
Mitigation & Contingency
Mitigation: Enforce the 5-affiliation maximum as a database-level constraint (CHECK + trigger or RPC with a FOR UPDATE lock) rather than relying solely on application-layer validation.
Contingency: If a constraint violation is detected in production, run a corrective query to end the most recently created excess affiliation and notify the relevant coordinator.