1 files changed, 56 insertions, 0 deletions
diff --git a/db/post_migrate/20210721174521_add_non_null_constraint_for_escalation_rule_on_pending_alert_escalations.rb b/db/post_migrate/20210721174521_add_non_null_constraint_for_escalation_rule_on_pending_alert_escalations.rb
new file mode 100644
index 00000000000..5d42fd4896d
--- /dev/null
+++ b/db/post_migrate/20210721174521_add_non_null_constraint_for_escalation_rule_on_pending_alert_escalations.rb
@@ -0,0 +1,56 @@
+# frozen_string_literal: true
+
+class AddNonNullConstraintForEscalationRuleOnPendingAlertEscalations < ActiveRecord::Migration[6.1]
+  ELAPSED_WHOLE_MINUTES_IN_SECONDS = <<~SQL
+    ABS(ROUND(
+      EXTRACT(EPOCH FROM (escalations.process_at - escalations.created_at))/60*60
+    ))
+  SQL
+
+  INSERT_RULES_FOR_ESCALATIONS_WITHOUT_RULES = <<~SQL
+    INSERT INTO incident_management_escalation_rules (policy_id, oncall_schedule_id, status, elapsed_time_seconds, is_removed)
+    SELECT
+      policies.id,
+      schedule_id,
+      status,
+      #{ELAPSED_WHOLE_MINUTES_IN_SECONDS} AS elapsed_time_seconds,
+      TRUE
+    FROM incident_management_pending_alert_escalations AS escalations
+    INNER JOIN incident_management_oncall_schedules AS schedules ON schedules.id = schedule_id
+    INNER JOIN incident_management_escalation_policies AS policies ON policies.project_id = schedules.project_id
+    WHERE rule_id IS NULL
+    GROUP BY policies.id, schedule_id, status, elapsed_time_seconds
+    ON CONFLICT DO NOTHING;
+  SQL
+
+  UPDATE_EMPTY_RULE_IDS = <<~SQL
+    UPDATE incident_management_pending_alert_escalations AS escalations
+    SET rule_id = rules.id
+    FROM incident_management_pending_alert_escalations AS through_escalations
+    INNER JOIN incident_management_oncall_schedules AS schedules ON schedules.id = through_escalations.schedule_id
+    INNER JOIN incident_management_escalation_policies AS policies ON policies.project_id = schedules.project_id
+    INNER JOIN incident_management_escalation_rules AS rules ON rules.policy_id = policies.id
+    WHERE escalations.rule_id IS NULL
+    AND rules.status = escalations.status
+    AND rules.oncall_schedule_id = escalations.schedule_id
+    AND rules.elapsed_time_seconds = #{ELAPSED_WHOLE_MINUTES_IN_SECONDS};
+  SQL
+
+  DELETE_LEFTOVER_ESCALATIONS_WITHOUT_RULES = 'DELETE FROM incident_management_pending_alert_escalations WHERE rule_id IS NULL;'
+
+  # For each alert which has a pending escalation without a corresponding rule,
+  # create a rule with the expected attributes for the project's policy.
+  #
+  # Deletes all escalations without rules/policy & adds non-null constraint for rule_id.
+  def up
+    exec_query INSERT_RULES_FOR_ESCALATIONS_WITHOUT_RULES
+    exec_query UPDATE_EMPTY_RULE_IDS
+    exec_query DELETE_LEFTOVER_ESCALATIONS_WITHOUT_RULES
+
+    change_column_null :incident_management_pending_alert_escalations, :rule_id, false
+  end
+
+  def down
+    change_column_null :incident_management_pending_alert_escalations, :rule_id, true
+  end
+end