How do you automate on CRM fields that another system owns?

01The business problem

For most organisations of any size, the CRM is not the system of record for the facts that matter most commercially. Whether a subscription is being paid, when the contract ends, how many seats are licensed, how much the customer has spent to date, whether their access is currently suspended — these are owned by billing, by provisioning, by an ERP. They arrive in the CRM by replication.

And yet almost every automation the business actually cares about branches on exactly those fields. The renewal reminder, the churn alert, the "this account went quiet" escalation, the revenue report, the renewal forecast — all of them gate on data the CRM did not produce and cannot verify.

So the business asks the CRM to be authoritative about things it is only repeating. That is a perfectly reasonable architecture, and it is the normal one. The question is what you have to do differently because of it.

The honest framing: your CRM automation has a dependency it cannot see, cannot test, and will not be told about when it breaks.

02Why the obvious approach fails

The obvious approach is to sync the field in and then use it exactly as you would use a field a person typed. Four things go wrong, in increasing order of severity.

The field is writable, so somebody writes to it

A support agent sees a subscription end date that looks wrong and corrects it. It is correct for about a day. The next replication cycle overwrites it, silently, and now the audit trail records a human making a change that was reverted by a machine for reasons nobody documented. Worse, in the window between the two, automation fired on the corrected value.

A mirrored field that anyone can edit is not a mirror. It is a second source of truth with no reconciliation.

Automation branches directly on the upstream vocabulary

It is natural to write the process as "when upstream status becomes Cancelled, do the cancellation work". Do that in twenty places and the upstream system's enum has become your CRM's public API.

We have seen a single replicated status field read by more than a dozen distinct processes. When the upstream adds a value — and it will, because it is a live system with its own roadmap — every one of those processes silently routes the new value into whatever its fall-through branch happens to be. Usually that branch is "do nothing", which produces no error and no record that anything was missed.

A field changing is not the same as the event happening

When a replicated field changes, what you have learned is that the sync ran. The change timestamp is the replication time, not the business event time. If your process responds by writing Date lost = today, you have recorded the date the CRM found out, not the date the customer left.

On a healthy daily sync that discrepancy is a day and nobody notices. After an interruption it is however long the interruption lasted, applied to every affected record at once, and it lands in the churn reporting.

A field not changing is not the same as nothing happening

This is the one that does real damage, and §6 is about it. Change-triggered automation has no concept of "should have changed". When the upstream stops sending, the CRM's automation layer does not degrade, error, or warn. It goes quiet — which is indistinguishable from a quiet week.

03Data model — three layers, not one

The fix is to stop treating "the field" as a single thing. Split it into three layers with different owners and different rules.

Layer 1 — mirrored fields (owned by the integration)

A verbatim copy of the upstream value. Read-only for every role, including administrators, in normal operation. Marked by a naming convention so that anyone reading a process, a form or a report can tell at a glance that this value came from outside — a consistent suffix or prefix on the label is enough, and is worth more than documentation nobody opens.

These fields exist to be read. Nothing in the CRM should ever write them.

Layer 2 — derived state (owned by the CRM)

A deliberately small set of fields that express what the CRM believes about the account, in the CRM's own vocabulary: a lifecycle state, a date the relationship ended, the renewal dates the business plans against. Which facts deserve a layer-2 field at all is the question Blueprint 003 works through.

This is the layer your automation branches on. Every downstream process — the reminder, the report, the escalation, the dashboard filter — reads layer 2. Only a handful of mapping processes read layer 1.

The benefit is exactly the benefit of any adapter: when the upstream vocabulary changes, you edit the mapping processes and nothing else. The cost is that layer 2 can drift from layer 1, which is why §7 is largely about reconciliation.

Layer 3 — sync-health fields

Two fields that are about the replication itself rather than the customer:

• A last-sync timestamp on the record, so any process can ask how fresh its inputs are.
• A replication-completion flag that the upstream sets when it has finished writing a record's payload.

The second is the more interesting one, and it solves a real ordering problem — see §5.

Rejected alternatives

• Branching directly on layer 1 everywhere. Rejected above. It couples your entire automation estate to somebody else's enum.
• Making mirrored fields editable "so support can fix them". They can already fix them, in the system that owns them. Editing the mirror produces a correction that survives until the next sync and an audit trail that lies.
• Reverse-syncing so the CRM becomes authoritative. A legitimate architecture, and a much larger project with its own conflict-resolution design. It is not a workaround for this problem; it is a different problem. If you do not have write-back today, do not let this blueprint talk you into inventing it.
• Recomputing derived state on read, in a formula field. Attractive, and it removes the drift problem entirely. It also removes your ability to record when a state was entered, which is what most of the reporting needs, and it cannot be the trigger for anything.

04Field-level configuration

Two notes on types. Keep the mirrored copy in the same type as the upstream value rather than converting on the way in — a status mirrored as text and mapped to a dropdown in layer 2 fails loudly when a new value appears, whereas a dropdown that silently rejects an unknown option fails quietly. And make the idempotency markers fields or tags, not inferred state — "have we already sent this?" must be answerable by a filter, not by reasoning about dates.

05Automation & logic

Derive first, branch later

One mapping process per upstream field. Its only job is to translate layer 1 into layer 2. Every mapping process ends with an unmapped-value branch: a final condition matching everything the earlier branches did not, whose action is to notify an administrator that an unrecognised value has appeared.

That last branch is the cheapest insurance in this whole blueprint. It converts a silent mis-routing — the default outcome when an upstream system adds an enum value — into a message.

Trigger on the completion flag, not on the payload

When a record's fields are written by replication, they do not all arrive at the same instant. Process logic that triggers on one field and reads five others can fire on a half-written record and branch on a mixture of new and old values.

The pattern that solves it: have the upstream set a replication-complete flag as the last write of a record's batch, and trigger the process on that flag, reading the payload fields as conditions rather than as the trigger. The process then runs once, after the record is coherent.

This is worth building even where the sync appears to be atomic today, because it costs one field and it is the difference between "works" and "works under load".

Detect contradictions and route them to a person

An upstream system emits invalid combinations. Not because it is badly built — because two systems with independent clocks and independent edit paths will produce states that disagree, and some of those states are ones your business rules say cannot exist:

• An end date is populated while the status still reads active.
• The status reads cancelled but no end date arrived.
• A start date changes on a subscription that has been live for a year.
• A record is flagged suspended and paid at the same time.

Build a process for each. Its action is not to fix the data — the CRM does not own it and any fix is overwritten on the next cycle. Its action is to notify a named role with the specific contradiction and the specific place it must be corrected upstream.

Treating contradiction detection as a feature rather than as an error handler is the difference between an integration you trust and one you merely hope about.

Write conditions as ranges, always, with an idempotency gate

This is the single most important rule in this blueprint, and it is the one drawn most directly from the incident in §6.

The equality version is correct on every day the sync is healthy and wrong forever on the days it is not, because the condition is only ever true for one value and nothing re-examines it afterwards. The range version plus an idempotency marker is idempotent and gap-tolerant: it does the work late rather than not at all, and it does it once.

Equality conditions on a replicated field are, in effect, a bet that no sync will ever be interrupted. That is not a bet worth taking for the two minutes the range version costs.

Gate on freshness — but fail loudly

Processes that act on replicated data should check the last-sync timestamp before acting. The obvious implementation — "only run if the last sync is current" — has a trap in it that §6 describes, so pair the gate with an explicit log or alert on the rejected path. A process that declines to run must say so.

06Limits & trade-offs

Change-triggered automation is silent, not failed

Every onChange process listening to a replicated field simply did not fire, because the fields did not change. There is no error state for "expected an event that never came". A week with no subscription changes and a week with a dead integration produce identical logs.

There is no platform-side fix for this. The monitor in §7 is not a nice-to-have; it is the only thing that can tell you.

Scheduled automation keeps running, confidently, on stale data

This is worse than not running. Daily processes fired on schedule every day of the outage and operated on a snapshot that was progressively less true — skipping accounts that had qualified, and processing accounts that no longer did. Customer-facing renewal countdown emails went out computed from stale day counts, meaning some customers received an email stating a number of days that was wrong, and others received nothing at all at their milestone.

The catch-up fires everything at once, with the wrong day-anchor

When replication resumed, days of accumulated deltas arrived together and every change-triggered process fired in a burst. The field values were correct. The day-anchor was not: any process whose action was set date = today stamped the recovery date onto an event that happened days earlier. Loss dates, cancellation dates and the close dates of the loss records created from them all needed manual correction afterwards.

Exact-match conditions are skipped permanently and without trace

The most damaging category, because it leaves no evidence at all:

• A daily process matching End Date = yesterday will never match those accounts again. They keep no end-of-life stamp, and dashboards go on counting them as active indefinitely.
• A milestone process matching tenure = 13 months sees the counter jump from 12 to 14 in a single catch-up write. It never fires, for those accounts, ever.
• Renewal-review processes matching an exact days-to-renewal value skip the accounts whose exact day fell inside the window.

None of these produce an error, a retry, or a queue entry. They produce a report that is quietly short, discovered weeks later, if at all.

A freshness gate can disable the very check it protects

One process was gated on "only run if the last sync timestamp is in the current period" — sensible-looking protection against acting on stale data. The last sync timestamp is itself a replicated field. During the outage it stopped advancing, so the gate evaluated false for every account and the process did nothing at all, for everyone, including accounts whose thresholds it was supposed to be watching.

A gate on a replicated field cannot distinguish "the data is stale" from "the data is fine and the staleness indicator is stale". Gate on freshness by all means — and put the alert on the rejected path, or the guard becomes the outage.

Previous-value diffs are themselves replicated

Change alerts of the form "seat count went from X to Y" usually read a previous-value snapshot that is also synced. After a gap, the snapshot and the current value may be several changes apart, so the diff reported to a human is arithmetically fine and factually misleading. Verify diffs after any interruption rather than trusting the alert text.

There is no ordering or transactional guarantee, and you cannot make the CRM wait

Process automation on this platform is not transactional and offers no cross-record ordering guarantee. You cannot express "hold this process until the sync completes", which is why the completion-flag trigger in §5 is a pattern rather than a setting. Nor can you replay a change-triggered process against a period it did not fire in; recovery is a query and a manual or bulk re-run, which is why §7 insists you write those queries before you need them.

The trade-off you are accepting

The three-layer model buys you decoupling and pays for it in duplication. Layer 2 can and will drift from layer 1 — through outages, through mapping bugs, through manual edits made during a recovery. You are accepting a reconciliation obligation in exchange for an automation estate that does not shatter when an upstream enum changes.

That trade is worth taking, on one condition: that the derivation is re-runnable. A process that recomputes all of layer 2 from the current contents of layer 1, on demand, for a filtered set of records, is the most valuable single thing to build here. It is your recovery tool, your migration tool and your test harness. Build it with the mapping, not after the first incident.

07Verification

The subject of verification here is not "does the automation work" — it did work, all the way through the outage, which was the problem. It is "can the CRM tell when its inputs stopped being true".

• Build the sync-freshness monitor first. A scheduled process, on a cadence shorter than your tolerance for the gap, that reads the maximum last-sync timestamp across records and alerts if it is older than a threshold. It must not itself depend on any replicated field other than the timestamp. Without this, the CRM's only outage detector is a human noticing that a report looks odd — which is what happened.
• Write the reconciliation queries before you need them. For each derived field, a saved query that finds records where layer 2 disagrees with what layer 1 implies: active-status records carrying an end date; ended subscriptions with no end-of-life stamp; lifecycle states that no current combination of mirrored fields would produce. Run them on a schedule, and after every known interruption. A derived state that no reconciliation query can explain is the regression signal.
• Test the gap, not the happy path. In a test space, pause the feed, let the scheduled processes run through several cycles, resume with a batched catch-up, and then check three things: what fired, what should have fired and did not, and what dates got stamped. Every finding in §6 is reproducible this way, which is the only way to know whether your own estate has them.
• Log the declines. When a freshness gate rejects, record it. "Did nothing because the data was stale" and "did nothing because there was nothing to do" must be distinguishable after the fact, and by default they are not.
• Audit the day-anchor after any recovery. Query for records whose event dates equal the recovery date, and check each against the upstream. A cluster of business events all dated to the same day is the fingerprint of a catch-up burst, not a coincidence.
• Re-run the derivation and diff it. The re-derivation process from §6 doubles as the check: run it over a sample, compare before and after, and confirm nothing moves. Anything that moves is drift you did not know you had.

What would signal a regression: any equality condition on a replicated field appearing in a new process; a milestone or notification counter that stays flat across a period when volume did not change; any date field where a significant number of records share the same value; a mapping process without a final unmapped-value branch.

Related blueprints

• Blueprint 003 — Where should this data live? The prior question. Deciding what the CRM should own at all is what determines how much of your estate ends up in layer 1.
• Blueprint 008 — Forecasting renewals that don't exist yet. Renewal dates are the most commonly replicated fields there are, and the forecasting mechanisms described there sit directly downstream of this pattern.
• Blueprint 005 — Migrating contacts without silently losing data. The one-off version of the same discipline: reconcile what arrived against what was sent, rather than trusting the load report.