Secure Stripe webhook verification and sync to PostgreSQL

mkdir stripe-webhook-postgres && cd stripe-webhook-postgres
npm init -y
npm install express stripe pg dotenv
npm install --save-dev nodemon

CREATE TABLE IF NOT EXISTS subscriptions (
  id VARCHAR(255) PRIMARY KEY, -- e.g. sub_1N4...
  customer_id VARCHAR(255) NOT NULL, -- e.g. cus_1N4...
  status VARCHAR(50) NOT NULL, -- active, trialing, past_due, canceled
  price_id VARCHAR(255) NOT NULL, -- e.g. price_1N4...
  quantity INTEGER DEFAULT 1,
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  cancel_at_period_end BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_subscriptions_customer_id ON subscriptions(customer_id);

import express from 'express';
import Stripe from 'stripe';
import pg from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const app = express();
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

// CRITICAL: Raw body parser must be mounted before standard json parser
app.post(
  '/api/webhooks/stripe',
  express.raw({ type: 'application/json' }),
  async (req, res) => {
    const sig = req.headers['stripe-signature'];
    let event;

    try {
      event = stripe.webhooks.constructEvent(
        req.body,
        sig,
        process.env.STRIPE_WEBHOOK_SECRET
      );
    } catch (err) {
      console.error(`Webhook signature verification failed: ${err.message}`);
      return res.status(400).send(`Webhook Error: ${err.message}`);
    }

    // Handle supported event types
    try {
      switch (event.type) {
        case 'customer.subscription.created':
        case 'customer.subscription.updated': {
          const sub = event.data.object;
          await upsertSubscription(sub);
          break;
        }
        case 'customer.subscription.deleted': {
          const sub = event.data.object;
          await markSubscriptionCanceled(sub.id);
          break;
        }
        default:
          console.log(`Unhandled event type: ${event.type}`);
      }

      // Acknowledge receipt to Stripe
      res.json({ received: true });
    } catch (err) {
      console.error(`Failed processing webhook event: ${err.message}`);
      res.status(500).json({ error: 'Database processing error' });
    }
  }
);

async function upsertSubscription(sub) {
  const query = `
    INSERT INTO subscriptions (
      id, customer_id, status, price_id, quantity,
      current_period_start, current_period_end, cancel_at_period_end, updated_at
    )
    VALUES ($1, $2, $3, $4, $5, to_timestamp($6), to_timestamp($7), $8, NOW())
    ON CONFLICT (id) DO UPDATE SET
      status = EXCLUDED.status,
      price_id = EXCLUDED.price_id,
      quantity = EXCLUDED.quantity,
      current_period_start = EXCLUDED.current_period_start,
      current_period_end = EXCLUDED.current_period_end,
      cancel_at_period_end = EXCLUDED.cancel_at_period_end,
      updated_at = NOW();
  `;

  const values = [
    sub.id,
    sub.customer,
    sub.status,
    sub.items.data[0]?.price?.id || '',
    sub.quantity || 1,
    sub.current_period_start,
    sub.current_period_end,
    sub.cancel_at_period_end
  ];

  await pool.query(query, values);
}

async function markSubscriptionCanceled(subId) {
  await pool.query(
    `UPDATE subscriptions SET status = 'canceled', updated_at = NOW() WHERE id = $1`,
    [subId]
  );
}

app.listen(3000, () => console.log('Stripe webhook listener running on port 3000'));

cat << 'EOF' > .env
PORT=3000
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/mydb
STRIPE_SECRET_KEY=sk_test_51...
STRIPE_WEBHOOK_SECRET=whsec_...
EOF

node server.js

# 1. Listen and forward events to local port 3000
stripe listen --forward-to localhost:3000/api/webhooks/stripe

# 2. In another terminal, trigger a test subscription event
stripe trigger customer.subscription.created