Repository: keephq/keep
Pull request: fix: Add migration to remove and prevent multiple enrichments per fingerprint
<!-- 
Thanks for creating this pull request 🤗

Please make sure that the pull request is limited to one type (docs, feature, etc.) and keep it as small as possible. You can open multiple prs instead of opening a huge one.
-->

<!-- If this pull request closes an issue, please mention the issue number below -->
Closes #3986,#3978

## 📑 Description
<!-- Add a brief description of the pr -->

<!-- You can also choose to add a list of changes and if they have been completed or not by using the markdown to-do list syntax
- [ ] Not Completed
- [x] Completed
-->

## ✅ Checks
<!-- Make sure your pr passes the CI checks and do check the following fields as needed - -->
- [ ] My pull request adheres to the code style of this project
- [ ] My code requires changes to the documentation
- [ ] I have updated the documentation as required
- [ ] All the tests have passed

## ℹ Additional Information
<!-- Any additional information like breaking changes, dependencies added, screenshots, comparisons between new and old behavior, etc. -->


Problem statement:
[🐛 Bug]: Multiple enrichments per one alert_fingerprint in alertenrichment table
**Describe the bug**
This leads to unexpected results during Alerts querying

**Expected behavior**
One enrichment per fingerprint

**Screenshots**
If applicable, add screenshots to help explain your problem.

**Additional context**
We need to have migration that removes duplicates from alertenrichment and adds unique constraint alert_fingerprint+tenant_id

Hints from issue discussion:

Commit message: Merge branch 'main' into investigate-ghost-alerts-problem

Patch to review:
diff --git a/keep/api/models/db/migrations/versions/2025-03-11-16-54_16309df224d1.py b/keep/api/models/db/migrations/versions/2025-03-11-16-54_16309df224d1.py
new file mode 100644
index 0000000000..ef59647039
--- /dev/null
+++ b/keep/api/models/db/migrations/versions/2025-03-11-16-54_16309df224d1.py
@@ -0,0 +1,193 @@
+"""Add_unique_constraint_for_alert_fingerprint_and_tenant_id
+
+Revision ID: 16309df224d1
+Revises: 0b80bda47ee2
+Create Date: 2025-03-11 16:54:14.972144
+
+"""
+
+import sqlalchemy as sa
+import sqlmodel
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision = "16309df224d1"
+down_revision = "0b80bda47ee2"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    conn = op.get_bind()
+    dialect = conn.dialect.name
+
+    if dialect == "mysql":
+        try:
+            op.drop_constraint("alert_fingerprint", "alertenrichment", type_="unique")
+        except Exception:
+            # ignore because this constraint may not exist in prod
+            pass
+
+        op.execute(
+            """
+                WITH duplicates AS (
+                    SELECT id,
+                        ROW_NUMBER() OVER (
+                            PARTITION BY tenant_id, alert_fingerprint 
+                            ORDER BY timestamp DESC
+                        ) AS rn
+                    FROM alertenrichment
+                )
+                DELETE FROM alertenrichment
+                WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
+            """
+        )
+
+        with op.batch_alter_table("alertenrichment") as batch_op:
+            batch_op.create_unique_constraint(
+                "uc_alertenrichment_tenant_fingerprint",
+                ["tenant_id", "alert_fingerprint"],
+            )
+    elif dialect == "postgresql":
+        constraint_exists = conn.execute(
+            """
+                SELECT conname 
+                FROM pg_constraint 
+                WHERE conrelid = 'alertenrichment'::regclass 
+                AND conname = 'alert_fingerprint';
+            """
+        ).fetchone()
+        with op.batch_alter_table("alertenrichment") as batch_op:
+            if constraint_exists:
+                batch_op.drop_constraint("alert_fingerprint", type_="unique")
+
+            batch_op.execute(
+                """
+                    WITH duplicates AS (
+                        SELECT id,
+                            ROW_NUMBER() OVER (
+                                PARTITION BY tenant_id, alert_fingerprint 
+                                ORDER BY timestamp DESC
+                            ) AS rn
+                        FROM alertenrichment
+                    )
+                    DELETE FROM alertenrichment
+                    WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
+                """
+            )
+            batch_op.create_unique_constraint(
+                "uc_alertenrichment_tenant_fingerprint",
+                ["tenant_id", "alert_fingerprint"],
+            )
+    elif dialect == "sqlite":
+        op.create_table(
+            "alertenrichment_new",
+            sa.Column("enrichments", sa.JSON(), nullable=True),
+            sa.Column("id", sqlmodel.sql.sqltypes.types.Uuid(), nullable=False),
+            sa.Column("tenant_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
+            sa.Column("timestamp", sa.DateTime(), nullable=False),
+            sa.Column(
+                "alert_fingerprint", sqlmodel.sql.sqltypes.AutoString(), nullable=False
+            ),
+            sa.ForeignKeyConstraint(
+                ["tenant_id"],
+                ["tenant.id"],
+            ),
+            sa.PrimaryKeyConstraint("id"),
+        )
+
+        # Copy existing data
+        op.execute(
+            """
+                INSERT INTO alertenrichment_new (id, tenant_id, alert_fingerprint, timestamp, enrichments)
+                SELECT id, tenant_id, alert_fingerprint, timestamp, enrichments FROM alertenrichment;
+            """
+        )
+        op.execute(
+            """
+                WITH duplicates AS (
+                    SELECT id,
+                        ROW_NUMBER() OVER (
+                            PARTITION BY tenant_id, alert_fingerprint 
+                            ORDER BY timestamp DESC
+                        ) AS rn
+                    FROM alertenrichment_new
+                )
+                DELETE FROM alertenrichment_new
+                WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
+            """
+        )
+        with op.batch_alter_table("alertenrichment_new") as batch_op:
+            batch_op.create_unique_constraint(
+                "uc_alertenrichment_tenant_fingerprint",
+                ["tenant_id", "alert_fingerprint"],
+            )
+
+        op.execute("DROP TABLE alertenrichment;")
+        op.execute("ALTER TABLE alertenrichment_new RENAME TO alertenrichment;")
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    conn = op.get_bind()
+    dialect = conn.dialect.name
+
+    if dialect == "mysql":
+        op.execute(
+            "ALTER TABLE alertenrichment DROP FOREIGN KEY alertenrichment_ibfk_1;"
+        )
+
+        op.drop_constraint(
+            "uc_alertenrichment_tenant_fingerprint", "alertenrichment", type_="unique"
+        )
+        op.execute(
+            """
+                ALTER TABLE alertenrichment
+                ADD CONSTRAINT alertenrichment_ibfk_1
+                FOREIGN KEY (tenant_id) 
+                REFERENCES tenant(id)
+                ON DELETE CASCADE ON UPDATE CASCADE;
+            """
+        )
+        op.create_unique_constraint(
+            "alert_fingerprint", "alertenrichment", ["alert_fingerprint"]
+        )
+
+    elif dialect == "postgresql":
+        op.drop_constraint(
+            "uc_alertenrichment_tenant_fingerprint", "alertenrichment", type_="unique"
+        )
+        op.create_unique_constraint(
+            "alert_fingerprint", "alertenrichment", ["alert_fingerprint"]
+        )
+
+    elif dialect == "sqlite":
+        op.create_table(
+            "alertenrichment_new",
+            sa.Column("enrichments", sa.JSON(), nullable=True),
+            sa.Column("id", sqlmodel.sql.sqltypes.types.Uuid(), nullable=False),
+            sa.Column("tenant_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
+            sa.Column("timestamp", sa.DateTime(), nullable=False),
+            sa.Column(
+                "alert_fingerprint", sqlmodel.sql.sqltypes.AutoString(), nullable=False
+            ),
+            sa.ForeignKeyConstraint(
+                ["tenant_id"],
+                ["tenant.id"],
+            ),
+            sa.PrimaryKeyConstraint("id"),
+            sa.UniqueConstraint("alert_fingerprint"),
+        )
+
+        op.execute(
+            """
+                INSERT INTO alertenrichment_new (id, tenant_id, alert_fingerprint, timestamp, enrichments)
+                SELECT id, tenant_id, alert_fingerprint, timestamp, enrichments FROM alertenrichment;
+            """
+        )
+
+        op.execute("DROP TABLE alertenrichment;")
+        op.execute("ALTER TABLE alertenrichment_new RENAME TO alertenrichment;")
+    # ### end Alembic commands ###


Code review comments:
diff_hunk: @@ -0,0 +1,193 @@
+"""Add_unique_constraint_for_alert_fingerprint_and_tenant_id
+
+Revision ID: 16309df224d1
+Revises: 0b80bda47ee2
+Create Date: 2025-03-11 16:54:14.972144
+
+"""
+
+import sqlalchemy as sa
+import sqlmodel
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision = "16309df224d1"
+down_revision = "0b80bda47ee2"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    conn = op.get_bind()
+    dialect = conn.dialect.name
+
+    if dialect == "mysql":
+        try:
+            op.drop_constraint("alert_fingerprint", "alertenrichment", type_="unique")
+        except Exception:
+            # ignore because this constraint may not exist in prod
+            pass
+
+        op.execute(
+            """
+                WITH duplicates AS (
+                    SELECT id,
+                        ROW_NUMBER() OVER (
+                            PARTITION BY tenant_id, alert_fingerprint 
+                            ORDER BY timestamp DESC
+                        ) AS rn
+                    FROM alertenrichment
+                )
+                DELETE FROM alertenrichment
+                WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
+            """
+        )
+
+        with op.batch_alter_table("alertenrichment") as batch_op:
+            batch_op.create_unique_constraint(
+                "uc_alertenrichment_tenant_fingerprint",
+                ["tenant_id", "alert_fingerprint"],
+            )
+    elif dialect == "postgresql":
+        constraint_exists = conn.execute(
+            """
+                SELECT conname 
+                FROM pg_constraint 
+                WHERE conrelid = 'alertenrichment'::regclass 
+                AND conname = 'alert_fingerprint';
+            """
+        ).fetchone()
+        with op.batch_alter_table("alertenrichment") as batch_op:
+            if constraint_exists:
+                batch_op.drop_constraint("alert_fingerprint", type_="unique")
+
+            batch_op.execute(
+                """
+                    WITH duplicates AS (
+                        SELECT id,
+                            ROW_NUMBER() OVER (
+                                PARTITION BY tenant_id, alert_fingerprint 
+                                ORDER BY timestamp DESC
+                            ) AS rn
+                        FROM alertenrichment
+                    )
+                    DELETE FROM alertenrichment
+                    WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
+                """
+            )
+            batch_op.create_unique_constraint(
+                "uc_alertenrichment_tenant_fingerprint",
+                ["tenant_id", "alert_fingerprint"],
+            )
+    elif dialect == "sqlite":
+        op.create_table(
+            "alertenrichment_new",
+            sa.Column("enrichments", sa.JSON(), nullable=True),
+            sa.Column("id", sqlmodel.sql.sqltypes.types.Uuid(), nullable=False),
+            sa.Column("tenant_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
+            sa.Column("timestamp", sa.DateTime(), nullable=False),
+            sa.Column(
+                "alert_fingerprint", sqlmodel.sql.sqltypes.AutoString(), nullable=False
+            ),
+            sa.ForeignKeyConstraint(
+                ["tenant_id"],
+                ["tenant.id"],
+            ),
+            sa.PrimaryKeyConstraint("id"),
+        )
+
+        # Copy existing data
+        op.execute(
+            """
+                INSERT INTO alertenrichment_new (id, tenant_id, alert_fingerprint, timestamp, enrichments)
+                SELECT id, tenant_id, alert_fingerprint, timestamp, enrichments FROM alertenrichment;
+            """
+        )
+        op.execute(
+            """
+                WITH duplicates AS (
+                    SELECT id,
+                        ROW_NUMBER() OVER (
+                            PARTITION BY tenant_id, alert_fingerprint 
+                            ORDER BY timestamp DESC
+                        ) AS rn
+                    FROM alertenrichment_new
+                )
+                DELETE FROM alertenrichment_new
+                WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
+            """
+        )
+        with op.batch_alter_table("alertenrichment_new") as batch_op:
+            batch_op.create_unique_constraint(
+                "uc_alertenrichment_tenant_fingerprint",
+                ["tenant_id", "alert_fingerprint"],
+            )
+
+        op.execute("DROP TABLE alertenrichment;")
+        op.execute("ALTER TABLE alertenrichment_new RENAME TO alertenrichment;")
path: keep/api/models/db/migrations/versions/2025-03-11-16-54_16309df224d1.py
text: @user1:
May I ask to leave `alertenrichment_old` permanently in the DB and remove it in 3-4 migrations later once we're sure it works fine?

@author:
Makes sense 👍