Reconciliation failure modes: the exact matches are the easy part

Every system that matches money to what is owed starts with the same rule. A payment arrives carrying a reference, the reference names exactly one account, the amount fits, and the money is applied. That rule is deterministic, it is easy to test, and on a good day it handles most of what comes in.

It is also the part that needs the least engineering. The reason reconciliation software exists is everything that rule cannot place, and the way a system treats those payments decides whether anybody can trust its numbers. This post is about that remainder: what it looks like, why guessing at it is the expensive mistake, and the three design choices that make it manageable. The Daraja-specific mechanics of getting payments in are in our M-Pesa Daraja integration guide; this is what happens after they arrive.

What exact matching cannot place

These are not edge cases to be handled one day. Each is a normal event in any system that takes money from people, and each needs a defined behaviour before the first payment arrives.

No reference. The payer left the account field empty, or paid a Till that has none. There is nothing to match on.

A wrong reference that resembles a right one. One character off, digits transposed, an old account number. This is the dangerous one, because a matcher that is a little too helpful will find a real account and credit it.

A payer who is not the account holder. A relative, an employer, a company paying for several people. The name and the number on the payment belong to somebody else, and with M-Pesa C2B the payer’s number arrives masked anyway.

Part of what is owed. The payment is real and the reference is right, but it covers half the bill.

More than what is owed. Two months at once, a rounded-up amount, a payment for something not yet invoiced.

One payment for several obligations, or several payments for one. Rent plus water in a single transfer. A school fee paid in three instalments from two phones.

The same payment, reported twice. A repeated notification, a retried job, or the same money seen once through a callback and again on the statement.

A payment nobody told you about. It is on the provider’s statement and nowhere in your system, because a notification was lost.

A correction. Somebody allocated a payment to the wrong account last week, and it has to move without making last week’s reports lie.

The suspense queue is a design decision, not a failure

When exact matching fails, a system has two choices. It can guess, or it can hold the payment somewhere named, visible and owned until a person decides.

Guessing looks efficient, and it is the expensive option. A fuzzy match on a reference, a nearest-amount match, a match on the payer’s name: each guess, when it is wrong, credits one customer with another customer’s money. Worse, from inside the system a wrong allocation looks exactly like a right one. Nobody sees it until the person whose payment went astray asks why they are in arrears, by which time two accounts are wrong and one of them looks fine.

So the second choice is the correct one, and it deserves to be built properly rather than as an error state. A suspense queue that works has:

• A name and an owner. Somebody’s job includes emptying it, and everybody knows whose.
• Age on every item. A payment in suspense for an hour is routine. One in suspense for a month is a customer who believes they paid and is about to be chased for arrears.
• Actions that are themselves recorded. Allocate to an account, split across several, hold as credit, refund. Each resolution writes who did it, when, and why, in the same ledger as everything else.
• A size you watch. The count and the oldest age are the health check for the whole payment path. A queue that grows week on week means something upstream changed: a new payment channel, a reference format customers misunderstand, a matching rule that broke.

The queue’s job is to make the cost of an unmatched payment visible and small, rather than invisible and large.

Append-only allocation, so every number can be explained

The second decision is about how allocations are stored. The tempting design keeps a balance column on each invoice and updates it as payments arrive. It is simple, and it destroys the history the moment anything is corrected, because the corrected figure overwrites the one that was there.

The design that holds up stores each allocation as a row that is never updated or deleted. A payment is placed on a charge by inserting a row. A mistake is undone by inserting a row with the opposite amount that points at the one it reverses. The balance is never stored; it is derived, every time it is read, from what was charged minus what was allocated.

  

    sql
    
      Copy
    
  
  
CREATE TABLE charges (
    id            INTEGER PRIMARY KEY,
    account_id    INTEGER NOT NULL,
    period_start  DATE    NOT NULL,
    amount_cents  BIGINT  NOT NULL
);

CREATE TABLE payments (
    id            INTEGER PRIMARY KEY,
    receipt       VARCHAR(32) NOT NULL UNIQUE,
    amount_cents  BIGINT  NOT NULL,
    received_at   TIMESTAMP NOT NULL
);

-- Append-only. Rows are inserted and never updated or deleted.
CREATE TABLE allocations (
    id            INTEGER PRIMARY KEY,
    payment_id    INTEGER NOT NULL REFERENCES payments (id),
    charge_id     INTEGER REFERENCES charges (id),   -- NULL: held as credit
    amount_cents  BIGINT  NOT NULL,                  -- negative to reverse
    reverses_id   INTEGER REFERENCES allocations (id),
    reason        VARCHAR(255) NOT NULL,
    created_by    INTEGER NOT NULL,
    created_at    TIMESTAMP NOT NULL
);

A worked example: two monthly charges of KES 25,000, one payment of KES 30,000 allocated oldest first, and a later correction when a person finds the September part was placed on the wrong charge.

  

    sql
    
      Copy
    
  
  
INSERT INTO charges VALUES (1, 7, '2026-08-01', 2500000), (2, 7, '2026-09-01', 2500000);
INSERT INTO payments VALUES (1, 'TJ81AB2C3D', 3000000, '2026-09-04 10:12:00');
INSERT INTO allocations VALUES
  (1, 1, 1, 2500000, NULL, 'arrears first', 1, '2026-09-04 10:12:05'),
  (2, 1, 2,  500000, NULL, 'arrears first', 1, '2026-09-04 10:12:05'),
  -- a person finds the September part was placed on the wrong charge:
  (3, 1, 2, -500000, 2,    'wrong lease, see ticket 41', 2, '2026-09-10 09:00:00');

Three questions, each answered by a query rather than by a stored figure:

  

    sql
    
      Copy
    
  
  
-- What is owed on each charge, derived every time it is read.
SELECT c.id, c.amount_cents - COALESCE(SUM(a.amount_cents), 0) AS outstanding_cents
FROM charges c
LEFT JOIN allocations a ON a.charge_id = c.id
GROUP BY c.id, c.amount_cents;

-- The same question as it would have been answered on 5 September.
SELECT c.id, c.amount_cents - COALESCE(SUM(a.amount_cents), 0) AS outstanding_cents
FROM charges c
LEFT JOIN allocations a ON a.charge_id = c.id AND a.created_at < '2026-09-06'
GROUP BY c.id, c.amount_cents;

-- Money received and not yet placed on any charge.
SELECT p.receipt, p.amount_cents - COALESCE(SUM(CASE WHEN a.charge_id IS NOT NULL THEN a.amount_cents END), 0) AS unplaced_cents
FROM payments p
LEFT JOIN allocations a ON a.payment_id = p.id
GROUP BY p.id, p.receipt, p.amount_cents;

The first says charge 2 is fully outstanding again. The second says that on 5 September it showed KES 20,000 outstanding, which is what the statement sent that day said, so the old statement is still explainable. The third shows KES 5,000 received and not yet placed, which is the suspense queue, computed rather than maintained.

That second query is the reason for the whole design. When a customer disputes a statement from last month, the system can reproduce exactly what that statement said and show every row that has changed the answer since, with who made each change and why.

The rules we hold rentiQ to

rentiQ, the property management platform we build and run, allocates this way, and a few of its rules generalise to any system that takes money against obligations:

• Arrears first, oldest period first. A payment without instructions settles the oldest unpaid charge before the newest. Allocation runs inside a transaction with the affected rows locked, so two payments arriving together cannot both settle the same charge.
• A payment may target a charge, and the remainder still flows oldest first. A customer paying a specific invoice gets that invoice settled.
• Overpayment becomes credit, not a discount. It sits in a ledger of its own and is applied when the next charge is issued, with an entry recording the application. Folding it into the next invoice as a reduced amount would make it impossible to tell later whether the customer overpaid or was undercharged.
• Corrections are offsetting records. A reversed payment keeps its row and gains a negative twin, its allocations are reversed the same way, and any credit it created is unwound.
• Receipt numbers have no gaps. They are drawn from a counter locked inside the same transaction as the payment, so a rolled-back payment releases its number rather than leaving a hole an auditor has to ask about.
• Never sum invoice balances. An invoice that carries arrears brought forward restates debt that is already on an earlier invoice. Add up the balances and the debt is counted twice. True debt is what was charged minus what was paid, which is the query above.

That last rule is the one that most often goes wrong quietly, because each invoice is individually correct and only the total is wrong.

rentiQ records payments that staff enter today, so the references it sees were typed by people its users can ask. The suspense queue described above matters most once payments arrive on their own, through a Paybill integration. rentiQ’s payment recording sits behind one interface for that reason, so an automatic recorder can replace the manual one without its callers changing.

What a correct audit trail answers

An audit trail is not a log file. It is the ability to answer specific questions from records, without anyone remembering anything. A payment system’s trail should answer, on demand:

• Which charges did this payment settle, and who or what decided that, and when?
• What did this account’s balance look like on any given date, and what has changed since?
• Which money has been received and not placed, and for how long?
• Who moved this payment from one account to another, and what reason did they record?
• Does every line on the provider’s statement correspond to a payment here, and every payment here to a line on the statement?

The last question is the one the rest depend on. It is answered by a daily comparison with the provider’s statement, which has three possible findings: money on the statement you never recorded, amounts that disagree, and payments you recorded that the statement never shows. The first is a lost notification and is recovered. The second and third should never happen, and when they do, a person looks before anything changes.

If a system can answer all five without a developer running ad hoc queries, its numbers can be trusted by the people who have to sign them. If it cannot, it is a system that is right until the first dispute.

Our reconciliation checklist turns these into questions you can ask of a system you already run, and the rentiQ case study shows where these rules sit inside a whole product.