-- migrations/001_core_schema.sql
create table tenant_accounts (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null unique,
  email text not null unique,
  display_name text,
  created_at timestamptz default now()
);

create table billing_records (
  account_id uuid primary key references tenant_accounts(id) on delete cascade,
  stripe_customer_ref text unique,
  stripe_sub_ref text unique,
  plan_status text check (plan_status in ('active', 'trialing', 'past_due', 'canceled')),
  period_end timestamptz,
  last_synced timestamptz default now()
);

create index idx_billing_status on billing_records(plan_status);

// app/auth/verify/route.ts
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';
import { NextResponse } from 'next/server';

export async function GET(request: Request) {
  const { searchParams, origin } = new URL(request.url);
  const authCode = searchParams.get('code');

  if (!authCode) {
    return NextResponse.redirect(`${origin}/login?error=missing_code`);
  }

  const cookieJar = cookies();
  const supabaseClient = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    { cookies: { get: (name) => cookieJar.get(name)?.value, set: () => {}, remove: () => {} } }
  );

  await supabaseClient.auth.exchangeCodeForSession(authCode);
  return NextResponse.redirect(`${origin}/workspace`);
}

// app/api/billing/initiate/route.ts
import Stripe from 'stripe';
import { NextResponse } from 'next/server';
import { getAuthenticatedSession } from '@/lib/session';

const paymentProcessor = new Stripe(process.env.STRIPE_SECRET_KEY!);

export async function POST(request: Request) {
  const session = await getAuthenticatedSession();
  if (!session?.user?.id) {
    return new NextResponse('Authentication required', { status: 401 });
  }

  const checkoutSession = await paymentProcessor.checkout.sessions.create({
    mode: 'subscription',
    customer_email:

session.user.email, line_items: [{ price: process.env.STRIPE_TIER_PRO!, quantity: 1 }], success_url: ${process.env.NEXT_PUBLIC_BASE_URL}/workspace?payment=confirmed, cancel_url: ${process.env.NEXT_PUBLIC_BASE_URL}/pricing?payment=abandoned, metadata: { tenant_id: session.user.id }, });

return NextResponse.json({ redirect_url: checkoutSession.url }); }

The `metadata.tenant_id` field is critical. It bridges the payment gateway's anonymous session with your internal user identity. Without it, the webhook handler cannot map transactions to accounts.

**Webhook Event Processor**
```ts
// app/api/webhooks/payment/route.ts
import Stripe from 'stripe';
import { NextResponse } from 'next/server';
import { getAdminDatabaseClient } from '@/lib/database';

const gateway = new Stripe(process.env.STRIPE_SECRET_KEY!);
const signingKey = process.env.STRIPE_WEBHOOK_SECRET!;

export async function POST(request: Request) {
  const rawPayload = await request.text();
  const signature = request.headers.get('stripe-signature')!;

  let event: Stripe.Event;
  try {
    event = gateway.webhooks.constructEvent(rawPayload, signature, signingKey);
  } catch {
    return new NextResponse('Invalid signature', { status: 400 });
  }

  const db = getAdminDatabaseClient();

  if (event.type === 'checkout.session.completed' || event.type === 'customer.subscription.updated') {
    const subscription = event.data.object as Stripe.Subscription;
    
    await db.from('billing_records').upsert({
      account_id: subscription.metadata.tenant_id,
      stripe_customer_ref: subscription.customer as string,
      stripe_sub_ref: subscription.id,
      plan_status: subscription.status,
      period_end: new Date(subscription.current_period_end * 1000),
      last_synced: new Date(),
    }, { onConflict: 'account_id' });
  }

  return NextResponse.json({ acknowledged: true });
}

The handler uses upsert with conflict resolution on account_id to guarantee idempotency. Stripe retries failed deliveries, and duplicate processing corrupts subscription state. The 200 response must return within 5 seconds; any heavy logging or notification logic should be deferred to a background queue.

Phase 3: Communication & Operational Automation (Days 61–90)

Once payments flow, operational overhead spikes. Transactional emails, self-service billing management, and error tracking must be automated to maintain the 3–5 monthly touchpoint target.

Transactional Email Service

// lib/notifications.ts
import { Resend } from 'resend';

const mailer = new Resend(process.env.RESEND_API_KEY!);

export async function dispatchOnboarding(targetEmail: string) {
  return mailer.emails.send({
    from: 'notifications@yourproduct.io',
    to: targetEmail,
    subject: 'Getting started: 3-minute setup guide',
    html: '<p>Access your workspace here...</p>',
  });
}

Direct invocation from server actions keeps the architecture simple during early growth. As volume scales, migrate to Supabase Edge Functions or a dedicated queue to decouple email delivery from request latency.

Architecture Rationale

• Supabase over Firebase: Relational schema with explicit foreign keys and RLS policies provide stronger data isolation for multi-tenant SaaS. Triggers eliminate application-level synchronization bugs.
• Stripe Checkout over Custom UI: Hosted pages abstract PCI compliance, support global payment methods, and reduce frontend code by ~60%. The trade-off is limited UI customization, which is acceptable for micro-tools.
• Vercel Serverless over Container Orchestration: Automatic scaling handles Product Hunt traffic spikes without cold-start penalties. Edge functions reduce latency for auth and webhook verification.

Pitfall Guide

1. Webhook Secret Environment Leakage

Explanation: Developers frequently copy production webhook secrets into local .env.local files. Signature verification fails in development, causing silent webhook drops. Fix: Maintain separate webhook endpoints for local and production environments. Use stripe listen --forward-to localhost:3000/api/webhooks/payment to generate a local signing key. Validate environment separation in CI pipelines.

2. Missing Idempotency in Payment Handlers

Explanation: Stripe retries webhook deliveries on timeout or network failure. Without idempotency, duplicate INSERT operations create conflicting subscription states. Fix: Always use UPSERT or ON CONFLICT DO UPDATE with a unique business key (account_id). Return HTTP 200 immediately after database acknowledgment. Defer non-critical operations (analytics, notifications) to async workers.

3. Overcomplicating the Checkout UI

Explanation: Building custom payment forms introduces PCI compliance requirements, card validation bugs, and regional payment method fragmentation. Fix: Use hosted checkout sessions exclusively during the first 90 days. Redirect users to the gateway, handle success/cancel URLs, and let the provider manage payment method diversity. Revisit custom UI only after reaching $5k MRR.

4. Ignoring Row-Level Security (RLS) Policies

Explanation: Application-level authorization checks are bypassed when direct database queries execute from server components or background jobs. Fix: Enable RLS on all tenant tables. Write policies that enforce owner_id = auth.uid() or service_role = current_user. Test policies using Supabase's built-in policy simulator before deployment.

5. Delaying the First Payment Gate

Explanation: Teams wait until "all features are ready" before integrating billing. This delays revenue validation and extends the feedback loop. Fix: Implement a hard payment gate at day 30. Offer a 7-day trial or limited free tier to collect usage data, then require payment for continued access. Revenue data is more valuable than feature completeness.

6. Hardcoding User References in Webhooks

Explanation: Relying on email addresses or display names to match webhook events to internal accounts fails when users change emails or use OAuth providers. Fix: Always pass a stable internal identifier via metadata during checkout session creation. Use UUIDs or database primary keys. Never parse webhook payloads for user matching logic.

7. Neglecting Stripe Billing Portal Integration

Explanation: Users cannot update expired cards, downgrade plans, or cancel subscriptions without manual intervention. Support tickets multiply. Fix: Generate a billing portal session URL on demand. Provide a "Manage Subscription" button in the dashboard that redirects to Stripe's hosted portal. This reduces operational overhead by ~90%.

Production Bundle

Action Checklist

• Initialize Supabase project with RLS enabled and create tenant/billing schema
• Configure authentication provider with magic link or OAuth, register callback route
• Implement database trigger to provision tenant accounts on auth creation
• Create Stripe product and price ID, configure environment variables
• Build checkout session route with metadata.tenant_id mapping
• Deploy webhook handler with signature verification and idempotent upsert
• Integrate Resend for onboarding and trial-expiry notifications
• Generate Stripe Billing Portal session endpoint for self-service management

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Global audience, <100 users	Stripe Checkout + Supabase	Unified payment methods, managed compliance, free tier covers early volume	$0–$25/mo (Vercel Pro optional)
Korea/Japan focus, local compliance	TossPayments + Supabase	Local card networks, regulatory alignment, higher conversion in APAC	$0–$50/mo (gateway fees apply)
High concurrency, >1k daily requests	Vercel Edge Functions + Redis cache	Reduced cold starts, sub-50ms auth checks, webhook verification at edge	$20–$50/mo (bandwidth + compute)
Complex billing (usage-based, tiers)	Stripe Metered Billing + Custom Webhook	Granular event tracking, automated proration, flexible pricing models	$0–$100/mo (depends on volume)

Configuration Template

# .env.example
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

STRIPE_SECRET_KEY=sk_live_...
STRIPE_WEBHOOK_SECRET=whsec_...
STRIPE_TIER_PRO=price_...

NEXT_PUBLIC_BASE_URL=https://yourproduct.io
RESEND_API_KEY=re_...

# Optional: Sentry for error tracking
SENTRY_DSN=https://...@o...ingest.sentry.io/...

// lib/database.ts
import { createClient } from '@supabase/supabase-js';

export function getAdminDatabaseClient() {
  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    { auth: { persistSession: false } }
  );
}

Quick Start Guide

1. Initialize Project: Run npx create-next-app@latest micro-tool --typescript --tailwind --app. Install dependencies: npm i @supabase/supabase-js @supabase/ssr stripe resend.
2. Configure Environment: Copy .env.example to .env.local. Populate Supabase URLs, Stripe keys, and Resend API key. Run stripe login and stripe listen --forward-to localhost:3000/api/webhooks/payment for local webhook testing.
3. Deploy Schema: Execute the SQL migration in Supabase Dashboard. Enable RLS on tenant_accounts and billing_records. Verify trigger execution by creating a test user.
4. Validate Flow: Start dev server (npm run dev). Complete authentication, trigger checkout with test card 4242 4242 4242 4242, and confirm webhook updates billing_records.plan_status to active. Deploy to Vercel and repeat validation in production.