A 15-minute warehouse that runs on one PC

AHA’s sales BI was only as fresh as its last manual sync. Deal and lead statuses change all day, so the brief was simple: what’s on screen can’t be out of date.

What the old copy got wrong

The BI started as a copy of the one in AHA’s earlier growth-intelligence platform, so it read that platform’s copy of Zoho CRM. That copy still feeds the map pages, but it was never designed for a BI:

• It was refreshed by hand, and its daily scheduled sync wasn’t running.
• A failed sync still reported success, because the puller caught its own errors.
• Phone numbers had been left out since June, Performance Max spend wasn’t stored, and nothing kept stage history, so nobody could see when a deal was really won or lost.

Every fix meant changing code the map pages depended on. So the BI got a copy of its own.

Three layers, one way in

Between 9 and 15 Oct I added read-only pullers for each source, built with Claude Code: I set the design and the checks, and it wrote much of the Python and SQL.

• Zoho: every lead and deal with every field, every change a pull sees, deletions (marked, never removed), stage history, notes and calls.
• Google Ads: daily totals for every campaign type, Performance Max included. The whole history since January 2025 is re-read twice a day, so late conversions land.
• Meta: every ad, every day.
• Call recordings: copied into a private storage bucket that only the admin can play.

Everything lands in its own Postgres schema, in three layers: raw (as each source sent it), clean (the business rules applied once) and per consultant (month, quarter, open deals). The API can’t touch its tables directly. The BI reads through a set of functions, the pullers write through locked ones that need the service key, and each layer was checked equal to its source before anything read it.

The schedule

Windows Task Scheduler runs it all:

every 15 min   every deal, changed leads, deletions, stage history
               rebuild the deal views (readers keep the old rows meanwhile)
               changed notes and calls, new recordings
               hourly: ad status
07:30, 19:30   re-read every lead and deal, full ad history
Sync button    the 15-minute pull, on demand

A 15-minute run takes about 40 seconds, and a lock stops two runs from overlapping. The twice-daily full pass exists because change stamps aren’t perfect. The first one found leads that Zoho had changed without updating their modified time. None of those changes touched a status, rating or assignment, but that drift is exactly what a full re-read is for.

The admin’s Sync button leaves a request in the database, and a watcher on the PC checks for one every 5 seconds. The BI’s “Updated” time is the moment Zoho was read, not the moment the job finished.

Does it hold up?

• Equal to the source. A live check on 12 Oct matched every deal’s id and last-change time to Zoho, and so did the 200 leads and 200 notes changed most recently. Google spend agreed with the audited figures within 0.001% over 154 days, and Meta within a rupee over 371 days.
• Reliable. From 10 to 27 Oct the live pull ran just over 500 times, and 99.2% of runs succeeded. All four failures were network errors.
• Quick on demand. 39 syncs from the button, with a median of 44 seconds.
• Fast to load. Moving the BI’s start-up reads into purpose-built functions cut start-up checks from 0.69 s to 0.23 s, with 4 requests on load instead of 12.

The limit: it’s one PC

Scheduled tasks run only while the PC is on and someone is logged in. When it’s off, the BI keeps the last copy, says how old it is, and shows a banner after 2 hours. Most nights nothing runs until the PC is back on, and three times so far the copy went 31 to 43 hours without a read. Moving the pulls to AWS is planned, and needs the keys stored there. It isn’t done yet.

What I’d keep

1. Give the BI its own copy. Fixing a shared one means changing code other tools rely on.
2. One way in. When only locked functions can write, nothing else quietly changes the data.
3. Pair a fast pull with a slow one. Incremental reads are only as good as the source’s change stamps.
4. Show when the source was read, not when the job ended.
5. Log every run. “Is it working?” should be a count, not a feeling.