Why can't I just rename a column in one migration?

Because a deploy is not atomic. For a few minutes your old code and your new code are both running against the same database. The instant the rename lands, every old instance that still selects the old name starts throwing errors, and every request routed to it fails. Renaming safely takes three separate deploys: add the new column and dual-write to both, backfill the old rows, then drop the old column once nothing reads it.

Does adding a column to a large Postgres table lock it?

Adding a nullable column, or a column with a non-volatile default, is a metadata-only change on PostgreSQL 11 and up, so it is effectively instant. What hurts is SET NOT NULL (full table scan under an ACCESS EXCLUSIVE lock), changing a column type (full table rewrite), and building an index without CONCURRENTLY (blocks writes for the whole build). Set a short lock_timeout at the top of every migration so a blocked statement fails fast instead of queueing every query behind it.

How do I stop Claude Code from writing a migration that breaks production?

Put the rules in CLAUDE.md and enforce them in CI rather than trusting the model to remember. The rules: additive changes only in a single migration, no rename or drop in the same deploy as the code change, a paired rollback file for every migration. The CI enforcement: regenerate the Supabase types and fail the build if they differ from the checked-in file, and run an up/down/up drill against a fresh local database on every pull request.

Database Migrations

A schema change is the one deploy that can take your product down for everyone at once. The fix is not being careful. The fix is a shape: expand, backfill, contract, three separate deploys, each one safe to roll back on its own. This guide wires that shape into a Next.js 16 app on PostgreSQL via Supabase, with a generated-types check that fails the build on drift, an Inngest job that backfills millions of rows without holding a lock, and a rollback drill that runs in CI.

Why One Migration Is Never Enough

Deploys are not atomic. When you ship, old instances and new instances serve traffic side by side for anywhere from thirty seconds to several minutes. Add a database migration and you now have four possible combinations of code and schema running at once, and every one of them has to work.

That is the whole reason ALTER TABLE profiles RENAME COLUMN name TO full_name is a production incident waiting to happen. The migration succeeds in half a second. Then every old instance still selecting name throws column "name" does not exist until the rollout finishes.

Expand/backfill/contract splits the change into three deploys that are each individually safe:

Phase	Migration	Code deploy	Safe to roll back?
Expand	Add the new column, nullable. Add a dual-write trigger.	Write to both, read the old one.	Yes, nothing depends on it
Backfill	No schema change.	No change.	Yes, the job is restartable
Contract	Drop the trigger and the old column.	Read and write the new one only.	Only after the bake period

Between phase two and phase three there is a bake period where both columns are correct and populated. That gap is your escape hatch. If the new read path is wrong, you revert the app and the old column is still there, still current, still being written by the trigger.

Point Claude at the Rules First

Claude Code will happily write you a one-shot rename migration, because that is what most of the training data looks like. The cheapest fix is a block in CLAUDE.md that it reads at session start. Keep it concrete: file paths, exact commands, and the specific SQL patterns you refuse to ship.

## Database Migrations

- Migrations live in supabase/migrations/, created with `supabase migration new <name>`.
- Every migration file has a paired rollback in supabase/rollback/ with the SAME filename.
- NEVER rename or drop a column in the same PR as the code that stops using it.
  Use expand -> backfill -> contract across three PRs.
- Every migration starts with `set lock_timeout = '3s';`.
- No `create index` without `concurrently`. Index builds go in supabase/manual/, not migrations.
- SET NOT NULL is a two-step: add a CHECK ... NOT VALID, VALIDATE it, then SET NOT NULL.
- Backfills never run inside a migration. They run as an Inngest job in batches.
- After any schema change, run `npm run db:types` and commit lib/database.types.ts.

The rollback rule is the one that changes Claude's behavior most. Once it knows every migration needs a partner file, it stops writing changes that cannot be undone, because it has to sit down and write the undo.

Phase 1: Expand

The expand migration is additive only. New nullable column, plus a trigger that keeps the old and new columns in sync for every write that lands while both versions of your code are live.

Create the file with the CLI so it gets a sortable timestamp prefix.

supabase migration new expand_profiles_full_name

The migration itself does three things: it sets a short lock timeout so a blocked ALTER fails fast instead of queueing every other query behind it, it adds the nullable column, and it installs a trigger that writes full_name on every insert and update. The trigger is what makes the old code path safe. An old instance writing only first_name and last_name still produces a correct full_name.

-- supabase/migrations/20260722091500_expand_profiles_full_name.sql
set lock_timeout = '3s';

alter table public.profiles
  add column if not exists full_name text;

create or replace function public.sync_profiles_full_name()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if new.full_name is null or new.full_name = '' then
    new.full_name := nullif(
      btrim(coalesce(new.first_name, '') || ' ' || coalesce(new.last_name, '')),
      ''
    );
  end if;
  return new;
end;
$$;

drop trigger if exists trg_profiles_full_name on public.profiles;

create trigger trg_profiles_full_name
  before insert or update on public.profiles
  for each row
  execute function public.sync_profiles_full_name();

add column ... text with no default and no not null is a catalog-only change on PostgreSQL 11 and up. It does not rewrite the table, so it finishes in milliseconds whether you have a thousand rows or fifty million.

The rollback file is the exact inverse, and you write it now while the change is fresh, not during the incident.

-- supabase/rollback/20260722091500_expand_profiles_full_name.sql
set lock_timeout = '3s';

drop trigger if exists trg_profiles_full_name on public.profiles;
drop function if exists public.sync_profiles_full_name();

alter table public.profiles
  drop column if exists full_name;

Push it to the linked project once the local drill passes.

supabase db push

The Operations That Actually Cause Downtime

Most migrations are harmless. A small number take an ACCESS EXCLUSIVE lock and hold it while they scan or rewrite the entire table, and every read and write on that table queues behind them. Knowing which is which is most of the skill.

Operation	Lock behavior	Safe pattern
Add nullable column	Metadata only	Ship it directly
Add column with non-volatile default	Metadata only (PG 11+)	Ship it directly
`set not null`	Full scan, `ACCESS EXCLUSIVE`	Add `check ... not valid`, `validate`, then `set not null`
Change column type	Full table rewrite	New column, backfill, contract
Add foreign key	Validates every row	Add `not valid`, then `validate constraint`
`create index`	Blocks writes for the build	`create index concurrently`, outside migrations
Drop column	Metadata only	Safe on the database, breaks any code still selecting it

The set not null case is worth spelling out because it is the one people get wrong on a big table. PostgreSQL 12 and up will skip the full validation scan if a matching, already-validated CHECK constraint exists. So you add the constraint as NOT VALID (instant, because it skips the validation scan), validate it separately (scans, but only takes SHARE UPDATE EXCLUSIVE, so writes keep flowing), and only then flip the column.

-- supabase/migrations/20260729101200_profiles_full_name_not_null.sql
set lock_timeout = '3s';

alter table public.profiles
  add constraint profiles_full_name_present
  check (full_name is not null) not valid;

alter table public.profiles
  validate constraint profiles_full_name_present;

alter table public.profiles
  alter column full_name set not null;

CREATE INDEX CONCURRENTLY is the exception to keeping everything in migration files. It cannot run inside a transaction block, and migration runners generally wrap each file in one. Keep those statements in supabase/manual/ and run them against the database directly, then note the date you ran them in the file.

-- supabase/manual/20260722_profiles_full_name_index.sql
-- Run manually against production. Cannot live in a migration (no transaction).
--   psql "$SUPABASE_DB_URL" -f supabase/manual/20260722_profiles_full_name_index.sql
create index concurrently if not exists profiles_full_name_idx
  on public.profiles (full_name);

A Types Check That Breaks the Build

The Supabase CLI generates TypeScript types from your schema. If those types are checked in and stale, every consumer of your database client is lying to you, and Claude Code writes code against a schema that no longer exists. Make staleness a build failure.

Generate the types into a tracked file and add both a write script and a check script.

{
  "scripts": {
    "db:types": "supabase gen types typescript --local > lib/database.types.ts",
    "db:types:check": "bash scripts/check-db-types.sh",
    "db:drill": "bash scripts/migration-drill.sh"
  }
}

The check script regenerates the types into a temporary file and diffs. Any difference means someone changed the schema without regenerating, and the exit code takes the build down with it.

#!/usr/bin/env bash
# scripts/check-db-types.sh
set -euo pipefail

TMP="$(mktemp)"
trap 'rm -f "$TMP"' EXIT

supabase gen types typescript --local > "$TMP"

if ! diff -u lib/database.types.ts "$TMP"; then
  echo ""
  echo "lib/database.types.ts is out of date."
  echo "Run: npm run db:types && git add lib/database.types.ts"
  exit 1
fi

echo "Database types are current."

There is a second kind of drift worth catching: someone changed the remote schema by hand in the Supabase dashboard, so your migration files no longer describe reality. supabase db diff against the linked project prints the difference, and empty output means you are clean.

supabase db diff --linked --schema public

Run that on a schedule rather than on every pull request. It needs credentials for the real project, and it is a drift alarm, not a gate.

Phase 2: Backfill With Inngest

A backfill is not a migration. update profiles set full_name = ... with no WHERE clause takes a row lock on every row in the table, holds one transaction open for however long it takes, bloats the table, and if it times out at minute nine you get nothing. Batch it instead, and run it somewhere with retries.

Do the actual work inside a PostgreSQL function so each batch is one round trip and one short transaction. This one pages by primary key (keyset pagination, which stays fast at any offset), updates only the rows that still need it, and returns the cursor for the next page.

-- supabase/migrations/20260722093000_backfill_full_name_fn.sql
set lock_timeout = '3s';

create or replace function public.backfill_profiles_full_name(
  p_after uuid,
  p_limit integer
)
returns table (last_id uuid, scanned integer, updated integer)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_page uuid[];
  v_updated integer := 0;
begin
  select array_agg(id order by id)
    into v_page
    from (
      select id
      from public.profiles
      where p_after is null or id > p_after
      order by id
      limit least(greatest(p_limit, 1), 2000)
    ) as page;

  if v_page is null then
    return query select null::uuid, 0, 0;
    return;
  end if;

  update public.profiles p
     set full_name = nullif(
           btrim(coalesce(p.first_name, '') || ' ' || coalesce(p.last_name, '')),
           ''
         )
   where p.id = any(v_page)
     and p.full_name is null;

  get diagnostics v_updated = row_count;

  return query
    select v_page[array_length(v_page, 1)],
           array_length(v_page, 1),
           v_updated;
end;
$$;

revoke execute on function public.backfill_profiles_full_name(uuid, integer)
  from public, anon, authenticated;

The revoke line matters. A security definer function runs with the privileges of its owner and bypasses row level security, so leaving it callable by the anon role hands every visitor a way to mutate the table.

The Inngest function drives that RPC. Each page is its own step.run, which makes it a durable checkpoint: if page 40 fails, Inngest retries page 40 and does not replay the 39 pages that already committed. After a fixed number of pages it sends itself a continuation event with the cursor, which keeps any single run well under the platform step limit and lets the job span millions of rows.

// lib/inngest/functions/backfill-full-name.ts
import { createClient } from "@supabase/supabase-js";
import { inngest } from "@/lib/inngest/client";
import type { Database } from "@/lib/database.types";

const PAGE_SIZE = 500;
const PAGES_PER_RUN = 40;

function adminClient() {
  return createClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    { auth: { persistSession: false } }
  );
}

export const backfillFullName = inngest.createFunction(
  {
    id: "backfill-profiles-full-name",
    concurrency: { limit: 1 },
    retries: 4,
  },
  { event: "db/backfill.full-name.requested" },
  async ({ event, step }) => {
    const supabase = adminClient();
    let cursor: string | null = event.data.cursor ?? null;
    let updated = event.data.updated ?? 0;
    let scanned = event.data.scanned ?? 0;

    for (let page = 0; page < PAGES_PER_RUN; page++) {
      const result = await step.run(`page-${scanned}`, async () => {
        const { data, error } = await supabase.rpc(
          "backfill_profiles_full_name",
          { p_after: cursor, p_limit: PAGE_SIZE }
        );

        if (error) throw new Error(`backfill rpc failed: ${error.message}`);

        const row = data?.[0];
        return {
          lastId: row?.last_id ?? null,
          scanned: row?.scanned ?? 0,
          updated: row?.updated ?? 0,
        };
      });

      scanned += result.scanned;
      updated += result.updated;
      cursor = result.lastId;

      if (result.scanned === 0 || cursor === null) {
        return { done: true, scanned, updated };
      }
    }

    await step.sendEvent("continue-backfill", {
      name: "db/backfill.full-name.requested",
      data: { cursor, scanned, updated },
    });

    return { done: false, scanned, updated };
  }
);

concurrency: { limit: 1 } is deliberate. One writer means the backfill never competes with itself for row locks, and the load it puts on the database stays flat and predictable instead of spiking when a retry overlaps a fresh run.

Kick it off once from a script or an admin-only endpoint, with no cursor.

// scripts/start-backfill.ts
import { inngest } from "@/lib/inngest/client";

await inngest.send({
  name: "db/backfill.full-name.requested",
  data: { cursor: null, scanned: 0, updated: 0 },
});

console.log("Backfill queued.");

Before you move to phase three, confirm the backfill is actually finished. A count of remaining rows is the only evidence that counts, and it should be zero twice, an hour apart.

select count(*) as remaining
from public.profiles
where full_name is null;

The Read Path During the Transition

While both columns exist, the application reads the new one and falls back to the old. This single function is what lets you deploy the read change and the contract migration on different days.

// lib/profiles.ts
import type { Database } from "@/lib/database.types";

type ProfileRow = Database["public"]["Tables"]["profiles"]["Row"];

export function displayName(profile: ProfileRow): string {
  if (profile.full_name && profile.full_name.trim() !== "") {
    return profile.full_name;
  }

  const legacy = [profile.first_name, profile.last_name]
    .filter(Boolean)
    .join(" ")
    .trim();

  return legacy !== "" ? legacy : "Unnamed";
}

Server Components call it directly. There is no client fetch and no loading state, which means the fallback costs you nothing at runtime.

// app/profile/[id]/page.tsx
import { notFound } from "next/navigation";
import { createServerClient } from "@/lib/supabase/server";
import { displayName } from "@/lib/profiles";

interface PageProps {
  params: Promise<{ id: string }>;
}

export default async function ProfilePage({ params }: PageProps) {
  const { id } = await params;
  const supabase = await createServerClient();

  const { data: profile } = await supabase
    .from("profiles")
    .select("id, first_name, last_name, full_name")
    .eq("id", id)
    .single();

  if (!profile) notFound();

  return (
    <main className="mx-auto max-w-lg py-12">
      <h1 className="text-2xl font-bold">{displayName(profile)}</h1>
    </main>
  );
}

Delete this fallback in the contract PR, not before. If it is still in the codebase, the old column is still load-bearing.

Phase 3: Contract

Contract is the only irreversible phase, so it gets the most preconditions. Ship it when all four are true: the remaining-rows count is zero, the new read path has been in production for at least a full business day, no code references the old columns (grep for them and mean it), and you have a database backup timestamped after the backfill finished.

-- supabase/migrations/20260805104500_contract_profiles_legacy_names.sql
set lock_timeout = '3s';

drop trigger if exists trg_profiles_full_name on public.profiles;
drop function if exists public.sync_profiles_full_name();

alter table public.profiles
  drop column if exists first_name,
  drop column if exists last_name;

Dropping a column in PostgreSQL is a catalog update, not a rewrite, so it is instant. The risk is never the lock. The risk is the one background worker nobody grepped for that still selects first_name.

The rollback for a contract migration cannot restore the data, and pretending otherwise is worse than admitting it. Write the file anyway, so the structure comes back and the app boots while you restore rows from backup.

-- supabase/rollback/20260805104500_contract_profiles_legacy_names.sql
-- WARNING: restores structure only. Column data must come from a backup.
set lock_timeout = '3s';

alter table public.profiles
  add column if not exists first_name text,
  add column if not exists last_name text;

The Rollback Drill

A rollback file you have never executed is a guess. Turn it into a test: reset a local database, apply everything, apply the rollback, apply again. If the migration is not idempotent or the rollback is incomplete, the second apply fails and you find out on a laptop instead of at 2am.

#!/usr/bin/env bash
# scripts/migration-drill.sh
set -euo pipefail

DB_URL="postgresql://postgres:postgres@127.0.0.1:54322/postgres"
LATEST="$(ls supabase/migrations/*.sql | sort | tail -n 1)"
NAME="$(basename "$LATEST")"
ROLLBACK="supabase/rollback/$NAME"

if [ ! -f "$ROLLBACK" ]; then
  echo "Missing rollback file: $ROLLBACK"
  exit 1
fi

echo "==> up (full reset, applies every migration)"
supabase db reset

echo "==> down ($NAME)"
psql "$DB_URL" -v ON_ERROR_STOP=1 -f "$ROLLBACK"

echo "==> up again (idempotency check)"
psql "$DB_URL" -v ON_ERROR_STOP=1 -f "$LATEST"

echo "==> types still match"
npm run db:types:check

echo "Drill passed: $NAME"

Wire both scripts into CI. The Supabase CLI runs the whole stack in Docker on the runner, so the drill exercises real PostgreSQL, not a mock.

# .github/workflows/db.yml
name: database

on:
  pull_request:
    paths:
      - "supabase/**"
      - "lib/database.types.ts"

jobs:
  migrations:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
      - uses: supabase/setup-cli@v1
        with:
          version: latest

      - run: npm ci
      - run: supabase start
      - run: npm run db:drill
      - run: npx tsc --noEmit

That workflow only runs when something under supabase/ or the generated types file changes, so it stays out of the way on frontend pull requests.

What to Actually Ask Claude Code

With the rules in CLAUDE.md and the gates in CI, the prompts get short. Plan mode first, because a migration plan is worth reading before any SQL exists.

claude --permission-mode plan "expand/backfill/contract plan to replace profiles.first_name and
profiles.last_name with a single full_name column. Three PRs. List the migration
files, the rollback files, the Inngest backfill, and the preconditions for contract."

Then one phase at a time, never two.

claude "write phase 1 only: the expand migration plus its rollback file, following
the Database Migrations rules in CLAUDE.md. Then run npm run db:drill."

The db:drill in that prompt is the important half. Claude runs it, reads the failure, fixes the SQL, and runs it again. Without a command it can execute, you are the one running the loop.

A single Claude Code session handles one migration well. What it does not do on its own is hold the discipline across three PRs and two weeks, which is exactly where schema changes go wrong. That is what the $29 Code Kit adds on top of Claude Code (one payment, no subscription): a harness that plans a change before writing it, builds it, evaluates the output, tests it, and puts every step through the same quality gates (zero type errors, zero lint errors, a clean build) before anything is called done. Claude Code itself still needs a paid Anthropic plan, and the Kit is the pipeline around it, not a replacement for it.

Expand, backfill, contract. A rollback file written at the same time as every migration, and a drill that proves it works. A types check that fails the build the moment the schema and the code disagree. None of it is clever. All of it is the difference between a schema change nobody notices and a schema change everybody does.

Posted by @speedy_devv

Database Migrations

On this page