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

Duplicate (coinbase_item_id, account_id) pairs exist in coin

Error message

Duplicate (coinbase_item_id, account_id) pairs exist in coinbase_accounts. Resolve duplicates before running this migration.

What it means

Raised by 20260219200004_scope_coinbase_account_uniqueness_to_item (db/migrate/20260219200004_scope_coinbase_account_uniqueness_to_item.rb:11) before adding a NEW per-item unique index on coinbase_accounts (coinbase_item_id, account_id). Unlike Plaid/Snaptrade there was no prior unique index, so pre-existing rows may contain duplicate (coinbase_item_id, account_id) pairs; the guard query (GROUP BY ... HAVING COUNT(*) > 1) detects that and aborts instead of letting add_index fail with a cryptic Postgres unique-constraint error. Note the class raised is ActiveRecord::IrreversibleMigration even though this fires in up — it is a data-conflict abort.

Source

Thrown at db/migrate/20260219200004_scope_coinbase_account_uniqueness_to_item.rb:11

# frozen_string_literal: true

# NEW constraint: add per-item unique index on coinbase_accounts. Unlike Plaid/Snaptrade,
# there was no prior unique index—this can fail if existing data has duplicate
# (coinbase_item_id, account_id) pairs. See: https://github.com/we-promise/sure/issues/740
class ScopeCoinbaseAccountUniquenessToItem < ActiveRecord::Migration[7.2]
  def up
    return if index_exists?(:coinbase_accounts, [ :coinbase_item_id, :account_id ], unique: true, name: "index_coinbase_accounts_on_item_and_account_id")

    if execute("SELECT 1 FROM coinbase_accounts WHERE account_id IS NOT NULL GROUP BY coinbase_item_id, account_id HAVING COUNT(*) > 1 LIMIT 1").any?
      raise ActiveRecord::Migration::IrreversibleMigration,
            "Duplicate (coinbase_item_id, account_id) pairs exist in coinbase_accounts. Resolve duplicates before running this migration."
    end

    add_index :coinbase_accounts,
              [ :coinbase_item_id, :account_id ],
              unique: true,
              name: "index_coinbase_accounts_on_item_and_account_id",
              where: "account_id IS NOT NULL"
  end

  def down
    remove_index :coinbase_accounts, name: "index_coinbase_accounts_on_item_and_account_id", if_exists: true
  end
end

View on GitHub (pinned to e69894adb9)

Solutions

Locate duplicates: SELECT coinbase_item_id, account_id, COUNT(*) FROM coinbase_accounts WHERE account_id IS NOT NULL GROUP BY coinbase_item_id, account_id HAVING COUNT(*) > 1;
Keep the canonical row (usually the newest or the one with a linked account_provider) and delete the rest, then re-run rails db:migrate.
If unsure which to keep, compare created_at and any associated account_providers before deleting.
Do not hand-edit the migration to skip the check — you would just move the failure into add_index with a worse message.

Example fix

// before
rails db:migrate # Duplicate (coinbase_item_id, account_id) pairs exist...

// after
-- dedupe, keep newest row per pair:
DELETE FROM coinbase_accounts a USING coinbase_accounts b
WHERE a.id < b.id AND a.coinbase_item_id = b.coinbase_item_id AND a.account_id = b.account_id;
rails db:migrate

Defensive patterns

Strategy: validation

Validate before calling

dupes = ActiveRecord::Base.connection.execute(<<~SQL).to_a
  SELECT 1 FROM coinbase_accounts WHERE account_id IS NOT NULL
  GROUP BY coinbase_item_id, account_id HAVING COUNT(*) > 1 LIMIT 1
SQL
raise 'dedupe coinbase_accounts first' if dupes.any?

Try / catch

begin
  ActiveRecord::MigrationContext.new(Rails.root.join('db/migrate')).migrate
rescue ActiveRecord::IrreversibleMigration => e
  # e.message names the table + pair columns; dedupe then re-run db:migrate
end

Prevention

Run the duplicate-detection query in a pre-deploy check for any migration adding a unique index.
Make provider sync upsert (find_or_create_by) on link tables so duplicates never form.
The migration is idempotent — fixing data and re-running migrate is always safe.

When it happens

Trigger: Running rails db:migrate on a database whose coinbase_accounts table already contains two rows with the same (coinbase_item_id, account_id) where account_id is not null — e.g. repeated imports or provider syncs inserting a second link row before any uniqueness existed.

Common situations: Long-running self-hosted instances accumulating duplicate link rows over time; staging restored from production hitting the same dupes; the migration is idempotent (returns early if index exists) so it only fires on the first attempt.

Related errors

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