🏠 Overview › ✅ Implementation Tasks ›✅ epic-peer-mentor-pause-management-foundation-task-003

Build Dart repository class for certification status

epic-peer-mentor-pause-management-foundation-task-003 — Implement the CertificationStatusRepository Dart class with methods to query, insert, update, and stream certification records from Supabase. Include typed data models, error handling, and Riverpod provider registration.

critical priority low complexity backend pending backend specialist Tier 2

Acceptance Criteria

CertificationStatus Dart class (or freezed data class) exists with all fields matching the cert_expiry_reminders table schema, including correct nullable annotations
CertificationStatus.fromJson() and toJson() correctly serialize/deserialize all fields, with date fields converted between Dart DateTime and ISO 8601 strings
CertificationStatusRepository class implements at minimum: fetchForMentor(mentorId), fetchForChapter(chapterId), upsertCertification(CertificationStatus), streamForMentor(mentorId) returning Stream<List<CertificationStatus>>
All methods handle Supabase PostgrestException and network errors, converting them to a typed RepositoryException or Result type rather than letting raw exceptions propagate
streamForMentor() uses Supabase Realtime channel subscription and emits updated lists on any INSERT/UPDATE/DELETE to the filtered rows
Riverpod provider certificationStatusRepositoryProvider is registered and scoped to override in tests
Repository class passes dart analyze with zero errors or warnings
Unit tests (mocked Supabase client) for fetchForMentor and upsertCertification pass
No hardcoded table name strings outside a single constants file or the repository class itself

Technical Requirements

frameworks
Flutter
Riverpod
supabase_flutter
freezed (optional but recommended for data models)
apis
Supabase REST API (PostgREST)
Supabase Realtime
data models
CertificationStatus
CertExpiryReminder
performance requirements
fetchForMentor must add a .limit() clause to prevent unbounded result sets (max 100 records per query)
streamForMentor must filter the Realtime subscription by mentor_id to avoid receiving all table events
security requirements
Repository must use the authenticated Supabase client (supabase.auth.currentSession) — never the service role key
No mentor_id or chapter_id is hardcoded — always derived from the authenticated user session or passed as a parameter
Sensitive certification data must not be logged to the console in non-debug builds

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Follow the repository pattern already established in the codebase — look at existing repositories (e.g., PeerMentorStatusRepository if it exists) for the constructor signature, provider registration pattern, and error wrapping convention. Use freezed for CertificationStatus if the rest of the project uses it — consistency matters more than personal preference. For the Realtime stream, use supabase.from('cert_expiry_reminders').stream(primaryKey: ['id']).eq('mentor_id', mentorId) which returns a Stream>> that you map to Stream>. Remember to cancel the subscription in a dispose method or use Riverpod's ref.onDispose.

The upsert should use onConflict: 'mentor_id,certification_type' to match the unique constraint defined in the schema migration.

Testing Requirements

Unit tests using flutter_test with a mocked SupabaseClient (mockito or mocktail). Test: (1) fetchForMentor returns a typed List on success; (2) fetchForMentor throws RepositoryException on PostgrestException; (3) upsertCertification calls the correct Supabase upsert method with onConflict: 'mentor_id,certification_type'; (4) fromJson correctly parses a sample JSON fixture including date fields and boolean reminder flags. Do not test Realtime streaming in unit tests — save that for integration tests. Aim for 80%+ branch coverage on the repository class.

Component
Certification Status Repository
data low
Dependencies (1)
Define and apply Row Level Security policies on the cert_expiry_reminders table to enforce coordinator and admin access patterns. Ensure peer mentors can only read their own certification records, while coordinators can read records within their chapter scope. epic-peer-mentor-pause-management-foundation-task-002
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for coordinator-scoped status queries may be difficult to express correctly, especially for peer mentors assigned to multiple coordinators or chapters, leading to data leakage or overly restrictive access blocking valid queries.

Mitigation & Contingency

Mitigation: Design RLS policies using security-definer RPCs rather than table-level policies for complex multi-coordinator scenarios. Write a comprehensive RLS test matrix covering all role and assignment permutations before marking complete.

Contingency: Fall back to application-level filtering in the repository layer with explicit coordinator_id parameter checks if RLS proves intractable, and document the trade-off for security review.

high impact medium prob dependency

The HLF Dynamics portal API contract may be undocumented or subject to change, causing the DynamicsPortalClient to break during development or production rollout.

Mitigation & Contingency

Mitigation: Obtain the full Dynamics portal API specification and credentials early in the sprint. Build the client behind a well-defined interface so the HLF-specific implementation can be swapped without affecting upstream services.

Contingency: If the Dynamics API is unavailable or unstable, stub the client with a feature-flag-guarded no-op implementation so all other epics can proceed to completion independently.

medium impact low prob technical

Supabase Edge Functions used as the nightly scheduler host may have cold-start latency or execution time limits that prevent reliable nightly certification checks on large mentor rosters.

Mitigation & Contingency

Mitigation: Benchmark Edge Function execution time against the expected roster size. Design the expiry check to process in paginated batches to stay within execution limits. Use pg_cron with a direct database function as an alternative trigger if Edge Functions prove unreliable.

Contingency: Migrate the scheduler trigger to pg_cron invoking a Postgres function directly, removing the Edge Function dependency entirely for the scheduling layer.

Quick Links
All Tasks Execution Plan