Implement BadgeRepository data access layer

epic-achievement-badges-foundation-task-003 — Implement the BadgeRepository Dart class that provides full CRUD operations for earned_badges and tier_assignments tables via the Supabase client. Methods required: fetchEarnedBadgesForUser, awardBadge, revokeBadge, fetchTierAssignment, assignTier, and updateTierAssignment. All queries must be organisation-scoped. Implement error handling and map Supabase exceptions to domain-level exceptions.

critical priority low complexity backend pending backend specialist Tier 2

Acceptance Criteria

BadgeRepository is a Dart class with a constructor accepting a SupabaseClient dependency (injectable via Riverpod or constructor injection)

fetchEarnedBadgesForUser(String userId, String organisationId) returns List<EarnedBadge> filtered to the given user and organisation

awardBadge(AwardBadgeParams params) inserts a record into earned_badges and returns the created EarnedBadge; throws DuplicateBadgeException if uniqueness constraint is violated

revokeBadge(String earnedBadgeId) updates status to 'revoked' and sets revoked_at to now(); throws BadgeNotFoundException if ID does not exist

fetchTierAssignment(String userId, String organisationId) returns TierAssignment? (nullable) for the user

assignTier(AssignTierParams params) inserts or upserts a tier_assignments record and returns the resulting TierAssignment

updateTierAssignment(String assignmentId, String newTierId) updates the tier_id and assigned_at fields

All methods throw typed domain exceptions: BadgeNotFoundException, DuplicateBadgeException, BadgeRepositoryException (generic)

Supabase PostgrestException is caught and mapped — error code '23505' maps to DuplicateBadgeException, '23503' maps to InvalidReferenceException

No raw Supabase types leak outside the repository — all return types are domain models (EarnedBadge, TierAssignment)

Organisation scoping is applied in every query (.eq('organisation_id', organisationId)) — never relying solely on RLS

Repository has a corresponding abstract interface (IBadgeRepository) to enable mocking in tests

Technical Requirements

frameworks

Flutter

Supabase

Riverpod

apis

Supabase PostgREST client (supabase_flutter package)

Supabase realtime (optional for future)

data models

EarnedBadge

TierAssignment

AwardBadgeParams

AssignTierParams

performance requirements

fetchEarnedBadgesForUser must select only required columns (avoid SELECT *) to minimise payload size

Batch operations should use Supabase upsert for assignTier to avoid a round-trip SELECT before INSERT

security requirements

Never accept raw SQL strings as parameters — use Supabase's typed query builder exclusively

organisation_id must always be sourced from the authenticated session or a trusted service layer, never from user-controlled input directly

Avoid logging sensitive badge data (user IDs, badge award details) in production error logs

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Define an abstract interface `IBadgeRepository` first, then implement `BadgeRepository`. Register both in Riverpod as `badgeRepositoryProvider`. Use Dart extension methods on `Map` for `EarnedBadge.fromJson` and `TierAssignment.fromJson` to keep the repository class lean. Map Supabase error codes systematically — PostgreSQL error code 23505 is unique violation, 23503 is foreign key violation, P0001 is a raised exception from a trigger.

Use a private helper `_handleSupabaseError(PostgrestException e)` that maps codes to domain exceptions. For revokeBadge, use `.update({'status': 'revoked', 'revoked_at': DateTime.now().toIso8601String()}).eq('id', earnedBadgeId)` and check that the response count is 1; if 0, throw BadgeNotFoundException. Do not implement soft-delete as hard-delete — the schema uses status column (see task-001 notes).

Testing Requirements

Write unit tests using flutter_test with a mocked SupabaseClient (using mockito or mocktail). Test coverage must include: (1) fetchEarnedBadgesForUser returns correctly mapped EarnedBadge list from mock response, (2) awardBadge returns created EarnedBadge on success, (3) awardBadge throws DuplicateBadgeException when Supabase returns error code 23505, (4) revokeBadge throws BadgeNotFoundException when update affects 0 rows, (5) fetchTierAssignment returns null when no record exists, (6) all methods pass organisation_id in the query filter. Write at least one integration test against a local Supabase instance verifying the full award → revoke cycle. Aim for 90%+ line coverage on the repository class.

Component

Badge Repository

data low

Dependencies (1)

Write and apply Row Level Security policies for all four badge-related tables (badge_definitions, earned_badges, recognition_tiers, tier_assignments). Policies must enforce organisation-scoped access so that volunteers and coordinators can only read/write records belonging to their own organisation. Superadmin bypass role must be documented and tested. Include both SELECT and INSERT/UPDATE/DELETE policies. epic-achievement-badges-foundation-task-002

Epic Risks (2)

high impact medium prob scope

Badge criteria are stored as structured JSON in badge_definitions. If the JSON schema for criteria (threshold counts, streak lengths, training completion flags) is not well-defined upfront, the evaluation service will be built against a moving target, requiring costly migrations and refactors.

Mitigation & Contingency

Mitigation: Define and document the criteria JSON schema in a shared type file before any repository code is written. Review the schema with all three organisations' badge requirements — especially Blindeforbundet's honorar thresholds — and version the JSON schema using a 'criteria_version' field from day one.

Contingency: If the criteria schema must change after services are built, write a Supabase migration to backfill existing rows and add a migration version column. Keep the evaluation service criteria parser isolated behind an interface so only one function needs updating.

medium impact medium prob dependency

Badge icon assets may not yet exist or may fail WCAG 2.2 AA contrast validation (minimum 3:1 for graphical objects) when rendered over design-token backgrounds. Missing or non-compliant icons could block UI epic delivery for Blindeforbundet, for whom screen reader and visual accessibility is non-negotiable.

Mitigation & Contingency

Mitigation: During this epic, implement the contrast-ratio validator in badge-icon-asset-manager and run it as a Flutter test against all candidate icon assets early. Coordinate with the design team to provide WCAG-compliant SVG icons in both locked and unlocked variants before the UI epic begins.

Contingency: If assets are late or fail contrast checks, ship placeholder icons that are guaranteed compliant (solid design-token colour fills with text labels) and swap in final assets post-QA without requiring a code change.

Quick Links

All Tasks Execution Plan