{"record":{"id":"b9c2d0bb3851fc2d","repo":"we-promise/sure","slug":"duplicate-coinbase-item-id-account-id-pairs-exi","errorCode":null,"errorMessage":"Duplicate (coinbase_item_id, account_id) pairs exist in coinbase_accounts. Resolve duplicates before running this migration.","messagePattern":"Duplicate \\(coinbase_item_id, account_id\\) pairs exist in coinbase_accounts\\. Resolve duplicates before running this migration\\.","errorType":"exception","errorClass":"ActiveRecord::Migration::IrreversibleMigration","httpStatus":null,"severity":"error","filePath":"db/migrate/20260219200004_scope_coinbase_account_uniqueness_to_item.rb","lineNumber":11,"sourceCode":"# frozen_string_literal: true\n\n# NEW constraint: add per-item unique index on coinbase_accounts. Unlike Plaid/Snaptrade,\n# there was no prior unique index—this can fail if existing data has duplicate\n# (coinbase_item_id, account_id) pairs. See: https://github.com/we-promise/sure/issues/740\nclass ScopeCoinbaseAccountUniquenessToItem < ActiveRecord::Migration[7.2]\n  def up\n    return if index_exists?(:coinbase_accounts, [ :coinbase_item_id, :account_id ], unique: true, name: \"index_coinbase_accounts_on_item_and_account_id\")\n\n    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?\n      raise ActiveRecord::Migration::IrreversibleMigration,\n            \"Duplicate (coinbase_item_id, account_id) pairs exist in coinbase_accounts. Resolve duplicates before running this migration.\"\n    end\n\n    add_index :coinbase_accounts,\n              [ :coinbase_item_id, :account_id ],\n              unique: true,\n              name: \"index_coinbase_accounts_on_item_and_account_id\",\n              where: \"account_id IS NOT NULL\"\n  end\n\n  def down\n    remove_index :coinbase_accounts, name: \"index_coinbase_accounts_on_item_and_account_id\", if_exists: true\n  end\nend\n","sourceCodeStart":1,"sourceCodeEnd":26,"githubUrl":"https://github.com/we-promise/sure/blob/e69894adb92547273377398c15f45c979cd9416a/db/migrate/20260219200004_scope_coinbase_account_uniqueness_to_item.rb#L1-L26","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"exampleFix":"// before\nrails db:migrate # Duplicate (coinbase_item_id, account_id) pairs exist...\n\n// after\n-- dedupe, keep newest row per pair:\nDELETE FROM coinbase_accounts a USING coinbase_accounts b\nWHERE a.id < b.id AND a.coinbase_item_id = b.coinbase_item_id AND a.account_id = b.account_id;\nrails db:migrate","handlingStrategy":"validation","validationCode":"dupes = ActiveRecord::Base.connection.execute(<<~SQL).to_a\n  SELECT 1 FROM coinbase_accounts WHERE account_id IS NOT NULL\n  GROUP BY coinbase_item_id, account_id HAVING COUNT(*) > 1 LIMIT 1\nSQL\nraise 'dedupe coinbase_accounts first' if dupes.any?","typeGuard":null,"tryCatchPattern":"begin\n  ActiveRecord::MigrationContext.new(Rails.root.join('db/migrate')).migrate\nrescue ActiveRecord::IrreversibleMigration => e\n  # e.message names the table + pair columns; dedupe then re-run db:migrate\nend","preventionTips":["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."],"tags":["rails","migration","unique-index","coinbase","duplicate-data"],"backgroundTag":"unique-index-migration-conflict","analyzedSha":"e69894adb92547273377398c15f45c979cd9416a","analyzedAt":"2026-08-21T18:22:41.165Z","schemaVersion":2},"datasetVersion":"2026-08-21T23:17:16.201Z"}