FIX: drafts are unique by draft_key and user_id

FIX: drafts are unique by draft_key and user_id

Previously our index was non unique, causing situations where a user could have multiple drafts stored in the table for the same exact entity.

This does not properly reflect reality and needed to change as in certain cases duplicate drafts could be created causing internal data inconsistency

diff --git a/app/models/draft.rb b/app/models/draft.rb
index 2bbc3bcaf6..4f7a03711f 100644
--- a/app/models/draft.rb
+++ b/app/models/draft.rb
@@ -282,5 +282,5 @@ end
 #
 # Indexes
 #
-#  index_drafts_on_user_id_and_draft_key  (user_id,draft_key)
+#  index_drafts_on_user_id_and_draft_key  (user_id,draft_key) UNIQUE
 #
diff --git a/db/migrate/20191108000414_add_unique_index_to_drafts.rb b/db/migrate/20191108000414_add_unique_index_to_drafts.rb
new file mode 100644
index 0000000000..f50d1b7c49
--- /dev/null
+++ b/db/migrate/20191108000414_add_unique_index_to_drafts.rb
@@ -0,0 +1,27 @@
+# frozen_string_literal: true
+
+class AddUniqueIndexToDrafts < ActiveRecord::Migration[6.0]
+  def up
+
+    execute <<~SQL
+      DELETE FROM drafts d1
+      USING (
+        SELECT MAX(id) as id, draft_key, user_id
+        FROM drafts
+        GROUP BY draft_key, user_id
+        HAVING COUNT(*) > 1
+      ) d2
+      WHERE
+        d1.draft_key = d2.draft_key AND
+        d1.user_id = d2.user_id AND
+        d1.id <> d2.id
+    SQL
+
+    remove_index :drafts, [:user_id, :draft_key]
+    add_index :drafts, [:user_id, :draft_key], unique: true
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration
+  end
+end

GitHub sha: deec2cf5

1 Like