critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

Abstract interface `NotificationRecordRepository` is extended with: `hasNotificationBeenSentForThreshold`, `recordThresholdNotificationSent`, and `getUnreadCount`
`hasNotificationBeenSentForThreshold(String recipientId, int thresholdDays)` returns `Future<bool>`: true if a record with matching recipient_id, threshold_days, and is_read=false/true already exists; false otherwise
`recordThresholdNotificationSent(String recipientId, int thresholdDays, String notificationType, String referenceId)` performs an atomic upsert — if the unique constraint (recipient_id, notification_type, threshold_days, reference_id) is violated, the operation succeeds silently (no exception) rather than crashing
`getUnreadCount(String recipientId)` returns `Future<int>` using a COUNT query filtered by recipient_id AND is_read=false; returns 0 (not null, not throws) when recipient has no unread notifications
Concurrent calls to `recordThresholdNotificationSent` with identical arguments do not create duplicate records — the unique constraint + upsert behavior guarantees idempotency
All three methods are added to the abstract interface and tested against the mock implementation
The Riverpod provider does not change — the concrete class is extended in place
getUnreadCount result is suitable for direct use as a Flutter badge count (int, ≥ 0)

Technical Requirements

frameworks
Flutter
Riverpod
supabase_flutter
apis
Supabase PostgREST (table: notification_records)
Supabase RPC (optional for atomic count query)
data models
NotificationRecord
notification_records (DB table with unique constraint)
performance requirements
hasNotificationBeenSentForThreshold must use a SELECT COUNT(*) or SELECT EXISTS query — not fetch full rows — to minimize payload
getUnreadCount must use a server-side COUNT aggregate, not client-side list.length
recordThresholdNotificationSent must be a single upsert DB call — no read-then-write pattern
security requirements
hasNotificationBeenSentForThreshold is intended for edge function use with service-role key — document this clearly
getUnreadCount for client use must rely on RLS to restrict count to the authenticated user's records only
Upsert must not allow a peer mentor to insert notification records for another recipient — enforce via RLS on INSERT

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

For `hasNotificationBeenSentForThreshold`, use: `.from('notification_records').select('id').eq('recipient_id', recipientId).eq('threshold_days', thresholdDays).eq('notification_type', notificationType).limit(1)` — checking list.isNotEmpty is more reliable than COUNT in the Supabase Dart client. For `recordThresholdNotificationSent`, use Supabase's `.upsert(payload, onConflict: 'recipient_id,notification_type,threshold_days,reference_id', ignoreDuplicates: true)` — the `ignoreDuplicates: true` flag means a conflict is silently ignored, achieving idempotent behavior safe for concurrent edge function invocations. For `getUnreadCount`, the Supabase Dart client supports `count: CountOption.exact` on a `.select()` call: `.from('notification_records').select('*', const FetchOptions(count: CountOption.exact)).eq('recipient_id', recipientId).eq('is_read', false)` — access the count via `response.count`. This avoids fetching full row data for a badge count.

Document the service-role vs. authenticated-client usage differences in code comments at the top of the concrete class.

Testing Requirements

Unit tests with mocked Supabase client: (1) hasNotificationBeenSentForThreshold returns true when mock returns count > 0; (2) returns false when mock returns count = 0; (3) recordThresholdNotificationSent calls upsert with ignoreDuplicates=true or equivalent and does not throw on conflict; (4) getUnreadCount returns 0 when mock returns empty result; (5) getUnreadCount returns correct integer from count response. Integration test against local Supabase: (1) call recordThresholdNotificationSent twice with same args — confirm only one record exists in DB; (2) confirm hasNotificationBeenSentForThreshold returns true after first call; (3) mark the record as read and confirm getUnreadCount decrements. Test concurrent calls by calling recordThresholdNotificationSent from two Future.wait parallel calls and assert single DB record.

Component
Notification Record Repository
data medium
Epic Risks (3)
high impact medium prob technical

The RLS policy predicate that checks certification_expiry_date and suppression_status on every coordinator list query could cause full table scans at scale, degrading response time for coordinator contact list screens across all chapters.

Mitigation & Contingency

Mitigation: Add a partial index on (certification_expiry_date, suppression_status) filtered to active mentors. Benchmark the policy predicate against a representative data set (500+ mentors) during development using EXPLAIN ANALYZE on Supabase staging.

Contingency: If the index does not resolve the performance issue, introduce a computed boolean column is_publicly_visible that is updated by the mentor_visibility_suppressor service and indexed separately, shifting the predicate cost to write time rather than read time.

medium impact medium prob integration

FCM device tokens become invalid when users reinstall the app or switch devices. If the token management strategy does not handle token refresh reliably, notification delivery will silently fail for a significant portion of the user base without surfacing errors.

Mitigation & Contingency

Mitigation: Implement the FCM token refresh callback in the Flutter client to upsert the latest token to Supabase on every app launch. Store token with a last_refreshed_at timestamp. The FCM sender should handle UNREGISTERED error codes by deleting stale tokens.

Contingency: If token staleness becomes widespread, add a token health check that forces re-registration during the expiry check edge function run by querying mentors whose token was last refreshed more than 30 days ago and triggering a silent push to prompt re-registration.

medium impact low prob integration

The certification expiry and notification record tables may have column naming or constraint conflicts with existing tables in the peer mentor status and certification management features, causing migration failures in shared Supabase environments.

Mitigation & Contingency

Mitigation: Audit existing table schemas for user_roles, certifications, and notification tables before writing migrations. Prefix new columns with expiry_ to avoid collisions. Run migrations against a clean Supabase branch environment before merging.

Contingency: If a conflict is found post-merge, apply ALTER TABLE migrations to rename conflicting columns and issue a hotfix migration. Communicate schema changes to all dependent feature teams via a shared migration changelog.