🏠 Overview › ✅ Implementation Tasks ›✅ epic-certification-management-foundation-task-003

Implement CertificationRepository CRUD methods

epic-certification-management-foundation-task-003 — Build the CertificationRepository Dart class with full CRUD: createCertification, getCertificationById, getCertificationsByMentorId, updateCertification, softDeleteCertification. All methods must use the Supabase client with proper RLS context. Repository interface must be defined as an abstract class to support mocking in tests.

critical priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

Abstract class CertificationRepository is defined with method signatures for createCertification, getCertificationById, getCertificationsByMentorId, updateCertification, and softDeleteCertification
Concrete class SupabaseCertificationRepository implements CertificationRepository using the injected SupabaseClient
createCertification accepts a CertificationModel and returns the created record (with server-generated id and timestamps) or throws a typed RepositoryException
getCertificationById returns CertificationModel? (nullable) — null if not found, throws on network/RLS errors
getCertificationsByMentorId returns List<CertificationModel> filtered by peer_mentor_id, ordered by issued_at DESC
updateCertification applies a partial update (only changed fields) and returns the updated CertificationModel
softDeleteCertification sets status = 'suspended' and does NOT hard-delete the row, preserving the renewal_history audit trail
CertificationModel is a Dart freezed/immutable data class with fromJson/toJson, mapping all table columns including the renewal_history JSONB list
All methods are async and return Future<T>; errors from Supabase (PostgrestException, network errors) are caught and rethrown as domain-level RepositoryException with a meaningful message
A MockCertificationRepository stub class is provided for use in BLoC/Riverpod unit tests
All public methods are covered by unit tests using the mock implementation

Technical Requirements

frameworks
Flutter
Supabase Flutter SDK (supabase_flutter)
freezed
json_serializable
riverpod (for DI of repository)
apis
Supabase PostgREST REST API via supabase_flutter client
data models
CertificationModel
RenewalHistoryEntry
CertificationStatus (enum: active, expired, suspended)
performance requirements
getCertificationsByMentorId must not fetch more than 200 rows by default; add a limit parameter
Repository methods must not perform synchronous blocking work on the UI thread
security requirements
Never expose the Supabase service-role key in the Flutter app; use anon key with RLS
CertificationModel.fromJson must validate that status is one of the known enum values and throw on unexpected values
Repository must not log PII (peer_mentor_id, certification details) to the console in production builds

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Define CertificationRepository as an abstract class in lib/features/certification/data/repositories/certification_repository.dart. Place the Supabase implementation in lib/features/certification/data/repositories/supabase_certification_repository.dart. Use freezed for CertificationModel to get copyWith, equality, and json support for free. For the renewal_history JSONB, define a RenewalHistoryEntry freezed class and serialize the list with json_serializable.

Register SupabaseCertificationRepository as a Riverpod Provider so it can be overridden with MockCertificationRepository in tests. Do not use .execute() pattern from older Supabase SDK versions; use the modern .select().eq().maybeSingle() fluent API. Wrap all Supabase calls in a try/catch converting PostgrestException to a typed RepositoryException defined in the domain layer.

Testing Requirements

Unit tests (flutter_test): mock the SupabaseClient using a hand-written or Mockito-generated mock; test each CRUD method for: (1) happy path returning correct model; (2) Supabase 404 / empty response returning null or empty list; (3) PostgrestException being re-thrown as RepositoryException; (4) softDelete setting status to 'suspended' not deleting the row. Integration tests: run against a local Supabase instance (supabase start) and verify actual database round-trips for each method. Aim for 90%+ line coverage on the repository class.

Component
Certification Repository
data medium
Dependencies (2)
Design and migrate the peer_mentor_certifications table in Supabase with all required columns: id, peer_mentor_id, certification_type, issued_at, expires_at, status (active/expired/suspended), and renewal_history as a JSONB column storing the full longitudinal qualification record. Apply RLS policies to restrict row access by organisation and role. epic-certification-management-foundation-task-001 Design and migrate the cert_notification_log table used to prevent duplicate push notifications. Columns: id, certification_id, notification_type (expiry_warning_90d, expiry_warning_30d, expiry_warning_7d, expired), sent_at, recipient_id, idempotency_key (unique). Add index on (certification_id, notification_type) for fast deduplication lookups. epic-certification-management-foundation-task-002
Epic Risks (3)
high impact medium prob integration

HLF Dynamics portal webhook API contract may be undocumented, subject to change, or require a separate authentication flow not yet agreed upon with HLF. If the contract changes post-implementation, the sync service silently fails and expired peer mentors remain on public listings.

Mitigation & Contingency

Mitigation: Obtain the official Dynamics webhook specification and test credentials from HLF before starting HLFDynamicsSyncService implementation. Agree on a versioned webhook contract and request a staging endpoint for integration testing.

Contingency: If the contract is unavailable, stub the sync service behind a feature flag and ship without Dynamics sync initially. Queue sync events locally and replay once the contract is confirmed.

high impact medium prob security

Supabase RLS policies for certifications must correctly scope data to the coordinator's chapter without leaking cross-organisation data, particularly complex in multi-chapter membership scenarios. A misconfigured policy could expose peer mentor PII to wrong coordinators.

Mitigation & Contingency

Mitigation: Write RLS policies against the established org-hierarchy schema used by other tables. Peer review all policies before migration deployment. Add integration tests that assert cross-organisation data isolation using test accounts with different org scopes.

Contingency: If a policy gap is discovered post-merge, immediately disable the affected query endpoint and apply a hotfix migration. Audit access logs in Supabase for any cross-org data access events.

medium impact low prob technical

Storing renewal history as a JSONB field rather than a normalised table simplifies queries but makes retrospective schema changes (adding fields to history entries) harder and could cause issues if history grows very large for long-tenured mentors.

Mitigation & Contingency

Mitigation: Define a versioned JSONB entry schema (include a schema_version field in each entry) so future migrations can transform old entries. Add a size guard in the repository to warn if renewal_history exceeds 500 entries.

Contingency: If JSONB approach proves limiting, add a normalised certification_renewal_events table and migrate history entries in a background job, keeping the JSONB field as a read cache.

Quick Links
All Tasks Execution Plan