The phone can say paid while the invoice still says pending, and the statement can contain a payment nobody in the office can find. Reconciliation is the daily job of making those two lists the same list.
One reference, one amount, one invoice
Every collection you initiate should carry a reference you generated and stored on the invoice before the customer is asked to pay. The statement line should be matched on that reference first, and on the amount second. A match on phone number alone will attach a rent payment to the wrong invoice the week two customers share a family line.
Store the statement line even when you cannot match it. An unmatched credit is a queue for a person, not a row to delete. The person either links it to an invoice, marks it as not ours, or leaves it for the next day. The choice is recorded.
The callback is not the statement
A callback can fail, arrive twice, or never arrive. The statement is the provider’s list of what moved. Import it as lines with the provider’s transaction id, which is unique. Importing the same file twice must not create a second match or a second receipt.
A line that matches an invoice already marked paid is a confirmation, not a second payment. A line whose amount differs from the invoice is not a match. Put it in the exception queue with both amounts visible. Do not silently mark the invoice paid for a smaller figure.
Close the day on the exceptions
The useful report is short: invoices still pending that have no statement line, statement lines with no invoice, and amount mismatches. A dashboard of totals will hide the one payment that matters. The clerk should be able to finish that list, not admire a chart.
Write the day as closed when the exception list is empty or each exception has an owner. Closing the day does not move money. It says a person looked. The next morning starts from that mark, not from a fresh pile of doubt.
Match without doubling
The provider transaction id is unique. The second import of the same CSV hits that constraint and stops. The match updates the invoice only when the amount agrees and the invoice is not already paid.
Python · payments/reconcile.py
def import_line(tx_id: str, reference: str, amount):
line, created = StatementLine.objects.get_or_create(
tx_id=tx_id,
defaults={"reference": reference, "amount": amount},
)
if not created:
return line
invoice = Invoice.objects.filter(reference=reference).first()
if invoice and invoice.amount == amount and invoice.status != "paid":
invoice.status = "paid"
invoice.save(update_fields=["status"])
line.invoice = invoice
line.save(update_fields=["invoice"])
return lineThe list a clerk can finish
The useful screen is short. Invoices still pending with no statement line. Statement lines with no invoice. Lines where the amount disagrees. A chart of totals will hide the one payment a parent or a member will ask about at the counter. Build the three lists and a way to link or dismiss a line with a reason.
Importing the file twice is a test, not an edge. The provider transaction id is unique. The second import does nothing. If it creates a second match or a second receipt, the import is not safe to retry, and someone will retry it. Keep the raw file as well, so a bad parse can be reread without asking the provider for another download.
Phone number is a hint, not a key. Families share lines. A match on the number alone will pay the wrong invoice and look tidy until the person with the slip arrives. The reference you showed them before they paid is the key. If they paid with no reference, the line waits for a person. It does not guess.
Close the day only when each exception has an owner or the lists are empty. Closing does not move money. It records that someone looked. The next morning starts from that mark instead of a fresh pile of doubt.
On the next statement import
- Put your reference on the invoice before you ask for payment.
- Import statement lines by the provider transaction id.
- Leave unmatched lines in a queue a person can finish.