A settlement mismatch that surfaces in tomorrow’s batch job has already cost a full day of unreconciled exposure. Somewhere in that window, a duplicate charge, a missing payout, or a ledger entry that never matched the processor’s file sat unnoticed. The team finds out after the fact, when the fix takes longer and the customer has already complained.
For payment teams, fintech real-time data processing starts here. Real-time settlement reconciliation compares the internal ledger against the processor’s settlement records continuously, as each side changes, instead of once a day. This article shows how to build that loop on TiDB with an idempotent double-entry ledger, a TiCDC changefeed that streams every committed ledger change to a reconciliation service, and the SQL that catches mismatches in both directions.
What Real-Time Settlement Reconciliation Means
Real-time settlement reconciliation means matching every internal ledger entry against the payment processor’s settlement record as soon as either side changes, rather than diffing a full day of data in one batch. An unexplained gap between what the ledger says and what the processor confirms is a bug, a fraud signal, or money that still needs to move. Reconciling in real time means the team learns which one while it can still act.
Most teams start with a nightly job because it is the default. A cron job pulls yesterday’s ledger, diffs it against the processor’s settlement file, and flags mismatches the next morning. That works while a person can review the mismatch list by hand. It stops working when volume outgrows manual review, when a single mismatch represents enough money that a day of delay is a real risk, or when a regulator asks why a discrepancy sat open for 18 hours.
Two situations from production make the cost concrete. In the first, a payment settles on the processor’s side, but the matching ledger write fails on a transient error the application swallows. Under a nightly batch, that gap stays open for up to 24 hours, and a support ticket may surface it before the batch does. In the second, a processor settles a payment for a different amount than the ledger recorded. Under real-time reconciliation, both cases surface within seconds as internal alerts instead of customer complaints.
Why Ledger Consistency Is Non-Negotiable in Fintech Real-Time Data Processing
Real-time reconciliation is only as reliable as the ledger it reads. Two properties make a ledger trustworthy enough to reconcile against continuously: immutable double-entry records and idempotent writes.
Derive balances from immutable entries
A payment ledger is not a balance column updated in place. An in-place update records what the balance is now, with no record of why it changed, so there is nothing to audit. A double-entry ledger writes one debit and one matching credit for every transfer and never edits a row after it commits. The current balance is the sum of an account’s entries.
Enforce idempotency in the database
Networks retry, and clients time out and resend. Without a unique identifier tied to each request, a retried payment posts twice. Put the guarantee in the database as a unique constraint, not only in application logic, so a duplicate request fails safely instead of double-posting.
CREATE TABLE transfers (
id BIGINT AUTO_RANDOM PRIMARY KEY,
idempotency_key VARCHAR(64) NOT NULL,
processor_ref VARCHAR(64) NOT NULL,
amount_cents BIGINT NOT NULL,
currency CHAR(3) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_idempotency (idempotency_key),
KEY idx_processor_ref (processor_ref)
);
CREATE TABLE ledger_entries (
id BIGINT AUTO_RANDOM PRIMARY KEY,
transfer_id BIGINT NOT NULL,
account_id BIGINT NOT NULL,
direction ENUM('debit', 'credit') NOT NULL,
amount_cents BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_transfer_direction (transfer_id, direction),
KEY idx_account (account_id)
);Write the transfer row and its two ledger entries in one transaction. If a client retries with the same idempotency_key, the insert into transfers fails with a duplicate-key error (ERROR 1062), the transaction rolls back, and the application returns the original result. The unique key on (transfer_id, direction) blocks a second debit or credit for the same transfer. Balances come from committed, immutable rows.
SELECT account_id,
SUM(CASE WHEN direction = 'credit' THEN amount_cents ELSE -amount_cents END) AS balance_cents
FROM ledger_entries
WHERE account_id = 1001
GROUP BY account_id;Idempotency in the ledger works alongside idempotency at the API layer, which covers the request side of the same problem. Reconciliation catches what neither can prevent, such as a settlement that never arrives or arrives with a different amount.
How TiDB and TiCDC Solve the Real-Time Reconciliation Problem
The nightly pattern exists because most databases give downstream systems no way to learn what changed, short of a scheduled export or a polling query. TiDB answers with TiCDC, its change data capture (CDC) component. TiCDC pulls row-level changes from TiKV and streams them to downstream systems such as Kafka, MySQL, or cloud storage as they commit. It is a CDC mechanism, not an ETL pipeline. It captures the change stream itself instead of extracting, transforming, and loading a batch on a schedule.
Create a changefeed for the ledger tables
A changefeed is a TiCDC replication task. The configuration below limits it to the two ledger tables and partitions Kafka messages by primary key, so every change to the same row lands in the same partition in commit order.
# ledger-feed.toml
[filter]
rules = ['payments.transfers', 'payments.ledger_entries']
[sink]
dispatchers = [
{matcher = ['payments.*'], partition = "index-value"},
]cdc cli changefeed create \
--server=http://ticdc-host:8300 \
--sink-uri="kafka://kafka-host:9092/ledger-changes?protocol=canal-json&enable-tidb-extension=true&kafka-version=2.4.0&partition-num=6&replication-factor=3" \
--changefeed-id="ledger-reconciliation" \
--config=ledger-feed.tomlThe canal-json protocol with enable-tidb-extension=true adds each change’s commit timestamp to the message, which the consumer uses to order events and detect replays. Set kafka-version to your broker’s actual version. In TiDB Cloud, you create changefeeds from the console instead of the CLI. See the TiCDC Kafka sink reference for every sink option.
Design the consumer for at-least-once delivery
TiCDC outputs every change at least once. After a changefeed pauses and resumes, the Kafka sink can resend messages it already delivered, so a consumer might receive msg1, msg2, msg3, msg2, msg3. A reconciliation consumer that treats each message as new will raise false alerts or double-count. Key every processed event on the transfer’s idempotency_key, or on the row’s primary key plus commit timestamp, and skip anything already seen. The same discipline that protects the ledger protects the reconciliation service.
How to Build a Real-Time Reconciliation Loop With TiDB
The reconciliation loop has four steps, and each one runs as data arrives rather than on a schedule.
- A ledger write commits in TiDB. The transfer row and both ledger entries commit together in one transaction.
- TiCDC streams the change. The changefeed publishes the committed rows to the ledger-changes topic.
- The reconciliation service matches it. The consumer looks up the processor’s settlement record by processor_ref. Settlement records arrive on the processor’s own schedule, through webhooks or settlement files, and land in a processor_settlements table.
- A mismatch raises an alert immediately. A missing, late, or different settlement becomes an alert within seconds of the write, not the next morning.
The matching logic is plain SQL. With settlement records stored in TiDB alongside the ledger, three queries cover the failure cases.
CREATE TABLE processor_settlements (
processor_ref VARCHAR(64) PRIMARY KEY,
amount_cents BIGINT NOT NULL,
currency CHAR(3) NOT NULL,
status VARCHAR(16) NOT NULL,
settled_at TIMESTAMP NOT NULL
);
-- 1. Settled by the processor, missing from the ledger
SELECT s.processor_ref, s.amount_cents, s.settled_at
FROM processor_settlements s
LEFT JOIN transfers t ON t.processor_ref = s.processor_ref
WHERE t.id IS NULL
AND s.settled_at < NOW() - INTERVAL 5 MINUTE;
-- 2. In the ledger, not settled after one hour
SELECT t.processor_ref, t.amount_cents, t.created_at
FROM transfers t
LEFT JOIN processor_settlements s ON s.processor_ref = t.processor_ref
WHERE s.processor_ref IS NULL
AND t.created_at < NOW() - INTERVAL 1 HOUR;
-- 3. Matched, but the amounts or currencies disagree
SELECT t.processor_ref,
t.amount_cents AS ledger_cents,
s.amount_cents AS settled_cents
FROM transfers t
JOIN processor_settlements s ON s.processor_ref = t.processor_ref
WHERE t.amount_cents <> s.amount_cents
OR t.currency <> s.currency;In the consumer, the same checks run for a single processor_ref each time an event arrives. The full-table versions work as a periodic sweep.
Route mismatches by what they mean
Not every mismatch deserves the same response. A settlement with no matching ledger entry points to a failed or delayed internal write, so retry the write automatically before escalating. A ledger entry with no settlement after an hour is a stronger signal. Either the processor is behind, or the money never moved, and that case should page someone. An amount mismatch always goes to a person. Routing the three cases differently keeps the service from becoming an alert stream the team learns to ignore.
Keep the processor’s settlement file as a backstop
Real-time reconciliation does not retire the processor’s settlement file. Processors still deliver authoritative files on their own cadence, and a full pass against that file catches anything the event stream missed, such as a webhook that never fired. What changes is the exposure window on the ledger side, which shrinks from a day to the changefeed’s replication lag. TiCDC reports that lag for each changefeed, so alert on it too. A stalled changefeed is a reconciliation outage.
If both sides already live in TiDB, you could run the queries above every minute instead. TiCDC removes the polling loop, pushes each change to consumers once it commits, and feeds the same stream to other systems, such as a ledger archive or fraud scoring, without each one polling the database.
Settlement at scale is a workload TiDB already runs. Trip.com moved its hotel settlement business to TiDB after MySQL sharding failed to fit data that has to be managed across suppliers, hotels, and users at once, with settlement data expected to reach about 8 TB.
A nightly settlement diff was never a design decision. It was a default nobody revisited until mismatches started costing real money. Real-time reconciliation keeps idempotency at the write path and the processor’s file as a backstop, and closes the gap between them, the window where a ledger write and a settlement should agree and TiCDC can confirm that they do.
To build the ledger schema and reconciliation queries from this article, start a free TiDB Cloud Starter cluster. For more financial workloads on TiDB, see TiDB for fintech.
Real-Time Settlement Reconciliation FAQ
What is real-time settlement reconciliation?
Real-time settlement reconciliation is the continuous matching of a company’s internal payment ledger against a payment processor’s settlement records as each side changes, instead of in a nightly batch. Each new ledger entry is checked against the processor’s record for the same payment, and a missing, late, or mismatched settlement raises an alert within seconds. It shrinks the window in which a failed ledger write or missing payout can go unnoticed from about a day to the replication lag of the change stream.
How is TiCDC different from a traditional ETL pipeline?
An ETL pipeline extracts data on a schedule, transforms it, and loads it into another system in batches. TiCDC is TiDB’s change data capture component. It reads row-level changes from TiKV and streams each committed change to downstream systems such as Kafka, MySQL, or cloud storage as it happens, with no batch window or scheduled export job to maintain. TiCDC delivers every change at least once, so consumers should deduplicate events by primary key or idempotency key.
Does real-time reconciliation replace idempotency checks at the API layer?
No. The two solve different parts of the same problem, and payment systems need both. Idempotency at the API layer and a unique constraint in the ledger stop a retried request from posting a charge twice. Real-time reconciliation catches failures idempotency cannot prevent, such as a ledger write that failed silently, a processor settlement that never arrives, or a settlement for a different amount. Reconciliation also depends on idempotency, since a ledger with duplicate entries produces false mismatches.
Can TiDB support both the ledger and the reconciliation pipeline in one system?
Yes. The ledger runs as ordinary transactional tables in TiDB, with ACID transactions and unique constraints enforcing idempotency. TiCDC streams committed changes from those same tables to the reconciliation consumer, and the processor’s settlement records can land in TiDB as well, so matching queries join both sides with standard SQL. The only additional components are TiCDC and the downstream consumer, such as a Kafka topic and a reconciliation service. No separate database or ETL layer is required.