Failed migration was still applied

[jbojcic1]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I am using branching and I have a persistent branch called next and I had another feature branch. The feature branch had one new migration file that was doing bunch of stuff like adding new db table, updating existing tables and db function, adding new constraints, and so on. When I merged the feature branch to next, migration in the Supabase dashboard is shown as failed but it seems like it was applied because I see the table added by the migration now and I also see the migration added to schema_migrations table.

Btw the migration was supposed to fail because existing data violates the newly added constraint.

Migration looks something like:

create table wallet.cashu_proofs (
  "id" uuid primary key default gen_random_uuid(),
  "user_id" uuid not null references wallet.users (id) on delete cascade,
  ...
);

...

alter table wallet.cashu_send_swaps add column requires_input_proofs_swap boolean generated always as (amount_to_send != input_amount) stored;

alter table wallet.cashu_send_swaps add constraint cashu_send_swaps_keyset_required_check
  check (
    (requires_input_proofs_swap = false) or
    (requires_input_proofs_swap = true and keyset_id is not null and keyset_counter is not null)
  );

...

The full migration file can be seen here and the error I see in the Supabase dashboard is:

Applying migration 20251119133559_cashu_proofs_changes.sql...
NOTICE (00000): type "wallet.cashu_proof_input" does not exist, skipping
NOTICE (00000): type "wallet.add_cashu_proofs_and_update_account_result" does not exist, skipping
NOTICE (00000): index "cashu_send_quotes_quote_id_key" does not exist, skipping
NOTICE (00000): type "wallet.complete_cashu_send_quote_result" does not exist, skipping
NOTICE (00000): type "wallet.expire_cashu_send_quote_result" does not exist, skipping
NOTICE (00000): type "wallet.fail_cashu_send_quote_result" does not exist, skipping
2025/11/24 21:26:50 ERROR: check constraint "cashu_send_swaps_keyset_required_check" of relation "cashu_send_swaps" is violated by some row (SQLSTATE 23514)
At statement: 99
-- Add constraint: keyset_id and keyset_counter are required when requires_input_proofs_swap is true
alter table wallet.cashu_send_swaps add constraint cashu_send_swaps_keyset_required_check
  check (
    (requires_input_proofs_swap = false) or
    (requires_input_proofs_swap = true and keyset_id is not null and keyset_counter is not null)
  )

To Reproduce

Don't know how to reproduce. I tried to reproduce locally but didn't manage to.

Expected behavior

Since the migration was adding new constraint and existing data didn't satisfy that constraint, the migration should have failed and rolled back.

Screenshots

Migrations:

Proof that migration seems to have been applied:

System information

Additional context

[jbojcic1]

Btw I asked about this in Discord already here

[jbojcic1]

I was thinking this might be something with the constraint and/or generated column so I tried to reproduce on my local database. I created one migration with this content:

create table wallet.testing (
  "id" serial primary key,
  "created_at" timestamp with time zone not null default now(),
  "is_even" boolean generated always as (id % 2 = 0) stored,
  "even_description" text
);

insert into wallet.testing (created_at, even_description) values
  (now() - interval '2 day', null), -- id: 1
  (now() - interval '1 day', 'Even description for id 2'),  -- id: 2
  (now(), null),  -- id: 3
  (now() + interval '1 day', null);  -- id: 4 (even id that doesn't have the description)

create index testing_created_at_idx on wallet.testing (created_at);

Then another one with:

alter table wallet.testing
  add constraint testing_even_description_required
  check (not is_even or even_description is not null);

create table wallet.testing_2 (
  "id" serial primary key,
  "created_at" timestamp with time zone not null default now(),
);

Then I reset the db with supabase db reset to apply the migrations. The first migration executed fine and the second migration has failed as expected because row 4 violates the constraint and it was rolled back as I expected (table testing_2 wasn't created). I also then made a branch on Supabase to test it, and there too the migration two was rolled back after failing. So this definitely seems like Supabase does intend to run the migration file as transaction but there was some weird bug when it wasn't rolled back for me in the example explained above.

Atm it is fine because we are not live now and we can even reset the db but I need to make sure I understand why it happened before we go live soon.

[Adityakk9031]

.take

[Hallidayo]

Transferred over from the supabase/supabase repo

[sweatybridge]

I see the table added by the migration now and I also see the migration added to schema_migrations table.

That is very strange indeed. Are you saying that your migration was applied partially? ie. the table was created but it's missing the constraint?

[jbojcic1]

That is very strange indeed. Are you saying that your migration was applied partially? ie. the table was created but it's missing the constraint?

@sweatybridge that's what I initially thought when I wrote this issue but then I saw that the constraint is there too.

So seems like it was completely applied and constraint check was somehow deferred after entire migration has executed, and when it then performed the check, it failed because of the existing data which caused Supabase dashboard to show the entire migration as failed. Not sure if that makes sense or is even possible

Screenshot:

[sweatybridge]

So seems like it was completely applied and constraint check was somehow deferred after entire migration has executed, and when it then performed the check, it failed because of the existing data which caused Supabase dashboard to show the entire migration as failed.

It sounds possible indeed. In that case, the error will be scoped to one migration run. I think you can fix it by pushing another commit to trigger a new run.

[jbojcic1]

So seems like it was completely applied and constraint check was somehow deferred after entire migration has executed, and when it then performed the check, it failed because of the existing data which caused Supabase dashboard to show the entire migration as failed.

It sounds possible indeed. In that case, the error will be scoped to one migration run. I think you can fix it by pushing another commit to trigger a new run.

@sweatybridge so generate new empty migration and push that?