The integration layer
A reference architecture for getting data out of systems of record and into an application that can rely on it — sync, identity, permissions, and the tests that catch an upstream change before your users do.
Every application that does something useful with an organisation's data has an integration layer, whether or not anyone designed one. This paper describes what that layer should contain, in the order the decisions actually have to be made.
It applies whether the consumer is an AI system, a reporting surface, or an ordinary application feature. The requirements barely change; only the tolerance for staleness does.
The decision that comes first
Before any code: do you read through to the source, or do you keep a copy?
Read-through means every request hits the upstream API. It is always current and it holds no data, which makes the compliance conversation short. It also inherits the upstream's latency, its rate limits and its outages, and it cannot answer any question that requires joining across systems.
Local copy means an ingestion pipeline and a store you own. It is fast, joinable, and available when the upstream is not. It is also stale by some amount you have to define, and it makes you a data controller for a copy of someone else's records.
Most systems need both: a copy for anything that involves search, joins or aggregate views, and read-through for anything where being wrong by five minutes is unacceptable — balances, entitlements, current status.
Decide this explicitly and write down the staleness budget for each entity. "Contacts may be up to fifteen minutes stale; entitlements are always read live" is a design. Discovering the answer per-feature as you go is not.
Ingestion
Watermarks
Incremental sync needs a reliable "changed since" signal. Before designing around updated_at, verify it: is it set by every write path, including bulk imports and admin edits? Is it the source's clock or the row's? Does it move when a related record changes, or only the row itself?
When the field is unreliable — which is common — the fallbacks are a change-data-capture feed if the vendor offers one, an event webhook with a periodic reconciliation pass behind it, or a full comparison on a schedule. Choose deliberately; do not assume the timestamp is trustworthy because it exists.
Deletes
Most APIs do not tell you about deletions. A record simply stops appearing, which is indistinguishable from it no longer matching your filter.
The reliable pattern is a periodic reconciliation: fetch the full set of identifiers, compare against your copy, and tombstone anything absent. Run it on a schedule proportional to the cost of being wrong. Without it, deleted records live in your system indefinitely, and the first person to notice is a customer asking why a departed employee still appears.
Backfill and steady state are different programs
The initial load has different constraints from ongoing sync: different rate-limit behaviour, different batch sizes, different failure semantics. Treat them as separate paths with separate tests. A backfill that reuses the incremental path usually discovers the difference in production, at three in the morning.
Make it resumable
Sync jobs fail halfway. The job needs a durable cursor so a restart continues rather than beginning again, and writes need to be idempotent so a replayed batch does not duplicate. This is not sophisticated engineering; it is just work that is easy to skip and expensive to retrofit.
Identity
The moment there are two systems, the same real-world entity exists twice with two identifiers and two spellings.
Do not resolve this implicitly inside feature code. Build an explicit mapping — an entity table holding your canonical identifier and the foreign key in each source system — and populate it with a documented rule: exact match on a unique key where one exists, deterministic normalisation where it does not, and a review queue for anything ambiguous.
Measure the disagreement rate before designing around it. "The identifiers match for 97% of records" is a scoping input that tells you whether you need a review queue at all. "They should match" is a hope, and hopes do not survive a merger, a data migration, or a sales team pasting values into the wrong field.
Store the provenance of every field: which system it came from and when. When two sources disagree, you need a precedence rule you can point at rather than a debate.
Permissions
This is the part that stalls projects, and it is mostly not a technical problem.
The source system has a permission model. Your application has another. Someone has to decide what happens when they differ, and that decision carries risk that an engineering team is usually not authorised to accept on its own.
Three patterns, in descending order of safety:
Mirror the source. Replicate the upstream's access rules and evaluate them per query. Safest and most faithful; expensive, and it makes your query path depend on a second system's semantics.
Map to your own roles. Define your application's roles and write an explicit mapping from source permissions. Simpler and faster, but the mapping is a place where privilege can be silently widened — so it needs review and a test suite of its own.
Restrict to the intersection. Only expose data that everyone with access to your application is entitled to see. Crude, and often the right answer for a first release, because it is obviously safe and it unblocks everything else.
Whichever you choose, enforce it in the data layer rather than in the interface. A permission check in a React component is a display rule, not access control, and the API behind it is what an attacker will call.
Start this conversation in week one. It needs the data owner, the risk owner, and a written answer, and getting those three aligned is a calendar problem that runs in parallel with everything else — or it becomes the reason a finished system sits unreleased.
Tests that earn their place
Contract tests against the live upstream. Assert the shape, types and cardinality of what the source actually returns, on a schedule, against the real system. This is the single highest-value test in the layer, because schema drift is silent by default: nothing errors, a value is simply missing or misinterpreted, and the system keeps producing confident output from degraded input.
Reconciliation checks. Compare counts and checksums between source and copy on a schedule, and alert on divergence beyond a threshold. This catches the sync failures that do not raise exceptions.
Permission tests. For each role, assert that a query returns what it should and — more importantly — does not return what it should not. Negative assertions are the ones that catch a widened mapping.
Freshness monitoring. Alert when the newest record in your copy is older than the staleness budget for that entity. A sync that silently stopped looks exactly like a quiet week until someone checks.
An order of work
- Get credentials to the real system. This takes longer than expected and blocks everything.
- Pull real data — including the malformed records — and look at it before designing anything.
- Write the contract test, and let it run from day one.
- Open the permissions conversation with the data and risk owners.
- Build the connector for one entity end to end: backfill, incremental, deletes, reconciliation.
- Measure identity disagreement and decide whether a review queue is needed.
- Only then build the feature that consumes it.
The ordering is the point. Steps one to three are where the unpleasant surprises live, they are cheap to absorb early, and they are the steps most commonly scheduled last.