🏠 Overview › ✅ Implementation Tasks ›✅ epic-certification-management-core-logic-task-002

Implement Supabase-backed CertificationRepository

epic-certification-management-core-logic-task-002 — Implement the CertificationRepository using the Supabase Dart client. Cover CRUD operations for certification records: create, read by mentor ID, update renewal date, update expiry status, and paginated queries for certs expiring within N days. Apply RLS policies to scope queries to the authenticated user's organisation.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

Concrete class `SupabaseCertificationRepository` implements `CertificationRepository` with all declared methods
`getCertificationsByMentor` queries the `certification` table filtered by `peer_mentor_id` and returns correctly deserialized Certification objects
`saveRenewal` performs an upsert on the `certification` table and returns the persisted record including server-generated timestamps
`updateExpiryStatus` performs a targeted update on `status` and `updated_at` columns only, without overwriting other fields
`getCertificationsExpiringWithin` queries where `expires_at` is between `now()` and `now() + window` and scopes by `organization_id` from the authenticated JWT claims
Pagination is implemented using Supabase `.range(from, to)` with a configurable page size (default 50)
All queries rely on Supabase RLS to enforce organisation isolation — no client-side organisation filtering is added on top
Network errors and Supabase `PostgrestException` are caught and rethrown as typed `CertificationRepositoryException` subclasses
The implementation does not hold any BLoC, widget, or UI references — pure data layer
All async operations complete without unhandled exceptions when the Supabase session is valid
Integration smoke test passes against a local Supabase instance with seeded certification data

Technical Requirements

frameworks
Flutter
Dart
Supabase
apis
Supabase PostgreSQL 15
Supabase Auth
data models
certification
performance requirements
Paginated queries must use `.range()` to avoid full table scans on large certification tables
Index on `(peer_mentor_id, organization_id)` must exist in Supabase schema for getCertificationsByMentor to perform within 200ms
security requirements
RLS policy on `certification` table must enforce `organization_id = auth.jwt() ->> 'org_id'` for all SELECT, INSERT, UPDATE operations
Service role key must never be used in the mobile client — all queries use the anon/user JWT
No raw SQL passed from client — use Supabase query builder only to prevent injection

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Place the concrete implementation in `lib/data/repositories/supabase_certification_repository.dart`. Inject `SupabaseClient` via constructor for testability. Use `.from('certification')` query builder throughout — never raw SQL. For `saveRenewal`, use `.upsert()` with `onConflict: 'id'` to handle both create and renewal update in one call.

For `getCertificationsExpiringWithin`, construct the upper bound timestamp server-side using PostgreSQL interval syntax via `.lte('expires_at', upperBound.toIso8601String())` rather than relying on Dart-side date arithmetic to avoid clock skew. Map `PostgrestException` codes to specific `CertificationRepositoryException` subclasses: code `PGRST116` (row not found) → `CertificationNotFoundException`, auth errors → `CertificationPermissionDeniedException`. Register this repository as a singleton in the Riverpod provider tree so the Supabase client instance is not recreated per call.

Testing Requirements

Write unit tests using `mocktail` to mock the Supabase client and verify that each repository method constructs the correct query chain (table name, filters, columns). Write integration tests against a local Supabase instance (via Docker or supabase CLI) seeded with test certification rows for two different organisations; assert that queries for org A never return rows for org B (RLS boundary test). Test error paths: simulate a `PostgrestException` and assert that a typed `CertificationRepositoryException` is thrown. Test pagination: verify that requesting page 2 with page size 5 returns the correct slice.

Aim for 90% branch coverage on the concrete implementation class.

Component
Certification Management Service
service high
Dependencies (1)
Define the abstract CertificationRepository interface that CertificationManagementService will depend on. Specify method signatures for fetching certifications by mentor, saving renewals, updating expiry status, and querying certs due for expiry within a given date window. Ensure data models align with the Supabase schema for certifications. epic-certification-management-core-logic-task-001
Epic Risks (2)
high impact medium prob technical

The auto-pause workflow requires CertificationManagementService to call PauseManagementService and HLFDynamicsSyncService in the same logical transaction. If PauseManagementService succeeds but the Dynamics webhook fails, the mentor is paused locally but remains visible on the HLF portal.

Mitigation & Contingency

Mitigation: Implement a saga pattern: write a pending sync event to the database before calling Dynamics, and have a background retry job consume pending events. This guarantees eventual consistency even if the webhook fails transiently.

Contingency: If the Dynamics sync fails after auto-pause, surface an explicit coordinator alert in the dashboard indicating 'Dynamics sync pending — mentor may still be visible on portal'. Allow manual retry from coordinator UI.

medium impact low prob technical

If the nightly cron job runs concurrently (e.g., due to infra retry), CertificationReminderService could dispatch duplicate notifications to mentors before the cert_notification_log insert is visible to the second invocation.

Mitigation & Contingency

Mitigation: Use Supabase's upsert with a unique constraint on (mentor_id, threshold_days, cert_id) in cert_notification_log. The second concurrent insert will fail gracefully and the duplicate dispatch will be skipped.

Contingency: If duplicate notifications do reach mentors, add a post-dispatch dedup check and include a 'you may receive this notification again' disclaimer until the constraint is deployed.

Quick Links
All Tasks Execution Plan