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.

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.