All notes
Fintech

In their file, not in our database: daily payment reconciliation against the provider and the bank

The provider paid out, the total doesn't match your database — which payment is it? Daily reconciliation on Django and PostgreSQL: mismatch kinds, safe auto-closing, the ledger and alerts.

Monday morning, a message from finance: "The provider paid out Friday's payments, but the amount is lower than the admin report says. Where's the difference?" You check the database: the sum of Friday's success payments is one number, the bank statement shows another. You subtract the fees — still off. Now someone has to find one or two transactions among thousands, by hand, in a spreadsheet.

Or the reverse: a customer writes to support — "my card was charged, my balance wasn't topped up." In the database the payment is still pending: the provider's callback never arrived. Had the customer not written, nobody would have known.

Same problem both times: the system trusts its own database, while the truth — where the money actually is — lives somewhere else. Reconciliation is the process of comparing those sources regularly, automatically and explainably. In this post we'll build it from scratch on Django and PostgreSQL: importing the provider statement, finding mismatches with SQL, closing them automatically and by hand, tying it to the ledger, and reporting and alerting.

Where the differences come from

However carefully the integration is written, your database and the provider's data will drift apart from time to time. The usual reasons:

  • Lost callback. The provider completed the payment, but the webhook never reached you, or failed and ran out of retries. Ours: pending, theirs: success.
  • Timeout. You called the provider, gave up waiting and marked the payment failed. The provider finished the operation anyway.
  • Late cancellation. The operation was cancelled on the provider's side (reversal, chargeback, manually by their support), and no notification reached you.
  • Day boundary. You count days in UTC, the provider in Tashkent time, the bank by its own business day (cut-off). A payment at 23:59:58 lands in two different days.
  • Amount units. One API returns amounts in tiyin, another in sum with a fractional part. One rounding step and you have an amount mismatch.
  • Fees. The bank receives the net amount after commission, not the gross.
  • Plain bugs. Duplicates, a wrong state transition, orphaned rows after a migration.

None of these can be eliminated by "writing better code": networks fail and external systems make mistakes too. So reconciliation isn't a fix for the integration — it's a separate control layer on top of it.

Why the naive approach breaks

The first idea is to compare two numbers at the end of the day: our total and what hit the bank. That raises an alarm but explains nothing. Worse, two errors can cancel each other out: one payment is extra on our side, another is extra on theirs, and the totals happen to match. The total checks out, so everything's fine — or so you think.

The second mistake is looping over the statement in Python and calling Payment.objects.get(...) for every row. It works at small volumes, but every row is a separate query, and more importantly, this approach never sees "we have it, they don't": you only check what's in the statement.

The third and most dangerous one is automatically running an UPDATE to make your database match the provider whenever you find a difference. It erases history and bypasses the state machine and the ledger. And if the error is on the provider's side, you've just copied it into your own data.

Proper reconciliation gives you four things: exactly which records differ, the kind of difference, who closed it and how, and a link to the money itself — the bank statement.

Three sources, two levels

In fintech there are usually three sources:

  1. Our database — the Payment table and the ledger.
  2. The provider statement — a daily file (CSV, XLSX or via API) listing every transaction: provider ID, amount, fee, status, time. The exact format depends on the provider, so take it from their docs and normalise it at import time.
  3. The bank statement — how much money actually arrived in your account. Usually a single aggregate transfer (settlement): the day's net amount.

So the comparison happens on two levels:

  • Transaction level: database ↔ provider statement. Every payment must match individually. This answers "which payment?".
  • Money level: provider statement ↔ bank statement. The net amount computed from the statement must equal what the bank received. This answers "did the money actually arrive?".

Models

On our side, assume a Payment model already exists with provider, provider_txn_id, amount_tiyin, status and performed_at, plus a unique constraint on (provider, provider_txn_id). Amounts are stored as integers in tiyin — no floats.

Reconciliation needs four models:

# recon/models.py
from django.conf import settings
from django.db import models
from django.db.models import Q


class StatementBatch(models.Model):
    provider = models.CharField(max_length=32)
    statement_date = models.DateField()
    file_sha256 = models.CharField(max_length=64, unique=True)
    line_count = models.PositiveIntegerField()
    total_tiyin = models.BigIntegerField()
    imported_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [models.Index(fields=["provider", "statement_date"])]


class StatementLine(models.Model):
    batch = models.ForeignKey(StatementBatch, on_delete=models.PROTECT, related_name="lines")
    external_id = models.CharField(max_length=64)
    status = models.CharField(max_length=16)  # normalised: success / cancelled
    amount_tiyin = models.BigIntegerField()
    fee_tiyin = models.BigIntegerField(default=0)
    performed_at = models.DateTimeField()
    raw = models.JSONField()  # the original row, exactly as the provider sent it

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=["batch", "external_id"], name="uniq_line_per_batch"),
        ]
        indexes = [models.Index(fields=["external_id"])]


class ReconciliationRun(models.Model):
    provider = models.CharField(max_length=32)
    batch = models.ForeignKey(StatementBatch, on_delete=models.PROTECT)
    started_at = models.DateTimeField(auto_now_add=True)
    finished_at = models.DateTimeField(null=True)
    stats = models.JSONField(default=dict)


class Discrepancy(models.Model):
    class Kind(models.TextChoices):
        MISSING_OURS = "missing_ours"      # they have it, we don't
        MISSING_THEIRS = "missing_theirs"  # we have it, they don't
        AMOUNT = "amount"
        STATUS = "status"

    class State(models.TextChoices):
        OPEN = "open"
        AUTO_RESOLVED = "auto_resolved"
        RESOLVED = "resolved"

    run = models.ForeignKey(ReconciliationRun, on_delete=models.PROTECT, related_name="discrepancies")
    kind = models.CharField(max_length=16, choices=Kind.choices)
    state = models.CharField(max_length=16, choices=State.choices, default=State.OPEN)
    payment = models.ForeignKey("payments.Payment", null=True, on_delete=models.PROTECT)
    line = models.ForeignKey(StatementLine, null=True, on_delete=models.PROTECT)
    our_amount_tiyin = models.BigIntegerField(null=True)
    their_amount_tiyin = models.BigIntegerField(null=True)
    note = models.TextField(blank=True)
    resolved_by = models.ForeignKey(
        settings.AUTH_USER_MODEL, null=True, blank=True, on_delete=models.PROTECT
    )
    resolved_at = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["kind", "payment", "line"],
                condition=Q(state="open"),
                nulls_distinct=False,
                name="uniq_open_discrepancy",
            ),
        ]

A few deliberate decisions:

  • file_sha256 is unique — the same file is never imported twice.
  • raw is the original statement row. When a dispute comes up — "what exactly did the provider send?" — the answer is right there.
  • The partial unique constraint on Discrepancy prevents the same open discrepancy from being created twice, so you can re-run reconciliation as often as you like. nulls_distinct=False (Django 5.0+, PostgreSQL 15+) is essential: one of payment or line is often NULL, and by default PostgreSQL treats two NULLs as distinct, which would make the constraint useless.

Importing the statement

# recon/importer.py
import csv
import hashlib
import io
from datetime import date, datetime
from decimal import Decimal
from zoneinfo import ZoneInfo

from django.db import transaction

from .models import StatementBatch, StatementLine

PROVIDER_TZ = ZoneInfo("Asia/Tashkent")
# Map provider statuses to our vocabulary. An unknown status raises KeyError on purpose.
STATUS_MAP = {"SUCCESS": "success", "CANCELLED": "cancelled"}


def to_tiyin(value: str) -> int:
    cleaned = value.strip().replace(" ", "").replace(" ", "").replace(",", ".")
    tiyin = Decimal(cleaned) * 100
    if tiyin != tiyin.to_integral_value():
        raise ValueError(f"fraction of a tiyin: {value!r}")
    return int(tiyin)


def import_statement(provider: str, statement_date: date, content: bytes) -> StatementBatch:
    digest = hashlib.sha256(content).hexdigest()
    if existing := StatementBatch.objects.filter(file_sha256=digest).first():
        return existing  # same file again: nothing to do

    reader = csv.DictReader(io.StringIO(content.decode("utf-8-sig")), delimiter=";")
    lines: list[StatementLine] = []
    for row in reader:
        lines.append(
            StatementLine(
                external_id=row["transaction_id"].strip(),
                status=STATUS_MAP[row["status"].strip().upper()],
                amount_tiyin=to_tiyin(row["amount"]),
                fee_tiyin=to_tiyin(row.get("fee") or "0"),
                performed_at=datetime.strptime(
                    row["performed_at"], "%Y-%m-%d %H:%M:%S"
                ).replace(tzinfo=PROVIDER_TZ),
                raw=row,
            )
        )

    with transaction.atomic():
        batch = StatementBatch.objects.create(
            provider=provider,
            statement_date=statement_date,
            file_sha256=digest,
            line_count=len(lines),
            total_tiyin=sum(line.amount_tiyin for line in lines),
        )
        for line in lines:
            line.batch = batch
        StatementLine.objects.bulk_create(lines, batch_size=2000)
    return batch

The importer is strict on purpose: an unknown status or a fraction of a tiyin makes it fail with KeyError or ValueError. A failed import and a notified human beat a statement imported "approximately". The batch and its lines are written in one transaction — there's no such thing as a half-imported file.

If the provider puts control totals (line count and total amount) at the end of the file, compare them with line_count and total_tiyin. That verifies the file itself arrived complete.

Finding mismatches with SQL

The real work is two queries. The first goes from the statement side: for every statement line, do we have a matching payment, and do the amount and status agree?

-- THEIR_SIDE_SQL: every statement line vs. our payments
SELECT l.id, l.amount_tiyin, l.status,
       p.id, p.amount_tiyin, p.status
FROM recon_statementline AS l
LEFT JOIN payments_payment AS p
       ON p.provider = %(provider)s
      AND p.provider_txn_id = l.external_id
WHERE l.batch_id = %(batch_id)s
  AND (p.id IS NULL
       OR p.amount_tiyin <> l.amount_tiyin
       OR p.status <> l.status);

The second goes from our side: a payment is successful for us but appears in none of the provider's statements.

-- OUR_SIDE_SQL: successful payments of one day that appear in no statement at all
SELECT p.id, p.amount_tiyin
FROM payments_payment AS p
WHERE p.provider = %(provider)s
  AND p.status = 'success'
  AND p.performed_at >= %(start)s
  AND p.performed_at <  %(end)s
  AND NOT EXISTS (
        SELECT 1
        FROM recon_statementline AS l
        JOIN recon_statementbatch AS b ON b.id = l.batch_id
        WHERE b.provider = p.provider
          AND l.external_id = p.provider_txn_id
  );

Note that the second query compares against all of the provider's statements, not one batch. A payment at 23:59:58 may show up in tomorrow's file, so we only conclude "they don't have it" with a lag: when the statement for day D arrives, we check our side for day D−2. That way payments on the day boundary don't produce false alarms.

On the Python side we turn the results into Discrepancy rows:

# recon/matching.py
from datetime import date, datetime, time, timedelta
from zoneinfo import ZoneInfo

from django.db import connection
from django.utils import timezone

from .models import Discrepancy, ReconciliationRun, StatementBatch
from .sql import OUR_SIDE_SQL, THEIR_SIDE_SQL  # the two queries above

TASHKENT = ZoneInfo("Asia/Tashkent")
MISSING_THEIRS_LAG_DAYS = 2


def day_bounds(day: date) -> tuple[datetime, datetime]:
    start = datetime.combine(day, time.min, tzinfo=TASHKENT)
    return start, start + timedelta(days=1)


def run_reconciliation(batch: StatementBatch) -> ReconciliationRun:
    run = ReconciliationRun.objects.create(provider=batch.provider, batch=batch)
    found: list[Discrepancy] = []

    with connection.cursor() as cur:
        cur.execute(THEIR_SIDE_SQL, {"provider": batch.provider, "batch_id": batch.id})
        for line_id, their_amount, _their_status, payment_id, our_amount, _our_status in cur.fetchall():
            if payment_id is None:
                kind = Discrepancy.Kind.MISSING_OURS
            elif our_amount != their_amount:
                kind = Discrepancy.Kind.AMOUNT
            else:
                kind = Discrepancy.Kind.STATUS
            found.append(Discrepancy(
                run=run, kind=kind, line_id=line_id, payment_id=payment_id,
                our_amount_tiyin=our_amount, their_amount_tiyin=their_amount,
            ))

        start, end = day_bounds(batch.statement_date - timedelta(days=MISSING_THEIRS_LAG_DAYS))
        cur.execute(OUR_SIDE_SQL, {"provider": batch.provider, "start": start, "end": end})
        for payment_id, our_amount in cur.fetchall():
            found.append(Discrepancy(
                run=run, kind=Discrepancy.Kind.MISSING_THEIRS,
                payment_id=payment_id, our_amount_tiyin=our_amount,
            ))

    # ON CONFLICT DO NOTHING: an already open discrepancy is not created twice
    Discrepancy.objects.bulk_create(found, ignore_conflicts=True)
    run.finished_at = timezone.now()
    run.stats = {"lines": batch.line_count, "candidates": len(found)}
    run.save(update_fields=["finished_at", "stats"])
    return run

bulk_create(..., ignore_conflicts=True) becomes ON CONFLICT DO NOTHING on PostgreSQL: a discrepancy that's already open isn't created again.

Both queries need indexes: payments_payment (provider, provider_txn_id) (the unique constraint provides it), recon_statementline (external_id), and a separate one for the day-window lookup. Check the plan yourself:

CREATE INDEX CONCURRENTLY IF NOT EXISTS payment_recon_day_idx
    ON payments_payment (provider, status, performed_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id
FROM payments_payment AS p
WHERE p.provider = 'psp_a'
  AND p.status = 'success'
  AND p.performed_at >= '2026-11-18 00:00+05'
  AND p.performed_at <  '2026-11-19 00:00+05';

In a Django project, add the index through a migration (django.contrib.postgres.operations.AddIndexConcurrently), not by hand. If EXPLAIN shows a Seq Scan over a big table, reconciliation will get slower as volume grows.

Four kinds of mismatch

Kind Meaning Typical cause Auto-close
missing_ours They have it, we don't Callback never arrived, payment never created No — the most dangerous case
missing_theirs Ours is success, they don't have it Forged or mishandled callback, bug No
amount Amounts differ Units (sum/tiyin), rounding, partial refund Never
status Statuses differ Lost callback, timeout, late cancellation Yes, if the provider API confirms

missing_ours is especially serious: the customer was charged, but you didn't deliver or didn't top up the balance. missing_theirs is the opposite: you delivered, but the money never came. Both need a human decision.

Automatic vs. manual review

Send every discrepancy to a human and within a week nobody reads them. Close everything automatically and reconciliation becomes pointless. The rule is simple: only close automatically what is confirmed by an independent source and fits a normal state-machine transition.

# recon/resolve.py
from django.db import transaction
from django.utils import timezone

from payments.models import Payment

from .models import Discrepancy


def close(d: Discrepancy, state: str, note: str, user=None) -> None:
    d.state, d.note, d.resolved_by, d.resolved_at = state, note, user, timezone.now()
    d.save(update_fields=["state", "note", "resolved_by", "resolved_at"])


def try_auto_resolve(d: Discrepancy, client) -> bool:
    if d.kind != Discrepancy.Kind.STATUS:
        return False

    # Network call outside the transaction: no row lock is held while we wait.
    remote = client.get_status(d.payment.provider_txn_id)  # normalised: success / cancelled

    with transaction.atomic():
        payment = Payment.objects.select_for_update().get(pk=d.payment_id)
        if remote == payment.status:
            close(d, Discrepancy.State.AUTO_RESOLVED, f"statement outdated, provider API: {remote}")
            return True
        if payment.status == "pending" and remote == "success":
            payment.mark_success(source="reconciliation")  # the same transition the callback uses
            close(d, Discrepancy.State.AUTO_RESOLVED, "lost callback, confirmed via provider API")
            return True
    return False  # everything else goes to a human

What's going on here:

  1. Only status discrepancies are considered for auto-closing.
  2. We ask the provider API for the current status — fresher than the statement. The call happens outside the transaction, so no row lock is held while we wait on the network.
  3. If the API confirms our status (say, the statement said success, it was cancelled later, and we have cancelled too), the difference is just timing — close it.
  4. If we have pending and the API says success, it's a lost callback. We move the payment through the same transition the callback uses: mark_success() contains the state check, the ledger entry and the other side effects. No raw UPDATE.
  5. Everything else, including unusual transitions like failed → success, stays open and goes to a human.

For manual review, Django admin is enough: a list of open discrepancies, filters by kind and age, the raw row on display, and a "close" action. What matters is that every closed discrepancy records who closed it, when, and why (note). A discrepancy closed without a note is an open question for the auditors.

Tying it to the ledger

Reconciliation has to close the books, not just the Payment table. In a double-entry ledger, a single clearing account makes this easy:

  • When a payment succeeds: Dr receivable:<provider> (the provider owes us) / Cr customer balance or revenue.
  • When the settlement shows up on the bank statement: Dr bank:main (net amount), Dr expense:psp_fees (fee) / Cr receivable:<provider> (gross amount).

If everything is right, the balance of receivable:<provider> equals the sum of payments not yet settled. That's a simple but powerful invariant for the whole system.

# recon/settlement.py
from django.db.models import Q, Sum

from ledger.services import post_entry  # postings: (account, amount), debit > 0, credit < 0

from .models import StatementBatch


def book_settlement(batch: StatementBatch, bank_amount_tiyin: int, bank_ref: str) -> int:
    agg = batch.lines.aggregate(
        gross=Sum("amount_tiyin", filter=Q(status="success"), default=0),
        fee=Sum("fee_tiyin", filter=Q(status="success"), default=0),
    )
    gross, fee = agg["gross"], agg["fee"]
    diff = (gross - fee) - bank_amount_tiyin  # > 0: the bank received less than expected

    postings = [
        ("bank:main", bank_amount_tiyin),          # debit: money that actually arrived
        ("expense:psp_fees", fee),                 # debit: provider commission
        (f"receivable:{batch.provider}", -gross),  # credit: the provider no longer owes it
    ]
    if diff:
        postings.append(("suspense:reconciliation", diff))  # keeps the entry balanced
    post_entry(
        reference=f"settlement:{batch.provider}:{batch.statement_date}:{bank_ref}",
        postings=postings,
    )
    return diff

Even when there's a difference, the entry must balance, so the difference goes to a separate suspense:reconciliation account. A non-zero balance there means there's an open question. How refunds and cancellations are netted out of the settlement depends on your contract with the provider — adapt the formula to it.

Old ledger entries are never modified. When a difference is resolved — the provider paid the shortfall in the next settlement, or you wrote it off as a loss — you post a new adjusting entry from suspense to the right account, with the discrepancy ID as its reference.

Reporting and alerting

The whole process runs daily via Celery beat:

# recon/tasks.py
from datetime import date, timedelta

from celery import shared_task
from django.db.models import Count
from django.utils import timezone

from .clients import StatementNotReady, get_client
from .importer import import_statement
from .matching import run_reconciliation
from .models import Discrepancy
from .notify import notify  # Telegram, Slack, e-mail: whatever the team actually reads
from .resolve import try_auto_resolve


@shared_task(bind=True, max_retries=8, default_retry_delay=30 * 60)
def reconcile_day(self, provider: str, day_iso: str | None = None):
    day = date.fromisoformat(day_iso) if day_iso else timezone.localdate() - timedelta(days=1)
    client = get_client(provider)

    try:
        content = client.download_statement(day)
    except StatementNotReady as exc:
        if self.request.retries >= self.max_retries:
            notify(f"[recon] {provider}: statement for {day} has not arrived", urgent=True)
            return
        # pin the day explicitly, otherwise a retry after midnight checks the wrong day
        raise self.retry(exc=exc, args=(provider, day.isoformat()))

    batch = import_statement(provider, day, content)
    run_reconciliation(batch)

    open_qs = Discrepancy.objects.filter(run__provider=provider, state=Discrepancy.State.OPEN)
    for d in open_qs.filter(kind=Discrepancy.Kind.STATUS).select_related("payment"):
        try_auto_resolve(d, client)

    by_kind = dict(open_qs.order_by().values_list("kind").annotate(n=Count("id")))
    stale = open_qs.filter(created_at__lt=timezone.now() - timedelta(days=3)).count()
    notify(
        f"[recon] {provider} {day}: {batch.line_count} lines, open: {by_kind}, older than 3 days: {stale}",
        urgent=bool(by_kind.get("missing_ours") or by_kind.get("amount") or stale),
    )
# config/celery.py
from celery import Celery
from celery.schedules import crontab

app = Celery("config")
app.config_from_object("django.conf:settings", namespace="CELERY")
app.conf.timezone = "Asia/Tashkent"
app.conf.beat_schedule = {
    "reconcile-psp-a": {
        "task": "recon.tasks.reconcile_day",
        "schedule": crontab(hour=9, minute=0),
        "args": ("psp_a",),
    },
}

The bank statement usually arrives through a different channel, so book_settlement is a separate task, triggered after the bank file is imported.

When to alert:

  • The statement didn't arrive. The most dangerous case, because it's silent: no discrepancies, because there's nothing to compare. Once retries are exhausted, always send a notification.
  • missing_ours or amount appeared — immediately.
  • An open discrepancy has been sitting for several days — a reminder.
  • The settlement difference isn't zero, or the suspense balance isn't zero.

A short daily summary helps too: how many lines were compared, how many auto-closed, how many are open. If it arrives every day, its absence becomes a signal in its own right.

"No differences" and "no check happened" are two different things. Zero discrepancies only means something if the statement arrived, was imported and was compared.

Pitfalls in production

  • Time zones. Always store performed_at timezone-aware, and find out from the provider's docs which zone the statement times are in. Compute the day window in Asia/Tashkent and pass aware datetimes to the query.
  • Retries and dates. If the task recomputes "yesterday" on every attempt, a retry after midnight checks the wrong day. That's why the code above passes the date explicitly to retry(args=...).
  • Refunds. Many providers report a refund as a separate line, sometimes with a negative amount and in another day's statement. Reconcile partial refunds as their own model, or you'll drown in false amount mismatches.
  • Corrected statements. The provider may resend a file. Different hash — a new batch. Mark which batch is current (for example with a supersedes field) and account for that in the queries.
  • Duplicate provider IDs. A transaction can appear twice in one statement (a payment and its cancellation). The (batch, external_id) constraint will fail the import — which is good, because it tells you that you don't fully understand the format yet. Then you add a line-type field and widen the constraint.
  • Long transactions. The matching queries only read; don't wrap them in a large atomic() or mix them with select_for_update(). Locks are taken briefly, and only on the payment being changed.
  • Provider API limits. Auto-closing calls the API once per discrepancy. With many discrepancies you'll hit the rate limit: throttle the calls or split them into separate tasks.
  • "Fixing" the database to match the statement. Even when it looks obvious, don't run UPDATE payments_payment SET status = ... by hand. Go through the state machine and the ledger, or the next run will find a new mismatch between the ledger and Payment.

Checklist

  • Amounts stored as integers in tiyin; no floats.
  • Statements imported idempotently by hash, the original row kept in raw.
  • Two-way comparison: statement → database and database → all statements.
  • "They don't have it" is concluded with a lag for the day boundary.
  • Auto-close only status discrepancies, and only with confirmation from the provider API.
  • Every fix goes through the state machine and the ledger; old records never change.
  • Settlement is compared with the bank statement; the difference goes to suspense.
  • Statement missing — alert. Silence is a signal too.
  • Every closed discrepancy records who closed it, when and why.

Takeaway

No matter how good the integration is, your database and the provider will diverge — the only question is who notices first: you or the customer. Daily reconciliation makes those differences visible, with concrete records, kinds and an owner. Build it on a two-way SQL comparison, cautious auto-closing and a single clearing account in the ledger, and "where's the money?" becomes a question you answer in minutes.

// comments

Loading comments...

    // next step

    Let's build something together.

    Message me on Telegram
    aslbek@asldev: ~
    ❯