Automated nightly reconciliation: finding mismatches before users do

Short answer: once a day, take the closed previous day, list the deposits your ledger recorded and the deposits the payment gateway says it settled, and match them by the gateway's reference. Everything that doesn't match exactly (missing on one side, different amounts, duplicates) is a finding that someone must look at. Write the matching as a pure function you can unit-test, run it from a BackgroundService, and make a non-empty result loud.

A real example: the small mismatch that eats days

Totals are off by a small amount. Nobody knows which of the day's thousands of transactions is responsible, so people export spreadsheets from the gateway panel and the database and compare them line by line. That takes days, and the next mismatch starts the same process again.

The cause is usually boring: a callback that never arrived, so the gateway took the money but the wallet wasn't credited. Or a retry that credited twice. Or a refund recorded on one side only. Every one of them is easy to see when a program puts the two lists side by side, and nearly impossible to see by eye.

The four kinds of mismatch

Runnable code

The core is a pure function: two lists in, a list of mismatches out. No database, no HTTP, so it's trivial to unit-test:

/// <summary>A deposit as our ledger recorded it.</summary>
public sealed record LedgerDeposit(string Reference, long AmountRials, DateOnly Date);

/// <summary>A deposit as the payment gateway's settlement report says it happened.</summary>
public sealed record GatewaySettlement(string Reference, long AmountRials, DateOnly Date);

public enum MismatchKind
{
    /// <summary>The gateway took the money, but the user's wallet was never credited.</summary>
    MissingInLedger,

    /// <summary>We credited a wallet for money the gateway never settled.</summary>
    MissingAtGateway,

    AmountDiffers,

    /// <summary>The same reference appears more than once on one side.</summary>
    Duplicate,
}

public sealed record Mismatch(string Reference, MismatchKind Kind, long? LedgerRials, long? GatewayRials);

public static class Reconciler
{
    /// <summary>Matches the two sides by gateway reference. An empty result means the day reconciles.</summary>
    public static IReadOnlyList<Mismatch> Reconcile(IEnumerable<LedgerDeposit> ledger, IEnumerable<GatewaySettlement> gateway)
    {
        var mismatches = new List<Mismatch>();

        var ledgerByRef = Index(ledger, d => d.Reference, d => d.AmountRials, mismatches, isLedger: true);
        var gatewayByRef = Index(gateway, s => s.Reference, s => s.AmountRials, mismatches, isLedger: false);

        foreach (var (reference, ledgerAmount) in ledgerByRef)
        {
            if (!gatewayByRef.TryGetValue(reference, out var gatewayAmount))
            {
                mismatches.Add(new Mismatch(reference, MismatchKind.MissingAtGateway, ledgerAmount, null));
            }
            else if (gatewayAmount != ledgerAmount)
            {
                mismatches.Add(new Mismatch(reference, MismatchKind.AmountDiffers, ledgerAmount, gatewayAmount));
            }
        }

        mismatches.AddRange(gatewayByRef
            .Where(g => !ledgerByRef.ContainsKey(g.Key))
            .Select(g => new Mismatch(g.Key, MismatchKind.MissingInLedger, null, g.Value)));

        return mismatches;
    }

    private static Dictionary<string, long> Index<T>(
        IEnumerable<T> rows, Func<T, string> reference, Func<T, long> amount, List<Mismatch> mismatches, bool isLedger)
    {
        var index = new Dictionary<string, long>(StringComparer.Ordinal);
        foreach (var row in rows)
        {
            if (!index.TryAdd(reference(row), amount(row)))
            {
                mismatches.Add(new Mismatch(reference(row), MismatchKind.Duplicate,
                    isLedger ? amount(row) : null, isLedger ? null : amount(row)));
            }
        }

        return index;
    }
}

The scheduling is a BackgroundService that sleeps until 02:00 Tehran time, reconciles yesterday and logs the result. Where the two lists come from is behind an interface, so the job doesn't care whether the gateway report is an API, a CSV or a table:

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

/// <summary>The two sides for one day. Implement it with your ledger query and your gateway's settlement report.</summary>
public interface IReconciliationSources
{
    Task<IReadOnlyList<LedgerDeposit>> LedgerAsync(DateOnly day, CancellationToken ct);

    Task<IReadOnlyList<GatewaySettlement>> GatewayAsync(DateOnly day, CancellationToken ct);
}

/// <summary>Reconciles yesterday every night at 02:00 Tehran time and shouts when it doesn't add up.</summary>
public sealed class NightlyReconciliation(IServiceScopeFactory scopes, TimeProvider clock, ILogger<NightlyReconciliation> logger) : BackgroundService
{
    private static readonly TimeZoneInfo Tehran = TimeZoneInfo.FindSystemTimeZoneById("Asia/Tehran");
    private static readonly TimeOnly RunAt = new(2, 0);

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            await Task.Delay(UntilNextRun(), clock, stoppingToken);

            var yesterday = DateOnly.FromDateTime(TimeZoneInfo.ConvertTime(clock.GetUtcNow(), Tehran).DateTime).AddDays(-1);
            try
            {
                using var scope = scopes.CreateScope();
                var sources = scope.ServiceProvider.GetRequiredService<IReconciliationSources>();
                var mismatches = Reconciler.Reconcile(
                    await sources.LedgerAsync(yesterday, stoppingToken),
                    await sources.GatewayAsync(yesterday, stoppingToken));

                if (mismatches.Count == 0)
                {
                    logger.LogInformation("Reconciliation {Day}: OK.", yesterday);
                }
                else
                {
                    // Error level: this should page someone (alert rule on the log, Telegram, email).
                    logger.LogError("Reconciliation {Day}: {Count} mismatch(es): {@Mismatches}", yesterday, mismatches.Count, mismatches);
                }
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                logger.LogError(ex, "Reconciliation {Day} could not run.", yesterday);
            }
        }
    }

    private TimeSpan UntilNextRun()
    {
        var now = TimeZoneInfo.ConvertTime(clock.GetUtcNow(), Tehran);
        var next = now.Date + RunAt.ToTimeSpan();
        if (next <= now.DateTime)
        {
            next = next.AddDays(1);
        }

        return next - now.DateTime;
    }
}

Register it with builder.Services.AddHostedService<NightlyReconciliation>(), plus your IReconciliationSources implementation and TimeProvider.System. Taking TimeProvider instead of DateTime.UtcNow means a test can move time forward and check the schedule.

Making it useful, not just correct

• Reconcile a closed day. Run after midnight for the day before, so late callbacks have had time to arrive. If your gateway settles later, reconcile two days back.
• Alert on findings. An error-level log line nobody reads is a spreadsheet nobody opens. Route it to a channel a person sees: Telegram, email, your monitoring.
• Store the results. A table of findings with status (open, explained, fixed) shows whether the same cause keeps coming back.
• Fix forward. Correct the ledger with new entries (a reversal or a missing credit), never by editing past rows. The reconciliation history stays truthful.
• Turn causes into code. A "missing in ledger" that keeps recurring means the callback path needs a retry or a verification sweep, not a better spreadsheet.

Pros and cons

Pros

• Mismatches are found the next morning, not when a user complains or a monthly report doesn't add up.
• The matching logic is a pure function with unit tests, and its rules are written down.
• Every finding points at a specific reference, so investigation starts at the right row.

Cons

• You depend on the gateway's settlement data: its format, timing and availability.
• Timing differences produce false positives unless you reconcile a closed window.
• It finds problems; it doesn't prevent them. Idempotency and verification in the payment flow still do that job.

Where it sits in the design

Reconciliation is the safety net under the other money rules: a ledger as the single source of truth, idempotent callbacks, concurrency control on balances. Each of those prevents a class of errors; reconciliation catches whatever gets past them, and tells you which rule needs work.

Need reconciliation, or a second pair of eyes on a payment flow? See how I can help.