🏠 Overview › ✅ Implementation Tasks ›✅ epic-pause-status-notifications-backend-pipeline-task-006

Implement coordinator resolution in orchestrator

epic-pause-status-notifications-backend-pipeline-task-006 — Implement the coordinator resolution step within the Pause Notification Orchestrator. Query the database to find the coordinator responsible for the pausing mentor using the peer_mentor_profiles and coordinator_assignments tables. Handle cases where no coordinator is assigned and log resolution failures without crashing the pipeline.

critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

Given a valid peer_mentor_id, the orchestrator resolves the assigned coordinator's user_id and profile within 500ms.
When a coordinator is found, the resolved coordinator object includes user_id, display_name, and device_token lookup eligibility.
When no coordinator_assignment row exists for the mentor, the pipeline logs a structured warning (not an error) with mentor_id and organization_id, then continues without crashing.
When multiple coordinator assignments exist (e.g., temporary coverage), the resolution selects the primary active assignment (status = 'active', is_primary = true).
Resolution queries are scoped by organization_id derived from the TransitionEvent to enforce multi-tenant isolation.
All database queries use the Supabase service role client (never anon key) and respect RLS bypass only within the Edge Function environment.
Resolution failure is captured in a structured result object returned to the orchestrator, not thrown as an unhandled exception.
Logs include: resolution outcome (found/not_found), mentor_id, coordinator_id (if found), duration_ms, and organization_id.
Edge case: if coordinator account is deactivated, treat as not_found and log accordingly.
Resolution result is typed as a discriminated union: `{ found: true; coordinatorId: string; userId: string } | { found: false; reason: string }`.

Technical Requirements

frameworks
Supabase Edge Functions (Deno)
Supabase PostgreSQL 15
apis
Supabase PostgREST REST API
Supabase service role client
data models
assignment
contact
performance requirements
Coordinator resolution must complete within 500ms to stay within the 5-second end-to-end SLA.
Use a single JOIN query rather than sequential selects to minimize round-trips.
Index on coordinator_assignments(peer_mentor_id, status) must be confirmed present before deployment.
security requirements
Service role key used only server-side within Edge Function — never exposed to mobile clients.
Query scoped by organization_id from validated JWT claims to prevent cross-tenant data leakage.
No PII (names, contact details) included in log output — only UUIDs.
RLS is bypassed via service role; all tenant scoping must be enforced explicitly in query WHERE clauses.

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Implement as a pure async function `resolveCoordinator(supabase, mentorId, organizationId): Promise` — stateless, injectable, easily unit-testable. Use a single query with a JOIN: `SELECT ca.coordinator_user_id, p.display_name FROM coordinator_assignments ca JOIN profiles p ON p.user_id = ca.coordinator_user_id WHERE ca.peer_mentor_id = $mentorId AND ca.organization_id = $organizationId AND ca.status = 'active' AND ca.is_primary = true LIMIT 1`. Wrap in try/catch and map DB errors to the not_found union type with reason='db_error'. Avoid throwing — the orchestrator should receive a typed result and decide whether to continue or abort.

Log at WARN level (not ERROR) for not_found to avoid alert fatigue on legitimate unassigned mentors.

Testing Requirements

Unit tests: mock the Supabase client and verify (a) correct JOIN query construction, (b) correct handling of empty result set returning not_found discriminated union, (c) deactivated coordinator treated as not_found, (d) multi-assignment scenario selecting is_primary=true row. Integration tests: run against a test Supabase instance with seeded coordinator_assignments data, verify resolution succeeds with known mentor_id, verify not_found path with an unassigned mentor. Assert logs contain required structured fields. All tests must run in Deno test runner.

Component
Pause Notification Orchestrator
service medium
Dependencies (1)
Define the TypeScript interface for the Pause Notification Orchestrator service including input types (mentor ID, transition event, timestamp), output types (dispatch result, error details), and the coordinator resolution contract. Establish the service boundary before implementation begins. epic-pause-status-notifications-backend-pipeline-task-005
Epic Risks (3)
medium impact medium prob technical

Supabase Edge Functions have cold start latency that may push coordinator notification delivery beyond the 5-second SLA, particularly during low-traffic periods when the function is not warm.

Mitigation & Contingency

Mitigation: Keep the Edge Function lightweight — delegate all heavy logic to the orchestrator layer and avoid large dependency bundles. Measure p95 end-to-end latency in staging and document actual SLA achievable.

Contingency: If cold start latency consistently breaches 5 seconds, introduce a keep-warm ping from the nightly-scheduler or document the actual p95 latency in the feature spec and adjust the acceptance criterion to reflect the realistic bound.

medium impact medium prob technical

Supabase database webhooks may fire duplicate events for a single status change under retry conditions, causing coordinators to receive multiple identical notifications for one pause event.

Mitigation & Contingency

Mitigation: Add idempotency checking in the webhook handler using the event timestamp and peer mentor ID. Store a notification dispatch record in the pause-status-record-repository and skip dispatch if a record for the same event already exists.

Contingency: If duplicates slip through in production, add a de-duplication filter in the notification centre UI layer so the coordinator sees at most one card per event, and implement a cleanup job for the notifications table.

medium impact low prob scope

A peer mentor with multi-chapter membership may have more than one responsible coordinator. The orchestrator design currently targets a single coordinator, and resolving multiple recipients may require schema changes to the org membership query.

Mitigation & Contingency

Mitigation: Review the multi-chapter-membership-service patterns before implementing the orchestrator's coordinator resolution. Design the dispatcher call to accept an array of coordinator IDs from the outset so adding multiple recipients is non-breaking.

Contingency: If multi-coordinator dispatch is out of scope for this epic, document the limitation and create a follow-up task. Default to the primary coordinator (lowest chapter hierarchy level) as the single recipient in the interim.

Quick Links
All Tasks Execution Plan