Implement coordinator relationship resolver
epic-pause-status-notifications-foundation-task-003 — Build the coordinator resolution logic within the repository that traverses the org membership table to find the assigned coordinator for a given peer mentor. Must handle multi-chapter affiliations and return a deterministic primary coordinator when multiple are found.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Query the org_memberships table with a filter on mentor_id and join to the coordinators/users table to retrieve the active coordinator per chapter in one query. Use `.from('org_memberships').select('chapter_id, is_primary, coordinator:coordinator_id(id, role, is_active)').eq('mentor_id', mentorId).eq('coordinator.is_active', true)`. After fetching all chapter relationships, apply the deterministic tiebreaker in Dart (not in SQL) for clarity and testability: (1) prefer rows where is_primary = true, (2) if tie, select the row with the lowest chapter_id (lexicographic) as a stable fallback. Document the chosen rule clearly.
This keeps the SQL simple and the business rule auditable in Dart code. Coordinate with the product team to confirm the tiebreaker rule before implementation — this is a business decision, not a technical one.
Testing Requirements
Unit tests with mocked Supabase responses: (1) Single-chapter mentor → correct coordinator returned. (2) Multi-chapter mentor (3 chapters) → deterministic primary coordinator returned, not a random one. (3) Multi-chapter mentor with explicit isPrimary=true chapter → that chapter's coordinator is always selected. (4) Mentor with chapters but no active coordinator in any chapter → CoordinatorNotFound failure returned.
(5) Empty membership list → CoordinatorNotFound failure returned. (6) Calling resolver twice with the same input returns the same result (determinism test). Integration test in staging: create a test mentor with 2-chapter membership and assert the same coordinator is returned on 10 consecutive calls.
The org membership table structure used to resolve coordinator relationships may differ from what the repository assumes, causing incorrect coordinator lookup or missing rows for mentors in multi-chapter scenarios.
Mitigation & Contingency
Mitigation: Review the existing org membership table schema and RLS policies before writing repository queries. Align query logic with the patterns already used by peer-mentor-status-repository and multi-chapter-membership-service.
Contingency: If schema differs, add an adapter layer in the repository that normalises the membership resolution and document the discrepancy for the data team. Fall back to coordinator lookup via the feature's own stored coordinator_id field if org membership join fails.
Device tokens stored in the database may be stale or unregistered, causing FCM dispatch failures that silently drop coordinator notifications — the primary coordination safeguard of this feature.
Mitigation & Contingency
Mitigation: Implement token validation on every dispatch call and handle FCM's NOT_REGISTERED error by flagging the token as invalid in the database. Reuse the token refresh pattern already established by fcm-token-manager.
Contingency: If push delivery fails after retry, ensure the in-app notification record is always written regardless of push outcome so coordinators can still see the event in the notification centre.
The optional reason field may contain special characters, emoji, or non-Latin scripts that exceed the 200-character byte limit when FCM encodes the payload, causing delivery failures.
Mitigation & Contingency
Mitigation: Enforce the 200-character limit on Unicode code point count, not byte count, in the payload builder. Add a unit test with multi-byte input strings.
Contingency: If an oversized payload is detected at dispatch time, strip the reason field from the push notification body and note 'See in-app notification for full reason' to preserve delivery.