A silent data-corruption bug in a Laravel sync pipeline

Context

• Project type: a Laravel application handling workforce time-tracking and billability reporting, syncing data from a third-party time-tracking platform into internal monthly and yearly reporting tables.
• Environment: Laravel/PHP, MySQL, Laravel's queue and scheduler system, a third-party REST reporting API.
• Constraint: several interdependent scheduled jobs synced and aggregated time-tracking data on a recurring basis (the core sync running every 15–30 minutes, plus dependent monthly/yearly report-generation jobs), and the sync logic had grown organically over time around a fixed, ever-widening historical date range.

Problem

Previously-correct billable and total hours for some users started silently resetting to zero in the monthly and yearly reporting tables, with no visible error. Independently, the sync jobs themselves were becoming less reliable, with intermittent failures against the third-party API increasing over time.

Diagnosis

Traced the zero-hour records back to how the sync service handled a failed API call: on failure it returned an empty result, and the calling code's fallback logic treated "no data returned" identically to "genuinely zero hours," then persisted that zero straight into the reporting tables — silently overwriting previously correct values. The failures themselves traced back to the sync jobs re-requesting an ever-growing, multi-year date range on every run (a fixed historical start date that never advanced), for every user including long-inactive ones, on a 15-minute cadence — a combination that was pushing past the third-party API's practical rate and range limits. Separately, several report-generation jobs that read and wrote overlapping rows were scheduled at the same or overlapping cadence, occasionally producing MySQL deadlocks under concurrent execution.

API callbounded sync window

● ok

Write hoursvalue from API

●report updated

✕ fail

null ≠ 0failure kept distinct

Skip writeexisting data kept

✕fail · nothing overwritten

▲beforea failed call was saved as 0 hours

Diagram of the fixed sync. A sync job makes an API call over a bounded sync window, and the result splits into two paths. Ok path: the hours from the API are written and the report is updated. Fail path: the result is null, kept distinct from zero, so the write is skipped and the existing data is kept. Nothing is overwritten. Warning, before the fix: a failed call was saved as 0 hours.

Actions

• Made the failure path distinguishable from a genuine zero: changed the sync service to return a null result (not an empty one) on a failed or malformed API response, and updated every caller to skip writing for that period entirely on a null result rather than defaulting to zero.
• Bounded the sync window to a rolling lookback period instead of an ever-growing fixed start date, and scoped it to active users only, cutting both request volume and the amount of historical data re-requested on every run.
• Reduced request volume further by increasing the API page size, lowering the sync job's frequency, and excluding its run time from overlapping with a separate heavy nightly sync job.
• Built and ran a one-off repair command that found every reporting-table row with zero recorded hours for an active user, re-fetched that specific user/month from the third-party API, and corrected the row wherever real data was available — repairing the damage the original bug had already caused.
• Iterated on the sync design itself: an initial fix introduced an incremental, cache-based "sync cursor" to avoid re-fetching the same months repeatedly, then deliberately simplified this back to direct, bounded per-month requests once the simpler throttling changes proved sufficient on their own.
• Fixed the deadlocks by staggering the overlapping report-generation jobs onto offset schedules so they no longer executed against the same rows concurrently, and added a guard so a report-generation job would not overwrite an already-correct nonzero value with a zero one, as a second line of defense against the same class of bug recurring elsewhere.

Result

• Verified operational result: eliminated the specific failure-treated-as-zero code path across the affected jobs, repaired previously corrupted zero-hour records for active users via the backfill command, reduced third-party API request volume and date range per sync run, and removed the concurrent-write pattern that was causing database deadlocks between report-generation jobs.

Relevant capabilities

Laravel/PHP production debugging, MySQL concurrency/deadlock diagnosis, third-party REST API integration reliability (rate/range limiting, error handling), data-integrity repair tooling (one-off backfill/repair commands), and scheduled-job/queue design for recurring sync pipelines.

Capability 01Escalation & maintenanceSenior help when your team is stuck: production debugging, incident diagnosis, performance investigations, and ongoing PHP, Laravel and WordPress maintenance, all behind your process.