1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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
|