Database schema

users

Column	Type	Notes
`id`	`uuid` primary key	`defaultRandom()`
`clerk_user_id`	`text not null unique`	created lazily by `requireUser` on first sight
`email`	`text`	mirrored from Clerk, which stays the source of truth
`display_name`	`text`	full name if Clerk has one, otherwise the username
`locale`	`text not null`	language for mail about this person's access, default `en`
`stripe_customer_id`	`text unique`	created lazily on first checkout, cleared and replaced if Stripe stops accepting it
`created_at`	`timestamptz not null`	`defaultNow()`
`updated_at`	`timestamptz not null`	`defaultNow()`, refreshed by `$onUpdate`

Indexes: users_email_idx on email, and the unique constraint users_stripe_customer_id_key.

locale records the language the caller last checked out in. POST /checkout/session rewrites it from the request on every checkout, so a buyer who switches language is followed by their mail.

It is the column for mail whose subject is a person's access rather than one paid object. Both mails that ship today are about a Purchase, so they read purchases.locale instead.

Rows that predate the column carry the default en, as does an account requireUser created before its first checkout. Identity creation takes no locale, so English is the fallback in both cases.

stripe_customer_id is the reference that locks the charge email. Passing an existing Customer with a valid email to Checkout makes the email field non-editable, which is the only documented way to guarantee that the address Stripe records is the address Clerk holds. It is written on the caller's first checkout, because creating it at signup would couple signup latency to Stripe availability and would manufacture a Customer for everyone who never pays. The Customer's own email is refreshed from users.email before every later checkout, so a changed Clerk address reaches Stripe on the payment path rather than through an identity-time write. Nothing here caches what Stripe was last told; that would be a second column claiming to hold the same value. A fork that swaps payment providers changes this column too.

The value is not permanent. An id the current STRIPE_SECRET_KEY cannot use is cleared from the column, and the next checkout mints a replacement through the same lazy-creation path, so one unusable reference no longer ends an account's checkouts for good. The billing model has what counts as unusable and why.

Four tables and one view ship, one file each under packages/shared/src/db/schema/, with the relations declared only in that folder's index.ts.

There is no is_admin column, because a column needs a write path and the first write path is a bootstrap script. There is no password column, because no user of this product has one. There is no plan or entitlement column, because entitlement is derived from the price actually paid, so there is no field to edit.

purchases

One completed one-time payment. Recorded per payment, and it grants its offer's features permanently.

Column	Type	Notes
`id`	`uuid` primary key	`defaultRandom()`
`user_id`	`uuid`	nullable, `on delete cascade` to `users.id`
`email`	`text not null`	the charge email Stripe recorded
`stripe_customer_id`	`text`
`stripe_checkout_session_id`	`text`	unique
`stripe_payment_intent_id`	`text not null`	unique, and the upsert's idempotency key
`stripe_charge_id`	`text`	the receipt number the receipt email prints
`stripe_price_id`	`text not null`	the only input to entitlement
`amount`	`integer not null`	minor units, as Stripe charged
`currency`	`text not null`
`locale`	`text not null`	language selected at checkout, default `en`
`github_handle`	`text`	checked against GitHub before payment, from Session `metadata`
`refunded_at`	`timestamptz`	set only while Stripe reports a full refund
`amount_refunded`	`integer not null`	minor units refunded, default `0`
`fulfillment_state`	`text not null`	`pending`, `in_progress`, `delivered` or `failed`, default `pending`
`fulfillment_attempted_at`	`timestamptz`	when this row's most recent attempt began
`fulfilled_at`	`timestamptz`	set on 201 or 204, overwritten by each later success
`fulfillment_attempts`	`integer not null`	default `0`, incremented by the claim
`fulfillment_last_error`	`text`	set on a terminal failure, never a serialized error
`created_at`	`timestamptz not null`
`updated_at`	`timestamptz not null`

Indexes: purchases_stripe_payment_intent_id_key unique, purchases_stripe_checkout_session_id_key unique, purchases_user_id_idx, purchases_email_idx, and purchases_pending_idx on created_at partial where fulfillment_state = 'pending'.

fulfillment_state is the only place the state is recorded, and every reader is an eq or an inArray on it. Its four values come from FULFILLMENT_STATES in packages/shared/src/responses.ts, the same runtime array the column type and the wire type both read. See docs/adr/0038-fulfillment-state-is-a-column.md.

fulfillment_attempted_at and fulfilled_at each carry one fact. The first is written by the claim and says when the most recent attempt began, which is what the buyer's requeue cooldown reads on a failed row. The second is written on each successful delivery, so a later attempt that fails leaves it in place and moves the state to failed.

user_id is set when the row is written. Checkout requires a caller, so the Session carries both client_reference_id and a Customer that belongs to one user, and the webhook resolves the owner from those rather than by comparing email addresses. The column stays nullable because a Session created outside this api carries neither key; that case is logged as stripe.checkout.unowned and repaired with POST /admin/purchases/:id/link.

email is no longer a lookup key. It is kept because the receipt and invite mails address it and because it records what Stripe actually charged.

locale is a purchase snapshot rather than the user's current browser preference. Receipt and fulfillment emails can arrive after checkout, so they use the language of that purchase even if the buyer later changes the site language.

There is no product_type column. The reference implementation took one from the request body alongside the price, so the offer granted was whatever the caller said they bought.

subscriptions

One recurring Stripe Subscription. A customer may hold several independent ones at once.

Column	Type	Notes
`id`	`uuid` primary key	`defaultRandom()`
`stripe_subscription_id`	`text not null`	unique, and the upsert key
`user_id`	`uuid`	nullable, `on delete cascade` to `users.id`
`email`	`text not null`	from the expanded Customer, not a lookup key
`stripe_customer_id`	`text not null`
`stripe_price_id`	`text not null`	the only input to entitlement
`status`	`text not null`	one of the eight Stripe statuses
`amount`	`integer`	nullable, the latest paid Invoice's `amount_paid`
`currency`	`text`	nullable, alongside `amount`
`current_period_start`	`timestamptz`	from `items.data[].current_period_start`
`current_period_end`	`timestamptz`	from `items.data[].current_period_end`
`cancel_at_period_end`	`boolean not null`	default `false`
`canceled_at`	`timestamptz`
`created_at`	`timestamptz not null`
`updated_at`	`timestamptz not null`

Indexes: subscriptions_stripe_subscription_id_key unique, subscriptions_user_id_idx, subscriptions_email_idx.

status is one of active, past_due, unpaid, canceled, incomplete, incomplete_expired, trialing, paused. The list lives in packages/shared/src/stripe.ts as a value, because both the column type and the wire type need the runtime array.

Warning

The row keys on stripe_subscription_id, never on the customer id. A customer id identifies a person who may hold several subscriptions; a subscription id identifies the thing being paid for. An upsert keyed on the customer is a bug that only appears once somebody buys a second offer, which is why the unique index exists rather than a comment asking nicely.

amount is nullable and the null is meaningful: it is a subscription with no paid invoice yet. purchases carries the same pair as not null because a purchase is only recorded once the money has moved, and that asymmetry is the difference between the two objects.

amount comes from the latest invoice's amount_paid rather than from the price, so it is net of a promotion code, a proration and a mid-life price change. Checkout sets allow_promotion_codes, so a discounted subscription is a state this product invites.

email_deliveries

One row per mail claimed, whether or not the send then succeeded. A mail with no exclusive claim of its own takes one here before delivery, so a Stripe event that fires repeatedly still produces one mail.

Column	Type	Notes
`id`	`uuid` primary key	`defaultRandom()`
`kind`	`text not null`	one of `MAIL_KINDS`: `invite`, `receipt`, `access-at-risk`, `access-ended`, `trial-ending`
`stripe_object_id`	`text not null`	the Stripe object the mail reports on
`user_id`	`uuid`	nullable, `on delete set null` to `users.id`
`resend_id`	`text`	written once Resend accepts the mail
`created_at`	`timestamptz not null`	`defaultNow()`

Indexes: email_deliveries_kind_stripe_object_id_key unique on (kind, stripe_object_id).

The unique index is the claim. deliverOnce in apps/api/src/lib/email/ inserts with onConflictDoNothing().returning() and sends only when a row comes back, so the second caller for the same (kind, stripe_object_id) gets nothing back and sends nothing. invoice.payment_failed fires once per Smart Retry, which is four identical mails per dunning cycle without a claim.

The claim is at-most-once, not at-least-once. A Resend outage burns the row, which is logged and never retried, because mail about access that arrives late is worse than mail that never arrives. Delete a row only to deliberately permit one repeat. An unconfigured mailer is the one exception: it attempted no send, so deliverOnce releases the row.

kind comes from MAIL_KINDS in packages/shared/src/mail.ts, the same runtime array the column type and deliver both read.

user_id is nullable because a mail can precede a linked owner, and deleting the user keeps the row rather than cascading, so a re-fired webhook still finds the claim taken.

The invite takes no row. It sends inside the Fulfillment claim on purchases.fulfillment_state, which already runs it at most once. See docs/adr/0038-fulfillment-state-is-a-column.md.

The receipt claimed by writing purchases.stripe_charge_id before this table existed. That column keeps the charge id as a recorded fact and this table holds the lock. See docs/adr/0032-mail-follows-entitlement-not-invoices.md.

user_entitlements

A view, not a table. There is no second copy of entitlement anywhere, so a stale entitlement is not a bug class here.

Column	Type	Notes
`user_id`	`uuid`
`stripe_price_id`	`text`
`source`	`text`	`purchase` or `subscription`
`created_at`	`timestamptz`	read in TypeScript as `since`
`refunded_at`	`timestamptz`	the purchase's refund state, `null` on every subscription row

It is a UNION ALL of two selects: purchases where user_id is not null, and subscriptions where user_id is not null and the status is active, trialing or past_due. past_due is in the list because Stripe retries a failed renewal for up to two weeks, so revocation waits for canceled or unpaid. See docs/adr/0032-mail-follows-entitlement-not-invoices.md.

The view selects refunded_at and does not filter on it. readEntitlements skips a refunded row while REFUND_REVOKES_ACCESS is enabled. See docs/adr/0030-refund-revokes-a-derived-grant.md.

It stops at stripe_price_id on purpose. The price to offer mapping is the catalog, which is TypeScript, so putting it in SQL would mean a catalog edit needed a migration.

The view carries no order of its own, so readEntitlements sorts by since descending. Without that, the newest offer's position depends on the query plan.

Relations

export const relations = defineRelations(schema, (r) => ({
  users: {
    purchases: r.many.purchases(),
    subscriptions: r.many.subscriptions(),
  },
  purchases: { user: r.one.users({ from: r.purchases.userId, to: r.users.id }) },
  subscriptions: {
    user: r.one.users({ from: r.subscriptions.userId, to: r.users.id }),
  },
}))

user_entitlements is deliberately absent. It is read directly rather than traversed from a user, and Drizzle's relational builder does not take views.

Migrations

Committed under packages/shared/drizzle/, one folder per migration, each holding a migration.sql and the snapshot.json that drizzle-kit diffs the next one against.

Command	What it does
`pnpm db:generate`	diffs the TypeScript schema against the snapshot and writes the next migration
`pnpm db:migrate`	creates the target database if needed, then applies every pending migration

Nothing migrates at boot. Drizzle's migrator takes no advisory lock, so two instances migrating at once keep the data consistent but the loser exits 1, which is half a deploy dead.

Which database gets migrated is NODE_ENV, and it fails toward dev. Migrating production is NODE_ENV=production pnpm db:migrate, typed on purpose.

No migration is hand-written, and none is hand-edited afterwards. The recipe, and what goes wrong if you do, is Add a database table.

docs/adr/0009-drizzle-v1-on-neon-postgres.md, the ORM and the pooled and direct endpoints
docs/adr/0005-entitlement-derived-from-price-paid.md, why the view exists instead of a table
docs/adr/0003-independent-subscriptions-per-customer.md, the subscription key
docs/adr/0007-atomic-claim-for-side-effects.md, the claim column
docs/adr/0032-mail-follows-entitlement-not-invoices.md, one claim mechanism for every mail
docs/adr/0031-identity-precedes-payment.md, users.stripe_customer_id and how a paid row finds its owner
docs/adr/0023-two-databases-in-one-neon-project.md, dev and prod