Design Supabase schema for badge tables
epic-achievement-badges-foundation-task-001 — Design and write Supabase migration scripts for badge_definitions, earned_badges, recognition_tiers, and tier_assignments tables. Include all columns, foreign key constraints, uniqueness constraints preventing duplicate badge awards per volunteer per organisation, and CHECK constraints for valid status values. Schema must support configurable honorar-threshold criteria (e.g., 3rd and 15th assignment triggers) as JSON config columns in badge_definitions without requiring code changes.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use Supabase CLI migration workflow (`supabase migration new badge_schema`). Write the UP migration as a single transaction so it rolls back atomically on failure. The criteria JSONB schema should be documented via a JSON Schema comment in the migration file — Supabase does not enforce JSONB structure natively, so document the expected shape: `{"type": "assignment_count", "threshold":
The tier_assignments UNIQUE constraint should similarly be partial if re-assignment is a valid workflow. Coordinate with the RLS task (task-002) — schema must be finalised before policies are written. Avoid using SERIAL/SEQUENCE for IDs; use `gen_random_uuid()` as the default for all PK columns to align with Supabase conventions.
Testing Requirements
Write SQL integration tests using pgTAP or Supabase's built-in test runner. Tests must verify: (1) successful INSERT into all four tables with valid data, (2) UNIQUE constraint violation when attempting to award the same badge twice to the same user in the same organisation, (3) CHECK constraint rejection for invalid status values, (4) FK constraint enforcement (inserting earned_badge with non-existent badge_definition_id must fail), (5) criteria JSONB column stores and retrieves complex threshold objects correctly, (6) DOWN migration cleanly removes all tables and constraints. Run migrations against a local Supabase instance as part of CI. At least one test per constraint type.
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.
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.