Manual trigger support for Sync Scheduler

epic-external-system-integration-configuration-backend-infrastructure-task-009 — Add manual trigger capability to the Sync Scheduler allowing coordinators and admins to initiate an integration sync outside the scheduled window. Expose a Supabase RPC function trigger_integration_sync(org_id, integration_type) that validates the caller's permissions, checks that no active run is already in progress for the integration, and invokes the same job trigger engine used by the scheduled path.

high priority medium complexity api pending api specialist Tier 4

Acceptance Criteria

RPC function trigger_integration_sync(org_id UUID, integration_type TEXT) is callable by Flutter client via supabase.rpc()

Caller must have role = 'coordinator' or role = 'admin' within the specified org_id — other roles receive error code 'permission_denied'

Caller's JWT org_id claim must match the org_id parameter — cross-org trigger returns error code 'org_scope_violation'

If sync_run_log contains a row with status = 'running' or 'pending' for the same (org_id, integration_type) pair, RPC returns error code 'sync_already_in_progress' without creating a new run

On success, RPC returns the new run_id (UUID) so the Flutter client can poll for status

RPC delegates execution to the same job trigger engine used by the scheduled path — no duplicated trigger logic

Manual trigger is recorded in sync_run_log with triggered_by = 'manual' and triggered_by_user_id = caller's user ID

RPC enforces a rate limit: maximum 1 manual trigger per (org_id, integration_type) per 5 minutes

Invalid integration_type values not in the supported set return error code 'unknown_integration_type'

Flutter Riverpod provider exposes triggerManualSync(orgId, integrationType) returning AsyncValue<String> (run_id or error)

Technical Requirements

frameworks

Supabase PostgreSQL RPC (PL/pgSQL or SQL function)

Flutter (Dart)

Riverpod

Supabase Edge Functions (Deno) — job trigger engine invocation

apis

Supabase RPC — trigger_integration_sync function

Supabase PostgreSQL — sync_run_log, organization_integrations tables

Integration Edge Function — invoked by trigger engine

performance requirements

RPC function must respond within 500ms for the permission/conflict check phase before delegating to async trigger

Trigger engine invocation from RPC should be async (fire-and-forget) so the RPC returns run_id immediately without waiting for sync completion

security requirements

RPC uses SECURITY DEFINER with explicit permission checks — do not rely solely on RLS for authorization

Rate limit state stored server-side (in sync_run_log timestamps) — not client-side

triggered_by_user_id must be extracted from auth.uid() inside the RPC function, never from the function parameter

Supported integration_type values must be validated against a hardcoded allowlist in the RPC to prevent SQL injection via the parameter

Manual trigger audit trail (user_id, timestamp, org_id, integration_type) must be retained for GDPR accountability logging

ui components

ManualSyncButton widget (Flutter) — shows loading state while run_id is pending

SyncStatusBanner widget — displays current run status polled via Supabase Realtime on sync_run_log row

Execution Context

Execution Tier

Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

Integration Task

Handles integration between different epics or system components. Requires coordination across multiple development streams.

View Full Execution Plan

Implementation Notes

Implement the RPC as a SQL SECURITY DEFINER function in a migration file. The function should: (1) call auth.uid() and auth.jwt() to get caller identity — never trust function parameters for security checks; (2) join users/roles table to verify coordinator/admin role for the org; (3) check sync_run_log for active runs using EXISTS query; (4) check rate limit with SELECT COUNT(*) WHERE triggered_at > NOW() - INTERVAL '5 minutes'; (5) insert pending sync_run_log row with triggered_by = 'manual'; (6) invoke the Integration Edge Function asynchronously via pg_net.http_post() or return the run_id and let the caller poll. On the Flutter side, implement a SyncSchedulerRepository with a triggerManualSync method wrapping supabase.rpc(). Wire into a Riverpod AsyncNotifier.

The SyncStatusBanner should subscribe to Supabase Realtime changes on sync_run_log filtered by run_id to show real-time status updates without polling.

Testing Requirements

Unit tests (Dart flutter_test): (1) Riverpod provider calls supabase.rpc with correct params; (2) 'sync_already_in_progress' error surfaced as AsyncError with user-facing message; (3) successful return populates run_id state. Unit tests (Deno, RPC logic extracted to TS helper): (1) coordinator role allowed; (2) viewer role rejected; (3) cross-org org_id rejected; (4) rate limit: second trigger within 5 min rejected; (5) unknown integration_type rejected. Integration test: invoke RPC via Supabase local dev as coordinator user, assert sync_run_log row created with status 'pending', triggered_by = 'manual', triggered_by_user_id set. Widget test: ManualSyncButton shows CircularProgressIndicator while AsyncValue is loading.

Component

Sync Scheduler

service medium

Dependencies (1)

Build the core job trigger engine that executes on a pg_cron or Supabase scheduled Edge Function invocation. The engine reads the due integrations from the cron config reader, creates export job records in the sync_run_log table with status 'pending', invokes the Integration Edge Function for each due integration, and updates job status to 'running', 'completed', or 'failed' based on the response. epic-external-system-integration-configuration-backend-infrastructure-task-008

Epic Risks (3)

medium impact medium prob technical

Supabase Edge Functions have cold start latency that can cause the first sync invocation after idle periods to fail or timeout when the external API has a short connection window, leading to missed scheduled syncs that go undetected.

Mitigation & Contingency

Mitigation: Configure Edge Function memory and implement a warm-up ping mechanism before heavy sync invocations. Set generous timeout values on the external API calls. Log all cold-start incidents for monitoring.

Contingency: If cold starts cause consistent sync failures, migrate the sync scheduler to a persistent Supabase cron job that pre-warms the function 30 seconds before the scheduled sync time.

high impact low prob technical

The sync scheduler must execute jobs at predictable times for financial reporting accuracy. Drift in cron execution timing (due to Supabase infrastructure delays) could cause syncs to run at wrong times, leading to missing data in accounting exports or duplicate exports across reporting periods.

Mitigation & Contingency

Mitigation: Implement idempotency keys based on integration ID + scheduled period, so re-runs of a delayed sync cannot create duplicate exports. Log actual execution timestamps vs scheduled timestamps and alert on drift exceeding 5 minutes.

Contingency: If scheduler reliability is insufficient, integrate with a dedicated cron service (e.g., pg_cron on Supabase) for millisecond-precise scheduling, replacing the application-level scheduler.

high impact medium prob integration

Aggressive health monitoring ping frequency could trigger rate limiting on external APIs (especially Xledger and Dynamics), causing legitimate export calls to fail after the monitor exhausts the API's request quota.

Mitigation & Contingency

Mitigation: Use lightweight health check endpoints (HEAD requests or vendor-specific ping/status endpoints) rather than data requests. Set health check frequency to once per 15 minutes minimum. Implement exponential backoff after consecutive failures.

Contingency: If rate limiting occurs, disable active health monitoring for the affected integration type and switch to passive health detection (mark unhealthy only when a scheduled sync fails).

Quick Links

All Tasks Execution Plan