Implement AssignmentContactTrackingRepository
epic-assignment-follow-up-reminders-foundation-task-006 — Implement the AssignmentContactTrackingRepository Dart class that queries the assignments table for last_contact_date data. Expose methods: updateLastContactDate(assignmentId, date), fetchOverdueAssignments(orgId, thresholdDays), fetchAssignmentContactSummary(assignmentId). Queries must include org_id scope and leverage the compound index created in the migration task.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
The PostgREST OR filter for 'overdue OR null' requires careful syntax: `.or('last_contact_date.lte.${threshold.toIso8601String()},last_contact_date.is.null')` — test this explicitly as PostgREST OR with IS NULL is a common gotcha. Define two domain models: `OverdueAssignment` (contains assignmentId, peerId, orgId, lastContactDate, daysSinceContact) and `AssignmentContactSummary` (same fields plus a label/status field). Compute daysSinceContact in Dart, not in SQL, to keep the repository logic simple and testable. For updateLastContactDate, use `.update({'last_contact_date': date.toIso8601String()}).eq('id', assignmentId).select()` — the `.select()` after `.update()` returns the updated row, allowing immediate construction of AssignmentContactSummary without a second round-trip.
thresholdDays in fetchOverdueAssignments should come from ReminderConfigRepository — the calling BLoC/use case should fetch config first and pass thresholdDays in. Do not fetch config inside this repository (separation of concerns). Consider adding a `watchOverdueCount(String orgId, int thresholdDays)` Stream method in a follow-up task for live coordinator dashboard badge updates.
Testing Requirements
Unit tests with mocked SupabaseClient (flutter_test): (1) updateLastContactDate: mock returns updated row, assert AssignmentContactSummary with correct lastContactDate; (2) updateLastContactDate: mock returns empty (0 rows), assert AssignmentNotFoundException; (3) fetchOverdueAssignments with thresholdDays=10: assert query filters by org_id AND (last_contact_date <= threshold OR last_contact_date IS NULL); (4) fetchOverdueAssignments returns empty list when mock returns []; (5) fetchOverdueAssignments returns correct OverdueAssignment list with daysSinceContact computed correctly; (6) fetchAssignmentContactSummary: mock returns row with null last_contact_date, assert daysSinceContact is null; (7) fetchAssignmentContactSummary: mock returns row with last_contact_date 5 days ago, assert daysSinceContact=5; (8) fetchAssignmentContactSummary: mock returns empty, assert AssignmentNotFoundException; (9) Riverpod Provider resolves in ProviderContainer. Integration test against test Supabase project: assert overdue query returns only assignments older than threshold, never contacts from other orgs.
Adding last_contact_date to the assignments table may conflict with existing RLS policies or trigger-based logic that monitors the assignments table. If the migration is not carefully reviewed, existing assignment management features could break in production.
Mitigation & Contingency
Mitigation: Review all existing triggers, policies, and foreign key constraints on the assignments table before writing the migration. Run the migration against a staging Supabase instance with production-like data and execute the full existing test suite before merging.
Contingency: Roll back the migration using Supabase's versioned migration history. Apply the schema change as an additive-only migration (nullable column with default) to ensure zero downtime and reversibility.
The PushNotificationService wraps an existing FCM integration whose internal API contract may have changed or may not expose the payload formatting required for deep-link CTAs. Misalignment discovered late delays the dispatch service epic.
Mitigation & Contingency
Mitigation: Before implementing the wrapper, read the existing push notification integration code and confirm the method signatures, payload structure, and token management model. Agree on a stable interface contract in a shared Dart abstract class.
Contingency: If the existing service is incompatible, implement a thin adapter layer that translates reminder payloads to the existing service's format, isolating the reminder feature from upstream changes.
Incorrect RLS policies on notification_log could allow coordinators to read reminder records belonging to peer mentors in other chapters, exposing sensitive assignment information across organisational boundaries.
Mitigation & Contingency
Mitigation: Write explicit RLS policies with integration tests that assert cross-chapter queries return zero rows. Use Supabase's built-in auth.uid() and join through the org membership tables to scope all queries.
Contingency: If a policy gap is discovered post-merge, immediately disable the affected table's SELECT policy, deploy a corrected policy, and audit recent queries in Supabase logs for any cross-boundary reads.