A data backfill as a Drizzle custom migration: the ON CONFLICT that fails it and the query that finds what it skipped

When the kit added a billing_customers table on 2026-10-14, the code that reads it got a fallback for everyone who subscribed earlier. That fallback is the expand half of an expand/contract change. The contract half is copying the old values into the new table so the fallback can be deleted. That copy is a data migration, and drizzle-kit has a command for exactly that: drizzle-kit generate --custom, which produces an empty migration file for hand-written SQL. I wrote that Drizzle custom migration for a data backfill against a database seeded with the awkward cases. The obvious version of the SQL failed outright, and the version that worked skipped a row without saying so.

The fallback the backfill retires

From src/lib/billing/subscription.ts:

export async function getStripeCustomerId(userId: string): Promise<string | null> {
  if (!hasDatabase()) return null;
  const [saved] = await db()
    .select({ customerId: billingCustomers.stripeCustomerId })
    .from(billingCustomers)
    .where(eq(billingCustomers.userId, userId))
    .limit(1);
  if (saved) return saved.customerId;

  // Users who subscribed before billing_customers existed only have the id on
  // their subscription row.
  const [row] = await db()
    .select({ customerId: subscriptions.stripeCustomerId })
    .from(subscriptions)
    .where(eq(subscriptions.userId, userId))
    .orderBy(desc(subscriptions.updatedAt))
    .limit(1);
  return row?.customerId ?? null;
}

Every lookup for a legacy user costs a second query, and the code has to understand both shapes forever. The order is the one from shipping a Drizzle schema change when the deploy does not run migrations: first the code that writes the new table goes live, then the backfill runs, then a check confirms it worked, and only then does a release remove the fallback. Backfill too early and customers created by the old code in between are missed.

A custom migration file

npx drizzle-kit generate --custom --name backfill_billing_customers

That writes drizzle/0002_backfill_billing_customers.sql containing a single comment, -- Custom SQL migration file, put your code below! --. It also adds a journal entry and a snapshot identical to the previous one, since the schema did not change, so the next normal generate still diffs correctly. Into it goes:

INSERT INTO "billing_customers" ("user_id", "stripe_customer_id")
SELECT DISTINCT ON ("user_id") "user_id", "stripe_customer_id"
FROM "subscriptions"
ORDER BY "user_id", "updated_at" DESC
ON CONFLICT DO NOTHING;

DISTINCT ON with ORDER BY "updated_at" DESC keeps one row per user, the most recently updated one. That is the same row the fallback picks with orderBy(desc(subscriptions.updatedAt)), so the backfill writes down exactly what the code was already returning.

Tested against the awkward cases

On a throwaway Postgres 16 with the kit's two migrations applied, I seeded six users:

• u1: a plain legacy subscriber, with a subscription and no billing_customers row.
• u2: a legacy user with two customers from before the double-customer fix: cus_u2_old on a canceled subscription, cus_u2_new on the newer active one.
• u3: a post-fix user, already backfilled.
• u4: a saved row (cus_u4_a) that disagrees with their newest subscription (cus_u4_b).
• u5: a canceled subscription whose customer id, cus_u3, belongs to u3 in billing_customers.
• u6: no subscription at all.

The first version used a conflict target, ON CONFLICT ("user_id") DO NOTHING, and failed:

ERROR:  duplicate key value violates unique constraint "billing_customers_stripe_customer_id_unique"
DETAIL:  Key (stripe_customer_id)=(cus_u3) already exists.

A conflict target only covers the constraint it names. billing_customers has two: the primary key on user_id and a unique stripe_customer_id. A collision on the second one still raises. And because drizzle-kit migrate runs all pending migrations in one transaction, a failing backfill takes every migration in that batch down with it.

Without a target, drizzle-kit migrate exited 0. u1 got cus_u1. u2 got cus_u2_new, the newest, not the old one. u3 and u4 were left alone, and u6 got nothing, correctly. Running the same INSERT again by hand reported INSERT 0 0, and running migrate again changed nothing, because the file is recorded once. It is safe to rerun.

It also skipped u5 without a word, because DO NOTHING with no target swallows every kind of conflict.

Check before you remove the fallback

So the backfill is not done when the migration exits 0. It is done when these two queries come back empty, or every row they return has been looked at:

-- Subscribers who still have no saved customer
SELECT s.user_id, s.stripe_customer_id
FROM (SELECT DISTINCT ON (user_id) user_id, stripe_customer_id
      FROM subscriptions ORDER BY user_id, updated_at DESC) s
LEFT JOIN billing_customers b USING (user_id)
WHERE b.user_id IS NULL;

-- Saved customers that disagree with the newest subscription
SELECT s.user_id, b.stripe_customer_id AS saved, s.stripe_customer_id AS newest_sub
FROM (SELECT DISTINCT ON (user_id) user_id, stripe_customer_id
      FROM subscriptions ORDER BY user_id, updated_at DESC) s
JOIN billing_customers b USING (user_id)
WHERE b.stripe_customer_id <> s.stripe_customer_id;

On the test data, the first returned u5 | cus_u3 and the second u4 | cus_u4_a | cus_u4_b. Both are worth a person's attention before the fallback goes. u5 is the telling one. Today the fallback hands u5 the customer cus_u3, which is another user's, so the backfill did not create that problem. The check is what finally surfaced it.

Things that bite

• If you sync with db:push, migration files never run. Push applies schema differences and ignores the migrations folder, so a backfill written as a migration does nothing there. Run the SQL by hand instead. A database built with push also needs its migration history baselined before drizzle-kit migrate will work on it; the deploy-order post linked above covers that.
• Large tables want batches. Inside migrate's single transaction, one big INSERT ... SELECT holds its locks until every pending migration finishes. For a large table, run the backfill outside migrate, in batches of user ids.
• The data migration does not touch the schema. That is why --custom exists. Mixing it into a migration that also changes columns makes a failure harder to read and to retry.

Shipwright, the Next.js and Claude starter kit this blog documents, ships the billing_customers table and the fallback above. This backfill and its checks are what retiring that fallback takes. Neither is in the kit yet.