Implement CertificationExpiryRepository base class

epic-certificate-expiry-notifications-data-foundation-task-003 — Create the Dart repository class CertificationExpiryRepository with its abstract interface. Implement Supabase client integration, basic CRUD operations, and the foundational query infrastructure. Establish the data model classes (CertificationExpiry, CertificationExpiryRecord) with fromJson/toJson serialization. Wire up the Riverpod provider for dependency injection.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

Abstract class `CertificationExpiryRepository` defines the full interface contract (all methods are abstract)

Concrete class `SupabaseCertificationExpiryRepository` implements the interface using the Supabase Dart client

Data model class `CertificationExpiryRecord` is a Dart `final class` (or `@immutable`) with fields matching the DB schema: id, peerId, certificateType, expiryDate, lastNotifiedAt, notificationThresholdsSent

`CertificationExpiryRecord.fromJson(Map<String, dynamic>)` correctly parses all fields including the int[] array and nullable timestamptz

`CertificationExpiryRecord.toJson()` produces a map compatible with the Supabase insert/update API

Method `getByPeerId(String peerId)` returns `Future<CertificationExpiryRecord?>` and returns null (not throws) when record not found

Method `upsert(CertificationExpiryRecord record)` inserts or updates the record atomically using Supabase's `upsert()` with `onConflict`

Method `delete(String id)` soft-deletes or hard-deletes as appropriate with proper error propagation

Riverpod `Provider<CertificationExpiryRepository>` is defined and injects `SupabaseClient` from the existing Supabase provider

All Supabase errors are caught and rethrown as domain-specific `CertificationExpiryException` types with descriptive messages

No business logic lives in the repository — it is a pure data-access layer

Technical Requirements

frameworks

Flutter

Riverpod

supabase_flutter

apis

Supabase PostgREST (table: certification_expiry_tracking)

data models

CertificationExpiryRecord

CertificationExpiryException

performance requirements

Repository methods must not block the UI thread — all Supabase calls must be awaited in async methods

No local caching in the repository layer — caching belongs in a separate cache layer or BLoC state

security requirements

Repository uses the authenticated Supabase client (JWT from current session) — never the service-role key

peerId values must not be logged in plain text in production

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Place the abstract interface in `lib/features/certification/data/certification_expiry_repository.dart` and the concrete implementation in `lib/features/certification/data/supabase_certification_expiry_repository.dart` following the existing project layer conventions. For `fromJson`, cast the `notification_thresholds_sent` field as `List` then map to `List` — Supabase returns JSON arrays as `List`. Use Dart's `DateTime.parse()` for `expiry_date` (which arrives as a date string, e.g., '2025-12-31') and `DateTime.tryParse()` for nullable `last_notified_at`. The Riverpod provider should be a `Provider` (not `StateNotifierProvider`) since the repository is stateless.

Pattern: `final certificationExpiryRepositoryProvider = Provider((ref) => SupabaseCertificationExpiryRepository(ref.watch(supabaseClientProvider)));`

Testing Requirements

Write unit tests using `flutter_test` with a mocked `SupabaseClient` (use Mockito or a hand-written fake). Test cases: (1) `getByPeerId` returns null when Supabase returns an empty list; (2) `getByPeerId` returns a populated `CertificationExpiryRecord` when data is present; (3) `fromJson` correctly parses a fixture JSON with a populated `notification_thresholds_sent` array; (4) `fromJson` handles null `last_notified_at` without throwing; (5) `upsert` calls Supabase with the correct table name and conflict target; (6) a Supabase `PostgrestException` is caught and rethrown as `CertificationExpiryException`. Aim for 90%+ line coverage on the repository class.

Component

Certification Expiry Repository

data medium

Dependencies (1)

Design and apply the Supabase database migration that creates the certification_expiry_tracking table. Include columns for peer_mentor_id, certificate_type, expiry_date, last_notified_at, and notification_thresholds_sent (array of integers representing 60/30/7-day markers). Add appropriate indexes on expiry_date and peer_mentor_id for efficient time-windowed queries. epic-certificate-expiry-notifications-data-foundation-task-001

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.

Quick Links

All Tasks Execution Plan