By Jake Mcdonald, founder of ShipEmber · November 17, 2026 · About an 11-minute read · Checked against the Stripe, Auth.js, Prisma, and PostgreSQL docs
I'm Jake Mcdonald. Every time I start a SaaS, there's a moment where I open schema.prisma and ask the same question: how much of Stripe should live in my database? Copy too little and every page load turns into a Stripe API call. Copy too much and you end up with two sources of truth for money, which is a bad place to be.
The rule I've settled on: Stripe owns the money, my database owns the answer to "can this user use Pro right now?" I store IDs and a few fields of state that Stripe sends me through webhooks, and I let Stripe keep everything else: cards, invoices, amounts, tax, and retry schedules.
This post walks through the schema I use for a one-product Free/Pro app in Next.js (App Router): the Auth.js models, a Stripe customer ID, a Subscription table, a StripeEvent table for idempotency, and the indexes that hold it together. The Stripe facts were checked against the Subscription object reference, the 2025-03-31.basil changelog, and Stripe's webhook docs. The Auth.js models come from the Auth.js Prisma adapter page, and the config notes from Prisma's v7 upgrade guide. I checked all of them on November 17, 2026.
The short version: store IDs and state, not money
| Data | Where it lives | Why |
|---|---|---|
Stripe customer ID (cus_...) |
Your DB, on User, unique |
Links webhooks back to a user without guessing by email |
| Subscription ID, status, price ID | Your DB, Subscription table |
Lets you check access on the server without calling Stripe |
| Current period end, cancel flags | Your DB, Subscription table |
Shows "renews on" or "ends on" in the dashboard |
Processed event IDs (evt_...) |
Your DB, StripeEvent table |
Stops a duplicate delivery from running twice |
| Card numbers, CVCs, bank details | Stripe only | Checkout collects them on a Stripe-hosted page; they never need to touch your server |
| Invoices, amounts, tax, proration | Stripe only | Stripe calculates them; the customer portal shows invoices |
| "Is this user Pro?" | Derived in code | Computed from status + price ID, not stored as a second truth |
Start with the Auth.js adapter models
If you use Auth.js with a database session (or just want users in Postgres), @auth/prisma-adapter expects specific models. The current Auth.js docs list four: User, Account, Session, and VerificationToken, plus an optional Authenticator model if you turn on WebAuthn (passkeys). Keep those model and field names as the docs show them. The adapter reads and writes them directly, so renaming providerAccountId or sessionToken breaks sign-in in ways that are confusing to debug.
Billing fields go next to the adapter fields, not in place of them. Adding stripeCustomerId to User is fine. The adapter ignores columns it doesn't know about.
One Prisma 7 note: the schema on the Auth.js page still shows url = env("DATABASE_URL") inside the datasource block. In Prisma ORM 7, the connection URL moved out of schema.prisma and into prisma.config.ts, and Prisma no longer loads .env for you (the docs use import "dotenv/config"). If you copy the Auth.js schema into a Prisma 7 project, move the URL:
// prisma.config.ts (Prisma ORM 7)
import "dotenv/config"
import { defineConfig, env } from "prisma/config"
export default defineConfig({
schema: "prisma/schema.prisma",
datasource: {
url: env("DATABASE_URL"),
},
})
The billing schema
Here's the billing part. The Auth.js models stay as the docs show them; I've only added the relations to User.
datasource db {
provider = "postgresql"
}
generator client {
provider = "prisma-client"
output = "../src/generated/prisma"
}
// Mirrors Stripe's subscription statuses exactly, so no mapping layer
enum SubscriptionStatus {
incomplete
incomplete_expired
trialing
active
past_due
canceled
unpaid
paused
}
model User {
id String @id @default(cuid())
name String?
email String @unique
emailVerified DateTime?
image String?
stripeCustomerId String? @unique // cus_..., set once, reused forever
accounts Account[]
sessions Session[]
subscriptions Subscription[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model Subscription {
id String @id @default(cuid())
userId String
stripeSubscriptionId String @unique // sub_...
status SubscriptionStatus
priceId String // price_..., used to derive the plan
currentPeriodEnd DateTime? // from the subscription item (see below)
cancelAtPeriodEnd Boolean @default(false)
cancelAt DateTime? // a scheduled end date, if any
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId])
}
// Idempotency ledger: one row per Stripe event you've handled
model StripeEvent {
id String @id // evt_...
type String
createdAt DateTime @default(now())
}
A few choices in there are worth explaining.
The customer: one stripeCustomerId per user, and make it unique
Stripe's webhook events about subscriptions carry a customer ID, not your user ID. So the first lookup in almost every handler is "which user has this customer ID?" That's why stripeCustomerId is @unique: it gives you an index for the lookup and makes it impossible for two users to point at the same Stripe customer.
The trap is creating more customers than you meant to. Stripe's docs for Checkout Sessions say that if you leave customer blank in subscription mode, Checkout creates a new Customer object. That's fine the first time. But if a user checks out, cancels, and comes back later, a blank customer gives them a second customer, and now their history is split. So:
- On the first checkout, pass
client_reference_id(your user ID) so thecheckout.session.completedhandler knows who paid, then savesession.customertoUser.stripeCustomerId. (You can also create the customer yourself before Checkout and save it right away.) - On every later checkout or portal session, pass the saved
customer.
Do you need a separate Customer table? For one product with Free and Pro, no. A column is enough. A table starts to make sense when a customer can belong to an organization instead of a person, which is a bigger app than this post is about.
The subscription: the fields worth copying
From the whole Subscription object, I copy six fields. Everything else I fetch from Stripe if I ever need it.
stripeSubscriptionId, unique. It's what you upsert on when a customer.subscription.updated event arrives.
status. Stripe lists eight values: incomplete, incomplete_expired, trialing, active, past_due, canceled, unpaid, and paused. Quick meanings, from the API reference:
-
incomplete: the first payment attempt failed. If the first invoice isn't paid within 23 hours, it becomesincomplete_expired, which is final. -
trialing: in a trial; it moves toactivewhen the trial ends. -
past_due: a renewal payment failed (for automatic charging) and Stripe is retrying. -
canceledorunpaid: where it ends up after retries run out, depending on your Billing settings. -
paused: for example, a trial ended without a payment method. It's different from pausing collection, which leaves the status alone.
I mirror these as a Prisma enum with Stripe's exact spellings. That way the webhook writes sub.status straight through, and a value I forgot to map can't quietly become active. If you'd rather not run a migration if Stripe ever adds a status, a plain String column works too.
priceId, from sub.items.data[0].price.id. I store the price, not a plan name, because the price is what Stripe actually knows about. The plan is something I derive (more below).
currentPeriodEnd. This one changed, and plenty of tutorials haven't caught up. Starting with API version 2025-03-31.basil (stripe-node v18), current_period_start and current_period_end were removed from the Subscription object and added to each subscription item. Stripe's changelog says to read items.data.current_period_end instead. Code that still reads subscription.current_period_end on a newer API version gets undefined, and if it's behind a type cast, nothing complains. Your "renews on" date is just quietly empty.
When you create a webhook endpoint for snapshot events, you pick its API version, so check both your SDK version and your endpoint's version. For a single-item Free/Pro subscription, reading the first item is enough:
// lib/billing/sync.ts
import type Stripe from "stripe"
const toDate = (s: number | null | undefined) => (s ? new Date(s * 1000) : null)
export function subscriptionFields(sub: Stripe.Subscription) {
const item = sub.items.data[0]
return {
stripeSubscriptionId: sub.id,
status: sub.status, // same strings as the Prisma enum
priceId: item?.price.id ?? "",
// basil and later: the period lives on the item, not the subscription
currentPeriodEnd: toDate(item?.current_period_end),
cancelAtPeriodEnd: sub.cancel_at_period_end,
cancelAt: toDate(sub.cancel_at),
}
}
If you ever sell multi-item subscriptions with different intervals, each item has its own period, and you'll want to decide which one you show.
cancelAtPeriodEnd and cancelAt. cancel_at_period_end is true when an active subscription is set to end at the end of the period. But in 2025, Stripe also added cancel_at values (min_period_end, max_period_end) for subscriptions in flexible billing mode and marked the cancel_at_period_end parameter as deprecated. The changelog notes that the cancel_at_period_end field isn't compatible with the new cancel_at helpers. In practice, a subscription can be scheduled to end with cancel_at set while cancel_at_period_end is false. Storing both costs one column and keeps the "Your plan ends on..." banner honest either way.
StripeEvent: the table that makes webhooks safe to retry
Stripe's docs say it plainly: webhook endpoints "might occasionally receive the same event more than once," and you should log the event IDs you've processed and skip ones you've seen. They also say Stripe doesn't guarantee events arrive in order, and that you shouldn't use created to decide order or detect duplicates.
That's what StripeEvent is for. The evt_... ID is the primary key, so the database itself refuses a second row for the same event. Insert it in the same transaction as the work, and two simultaneous deliveries can't both win. I walked through that claim-in-a-transaction pattern, plus refetching the subscription so out-of-order events don't matter, in Stripe Webhooks in Next.js: Idempotency, Out-of-Order Events, and Retries.
I keep only id, type, and a timestamp. You could store the full payload for debugging, but it includes customer details, and the Dashboard already keeps event history. If you do store it, plan a cleanup job.
Derive the plan instead of storing it twice
There's no plan column in the schema above. That's on purpose. "Pro" is a fact about two things Stripe already told you, the status and the price, so I compute it:
// lib/billing/plan.ts
import type { Subscription } from "@/generated/prisma/client"
const PRO_PRICE_IDS = new Set(
[process.env.STRIPE_PRO_MONTHLY_PRICE_ID, process.env.STRIPE_PRO_YEARLY_PRICE_ID].filter(Boolean),
)
export function hasPro(sub: Pick<Subscription, "status" | "priceId"> | null) {
if (!sub) return false
const paying = sub.status === "active" || sub.status === "trialing"
return paying && PRO_PRICE_IDS.has(sub.priceId)
}
Whether past_due keeps access during Stripe's retries is a product decision. I lean toward a short grace period so a failed card doesn't lock someone out mid-task, but either is fine as long as it's in one function. And that function has to run on the server: in the page, the Server Action, and the API route. I covered where to call it in Free vs Pro Feature Gating in Next.js App Router.
If you'd still like a cached plan column for fast queries, that's a reasonable trade. Just write it only from the webhook, in the same transaction as the subscription upsert, so the two can't drift apart.
What not to store
- Card data. No card numbers, CVCs, or expiry dates. Checkout and the customer portal collect payment details on Stripe-hosted pages, so your app never has to touch them. Storing raw card data would pull your app into PCI requirements you don't want.
- Prices and amounts. Keep price IDs, not "$12/month." Prices change, currencies vary, and coupons exist. Stripe knows the real number.
- Invoices and receipts. The Stripe customer portal can show invoice history and handle cancel and upgrade. That saves you a table and a page. More on that in Stripe Customer Portal in Next.js.
- A plan you set from the success page. The Checkout success URL just means the user's browser came back. Only the webhook should change billing state.
Indexes and unique constraints, in one list
-
User.email @unique: already in the Auth.js schema. -
User.stripeCustomerId @unique: the webhook's main lookup; also blocks two users from sharing a customer. -
Subscription.stripeSubscriptionId @unique: the upsert key for subscription events. -
Subscription @@index([userId]): for "load this user's subscription" on every gated request. If you only ever allow one subscription per user, makeuserId@uniqueinstead and upsert by user. That's simpler, but you lose history when someone resubscribes and gets a newsub_.... -
StripeEvent.id @id: the idempotency guarantee itself. - Auth.js keys:
Accountis keyed on[provider, providerAccountId],Session.sessionTokenis unique, andVerificationTokenis keyed on[identifier, token]. Keep them as the docs define them.
One Postgres detail people miss: a foreign key doesn't automatically create an index on the referencing column. The PostgreSQL docs on constraints point this out and suggest indexing it yourself, since deletes on the referenced table have to scan for matching rows. That's why Subscription gets @@index([userId]), and it's worth adding the same to Account and Session if your user deletes cascade through big tables.
A 10-minute test before you trust it
- Run
stripe listen --forward-to localhost:3000/api/stripe/webhookand subscribe with a test card. You should see oneUser.stripeCustomerId, oneSubscriptionrow withstatus = active, and oneStripeEventrow per handled event. -
Check
currentPeriodEndisn't null. If it is, you're reading the old subscription-level field. - Check out a second time as the same user (after canceling). There should still be one Stripe customer for that user.
- Cancel through the customer portal.
cancelAtPeriodEndorcancelAtshould be set, andhasProshould stay true until the period ends. - Resend one event (from the Dashboard, or with
stripe events resendin the CLI) and confirm nothing runs twice.
Why choose ShipEmber
None of this requires a product. It's a schema and a couple of helpers, and if you'd rather write them yourself, the code above is a solid start. I built ShipEmber because I kept rebuilding this exact layer (plus the auth, checkout, webhook, and gating around it) for every new idea.
ShipEmber is a Next.js App Router starter with that wiring done: Auth.js sign-in (Google, GitHub, and email magic links) on the Prisma adapter, Postgres via Prisma with a seed script, Stripe Checkout, a customer portal route, a signature-verified webhook route with a StripeEvent table, and a dashboard that checks the plan on the server. Its schema follows the shape in this post: a unique stripeCustomerId on User, a Subscription table with a unique Stripe subscription ID, and the idempotency table. It does differ in a couple of ways I mentioned above: it keeps one subscription row per user and caches a plan column that the webhook updates.
To be honest about where it doesn't fit: if you need organizations, per-seat billing, or several products from day one, a bigger kit will serve you better. Those usually cost more. ShipFast's Starter plan shows $199 on shipfa.st, and supastarter's Solo plan is $349 (both checked on November 17, 2026). ShipEmber is deliberately small, built for one product with a Free plan and a Pro plan.
It's a one-time payment, not a subscription. Right now the pricing page shows $49 as the launch price, going to $79. You can use it in unlimited personal and client projects, and if it doesn't work as described, you can email within 14 days for a refund.
Prefer watching over reading? There's a short walkthrough:
Watch the ShipEmber walkthrough on YouTube
See pricing or Get ShipEmber, $49.
Related: Stripe Webhooks in Next.js: Idempotency, Out-of-Order Events, and Retries · Free vs Pro Feature Gating in Next.js App Router · Stripe Customer Portal in Next.js
Doc note: subscription statuses, cancel_at_period_end, and cancel_at checked on docs.stripe.com/api/subscriptions/object; the item-level current_period_end move on the 2025-03-31.basil changelog; cancel_at enums and the cancel_at_period_end deprecation on the 2025-05-28 and 2025-07-30 basil changelogs; Checkout customer creation on docs.stripe.com/api/checkout/sessions/create; duplicate and out-of-order events on docs.stripe.com/webhooks; Auth.js models on authjs.dev; Prisma 7 config on prisma.io/docs; foreign key indexing on postgresql.org/docs. All on November 17, 2026.
— Jake Mcdonald, Springfield, IL
Originally published on shipember.com.
Top comments (0)