A Zoho CRM copy that only fetches what changed
The daily briefing needed its own copy of Zoho CRM. Each run asks only for records changed since the last good sync, upserts them by Zoho ID and keeps the whole record, custom fields included.
The problem
Re-downloading the whole CRM every day gets slower as it grows, and a fixed field list drops data.
The fix
Fetch only changed records, upsert them by Zoho ID, and keep each full record as JSON.
- 4
- Zoho modules copied on every run
- 1
- row per Zoho record, however often it changes
- 0
- fields filtered out of the copy
Before the briefing could say anything about the day, it needed a copy of the CRM it could query freely. Re-downloading all of Zoho every morning works in week one and gets slower every week after.
Ask only for what changed
Each run starts by reading a bookmark: the time of the last successful sync. For each of four Zoho modules (leads, deals, contacts and accounts), the job sends that time in an If-Modified-Since header, which asks Zoho only for records created or edited since then. When nothing has changed, Zoho sends back an empty answer and the module is skipped. The very first run has no bookmark, so it asks for everything.
Upsert by Zoho’s own ID
Every table uses the Zoho record ID as its primary key, and every write is an upsert: insert the record if the ID is new, update it if the ID is already there. A lead edited three times in a week is still one row, holding its latest version. It also makes the job safe to rerun. Running it twice leaves the CRM tables just as running it once would.
Keep the whole record
Each table has a few typed columns, the ones the SQL actually queries: owner, status or stage, source, amount and dates. The whole record also goes into raw_data, a JSONB column (Postgres’s binary JSON type) that stores it exactly as Zoho sent it.
That wasn’t the first design. An earlier version asked Zoho for a fixed list of fields. I removed the list so every field comes back, custom ones included, and added the JSON column to the existing leads table without losing a row. If a manager adds a field in Zoho tomorrow, it’s in the copy after the next run, with no schema change.
A bookmark that shows its work
Every run writes one row to a sync_logs table: when it ran, how many records came back and a status. The next run reads the latest successful row as its bookmark. The job logs a run even when nothing changed, so the dashboard’s System Health tab can chart records per run and show that the job is alive.
Two things that broke before any data moved
Tokens. Zoho access tokens last an hour. The job holds a long-lived refresh token and trades it for a fresh access token at the start of every run. If that trade fails, the run stops before it touches the database.
DNS. My ISP’s DNS was hijacking lookups for the database’s .co domain, so the job couldn’t reach it. Instead of changing the machine’s DNS settings, the job asks Google’s public resolver for the address over DNS-over-HTTPS, then points Python’s lookups for that one host at the answer. Every other lookup is untouched, and if the workaround itself fails, the job logs a warning and carries on.
What I’d change
Three things would make it sturdier:
- The bookmark moves even when a module fails. A failed fetch returns an empty list and the run still logs success, so the next run would skip that module’s missed changes. The bookmark should only move when all four modules succeed.
- It’s stamped when the run ends. A record edited mid-run could fall between two bookmarks. Stamping the time the run started closes that gap.
- Each module is one request, with no paging. That’s fine for a day’s changes, but a first full load of a large CRM needs to page through the results.
The rules I took from it
- Copy faithfully. Keep the raw record and decide what matters later.
- Upsert on the source’s own ID, so edits and reruns never duplicate.
- Move the bookmark only when everything succeeded.
- Refresh credentials at the start of every run, not after something breaks.