Sync Scheduler cron configuration reader

epic-external-system-integration-configuration-backend-infrastructure-task-007 — Implement the cron configuration reader for the Sync Scheduler that queries the organization_integrations table and reads each integration's cron_schedule field (e.g., '0 2 * * *' for 2am nightly). Parse and validate cron expressions, build a schedule map per organization-integration pair, and expose a function that returns which integrations are due to run at a given timestamp.

high priority medium complexity backend pending backend specialist Tier 0

Acceptance Criteria

Reads all rows from organization_integrations where is_enabled = true and cron_schedule IS NOT NULL

Parses standard 5-field cron expressions (minute hour day-of-month month day-of-week) correctly

Returns validation error for malformed cron expressions (e.g., '99 * * * *', '* * * * * *' with 6 fields) without crashing

getIntegrationsDueAt(timestamp: Date) returns an array of { orgId, integrationId, integrationType } for all integrations whose cron schedule fires at or within a 1-minute window of the given timestamp

Schedule map is rebuilt from database on each Scheduler invocation — no in-memory caching that could serve stale schedules after config changes

Integrations with cron_schedule = null or is_enabled = false are excluded from the result

Function handles time zones correctly — cron times interpreted as UTC unless the organization record specifies a timezone offset

Correctly handles month-end edge cases: '0 2 31 * *' does not fire in months with fewer than 31 days

Returns empty array (not error) when no integrations are due at the given timestamp

Database query uses a single efficient JOIN query, not N+1 queries per organization

Technical Requirements

frameworks

Supabase Edge Functions (Deno runtime)

cron-parser or equivalent Deno-compatible cron parsing library

apis

Supabase PostgreSQL — SELECT from organization_integrations table

performance requirements

Config read and parse for up to 100 organization-integration pairs must complete in under 100ms

Single SQL query to fetch all enabled integrations — no per-row round trips

security requirements

Reader uses service role for database access — never a user-scoped JWT that could be manipulated to exclude other orgs' schedules

Cron expression values from database must be validated before being passed to cron parser to prevent regex injection or parser exploits

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

Integration Task

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

View Full Execution Plan

Implementation Notes

Implement as cron-config-reader.ts with a single exported async function readDueIntegrations(atTime: Date, supabaseClient: SupabaseClient): Promise. Inject the Supabase client as a parameter for testability. For cron parsing, evaluate cronstrue or a pure Deno cron-parser — confirm the library handles 5-field standard cron (not 6-field quartz). Match window: check if the cron fires at any minute within [atTime - 30s, atTime + 30s] to handle scheduler invocation timing drift.

Store validated DueIntegration as { orgId: string, integrationId: string, integrationType: 'xledger' | 'dynamics' | 'bufdir' } to give the trigger engine type safety. Log a warning (not error) for any rows with invalid cron expressions so an org misconfiguration does not crash the entire scheduler run.

Testing Requirements

Unit tests: (1) valid cron '0 2 * * *' — fires at 02:00 UTC, not at 02:01; (2) invalid cron '99 * * * *' — returns validation error; (3) is_enabled = false — excluded from results; (4) cron_schedule = null — excluded; (5) month-end '0 2 31 * *' — does not fire on 30 November; (6) multiple orgs with overlapping schedules — all returned correctly. Use mock Supabase client injected at test time to avoid real DB dependency. Integration test: seed organization_integrations table locally and assert getIntegrationsDueAt returns correct subset for a known timestamp.

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).

Quick Links

All Tasks Execution Plan