Reestrda bor, bazada yo'q: to'lovlarni provayder reestri va bank ko'chirmasi bilan har kuni solishtirish
Provayder pul o'tkazdi, summa esa bazaga to'g'ri kelmaydi — qaysi to'lov? Django va PostgreSQL'da kunlik rekonsiliatsiya: farq turlari, avtomatik va qo'lda yopish, ledger va ogohlantirishlar.
Dushanba ertalab moliya bo'limidan xabar keladi: "Provayder juma kungi to'lovlar uchun hisobimizga pul o'tkazdi, lekin summa admin paneldagi hisobotdan kam. Farq qayerda?" Bazaga qaraysiz: juma kungi success to'lovlar yig'indisi bir son, bank ko'chirmasidagi tushum boshqa son. Komissiyani ayirib ko'rasiz — baribir to'g'ri kelmaydi. Endi minglab tranzaksiya ichidan bir-ikkitasini topish kerak, va buni Excel'da, qo'lda qilish kerak.
Yoki teskari holat: mijoz support'ga yozadi — "kartamdan pul yechildi, balansim to'lmadi". Bazada bu to'lov hamon pending: provayderning callback'i kelmagan. Mijoz yozmaganida, bu haqda hech kim bilmay qolardi.
Ikkala holatda muammo bitta: tizim o'z bazasiga ishonadi, haqiqat esa — pul aslida qayerda ekani — boshqa joyda yozilgan. Rekonsiliatsiya (reconciliation, ruscha "сверка") — shu manbalarni muntazam, avtomatik va izohlanadigan tarzda solishtirish jarayoni. Bu maqolada uni Django va PostgreSQL'da noldan quramiz: reestrni import qilish, mos kelmasliklarni SQL bilan topish, ularni avtomatik va qo'lda yopish, ledger bilan bog'lash, hisobot va ogohlantirishlar.
Farq qayerdan paydo bo'ladi
Integratsiya qanchalik ehtiyotkor yozilmasin, baza va provayder ma'lumotlari vaqti-vaqti bilan baribir ajraladi. Odatiy sabablar:
- Yo'qolgan callback. Provayder to'lovni o'tkazdi, lekin webhook yetib kelmadi yoki xato qaytardi va qayta urinishlar tugadi. Bizda
pending, ulardasuccess. - Timeout. Provayderga so'rov yubordingiz, javob kutish vaqti tugadi va to'lovni
faileddeb belgiladingiz. Provayder esa operatsiyani oxirigacha bajargan. - Keyinroq bekor qilish. Operatsiya provayder tomonida bekor qilindi (reversal, chargeback yoki support orqali qo'lda), lekin bizga xabar kelmadi.
- Kun chegarasi. Siz kunni UTC bo'yicha hisoblaysiz, provayder Toshkent vaqti bo'yicha, bank esa o'zining operatsion kuni (cut-off) bo'yicha. 23:59:58 dagi to'lov ikki xil kunga tushib qoladi.
- Summa birligi. Bir API summani tiyinda beradi, boshqasi so'mda kasr bilan. Bir joyda yaxlitlash — va summa farqi tayyor.
- Komissiya. Bankka yalpi summa emas, komissiya ushlab qolingandan keyingi sof summa tushadi.
- Oddiy bug. Dublikat yozuv, noto'g'ri holat o'tishi, migratsiyadan keyin qolgan "yetim" qatorlar.
Bularning hech birini "yaxshiroq kod yozib" butunlay yo'qotib bo'lmaydi: tarmoq uziladi, tashqi tizimlar ham xato qiladi. Shuning uchun rekonsiliatsiya integratsiyani tuzatish emas, balki uning ustidagi alohida nazorat qatlami.
Nega oddiy yondashuv ishlamaydi
Birinchi xayolga keladigan yechim — kun oxirida ikkita sonni solishtirish: bizdagi yig'indi va bankka tushgan summa. Bu xavotir signalini beradi, lekin hech narsani tushuntirmaydi. Undan ham yomoni, ikki xato bir-birini yopib qo'yishi mumkin: bir to'lov bizda ortiqcha, boshqasi ularda ortiqcha, summalar esa tasodifan teng. Yig'indi to'g'ri — demak muammo yo'q, deb o'ylaysiz.
Ikkinchi xato — reestrni Python'da qatorma-qator aylanib, har bir qator uchun Payment.objects.get(...) chaqirish. Kichik hajmda ishlaydi, lekin har bir qator alohida query bo'ladi, va eng muhimi, "bizda bor, ularda yo'q" holatini bu usul umuman ko'rmaydi: siz faqat reestrdagi narsani tekshiryapsiz.
Uchinchi va eng xavflisi — farq topilganda bazani provayder ma'lumotiga moslab avtomatik UPDATE qilish. Bu tarixni o'chiradi, holat mashinasi va ledger'ni chetlab o'tadi. Agar xato provayder tomonida bo'lsa, siz uni o'z bazangizga ko'chirib olgan bo'lasiz.
To'g'ri rekonsiliatsiya to'rt narsani beradi: aynan qaysi yozuvlar farq qilishini, farq turini, uni kim va qanday yopganini, hamda pulning o'zi — bank ko'chirmasi bilan bog'liqligini.
Uch manba, ikki daraja
Fintech'da odatda uchta manba bo'ladi:
- Bizning baza —
Paymentjadvali va ledger. - Provayder reestri — kunlik fayl (CSV, XLSX yoki API orqali). Unda har bir tranzaksiya: provayder ID, summa, komissiya, holat, vaqt. Aniq formati provayderga bog'liq, shuning uchun uni hujjatdan o'qing va import qatlamida normallashtiring.
- Bank ko'chirmasi — hisobingizga haqiqatda qancha pul tushgani. Odatda bu bitta yig'ma o'tkazma (settlement): kunning sof summasi.
Shunga ko'ra solishtirish ham ikki darajada bo'ladi:
- Tranzaksiya darajasi: baza ↔ reestr. Har bir to'lov alohida mos kelishi kerak. Bu "qaysi to'lov?" degan savolga javob beradi.
- Pul darajasi: reestr ↔ bank ko'chirmasi. Reestr bo'yicha hisoblangan sof summa bankka tushgan summaga teng bo'lishi kerak. Bu "pul haqiqatan keldimi?" degan savolga javob beradi.
Modellar
Bizning tomonda Payment modeli allaqachon bor deb olamiz: provider, provider_txn_id, amount_tiyin, status, performed_at maydonlari va (provider, provider_txn_id) juftligiga unique cheklov. Summalar butun son ko'rinishida, tiyinda saqlanadi — float yo'q.
Rekonsiliatsiya uchun to'rtta model kerak:
# 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",
),
]
Bu yerda bir nechta muhim qaror bor:
file_sha256unique — bitta fayl ikki marta import qilinmaydi.raw— reestrdagi asl qator. Bahs chiqqanda "provayder aynan nima yuborgan edi?" degan savolga javob shu yerda turadi.Discrepancydagi partial unique cheklov bir xil ochiq farqni ikki marta yaratishga yo'l qo'ymaydi, shuning uchun rekonsiliatsiyani xohlagancha qayta ishga tushirish mumkin.nulls_distinct=False(Django 5.0+, PostgreSQL 15+) shart, chunkipaymentyokilinedan biri ko'pinchaNULLbo'ladi, odatiy holatda esa PostgreSQL ikkitaNULLni har xil qiymat deb hisoblaydi va cheklov ishlamay qoladi.
Reestrni import qilish
# 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
Import ataylab "qattiq" yozilgan: noma'lum holat yoki tiyindan kichik qism chiqsa, KeyError yoki ValueError bilan yiqiladi. Reestrni "taxminan" import qilgandan ko'ra, import yiqilib, odam xabar olgani yaxshiroq. Batch va uning qatorlari bitta transaction ichida yoziladi — yarim import qilingan fayl bo'lmaydi.
Agar provayder fayl oxirida nazorat yig'indisini (qatorlar soni va umumiy summa) bersa, uni line_count va total_tiyin bilan solishtiring. Bu faylning o'zi to'liq yetib kelganini tekshiradi.
Mos kelmasliklarni SQL bilan topish
Asosiy ish ikkita query'da. Birinchisi reestr tomonidan boradi: reestrdagi har bir qator uchun bizda mos to'lov bormi, summasi va holati bir xilmi.
-- 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);
Ikkinchisi bizning tomondan: to'lov bizda muvaffaqiyatli, lekin provayderning hech bir reestrida yo'q.
-- 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
);
E'tibor bering: ikkinchi query bitta batch bilan emas, provayderning barcha reestrlari bilan solishtiradi. 23:59:58 dagi to'lov ertangi reestrga tushishi mumkin, shuning uchun "ularda yo'q" degan xulosani kechikish (lag) bilan chiqaramiz: D kun reestri kelganda, bizning tomonni D−2 kun uchun tekshiramiz. Shunda kun chegarasidagi to'lovlar soxta signal bermaydi.
Python tomonida natijani Discrepancy yozuvlariga aylantiramiz:
# 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) PostgreSQL'da ON CONFLICT DO NOTHING ga aylanadi: allaqachon ochiq turgan farq qayta yaratilmaydi.
Ikkala query uchun index kerak: payments_payment (provider, provider_txn_id) (unique cheklov buni beradi), recon_statementline (external_id) va kun oynasi bo'yicha qidirish uchun alohida index. Rejani albatta o'zingiz tekshiring:
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';
Django loyihasida index'ni migratsiya orqali qo'shing (django.contrib.postgres.operations.AddIndexConcurrently), qo'lda emas. EXPLAIN natijasida katta jadval bo'ylab Seq Scan ko'rinsa, rekonsiliatsiya hajm o'sgan sari sekinlashadi.
To'rt xil mos kelmaslik
| Tur | Ma'nosi | Odatiy sabab | Avtomatik yopish |
|---|---|---|---|
missing_ours |
Ularda bor, bizda yo'q | Callback umuman kelmagan, to'lov yaratilmay qolgan | Yo'q — eng xavfli holat |
missing_theirs |
Bizda success, ularda yo'q |
Soxta yoki xato qayta ishlangan callback, bug | Yo'q |
amount |
Summa farq qiladi | Birlik (so'm/tiyin), yaxlitlash, qisman qaytarish | Hech qachon |
status |
Holat farq qiladi | Yo'qolgan callback, timeout, keyinroq bekor qilish | Ha, provayder API tasdiqlasa |
missing_ours ayniqsa jiddiy: mijozdan pul yechilgan, siz esa xizmat ko'rsatmagansiz yoki balansni to'ldirmagansiz. missing_theirs buning teskarisi: xizmat ko'rsatilgan, pul esa kelmagan. Ikkalasi ham odam qarorini talab qiladi.
Avtomatik va qo'lda ko'rib chiqish
Hamma farqni odamga yuborsangiz, bir haftadan keyin ularni hech kim o'qimaydi. Hammasini avtomatik yopsangiz, rekonsiliatsiyaning ma'nosi qolmaydi. Qoida oddiy: faqat mustaqil manba tasdiqlagan va holat mashinasining oddiy o'tishiga tushadigan holatlar avtomatik yopiladi.
# 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
Bu yerda nima bo'lyapti:
- Faqat
statusturidagi farqlar avtomatik ko'riladi. - Provayder API'dan joriy holat so'raladi — bu reestrdan yangiroq ma'lumot. So'rov transaction'dan tashqarida: tarmoqni kutayotganda qatorda lock ushlab turilmaydi.
- Agar API bizdagi holatni tasdiqlasa (masalan, reestrda
success, keyin bekor qilingan va bizda hamcancelled), farq shunchaki vaqt bilan bog'liq — yopamiz. - Agar bizda
pending, API'dasuccessbo'lsa, bu yo'qolgan callback. To'lovni callback ishlatadigan o'sha holat o'tishi orqali o'tkazamiz:mark_success()ichida holat tekshiruvi, ledger yozuvi va boshqa side-effect'lar bor. To'g'ridan-to'g'riUPDATEyo'q. - Qolgan hamma narsa, jumladan
failed→successkabi g'ayrioddiy o'tishlar, ochiq qoladi va odamga boradi.
Qo'lda ko'rib chiqish uchun Django admin yetarli: ochiq farqlar ro'yxati, turi va yoshi bo'yicha filtr, raw qatorni ko'rsatish va "yopish" action'i. Muhimi — har bir yopilgan farqda kim yopgani, qachon va nima uchun (note) yozilgan bo'lsin. Izohsiz yopilgan farq audit uchun ochiq savol bo'lib qoladi.
Ledger bilan bog'lash
Rekonsiliatsiya faqat Payment jadvalini emas, buxgalteriyani ham yopishi kerak. Double-entry ledger'da buni bitta oraliq hisob (clearing account) bilan qilish qulay:
- To'lov muvaffaqiyatli bo'lganda: Dt
receivable:<provider>(provayder bizga qarzdor) / Kt mijoz balansi yoki daromad. - Bank ko'chirmasida settlement ko'ringanda: Dt
bank:main(sof summa), Dtexpense:psp_fees(komissiya) / Ktreceivable:<provider>(yalpi summa).
Hammasi to'g'ri bo'lsa, receivable:<provider> balansi hali settlement qilinmagan to'lovlar yig'indisiga teng bo'ladi. Bu butun tizim uchun sodda, lekin kuchli invariant.
# 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
Farq bo'lsa ham ledger yozuvi balanslangan bo'lishi shart, shuning uchun farq alohida suspense:reconciliation hisobiga tushadi. Bu hisobning balansi nol bo'lmasa — demak ochiq savol bor. Qaytarishlar va bekor qilishlar settlement'dan qanday ayirilishi provayder bilan shartnomaga bog'liq, formulani shunga moslang.
Eski ledger yozuvlari hech qachon o'zgartirilmaydi. Farq hal bo'lganda — masalan, provayder yetishmagan summani keyingi settlement'da o'tkazdi yoki biz uni zarar sifatida qabul qildik — suspense dan tegishli hisobga yangi tuzatuvchi yozuv (adjustment) qilinadi va unga farq ID'si reference sifatida yoziladi.
Hisobot va ogohlantirish
Butun jarayon Celery beat orqali har kuni ishlaydi:
# 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",),
},
}
Bank ko'chirmasi odatda boshqa kanal orqali keladi, shuning uchun book_settlement alohida vazifa bo'lib, bank fayli import qilingandan keyin chaqiriladi.
Ogohlantirish qachon kerak:
- Reestr kelmadi. Bu eng xavfli holat, chunki u jim o'tadi: farq yo'q, chunki solishtiradigan narsaning o'zi yo'q. Retry'lar tugagach, albatta xabar yuboriladi.
missing_oursyokiamountpaydo bo'ldi — darhol.- Ochiq farq bir necha kundan beri turibdi — eslatma.
- Settlement farqi nol emas yoki
suspensebalansi nol emas.
Kunlik qisqa xulosa ham foydali: nechta qator solishtirildi, nechtasi avtomatik yopildi, nechtasi ochiq. Bunday xabar har kuni kelib tursa, uning kelmay qolishi ham signalga aylanadi.
Farq yo'qligi va tekshiruv o'tkazilmagani — ikki xil narsa. "Nol farq" faqat reestr kelgan, import qilingan va solishtirilgan bo'lsagina ma'noga ega.
Production'da uchraydigan tuzoqlar
- Vaqt zonasi.
performed_atni doim timezone-aware saqlang va reestrdagi vaqt qaysi zonada ekanini provayder hujjatidan aniqlang. Kun oynasiniAsia/Tashkentbo'yicha hisoblab, query'ga aware datetime sifatida bering. - Retry va sana. Celery vazifasi "kecha"ni har safar qaytadan hisoblasa, yarim tundan keyingi retry boshqa kunni tekshiradi. Shuning uchun yuqoridagi kodda sana
retry(args=...)ga aniq uzatiladi. - Qaytarishlar (refund). Ko'p provayderlar qaytarishni alohida qator sifatida, ba'zan manfiy summa bilan va boshqa kunning reestrida beradi. Qisman qaytarishni alohida model sifatida solishtiring, aks holda soxta
amountfarqlari ko'payadi. - Tuzatilgan reestr. Provayder faylni qayta yuborishi mumkin. Hash boshqa — yangi batch yaratiladi. Qaysi batch amaldagi ekanini belgilang (masalan,
supersedesmaydoni) va query'larni shunga moslang. - Dublikat provayder ID. Bitta tranzaksiya bitta reestrda ikki marta kelishi mumkin (to'lov va uning bekor qilinishi).
(batch, external_id)cheklovi bunda importni yiqitadi — bu yaxshi, chunki formatni to'liq tushunmaganingizni bildiradi. Keyin qatorga tur maydoni qo'shib, cheklovni kengaytirasiz. - Uzun transaction. Solishtirish query'lari faqat o'qiydi; ularni katta
atomic()ichiga o'rab,select_for_update()bilan aralashtirmang. Lock faqat bitta to'lov o'zgarayotganda, qisqa vaqtga olinadi. - Provayder API limiti. Avtomatik yopishda har bir farq uchun API chaqiriladi. Farqlar ko'payib ketsa, rate limit'ga urilasiz: chaqiruvlarni sekinlashtiring yoki alohida vazifalarga bo'ling.
- Bazani reestrga "moslash". Hatto hammasi aniq ko'rinsa ham, qo'lda
UPDATE payments_payment SET status = ...qilmang. Holat mashinasi va ledger orqali o'ting, aks holda keyingi tekshiruv ledger bilanPaymento'rtasida yangi farq topadi.
Checklist
- Summalar tiyinda, butun son sifatida saqlanadi; float yo'q.
- Reestr hash bo'yicha idempotent import qilinadi, asl qator
rawda turadi. - Solishtirish ikki tomonlama: reestr → baza va baza → barcha reestrlar.
- "Ularda yo'q" xulosasi kun chegarasi uchun lag bilan chiqariladi.
- Faqat
statusfarqi va faqat provayder API tasdig'i bilan avtomatik yopiladi. - Har qanday tuzatish holat mashinasi va ledger orqali; eski yozuvlar o'zgarmaydi.
- Settlement bank ko'chirmasi bilan solishtiriladi, farq
suspensehisobiga tushadi. - Reestr kelmasa — ogohlantirish. Jimlik ham signal.
- Har bir yopilgan farqda kim, qachon va nima uchun yopgani yozilgan.
Xulosa
Integratsiya qanchalik yaxshi bo'lmasin, baza va provayder o'rtasida farq baribir paydo bo'ladi — savol faqat uni kim birinchi topishida: sizmi yoki mijoz. Kunlik rekonsiliatsiya bu farqlarni aniq yozuvlar, turlar va mas'ul shaxs bilan ko'rinadigan qiladi. Uni ikki tomonlama SQL solishtiruv, ehtiyotkor avtomatik yopish va ledger'dagi bitta clearing hisob ustiga quring — shunda "pul qayerda?" degan savolga javob bir necha daqiqada topiladi.
// izohlar
Izohlar yuklanmoqda...