An order page says that payment is pending. The payment provider says it succeeded. Meanwhile, the warehouse has no instruction to prepare the parcel. Each service is running, its health check is green, and no request is currently failing. The customer still has a problem.
Distributed applications can accumulate disagreements even when their individual components appear healthy. Finding those disagreements, understanding which facts are authoritative and repairing them carefully is a distinct engineering task. It deserves more than a script that copies whichever record was updated most recently.
Introduction
Reconciliation means comparing related information and resolving differences according to an explicit set of rules. In an application, that might mean checking orders against payment outcomes, rebuilding missing search documents or comparing inventory reservations with confirmed bookings.
The goal is not to make every database identical. Different services deliberately store different views of the same business process. An order service knows what the customer bought; a payment service knows which payment attempts exist; a reporting service calculates totals from those facts.
The useful question is whether those views agree about the relationships that matter. Every successful payment should have a recognised business reference. Every searchable product should correspond to an eligible product record. A cancelled reservation should not continue consuming availability indefinitely.
We will use a small shop to explain how to detect and repair disagreements without replacing one mistake with another. The same method applies to subscriptions, bookings, account provisioning and other workflows that cross service boundaries.
Start by Describing the Agreement You Expect
Before writing a comparison query, state the rule in ordinary language. For example: when a payment has completed for an active order, the order should eventually record that payment and continue through its fulfilment workflow.
That sentence already contains several important limits. It concerns completed payments, not every attempted payment. It refers to an active order, because a cancelled order may require a different response. It allows a short processing delay rather than treating every momentary difference as corruption.
Translate the rule into the fields required to evaluate it. You might need the order identifier, payment attempt identifier, provider reference, amount, currency, order status and payment outcome. Comparing only the words paid and pending would discard information needed to decide whether the records actually belong together.
Keep each rule narrow enough to explain. A single job called “make services consistent” can conceal many unrelated business decisions. Separate checks for missing records, incorrect amounts, stale statuses and duplicate associations produce clearer findings and safer repair options.
Write down legitimate exceptions too. A free order may have no payment. A payment can cover several invoices if the product supports that relationship. Reconciliation should model the real business agreement, rather than imposing a convenient relationship that the application never promised.
Choose an Authority for Each Fact
There is rarely one database that owns every fact in a distributed workflow. Authority is more useful at the level of an individual fact or decision.
The order service owns the agreed basket and its pricing rules. The payment provider owns the outcome of the operation submitted to that provider. The fulfilment service owns whether a parcel has already been handed to a carrier. None of those services can safely infer all the other facts from its own status field.
Suppose an order says £42 but a payment record says £24. Copying the order total into the payment record would not change the money that moved. Copying £24 into the order would silently rewrite what the customer agreed to buy. The mismatch needs investigation and a defined business response.
A practical reconciliation specification can record the relationship like this:
| Fact | Authoritative owner | Other services may store |
|---|---|---|
| Agreed order total | Order service | A reference or reporting copy |
| Provider payment outcome | Payment provider, verified by payment service | A derived payment status |
| Parcel dispatch | Fulfilment service | A customer-facing delivery status |
The payment service remains responsible for validating and interpreting provider information before changing local workflows. An unauthenticated browser claim that payment succeeded is not equivalent to an authoritative provider response.
This mapping prevents the repair tool from becoming an accidental owner of every service's business rules.
Separate Temporary Delay from Persistent Disagreement
An event-driven system normally passes through short periods of disagreement. Payment completes first; the order learns about it afterwards. Reporting may update later still.
If the comparison runs between those steps, it will find an apparent mismatch even when the normal workflow is making progress. Automatically repairing every such result creates extra traffic and can race with the original delivery.
Introduce a grace period suited to the workflow. A reporting copy may reasonably lag behind its source. A customer-facing payment outcome may require attention sooner. Choose the allowance from the expected processing behaviour and business impact, rather than applying the same delay everywhere.
Age alone is not proof of a bug. Some payment methods legitimately remain processing for an extended period. A reservation might wait for a manual review. The comparison should understand those states instead of treating “not finished yet” as “failed”.
Record the first time a mismatch was observed and check whether it persists. This distinguishes a brief delay from an order that has remained stuck through several runs. Use stable record identities for this history so that every scan does not create an apparently new incident.
Also observe the normal pipeline. A large rise in queue age may explain widespread temporary differences. Reconciliation should not flood an already overloaded consumer with duplicate repair requests while the original messages are still waiting.
Compare the Same Logical Point in Time
Even a careful query can produce false differences if its inputs describe different moments. Imagine reading the order before a cancellation and reading the payment after its refund. The two values may both be correct for the times at which they were read.
There is no automatic shared snapshot across unrelated databases and external APIs. A transaction around one database query does not freeze another service's data.
For a reporting projection, a useful comparison boundary may be a source event position. If the projection has processed through a known position, compare its output with the source history through that same boundary. With partitioned streams, the boundary may be one position per partition rather than one global number.
For ordinary operational checks, read a candidate, retrieve the corresponding authoritative record, and recheck important versions before applying a repair. If the record changed during the comparison, classify it as needing another check instead of forcing the old conclusion through.
Consider an order at version 8 when the mismatch is detected. Before repair, a cancellation advances it to version 9. A repair that assumes version 8 must stop and reconsider. This is not an inconvenient edge case: it is normal customer activity happening alongside maintenance work.
Record the observation times and source versions in the finding. Those details explain why a later inspection might show different values without implying that the reconciliation job invented its evidence.
Match Records Using Stable Business References
Matching by customer name, rounded timestamp or approximate amount may be useful for investigation, but it is a poor basis for automatic repair. A customer can place two identical orders in the same minute.
Carry stable references through the original workflow. An order can have several payment attempts, and each attempt should have a distinct identity linked to its provider reference. That relationship allows the checker to retrieve the correct operation instead of guessing which payment belongs to which order.
Include the tenant or account scope when identifiers are not globally unique. Order 1024 in one shop must not be matched with order 1024 in another. The same rule applies when a provider has separate live and test environments or several merchant accounts.
Treat missing and duplicate references as findings in their own right. If two local attempts point to the same provider payment, selecting the first row hides a potentially important modelling error.
Normalise representation only where the meaning is known. Compare monetary values in the agreed unit and currency; do not compare a pounds string with a pence integer. Distinguish an absent field from a zero value. Avoid making arbitrary case changes to identifiers whose owning system treats case as meaningful.
Clear matching rules make the difference between a useful integrity check and a script that occasionally joins unrelated customers together.
Produce a Readable Difference Before Writing Anything
The first version of a reconciliation job should produce findings, not repairs. This is a valuable design stage because it reveals whether the comparison understands normal data.
A finding might contain the rule name, order identity, payment reference, observed order version, expected relationship and the specific values that disagree. It should also say whether an automatic repair is available or whether the evidence remains incomplete.
For example:
Rule: completed-payment-not-recorded
Order: order-842
Observed order version: 12
Payment reference: pay-391
Provider outcome: succeeded
Local outcome: pending
Suggested action: verify and record existing payment outcome
This record does not authorise another charge. It describes a missing local acknowledgement of an existing outcome.
Classify findings so that different cases receive different handling. A missing search document may be safely rebuilt. An amount mismatch may need investigation. A provider timeout leaves the result unknown and should not be classified as a failed payment.
Sample findings against the original records before enabling writes. Look particularly at cancellations, partial refunds, older schema versions and multiple attempts. Those cases expose assumptions that a collection of straightforward successful orders will not test.
Keep sensitive data out of ordinary logs. Stable references and relevant state differences usually provide enough diagnostic value without copying full customer records into a second, poorly controlled store.
Repair Through the Service That Owns the Decision
A reconciliation process should usually request a correction from the owning service rather than directly editing its tables. The owner can apply current rules, check versions and preserve the same audit trail as the normal workflow.
For our shop, the checker might ask the payment service to verify a known provider reference and record its current outcome. The payment service then emits the appropriate durable notification for the order service to process.
That route has several advantages. Provider credentials remain with the integration that already owns them. The interpretation of payment statuses is shared with the live path. The order service still decides whether payment confirmation can advance the order or whether a cancellation requires another workflow.
Directly setting Orders.IsPaid = true bypasses those decisions. It may also omit the event that reporting, fulfilment or customer notifications require. A table can look repaired while the wider workflow remains stuck.
Sometimes a controlled migration or projection rebuild legitimately writes directly to its destination. Make that a documented operation with narrow ownership and explicit downstream consequences. Do not let an emergency convenience become an unrestricted maintenance interface for every service.
The repair request should explain its reason and carry the evidence references. Future investigation should be able to distinguish a normal webhook update from a reconciliation action without changing the meaning of the payment itself.
Protect Repairs Against Concurrent Changes
Detection and repair are separate operations. A finding can become stale between them, even if the delay is only a few milliseconds.
Use the owning service's normal concurrency controls. One common approach is an expected version: the repair says which record version it examined, and the service accepts the change only if that version is still current.
For a simple derived row, the underlying idea might look like this:
UPDATE OrderPaymentView
SET PaymentStatus = @verifiedStatus,
Version = Version + 1
WHERE OrderId = @orderId
AND Version = @expectedVersion;
The caller must inspect how many rows changed. Zero rows means that the assumption no longer held, or the target did not exist. It is a signal to reread and reassess, not permission to issue an unconditional update.
This example only illustrates a version check. A real business transition may need additional conditions, an audit record and a durable event in the same local transaction. The service must also validate that the supplied status came from an authorised source.
Do not retry a stale repair by replacing the expected version with the newest number while retaining the old decision. That would defeat the protection. Recompute whether the mismatch still exists and which action is valid for the current state.
This approach allows normal customer activity to continue during repair while preventing maintenance work from silently overwriting it.
Make Retried Repairs Safe
The reconciliation job can fail too. It might send a repair request successfully, lose the response and resume later. If every retry creates another business action, the recovery mechanism becomes a source of duplicates.
Give each logical repair an identity that the receiving service can recognise. The identity should represent the intended correction, such as recording the outcome of a particular payment attempt. It should not be a newly generated value on every HTTP retry or every scheduled scan.
The receiver can record accepted repair identities with its state changes so that repeating the request returns the existing result. If the operation contacts an external provider, use the provider's supported idempotency and outcome-retrieval mechanisms as appropriate to that operation.
Be careful about the boundary of deduplication. A record may later develop a different mismatch that requires another correction. A key consisting only of the order identity could incorrectly suppress all future maintenance. Include the logical operation and relevant source identity or version where that matches the domain.
Likewise, a repair's administrative identity must not replace the business operation's identity. Recording payment pay-391 should continue to refer to pay-391; it does not create a new payment merely because a new reconciliation run discovered it.
Persist per-item outcomes and resume unfinished work. A whole-batch checkpoint is convenient, but it should not force completed external actions to be repeated after a crash halfway through the batch.
Treat Unknown Outcomes as Unknown
Payment examples are useful because they expose a dangerous shortcut: assuming that a timeout means an operation failed.
If the provider accepted a payment but the response was lost, the local service may still show pending. Creating another payment to “fix” the order could charge the customer twice. The first step is to retrieve the existing operation using its stable reference.
Stripe's payment status guidance distinguishes lifecycle states and recommends server-side notifications for fulfilment decisions. Its documentation also notes that later refunds and disputes are not fully described by a PaymentIntent remaining succeeded. A reconciliation rule must query the information relevant to the fact it is checking.
If the authoritative system cannot currently be reached, retain the finding as unresolved. Record the retrieval failure separately from the business status. Infrastructure uncertainty should not be converted into an invented payment outcome.
This principle applies beyond payments. A carrier timeout does not prove that a shipment was not created. A provisioning timeout does not prove that an account does not exist. Search for the original operation before attempting a replacement.
Where no reliable reference or retrieval mechanism exists, automatic repair may be impossible. Preserve the evidence and route the case for investigation instead of manufacturing confidence from incomplete data.
Keep Correction Separate from Compensation
Correcting a stale copy and undoing a business action are different operations. Updating a report to show an existing refund is a data correction. Issuing a new refund changes the world.
Suppose payment succeeded after an order was cancelled. Simply marking the order paid may violate the cancellation decision. The business may need to refund the payment, offer another fulfilment option or request review, depending on its rules.
Those responses belong to a compensating workflow: a new action that addresses the consequences of an earlier one. Microsoft's Compensating Transaction pattern explains why compensation requires business-specific decisions and can itself fail or need retries.
Do not restore an old database snapshot for one order and assume that this reverses a shipment or payment. External actions and concurrent updates continue to exist. A repair must account for them explicitly.
Separate the permissions and reporting for factual corrections from those for consequential actions. A tool allowed to rebuild search documents does not automatically need permission to cancel bookings or transfer money.
When a case needs review, present the conflicting facts, their sources and the available supported actions. A human should not have to reconstruct the entire incident from a generic “consistency error” message before deciding what happens next.
Scan Large Datasets Without Disrupting the Application
A reconciliation job should not turn a small integrity problem into a service outage. Reading every record at maximum speed can consume database connections, saturate provider rate limits and compete with live requests.
Process bounded batches and limit concurrency separately for each dependency. A slow external API should not cause thousands of pending lookups to accumulate in memory. Back off on overload signals and preserve progress so that reducing the rate does not lose work.
Use a stable pagination strategy. Paging through a changing dataset by row offset can skip or repeat records as earlier rows are added or removed. A stable ordered key and an explicit scan boundary often make resumption easier, although updates to previously scanned rows still require a later pass or a change feed.
For very large datasets, start with coarse comparisons such as counts and totals per date or tenant, then investigate suspicious groups. These checks locate likely problems; matching totals do not prove that every record matches. Two errors can cancel each other out.
Checksums can reduce comparison cost when both sides use the same normalisation and scope. They still require a way to drill down to individual records, and they cannot compensate for comparing different moments in time.
Plan coverage as well as speed. A job that continually checks the newest records while never reaching older data can leave a permanent blind spot despite producing healthy-looking activity metrics.
Introduce Automatic Repair in Small Steps
A correct comparison does not automatically make an unrestricted repair job safe. The repair code has its own assumptions, permissions and failure modes, so introduce it as a controlled application change.
Begin with one well-understood rule and a small population. For example, rebuild missing search documents for a selected product category before enabling every kind of product correction. Inspect the resulting documents and confirm that normal updates still work afterwards.
Set a limit on how many repairs one run may perform. An unexpected finding count can indicate a broken comparison, a credentials problem that made one source appear empty, or a deliberate business change that the rule does not understand. A missing response must never be interpreted as an empty authoritative dataset and trigger widespread deletion.
Keep the detection result separate from the execution decision. A run can report ten thousand candidates while only applying a small, explicitly configured subset. This preserves visibility without committing the system to an equally large burst of writes.
Define stop conditions around unexpected errors, rising live-request latency and failed post-repair checks. Stopping should preserve completed outcomes and leave unfinished items resumable. It should not attempt to reverse every accepted business action automatically.
Retain the rule version and repair implementation version with the run. If a later investigation finds that a comparison normalised currencies incorrectly, you need to identify exactly which findings and actions used that version.
Finally, verify the repaired relationship independently of the command response. An endpoint returning success may mean that it accepted work for later processing. Check that the expected state actually appeared and that no conflicting state was introduced. A small set of fully verified repairs provides a stronger basis for expansion than a large count of successful HTTP responses.
Work Through a Lost Payment Notification
Consider order order-842, currently active and waiting for payment. Attempt attempt-2 has provider reference pay-391. The provider completed that payment, but a processing bug prevented the local outcome from being recorded.
The reconciliation scan finds the attempt after its normal processing allowance. It retrieves pay-391 from the configured provider account and verifies that the reference, amount and currency match the attempt. It records a finding with the current local versions.
The checker requests verification through the payment service using a stable repair identity. The payment service retrieves the authoritative outcome, confirms that this transition is valid and records the result with its durable notification. Repeating the same request would not create another payment.
The order service receives the payment confirmation. It checks the order's current state and accepts the known payment attempt. Its normal fulfilment workflow proceeds with its existing duplicate protection.
The reconciliation job then verifies the relationship again. It marks the finding resolved only when the relevant state agrees, allowing for downstream processing time. An accepted repair request alone is not proof that every dependent view has caught up.
If the customer had cancelled the order during this sequence, the order service would follow the cancellation-related payment policy instead. The same verified fact can lead to a different valid action because the current business state matters.
Monitor Integrity and Fix the Original Cause
Track the number and age of unresolved mismatches, the proportion repaired automatically, failed repair attempts and the time since each dataset was last covered. Break results down by rule so that one noisy comparison does not hide another serious problem.
Watch for a repair loop: the same records repeatedly diverge after being corrected. This often indicates a continuing bug, conflicting owners or an older event overwriting newer state. Increasing the repair frequency may conceal the problem while adding load.
Investigate the normal path that produced each significant class of mismatch. Perhaps a database update and event publication were not coordinated. Perhaps an event handler acknowledged before committing. Perhaps a schema change caused one consumer to skip important records.
Reconciliation provides a safety net and evidence for that investigation. It should not become the only mechanism that makes the main workflow function.
Google's SRE discussion of data integrity treats protecting and recovering data as an engineering concern in its own right. Apply that mindset to application relationships: service availability is useful, but customers also need the stored facts and resulting actions to remain correct.
Test the checker with missing records, duplicate references, concurrent updates, provider outages and a crash after repair acceptance. Verify that uncertain cases remain visible and that restarting the job preserves both progress and evidence.
Summary
Reconciliation starts with a precise relationship between facts and a clear owner for each fact. Compare stable identities at a meaningful boundary, allow normal processing delay and produce an understandable finding before attempting a correction.
Repair through the responsible service, protect against concurrent changes and make repeated requests safe. Verify uncertain external outcomes instead of assuming failure, and distinguish updating a stale view from performing a new business action.
A good reconciliation process makes disagreement visible, repairs supported cases and preserves evidence for the rest. It also points back to the original failure, helping the application become more reliable rather than merely better at repeatedly cleaning up the same mistake.