Payment Reconciliation Basics for M-Pesa Integrations

Callbacks fail. Servers go down. Networks drop requests. In a perfect world, every STK Push results in a clean callback that updates your database. In the real world, things break, and when the thing that breaks handles money, people notice fast.

Common scenarios where payments get "lost":

• Missed callback: the customer paid, but your server was down and never received the callback. The customer sees a deduction on their M-Pesa statement, but your system shows "pending."
• Duplicate callback: Safaricom retried a callback and your system processed it twice, crediting the customer double.
• Amount mismatch: the callback amount does not match the order amount (rare with STK Push, more common with C2B where customers type amounts manually).
• Orphaned payment: a callback arrived for a CheckoutRequestID that does not exist in your database (maybe the initial STK Push request failed to save).
• Timeout ambiguity: the STK Push timed out. Did the customer pay or not? The callback might arrive late, or not at all.

Without reconciliation, each of these becomes a customer support ticket and a potential dispute. With reconciliation, your system catches them automatically.

Daraja provides a Transaction Status endpoint that lets you query the status of any transaction by its receipt number or checkout request ID. This is your primary tool for reconciliation.

Node.js:

async function queryTransactionStatus(
  transactionId: string
) {
  const token = await getAccessToken();

  const response = await axios.post(
    'https://sandbox.safaricom.co.ke/mpesa/transactionstatus/v1/query',
    {
      Initiator: process.env.INITIATOR_NAME,
      SecurityCredential: process.env.SECURITY_CREDENTIAL,
      CommandID: 'TransactionStatusQuery',
      TransactionID: transactionId,
      OriginalConversationID: transactionId,
      PartyA: process.env.DARAJA_SHORTCODE,
      IdentifierType: '4',
      ResultURL: process.env.STATUS_RESULT_URL,
      QueueTimeOutURL: process.env.STATUS_TIMEOUT_URL,
      Remarks: 'Status check',
      Occasion: 'Reconciliation',
    },
    {
      headers: { Authorization: `Bearer ${token}` },
    }
  );

  return response.data;
}

For STK Push transactions specifically, use the STK Push Query endpoint, which is simpler:

async function queryStkPushStatus(
  checkoutRequestId: string
) {
  const token = await getAccessToken();
  const { password, timestamp } = generatePassword(
    process.env.DARAJA_SHORTCODE!,
    process.env.DARAJA_PASSKEY!
  );

  const response = await axios.post(
    'https://sandbox.safaricom.co.ke/mpesa/stkpushquery/v1/query',
    {
      BusinessShortCode: process.env.DARAJA_SHORTCODE,
      Password: password,
      Timestamp: timestamp,
      CheckoutRequestID: checkoutRequestId,
    },
    {
      headers: { Authorization: `Bearer ${token}` },
    }
  );

  return response.data;
}

The STK Push Query returns a result code and, if the payment succeeded, the M-Pesa receipt number. Use this endpoint as your first line of defense when a callback does not arrive.

A production reconciliation system has three components: real-time matching, a periodic sweep, and alerts.

1. Real-time matching (in the callback handler)

async function processCallback(callback: any) {
  const checkoutId = callback.CheckoutRequestID;
  const resultCode = callback.ResultCode;

  // Find the order
  const order = await db.orders.findUnique({
    where: { checkoutRequestId: checkoutId },
  });

  if (!order) {
    // Orphaned callback: log and alert
    console.error(
      `Callback for unknown checkout: ${checkoutId}`
    );
    await alertOps('orphaned_callback', { checkoutId });
    return;
  }

  if (order.status === 'paid') {
    // Duplicate callback: skip
    console.log(
      `Duplicate callback for ${checkoutId}, ignoring`
    );
    return;
  }

  if (resultCode === 0) {
    const items = callback.CallbackMetadata.Item;
    const paidAmount = items.find(
      (i: any) => i.Name === 'Amount'
    ).Value;
    const receipt = items.find(
      (i: any) => i.Name === 'MpesaReceiptNumber'
    ).Value;

    // Amount verification
    if (paidAmount !== order.amountKes) {
      console.error(
        `Amount mismatch for ${checkoutId}: ` +
        `expected ${order.amountKes}, got ${paidAmount}`
      );
      await db.orders.update({
        where: { id: order.id },
        data: {
          status: 'amount_mismatch',
          mpesaReceipt: receipt,
          callbackAmount: paidAmount,
        },
      });
      await alertOps('amount_mismatch', {
        orderId: order.id,
        expected: order.amountKes,
        received: paidAmount,
      });
      return;
    }

    // All good: mark as paid
    await db.orders.update({
      where: { id: order.id },
      data: {
        status: 'paid',
        mpesaReceipt: receipt,
        paidAt: new Date(),
      },
    });
  } else {
    await db.orders.update({
      where: { id: order.id },
      data: { status: 'failed', failureCode: resultCode },
    });
  }
}

2. Periodic sweep (runs every 5 to 10 minutes)

async function reconciliationSweep() {
  // Find orders stuck in "pending" for more than 2 minutes
  const cutoff = new Date(Date.now() - 2 * 60 * 1000);

  const stuckOrders = await db.orders.findMany({
    where: {
      status: 'pending',
      createdAt: { lt: cutoff },
    },
  });

  for (const order of stuckOrders) {
    try {
      const result = await queryStkPushStatus(
        order.checkoutRequestId
      );

      if (result.ResultCode === 0) {
        // Payment actually succeeded, callback was missed
        await db.orders.update({
          where: { id: order.id },
          data: {
            status: 'paid',
            reconciledVia: 'sweep',
            paidAt: new Date(),
          },
        });
        console.log(
          `Reconciled order ${order.id} via sweep`
        );
      } else if (result.ResultCode !== undefined) {
        // Definitively failed or cancelled
        await db.orders.update({
          where: { id: order.id },
          data: {
            status: 'failed',
            failureCode: result.ResultCode,
            reconciledVia: 'sweep',
          },
        });
      }
      // If ResultCode is undefined, status still unknown.
      // Leave as pending for the next sweep.
    } catch (error) {
      console.error(
        `Sweep query failed for order ${order.id}:`,
        error
      );
    }
  }
}

3. Alerts

Set up automated alerts for situations that need human attention: orphaned callbacks, amount mismatches, and orders stuck in "pending" for more than 30 minutes. A Slack webhook, email, or SMS alert works. The goal is that no payment anomaly goes unnoticed for more than an hour.

Your payment records should capture enough information for auditing and dispute resolution. At minimum, store:

-- Payment record schema
CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID REFERENCES orders(id),
  checkout_request_id TEXT UNIQUE NOT NULL,
  merchant_request_id TEXT,
  phone_number TEXT NOT NULL,
  amount_kes INTEGER NOT NULL,
  callback_amount INTEGER,
  mpesa_receipt TEXT,
  result_code INTEGER,
  result_desc TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  reconciled_via TEXT,  -- 'callback', 'sweep', 'manual'
  raw_callback JSONB,  -- store the full callback payload
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Key design decisions:

• Store the raw callback: keep the full JSON payload from Safaricom in a JSONB column. When a dispute arises months later, this is your evidence.
• Track reconciliation source: the reconciled_via column tells you whether the payment was confirmed by callback, by your reconciliation sweep, or by manual investigation.
• Separate amounts: store both your expected amount (amount_kes) and the actual amount from the callback (callback_amount). This makes mismatches visible in queries.
• Unique constraint on checkout_request_id: prevents duplicate processing at the database level, even if your application code has a bug.

Beyond automated sweeps, production systems should perform a daily reconciliation against M-Pesa statements.

The process:

1. Download the M-Pesa organization statement from the M-Pesa portal or via the Account Balance API
2. Export your payment records for the same period from your database
3. Match records by M-Pesa receipt number
4. Flag any records that exist in the M-Pesa statement but not in your database (missed callbacks) or vice versa (phantom records in your system)

// Simplified daily reconciliation logic
async function dailyReconciliation(
  mpesaRecords: MpesaRecord[],
  dbRecords: DbPayment[]
) {
  const dbByReceipt = new Map(
    dbRecords.map(r => [r.mpesaReceipt, r])
  );
  const mpesaByReceipt = new Map(
    mpesaRecords.map(r => [r.receiptNumber, r])
  );

  const issues: string[] = [];

  // Payments in M-Pesa but not in our DB
  for (const [receipt, mpesa] of mpesaByReceipt) {
    if (!dbByReceipt.has(receipt)) {
      issues.push(
        `Missing from DB: ${receipt}, ` +
        `KES ${mpesa.amount}, ${mpesa.phone}`
      );
    }
  }

  // Payments in our DB but not in M-Pesa statement
  for (const [receipt, db] of dbByReceipt) {
    if (receipt && !mpesaByReceipt.has(receipt)) {
      issues.push(
        `Missing from M-Pesa: ${receipt}, ` +
        `KES ${db.amountKes}, order ${db.orderId}`
      );
    }
  }

  if (issues.length > 0) {
    console.log('Reconciliation issues found:');
    issues.forEach(i => console.log(` - ${i}`));
    await alertOps('daily_reconciliation', { issues });
  } else {
    console.log('Daily reconciliation clean.');
  }
}

Automate this as a scheduled job that runs every morning at 6 AM. Send the report to your finance team and engineering lead. Catching discrepancies early prevents them from becoming customer complaints.