On-chain ↔ off-chain reconciliation
Your ledger says you hold 10 BTC. The blockchain says you hold 9.998. Which is right?
By Solomon Ajayi · Free to read, no signup
Your crypto-fiat fintech custodies user crypto. Your INTERNAL ledger records every user's BTC balance (off-chain, just SQL rows). The ACTUAL chain holds the UTXOs / account balances. These two views MUST agree to the satoshi. Discrepancies happen: a chain reorg invalidates a deposit you already credited, a dust transaction adds satoshis you didn't book, network fees deducted at broadcast time aren't reflected in user wallets. A reconciliation job runs every block (every ~10 minutes for BTC) comparing the two views and posting CORRECTION entries. This lesson walks one such correction: chain shows 0.002 BTC less than ledger; you book a 'reconciliation adjustment' to bring them back in sync.
Custodying crypto means keeping two books that describe the same coins. Your internal ledger is just SQL rows recording what each user is owed; the blockchain is the actual record of how much you hold. These are different things, and they drift. A network fee paid at broadcast time leaves the chain but never debits a specific user, a dust transaction adds satoshis nobody booked, a reorg invalidates a deposit you already credited. The two views must agree to the satoshi, so a job runs every block to find the gap and close it.
The key insight is which book is authoritative for what. The chain is the source of truth for how many coins exist; your ledger is the source of truth for who has a claim on them. When the chain shows 0.002 BTC less than your ledger because a network fee was paid from the hot-wallet aggregate, the correction shrinks the asset, not the user liability: Reconciliation Adjustment up as an expense, Hot Wallet down. Individual users still see their original balance, because the fee was your cost of operating the network, not theirs.
Run the recon every block and make it idempotent on block number, so a replay of block 851234 produces the same adjustment exactly once rather than double-booking. The direction of the entry flips with the sign of the drift: an unexpected dust deposit means the chain has more than your ledger, so Hot Wallet goes up and the adjustment is a credit (a gain) instead of a debit.
Worked example, step by step
State: ledger says 10 BTC across all users; chain confirms 9.998 BTC
Discrepancy of 0.002 BTC (≈ ₦1,500,000 at ₦750M/BTC). Cause: a 0.002 BTC network fee was paid at broadcast time but never debited from any specific user wallet (it came from your hot-wallet aggregate). The user-wallet ledger overstates total liability by 0.002 BTC.
| Account | Debit | Credit |
|---|---|---|
| Hot Wallet (BTC, on-chain truth) (1900) | ₦7,500,000,000.00 | |
| User Wallet (BTC, ledger view) (2000) | ₦7,500,000,000.00 |
We bootstrap state with a 10 BTC = ₦7,500,000,000 entry (we use kobo notation: 1 BTC = ₦7.5M for math simplicity, real-world is far higher). Hot Wallet UP ₦7.5B (debit), User Wallet UP ₦7.5B (credit).
Reconciliation adjustment: write down 0.002 BTC of liability
Job runs. Chain has 9.998 BTC; ledger has 10 BTC. The DIFFERENCE has to go SOMEWHERE, and since the 0.002 BTC was used for legitimate network fees (an operational expense), it flows to the Reconciliation Adjustment / Network Fee Expense account. User Wallet liability DOWN, expense UP.
| Account | Debit | Credit |
|---|---|---|
| Reconciliation Adjustment (P&L) (5900) | ₦1,500,000.00 | |
| Hot Wallet (BTC, on-chain truth) (1900) | ₦1,500,000.00 |
Reconciliation Adjustment UP ₦1,500,000 (debit). Hot Wallet (BTC) DOWN ₦1,500,000 (credit). The hot wallet asset shrinks to match chain (10 BTC → 9.998 BTC); the offsetting cost lands in P&L. Notice the entry does NOT touch User Wallet, individual users still see their original BTC balance. The reconciliation expense is YOUR cost of operating the network.
Takeaway
Crypto reconciliation is asymmetric: the chain is the source of truth for asset existence, your ledger is the source of truth for user claims, and reconciliation entries close the gap whenever they diverge. Run reconciliation every block (or on-demand for events like deposits / withdrawals); never trust your internal ledger as authoritative for chain holdings. Build the recon job idempotent on block number so you can replay safely. Without this discipline, your audit can't sign off on your crypto custody and your sponsor bank pulls the relationship.
The code behind it
Per-asset drift between the internal sub-ledger (Hot Wallet 1900) and confirmed on-chain truth, flagged past tolerance. FULL JOIN so an asset missing from either side still surfaces.
-- Reconcile Hot Wallet (acct 1900) against confirmed on-chain balances.
-- All amounts are integer kobo; chain_kobo is the confirmed balance per asset.
WITH ledger_balance AS (
-- Net the asset side: debits add, credits subtract (Hot Wallet is an asset).
SELECT asset, SUM(debit) - SUM(credit) AS ledger_kobo
FROM ledger_lines
WHERE account_code = '1900' -- Hot Wallet (on-chain truth)
GROUP BY asset
)
SELECT
COALESCE(l.asset, c.asset) AS asset,
COALESCE(l.ledger_kobo, 0) AS ledger_kobo,
COALESCE(c.chain_kobo, 0) AS chain_kobo,
-- Drift > 0: ledger overstates holdings (e.g. a network fee we never booked).
-- Drift < 0: chain has more than we booked (e.g. an unexpected dust deposit).
COALESCE(l.ledger_kobo, 0) - COALESCE(c.chain_kobo, 0) AS drift_kobo,
ABS(COALESCE(l.ledger_kobo, 0) - COALESCE(c.chain_kobo, 0))
> COALESCE(t.tolerance_kobo, 0) AS needs_correction
-- FULL JOIN: an asset booked in the ledger but absent on chain (or vice versa)
-- is the WORST drift and must surface, not silently vanish on an inner join.
FROM ledger_balance l
FULL JOIN chain_truth c USING (asset) -- confirmed on-chain balance per asset
LEFT JOIN recon_tolerance t USING (asset) -- e.g. 100000 kobo per asset
ORDER BY ABS(COALESCE(l.ledger_kobo, 0) - COALESCE(c.chain_kobo, 0)) DESC;Practice this on a real ledger
Reading is half of it. Open this lesson in the lab to post the entries yourself against a real Postgres-backed double-entry ledger, with the validation on. Free, your sandbox is yours.