# Stopping Supabase generated types from drifting out of the schema

> Generated database types are only true at the moment they were generated. Commit them, regenerate them in the same commit as the migration, and check them in verify.

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.

```sh
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:

```json
"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:

```sh
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:

```ts
// 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:

```sh
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:

```sh
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:

```ts
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.

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com