CREATE TABLE `def_alert_incident` (
`IncidentGuid` varchar(36) NOT NULL,
`Title` varchar(500) NOT NULL,
`Description` text DEFAULT NULL,
`Severity` enum('CRITICAL','HIGH','MEDIUM','LOW') DEFAULT 'MEDIUM',
`Status` enum('CREATED','ACKNOWLEDGED','INVESTIGATING','RESOLVED','CLOSED') DEFAULT 'CREATED',
`Package` varchar(255) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`AssignedTo` varchar(100) DEFAULT NULL,
`AssignedToPersonGuid` char(50) DEFAULT NULL COMMENT 'PersonGuid assigned to incident',
`Impact` text DEFAULT NULL,
`ClosedAt` datetime DEFAULT NULL,
`ClosedBy` varchar(100) DEFAULT NULL,
`ClosedByPersonGuid` char(50) DEFAULT NULL COMMENT 'PersonGuid who closed incident',
PRIMARY KEY (`IncidentGuid`),
KEY `idx_package` (`Package`),
KEY `idx_status` (`Status`),
KEY `idx_severity` (`Severity`),
KEY `idx_created` (`CreatedAt`),
KEY `idx_package_status` (`Package`, `Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Incident tracking and management';
CREATE TABLE `def_alert_incident_alerts` (
`IncidentGuid` varchar(36) NOT NULL,
`AlertGuid` varchar(50) NOT NULL,
`LinkedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`IncidentGuid`, `AlertGuid`),
KEY `idx_incident` (`IncidentGuid`),
KEY `idx_alert` (`AlertGuid`),
FOREIGN KEY (`IncidentGuid`) REFERENCES `def_alert_incident`(`IncidentGuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Links alerts to incidents';
CREATE TABLE `def_alert_incident_timeline` (
`TimelineGuid` varchar(36) NOT NULL,
`IncidentGuid` varchar(36) NOT NULL,
`Timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`EventType` varchar(50) NOT NULL,
`Description` text DEFAULT NULL,
`User` varchar(100) DEFAULT NULL,
`PersonGuid` char(50) DEFAULT NULL COMMENT 'PersonGuid who performed action',
PRIMARY KEY (`TimelineGuid`),
KEY `idx_incident` (`IncidentGuid`),
KEY `idx_timestamp` (`Timestamp`),
FOREIGN KEY (`IncidentGuid`) REFERENCES `def_alert_incident`(`IncidentGuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Incident timeline and event history';
CREATE TABLE `def_alert_incident_notes` (
`NoteGuid` varchar(36) NOT NULL,
`IncidentGuid` varchar(36) NOT NULL,
`NoteType` enum('ROOT_CAUSE','RESOLUTION','IMPACT','LESSONS_LEARNED','GENERAL') DEFAULT 'GENERAL',
`Timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Note` text NOT NULL,
`User` varchar(100) DEFAULT NULL,
PRIMARY KEY (`NoteGuid`),
KEY `idx_incident` (`IncidentGuid`),
KEY `idx_type` (`NoteType`),
FOREIGN KEY (`IncidentGuid`) REFERENCES `def_alert_incident`(`IncidentGuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Incident notes and documentation';
CREATE TABLE `def_sla` (
`Package` varchar(255) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`CriticalResponseMinutes` int(11) DEFAULT 15 COMMENT 'Response time for CRITICAL incidents in minutes',
`HighResponseMinutes` int(11) DEFAULT 60 COMMENT 'Response time for HIGH incidents in minutes',
`MediumResponseMinutes` int(11) DEFAULT 240 COMMENT 'Response time for MEDIUM incidents in minutes',
`LowResponseMinutes` int(11) DEFAULT 1440 COMMENT 'Response time for LOW incidents in minutes',
`CriticalResolutionHours` int(11) DEFAULT 4 COMMENT 'Resolution time for CRITICAL incidents in hours',
`HighResolutionHours` int(11) DEFAULT 24 COMMENT 'Resolution time for HIGH incidents in hours',
`MediumResolutionHours` int(11) DEFAULT 72 COMMENT 'Resolution time for MEDIUM incidents in hours',
`LowResolutionHours` int(11) DEFAULT 168 COMMENT 'Resolution time for LOW incidents in hours',
`IncidentReportRecipients` text DEFAULT NULL COMMENT 'Comma-separated email addresses for incident reports',
`EscalationContacts` text DEFAULT NULL COMMENT 'Comma-separated email addresses for escalation',
`OnCallTeam` varchar(255) DEFAULT NULL COMMENT 'Name of on-call team/rotation',
`ReportFrequency` enum('DAILY','WEEKLY','MONTHLY','ON_CLOSE') DEFAULT 'WEEKLY' COMMENT 'Frequency of automated incident reports',
`UptimeTargetPercent` decimal(5,2) DEFAULT 99.90 COMMENT 'Target uptime percentage',
`Active` char(1) DEFAULT 'Y' COMMENT 'Y/N - Is this SLA active',
`CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`UpdatedAt` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Package`),
KEY `idx_active` (`Active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='SLA definitions and contact lists for packages';
CREATE TABLE `def_alert_incident_communications` (
`CommunicationGuid` varchar(36) NOT NULL,
`IncidentGuid` varchar(36) NOT NULL,
`ShortRef` varchar(20) NOT NULL COMMENT 'Short reference ID embedded in messages (e.g., REF-20250115-A7K)',
`Timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Channel` enum('EMAIL','SLACK','DISCORD','PAGERDUTY','SMS','WHATSAPP','MQTT','DBUS','PUSH') NOT NULL,
`Direction` enum('OUTBOUND','INBOUND') DEFAULT 'OUTBOUND',
`Recipient` varchar(255) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Message` text DEFAULT NULL,
`Status` enum('PENDING','SENT','FAILED','DELIVERED','READ') DEFAULT 'PENDING',
`ExternalId` varchar(255) DEFAULT NULL COMMENT 'PagerDuty dedup_key, Slack message ts, etc.',
`ErrorMessage` text DEFAULT NULL,
`Metadata` JSON DEFAULT NULL COMMENT 'Additional channel-specific data',
`SentBy` varchar(100) DEFAULT NULL COMMENT 'User or system that sent the communication',
PRIMARY KEY (`CommunicationGuid`),
UNIQUE KEY `idx_short_ref` (`ShortRef`),
KEY `idx_incident` (`IncidentGuid`),
KEY `idx_channel` (`Channel`),
KEY `idx_status` (`Status`),
KEY `idx_timestamp` (`Timestamp`),
KEY `idx_external_id` (`ExternalId`),
FOREIGN KEY (`IncidentGuid`) REFERENCES `def_alert_incident`(`IncidentGuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} COMMENT='Incident communication tracking and audit trail';
INSERT INTO def_alert_incident
(IncidentGuid, Title, Description, Severity, Status, Package,
AssignedTo, AssignedToPersonGuid, Impact)
VALUES
('{incident_guid}', '{title}', '{description}', '{severity}', '{status}', '{package}',
'{assigned_to}', {assigned_to_person_guid}, '{impact}')
UPDATE def_alert_incident
SET Status = '{status}'
WHERE IncidentGuid = '{incident_guid}'
UPDATE def_alert_incident
SET Status = 'CLOSED',
ClosedAt = NOW(),
ClosedBy = '{closed_by}',
ClosedByPersonGuid = {closed_by_person_guid}
WHERE IncidentGuid = '{incident_guid}'
INSERT IGNORE INTO def_alert_incident_alerts
(IncidentGuid, AlertGuid)
VALUES
('{incident_guid}', '{alert_guid}')
SELECT
t.Guid,
t.Alert,
t.Package,
t.AlertTriggerTime,
t.Severity,
t.AlertNote
FROM def_alert_incident_alerts ia
JOIN track_alert t ON ia.AlertGuid = t.Guid
WHERE ia.IncidentGuid = '{incident_guid}'
ORDER BY t.AlertTriggerTime DESC
INSERT INTO def_alert_incident_timeline
(TimelineGuid, IncidentGuid, EventType, Description, User, PersonGuid)
VALUES
(UUID(), '{incident_guid}', '{event_type}', '{description}', '{user}', {person_guid})
SELECT Timestamp, EventType, Description, User
FROM def_alert_incident_timeline
WHERE IncidentGuid = '{incident_guid}'
ORDER BY Timestamp ASC
INSERT INTO def_alert_incident_notes
(NoteGuid, IncidentGuid, NoteType, Note, User)
VALUES
(UUID(), '{incident_guid}', '{note_type}', '{notes}', '{user}')
SELECT NoteType, Timestamp, Note, User
FROM def_alert_incident_notes
WHERE IncidentGuid = '{incident_guid}'
ORDER BY Timestamp ASC
SELECT
IncidentGuid,
Title,
Description,
Severity,
Status,
Package,
CreatedAt,
AssignedTo,
Impact,
ClosedAt,
ClosedBy,
TIMESTAMPDIFF(HOUR, CreatedAt, COALESCE(ClosedAt, NOW())) as TimeToCloseHours
FROM def_alert_incident
WHERE IncidentGuid = '{incident_guid}'
SELECT
i.IncidentGuid,
i.Title,
i.Severity,
i.Status,
i.CreatedAt,
i.AssignedTo,
COUNT(ia.AlertGuid) as AlertCount
FROM def_alert_incident i
LEFT JOIN def_alert_incident_alerts ia ON i.IncidentGuid = ia.IncidentGuid
WHERE {where_clause}
AND i.CreatedAt > DATE_SUB(NOW(), INTERVAL {days} DAY)
GROUP BY i.IncidentGuid, i.Title, i.Severity, i.Status, i.CreatedAt, i.AssignedTo
ORDER BY i.CreatedAt DESC
LIMIT {limit}
SELECT
COUNT(*) as total_incidents,
SUM(CASE WHEN Status != 'CLOSED' THEN 1 ELSE 0 END) as open_incidents,
SUM(CASE WHEN Status = 'CLOSED' THEN 1 ELSE 0 END) as closed_incidents,
AVG(CASE
WHEN Status = 'CLOSED' THEN TIMESTAMPDIFF(HOUR, CreatedAt, ClosedAt)
ELSE NULL
END) as avg_time_to_close_hours,
SUM(CASE WHEN Severity = 'CRITICAL' THEN 1 ELSE 0 END) as critical_count,
SUM(CASE WHEN Severity = 'HIGH' THEN 1 ELSE 0 END) as high_count,
SUM(CASE WHEN Severity = 'MEDIUM' THEN 1 ELSE 0 END) as medium_count,
SUM(CASE WHEN Severity = 'LOW' THEN 1 ELSE 0 END) as low_count
FROM def_alert_incident
WHERE Package = '{package}'
AND CreatedAt > DATE_SUB(NOW(), INTERVAL {days} DAY)
SELECT
DATE(CreatedAt) as incident_date,
COUNT(*) as total_created,
SUM(CASE WHEN Status = 'CLOSED' AND DATE(ClosedAt) = DATE(CreatedAt) THEN 1 ELSE 0 END) as total_closed,
SUM(CASE WHEN Severity = 'CRITICAL' THEN 1 ELSE 0 END) as critical,
SUM(CASE WHEN Severity = 'HIGH' THEN 1 ELSE 0 END) as high,
SUM(CASE WHEN Severity = 'MEDIUM' THEN 1 ELSE 0 END) as medium,
SUM(CASE WHEN Severity = 'LOW' THEN 1 ELSE 0 END) as low
FROM def_alert_incident
WHERE Package = '{package}'
AND CreatedAt > DATE_SUB(NOW(), INTERVAL {days} DAY)
GROUP BY DATE(CreatedAt)
ORDER BY incident_date DESC
UPDATE def_alert_incident_alerts
SET IncidentGuid = '{target_incident_guid}'
WHERE IncidentGuid = '{source_incident_guid}'
UPDATE def_alert_incident_timeline
SET IncidentGuid = '{target_incident_guid}'
WHERE IncidentGuid = '{source_incident_guid}'
UPDATE def_alert_incident_notes
SET IncidentGuid = '{target_incident_guid}'
WHERE IncidentGuid = '{source_incident_guid}'
DELETE FROM def_alert_incident
WHERE IncidentGuid = '{source_incident_guid}'
SELECT
Package,
CriticalResponseMinutes,
HighResponseMinutes,
MediumResponseMinutes,
LowResponseMinutes,
CriticalResolutionHours,
HighResolutionHours,
MediumResolutionHours,
LowResolutionHours,
IncidentReportRecipients,
EscalationContacts,
OnCallTeam,
ReportFrequency,
UptimeTargetPercent
FROM def_sla
WHERE Package = '{package}'
AND Active = 'Y'
INSERT INTO def_sla
(Package, CriticalResponseMinutes, HighResponseMinutes, MediumResponseMinutes, LowResponseMinutes,
CriticalResolutionHours, HighResolutionHours, MediumResolutionHours, LowResolutionHours,
IncidentReportRecipients, EscalationContacts, OnCallTeam, ReportFrequency, UptimeTargetPercent, Active)
VALUES
('{package}', {critical_response_min}, {high_response_min}, {medium_response_min}, {low_response_min},
{critical_resolution_hrs}, {high_resolution_hrs}, {medium_resolution_hrs}, {low_resolution_hrs},
'{incident_recipients}', '{escalation_contacts}', '{oncall_team}', '{report_frequency}', {uptime_target}, '{active}')
ON DUPLICATE KEY UPDATE
CriticalResponseMinutes = {critical_response_min},
HighResponseMinutes = {high_response_min},
MediumResponseMinutes = {medium_response_min},
LowResponseMinutes = {low_response_min},
CriticalResolutionHours = {critical_resolution_hrs},
HighResolutionHours = {high_resolution_hrs},
MediumResolutionHours = {medium_resolution_hrs},
LowResolutionHours = {low_resolution_hrs},
IncidentReportRecipients = '{incident_recipients}',
EscalationContacts = '{escalation_contacts}',
OnCallTeam = '{oncall_team}',
ReportFrequency = '{report_frequency}',
UptimeTargetPercent = {uptime_target},
Active = '{active}',
UpdatedAt = NOW()
SELECT
i.IncidentGuid,
i.Title,
i.Description,
i.Severity,
i.Status,
i.Package,
i.CreatedAt,
i.AssignedTo,
i.Impact,
i.ClosedAt,
i.ClosedBy,
TIMESTAMPDIFF(HOUR, i.CreatedAt, COALESCE(i.ClosedAt, NOW())) as TimeToCloseHours,
COUNT(DISTINCT ia.AlertGuid) as AlertCount,
GROUP_CONCAT(DISTINCT t.EventType ORDER BY t.Timestamp SEPARATOR ', ') as TimelineEvents
FROM def_alert_incident i
LEFT JOIN def_alert_incident_alerts ia ON i.IncidentGuid = ia.IncidentGuid
LEFT JOIN def_alert_incident_timeline t ON i.IncidentGuid = t.IncidentGuid
WHERE i.IncidentGuid = '{incident_guid}'
GROUP BY i.IncidentGuid, i.Title, i.Description, i.Severity, i.Status, i.Package,
i.CreatedAt, i.AssignedTo, i.Impact, i.ClosedAt, i.ClosedBy
INSERT INTO def_alert_incident_communications
(CommunicationGuid, IncidentGuid, ShortRef, Channel, Direction, Recipient, Subject, Message, Status, ExternalId, Metadata, SentBy)
VALUES
('{communication_guid}', '{incident_guid}', '{short_ref}', '{channel}', '{direction}', '{recipient}', '{subject}', '{message}', '{status}', '{external_id}', '{metadata}', '{sent_by}')
UPDATE def_alert_incident_communications
SET Status = '{status}',
ErrorMessage = '{error_message}'
WHERE CommunicationGuid = '{communication_guid}'
SELECT
CommunicationGuid,
ShortRef,
Timestamp,
Channel,
Direction,
Recipient,
Subject,
Message,
Status,
ExternalId,
ErrorMessage,
SentBy
FROM def_alert_incident_communications
WHERE IncidentGuid = '{incident_guid}'
{channel_filter}
ORDER BY Timestamp DESC
LIMIT {limit}
SELECT
CommunicationGuid,
IncidentGuid,
Channel,
Status
FROM def_alert_incident_communications
WHERE ExternalId = '{external_id}'
AND Channel = '{channel}'
SELECT COUNT(*) as total
FROM def_alert_incident_communications
WHERE IncidentGuid = '{incident_guid}'
{channel_filter}
SELECT
c.CommunicationGuid,
c.IncidentGuid,
c.ShortRef,
c.Timestamp,
c.Channel,
c.Direction,
c.Recipient,
c.Subject,
c.Message,
c.Status,
c.ExternalId,
c.SentBy,
i.Title as IncidentTitle,
i.Severity as IncidentSeverity,
i.Status as IncidentStatus
FROM def_alert_incident_communications c
JOIN def_alert_incident i ON c.IncidentGuid = i.IncidentGuid
WHERE c.ShortRef = '{short_ref}'