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

Duplicate (lunchflow_item_id, account_id) pairs exist in lun

Error message

Duplicate (lunchflow_item_id, account_id) pairs exist in lunchflow_accounts. Resolve duplicates before running this migration.

What it means

Raised by 20260219200006_scope_lunchflow_account_uniqueness_to_item (db/migrate/20260219200006_scope_lunchflow_account_uniqueness_to_item.rb:11) before adding a new per-item unique index on lunchflow_accounts (lunchflow_item_id, account_id). Identical shape to the Coinbase migration: no prior unique index existed, so duplicate (lunchflow_item_id, account_id) pairs in existing data would make add_index fail; a GROUP BY/HAVING pre-check aborts with this message instead. Raised as ActiveRecord::IrreversibleMigration from the up direction.

Source

Thrown at db/migrate/20260219200006_scope_lunchflow_account_uniqueness_to_item.rb:11

# frozen_string_literal: true

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

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

    add_index :lunchflow_accounts,
              [ :lunchflow_item_id, :account_id ],
              unique: true,
              name: "index_lunchflow_accounts_on_item_and_account_id",
              where: "account_id IS NOT NULL"
  end

  def down
    remove_index :lunchflow_accounts, name: "index_lunchflow_accounts_on_item_and_account_id", if_exists: true
  end
end

View on GitHub (pinned to e69894adb9)

Solutions

Find the pairs: SELECT lunchflow_item_id, account_id, COUNT(*) FROM lunchflow_accounts WHERE account_id IS NOT NULL GROUP BY lunchflow_item_id, account_id HAVING COUNT(*) > 1;
Delete the surplus rows (keep one per pair, e.g. the newest), then re-run rails db:migrate.
Verify afterward with the same GROUP BY query returning zero rows.
In multi-family setups, confirm duplicates are intra-family before deleting — the new index scopes per item, not per family.

Example fix

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

// after
DELETE FROM lunchflow_accounts a USING lunchflow_accounts b
WHERE a.id < b.id AND a.lunchflow_item_id = b.lunchflow_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 lunchflow_accounts WHERE account_id IS NOT NULL
  GROUP BY lunchflow_item_id, account_id HAVING COUNT(*) > 1 LIMIT 1
SQL
raise 'dedupe lunchflow_accounts first' if dupes.any?

Try / catch

begin
  ActiveRecord::Tasks::DatabaseTasks.migrate
rescue ActiveRecord::IrreversibleMigration => e
  # dedupe (lunchflow_item_id, account_id) pairs, then re-run
end

Prevention

Add the dupe check to a release script that runs before db:migrate.
Use find_or_create_by(lunchflow_item:, account:) in sync code to keep link rows unique.
After dedupe, re-run the GROUP BY query to confirm zero rows before retrying migrate.

When it happens

Trigger: Running rails db:migrate against a database where lunchflow_accounts contains more than one row with the same non-null (lunchflow_item_id, account_id) — typically duplicate rows created by repeated Lunchflow syncs before the constraint existed.

Common situations: Self-hosted databases upgraded across the 2026-02 timeframe; repeated sync jobs that upserted rather than deduped; staging clones of production containing the same duplicate links.

Related errors

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