Supabase with Stripe: Payment Integration Tutorial

-- Database schema for Stripe integration
create table customers (
  id uuid references auth.users on delete cascade primary key,
  stripe_customer_id text unique not null,
  email text not null,
  full_name text,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Subscriptions
create table subscriptions (
  id text primary key, -- Stripe subscription ID
  user_id uuid references auth.users on delete cascade not null,
  customer_id text references customers(stripe_customer_id) on delete cascade not null,
  status text not null check (status in (
    'active', 'canceled', 'incomplete', 'incomplete_expired',
    'past_due', 'trialing', 'unpaid'
  )),
  price_id text not null, -- Stripe price ID
  quantity int default 1,
  cancel_at_period_end boolean default false,
  cancel_at timestamp with time zone,
  canceled_at timestamp with time zone,
  current_period_start timestamp with time zone not null,
  current_period_end timestamp with time zone not null,
  trial_start timestamp with time zone,
  trial_end timestamp with time zone,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Pricing plans (synced from Stripe)
create table pricing_plans (
  id text primary key, -- Stripe price ID
  product_id text not null,
  name text not null,
  description text,
  amount int not null, -- Amount in cents
  currency text default 'usd',
  interval text not null check (interval in ('day', 'week', 'month', 'year')),
  interval_count int default 1,
  trial_period_days int,
  features jsonb, -- Array of features
  is_active boolean default true,
  created_at timestamp with time zone default now()
);

-- Payment history
create table payments (
  id text primary key, -- Stripe payment intent ID
  user_id uuid references auth.users on delete cascade,
  customer_id text references customers(stripe_customer_id) on delete cascade,
  amount int not null,
  currency text default 'usd',
  status text not null,
  description text,
  metadata jsonb,
  created_at timestamp with time zone default now()
);

-- Indexes
create index idx_subscriptions_user on subscriptions(user_id);
create index idx_subscriptions_status on subscriptions(status);
create index idx_customers_stripe on customers(stripe_customer_id);

-- RLS policies
alter table customers enable row level security;
alter table subscriptions enable row level security;
alter table pricing_plans enable row level security;
alter table payments enable row level security;

create policy "Users can view own customer record"
  on customers for select
  using (auth.uid() = id);

create policy "Users can view own subscriptions"
  on subscriptions for select
  using (auth.uid() = user_id);

create policy "Anyone can view active pricing plans"
  on pricing_plans for select
  using (is_active = true);

create policy "Users can view own payments"
  on payments for select
  using (auth.uid() = user_id);

// supabase/functions/create-checkout/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import Stripe from 'https://esm.sh/[email protected]?target=deno'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const stripe = new Stripe(Deno.env.get('STRIPE_SECRET_KEY')!, {
  apiVersion: '2023-10-16',
})

const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
)

serve(async (req) => {
  try {
    const { priceId, successUrl, cancelUrl, mode = 'subscription' } = await req.json()

    // Get authenticated user
    const authHeader = req.headers.get('Authorization')!
    const token = authHeader.replace('Bearer ', '')
    const { data: { user }, error: authError } = await supabase.auth.getUser(token)

    if (authError || !user) {
      return new Response(
        JSON.stringify({ error: 'Unauthorized' }),
        { status: 401 }
      )
    }

    // Get or create Stripe customer
    let customerId: string
    const { data: customer } = await supabase
      .from('customers')
      .select('stripe_customer_id')
      .eq('id', user.id)
      .single()

    if (customer?.stripe_customer_id) {
      customerId = customer.stripe_customer_id
    } else {
      // Create new Stripe customer
      const stripeCustomer = await stripe.customers.create({
        email: user.email!,
        metadata: {
          supabase_user_id: user.id,
        },
      })

      customerId = stripeCustomer.id

      // Save to database
      await supabase.from('customers').insert({
        id: user.id,
        stripe_customer_id: customerId,
        email: user.email!,
      })
    }

    // Create Checkout Session
    const session = await stripe.checkout.sessions.create({
      customer: customerId,
      mode,
      payment_method_types: ['card'],
      line_items: [
        {
          price: priceId,
          quantity: 1,
        },
      ],
      success_url: successUrl || `${Deno.env.get('APP_URL')}/dashboard?session_id={CHECKOUT_SESSION_ID}`,
      cancel_url: cancelUrl || `${Deno.env.get('APP_URL')}/pricing`,
      subscription_data: mode === 'subscription' ? {
        metadata: {
          supabase_user_id: user.id,
        },
      } : undefined,
      metadata: {
        supabase_user_id: user.id,
      },
    })

    return new Response(
      JSON.stringify({ sessionId: session.id, url: session.url }),
      { headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    console.error('Error creating checkout session:', error)
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500 }
    )
  }
})

// Client-side usage
// lib/stripe.ts
import { loadStripe } from '@stripe/stripe-js'
import { createClient } from '@/lib/supabase/client'

export async function createCheckoutSession(priceId: string) {
  const supabase = createClient()
  const { data: { session } } = await supabase.auth.getSession()

  if (!session) {
    throw new Error('Not authenticated')
  }

  const response = await fetch(
    `${process.env.NEXT_PUBLIC_SUPABASE_URL}/functions/v1/create-checkout`,
    {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        Authorization: `Bearer ${session.access_token}`,
      },
      body: JSON.stringify({
        priceId,
        successUrl: `${window.location.origin}/dashboard?success=true`,
        cancelUrl: `${window.location.origin}/pricing`,
      }),
    }
  )

  const { url, error } = await response.json()

  if (error) {
    throw new Error(error)
  }

  // Redirect to Stripe Checkout
  window.location.href = url
}

// components/PricingCard.tsx
export function PricingCard({ plan }: { plan: any }) {
  const [loading, setLoading] = useState(false)

  async function handleSubscribe() {
    setLoading(true)
    try {
      await createCheckoutSession(plan.id)
    } catch (error) {
      console.error('Error:', error)
      alert('Failed to start checkout')
      setLoading(false)
    }
  }

  return (
    <div className="border rounded-lg p-6">
      <h3 className="text-2xl font-bold mb-2">{plan.name}</h3>
      <div className="text-4xl font-bold mb-4">
        ${(plan.amount / 100).toFixed(2)}
        <span className="text-lg text-gray-500">/{plan.interval}</span>
      </div>
      <ul className="space-y-2 mb-6">
        {plan.features?.map((feature: string, i: number) => (
          <li key={i} className="flex items-center gap-2">
            <span>✓</span> {feature}
          </li>
        ))}
      </ul>
      <button
        onClick={handleSubscribe}
        disabled={loading}
        className="w-full py-3 bg-blue-500 text-white rounded-lg disabled:opacity-50"
      >
        {loading ? 'Loading...' : 'Subscribe'}
      </button>
    </div>
  )
}

// supabase/functions/stripe-webhook/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import Stripe from 'https://esm.sh/[email protected]?target=deno'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const stripe = new Stripe(Deno.env.get('STRIPE_SECRET_KEY')!, {
  apiVersion: '2023-10-16',
})

const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
)

serve(async (req) => {
  const signature = req.headers.get('stripe-signature')!
  const body = await req.text()

  let event: Stripe.Event

  try {
    event = stripe.webhooks.constructEvent(
      body,
      signature,
      Deno.env.get('STRIPE_WEBHOOK_SECRET')!
    )
  } catch (err) {
    console.error('Webhook signature verification failed:', err.message)
    return new Response(
      JSON.stringify({ error: 'Invalid signature' }),
      { status: 400 }
    )
  }

  console.log('Received event:', event.type)

  try {
    switch (event.type) {
      case 'customer.subscription.created':
      case 'customer.subscription.updated':
        await handleSubscriptionUpdate(event.data.object as Stripe.Subscription)
        break

      case 'customer.subscription.deleted':
        await handleSubscriptionDeleted(event.data.object as Stripe.Subscription)
        break

      case 'invoice.paid':
        await handleInvoicePaid(event.data.object as Stripe.Invoice)
        break

      case 'invoice.payment_failed':
        await handlePaymentFailed(event.data.object as Stripe.Invoice)
        break

      case 'checkout.session.completed':
        await handleCheckoutCompleted(event.data.object as Stripe.Checkout.Session)
        break

      default:
        console.log(`Unhandled event type: ${event.type}`)
    }

    return new Response(JSON.stringify({ received: true }))
  } catch (error) {
    console.error('Error processing webhook:', error)
    return new Response(
      JSON.stringify({ error: 'Webhook processing failed' }),
      { status: 500 }
    )
  }
})

async function handleSubscriptionUpdate(subscription: Stripe.Subscription) {
  const userId = subscription.metadata.supabase_user_id
  if (!userId) {
    console.error('No user ID in subscription metadata')
    return
  }

  const subscriptionData = {
    id: subscription.id,
    user_id: userId,
    customer_id: subscription.customer as string,
    status: subscription.status,
    price_id: subscription.items.data[0].price.id,
    quantity: subscription.items.data[0].quantity,
    cancel_at_period_end: subscription.cancel_at_period_end,
    cancel_at: subscription.cancel_at ? new Date(subscription.cancel_at * 1000).toISOString() : null,
    canceled_at: subscription.canceled_at ? new Date(subscription.canceled_at * 1000).toISOString() : null,
    current_period_start: new Date(subscription.current_period_start * 1000).toISOString(),
    current_period_end: new Date(subscription.current_period_end * 1000).toISOString(),
    trial_start: subscription.trial_start ? new Date(subscription.trial_start * 1000).toISOString() : null,
    trial_end: subscription.trial_end ? new Date(subscription.trial_end * 1000).toISOString() : null,
  }

  const { error } = await supabase
    .from('subscriptions')
    .upsert(subscriptionData)

  if (error) {
    console.error('Error upserting subscription:', error)
  }
}

async function handleSubscriptionDeleted(subscription: Stripe.Subscription) {
  const { error } = await supabase
    .from('subscriptions')
    .update({
      status: 'canceled',
      canceled_at: new Date().toISOString(),
    })
    .eq('id', subscription.id)

  if (error) {
    console.error('Error deleting subscription:', error)
  }
}

async function handleInvoicePaid(invoice: Stripe.Invoice) {
  // Record payment
  const { error } = await supabase.from('payments').insert({
    id: invoice.payment_intent as string,
    user_id: invoice.subscription_details?.metadata?.supabase_user_id,
    customer_id: invoice.customer as string,
    amount: invoice.amount_paid,
    currency: invoice.currency,
    status: 'succeeded',
    description: invoice.description || 'Subscription payment',
  })

  if (error) {
    console.error('Error recording payment:', error)
  }
}

async function handlePaymentFailed(invoice: Stripe.Invoice) {
  const userId = invoice.subscription_details?.metadata?.supabase_user_id
  
  // TODO: Send notification to user about failed payment
  console.log(`Payment failed for user ${userId}`)
}

async function handleCheckoutCompleted(session: Stripe.Checkout.Session) {
  if (session.mode === 'payment') {
    // One-time payment
    const { error } = await supabase.from('payments').insert({
      id: session.payment_intent as string,
      user_id: session.metadata?.supabase_user_id,
      customer_id: session.customer as string,
      amount: session.amount_total!,
      currency: session.currency!,
      status: 'succeeded',
      description: 'One-time purchase',
    })

    if (error) {
      console.error('Error recording payment:', error)
    }
  }
}

// supabase/functions/create-portal-session/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import Stripe from 'https://esm.sh/[email protected]?target=deno'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const stripe = new Stripe(Deno.env.get('STRIPE_SECRET_KEY')!, {
  apiVersion: '2023-10-16',
})

const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
)

serve(async (req) => {
  try {
    const { returnUrl } = await req.json()

    // Get authenticated user
    const authHeader = req.headers.get('Authorization')!
    const token = authHeader.replace('Bearer ', '')
    const { data: { user }, error: authError } = await supabase.auth.getUser(token)

    if (authError || !user) {
      return new Response(
        JSON.stringify({ error: 'Unauthorized' }),
        { status: 401 }
      )
    }

    // Get customer ID
    const { data: customer } = await supabase
      .from('customers')
      .select('stripe_customer_id')
      .eq('id', user.id)
      .single()

    if (!customer?.stripe_customer_id) {
      return new Response(
        JSON.stringify({ error: 'No customer found' }),
        { status: 404 }
      )
    }

    // Create portal session
    const session = await stripe.billingPortal.sessions.create({
      customer: customer.stripe_customer_id,
      return_url: returnUrl || `${Deno.env.get('APP_URL')}/dashboard`,
    })

    return new Response(
      JSON.stringify({ url: session.url }),
      { headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    console.error('Error creating portal session:', error)
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500 }
    )
  }
})

// hooks/useSubscription.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface Subscription {
  id: string
  status: string
  price_id: string
  current_period_end: string
  cancel_at_period_end: boolean
}

export function useSubscription() {
  const [subscription, setSubscription] = useState<Subscription | null>(null)
  const [loading, setLoading] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    loadSubscription()

    // Subscribe to subscription changes
    const channel = supabase
      .channel('subscription-changes')
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'subscriptions',
        },
        () => loadSubscription()
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [])

  async function loadSubscription() {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) {
      setLoading(false)
      return
    }

    const { data } = await supabase
      .from('subscriptions')
      .select('*')
      .eq('user_id', user.id)
      .eq('status', 'active')
      .single()

    setSubscription(data)
    setLoading(false)
  }

  async function openCustomerPortal() {
    const { data: { session } } = await supabase.auth.getSession()
    if (!session) return

    const response = await fetch(
      `${process.env.NEXT_PUBLIC_SUPABASE_URL}/functions/v1/create-portal-session`,
      {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          Authorization: `Bearer ${session.access_token}`,
        },
        body: JSON.stringify({
          returnUrl: window.location.href,
        }),
      }
    )

    const { url, error } = await response.json()

    if (error) {
      console.error('Error:', error)
      return
    }

    window.location.href = url
  }

  const isActive = subscription?.status === 'active'
  const isPro = isActive && !subscription?.cancel_at_period_end

  return {
    subscription,
    loading,
    isActive,
    isPro,
    openCustomerPortal,
  }
}

// components/SubscriptionStatus.tsx
import { useSubscription } from '@/hooks/useSubscription'
import { format } from 'date-fns'

export function SubscriptionStatus() {
  const { subscription, isActive, isPro, openCustomerPortal } = useSubscription()

  if (!subscription) {
    return (
      <div className="bg-white rounded-lg shadow p-6">
        <h3 className="text-lg font-semibold mb-4">Subscription</h3>
        <p className="text-gray-600 mb-4">You don't have an active subscription.</p>
        <a
          href="/pricing"
          className="inline-block px-6 py-2 bg-blue-500 text-white rounded-lg"
        >
          View Plans
        </a>
      </div>
    )
  }

  return (
    <div className="bg-white rounded-lg shadow p-6">
      <h3 className="text-lg font-semibold mb-4">Subscription Status</h3>

      <div className="space-y-3 mb-6">
        <div>
          <span className="text-gray-600">Status:</span>
          <span className={`ml-2 font-semibold ${
            isActive ? 'text-green-600' : 'text-gray-600'
          }`}>
            {subscription.status}
          </span>
        </div>

        <div>
          <span className="text-gray-600">Current period ends:</span>
          <span className="ml-2 font-semibold">
            {format(new Date(subscription.current_period_end), 'MMM d, yyyy')}
          </span>
        </div>

        {subscription.cancel_at_period_end && (
          <div className="text-orange-600 font-medium">
            Your subscription will cancel at the end of the billing period.
          </div>
        )}
      </div>

      <button
        onClick={openCustomerPortal}
        className="w-full py-2 border rounded-lg hover:bg-gray-50"
      >
        Manage Subscription
      </button>
    </div>
  )
}

// Track usage and report to Stripe
-- Usage tracking table
create table usage_records (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users on delete cascade not null,
  subscription_id text references subscriptions(id) on delete cascade not null,
  metric text not null, -- e.g., 'api_calls', 'storage_gb'
  quantity int not null,
  timestamp timestamp with time zone default now(),
  reported_to_stripe boolean default false
);

create index idx_usage_records_user on usage_records(user_id, timestamp desc);
create index idx_usage_records_unreported on usage_records(reported_to_stripe) where reported_to_stripe = false;

// supabase/functions/report-usage/index.ts
import { serve } from 'https://deno.land/[email protected]/http/server.ts'
import Stripe from 'https://esm.sh/[email protected]?target=deno'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const stripe = new Stripe(Deno.env.get('STRIPE_SECRET_KEY')!, {
  apiVersion: '2023-10-16',
})

const supabase = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
)

// Run this via cron job every hour
serve(async (req) => {
  try {
    // Get unreported usage
    const { data: records } = await supabase
      .from('usage_records')
      .select(`
        *,
        subscription:subscriptions(*)
      `)
      .eq('reported_to_stripe', false)

    if (!records || records.length === 0) {
      return new Response(
        JSON.stringify({ message: 'No usage to report' })
      )
    }

    // Group by subscription and metric
    const grouped = records.reduce((acc, record) => {
      const key = `${record.subscription_id}:${record.metric}`
      if (!acc[key]) {
        acc[key] = {
          subscription_id: record.subscription_id,
          metric: record.metric,
          quantity: 0,
          records: [],
        }
      }
      acc[key].quantity += record.quantity
      acc[key].records.push(record.id)
      return acc
    }, {})

    // Report to Stripe
    for (const [key, usage] of Object.entries(grouped)) {
      const { subscription_id, metric, quantity, records: recordIds } = usage as any

      // Get subscription item ID for the metric
      const subscription = await stripe.subscriptions.retrieve(subscription_id)
      const subscriptionItem = subscription.items.data.find(
        (item) => item.price.lookup_key === metric
      )

      if (!subscriptionItem) {
        console.error(`No subscription item found for metric ${metric}`)
        continue
      }

      // Create usage record in Stripe
      await stripe.subscriptionItems.createUsageRecord(
        subscriptionItem.id,
        {
          quantity,
          timestamp: Math.floor(Date.now() / 1000),
          action: 'increment',
        }
      )

      // Mark as reported
      await supabase
        .from('usage_records')
        .update({ reported_to_stripe: true })
        .in('id', recordIds)

      console.log(`Reported ${quantity} ${metric} for subscription ${subscription_id}`)
    }

    return new Response(
      JSON.stringify({ message: 'Usage reported successfully' })
    )
  } catch (error) {
    console.error('Error reporting usage:', error)
    return new Response(
      JSON.stringify({ error: error.message }),
      { status: 500 }
    )
  }
})

// Track usage in your application
export async function trackUsage(metric: string, quantity: number = 1) {
  const supabase = createClient()
  const { data: { user } } = await supabase.auth.getUser()
  
  if (!user) return

  // Get active subscription
  const { data: subscription } = await supabase
    .from('subscriptions')
    .select('id')
    .eq('user_id', user.id)
    .eq('status', 'active')
    .single()

  if (!subscription) return

  // Record usage
  await supabase.from('usage_records').insert({
    user_id: user.id,
    subscription_id: subscription.id,
    metric,
    quantity,
  })
}