Add RLS-aware query filtering to adapter
epic-multi-chapter-membership-handling-foundation-task-005 — Extend the adapter to include organisation-scoped query filters that complement the RLS policies, providing defence-in-depth. Implement a method that accepts an organisation context parameter and appends the appropriate PostgREST filter clauses. Ensure queries remain efficient by leveraging the indexes created in the migration step.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Use the Supabase Dart client's fluent PostgREST builder (`.from('contact_chapters').select().eq('organisation_id', context.organisationId)`) — never build filter strings manually. Introduce an `OrganisationContext` value object (immutable Dart class with `organisationId` and optionally `userId`) to make the parameter contract explicit and type-safe. Pattern: create a private `_baseQuery(OrganisationContext ctx)` method that returns a pre-filtered QueryBuilder, then all public methods call `_baseQuery(ctx)` before adding further filters. This keeps filter logic in one place.
Avoid over-engineering: the context object does not need to hold the JWT itself, only the already-validated org ID. Guard the method with an `assert(ctx.isValid)` in debug mode. Confirm with the Supabase dashboard query analyser that the index is being used after inserting sufficient test data (the query planner only uses indexes when selectivity is high enough).
Testing Requirements
Unit tests (flutter_test): mock the Supabase PostgREST client and assert that every adapter method appends the correct `.eq('organisation_id', ...)` clause when given a valid OrganisationContext, and throws ArgumentError for null/invalid context. Integration tests: run against a local Supabase instance (or Supabase CLI) seeded with two organisations and shared contact IDs; verify that queries for org A never return rows from org B. Performance smoke test: insert 500 contact_chapter rows across two orgs and assert query latency is within threshold. Cover edge cases: contact with exactly 5 chapters, contact with 0 chapters, deactivated organisation.
Supabase RLS policies for a junction table that spans organisations are non-trivial. An incorrectly scoped policy could expose chapter affiliations from other organisations to coordinators, constituting a data breach.
Mitigation & Contingency
Mitigation: Draft RLS policies in a staging environment and run an explicit cross-organisation isolation test suite before merging. Use Supabase policy testing tools and peer review all policy definitions.
Contingency: If a policy error reaches review, roll back the migration and apply a corrective patch. Ensure no production data has been exposed by auditing Supabase logs for cross-organisation query results.
The contact_chapters table migration may conflict with existing foreign key structures or require a backfill for contacts already assigned to a single chapter, causing migration failures in production.
Mitigation & Contingency
Mitigation: Write the migration as an additive, non-destructive operation. Backfill existing single-chapter assignments by deriving them from the existing contact records. Test the full migration on a production-sized dataset clone before release.
Contingency: Provide a rollback migration script that removes the new table without touching existing contact records. Coordinate with operations for a maintenance window if a re-run is needed.