{"record":{"id":"77ddc77be007eac1","repo":"we-promise/sure","slug":"duplicate-lunchflow-item-id-account-id-pairs-ex","errorCode":null,"errorMessage":"Duplicate (lunchflow_item_id, account_id) pairs exist in lunchflow_accounts. Resolve duplicates before running this migration.","messagePattern":"Duplicate \\(lunchflow_item_id, account_id\\) pairs exist in lunchflow_accounts\\. Resolve duplicates before running this migration\\.","errorType":"exception","errorClass":"ActiveRecord::Migration::IrreversibleMigration","httpStatus":null,"severity":"error","filePath":"db/migrate/20260219200006_scope_lunchflow_account_uniqueness_to_item.rb","lineNumber":11,"sourceCode":"# frozen_string_literal: true\n\n# NEW constraint: add per-item unique index on lunchflow_accounts. Unlike Plaid/Snaptrade,\n# there was no prior unique index—this can fail if existing data has duplicate\n# (lunchflow_item_id, account_id) pairs. See: https://github.com/we-promise/sure/issues/740\nclass ScopeLunchflowAccountUniquenessToItem < ActiveRecord::Migration[7.2]\n  def up\n    return if index_exists?(:lunchflow_accounts, [ :lunchflow_item_id, :account_id ], unique: true, name: \"index_lunchflow_accounts_on_item_and_account_id\")\n\n    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?\n      raise ActiveRecord::Migration::IrreversibleMigration,\n            \"Duplicate (lunchflow_item_id, account_id) pairs exist in lunchflow_accounts. Resolve duplicates before running this migration.\"\n    end\n\n    add_index :lunchflow_accounts,\n              [ :lunchflow_item_id, :account_id ],\n              unique: true,\n              name: \"index_lunchflow_accounts_on_item_and_account_id\",\n              where: \"account_id IS NOT NULL\"\n  end\n\n  def down\n    remove_index :lunchflow_accounts, name: \"index_lunchflow_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/20260219200006_scope_lunchflow_account_uniqueness_to_item.rb#L1-L26","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"exampleFix":"// before\nrails db:migrate # Duplicate (lunchflow_item_id, account_id) pairs exist...\n\n// after\nDELETE FROM lunchflow_accounts a USING lunchflow_accounts b\nWHERE a.id < b.id AND a.lunchflow_item_id = b.lunchflow_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 lunchflow_accounts WHERE account_id IS NOT NULL\n  GROUP BY lunchflow_item_id, account_id HAVING COUNT(*) > 1 LIMIT 1\nSQL\nraise 'dedupe lunchflow_accounts first' if dupes.any?","typeGuard":null,"tryCatchPattern":"begin\n  ActiveRecord::Tasks::DatabaseTasks.migrate\nrescue ActiveRecord::IrreversibleMigration => e\n  # dedupe (lunchflow_item_id, account_id) pairs, then re-run\nend","preventionTips":["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."],"tags":["rails","migration","unique-index","lunchflow","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"}