Implement Supabase Realtime subscription for attestation queue updates
epic-travel-expense-registration-core-services-task-010 — Integrate Supabase Realtime into the expense attestation service so that the coordinator's queue updates live without manual refresh. Subscribe to INSERT and UPDATE events on the expense_claims table filtered by the coordinator's chapter scope. Merge incoming events into the existing queue state held by the AsyncNotifier. Unsubscribe and resubscribe cleanly on coordinator scope changes.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 7 - 84 tasks
Can start after Tier 6 completes
Implementation Notes
Use supabase.channel('attestation-queue-{chapterId}').onPostgresChanges(event: PostgresChangeEvent.all, schema: 'public', table: 'expense_claims', filter: PostgresChangeFilter(type: FilterType.eq, column: 'chapter_id', value: chapterId), callback: _handleRealtimeEvent).subscribe(). In _handleRealtimeEvent, for INSERT events fetch the full PendingClaim details via a single REST call (the Realtime payload only contains raw columns, not joined claimant name). For UPDATE events check the new.status field: if not 'pending-attestation', remove from local list. Override ref.onDispose() in the AsyncNotifier to call _channel.unsubscribe().
For the backgrounding/foreground reconciliation, use WidgetsBindingObserver in a separate provider that watches AppLifecycleState.resumed and calls ref.invalidate(attestationQueueProvider). Avoid storing the full PendingClaim in Realtime payloads — this keeps payload size minimal and avoids transmitting PII over the WebSocket.
Testing Requirements
Unit tests with a fake RealtimeChannel that emits synthetic payload events: (1) INSERT payload → new PendingClaim fetched and inserted at correct sort position; (2) UPDATE with status='approved' → claim removed from list; (3) UPDATE with status='pending-attestation' (re-opened) → claim added back; (4) channel disconnect → isConnected flag set false, reconciliation fetch triggered on reconnect; (5) provider disposal → channel.unsubscribe() called exactly once. Use flutter_test. Integration test on a staging Supabase project: open two Dart isolates, submit a claim from isolate B, verify isolate A's notifier receives the INSERT event and queue length increases by 1 within 5 seconds.
Mutual exclusion rules are stored in the expense type catalogue's exclusive_groups field. If the catalogue schema or group definitions differ between HLF and Blindeforbundet, the validation service must handle multiple group configurations without hardcoding organisation-specific logic.
Mitigation & Contingency
Mitigation: Design the validation service to be purely data-driven: it reads exclusive_groups from the cached catalogue and enforces whichever groups are defined, with no hardcoded organisation names. Write parameterised unit tests covering at least 4 different catalogue configurations to verify generality.
Contingency: If an organisation requires non-standard exclusion semantics (e.g. partial exclusion within a group), introduce an exclusion_type field to the catalogue schema and extend the service, treating it as a catalogue configuration change rather than a code fork.
The attestation service subscribes to Supabase Realtime for live queue updates. On mobile, Realtime WebSocket connections can be dropped during network transitions, causing the coordinator queue to become stale without the user being aware.
Mitigation & Contingency
Mitigation: Implement connection lifecycle management: reconnect on network-change events, show a 'reconnecting' indicator when the subscription is broken, and perform a full queue refresh on reconnect rather than relying solely on delta events.
Contingency: Add a manual pull-to-refresh gesture on the attestation queue screen as a guaranteed fallback. If Realtime proves unreliable in production, switch to periodic polling (30-second interval) as a degraded but functional mode.
If a peer mentor submits a draft while offline and then submits the same claim again after connectivity is restored (thinking the first attempt failed), duplicate claims may be persisted in Supabase.
Mitigation & Contingency
Mitigation: Assign a client-generated idempotency key (UUID) to each draft at creation time. The submission service sends this key as an upsert key to Supabase, preventing duplicate inserts. The draft is marked 'submitted' locally after first successful upload.
Contingency: Implement a server-side duplicate detection trigger on the expense_claims table checking (activity_id, claimant_id, created_date) within a 24-hour window and returning the existing record ID rather than inserting a duplicate.