The schema and its 0005 snapshot have drifted, so the migrations probe fails

[NathanTarbert]

The schema and its snapshot have drifted apart, so drizzle-kit generate writes a migration nobody asked for and the migrations job fails on the dirty tree. It's been failing on main since #87, and every branch cut from main picks it up — worth sorting out early since it's the kind of thing that trains people to read a red check as normal.

Run 32502636539 on 018d493: format/lint/types, build, tests and image all green, migrations red.

What drifted

Two fields moved in server/src/db/schema/core.ts in #87. meta/0005_snapshot.json was last written by #46, which merged before it, so the snapshot still records the older shape:

	core.ts at 018d493	meta/0005_snapshot.json	what a deployed database has
accounts.issuer	nullable (:92)	notNull: true	nullable
sso_providers.user_id FK	on delete set null (:169)	cascade	set null

bunx drizzle-kit generate --config=drizzle.config.ts in server/ therefore emits:

ALTER TABLE "sso_providers" DROP CONSTRAINT "sso_providers_user_id_users_id_fk";
--> statement-breakpoint
ALTER TABLE "accounts" ALTER COLUMN "issuer" DROP NOT NULL;
--> statement-breakpoint
ALTER TABLE "sso_providers" ADD CONSTRAINT "sso_providers_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;

The drift probe in ci.yml runs exactly that and checks whether the tree went dirty, which is the job working as intended.

The good news: the SQL chain is already correct

Worth stating separately, because it decides how safe the fix is. No deployed database is in the state the snapshot describes:

• accounts.issuer isn't in 0000_schema.sql at all. 0002_sign_in.sql:19 adds it nullable, 0003_backfill_account_issuer.sql fills it, and SET NOT NULL was never applied.
• 0004_identity_provider_outlives_its_registrar.sql already dropped and re-added the sso_providers foreign key with ON DELETE set null.

So every statement the generator wants to write is a no-op against a database built from 0000–0005: dropping a NOT NULL that was never applied, and re-landing a foreign key on the constraint it already has. Only the snapshot is out of step, and it describes a state no database is in.

Fix

Regenerate and commit the migration. It's three no-op statements, and the filename plus a comment should say so, so nobody later goes looking for the change it was written for. Editing meta/0005_snapshot.json back into agreement by hand is the alternative and avoids the pointless DDL, at the cost of hand-editing a generated file. Either way drizzle-kit generate needs to produce nothing afterwards, which is what the probe asserts.

Happy for someone to take this — it's small and self-contained.

Maybe worth a separate look

The drift probe caught this at the time and the merge went through anyway, so there may be a required-check setting to adjust. That's a repo setting rather than a code change, so probably its own conversation.

Found while looking at something else on #88, where @zopeVaibhav spotted the drift first and suggested it get its own issue.

[davidmckayv]

Fixed on main by #90, which carried the regenerated snapshot as migration 0006.

Confirmed just now on 37b576c: bun run db:generate answers "No schema changes, nothing to migrate", drizzle-kit check is happy, and the journal agrees with the directory. The migrations job is green on every PR opened since.

Your diagnosis in #93 was the better-written one and it is right in a detail mine glossed: the DDL is a genuine no-op, because SET NOT NULL was never applied anywhere and 0004 had already put the foreign key on set null. Anybody reading drizzle/0006 later and wondering what schema change it was written for should read #93.

Also worth recording that the underlying cause was two branches renumbering 0004 independently: #87 replaced main's 0004 and #46's 0005 snapshot had been generated against the one it replaced. Two PRs each correct on their own, disagreeing once both had landed.