case_study
Checkpointed CRM migration engine
Rails 8 · GoodJob · Postgres · a metered third-party API
Lead backend engineer — engine, performance, recovery
- Ruby on Rails 8
- GoodJob
- PostgreSQL
- pg_trgm
- Next.js
- TypeScript
- AWS
problem
The problem
The first importer was one long job per run. A deploy killed it, it could not say where it was, and retrying meant re-fetching everything against a daily token budget. The first real customer had 400,000 records and — nobody knew this in advance — 90,000 emails attached to their deals, each needing its own API call.
Correctness came first and was hard-won: an idempotency ledger that quietly blocked 63,164 records forever, a retry that stranded a quarter of a million rows behind a phase barrier and then reported success, and an email phase that ran for twenty hours while the database sat at 11% and every theory about why was wrong.
approach
What I engineered
- A checkpointed task engine on GoodJob: a plan of dependent phases, thousands of tasks that each own a cursor and a checkpoint, fenced claims, pages written in 25-row windows with a savepoint per row, and a phase barrier advanced by a single fenced UPDATE — five levels of batching nested inside one another, scheduled by a per-run in-flight limit, striped page walks and a reaper, on a four-thread lane of a Postgres-backed queue.
- A write-once ledger keyed on (tenant, source, kind, source id), claimed in the same transaction as every write, with the rule that a skip is a placeholder and only a created or matched row is final — the one SQL clause that unblocked the 63,164 records, plus a fourth outcome, already-here, so a rerun's numbers stop alarming.
- Retry that rewinds the barrier to where the failed work lives, a finaliser that refuses to declare success while work is queued, and Resume for stopped runs under the same admission rule that forbids two live runs per workspace.
- Measurement before optimisation: one query grouping task duration by the task's own payload showed the slow phase was latency-bound (1.25 s per message, serial), which ranked the levers and ruled out four infrastructure theories.
- Concurrency inside the job thread: four fetch threads per task that do HTTP only under the GVL, never touch the connection pool, and hand everything to one transaction per deal — with the per-account rate-budget row taken off the hot path (154,000 writes → a few thousand) and the queue lane widened only after the write cuts and a capacity audit.
- A ten-fold global search fix in the same product: a cross-table OR behind a LEFT JOIN rewritten as index-reachable UNION arms, a per-token trigram floor, and the matching moved out of the controller into a query object.
result
The result
The customer's data is in, once: 77,187 deals, 155,000 contacts, 57,000 notes and 90,000 emails, verified deal by deal, with every reason for anything left behind readable from the ledger. Recovery runs by entity take minutes and produce no duplicates.
A deploy mid-run costs thirty minutes, not a day. A stopped run resumes. The next customer gets a run page that says imported, already here, skipped or failed and means it, and an engine whose email phase is modelled at about four times the throughput of the one that took twenty hours.
- 400k+
- records migrated, once each
- 63,164
- records unblocked by one SQL clause
- 11 s → 0.8 s
- worst-case global search
- 0
- duplicates across four reruns
Measured on the staging environment during a customer migration in September 2026. The product and the customer are not named. The email-phase speed-up after the rework is modelled from measured task durations and has not yet been measured on a full run.
work_with_me
Have a project like this?
Tell me the problem on a 20-minute call and I'll tell you how I'd approach it, the same way Checkpointed CRM migration engine started.
Discuss your project