🏠 Overview › ✅ Implementation Tasks ›✅ epic-expense-approval-workflow-foundation-task-003

Define ClaimApproval database schema and Dart models

epic-expense-approval-workflow-foundation-task-003 — Create the claim_approval_decisions table in Supabase with columns for decision_id, claim_id, coordinator_id, decision (approved/rejected/escalated), justification, threshold_at_decision, decided_at, and full coordinator metadata snapshot. Define Dart model classes. Apply RLS so coordinators can only insert decisions for claims in their chapter scope.

critical priority low complexity database pending database specialist Tier 1

Acceptance Criteria

claim_approval_decisions table exists with columns: decision_id (UUID PK default gen_random_uuid()), claim_id (UUID NOT NULL FK to expense_claims), coordinator_id (UUID NOT NULL FK to auth.users), decision (TEXT NOT NULL CHECK in ('approved','rejected','escalated')), justification (TEXT nullable), threshold_at_decision (NUMERIC NOT NULL), decided_at (TIMESTAMPTZ NOT NULL DEFAULT now()), coordinator_snapshot (JSONB NOT NULL)
coordinator_snapshot captures coordinator's name, role, chapter_id, and email at decision time — immutable snapshot, not a live reference
RLS INSERT policy: auth.uid() must equal coordinator_id AND the claim must belong to a chapter the coordinator is authorized for (join to coordinator_chapter_memberships or equivalent)
RLS SELECT policy: coordinators see decisions for claims in their chapter; claim submitters see decisions for their own claims
No UPDATE or DELETE RLS policies defined on claim_approval_decisions
ClaimApprovalDecision Dart model fields: decisionId (String), claimId (String), coordinatorId (String), decision (ApprovalDecision enum: approved/rejected/escalated), justification (String?), thresholdAtDecision (double), decidedAt (DateTime), coordinatorSnapshot (CoordinatorSnapshot)
CoordinatorSnapshot is a separate immutable Dart value class with name, role, chapterId, email fields
ApprovalDecision.fromString() throws ArgumentError on unknown values
Supabase migration file created and version-controlled
Unit tests pass for full round-trip serialization of ClaimApprovalDecision including nested CoordinatorSnapshot

Technical Requirements

frameworks
Flutter
Supabase
apis
Supabase REST API
Supabase Auth RLS (auth.uid())
data models
ClaimApprovalDecision
CoordinatorSnapshot
ExpenseClaim
performance requirements
Index on claim_id for fast decision lookup per claim
Index on coordinator_id for coordinator dashboard queries
Index on decided_at for chronological audit reports
security requirements
coordinator_snapshot must be populated at insert time — never derivable from a JOIN to current user data (guards against retroactive data changes)
threshold_at_decision must be captured from the active threshold configuration at decision time, not computed later
RLS must prevent cross-chapter decision insertion — a coordinator from chapter A cannot approve claims from chapter B
justification is nullable but business rules (enforced at application layer) require it for rejections and escalations

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

The coordinator_snapshot JSONB column is a deliberate denormalization for audit integrity — if a coordinator's role or chapter changes later, historical decisions must still reflect the context at time of decision. Populate this snapshot in the application layer (repository) by reading the current coordinator profile from the auth session and user profile before inserting. The threshold_at_decision column solves a similar problem for threshold values — the application must query the active approval threshold and include it in the insert payload, not rely on a database default. For the Dart model, use a const constructor and override == and hashCode for value equality — useful in BLoC state comparisons.

Use the same migration version sequencing as task-001 to ensure dependency order is clear.

Testing Requirements

Unit tests: ClaimApprovalDecision.fromJson() and toJson() round-trip for all three ApprovalDecision values, nested CoordinatorSnapshot serialization, null justification handling, decided_at UTC parsing. Integration tests against Supabase test instance: INSERT succeeds when coordinator owns the claim's chapter, INSERT fails with 42501 when coordinator does not own the claim's chapter, SELECT returns only decisions within coordinator's scope, UPDATE attempt returns 42501, verify coordinator_snapshot is stored as provided (not a live JOIN).

Component
Claim Approval Decision Repository
data low
Dependencies (1)
Create the Supabase database table for claim_events with immutable audit columns (id, claim_id, event_type, actor_id, metadata, created_at). Define corresponding Dart model classes with fromJson/toJson serialization. Apply RLS policies so only authenticated users can insert; deny UPDATE and DELETE at the database level via policy and constraint. epic-expense-approval-workflow-foundation-task-001
Epic Risks (3)
medium impact medium prob technical

Optimistic locking in ExpenseClaimStatusRepository may produce excessive concurrency exceptions in high-volume coordinator sessions where multiple coordinators process the same queue simultaneously, causing confusing UI errors and coordinator frustration.

Mitigation & Contingency

Mitigation: Design the locking strategy with a short retry window (1-2 automatic retries with 200ms back-off) before surfacing the error to the UI. Document the concurrency model clearly so the UI layer can display a contextual 'claim was already actioned' message rather than a generic error.

Contingency: If contention remains high under load testing, switch to a last-writer-wins update with a conflict notification rather than a hard block, and log all concurrent edits for audit purposes.

medium impact medium prob integration

FCM device tokens stored for peer mentors may be stale (app reinstalled, token rotated) causing push notifications for claim status changes to silently fail, leaving submitters unaware their claim was approved or rejected.

Mitigation & Contingency

Mitigation: Implement token refresh on every app launch and store updated tokens in Supabase. ApprovalNotificationService should fall back to in-app Realtime delivery when FCM returns an invalid-token error and should queue a token refresh request.

Contingency: If FCM delivery rates fall below acceptable thresholds in production monitoring, add a polling fallback in the peer mentor claim list screen that checks status on foreground resume.

high impact low prob dependency

Supabase Realtime has per-project channel and connection limits. If many coordinators and peer mentors are simultaneously subscribed across multiple screens, the project may hit quota limits causing subscription failures.

Mitigation & Contingency

Mitigation: Design RealtimeApprovalSubscription to use a single shared channel per user session rather than per-screen subscriptions. Implement subscription reference counting so channels are only opened once and reused across screens.

Contingency: Upgrade the Supabase plan tier if limits are reached, and implement graceful degradation to polling with a 30-second interval when Realtime is unavailable.

Quick Links
All Tasks Execution Plan