Implement InAppNotificationRepository
epic-assignment-follow-up-reminders-foundation-task-005 — Implement the InAppNotificationRepository Dart class backed by the notification_log table. Expose methods: insertNotification(record), markAsRead(notificationId), fetchUnreadForUser(userId, orgId), fetchByAssignment(assignmentId), and deleteOlderThan(cutoff). Use Riverpod provider for injection. Ensure all queries filter by org_id for data isolation.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Define NotificationRecord with channel as an enum: `enum NotificationChannel { push, inApp }` and NotificationStatus as `enum NotificationStatus { sent, delivered, read, failed }`. Map these from/to string in fromJson/toJson. For watchUnreadCountForUser, use Supabase Realtime: `supabase.from('notification_log').stream(primaryKey: ['notification_id']).eq('org_id', orgId).eq('recipient_user_id', userId).execute()` — then `.map((rows) => rows.where((r) => r['status'] != 'read').length)`. Note that Supabase Realtime streams entire table snapshots by default; for production scale, consider using a Postgres function + RPC to return just the count.
The insertNotification method in the Flutter repository is only used in tests or admin flows — in production, notifications are inserted by an Edge Function using the service role. Document this constraint clearly with a `// Note: In production, use Edge Function for insertion` comment. deleteOlderThan is a housekeeping method — suggest calling it from a Supabase Edge Function cron rather than from the Flutter app to ensure it runs even when the app is not open.
Testing Requirements
Unit tests with mocked SupabaseClient (flutter_test + mockito): (1) insertNotification: mock returns a row, assert NotificationRecord has correct fields; (2) insertNotification: mock throws PostgrestException, assert NotificationNetworkException is thrown; (3) markAsRead: mock returns 1 updated row, assert true returned; (4) markAsRead: mock returns 0 updated rows, assert false returned; (5) fetchUnreadForUser: mock returns list of rows, assert correct NotificationRecord list with proper mapping; (6) fetchUnreadForUser: mock returns empty list, assert empty list (not exception); (7) fetchByAssignment: assert correct filter parameters are passed to Supabase client; (8) deleteOlderThan: mock returns count=3, assert method returns 3; (9) watchUnreadCountForUser: mock Realtime stream, assert Stream
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.