Modeling payments in your database: a step-by-step guide

Most payment integrations fail not because Stripe is hard to use, but because the data model underneath doesn't support it. You'll wire up webhooks, process payments, and six months later discover you can't reconcile a dispute or trace why a customer was charged twice. Modeling payments in your database: a step-by-step guide starts with the premise that your schema is the foundation—not an afterthought—and we'll walk through the four tables that make payments traceable, auditable, and operationally sound.

Why database design matters for payments

Payment processing involves multiple external systems, asynchronous workflows, and failure modes that don't exist elsewhere in your application. A user might retry a payment three times; Stripe's webhook might arrive out of order; a dispute can reverse a charge weeks later. Without a clear, normalized data model, you'll spend more time debugging "what actually happened" than building features.

The four-table pattern we'll cover—Orders, Payments, Events, and Ledger—creates a single source of truth. Each serves a distinct purpose: Orders capture customer intent, Payments track provider interactions, Events act as an immutable audit log, and Ledger records the financial impact. Together, they let you answer any payment question with a simple query.

Orders — What the customer bought

An order represents the customer's intent to pay for something. It's independent of Stripe and contains everything needed to create a payment:

An order is not the payment itself. You might create an order, show it to the user, and they abandon checkout. Later, they retry with the same order. In another scenario, you create multiple payment attempts against one order if the first card fails. This separation is crucial: the order is stable, payments are attempts.

Example: A SaaS customer orders a monthly subscription (500 cents = $5). That order sits in the database. We'll attempt to charge it once per month. If the first attempt fails, we create a new payment row but link it to the same order.

Payments — One row per attempt with provider IDs and status

The Payments table tracks every attempt to charge a payment method. This is where you store Stripe Payment Intent IDs, charge IDs, and status transitions:

The key insight: every interaction with Stripe's payment system gets one row. No updates, no overwrites. If a payment fails and you retry, create a new row. This immutability makes debugging trivial—you can see the full history without guessing which version of the record was accurate.

// Example: creating a payment in your app
async function createPayment(orderId: string, paymentMethodId: string) {
  const order = await db.orders.findById(orderId);
  
  const payment = await db.payments.create({
    order_id: orderId,
    amount_cents: order.amount_cents,
    currency: order.currency,
    payment_method_id: paymentMethodId,
    status: 'created',
    created_at: new Date(),
  });

  // Initiate with Stripe
  const intent = await stripe.paymentIntents.create({
    amount: order.amount_cents,
    currency: order.currency,
    payment_method: paymentMethodId,
    confirm: true,
  });

  // Update payment record with Stripe IDs
  await db.payments.update(payment.id, {
    stripe_payment_intent_id: intent.id,
    status: intent.status, // 'succeeded', 'processing', etc.
    stripe_charge_id: intent.charges.data[0]?.id || null,
  });

  return payment;
}

Notice we don't update the payment record to succeeded immediately. We wait for the webhook. This ensures our database state matches Stripe's authoritative source.

Events — Raw webhook events for audit and replay

Stripe sends webhooks asynchronously. Network failures, server crashes, and bugs mean you'll miss some. To be safe, you need an immutable log of every event Stripe sent and what your system did in response:

Store the entire webhook payload as-is. You'll thank yourself later when debugging. When an event fails to process, mark it unprocessed and add a log message. Your monitoring system can alert you, and you can manually retry or investigate.

// Example: webhook handler
app.post('/webhooks/stripe', async (req, res) => {
  const sig = req.headers['stripe-signature'];
  
  let event;
  try {
    event = stripe.webhooks.constructEvent(
      req.rawBody,
      sig,
      process.env.STRIPE_WEBHOOK_SECRET
    );
  } catch (err) {
    return res.status(400).send('Invalid signature');
  }

  // Store the raw event immediately
  const dbEvent = await db.stripeEvents.create({
    stripe_event_id: event.id,
    event_type: event.type,
    related_stripe_id: event.data.object.id,
    raw_payload: event,
    processed: false,
    created_at: new Date(event.created * 1000),
    received_at: new Date(),
  });

  // Process asynchronously
  processWebhookEvent(dbEvent.id).catch(err => {
    console.error(`Failed to process event ${dbEvent.id}:`, err);
  });

  // Acknowledge receipt immediately (idempotent)
  res.json({ received: true });
});

async function processWebhookEvent(eventId: string) {
  const event = await db.stripeEvents.findById(eventId);
  const payload = event.raw_payload;

  try {
    if (payload.type === 'payment_intent.succeeded') {
      const piId = payload.data.object.id;
      await handlePaymentSucceeded(piId);
    } else if (payload.type === 'charge.refunded') {
      const chargeId = payload.data.object.id;
      await handleChargeRefunded(chargeId);
    }
    // ... other event types

    await db.stripeEvents.update(eventId, {
      processed: true,
      processed_at: new Date(),
    });
  } catch (err) {
    await db.stripeEvents.update(eventId, {
      processing_error: err.message,
    });
    throw err; // Re-raise so monitoring catches it
  }
}

This pattern decouples ingestion from processing. You acknowledge the webhook immediately, so Stripe doesn't retry. But if your handler crashes, you have the raw event logged and can replay it.

Ledger — Double-entry records for money movements

The Ledger table is where you record the financial truth. Every movement of money—charges, refunds, payouts, fees—gets two entries: a debit and a credit. This is double-entry bookkeeping, borrowed from accounting:

A successful charge creates two ledger rows: a debit to the customer's account and a credit to your company's revenue. A refund reverses them. This way, you can always sum ledger entries to compute balances, and the math always checks out.

// Example: recording a successful charge
async function recordCharge(paymentId: string, userId: string, amountCents: number) {
  // Debit: money goes out of customer's account
  await db.ledger.create({
    account_id: userId,
    amount_cents: amountCents,
    currency: 'USD',
    type: 'charge',
    reference_id: paymentId,
    direction: 'debit',
    description: `Charge for order`,
    created_at: new Date(),
  });

  // Credit: money comes into company account
  await db.ledger.create({
    account_id: COMPANY_ACCOUNT_ID,
    amount_cents: amountCents,
    currency: 'USD',
    type: 'charge',
    reference_id: paymentId,
    direction: 'credit',
    description: `Revenue from user ${userId}`,
    created_at: new Date(),
  });
}

// Example: computing a customer's balance
async function getCustomerBalance(userId: string) {
  const result = await db.query(`
    SELECT
      SUM(CASE WHEN direction = 'debit' THEN amount_cents ELSE -amount_cents END) as balance_cents
    FROM ledger
    WHERE account_id = $1
  `, [userId]);
  
  return result.rows[0].balance_cents; // Negative = customer owes us
}

The ledger is immutable. Never update or delete entries. If you make a mistake, insert a reversal entry. This keeps the audit trail intact.

Connecting the tables

Here's how a real payment flows through your system:

1. Customer places order → Order row created (status: pending)
2. You call Stripe to create a Payment Intent → Payment row created (status: created)
3. Stripe processes the charge → Webhook fires
4. You store the webhook → Event row created (processed: false)
5. You process the event → Payment row updated (status: succeeded, stripe_charge_id set)
6. You record the financial impact → Two Ledger rows created (debit and credit)
7. Order status updated to confirmed

If the charge fails at step 3, you create a new Payment row and retry. The original row remains unchanged, creating a permanent record of the failed attempt.

Common mistakes

Storing money as floats: Never. Use integers (cents or the smallest unit in your currency). Floating-point arithmetic causes rounding errors that compound.

Updating payment rows: Treat them as immutable. Create new rows on retry, don't overwrite. This preserves the full history and makes debugging straightforward.

Skipping the Events table: It feels redundant until a webhook fails and you can't figure out what happened. Store the raw payload and you'll recover quickly.

Not using double-entry bookkeeping in the Ledger: It seems overkill until you need to reconcile. Every movement of money must have a matching debit and credit. This is non-negotiable.

Ignoring idempotency: Your webhook handler must be idempotent. If the same event arrives twice, processing it twice should produce the same state. Use stripe_event_id as a unique key to prevent duplicate ledger entries.

Key takeaways

• Separate Orders (intent), Payments (attempts), Events (webhooks), and Ledger (financial truth) into distinct tables. Each serves a purpose.
• Store money as integers in cents. Never use floats.
• Treat Payments and Events as immutable. Create new rows, don't update. This preserves history and makes audits trivial.
• Save the entire webhook payload as JSONB. You'll need it for debugging, and storage is cheap.
• Use double-entry bookkeeping in your Ledger. Every transaction is a debit and a credit. This makes reconciliation bulletproof.
• Make your webhook handler idempotent by using Stripe event IDs as unique keys. Duplicate events should not create duplicate ledger entries.
• Add timestamps for creation, update, and processing. They're invaluable for tracing and debugging timeline issues.