Sync job status tracking and run log storage

epic-external-system-integration-configuration-backend-infrastructure-task-010 — Design and implement the sync_run_log database table and associated repository layer for the Sync Scheduler. The table tracks job start time, end time, status, records processed, records failed, error message, and triggered_by (scheduler vs manual). Implement write operations for status transitions and read operations for the admin dashboard to display last run results per integration.

high priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

sync_run_log table exists in Supabase with columns: id (uuid PK), org_id (uuid, NOT NULL), integration_type (text, NOT NULL), started_at (timestamptz, NOT NULL), ended_at (timestamptz), status (text CHECK IN ('pending','running','completed','failed','cancelled')), records_processed (integer DEFAULT 0), records_failed (integer DEFAULT 0), error_message (text), triggered_by (text CHECK IN ('scheduler','manual'), NOT NULL)

Row-Level Security policy on sync_run_log ensures coordinators/admins only read rows where org_id matches their JWT claim

Repository function insertRunLog(orgId, integrationType, triggeredBy) creates a new row with status='running' and returns the new row id

Repository function updateRunLogStatus(runId, status, recordsProcessed, recordsFailed, errorMessage) updates status and metrics atomically

Repository function getLastRunPerIntegration(orgId) returns the most recent run log row per distinct integration_type for the given org

Repository function getRunHistory(orgId, integrationType, limit) returns up to N most recent run log rows ordered by started_at DESC

All repository functions are implemented as Supabase Edge Function helpers or Postgres RPC functions — no direct table mutations from the mobile client

An index exists on (org_id, integration_type, started_at DESC) to support the dashboard query efficiently

Migrations are idempotent and include rollback SQL

Unit tests cover all status transition writes and all read queries with mocked Supabase client

Technical Requirements

frameworks

Supabase PostgreSQL 15

Supabase Edge Functions (Deno)

flutter_test

apis

Supabase REST API

Supabase Edge Functions (Deno)

data models

bufdir_export_audit_log

activity

performance requirements

getLastRunPerIntegration query must return in under 200ms for orgs with up to 10 integration types

Index on (org_id, integration_type, started_at DESC) required

Run log rows older than 90 days should be eligible for archival via a scheduled cleanup job

security requirements

RLS policy: org_id = auth.jwt() ->> 'org_id' on all SELECT/INSERT/UPDATE operations

Service role key used only server-side in Edge Functions — never in mobile client

error_message column must not contain raw stack traces or credentials; sanitise before storage

triggered_by column uses CHECK constraint to prevent arbitrary string injection

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Integration Task

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

View Full Execution Plan

Implementation Notes

Use a Postgres enum or CHECK constraint for status — prefer CHECK for easier future extension without a migration. The (org_id, integration_type, status='running') unique partial index needed by task-011 should be added here as a prerequisite. Implement updateRunLogStatus as an atomic UPDATE ... WHERE id = $runId AND status = 'running' to prevent accidental overwrites of already-completed runs.

Keep error_message truncated to 2000 characters maximum in the repository layer to avoid unbounded storage. Use Supabase Edge Function wrappers rather than direct PostgREST calls so server-side validation is always enforced. Consider adding a generated column duration_seconds = EXTRACT(EPOCH FROM ended_at - started_at) for dashboard display convenience.

Testing Requirements

Unit tests using flutter_test with mocked Supabase client covering: (1) insertRunLog creates correct initial row with status='running'; (2) updateRunLogStatus transitions all valid statuses; (3) updateRunLogStatus with null errorMessage clears previous error; (4) getLastRunPerIntegration returns only the latest row per integration_type; (5) getRunHistory respects limit parameter; (6) RLS rejects cross-org reads (integration test against local Supabase instance). Migration SQL tested for idempotency by running twice. At least 80% line coverage on repository layer.

Component

Sync Scheduler

service medium

Dependencies (1)

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. epic-external-system-integration-configuration-backend-infrastructure-task-007

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