{"record":{"id":"fd274f1ed0b3d15b","repo":"we-promise/sure","slug":"cannot-rollback-cross-item-duplicates-exist-in-sn","errorCode":null,"errorMessage":"Cannot rollback: cross-item duplicates exist in snaptrade_accounts. Remove duplicates first.","messagePattern":"Cannot rollback: cross-item duplicates exist in snaptrade_accounts\\. Remove duplicates first\\.","errorType":"exception","errorClass":"ActiveRecord::IrreversibleMigration","httpStatus":null,"severity":"error","filePath":"db/migrate/20260219200003_scope_snaptrade_account_uniqueness_to_item.rb","lineNumber":20,"sourceCode":"\n# Scope snaptrade_accounts uniqueness to snaptrade_item so the same external\n# account can be linked in multiple families. See: https://github.com/we-promise/sure/issues/740\nclass ScopeSnaptradeAccountUniquenessToItem < ActiveRecord::Migration[7.2]\n  def up\n    remove_index :snaptrade_accounts, name: \"index_snaptrade_accounts_on_snaptrade_account_id\", if_exists: true\n\n    unless index_exists?(:snaptrade_accounts, [ :snaptrade_item_id, :snaptrade_account_id ], unique: true, name: \"index_snaptrade_accounts_on_item_and_snaptrade_account_id\")\n      add_index :snaptrade_accounts,\n                [ :snaptrade_item_id, :snaptrade_account_id ],\n                unique: true,\n                name: \"index_snaptrade_accounts_on_item_and_snaptrade_account_id\",\n                where: \"snaptrade_account_id IS NOT NULL\"\n    end\n  end\n\n  def down\n    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?\n      raise ActiveRecord::IrreversibleMigration,\n            \"Cannot rollback: cross-item duplicates exist in snaptrade_accounts. Remove duplicates first.\"\n    end\n\n    remove_index :snaptrade_accounts, name: \"index_snaptrade_accounts_on_item_and_snaptrade_account_id\", if_exists: true\n    unless index_exists?(:snaptrade_accounts, :snaptrade_account_id, name: \"index_snaptrade_accounts_on_snaptrade_account_id\")\n      add_index :snaptrade_accounts, :snaptrade_account_id,\n                name: \"index_snaptrade_accounts_on_snaptrade_account_id\",\n                unique: true,\n                where: \"snaptrade_account_id IS NOT NULL\"\n    end\n  end\nend\n","sourceCodeStart":2,"sourceCodeEnd":33,"githubUrl":"https://github.com/we-promise/sure/blob/e69894adb92547273377398c15f45c979cd9416a/db/migrate/20260219200003_scope_snaptrade_account_uniqueness_to_item.rb#L2-L33","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"exampleFix":"// before\nrails db:rollback # IrreversibleMigration: cross-item duplicates exist\n\n// after\n-- keep one link per snaptrade_account_id, then:\nrails db:rollback","handlingStrategy":"validation","validationCode":"dupes = ActiveRecord::Base.connection.execute(<<~SQL).to_a\n  SELECT 1 FROM snaptrade_accounts WHERE snaptrade_account_id IS NOT NULL\n  GROUP BY snaptrade_account_id HAVING COUNT(DISTINCT snaptrade_item_id) > 1 LIMIT 1\nSQL\nabort 'dedupe before rollback' if dupes.any?","typeGuard":null,"tryCatchPattern":"begin\n  ActiveRecord::Tasks::DatabaseTasks.rollback\nrescue ActiveRecord::IrreversibleMigration => e\n  puts \"Blocked: #{e.message} — resolve data, or fix forward with a new migration\"\nend","preventionTips":["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."],"tags":["rails","migration","rollback","unique-index","snaptrade"],"backgroundTag":"rollback-blocked-by-duplicate-data","analyzedSha":"e69894adb92547273377398c15f45c979cd9416a","analyzedAt":"2026-08-21T18:22:41.165Z","schemaVersion":2},"datasetVersion":"2026-08-21T23:17:16.201Z"}