{"content":"# Stripe Subscriptions DB Sync\n\nComplete subscription system with Stripe, Vercel Flags for plan configuration, webhook handling for syncing subscription state to Postgres, usage tracking, and billing portal integration.\n\n### Agent Skill\n\nInstall the Stripe AI skill for agentic payment flows:\n\n```bash\nbunx skills add stripe/ai -a opencode -a cursor -y\n```\n\n---\n\n### Architecture Flow\n\n```\nUser Action → Checkout Session → Stripe → Webhook → Database Sync → Feature Access\n```\n\n---\n\n## Dependencies\n\nRequired packages in `package.json`:\n\n```json\n{\n  \"dependencies\": {\n    \"stripe\": \"^17.7.0\",\n    \"flags\": \"^4.0.0\",\n    \"@vercel/edge-config\": \"^1.4.0\",\n    \"drizzle-orm\": \"^0.40.0\",\n    \"zod\": \"^3.23.8\"\n  }\n}\n```\n\n---\n\n## Environment Variables\n\nAdd these to your `.env.development` (synced to Vercel):\n\n```bash\n# Stripe Configuration\nSTRIPE_SECRET_KEY=sk_test_...          # Stripe secret key\nSTRIPE_WEBHOOK_SECRET=whsec_...        # Webhook signing secret\nSTRIPE_PRO_PRICE_ID=price_...          # Price ID for PRO plan\n\n# Application URL (for redirects)\nNEXT_PUBLIC_ORIGIN=http://localhost:3000\n```\n\n---\n\n## Stripe Database Schema\n\n### Stripe Customers Table\n\nLinks authenticated users to Stripe customer IDs:\n\n```typescript\n// src/lib/db/schema.ts\nexport const stripeCustomersTable = pgTable(\n  \"stripe_customers\",\n  {\n    id: uuid(\"id\").defaultRandom().primaryKey(),\n    userId: text(\"user_id\").notNull(),\n    stripeCustomerId: text(\"stripe_customer_id\").notNull(),\n    createdAt: timestamp(\"created_at\", { withTimezone: true })\n      .notNull()\n      .defaultNow(),\n    updatedAt: timestamp(\"updated_at\", { withTimezone: true })\n      .notNull()\n      .defaultNow()\n      .$onUpdate(() => new Date()),\n  },\n  () => [\n    sql`FOREIGN KEY (\"user_id\") REFERENCES \"neon_auth\".\"usersSyncTable\"(\"id\")`,\n  ],\n);\n```\n\n### Subscriptions Table\n\nStores active subscription data:\n\n```typescript\nexport const SUBSCRIPTION_STATUS = [\n  \"active\",\n  \"canceled\",\n  \"incomplete\",\n  \"incomplete_expired\",\n  \"past_due\",\n  \"paused\",\n  \"trialing\",\n  \"unpaid\",\n] as const;\n\nexport const subscriptionsTable = pgTable(\n  \"subscriptions\",\n  {\n    id: uuid(\"id\").defaultRandom().primaryKey(),\n    userId: text(\"user_id\").notNull().unique(),\n    stripeSubscriptionId: text(\"stripe_subscription_id\").notNull().unique(),\n    stripePriceId: text(\"stripe_price_id\").notNull(),\n    status: text(\"status\", { enum: SUBSCRIPTION_STATUS }).notNull(),\n    currentPeriodStart: timestamp(\"current_period_start\", {\n      withTimezone: true,\n    }).notNull(),\n    currentPeriodEnd: timestamp(\"current_period_end\", {\n      withTimezone: true,\n    }).notNull(),\n    cancelAtPeriodEnd: boolean(\"cancel_at_period_end\").notNull().default(false),\n    createdAt,\n    updatedAt,\n  },\n  () => [\n    sql`FOREIGN KEY (\"user_id\") REFERENCES \"neon_auth\".\"usersSyncTable\"(\"id\")`,\n  ],\n);\n```\n\n### User Metrics Table\n\nTracks usage per user per month:\n\n```typescript\nexport const userMetricsTable = pgTable(\n  \"user_metrics\",\n  {\n    userId: text(\"user_id\").notNull(),\n    month: text(\"month\").notNull(), // Format: \"YYYY-MM\"\n    chatRequests: integer(\"chat_requests\").default(0).notNull(),\n    createdAt,\n    updatedAt,\n  },\n  () => [\n    sql`PRIMARY KEY(\"user_id\", \"month\")`,\n    sql`FOREIGN KEY (\"user_id\") REFERENCES \"neon_auth\".\"usersSyncTable\"(\"id\")`,\n  ],\n);\n```\n\n---\n\n## Stripe Client Setup\n\nCreate a typed Stripe client factory:\n\n```typescript\n// src/lib/stripe/client.ts\nimport Stripe from \"stripe\";\nimport { stripeConfig } from \"./config\";\n\nlet stripeClient: Stripe | null = null;\n\nexport function getStripeClient(): Stripe {\n  if (!stripeClient) {\n    stripeClient = new Stripe(stripeConfig.server.secretKey, {\n      apiVersion: \"2025-04-30.basil\",\n      typescript: true,\n    });\n  }\n\n  return stripeClient;\n}\n```\n\n### Configuration Schema\n\n```typescript\n// src/lib/stripe/config.ts\nimport { configSchema, pub, server } from \"better-env/config-schema\";\n\nexport const stripeConfig = configSchema(\"Stripe\", {\n  secretKey: server({ env: \"STRIPE_SECRET_KEY\" }),\n  webhookSecret: server({ env: \"STRIPE_WEBHOOK_SECRET\" }),\n  proPriceId: server({ env: \"STRIPE_PRO_PRICE_ID\" }),\n  origin: pub({\n    env: \"NEXT_PUBLIC_ORIGIN\",\n    value: process.env.NEXT_PUBLIC_ORIGIN,\n  }),\n});\n```\n\n---\n\n## Subscription Plans & Feature Flags\n\nPlans are defined using Vercel Flags for easy configuration and testing:\n\n```typescript\n// src/lib/stripe/plans.ts\nimport { flag } from \"flags/next\"\nimport { stripeConfig } from \"./config\"\n\nexport function getPlansFlag() {\n  const defaultPlans = [\n    {\n      id: \"FREE\",\n      priceId: undefined,\n      chatLimit: 10,\n      chatDaysBehind: 0,\n      chatDaysAhead: 30,\n    },\n    {\n      id: \"PRO\",\n      priceId: stripeConfig.server.proPriceId,\n      chatLimit: 1000,\n      chatDaysBehind: Infinity,\n      chatDaysAhead: Infinity,\n    },\n  ]\n\n  return flag({\n    key: \"subscription-plans\",\n    options: [\n      { label: \"Default\", value: defaultPlans },\n      { label: \"Unlimited\", value: [...] }, // Testing variant\n    ],\n    decide() {\n      return defaultPlans\n    },\n  })\n}\n```\n\n### Getting User's Current Plan\n\n```typescript\nexport async function getStripePlan(userId: string) {\n  const plansFlag = getPlansFlag();\n  const plans = await plansFlag();\n  const freePlan = plans.find((plan) => plan.priceId === undefined) ?? plans[0];\n\n  const [customer, subData] = await Promise.all([\n    getStripeCustomer(userId),\n    getSubscription(userId),\n  ]);\n\n  if (!customer) return freePlan;\n  if (!subData || subData.status !== \"active\") return freePlan;\n\n  return (\n    plans.find((plan) => plan.priceId === subData.stripePriceId) ?? freePlan\n  );\n}\n```\n\n### Vercel Flags API Endpoint\n\nExpose flags for Vercel Toolbar testing:\n\n```typescript\n// src/app/.well-known/vercel/flags/route.ts\nimport { verifyAccess, type ApiData } from \"flags\";\nimport { getProviderData } from \"flags/next\";\n\nexport async function GET(request: NextRequest) {\n  const access = await verifyAccess(request.headers.get(\"Authorization\"));\n  if (!access) return NextResponse.json(null, { status: 401 });\n\n  return withUnauthenticatedContext(() => {\n    const providerData = getProviderData({\n      plans: getPlansFlag(),\n    });\n    return NextResponse.json<ApiData>(providerData);\n  });\n}\n```\n\n---\n\n## Customer Management\n\n### Database Operations\n\n```typescript\n// src/lib/db/stripe-customers.ts\nexport async function createStripeCustomer(\n  authenticatedUserId: string,\n  newStripeCustomer: NewStripeCustomer,\n): Promise<StripeCustomer> {\n  const { db } = getUserServerContext();\n  const result = await db\n    .insert(stripeCustomersTable)\n    .values({ ...newStripeCustomer, userId: authenticatedUserId })\n    .returning();\n  return result[0];\n}\n\nexport async function getStripeCustomer(\n  authenticatedUserId: string,\n): Promise<StripeCustomer | null> {\n  const { db } = getUserServerContext();\n  const result = await db\n    .select()\n    .from(stripeCustomersTable)\n    .where(eq(stripeCustomersTable.userId, authenticatedUserId));\n  return result[0] ?? null;\n}\n\nexport async function getStripeCustomerByCustomerId(\n  stripeCustomerId: string,\n): Promise<StripeCustomer | null> {\n  const { db } = getUserServerContext();\n  const result = await db\n    .select()\n    .from(stripeCustomersTable)\n    .where(eq(stripeCustomersTable.stripeCustomerId, stripeCustomerId));\n  return result[0] ?? null;\n}\n```\n\n### Creating Stripe Customer\n\n```typescript\n// src/lib/stripe/stripe.ts\nexport async function createStripeCustomer({\n  userId,\n  email,\n  name,\n}: {\n  userId: string;\n  email: string;\n  name?: string | null;\n}) {\n  const { stripe } = getServerContext();\n  const customer = await stripe.customers.create(\n    {\n      email,\n      name: name ?? undefined,\n      metadata: { userId },\n    },\n    { idempotencyKey: userId }, // Prevents duplicate customers\n  );\n  return customer.id;\n}\n```\n\n---\n\n## Checkout Flow\n\n### Redirect to Checkout\n\n```typescript\n// src/lib/stripe/stripe.ts\nexport async function redirectToCheckout({\n  userId,\n  email,\n  name,\n}: {\n  userId: string;\n  email: string;\n  name?: string | null;\n}) {\n  const { stripe, config } = getServerContext();\n\n  // Get or create Stripe customer\n  const customer = await getStripeCustomerDb(userId);\n  let stripeCustomerId = customer?.stripeCustomerId;\n\n  if (!stripeCustomerId) {\n    const customerId = await createStripeCustomer({ userId, email, name });\n    stripeCustomerId = customerId;\n    await createStripeCustomerDb(userId, { stripeCustomerId, userId });\n  }\n\n  // Get PRO plan price\n  const plansFlag = getPlansFlag();\n  const plans = await plansFlag();\n\n  const checkoutSession = await stripe.checkout.sessions.create({\n    customer: stripeCustomerId,\n    line_items: [\n      {\n        price: plans.find((plan) => plan.id === \"PRO\")?.priceId,\n        quantity: 1,\n      },\n    ],\n    mode: \"subscription\",\n    success_url: `${config.origin}/api/stripe`,\n    cancel_url: `${config.origin}/api/stripe`,\n    metadata: { userId },\n  });\n\n  redirect(checkoutSession.url!);\n}\n```\n\n### Server Action for Checkout\n\n```typescript\n// src/app/app/settings/actions.ts\n\"use server\";\n\nexport async function createCheckoutSession() {\n  return withAuthenticatedUserContext(async () => {\n    const { user } = getAuthenticatedUserContext();\n\n    if (!user.primaryEmailVerified || !user.primaryEmail) {\n      throw new Error(\"Email not verified\");\n    }\n\n    await redirectToCheckout({\n      userId: user.id,\n      email: user.primaryEmail,\n      name: user.displayName,\n    });\n  });\n}\n```\n\n---\n\n## Webhook Handling\n\n### API Route\n\n```typescript\n// src/app/api/stripe/route.ts\nimport { after, NextResponse } from \"next/server\";\n\n// GET: Post-checkout redirect handler\nexport async function GET() {\n  return withAuthenticatedUserContext(async () => {\n    const { userId } = getUserServerContext();\n    const customer = await getStripeCustomer(userId);\n\n    if (customer) {\n      await syncStripeData(customer.stripeCustomerId);\n    }\n\n    redirect(\"/app/settings\");\n  });\n}\n\n// POST: Webhook handler\nexport async function POST(req: Request) {\n  return withUnauthenticatedContext(async () => {\n    const body = await req.text();\n    const signature = await headers().then((h) => h.get(\"Stripe-Signature\"));\n\n    if (!signature) {\n      return new NextResponse(\"No signature\", { status: 400 });\n    }\n\n    // Process webhook in background (after response)\n    after(async () => {\n      await processStripeEvent({ body, signature });\n    });\n\n    return new NextResponse(null, { status: 200 });\n  });\n}\n```\n\n### Webhook Event Processing\n\n```typescript\n// src/lib/stripe/stripe.ts\nimport { logger } from \"@/lib/logging/logger\";\n\nconst allowedEventTypes = [\n  \"checkout.session.completed\",\n  \"checkout.session.async_payment_succeeded\",\n  \"customer.subscription.created\",\n  \"customer.subscription.updated\",\n  \"customer.subscription.deleted\",\n  \"customer.subscription.paused\",\n  \"customer.subscription.resumed\",\n  \"customer.subscription.pending_update_applied\",\n  \"customer.subscription.pending_update_expired\",\n  \"customer.subscription.trial_will_end\",\n  \"invoice.paid\",\n  \"invoice.payment_failed\",\n  \"invoice.payment_action_required\",\n  \"invoice.upcoming\",\n  \"invoice.marked_uncollectible\",\n  \"invoice.payment_succeeded\",\n  \"payment_intent.succeeded\",\n  \"payment_intent.payment_failed\",\n  \"payment_intent.canceled\",\n] as const;\n\nexport async function processStripeEvent({\n  body,\n  signature,\n}: {\n  body: string;\n  signature: string;\n}) {\n  const { event, success, error } = getStripeWebhookEvent({ body, signature });\n\n  if (!success) {\n    throw new Error(`Stripe webhook event error: ${error.message}`);\n  }\n\n  if (!isAllowedEventType(event)) {\n    logger.warn({ eventType: event.type }, \"Received untracked Stripe event\");\n    return;\n  }\n\n  const { customer } = event.data.object;\n  if (typeof customer !== \"string\") {\n    throw new Error(\"Stripe webhook handler failed\");\n  }\n\n  await syncStripeData(customer);\n}\n\nfunction getStripeWebhookEvent({\n  body,\n  signature,\n}: {\n  body: string;\n  signature: string;\n}) {\n  const { stripe, config } = getServerContext();\n  try {\n    const event = stripe.webhooks.constructEvent(\n      body,\n      signature,\n      config.stripe.webhookSecret,\n    );\n    return { success: true as const, event, error: null };\n  } catch (error) {\n    return { success: false as const, error: error as Error, event: null };\n  }\n}\n```\n\n### Syncing Subscription Data\n\n```typescript\nexport async function syncStripeData(customerId: string) {\n  const { stripe } = getServerContext();\n  const stripeCustomer = await getStripeCustomerByCustomerId(customerId);\n\n  if (!stripeCustomer) {\n    throw new Error(`Stripe customer not found: ${customerId}`);\n  }\n\n  const subscriptions = await stripe.subscriptions.list({\n    customer: customerId,\n    limit: 1,\n    status: \"all\",\n    expand: [\"data.default_payment_method\"],\n  });\n\n  if (subscriptions.data.length === 0) {\n    await deleteSubscriptions(customerId);\n    return null;\n  }\n\n  const subscriptionData = subscriptions.data[0];\n\n  await upsertSubscription(stripeCustomer.userId, {\n    userId: stripeCustomer.userId,\n    stripeSubscriptionId: subscriptionData.id,\n    status: subscriptionData.status,\n    stripePriceId: subscriptionData.items.data[0].price.id,\n    currentPeriodStart: new Date(subscriptionData.current_period_start * 1000),\n    currentPeriodEnd: new Date(subscriptionData.current_period_end * 1000),\n  });\n}\n```\n\n---\n\n## Billing Portal\n\nAllow users to manage their subscription:\n\n```typescript\n// src/lib/stripe/stripe.ts\nexport async function redirectToBillingPortal({ userId }: { userId: string }) {\n  const { stripe, config } = getServerContext();\n  const customer = await getStripeCustomer(userId);\n\n  if (!customer) {\n    throw new Error(\"Customer not found\");\n  }\n\n  const portalSession = await stripe.billingPortal.sessions.create({\n    customer: customer.stripeCustomerId,\n    return_url: `${config.origin}/app/settings`,\n  });\n\n  redirect(portalSession.url!);\n}\n```\n\n### Server Action\n\n```typescript\n// src/app/app/settings/actions.ts\nexport async function createBillingPortalSession() {\n  return withAuthenticatedUserContext(async () => {\n    const { user } = getAuthenticatedUserContext();\n    await redirectToBillingPortal({ userId: user.id });\n  });\n}\n```\n\n---\n\n## Usage Tracking\n\n### User Metrics Service\n\n```typescript\n// src/lib/user-metrics/service.ts\nimport \"server-only\";\n\nexport class UserMetricsService {\n  static getOrCreateCurrentMonthMetrics = async (\n    userId: string,\n  ): Promise<MonthlyUserMetrics> => {\n    const currentMonth = getCurrentMonth(); // \"YYYY-MM\" format\n    let [monthlyMetrics, stripePlan] = await Promise.all([\n      getUserMetrics(userId, currentMonth),\n      getStripePlan(userId),\n    ]);\n\n    if (monthlyMetrics) {\n      return {\n        chatRequests: monthlyMetrics.chatRequests,\n        chatLimit: monthlyMetrics.chatLimit,\n        subscription: monthlyMetrics.subscription,\n      };\n    }\n\n    const newMetrics = await createUserMetrics(userId, getCurrentMonth(), 0);\n    return {\n      chatRequests: newMetrics.chatRequests,\n      chatLimit: stripePlan.chatLimit,\n      subscription: stripePlan.id,\n    };\n  };\n\n  static incrementChatRequests = async (\n    userId: string,\n  ): Promise<MonthlyUserMetrics> => {\n    const monthlyMetrics = await this.getOrCreateCurrentMonthMetrics(userId);\n    const incrementedMetrics = await incrementUserMetrics(\n      userId,\n      getCurrentMonth(),\n    );\n\n    return {\n      chatRequests: incrementedMetrics.chatRequests,\n      chatLimit: monthlyMetrics.chatLimit,\n      subscription: monthlyMetrics.subscription,\n    };\n  };\n}\n```\n\n### Database Operations\n\n```typescript\n// src/lib/db/user-metrics.ts\nexport async function incrementUserMetrics(\n  userId: string,\n  month: string,\n): Promise<UserMetrics | null> {\n  const { db } = getUserServerContext();\n  const [updatedMetrics] = await db\n    .update(userMetricsTable)\n    .set({\n      chatRequests: sql`${userMetricsTable.chatRequests} + 1`,\n      updatedAt: new Date(),\n    })\n    .where(\n      and(\n        eq(userMetricsTable.userId, userId),\n        eq(userMetricsTable.month, month),\n      ),\n    )\n    .returning();\n\n  return updatedMetrics;\n}\n```\n\n---\n\n## UI Integration\n\n### Subscription Context Provider\n\n```typescript\n// src/lib/stripe/subscription-info.tsx\n\"use client\"\n\nimport { createContext, useContext } from \"react\"\n\nexport type SubscriptionInfo = {\n  planName: string\n  chatLimit: number\n}\n\nconst SubscriptionContext = createContext<SubscriptionInfo>({\n  planName: \"FREE\",\n  chatLimit: 100,\n})\n\nexport function SubscriptionContextProvider({\n  children,\n  subscriptionInfo,\n}: {\n  children: React.ReactNode\n  subscriptionInfo: SubscriptionInfo\n}) {\n  return (\n    <SubscriptionContext.Provider value={subscriptionInfo}>\n      {children}\n    </SubscriptionContext.Provider>\n  )\n}\n\nexport function useSubscriptionInfo() {\n  return useContext(SubscriptionContext)\n}\n```\n\n### Settings Page Component\n\n```tsx\n// Usage in settings page\n<div className=\"flex justify-end\">\n  {isPro ? (\n    <form action={createBillingPortalSession}>\n      <Button type=\"submit\">Manage Subscription</Button>\n    </form>\n  ) : (\n    <form action={createCheckoutSession}>\n      <Button type=\"submit\">Upgrade to Pro</Button>\n    </form>\n  )}\n</div>\n\n// Usage progress display\n<div className=\"space-y-2\">\n  <div className=\"flex items-center justify-between text-sm\">\n    <span>Monthly Chat Requests</span>\n    <span className=\"font-medium\">\n      {chatRequestCount} / {chatLimit}\n    </span>\n  </div>\n  <Progress value={(chatRequestCount / chatLimit) * 100} className=\"h-2\" />\n</div>\n```\n\n---\n\n## Development Setup\n\n### Local Webhook Forwarding\n\nThe `stripe.dev.ts` script uses Stripe CLI to forward webhooks locally:\n\n```typescript\n// scripts/stripe.dev.ts\nimport { spawn } from \"child_process\";\n\nconst events = [\n  \"checkout.session.completed\",\n  \"customer.subscription.created\",\n  \"customer.subscription.updated\",\n  // ... all webhook events\n];\n\nconst url = `${process.env.NEXT_PUBLIC_ORIGIN}/api/stripe`;\nconst stripeProcess = spawn(\"stripe\", [\n  \"listen\",\n  \"--events\",\n  events.join(\",\"),\n  \"--forward-to\",\n  url,\n]);\n```\n\n### Running Development Server\n\n```bash\n# In apps/web directory\nbun dev\n\n# This runs (via npm-run-all):\n# - bun dev:next    → Next.js dev server\n# - bun dev:stripe  → Stripe webhook forwarder\n```\n\n### Stripe CLI Setup\n\n1. Install Stripe CLI: `brew install stripe/stripe-cli/stripe`\n2. Login to Stripe: `stripe login`\n3. Set `.env.development` with test keys\n\n---\n\n## Production Setup\n\n### Production Webhook Registration\n\nThe `stripe.setup.ts` script registers webhook endpoint in Stripe Dashboard:\n\n```typescript\n// scripts/stripe.setup.ts\nimport Stripe from \"stripe\";\nimport { logger } from \"@/lib/logging/logger\";\n\nconst stripe = new Stripe(process.env.STRIPE_SECRET_KEY);\nconst url = `${process.env.NEXT_PUBLIC_ORIGIN}/api/stripe`;\n\nconst webhook = await stripe.webhookEndpoints.create({\n  url,\n  enabled_events: events,\n  description: \"Webhook endpoint for production\",\n});\n\nlogger.info({ secret: webhook.secret }, \"Webhook signing secret\");\n// Add this to your .env.development as STRIPE_WEBHOOK_SECRET and sync with `bun run env:push`\n```\n\n### Production Checklist\n\n1. **Create Stripe Products/Prices** in Stripe Dashboard\n2. **Set Environment Variables**:- `STRIPE_SECRET_KEY` - Live secret key\n- `STRIPE_WEBHOOK_SECRET` - From webhook registration\n- `STRIPE_PRO_PRICE_ID` - Your PRO plan price ID\n3. **Run Webhook Setup**: `bun run start:stripe`\n4. **Configure Billing Portal** in Stripe Dashboard\n5. **Test the Complete Flow**:- User clicks \"Upgrade to Pro\"\n- Completes checkout\n- Webhook fires\n- Subscription synced to database\n- User sees PRO features\n\n---\n\n## Summary\n\nThe subscription implementation follows these key patterns:\n\n1. **Separation of Concerns**: Stripe operations, database operations, and UI are cleanly separated\n2. **Idempotent Operations**: Customer creation uses idempotency keys to prevent duplicates\n3. **Background Processing**: Webhooks return 200 immediately and process in background\n4. **Feature Flags**: Plans are configurable via Vercel Flags for testing\n5. **Type Safety**: Full TypeScript with Zod validation for Stripe statuses\n6. **Usage Tracking**: Monthly metrics tracked per user with plan-based limits"}