Drift monitor
- Postgres
- SQL
- Python
- Cloud Run
- Slack
The problem
In September 2026 we cleaned the four main Salesforce objects (leads, contacts, accounts, opportunities) by hand and agreed rules for each. Every lead sits on the right account. Every account has one real domain and a real name. Every deal has people and an amount. Nothing is owned by someone who left.
The drift spec I had then was 30 checks, run by hand against the live org. A re-baseline on 22 September showed what that costs. A round of account merges had written 64 domain alias rows to a review sheet, but only 1 had reached Salesforce. 10 merged-away domains were covered by nothing, and 56 open leads sat on them. One automation run would have re-created the duplicate accounts we had just merged.
Constraints
- Salesforce forgets. Without the paid Field Audit Trail add-on, it keeps field history for about 18 months and then deletes it. If I wanted to know what a record looked like last year, I had to keep the copy myself.
- The org changes weekly. Admins add fields in Setup all the time. A copy with a hard-coded field list would quickly go stale.
- A partial pull must never look like a mass delete. If tonight's copy comes back short, the system must not decide the missing leads were deleted.
- The post has to stay readable. Some offenders are correct by policy. If they stay red forever, people stop reading.
How it works
The system has two parts: a mirror (a nightly copy of Salesforce in Postgres) and the monitor (SQL checks that read the copy).
Each night Salesforce is copied into Postgres. A short pull writes nothing. A changed record closes its old row and adds a new one. The 56 SQL checks then read the current rows, drop accepted exceptions, and report to a console page and a Monday Slack table.
- Copy everything, every night. At 01:00 UTC the job copies 26 objects: about 157,000 rows and 1,176 fields, field history included. A field added in Setup is copied the next night with no code change.
- Keep every version. This is a "type-2 history" table. In plain words: I never overwrite a row. When a record changes, its current row gets an end date and a new row starts. The table becomes a stack of dated versions, so "what did this account look like on 1 October?" is one date filter. To spot a change, I hash each record (a short fingerprint of all its values) and compare it with last night's. Same fingerprint, nothing is written.
- Deleted records stay, marked. A record that vanishes is looked up once more to learn whether it was deleted, merged (and into which record) or purged. Its last row stays, marked.
- Run the checks. There are 56 checks, one SQL file each: 19 lead, 7 contact, 19 account, 11 opportunity. Each has a kind: alert (red on any new offender), trend (red when the count climbs) or track (counted, never red).
- Subtract what's accepted. An exceptions table holds records that are correct by policy, such as shared domains in multi-entity families. The count, the rows and the CSV only show new drift.
- Report. A console page lists each check with its rows and a CSV export. Every Monday at 09:00 IST, a Slack table lists the alert checks that have something new. On a clean week, nothing is posted.
Receipts
- Before
- 30 checks run by hand; 10 merged domains unguarded
- Action
- Nightly full-history mirror + 56 SQL checks
- After
- 52 of 61 planned checks fully captured on day one
- Verified by
- 6 checks matched the September hand counts exactly
- On the first run, 6 checks matched the September hand counts exactly, so the SQL reads the data the way the cleanup did.
- A second sandbox run pulled 425 records and wrote 0 new versions. A copy that changes nothing writes nothing.
- Projected storage is about 1.4 GB after a year, and a night costs about 40 API calls.
What broke, and what I changed
- The count guard tripped on its first prod run. I first compared each pull with a separate
COUNT()query. For one object, COUNT said 10 rows but the integration user could only see 5. Now I compare with the query's own total, which uses the same visibility as the rows. A short pull is logged and nothing is written. - The first field-history design would have lost rows. I planned to pull only history rows newer than the last run. Prod showed why that fails. History is filtered by field access, so a permission change suddenly reveals older rows, which a "newer than" pull would skip forever. History is now pulled whole every night.
- The mass-delete alarm cried wolf. The alarm fires when more than 2% of an object's rows (or more than 200) vanish in one night. Then an admin deleted a tracked field. Salesforce dropped its 551 history rows, and the alarm fired. Those rows are still marked, but they no longer count toward the alarm.
- Some checks weren't worth watching. I dropped 4 checks that flagged states the design allows, and re-graded the rest. An alert now means every row is a fix someone can make this week.
What I'd do next
- Build the five missing checks. One needs read access to Salesforce's duplicate rules. Two need a weekly web crawl (name-variant twins, dead or parked domains). One needs the customer health list as data. One needs an account-stage rule ported to SQL.
- Mirror activities (tasks, events, emails) as their own pipeline, so the stage checks read raw activity instead of stamps.
- Stop drift at the source. Turn the amount and contact-role rules into Salesforce validation rules. Then decide whether the job should run the mechanical fixes itself, like seeding alias rows or reassigning owners.