we-promise/sure · error · ActiveRecord::IrreversibleMigration

Cannot rollback: cross-item duplicates exist in snaptrade_ac

Error message

Cannot rollback: cross-item duplicates exist in snaptrade_accounts. Remove duplicates first.

What it means

Raised as ActiveRecord::IrreversibleMigration in the down method of 20260219200003_scope_snaptrade_account_uniqueness_to_item (db/migrate/20260219200003_scope_snaptrade_accounts_uniqueness_to_item.rb:20). The up direction relaxes account uniqueness from global (per snaptrade_account_id) to per-item (snaptrade_item_id + snaptrade_account_id). Rolling back reinstates the stricter global unique index, which would fail if any snaptrade_account_id now belongs to more than one item — so the migration pre-checks with a GROUP BY/HAVING query and refuses to run rather than crash mid-DDL.

Source

Thrown at db/migrate/20260219200003_scope_snaptrade_account_uniqueness_to_item.rb:20

# Scope snaptrade_accounts uniqueness to snaptrade_item so the same external
# account can be linked in multiple families. See: https://github.com/we-promise/sure/issues/740
class ScopeSnaptradeAccountUniquenessToItem < ActiveRecord::Migration[7.2]
  def up
    remove_index :snaptrade_accounts, name: "index_snaptrade_accounts_on_snaptrade_account_id", if_exists: true

    unless index_exists?(:snaptrade_accounts, [ :snaptrade_item_id, :snaptrade_account_id ], unique: true, name: "index_snaptrade_accounts_on_item_and_snaptrade_account_id")
      add_index :snaptrade_accounts,
                [ :snaptrade_item_id, :snaptrade_account_id ],
                unique: true,
                name: "index_snaptrade_accounts_on_item_and_snaptrade_account_id",
                where: "snaptrade_account_id IS NOT NULL"
    end
  end

  def down
    if execute("SELECT 1 FROM snaptrade_accounts WHERE snaptrade_account_id IS NOT NULL GROUP BY snaptrade_account_id HAVING COUNT(DISTINCT snaptrade_item_id) > 1 LIMIT 1").any?
      raise ActiveRecord::IrreversibleMigration,
            "Cannot rollback: cross-item duplicates exist in snaptrade_accounts. Remove duplicates first."
    end

    remove_index :snaptrade_accounts, name: "index_snaptrade_accounts_on_item_and_snaptrade_account_id", if_exists: true
    unless index_exists?(:snaptrade_accounts, :snaptrade_account_id, name: "index_snaptrade_accounts_on_snaptrade_account_id")
      add_index :snaptrade_accounts, :snaptrade_account_id,
                name: "index_snaptrade_accounts_on_snaptrade_account_id",
                unique: true,
                where: "snaptrade_account_id IS NOT NULL"
    end
  end
end

View on GitHub (pinned to e69894adb9)

Solutions

Find offenders with: SELECT snaptrade_account_id, COUNT(DISTINCT snaptrade_item_id) FROM snaptrade_accounts WHERE snaptrade_account_id IS NOT NULL GROUP BY snaptrade_account_id HAVING COUNT(DISTINCT snaptrade_item_id) > 1; and decide which link to keep.
Delete or re-map the duplicate rows (keep one item link per snaptrade_account_id), then re-run the rollback.
If the relaxed constraint is correct going forward, don't roll back — write a new forward migration instead (redo/fix-forward).
On disposable environments, rails db:drop db:create db:migrate avoids the data negotiation entirely.

Example fix

// before
rails db:rollback # IrreversibleMigration: cross-item duplicates exist

// after
-- keep one link per snaptrade_account_id, then:
rails db:rollback

Defensive patterns

Strategy: validation

Validate before calling

dupes = ActiveRecord::Base.connection.execute(<<~SQL).to_a
  SELECT 1 FROM snaptrade_accounts WHERE snaptrade_account_id IS NOT NULL
  GROUP BY snaptrade_account_id HAVING COUNT(DISTINCT snaptrade_item_id) > 1 LIMIT 1
SQL
abort 'dedupe before rollback' if dupes.any?

Try / catch

begin
  ActiveRecord::Tasks::DatabaseTasks.rollback
rescue ActiveRecord::IrreversibleMigration => e
  puts "Blocked: #{e.message} — resolve data, or fix forward with a new migration"
end

Prevention

Before any rollback past a constraint-relaxing migration, run the same HAVING query the migration uses.
Prefer writing new forward migrations over rolling back ones that tighten/relax uniqueness.
In CI, test both up and down of data-dependent migrations against seeded duplicate data.

When it happens

Trigger: Running rails db:rollback (or db:migrate:down) for this migration on a database where, after the up ran, the same snaptrade_account_id was linked under two different snaptrade_items — legitimately possible under the new per-item rule.

Common situations: Re-linking a SnapTrade account to a second item after deploy, then attempting a rollback during a staging repro or down/up re-run; environments where data evolved under the relaxed constraint and someone tries to restore the old schema.

Related errors

AI-assisted analysis of we-promise/sure@e69894adb9 (2026-08-21). Data as JSON: /api/errors/fd274f1ed0b3d15b. Report an issue: GitHub.