Manas Mishra
Résumé

Case study Datavio · Fintech

Penny-level reconciliation: every rupee traced from payment to invoice

Brands selling to quick-commerce and marketplace channels get paid in lumpy transfers, net of debit notes, TDS and charges. I built a reconciliation engine that explains every payment down to the paisa — and every invoice back to the payments that settled it.

Role
Led design & build · one channel co-developed with a teammate
Context
Datavio · receivables for marketplace sellers
Stack
Python · FastAPI · PostgreSQL · Azure
Inputs
POs, GRNs, invoices, debit notes, payment reports
Payments (UTRs) reconciled on one channel's first production run
88/88
Of 1,012 payments matched on another channel (1,007) after a bug fix
99.5%
Mismatches on a 982-row validation file — and a re-run wrote nothing
0
Agreement required when splitting a debit note across SKU lines
±₹0.01
reconciliation · payment lookupmapped
UTR ••••4821
Transfer received₹2,41,380.52
  • INV-10421 paid in full+1,12,450.00
  • INV-10433 part 2 of 2+98,210.40
  • INV-10440 paid in full+52,118.12
  • Debit note price difference−14,902.00
  • Early-payment charge−6,233.22
  • TDS 0.1%−262.78
Adds up to₹2,41,380.52
✓ difference₹0.00
An illustrative lookup (not real data): one transfer broken down into the invoices it paid and the deductions taken from it — adding up to the paisa.
TL;DR Brands selling to quick-commerce and marketplace channels get paid in bank transfers (UTRs) that cover several invoices at once and net off debit notes, TDS and early-payment charges. The engine pulls POs, GRNs, invoices, debit notes and payment reports for each brand, then links every UTR to the invoices it paid and every invoice to every rupee that settled it. A payment only counts as mapped when the pieces add up to the transfer. On one channel's first production run, 88 of 88 payments reconciled; on another, 1,007 of 1,012 (99.5%) matched.

01 · The problemA payment is a puzzle with missing pieces

When a channel pays a brand, it doesn't pay invoice by invoice. It sends one bank transfer that covers several invoices — sometimes only part of one — minus debit notes for shortages or price differences, TDS, and early-payment charges if the brand used financing.

The pieces that explain a payment are scattered: purchase orders and goods-received notes, ERP and portal invoices, debit notes, payment reports at two different levels of detail, and PDF payment advices. So the questions finance teams actually ask — which invoices did this payment cover? why is this invoice still open? where did the rest of the money go? — meant digging through exports and spreadsheets.

02 · The intuitionModel money, not files

Every one of those reports describes the same underlying thing from a different angle: money moving between a brand and a channel. So instead of reconciling file against file, the engine normalises everything into one ledger of facts — invoices and their lines, POs and GRNs, debit and credit notes, and one record per invoice–payment pair — and answers questions in both directions.

A payment only counts as mapped when its pieces add up to the transfer.

03 · The solutionFrom scattered reports to a two-way lookup

  1. Pull every source, per brand. Workflow nodes collect POs and GRN events, ERP and portal invoices, debit notes, payment reports at the UTR and invoice level, channel ledgers, and payment-advice PDFs from an early-payment financier, read with Azure Document Intelligence.
  2. Normalise into one ledger. Invoices match on an upper-cased invoice ID plus channel, line items on PO number plus SKU. Debit notes are de-duplicated and categorised — quantity, price, return-to-vendor, promo, TDS — and negative-quantity ERP rows become credit notes.
  3. Link payments to invoices. Each invoice–payment pair is one record carrying the paid amount, early-payment charge, TDS and the transfer's net total. An invoice split across payments, or a payment covering many invoices, is simply more rows.
  4. Prove it adds up. A UTR is fully adjusted only when its invoice amounts plus charges land within ₹1 of the transfer; otherwise it's fetched again on the next run. Partial and unmapped payments are surfaced, never hidden.
  5. Explain every gap. Each invoice gets a status — Raised, Raised with GRN, Overdue, Closed, Cancelled or Unmapped — and any outstanding amount is broken into payment, book, TDS and unmapped buckets, in rupees.

04 · ImplementationThe decisions that make it trustworthy

One row per invoice–payment pair

The heart of the system is a table with one row per organisation, channel, invoice and UTR. A partial unique index also lets a UTR exist with no invoice attached — cash that has arrived but isn't allocated yet — so money never silently disappears between runs.

-- simplified sketch, not the production schema
create table invoice_payment_records (
  org_id            text,
  channel           text,
  invoice_id        text,                  -- upper-cased
  utr               text,
  paid_amount       numeric(14, 2),
  early_pay_charge  numeric(14, 2),
  tds               numeric(14, 2),
  net_utr_amount    numeric(14, 2),        -- the transfer total
  source            text,
  unique (org_id, channel, invoice_id, utr)
);

-- cash that arrived before we know which invoice it pays
create unique index unallocated_utr on invoice_payment_records (org_id, channel, utr)
  where invoice_id is null;

Tolerances are part of the spec

Money data is messy, so tolerance is a product decision, not a fudge factor:

  • A UTR is fully adjusted when paid amounts plus early-payment charges are within ₹1 of the transfer.
  • A payment imbalance is flagged at 1%.
  • TDS is checked against the expected 0.1% of invoice value and flagged when the gap exceeds the larger of 1% of expected TDS or ₹5.
  • Splitting a debit note across SKU lines must agree to ±₹0.01 after a GST-rate check.
  • When a channel pays several invoices in one transfer without a breakdown, matches within 0.1% are accepted but flagged for a person to confirm.

Safe to run again

Every write is an upsert keyed on the invoice–payment pair, so re-running a sync is always safe. On the validation file, the second run wrote nothing at all.

The bug the numbers caught

GotchaOn one channel, the first pass matched 999 of 1,012 payments. Good — but the misses pointed to a real bug: bank references shared across several payments were being over-allocated, attributing more than 3× a transfer's actual amount to its invoices. Adding up shared references before allocating fixed it and lifted the match to 1,007 of 1,012.

05 · ResultsEvery rupee accounted for

Before

A transfer arrives as a lump sum. Working out which invoices it paid — and why others are still open — means digging through exports and spreadsheets.

After

Look up any UTR or invoice and see every rupee: invoices paid, debit notes, credit notes, TDS and charges, with any gap explained.
  • 88 of 88 UTRs reconciled on one channel's first production run, writing 661 invoice–payment records.
  • 1,007 of 1,012 payments — 99.5% — matched on another channel after fixing the shared-reference bug.
  • 0 mismatches on a 982-row validation file covering 38 payments, including 41 invoices split across more than one payment.
  • On every payment that carried a debit note, invoice amounts matched the transfer to the paisa.

Debit notes themselves are split down to SKU lines by the document-AI pipeline, and the raw reports arrive through the vendor-portal connectors.

06 · TakeawaysWhat reconciliation taught me

  • Reconciliation is a data-modelling problem before it's a matching problem.
  • Make "adds up" a hard rule — and make every exception visible.
  • Tolerances are product decisions. Write them down and test them.
  • Idempotent writes turn "run it again" into a safe default.

Building finance workflows where every rupee has to add up? I'd love to compare notes.