[Self-Chaining]
Sync Engine: Legacy MySQL → Postgres Mirror
Edge functions that copy only what changed, one unit per call, with checksums, shadow tables and atomic publish.
The dashboard hub runs on Postgres, but the numbers are built in a legacy MySQL that rebuilds its tables about once an hour. Browsers can't query that MySQL.
Designed and built the sync functions, the scheduling and the sync log shown in the UI.
Copying everything blew the edge-function limits (memory, CPU, a 400-second wall clock), and when a chain died mid-run nobody noticed: dashboards just went stale.
01 · Unit = table × month
Each invocation processes one unit and then calls itself for the next (self-chaining), so no run gets near the limits.
02 · Checksums decide what moves
COUNT, sums of numeric columns and total text length per month, on both sides. Only months that differ are re-synced.
03 · Shadow table + atomic publish
A unit is written to a shadow table and swapped in inside one transaction; readers never see half a month.
04 · Curated aggregates
The mirror stores day × dimension aggregates and exact percentiles instead of millions of raw rows.
05 · Pending work per chain
The daily rebuild runs in 4 slices and each chain records what it still owes. A dead chain shows up as pending work, not as “last run OK”.
06 · Freshness on screen
pg_cron + pg_net trigger runs every 2 hours; a “last updated” badge reads the sync log.
what you're seeing
Left: the legacy database, one row per month with COUNT and SUM. Right: the Postgres mirror. In the middle, the edge function: it compares checksums, queues the months that differ and processes one per call. Below, the daily rebuild split in 4 chains, each tracking what it still owes.try this
- 1.Click Mutate legacy, then Run sync: only the amber months travel.
- 2.Switch to “all at once” and run again — memory blows past the limit.
- 3.In the daily rebuild, turn on “chain dies silently”: the dead chain stays visible as pending work.
real vs simplified
Units, checksums, shadow table, atomic publish and pending-per-chain are how the real sync works. Tables, sizes and the memory limit are illustrative.! brokeThe daily slice died at the 400-second limit when the MySQL was saturated by other teams' queries.
→ changed4 slices, and the pending list computed only on the first hop — later hops went from about a minute to a few seconds.
! brokeA nightly deep check had been dying silently for days, freezing one panel at an old date.
→ changedMoved it to the normal window; freshness is now checked on the data, not on the log.
! brokeA reprocessed source changed its ID format and the upsert-only mirror kept every old version, inflating a ROI by more than half.
→ changedA prune step removes rows that no longer exist at the source.