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:
- Our database — the
Paymenttable and the ledger. - 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.
- 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_sha256is unique — the same file is never imported twice.rawis 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
Discrepancyprevents 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 ofpaymentorlineis oftenNULL, and by default PostgreSQL treats twoNULLs 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:
- Only
statusdiscrepancies are considered for auto-closing. - 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.
- If the API confirms our status (say, the statement said
success, it was cancelled later, and we havecancelledtoo), the difference is just timing — close it. - If we have
pendingand the API sayssuccess, 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 rawUPDATE. - 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), Drexpense:psp_fees(fee) / Crreceivable:<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_oursoramountappeared — immediately.- An open discrepancy has been sitting for several days — a reminder.
- The settlement difference isn't zero, or the
suspensebalance 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_attimezone-aware, and find out from the provider's docs which zone the statement times are in. Compute the day window inAsia/Tashkentand 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
amountmismatches. - Corrected statements. The provider may resend a file. Different hash — a new batch. Mark which batch is current (for example with a
supersedesfield) 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 withselect_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 andPayment.
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
statusdiscrepancies, 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...