Stripe Dunning for Indie Hackers 2026: Auto-recover Failed Payments with Supabase + Resend

// app/api/stripe/webhook/route.ts
import Stripe from 'stripe'
import { NextRequest, NextResponse } from 'next/server'
import { handlePaymentFailed, handlePaymentRecovered } from '@/lib/dunning'

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!)

export async function POST(req: NextRequest) {
  const body = await req.text()
  const sig = req.headers.get('stripe-signature')!

  let event: Stripe.Event
  try {
    event = stripe.webhooks.constructEvent(body, sig, process.env.STRIPE_WEBHOOK_SECRET!)
  } catch {
    return NextResponse.json({ error: 'Webhook signature failed' }, { status: 400 })
  }

  switch (event.type) {
    case 'invoice.payment_failed':
      await handlePaymentFailed(event.data.object as Stripe.Invoice)
      break
    case 'invoice.paid':
      await handlePaymentRecovered(event.data.object as Stripe.Invoice)
      break
  }

  return NextResponse.json({ received: true })
}

// lib/dunning.ts
import Stripe from 'stripe'
import { createClient } from '@/lib/supabase/server'
import { sendPaymentFailedEmail, sendPaymentRecoveredEmail } from '@/lib/emails'

export async function handlePaymentFailed(invoice: Stripe.Invoice) {
  const supabase = createClient()
  const customerId = typeof invoice.customer === 'string'
    ? invoice.customer : invoice.customer?.id
  if (!customerId) return

  const { data: profile } = await supabase
    .from('profiles')
    .select('id, email, name')
    .eq('stripe_customer_id', customerId)
    .single()
  if (!profile) return

  // Mark as past_due
  await supabase
    .from('subscriptions')
    .update({ status: 'past_due', payment_failed_at: new Date().toISOString() })
    .eq('user_id', profile.id)

  // Generate Billing Portal URL
  const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!)
  const session = await stripe.billingPortal.sessions.create({
    customer: customerId,
    return_url: `${process.env.NEXT_PUBLIC_BASE_URL}/dashboard`,
  })

  await sendPaymentFailedEmail({
    to: profile.email,
    name: profile.name ?? 'there',
    billingPortalUrl: session.url,
  })
}

export async function handlePaymentRecovered(invoice: Stripe.Invoice) {
  const supabase = createClient()
  const customerId = typeof invoice.customer === 'string'
    ? invoice.customer : invoice.customer?.id
  if (!customerId) return

  const { data: profile } = await supabase
    .from('profiles').select('id, email, name').eq('stripe_customer_id', customerId).single()
  if (!profile) return

  await supabase
    .from('subscriptions')
    .update({ status: 'active', recovered_at: new Date().toISOString() })
    .eq('user_id', profile.id)

  await sendPaymentRecoveredEmail({ to: profile.email, name: profile.name ?? 'there' })
}

// lib/emails.ts
import { Resend } from 'resend'
const resend = new Resend(process.env.RESEND_API_KEY)

export async function sendPaymentFailedEmail({
  to, name, billingPortalUrl,
}: { to: string; name: string; billingPortalUrl: string }) {
  await resend.emails.send({
    from: 'support@yourapp.com',
    to,
    subject: `Action required: Update your payment method`,
    html: `
      <p>Hi ${name},</p>
      <p>We had trouble processing your payment — this can happen when a card expires or has insufficient funds.</p>
      <p>
        <a href="${billingPortalUrl}" style="background:#4f46e5;color:#fff;padding:12px 24px;border-radius:6px;text-decoration:none;display:inline-block;">
          Update payment method →
        </a>
      </p>
      <p style="color:#666;font-size:12px;">Stripe will retry automatically, but updating now ensures no service interruption.</p>
    `,
  })
}

ALTER TABLE subscriptions
  ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'active',
  ADD COLUMN IF NOT EXISTS payment_failed_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS payment_failed_count INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS recovered_at TIMESTAMPTZ;