Reconcile 10,000 Daily UPI Transactions: Automated UTR Matching Guide

When an e-commerce brand, quick-commerce dark store, retail chain, or subscription platform scales past 10,000 transactions per day, the biggest operational bottleneck is rarely server CPU usage: it is the finance and accounting department drowning in bank statement reconciliation.

At 10,000 transactions daily, your corporate current account generates over 300,000 statement line items every month. Attempting to manually cross-check bank PDFs against Excel sheets or Tally ledgers leads to accounting discrepancies, delayed monthly book closes, unfulfilled customer orders, and severe tax audit headaches.

Here is the engineering and operational guide for building an automated three-way UTR reconciliation engine capable of matching tens of thousands of daily UPI transactions in sub-second time.

---

The Reconciliation Nightmare at 10,000 Transactions / Day

At high transaction volume, finance teams encounter four recurring failure states:

High-Volume Payment Failure States:
┌─────────────────────────────────────────────────────────────┐
│ 1. The "Ghost Credit": Bank shows money credited, but the   │
│    merchant database has no matching customer order ID.     │
│                                                             │
│ 2. The "Deemed Success Timeout": Customer was debited,      │
│    browser closed, and order sits in "PENDING" indefinitely.│
│                                                             │
│ 3. The "Cent Discrepancy": Payment gateways deducting       │
│    hidden rounding fees or chargeback adjustments.          │
│                                                             │
│ 4. Month-End Book Closes: Accountants taking 12 days to     │
│    finalize monthly financial statements.                   │
└─────────────────────────────────────────────────────────────┘

---

The Three-Way Reconciliation Architecture

To achieve 100% financial auditability, your accounting infrastructure must execute automated Three-Way Reconciliation:

┌──────────────────────────────────────────────────────────────────┐
│                   Three-Way Reconciliation Model                 │
├──────────────────────────────────────────────────────────────────┤
│                                                                  │
│  [Source A: Internal E-Commerce Orders]                          │
│  (Cart ID, Expected Amount, Customer Phone, Created Timestamp)   │
│                                ▲                                 │
│                                │ Matching Key: Order Reference   │
│                                ▼                                 │
│  [Source B: VyaparGateway / Switch Transaction Log]              │
│  (Session ID, Bank Reference UTR, Webhook Delivery Timestamp)    │
│                                ▲                                 │
│                                │ Matching Key: 12-Digit UTR      │
│                                ▼                                 │
│  [Source C: Core Banking Feed / MT940 Statement]                 │
│  (Bank Clear Date, Credit Amount, RRN / UTR, Closing Balance)    │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

A transaction is considered reconciled only when all three data nodes agree on amount, UTR, and timestamp.

---

Parsing Core Banking Feeds: MT940 & CAMT.053

Scheduled commercial banks (HDFC, ICICI, Axis, SBI) deliver automated daily statement feeds via SFTP using SWIFT MT940 or ISO 20022 CAMT.053 XML formats:

Sample MT940 Statement Line:
:61:2603300330CR4500,00NTRF609415892014//ORD-98412
:86:UPI/609415892014/P2M/PAYEE/ICICI/KABIR MEHTA

• Field :61: encodes the booking date (260330 = 30th March 2026), credit flag (CR), transaction amount (4500,00), and bank reference (609415892014).
• Field :86: encodes customer metadata and remittance info.

---

High-Throughput UTR Matching Algorithm (TypeScript)

Here is a production-grade TypeScript reconciliation service running against PostgreSQL:

import { prisma } from "../lib/prisma";

interface BankStatementRecord {
  utr: string;
  amount: number;
  clearedAt: Date;
  rawNarration: string;
}

interface ReconciliationResult {
  matchedCount: number;
  unmatchedCredits: BankStatementRecord[];
  discrepancies: Array<{ utr: string; expected: number; actual: number }>;
}

export async function executeAutomatedReconciliation(
  bankFeed: BankStatementRecord[]
): Promise<ReconciliationResult> {
  let matchedCount = 0;
  const unmatchedCredits: BankStatementRecord[] = [];
  const discrepancies: Array<{ utr: string; expected: number; actual: number }> = [];

  for (const record of bankFeed) {
    // 1. Look up payment in internal gateway logs by 12-digit UTR
    const internalPayment = await prisma.paymentLog.findUnique({
      where: { utr: record.utr },
      include: { order: true },
    });

    if (!internalPayment) {
      // Unmatched: Money arrived in bank, but no order record exists
      unmatchedCredits.push(record);
      continue;
    }

    // 2. Exact Amount Verification
    if (Number(internalPayment.amountPaid) !== record.amount) {
      discrepancies.push({
        utr: record.utr,
        expected: Number(internalPayment.amountPaid),
        actual: record.amount,
      });
      continue;
    }

    // 3. Mark Atomic State as Fully Reconciled
    await prisma.paymentLog.update({
      where: { id: internalPayment.id },
      data: {
        reconciliationStatus: "MATCHED",
        reconciledAt: new Date(),
        bankClearedAt: record.clearedAt,
      },
    });

    matchedCount++;
  }

  return { matchedCount, unmatchedCredits, discrepancies };
}

---

Automated Synchronization with Tally Prime & Zoho Books

Once the reconciliation algorithm matches the UTR records, the engine compiles a daily summary and pushes automated accounting entries into Tally Prime via its XML HTTP Interface:

<ENVELOPE>
  <HEADER>
    <TALLYREQUEST>Import Data</TALLYREQUEST>
  </HEADER>
  <BODY>
    <IMPORTDATA>
      <REQUESTDESC>
        <REPORTNAME>Vouchers</REPORTNAME>
      </REQUESTDESC>
      <REQUESTDATA>
        <TALLYMESSAGE xmlns:UDF="TallyUDF">
          <VOUCHER VCHTYPE="Receipt" ACTION="Create">
            <DATE>20260330</DATE>
            <NARRATION>UPI Settlement UTR: 609415892014</NARRATION>
            <ALLLEDGERENTRIES.LIST>
              <LEDGERNAME>HDFC Bank Current Account</LEDGERNAME>
              <ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE>
              <AMOUNT>-4500.00</AMOUNT>
            </ALLLEDGERENTRIES.LIST>
            <ALLLEDGERENTRIES.LIST>
              <LEDGERNAME>Sales Revenue Account</LEDGERNAME>
              <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
              <AMOUNT>4500.00</AMOUNT>
            </ALLLEDGERENTRIES.LIST>
          </VOUCHER>
        </TALLYMESSAGE>
      </REQUESTDATA>
    </IMPORTDATA>
  </BODY>
</ENVELOPE>

By deploying VyaparGateway’s automated reconciliation engine, enterprise finance teams compress a 10-day manual month-end closing process into a zero-touch, sub-minute automated execution, maintaining perfect books of account and complete tax compliance.