Implement SupabaseContactChapterAdapter core CRUD

epic-multi-chapter-membership-handling-foundation-task-004 — Build the SupabaseContactChapterAdapter class with methods for inserting a new contact-chapter membership, deleting a membership, fetching all chapters for a given contact ID, and fetching all contacts for a given chapter ID. Use the Supabase Dart client's PostgREST interface. Handle error cases (network failure, constraint violations) and wrap responses using the domain mapper from task-003.

critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

A `SupabaseContactChapterAdapter` class is implemented in the data layer (e.g., `lib/data/adapters/supabase_contact_chapter_adapter.dart`)

`insertMembership(String contactId, String chapterId, {String? roleInChapter})` inserts a row into `contact_chapters` and returns the created `ContactChapter` domain object

`deleteMembership(String contactId, String chapterId)` deletes the matching row; returns `void` on success

`fetchChaptersForContact(String contactId)` returns `List<ContactChapter>` for all chapter memberships of the given contact

`fetchContactsForChapter(String chapterId)` returns `List<ContactChapter>` for all contacts in the given chapter

All methods throw a typed domain exception (e.g., `ChapterMembershipLimitExceededException`) when the Supabase response contains a constraint violation error code (`23514` for CHECK, `23505` for unique)

Network failures (e.g., `SocketException`, Supabase client timeout) are caught and rethrown as a `NetworkException` domain type — raw Supabase exceptions do not leak to callers

All methods are `async` and return `Future<T>`

The adapter does not hardcode any organisation ID — it relies on the authenticated Supabase client whose JWT carries the org context for RLS enforcement

An abstract interface `IContactChapterAdapter` (or equivalent) is defined and `SupabaseContactChapterAdapter` implements it, enabling mock injection in tests

Technical Requirements

frameworks

Flutter

Dart

Supabase

apis

Supabase PostgREST (insert, delete, select with eq filter)

Supabase PostgreSQL 15

data models

contact_chapter

contact

performance requirements

`fetchChaptersForContact` and `fetchContactsForChapter` must each issue exactly one PostgREST query — no sequential per-row fetches

Select queries must specify explicit column lists rather than `*` to reduce payload size

security requirements

The Supabase client instance used must be the authenticated client (user JWT applied) — never the service role client in the Flutter app

Input `contactId` and `chapterId` values must be validated as non-null, non-empty strings before constructing the PostgREST query to prevent empty-filter queries that would match all rows

Error messages surfaced from Supabase exceptions must be sanitised before being included in domain exceptions — do not propagate raw SQL error details to the UI layer

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Follow the PostgREST Dart client pattern: `supabase.from('contact_chapters').insert(payload).select().single()` for insert-and-return. For delete: `supabase.from('contact_chapters').delete().eq('contact_id', contactId).eq('chapter_id', chapterId)`. Wrap all PostgREST calls in try/catch for `PostgrestException` — inspect `error.code` to distinguish constraint violations (domain errors) from infrastructure errors. Define a private `_handlePostgrestException(PostgrestException e)` method to centralise this logic and keep individual method bodies clean.

For the abstract interface, keep it minimal — only the four public methods — so that test mocks remain easy to implement. Document the 5-membership limit in a code comment referencing the business rule source (NHF requirement from the workshop).

Testing Requirements

Unit tests using a mock `SupabaseClient` (via `mocktail` or `mockito`): (1) `insertMembership` — mock returns a valid JSON row, assert returned `ContactChapter` matches expected values; (2) `insertMembership` — mock throws PostgrestException with code `23514`, assert `ChapterMembershipLimitExceededException` is thrown; (3) `deleteMembership` — mock returns empty response, assert method completes without error; (4) `fetchChaptersForContact` — mock returns list of 3 JSON rows, assert list of 3 `ContactChapter` objects returned; (5) network failure path — mock throws `SocketException`, assert `NetworkException` thrown. Integration tests against local Supabase: seed test data, execute each method, assert correct DB state after each operation, clean up. All tests in `test/data/adapters/supabase_contact_chapter_adapter_test.dart`.

Component

Supabase Contact Chapter Adapter

infrastructure medium

Dependencies (2)

Create the ContactChapter Dart domain class with all required fields (contactId, chapterId, createdAt, etc.). Implement a bidirectional mapper that converts between ContactChapter domain objects and raw Supabase PostgREST JSON response maps. Ensure null-safety and proper type coercion for all fields. epic-multi-chapter-membership-handling-foundation-task-003 Define and apply Row Level Security policies on the contact_chapters table to enforce multi-organisation data isolation. Policies must restrict read and write access so that coordinators can only query memberships within their own organisation scope, and peer mentors can only read their own chapter affiliations. Validate policies against the existing RLS pattern used elsewhere in the Supabase project. epic-multi-chapter-membership-handling-foundation-task-002

Epic Risks (2)

high impact medium prob security

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.

medium impact medium prob technical

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.

Quick Links

All Tasks Execution Plan