Stopping Supabase generated types from drifting out of the schema

The bug report says a nightly job started writing null into a column that the TypeScript type insists is string. Nobody changed the job. Three weeks ago somebody made the column nullable in a migration, and src/db/types.generated.ts still describes the schema as it was before that.

Generated types are a photograph of the database, not a link to it. The compiler happily type-checks against a photograph.

Where the drift comes from

Four common sources, in rough order of frequency:

1. A migration lands without regenerating. The PR changes SQL, CI has nothing to say about it, review focuses on the SQL, and the types file is untouched.
2. Types generated from the wrong database. supabase gen types --local reads your Docker container. If you have not run bun run db:reset since pulling, your local schema is behind the repository and you generate types for a database nobody else has.
3. A dashboard edit. Someone adds a column in the Supabase table editor. Production has it, migrations do not, so local and hosted disagree permanently.
4. The file is gitignored. Treating generated output as a build artefact sounds principled and means every developer has a different version of the truth, and a fresh clone does not type-check until someone starts Docker.

The wrong way

# .gitignore
src/db/types.generated.ts

with a README line saying "run bun run db:types after pulling". Nobody does. The first symptom is a red editor on a new laptop; the second is a production null.

The right way

Commit the file, and regenerate it in the same commit as the migration.

The file does not exist in a freshly generated repo, because producing it means running the local stack. bun run db:start && bun run db:reset && bun run db:types creates it the first time; commit it then, and it is a normal source file from that point on.

bunx supabase migration new add_archived_at
# edit supabase/migrations/<ts>_add_archived_at.sql
bun run db:reset      # replay everything from empty
bun run db:types      # regenerate from the database you just rebuilt
git add supabase/migrations src/db/types.generated.ts

Order matters: reset first, then generate. Generating against a stale container is source number two above.

The script this project ships is:

"db:types": "bunx supabase gen types typescript --local > src/db/types.generated.ts"

Against a hosted project instead, when local Docker is not an option:

bunx supabase gen types typescript --project-id <project-ref> > src/db/types.generated.ts

Prefer --local. The hosted project may contain dashboard edits that are not in your migrations, and generating from it silently blesses that drift.

Make the check mechanical

A convention that depends on remembering is a convention that fails. Add a drift check that regenerates into a temp file and diffs:

// scripts/check-types-drift.ts
import { execFileSync } from "node:child_process";
import { readFileSync } from "node:fs";

const committed = readFileSync("src/db/types.generated.ts", "utf8");
const fresh = execFileSync(
  "supabase",
  ["gen", "types", "typescript", "--local"],
  { encoding: "utf8" },
);

if (committed.trim() !== fresh.trim()) {
  console.error(
    "src/db/types.generated.ts is stale. Run `bun run db:reset && bun run db:types` and commit the result.",
  );
  process.exit(1);
}

console.log("database types match the local schema");

Wire it into whatever runs before a push. In an agent-driven repo, a PreToolUse hook on git commit that runs this is more reliable than a checklist in a CLAUDE.md, because it fails loudly at the moment the mistake is made.

A cheaper version, if you do not want to boot Docker on every commit: compare the number of migration files against a counter written into the types file header when it was last generated. It catches source number one, which is most of the problem.

Detecting dashboard drift

Source number three needs a different tool. supabase db diff compares a linked hosted project against your migration history:

bun run db:link --project-ref <project-ref>
bunx supabase db diff --linked

Empty output means the hosted schema is exactly what your migrations produce. Non-empty output means somebody edited the dashboard, and you now capture it:

bunx supabase db diff --linked -f captured_dashboard_changes
bun run db:reset      # confirm the captured migration replays cleanly
bun run db:types

Read the generated file before committing. db diff also picks up extension version bumps and supabase_admin grants that belong to the platform, not to you - delete those hunks.

Use the generated types, do not restate them

Types only stay honest if code actually depends on them:

import type { Database } from "@/db/types.generated";

type Note = Database["public"]["Tables"]["notes"]["Row"];
type NewNote = Database["public"]["Tables"]["notes"]["Insert"];

// The Supabase client carries the schema through every query.
const supabase = createClient<Database>(url, anonKey);
const { data } = await supabase.from("notes").select("id, body");
//      ^? { id: string; body: string }[] | null

If your application defines its own interface Note { ... } next to the generated one, drift will happen inside a single commit, never mind across weeks. Derive application types from the generated row type with Pick, Omit and helpers, and let the compiler tell you when a migration invalidated a component.

Summary

• The generated file is committed, never gitignored.
• Reset, then generate, then commit - alongside the migration that caused it.
• A drift check in verify or a pre-commit hook is what makes the rule real.
• supabase db diff --linked catches the schema changes that never went through a migration at all.