Reconciling M-Pesa Transactions With Your Database

If you have built an M-Pesa integration and it works in sandbox, congratulations. You are maybe 60% done. The other 40% is making sure your database and M-Pesa agree on what happened, every single time, even when the network drops, your server restarts, or Safaricom's systems hiccup.

Here is what goes wrong in production:

• Missed callbacks. Daraja sends a callback to your server, but your server was restarting, your SSL certificate had expired, or your cloud provider had a momentary network blip. The user paid. M-Pesa deducted the money. Your database has no record of it. The user is angry.
• Duplicate callbacks. Daraja sends the callback, your server processes it, but the response does not reach Daraja in time. Daraja retries. Your server processes it again. The user's account is credited twice. You are now out of pocket.
• Reversed transactions. The user paid, your database marked the order as "paid," but then the user called Safaricom and reversed the transaction. Your database still says "paid." You have delivered a product or service for free.
• Timeout limbo. The STK Push timed out. Your database says "pending." M-Pesa actually processed the payment. The user paid but your system does not know it. Or the reverse: M-Pesa did not process it, but the user thinks they paid because the push appeared on their phone.

Every M-Pesa integration that handles real money needs three layers of defense: real-time callback processing, a polling fallback for missed callbacks, and a scheduled reconciliation job that catches everything else. Skipping any one of these layers means you will eventually lose money, double-charge a customer, or spend hours manually tracing transactions.

The rest of this guide walks through each layer and shows you how to build them.

Your database schema determines how easy or painful reconciliation will be. Get this right upfront and everything else becomes simpler. Get it wrong and you will be running raw SQL queries at midnight trying to figure out why your numbers do not add up.

Essential columns for your transactions table:

• id: Your internal primary key (UUID or auto-increment)
• merchant_request_id: Returned by Daraja when you initiate the STK Push. This is your link between the request and the callback
• checkout_request_id: Also returned on initiation. Some callback payloads reference this instead of merchant_request_id
• mpesa_receipt_number: The M-Pesa transaction code (e.g., "QKJ4ABCDEF"). This is the single most important field for reconciliation. It is what appears on the user's M-Pesa statement and what Safaricom uses internally
• phone_number: The payer's phone number in 254 format
• amount: The transaction amount as a decimal, not an integer. M-Pesa can process fractional amounts
• status: An enum with values like initiated, pending, completed, failed, reversed, reconciled
• result_code: The numeric result code from Daraja's callback
• result_description: The human-readable result description from the callback
• callback_raw: The full raw JSON payload from the callback. Store this as a JSON column or text. You will need it during disputes
• initiated_at: When your server sent the STK Push request
• completed_at: When you received and processed the callback
• reconciled_at: When the daily reconciliation job confirmed this transaction matches the M-Pesa statement

Why status enums beat boolean flags. A common beginner pattern is to have a paid boolean column. This breaks immediately. What does paid = false mean? That the payment failed? That it is still processing? That it was reversed? You cannot tell. An explicit status enum with defined transitions (initiated can become pending, pending can become completed or failed, completed can become reversed) gives you a state machine that catches invalid transitions. If something tries to set a "completed" transaction back to "pending," you know something is wrong.

Add indexes on the columns you query during reconciliation: mpesa_receipt_number, merchant_request_id, status, and initiated_at. Your daily reconciliation job will query transactions by status and date range. Without indexes, these queries get slow as your table grows.

Keep a separate transaction_events or transaction_logs table that records every status change with a timestamp. When a transaction moves from "pending" to "completed," log that event. When it moves from "completed" to "reversed," log that too. This audit trail is invaluable when you need to reconstruct what happened to a specific transaction during a customer dispute.

Daraja's callback mechanism is your first and fastest source of transaction data. When a user completes (or fails) an M-Pesa payment, Daraja sends a POST request to the callback URL you specified when initiating the STK Push. In the happy path, this arrives within seconds.

Making your callback handler production-ready:

First, respond fast. Your callback endpoint should acknowledge the request (return HTTP 200) as quickly as possible. Do not perform heavy processing before responding. Parse the payload, validate the basic structure, enqueue it for processing, and return 200. Process the business logic asynchronously. If your callback handler takes too long, Daraja may time out and retry, creating duplicate processing risk.

Second, implement idempotency. Check whether you have already processed a callback for this MerchantRequestID or CheckoutRequestID before doing anything. If you have, return 200 and skip all processing. The simplest implementation: add a unique constraint on merchant_request_id in your transactions table. If the insert fails due to a duplicate key violation, you know it is a retry.

Third, validate the payload. Daraja callbacks follow a specific JSON structure. If you receive a POST to your callback URL that does not match this structure, reject it. This protects against malicious actors who discover your callback URL and try to forge payment confirmations. In production, also validate the source IP against Safaricom's known IP ranges.

What callbacks do not cover:

• If your server is down when the callback arrives, you miss it. Daraja retries a limited number of times, but if all retries fail, the callback is lost.
• If your SSL certificate is expired or misconfigured, Daraja cannot reach your endpoint. This is one of the most common causes of missed callbacks in production.
• If Daraja itself has an internal issue and fails to send the callback, you never receive it. This is rare but it happens.

Callbacks handle the majority of your transactions. For the rest, you need a fallback.

Polling means actively asking Daraja "what happened with this transaction?" instead of waiting for the callback. You do this using the Transaction Status Query API or, for STK Push specifically, the STK Push Query API.

When to poll: Run a background job that queries your database for transactions stuck in "initiated" or "pending" status for longer than a reasonable threshold. For STK Push, if you have not received a callback within 3 to 5 minutes, something probably went wrong with the callback delivery. Query Daraja to find out the actual status.

STK Push Query API: This endpoint lets you check the status of a specific STK Push request using the CheckoutRequestID from the original request. The response tells you whether the transaction completed, failed, or is still pending. If it completed, the response includes the M-Pesa receipt number, which you can use to update your database.

Transaction Status Query API: A more general endpoint that checks the status of any M-Pesa transaction using the transaction ID. Useful for B2C payouts and other transaction types beyond STK Push.

Implementation pattern:

1. Run a cron job every 2 to 5 minutes
2. Query your database: SELECT * FROM transactions WHERE status IN ('initiated', 'pending') AND initiated_at < NOW() - INTERVAL '5 minutes'
3. For each result, call the STK Push Query API with the checkout_request_id
4. If Daraja says the transaction completed, update your database as if you had received the callback
5. If Daraja says it failed or the request is not found, mark it as failed in your database
6. If Daraja says it is still pending (which is unusual after 5 minutes), flag it for manual review

Rate limit awareness. The query APIs have their own rate limits. If you have 500 pending transactions, do not fire 500 API calls simultaneously. Process them in batches with a short delay between each call. A queue with a configurable concurrency limit (say, 5 concurrent requests) works well.

Do not over-poll. Polling every 10 seconds for a transaction that was just initiated is wasteful and risks hitting rate limits. Give the callback a reasonable window (3 to 5 minutes) before falling back to polling. Most callbacks arrive within seconds. Polling is for the exceptions, not the normal flow.

Between callbacks and polling, you should have visibility into the vast majority of your transactions. The daily reconciliation job handles the remaining edge cases.

Even with callbacks and polling, mismatches accumulate. A transaction might complete on M-Pesa's side but your database shows "failed" because of a parsing error in your callback handler. Or a reversal might happen hours after the original payment. Daily reconciliation catches these discrepancies before they become bigger problems.

Getting M-Pesa statements. Safaricom provides transaction statements through the Daraja portal and, for some account types, through an API. Download your daily statement (usually available in CSV or Excel format). This statement lists every transaction that hit your shortcode in the past 24 hours: incoming payments, outgoing payouts, reversals, and fees.

The reconciliation process:

1. Download or fetch yesterday's M-Pesa statement
2. Parse it into a list of transactions with receipt numbers, amounts, timestamps, and transaction types
3. For each transaction in the M-Pesa statement, look up the matching record in your database by mpesa_receipt_number
4. Compare amounts. If M-Pesa says KES 1,000 and your database says KES 1,000, the transaction is reconciled. Mark it accordingly
5. Flag mismatches: transactions in M-Pesa that are not in your database (missed callbacks), transactions in your database that are not in M-Pesa (phantom records), and amount discrepancies
6. For flagged items, create a reconciliation exception record that your finance or support team reviews

Handling reversals. When a user reverses an M-Pesa transaction through Safaricom, the reversal appears on your statement as a separate line item. Your reconciliation job should detect reversals by matching the reversed transaction's receipt number. When found, update the original transaction status to "reversed" and trigger whatever business logic is needed (cancel the order, revoke access, etc.). If you do not actively check for reversals, you will discover them only when your monthly accounts do not balance.

Automating the job. Run your reconciliation as a scheduled task (cron job, scheduled cloud function, or a task queue). Parse the statement, compare records, generate a report, and email the summary to your finance team. The report should list: total transactions reconciled, number of mismatches found, total amount discrepancy, and individual exception details. Keep the job idempotent so running it twice for the same day does not create duplicate exception records.

When to escalate. Small mismatches (a few transactions per day) are normal and usually caused by timing differences between your server clock and M-Pesa's. Systemic mismatches (dozens of unmatched transactions, or a growing trend of discrepancies) indicate a problem in your callback handler, polling logic, or network configuration. Investigate the root cause rather than just resolving individual exceptions.

These are not hypothetical scenarios. They happen in production. Build handling for each one before launch, not after your first customer complaint.

Partial payments. The user's M-Pesa balance is lower than the order amount. Some businesses allow partial payments and expect the user to "top up" with a second transaction. If you support this, your database needs to track partial payments against an order, calculate the remaining balance, and only mark the order as fully paid when the sum of all payments meets or exceeds the order total. Your reconciliation job needs to match multiple M-Pesa transactions to a single order. This adds complexity, so decide early whether partial payments are worth supporting for your use case.

Reversed transactions. As mentioned above, users can call Safaricom and reverse transactions. The reversal can happen hours or even days after the original payment. Your system should check for reversals during daily reconciliation and have a process for handling the downstream effects (revoking delivered services, flagging the user's account, notifying your support team). Some businesses also implement a hold period before delivering high-value goods or services, giving the reversal window time to pass.

Amount mismatches. The user was supposed to pay KES 500, but the M-Pesa callback shows KES 450. This can happen if the user manually enters an amount (for paybill payments as opposed to STK Push, where the amount is fixed). Your callback handler should compare the received amount against the expected amount. Accept the payment but flag it as underpaid. Whether you fulfill the order, refund the partial payment, or ask the user to send the difference depends on your business rules.

Duplicate phone numbers. Two different users share a phone number (common in families) or a user changes their phone number. Your system should tie transactions to user accounts, not just phone numbers. Use the internal order ID or reference number as the primary link, with the phone number as a secondary identifier.

Timezone issues. M-Pesa timestamps use East Africa Time (EAT, UTC+3). If your server runs in UTC (which is common for cloud servers), a transaction at 11:30 PM EAT on Monday appears as 8:30 PM UTC on Monday. During reconciliation, a transaction might appear on different "days" depending on which timezone you use. Pick one timezone for all your date-based queries and stick with it. Document the choice so future developers do not introduce bugs by assuming a different timezone.

M-Pesa maintenance windows. Safaricom schedules maintenance on M-Pesa systems, usually late at night. During maintenance, callbacks may be delayed or lost. Your polling fallback should be especially vigilant during and after maintenance windows. Check Safaricom's developer communication channels for maintenance announcements and adjust your monitoring thresholds accordingly.

You do not need to build everything from scratch. Here are practical tools and approaches that reduce the reconciliation workload.

Database tools. PostgreSQL is a solid choice for transaction storage. Its JSON column type handles raw callback payloads cleanly. Its advisory locks prevent concurrent callback processing races. And its robust indexing handles reconciliation queries at scale. If you are on MySQL, the JSON column type works similarly. SQLite is fine for prototyping but avoid it in production for financial data because it does not handle concurrent writes well.

Queue systems. For the polling fallback and reconciliation jobs, a simple task queue prevents you from overwhelming Daraja's APIs. BullMQ (Node.js), Celery (Python), or even a basic database-backed queue works. The key feature you need is retry with backoff: if a query fails, retry it after a delay rather than immediately.

Monitoring. Set up alerts for: callback endpoint downtime (uptime monitoring on your callback URL), transaction failure rate spikes, reconciliation exceptions above your threshold, and pending transactions older than your timeout window. Tools like UptimeRobot (free tier available) can monitor your callback URL. Application-level monitoring through Sentry, LogRocket, or even simple email alerts works for the rest.

Testing reconciliation. Create a test script that generates mock M-Pesa statement data and runs your reconciliation job against it. Include test cases for all the edge cases: missing transactions, reversed transactions, amount mismatches, and duplicate receipts. Run this test suite before deploying any changes to your reconciliation logic.

If you want hands-on practice building a complete M-Pesa integration with proper reconciliation, callback handling, and production deployment, our M-Pesa Integration for Developers course (KES 9,999) covers exactly this. You build a working payment system with a real Daraja sandbox, not just the happy path but the failure modes that matter in production.