🏠 Overview › ✅ Implementation Tasks ›✅ epic-achievement-badges-evaluation-engine-task-005

Build badge definition loader with org scoping

epic-achievement-badges-evaluation-engine-task-005 — Implement the badge definition loader within the evaluation service that fetches all enabled badge definitions for a given organisation from the Supabase database. Must support org-level filtering and cache definitions for the duration of a single evaluation run to minimise database round-trips.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

Loader fetches only badge definitions where `enabled = true` AND `org_id` matches the provided organisation ID
Loader returns an empty array (not an error) when no enabled definitions exist for the org
Results are cached in a Map keyed by org_id for the lifetime of the edge function invocation; second call for the same org does not issue a database query
Cache is scoped per function invocation (module-level Map is acceptable in Deno edge functions due to per-request isolation)
Loader throws a typed error (not a raw string) if the Supabase query fails, containing the org_id and HTTP status code
Returned definitions conform to the BadgeDefinition TypeScript interface from task-001
Blindeforbundet-specific: definitions include threshold values for 3rd and 15th honorar milestones
HLF-specific: definitions include certification expiry fields where applicable
Loader accepts a Supabase client instance as a constructor/function argument (enabling test injection)
No badge definitions from other organisations are ever returned, even under concurrent edge function execution

Technical Requirements

frameworks
Supabase Edge Functions (Deno runtime)
supabase-js v2 (Deno-compatible ESM import)
apis
Supabase PostgREST: `GET /rest/v1/badge_definitions?enabled=eq.true&org_id=eq.{orgId}&select=*`
data models
badge_definitions (org_id, enabled, criteria_type, criteria_config JSON, badge_id, display metadata)
performance requirements
Single database round-trip per org per invocation via in-memory cache
Query must use indexed columns: org_id and enabled — ensure Supabase index exists on badge_definitions(org_id, enabled)
Query response must arrive within 500 ms under normal Supabase latency
security requirements
Use service-role Supabase client (not anon key) — this runs inside a trusted edge function
Row-Level Security (RLS) policies on badge_definitions must allow service-role reads; verify policy does not accidentally expose cross-org data
Never log the full definitions array — it may contain org-internal configuration

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Implement as a `BadgeDefinitionLoader` class with a constructor accepting a `SupabaseClient`. Use a private `Map` for the in-memory cache. The `load(orgId: string): Promise` method checks the cache first, then queries Supabase. The `criteria_config` column stores a JSON object — define typed subtypes per `criteria_type` ('threshold' | 'streak' | 'training_completion') and use a discriminated union for type-safe access downstream.

Blindeforbundet's honorar thresholds (3 and 15) should be stored in `criteria_config` as `{ threshold: 3 }` and `{ threshold: 15 }` respectively, with `criteria_type: 'threshold'` and a sub-type discriminator field. Do not hardcode org-specific thresholds in application code.

Testing Requirements

Unit tests covered in task-008. During implementation, provide integration test stubs for: (1) org with 3 enabled and 2 disabled definitions — verify only 3 returned, (2) org with zero definitions — verify empty array returned, (3) two sequential calls for same org — verify DB call count is 1 (mock the Supabase client and assert `from().select()` called once). Use Deno's built-in stub/mock utilities from `https://deno.land/std/testing/mock.ts`.

Component
Badge Evaluation Service
service high
Dependencies (1)
Define TypeScript interfaces and abstract base classes for the three criteria evaluator types: threshold-based, streak-based, and training-completion. Establish the CriteriaEvaluator contract that all evaluators must implement, including input/output types for aggregated peer mentor stats and evaluation results. epic-achievement-badges-evaluation-engine-task-001
Epic Risks (3)
medium impact medium prob technical

Supabase Edge Functions may experience cold start latency of 500ms–2s when they have not been invoked recently. If evaluation latency consistently exceeds the 2-second UI expectation, the celebration overlay timing SLA cannot be met without the optimistic UI fallback from the UI epic.

Mitigation & Contingency

Mitigation: Keep the edge function warm by scheduling a lightweight health-check invocation every 5 minutes in production. Optimise the function size to minimise Deno module load time. Implement the optimistic UI path in badge-bloc (from the UI epic) as the primary UX path so cold start only affects server-side reconciliation, not perceived responsiveness.

Contingency: If cold starts remain problematic, migrate badge evaluation to a Supabase database function (pl/pgsql) triggered directly by a database trigger on activity insert, eliminating the Edge Function overhead entirely for the evaluation logic while keeping Edge Function only for FCM notification dispatch.

high impact low prob integration

Supabase database webhooks can fail silently if the edge function returns a non-2xx response or times out. A missed webhook means a peer mentor does not receive a badge they earned, which is both a functional defect and a trust issue for organisations relying on milestone tracking.

Mitigation & Contingency

Mitigation: Implement idempotent webhook processing: the edge function reads the activity ID from the webhook payload and checks whether evaluation for this activity has already run (via an audit log query) before proceeding. Add Supabase webhook retry configuration (3 retries with exponential backoff). Monitor webhook failure rates via Supabase logs alert.

Contingency: Implement a nightly reconciliation job (Supabase scheduled function) that scans all activities from the past 24 hours, re-evaluates badge criteria for any peer mentor with no corresponding evaluation log entry, and awards any missing badges. Alert operations if reconciliation awards more than 5% of badges, indicating systematic webhook failure.

high impact low prob security

The evaluation service loads badge definitions per organisation, but a misconfigured RLS policy or incorrect organisation scoping in the edge function could cause one organisation's badge criteria to be evaluated against another organisation's peer mentor activity data, leading to incorrect or cross-contaminated badge awards.

Mitigation & Contingency

Mitigation: The edge function must extract organisation_id from the webhook payload activity record and pass it explicitly to every database query. Write a security test that seeds two organisations with distinct badge definitions and verifies that evaluating a peer mentor in org A never reads or awards org B definitions. Use Supabase service role key only within the edge function, never the anon key.

Contingency: If cross-org contamination is detected in audit logs, immediately disable the edge function webhook, run a targeted SQL query to identify and revoke incorrectly awarded badges, notify affected organisations, and perform a full security review of all RLS policies on badge-related tables before re-enabling.

Quick Links
All Tasks Execution Plan