Implement Threshold Validation Supabase Edge Function

epic-expense-approval-workflow-coordinator-ui-task-008 — Deploy a Supabase Edge Function that executes server-side threshold validation when a claim is submitted. The function reads org-specific threshold config, evaluates claim against rules (amount, distance, receipt presence), updates claim status to auto_approved or pending, and emits a claim event record. Ensures validation logic runs securely server-side.

critical priority medium complexity infrastructure pending infrastructure specialist Tier 3

Acceptance Criteria

Edge Function is invocable at POST /functions/v1/validate-claim-threshold with a JSON body containing claim_id

Function reads the org-specific threshold config row from the database using the claim's organisation_id

Claims with amount <= threshold AND distance <= km_threshold AND receipt not required → status set to auto_approved

Claims that fail any threshold rule → status set to pending

A claim_events row is inserted for every validation run with event_type='threshold_evaluated', actor_id=null (system), and a notes JSON payload describing which rules passed/failed

Function returns HTTP 200 with {claim_id, new_status, auto_approved: boolean} on success

Function returns HTTP 400 with {error: 'claim_not_found'} if claim_id does not exist

Function returns HTTP 422 with {error: 'no_threshold_config'} if the org has no threshold config row

Function is idempotent: re-running on an already-evaluated claim updates status and appends a new event without duplicating side effects

All database mutations run inside a single transaction — partial updates are not committed

Function enforces Row Level Security: caller must have a valid Supabase service-role JWT; user JWTs are rejected

Deployed and callable from the Flutter client via Supabase.instance.functions.invoke()

Technical Requirements

frameworks

Supabase Edge Functions (Deno)

Supabase JS client (server-side)

apis

Supabase REST API (internal)

Supabase Functions invocation API

data models

expense_claims (id, organisation_id, amount, distance_km, has_receipt, status)

organisation_threshold_config (organisation_id, amount_threshold, km_threshold, receipt_required_above)

claim_events (id, claim_id, event_type, actor_id, notes, created_at)

performance requirements

Function cold start + execution < 2 seconds for p95

Single DB round-trip for read (JOIN claims + config), one for status update, one for event insert — max 3 queries

security requirements

Reject requests without service-role JWT

Validate claim_id is a valid UUID before querying

Use parameterised queries only — no string interpolation in SQL

Do not expose internal error stack traces in HTTP response body

Threshold config is org-scoped — assert organisation_id match before applying rules

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

Structure the function as: (1) parse + validate input, (2) fetch claim + org config in a single JOIN query, (3) evaluate rules in pure functions (easy to unit test in isolation), (4) wrap status update + event insert in a Postgres transaction using supabaseClient.rpc() or raw SQL via the service role. Keep the rule evaluation logic in a separate evaluateThresholds(claim, config) pure function so it can be tested without a database. Use Deno's built-in test runner for unit tests on the pure logic. Log structured JSON to stdout for observability (Supabase logs surface this).

For the Flutter caller, wrap the invocation in a try/catch and map FunctionException to domain errors before surfacing to the BLoC. Consider a database trigger as an alternative, but an Edge Function is preferred here for testability and explicit invocation control.

Testing Requirements

Integration tests (Deno test runner): test all threshold rule combinations — amount only, distance only, receipt required, all rules met (auto_approve), at least one rule failed (pending). Test idempotency by invoking twice on the same claim. Test error paths: missing claim, missing org config, malformed UUID. Verify claim_events row is inserted on each invocation.

Verify HTTP status codes and response body shape. Flutter integration test: invoke the function from the app and assert the returned status updates the local BLoC state. Target 90% branch coverage on the function logic.

Component

Coordinator Review Queue Screen

ui high

Dependencies (1)

Develop the ThresholdEvaluationService that applies organization-configured thresholds to determine whether a submitted expense claim qualifies for auto-approval or must enter the coordinator review queue. Read threshold configuration per org (distance limit, amount limit, receipt requirement) and return a structured evaluation result. epic-expense-approval-workflow-coordinator-ui-task-005

Epic Risks (3)

medium impact medium prob technical

Maintaining multi-select state across paginated list pages is architecturally complex in Flutter with Riverpod/BLoC. If the selection state is stored in the widget tree rather than the state layer, page transitions and list redraws can silently clear selections, causing coordinators to lose their multi-select and re-enter it.

Mitigation & Contingency

Mitigation: Store the selected claim ID set in a dedicated Riverpod StateNotifier outside the paginated list widget tree. The paginated list reads selection state from this provider and does not own it. Selection persists independently of list scroll position or page loads.

Contingency: If cross-page selection proves prohibitively complex, limit bulk selection to the currently visible page (add a clear warning in the UI) and prioritise single-page bulk approval for the initial release.

medium impact medium prob integration

If a coordinator has the queue open while another coordinator approves claims from the same queue (possible in large organisations with shared chapter coverage), the Realtime update may arrive out of order or be missed during a reconnect, leaving the first coordinator's view stale and allowing them to attempt to approve an already-actioned claim.

Mitigation & Contingency

Mitigation: The ApprovalWorkflowService's optimistic locking (from the foundation epic) will catch the concurrent edit at the database level. The CoordinatorReviewQueueScreen should handle the resulting ConcurrencyException by removing the claim from the local list and showing a brief snackbar: 'This claim was already actioned by another coordinator.'

Contingency: Add a queue staleness indicator (a subtle 'last updated X seconds ago' label) and a manual refresh button as a fallback for coordinators who notice inconsistencies.

low impact high prob dependency

The end-to-end test requirement that a peer mentor receives a push notification within 30 seconds of coordinator approval depends on FCM delivery latency, which is outside the application's control and can vary significantly in CI/CD environments.

Mitigation & Contingency

Mitigation: Structure end-to-end tests to verify notification intent (correct FCM payload dispatched, correct Realtime event emitted) rather than actual device delivery timing. Use test doubles for FCM delivery in automated tests and reserve real-device delivery tests for manual pre-release validation.

Contingency: If notification timing requirements must be validated in automation, instrument the ApprovalNotificationService with a test hook that records dispatch timestamps and assert against those rather than actual FCM callbacks.

Quick Links

All Tasks Execution Plan