Implement ExpenseClaimStatusRepository with optimistic locking
epic-expense-approval-workflow-foundation-task-006 — Implement ExpenseClaimStatusRepository with getStatus(), updateStatus(), and watchStatus() methods. Implement optimistic locking by including the current version in all UPDATE WHERE clauses and throwing OptimisticLockException when the row has been modified concurrently. Expose a stream via Supabase Realtime for reactive UI updates.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Optimistic locking with Supabase: use `.update(payload).eq('claim_id', claimId).eq('version', currentVersion)` — the version filter means 0 rows affected = conflict. Supabase's `.update()` does not return a count by default; use `.select()` chained after `.update()` to get the updated row back, then check if the list is empty (conflict) or has one item (success). Do NOT use a separate SELECT to check version before UPDATE — this creates a TOCTOU race condition that defeats the purpose of optimistic locking. For Realtime, use `supabase.channel('claim-status-$claimId').onPostgresChanges(...)` scoped to `filter: 'claim_id=eq.$claimId'`.
In the stream transformer, parse the payload directly from the Realtime event rather than issuing a follow-up SELECT, to minimize latency and round-trips. Provide a StreamController wrapper that handles the Supabase RealtimeChannel lifecycle (subscribe on listen, unsubscribe on cancel) within a single Dart Stream.
Testing Requirements
Unit tests using flutter_test and mock SupabaseClient: getStatus() success, getStatus() with empty result throws ClaimNotFoundException, updateStatus() with matching version succeeds, updateStatus() with mismatched version throws OptimisticLockException with re-fetched status, watchStatus() emits values on stream, stream cancellation removes Supabase Realtime subscription, InvalidTransitionException thrown before network call for disallowed transitions, all three retry scenarios for network errors. Integration tests: concurrent update simulation — two clients read version=1, first update succeeds returning version=2, second update returns OptimisticLockException, Realtime stream emits event within 2 seconds of direct database UPDATE, stream reconnects after simulated network drop.
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.
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.
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.