Implement BadgeAwardService atomic write with idempotency
epic-achievement-badges-services-task-009 — Implement BadgeAwardService.awardBadge(mentorId, badgeDefinitionId, orgId) using a Supabase upsert with a composite unique constraint on (mentor_id, badge_definition_id) to enforce idempotency. Include earned_at timestamp set at server time, return the full EarnedBadge record to callers, and wrap the operation in a Supabase RPC call to ensure atomicity. Handle conflict responses gracefully by returning the existing record.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Write the Supabase RPC function `award_badge_idempotent` in SQL using `INSERT INTO earned_badges (...) VALUES (...) ON CONFLICT (mentor_id, badge_definition_id) DO UPDATE SET updated_at = now() RETURNING *` — the DO UPDATE clause is needed to trigger RETURNING on conflict (DO NOTHING does not return the existing row in all Postgres versions). Ensure the composite UNIQUE constraint exists as a proper database constraint, not just enforced in application code. In Dart, call `supabase.rpc('award_badge_idempotent', params: {...})` and map the single returned row to `EarnedBadge.fromJson`. Keep `BadgeAwardService` stateless — it holds no cache and no state; cache management belongs to the Riverpod provider layer in task-010.
Model `BadgeAwardException` with a `code` enum (notFound, crossOrgViolation, permissionDenied, networkError) so callers can present specific error messages rather than generic ones.
Testing Requirements
Integration tests with a test Supabase instance (or mocked Supabase client): verify first call creates a row and returns full EarnedBadge; verify second call with same inputs returns the same row (same `id`, same `earned_at`) without error; verify concurrent calls (use Dart `Future.wait`) produce exactly one row; verify error is thrown for non-existent badge definition; verify error is thrown for cross-org badge definition; verify error is thrown for non-member mentorId. Unit test the Dart `BadgeAwardService` class with a mock Supabase client to verify error wrapping and response mapping. Test `EarnedBadge.fromJson` with sample Supabase response payloads including null optional fields.
peer-mentor-stats-aggregator must compute streaks and threshold counts across potentially hundreds of activity records per peer mentor. Naive queries (full table scans or N+1 patterns) will cause slow badge evaluation, especially when triggered on every activity save for all active peer mentors.
Mitigation & Contingency
Mitigation: Design aggregation queries using Supabase RPCs with window functions or materialised views from the start. Add database indexes on (peer_mentor_id, activity_date, activity_type) before writing any service code. Profile all aggregation queries against a dataset of 500+ activities during development.
Contingency: If query performance is insufficient at launch, implement incremental stat caching: maintain a peer_mentor_stats snapshot table updated on each activity insert via a database trigger, so the aggregator reads from pre-computed values rather than scanning raw activity rows.
badge-award-service must be idempotent, but if two concurrent edge function invocations evaluate the same peer mentor simultaneously (e.g., from a rapid double-save), both could pass the uniqueness check before either commits, resulting in duplicate badge records.
Mitigation & Contingency
Mitigation: Rely on the database-level uniqueness constraint (peer_mentor_id, badge_definition_id) as the final guard. In the service layer, use an upsert with ON CONFLICT DO NOTHING and return the existing record. Add a Postgres advisory lock or serialisable transaction for the award sequence during the edge function integration epic.
Contingency: If duplicate records are discovered in production, run a deduplication migration to remove extras (keeping earliest earned_at) and add a unique index if not already present. Alert engineering via Supabase database webhook on constraint violations.
The badge-configuration-service must validate org admin-supplied criteria JSON on save, but the full range of valid criteria types (threshold, streak, training-completion, tier-based) may not be fully enumerated during development, leading to either over-permissive or over-restrictive validation that frustrates admins.
Mitigation & Contingency
Mitigation: Define a versioned Dart sealed class hierarchy for CriteriaType before writing the validation logic. Review the hierarchy with product against all known badge types across NHF, Blindeforbundet, and HLF before implementation. Build the validator against the sealed class so new criteria types require an explicit code addition.
Contingency: If admins encounter validation rejections for legitimate criteria, expose a 'criteria_raw' escape hatch (JSON passthrough, admin-only) with a product warning, and schedule a sprint to formalise the new criteria type properly.