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

Define ExpenseClaimStatus database schema and Dart models

epic-expense-approval-workflow-foundation-task-005 — Create the expense_claim_status table with columns for claim_id (PK), status (enum: draft/submitted/pending_approval/approved/rejected/exported), version (integer for optimistic locking), updated_at, and updated_by. Define corresponding Dart model with copyWith support. Apply RLS policies restricting status transitions by role.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

expense_claim_status table exists with: claim_id (UUID PK FK to expense_claims), status (TEXT NOT NULL CHECK in ('draft','submitted','pending_approval','approved','rejected','exported')), version (INTEGER NOT NULL DEFAULT 1), updated_at (TIMESTAMPTZ NOT NULL DEFAULT now()), updated_by (UUID NOT NULL FK to auth.users)
A database trigger increments version and updates updated_at automatically on every UPDATE
RLS INSERT: only the claim's original submitter can create the initial status row (status must be 'draft')
RLS UPDATE for status transition to 'submitted': only the submitter when current status is 'draft'
RLS UPDATE for status transition to 'pending_approval' or 'approved' or 'rejected': only users with coordinator role in the claim's chapter
RLS UPDATE for status transition to 'exported': only users with admin or finance role
RLS SELECT: submitter sees their own claim status; coordinator sees all claim statuses in their chapter
ExpenseClaimStatus Dart model: claimId (String), status (ClaimStatus enum), version (int), updatedAt (DateTime), updatedBy (String)
ClaimStatus enum has values: draft, submitted, pendingApproval, approved, rejected, exported with fromString() factory
copyWith() method allows partial updates for reactive UI state management
Unit tests for fromJson/toJson round-trip, all ClaimStatus values, copyWith preserves unmodified fields
Supabase migration file version-controlled

Technical Requirements

frameworks
Flutter
Supabase
apis
Supabase REST API
Supabase Realtime (for watchStatus stream)
Supabase Auth RLS
data models
ExpenseClaimStatus
ClaimStatus
ExpenseClaim
performance requirements
Index on updated_by for coordinator dashboard queries
updated_at index for time-range audit queries
Trigger overhead for version increment must be under 5ms
security requirements
Invalid status transition attempts (e.g., draft → approved skipping submitted) must be caught by a CHECK constraint or trigger at the database level
version column must never be manually settable by application code — only incremented by trigger
updated_by must always match auth.uid() — enforced via RLS USING clause
Status 'exported' must only be set after 'approved' — enforce valid transition paths via database trigger

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

The status transition validation should be enforced at two levels: (1) a Dart ClaimStatusTransitionValidator in the domain layer that throws InvalidTransitionException for disallowed transitions before making any network call, and (2) a database trigger that enforces the same rules as a safety net. Valid transitions: draft→submitted (submitter), submitted→pending_approval (coordinator), pending_approval→approved (coordinator), pending_approval→rejected (coordinator), approved→exported (admin/finance), rejected→draft (submitter for resubmission). The version column is the key for optimistic locking in task-006 — define it here as a database-managed counter. For the copyWith() pattern, generate it or implement manually; do not use external code generation if the project doesn't already use build_runner, to keep the build consistent.

Testing Requirements

Unit tests: ExpenseClaimStatus.fromJson() all status values, copyWith() preserves unchanged fields and applies changes, ClaimStatus.fromString() throws on unknown string, version is parsed as integer not double. Integration tests against Supabase test instance: valid transition sequence (draft→submitted→pending_approval→approved→exported) succeeds, invalid transition (draft→approved) rejected by database constraint, version increments on each update, UPDATE with stale version (optimistic lock conflict) returns the current row without applying changes — verify application layer detects this, SELECT returns only records in coordinator's scope, updated_by is set to auth.uid() and cannot be overridden by client payload.

Component
Expense Claim Status Repository
data medium
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