A nightly sync that survives its own failures

Every screen in the growth-intelligence platform reads from one Postgres database, fed by a daily sync that talks to Zoho CRM, Google Ads, Meta Ads, Google’s geocoder and Claude. On any given day, one of them can fail. The real question was what the other steps should do when that happens.

Claude Code wrote most of the scripts. My job was deciding how each one is allowed to fail.

One script, 25 steps

The orchestrator runs 25 steps in order: leads and deals through Zoho’s Bulk Read API, demos and lead notes, call recordings and electrical-plan PDFs, Google and Meta ads, AI summaries and call analysis, geocoding, enrichment, and a quality check at the end. From 27 April a GitHub Action scheduled it for 08:00 IST every day, and it also shipped as a nightly cron job.

If all 25 had to succeed, one flaky API would hold back the rest. So every step is one of two kinds.

Two kinds of failure

• Core (9 steps): the leads, deals and demos pulls, recordings and plan PDFs, the OCR that reads those PDFs, geocoding and the buildings rebuild. Everything downstream reads from them, so if one fails, the run stops and writes “failed” and the step’s name to a status row.
• Optional (16 steps): ads, AI summaries, call analysis, enrichment and the quality check, among others. These are marked non-fatal: a failure logs a warning and the run moves on. An expired ads token is no reason to hold back the CRM data.

for (const step of steps) {
  try { run(step) }
  catch (err) {
    if (step.nonFatal) { warn(step); continue }  // log it, keep going
    writeStatus('failed', step.name); exit(1)     // stop, and say where
  }
}
writeStatus('success')

Every main page shows a freshness badge read from that status row: green under 12 hours, amber up to 36, red after that or when the last run failed. Hovering over it shows where a failed run stopped.

Failing small inside a step

• Attachments. The recording and plan-PDF syncs re-check every attachment on every run instead of keeping a cursor. It costs more, but it heals itself: a file missed yesterday lands today. Each file is handled on its own, so one bad attachment can’t halt the scan, and a retry overwrites instead of leaving an orphan.
• Meta Ads. Meta rejects large day-by-day requests, so the puller asks in 30-day chunks. A failed chunk is logged and skipped, and the rest still lands.
• Pinned windows. A normal run re-pulls only the last 7 days of Google Ads and 28 days of Meta, because Meta keeps attributing late conversions to past days. The windows are pinned in the orchestrator, so a backfill setting left in a config file can’t turn a daily run into a full-history re-pull.

A check that grades every run

The last step runs 28 checks: totals that shouldn’t collapse; records missing IDs, statuses, sources or coordinates; drops in leads, deals or demos since the previous run; and stored files reconciled against table rows, so a recording without a row, or a row pointing at nothing, shows up straight away. It grades the run; it doesn’t block it.

The log keeps the last 30 runs. Across those, from 12 August to 9 October, 93% of checks passed. The only flags were the same two every time: leads with no status, and demos with no lead source. Both are CRM data-entry gaps rather than sync failures, so the check keeps them visible until someone fills them in.

What clean pulls exposed

Getting the ads pullers right exposed an older problem. The ROAS page had been reading spend from an abandoned table, and its spend was badly inflated. Once the Meta puller could backfill history in chunks, I checked that it covered every ad the old table had, then pointed the spend views at it. It was a database-only change, and those totals now refresh with every run.

Rules I’d keep

1. Decide per step what failure means. Stop for data everything else depends on; warn for the rest.
2. Fail small. One bad file, chunk or token should cost one item, not the run.
3. Pin windows in code, not in a config file someone forgot to reset.
4. Grade every run and keep the history. A flag that appears every time is a process problem, not a bug.
5. Show freshness on every page, so nobody has to ask whether the data is current.

A sync is only as good as its schedule. By October the daily run had stopped and one puller was swallowing its own errors, which is why the AI growth agent later got a live warehouse of its own, refreshed every 15 minutes.