Design contact_chapters database migration
epic-multi-chapter-membership-handling-foundation-task-001 — Create a Supabase database migration that introduces the contact_chapters junction table with composite primary key (contact_id, chapter_id), foreign key constraints to contacts and chapters tables, required indexes for query performance, and CHECK constraints. Include created_at and updated_at timestamps and any additional metadata columns needed by the domain model.
Acceptance Criteria
Technical Requirements
Implementation Notes
The CHECK constraint for the 5-chapter maximum is best implemented as a constraint trigger or a deferred constraint rather than a simple column CHECK, because the limit applies across multiple rows for the same `contact_id`. A plain `CHECK` on a single row cannot count sibling rows. Use a `BEFORE INSERT` trigger function that counts existing rows for the incoming `contact_id` and raises an exception if `count >= 5`. Alternatively, use a PostgreSQL partial unique index approach with a counted view, but the trigger approach is cleaner for this use case.
Name the trigger `trg_contact_chapters_max_five` for discoverability. Ensure the `updated_at` column is kept current via a standard `moddatetime` trigger or a `BEFORE UPDATE` trigger calling `NEW.updated_at = now()`.
Testing Requirements
After applying the migration to a local Supabase instance, verify with `psql` or the Supabase Studio table editor: (1) table exists with correct column types; (2) inserting 5 rows for the same contact_id succeeds; (3) inserting a 6th row for the same contact_id fails with a CHECK constraint violation; (4) deleting a row from `contacts` cascades to `contact_chapters`; (5) EXPLAIN ANALYZE on `SELECT * FROM contact_chapters WHERE contact_id = $1` shows an index scan. Document these verification steps as a migration test checklist in the PR description.
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.