critical priority high complexity backend pending backend specialist Tier 3

Acceptance Criteria

On invocation, reads due integrations via cron config reader and processes all of them in a single run
For each due integration, inserts a sync_run_log row with status = 'pending' and a generated run_id before invoking the Integration Edge Function
Updates sync_run_log row to status = 'running' with started_at timestamp immediately before dispatching to Integration Edge Function
Updates sync_run_log row to status = 'completed' with completed_at and records_exported count on successful Integration Edge Function response
Updates sync_run_log row to status = 'failed' with failed_at, error_code, and error_message on failure or timeout
Concurrent invocations of the Scheduler (e.g., two pg_cron triggers firing close together) do not create duplicate sync runs — uses advisory lock or SELECT FOR UPDATE SKIP LOCKED on sync_run_log
If an integration already has a row in sync_run_log with status = 'running' started within the last 30 minutes, the engine skips it and logs a warning
Integrations are invoked in parallel (Promise.all or bounded concurrency pool of max 5) to minimize total wall-clock time
Engine returns a structured summary: { triggered: number, skipped: number, failed: number, runIds: string[] }
All status transitions are atomic — uses database transactions to prevent partial updates on crash

Technical Requirements

frameworks
Supabase Edge Functions (Deno runtime)
Supabase PostgreSQL — sync_run_log table with advisory locks
apis
Integration Edge Function — internal Supabase function invocation via supabase.functions.invoke()
Supabase PostgreSQL — sync_run_log CRUD
performance requirements
Engine must process up to 20 concurrent organization-integration pairs within 50-second wall-clock budget (Edge Function limit is 60s)
Parallel invocation concurrency capped at 5 to avoid overwhelming the Integration Edge Function or external APIs
sync_run_log inserts must use a single batch INSERT, not per-row inserts
security requirements
Engine uses service role key for all Supabase operations — never user JWT
Integration Edge Function is invoked with service role Authorization header so the auth guard (task-006) accepts it
run_id values are UUIDs — never sequential to prevent enumeration of sync history
sync_run_log rows must include org_id for RLS to prevent cross-org log visibility

Execution Context

Execution Tier
Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

Integration Task

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

Implementation Notes

Implement as job-trigger-engine.ts with a single exported async function runScheduledJobs(supabaseClient, integrationEdgeFunctionUrl). Use a p-limit or manual concurrency pool (not raw Promise.all on large arrays) to cap parallelism at 5. For duplicate prevention, use Postgres advisory locks: SELECT pg_try_advisory_xact_lock(hashtext(org_id || integration_id)) inside a transaction before inserting the pending row. If the lock is not acquired, skip.

Status transitions: use UPDATE ... WHERE status = 'pending' RETURNING * to detect race conditions — if 0 rows updated, another instance won the race. For the structured summary, collect results from all Promise.allSettled results and build the summary before returning. Handle the case where the cron config reader returns 0 due integrations gracefully — return immediately with triggered: 0 without error.

Testing Requirements

Unit tests with mocked Supabase client and Integration Edge Function: (1) single due integration → inserts pending, updates to running, updates to completed; (2) Integration EF returns error → updates to failed with error captured; (3) already-running integration (within 30 min) → skipped, not duplicate-triggered; (4) two concurrent engine calls → advisory lock prevents duplicate runs; (5) parallel execution — 5 integrations triggered concurrently, all complete. Integration test: full end-to-end with Supabase local dev — seed organization_integrations with 3 due entries, invoke scheduler, assert sync_run_log contains 3 completed rows. Load test: 20 due integrations complete within 50s.

Component
Sync Scheduler
service medium
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).