Designing an Efficient Payment Database in Node.js

The bug that costs you money isn't in the payment API call — it's in the database. A duplicate callback that credits an order twice, a payment marked "paid" that never cleared, an update that half-finished: these are schema and transaction problems, not integration problems. Here's how I design a payments database with SQL and Sequelize so it stays correct and fast, using one schema that serves both M-Pesa and Pesapal.

How do you design an efficient payment database in Node.js?

Design it around a dedicated, provider-agnostic payments table separate from your orders, with a unique constraint on each provider's reference for idempotency, an indexed status column for reconciliation, and money stored as DECIMAL. Confirm every payment inside a database transaction with row locking so a payment and its order can never drift out of sync.

The two rules underneath everything: never put payment state inside your orders table, and never trust an incoming notification — verify it, then write the result atomically.

One provider-agnostic payments table

Both M-Pesa and Pesapal follow the same shape — you initiate a payment, the customer pays elsewhere, and the provider notifies you asynchronously. So one table can serve both, with a provider column and a couple of reference columns. Modelling it in Sequelize:

const { DataTypes } = require("sequelize");
 
const Payment = sequelize.define(
  "Payment",
  {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true,
    },
    orderId: { type: DataTypes.UUID, allowNull: false },
    provider: { type: DataTypes.ENUM("mpesa", "pesapal"), allowNull: false },
 
    // Our own key, generated before we call the provider — our idempotency anchor
    merchantReference: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
    // The provider's tracking id (CheckoutRequestID / OrderTrackingId)
    providerReference: { type: DataTypes.STRING, unique: true },
    // The final receipt (MpesaReceiptNumber / Pesapal confirmation_code)
    receiptNumber: { type: DataTypes.STRING, unique: true },
 
    amount: { type: DataTypes.DECIMAL(12, 2), allowNull: false },
    currency: {
      type: DataTypes.STRING(3),
      allowNull: false,
      defaultValue: "KES",
    },
    status: {
      type: DataTypes.ENUM(
        "pending",
        "processing",
        "success",
        "failed",
        "cancelled",
      ),
      allowNull: false,
      defaultValue: "pending",
    },
    phone: { type: DataTypes.STRING },
    rawResponse: { type: DataTypes.JSONB }, // full provider payload, for audit
  },
  {
    tableName: "payments",
    indexes: [
      { fields: ["orderId"] },
      { fields: ["status", "createdAt"] }, // reconciliation queries
      { unique: true, fields: ["merchantReference"] },
      { unique: true, fields: ["providerReference"] },
    ],
  },
);

(JSONB is PostgreSQL; use JSON on MySQL.) A Payment belongsTo an Order, and an order can have several payment attempts — which is exactly why they're separate tables.

Idempotency is a database constraint, not an if

Payment notifications get delivered more than once. M-Pesa retries callbacks; Pesapal fires an IPN that you then have to verify. If your "mark as paid" logic is a JavaScript if (!alreadyPaid), two callbacks arriving together both pass the check before either writes — and you've credited the order twice.

The real guard is the unique constraint on providerReference / receiptNumber. The database physically refuses the duplicate, no matter how the requests race. Your application code is the convenience layer; the constraint is the guarantee.

Confirm payments inside a transaction, with a row lock

When a notification confirms a payment, two rows change — the payment and the order — and they must change together or not at all. Wrap it in a Sequelize transaction and lock the payment row so concurrent callbacks serialize:

const { Op } = require("sequelize");
 
async function confirmPayment(providerRef, { status, receiptNumber, raw }) {
  return sequelize.transaction(async (t) => {
    const payment = await Payment.findOne({
      where: { providerReference: providerRef },
      lock: t.LOCK.UPDATE, // SELECT ... FOR UPDATE — serialize concurrent callbacks
      transaction: t,
    });
 
    if (!payment) return;
    if (payment.status === "success") return; // already handled — idempotent exit
 
    payment.status = status;
    payment.receiptNumber = receiptNumber ?? payment.receiptNumber;
    payment.rawResponse = raw;
    await payment.save({ transaction: t });
 
    if (status === "success") {
      await Order.update(
        { status: "paid" },
        { where: { id: payment.orderId }, transaction: t },
      );
    }
  });
}

If anything throws, the whole transaction rolls back — you never end up with a paid payment and an unpaid order.

Mapping M-Pesa and Pesapal into the same columns

The schema stays identical; only the plumbing differs:

• merchantReference — you generate this (a UUID) before calling either provider. It's M-Pesa's AccountReference and Pesapal's order id.
• providerReference — M-Pesa's CheckoutRequestID, Pesapal's OrderTrackingId.
• receiptNumber — M-Pesa's MpesaReceiptNumber, Pesapal's confirmation_code.

The one behavioural difference worth burning into memory: Pesapal's IPN and callback do not contain the payment status — for security, they only give you the OrderTrackingId, and you must call GetTransactionStatus to learn whether the payment actually completed. M-Pesa's callback carries the result inline, but you should still treat a status query as the source of truth for anything that timed out. In both cases, the notification is a trigger to verify, never the verification itself. For the M-Pesa side specifically — access tokens, the STK Push request, and what the callback payload actually looks like — see my walkthrough of integrating M-Pesa into a Node.js backend.

Design for reconciliation from day one

Customers abandon payments. Networks drop callbacks. So some rows sit in pending forever unless you go check them — which is what the ["status", "createdAt"] index is for. A scheduled job sweeps stale pending payments efficiently:

const stale = await Payment.findAll({
  where: {
    status: "pending",
    createdAt: { [Op.lt]: new Date(Date.now() - 5 * 60 * 1000) },
  },
});
// For each, query the provider's status endpoint, then confirmPayment(...)

Without that index, this query scans your whole payments table on every run; with it, it touches only the rows that matter.

The smaller decisions that still bite

• Store money as DECIMAL (or integer minor units), never FLOAT. Floating point rounding on currency is a real, silent bug.
• Keep the raw payload. rawResponse is your audit trail when a provider and your records disagree.
• Promote what you query, JSON what you don't. Filterable fields get real indexed columns; the raw blob stays in JSON. Don't query inside JSON on the hot path.
• Use migrations, not sync({ alter: true }), in production. Auto-sync guesses at schema changes; migrations are deliberate and reviewable.

Always confirm exact field names and endpoints against the current official M-Pesa Daraja and Pesapal API 3.0 docs before going live — this is the structure, not a substitute for their references.

Building payments into your product?

A clean payments schema is the difference between a system you can trust with money and one you're afraid to touch. I design and build these in Node.js — Sequelize, Postgres, M-Pesa, Pesapal, and the reconciliation logic that keeps them honest. If that's what you need, get in touch, or find me on Fiverr and Upwork.