-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `d2c_ops`
--

-- --------------------------------------------------------

--
-- Table structure for table `ai_insights`
--

CREATE TABLE `ai_insights` (
  `id` int(11) NOT NULL,
  `incident_id` int(11) DEFAULT NULL,
  `root_cause` text DEFAULT NULL,
  `recommended_actions` text DEFAULT NULL,
  `similar_incidents` text DEFAULT NULL,
  `prevention_tips` text DEFAULT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `ai_insights`
--

INSERT INTO `ai_insights` (`id`, `incident_id`, `root_cause`, `recommended_actions`, `similar_incidents`, `prevention_tips`, `generated_at`) VALUES
(12, 154, 'The incident \"Momo Airtime Top-Up Service Outage\" points to an API gateway or upstream service failure. Gateway timeouts (HTTP 502/503/504) in telecom fintech environments are typically caused by one or more backend services becoming unresponsive — due to high CPU load, memory exhaustion, database connection saturation, or a deployment that introduced a regression. In a Critical severity scenario, this likely affects a significant portion of end-user transactions. Connection pool limits being reached, misconfigured load balancer health checks, or a cascading failure from a dependent microservice are also common root causes.', '• Check the API gateway error rate and identify which specific endpoints are failing (4xx/5xx breakdown).\n• Review application logs for stack traces, database connection errors, or timeout patterns in the last 30 minutes.\n• Scale up the affected service horizontally if CPU or memory is the bottleneck — add instances immediately.\n• Check all downstream dependencies (database, cache, third-party APIs) for health issues.\n• Roll back the most recent deployment if the incident started shortly after a release.\n• Notify affected clients/stakeholders with an estimated resolution time and update status page.', 'API timeout incidents in this environment are frequently related to:\n• Database query performance degradation causing slow responses that cascade into gateway timeouts.\n• Memory leaks in long-running service instances that gradually increase response times over hours.\n• Third-party payment provider outages (Momo, Sanlam) causing timeout cascades in the middleware layer.\n• Misconfigured auto-scaling policies that fail to spin up new instances fast enough during traffic spikes.\n• Connection pool exhaustion when database connections are not properly released after use.', '• Implement circuit breakers on all external API calls so a single slow dependency doesn\'t cascade.\n• Set conservative timeout limits (e.g. 5s) on all outbound API calls and handle timeout errors gracefully.\n• Add APM (Application Performance Monitoring) tracing to identify p95/p99 latency hotspots before they cause incidents.\n• Configure autoscaling triggers at 60% CPU/memory rather than 80% to give lead time for scale-out.\n• Run weekly load tests against staging to surface performance regressions before production deployment.', '2026-05-08 16:03:45'),
(13, 155, 'The incident \"Momo Payment Gateway Down\" points to an API gateway or upstream service failure. Gateway timeouts (HTTP 502/503/504) in telecom fintech environments are typically caused by one or more backend services becoming unresponsive — due to high CPU load, memory exhaustion, database connection saturation, or a deployment that introduced a regression. In a Critical severity scenario, this likely affects a significant portion of end-user transactions. Connection pool limits being reached, misconfigured load balancer health checks, or a cascading failure from a dependent microservice are also common root causes.', '• Check the API gateway error rate and identify which specific endpoints are failing (4xx/5xx breakdown).\n• Review application logs for stack traces, database connection errors, or timeout patterns in the last 30 minutes.\n• Scale up the affected service horizontally if CPU or memory is the bottleneck — add instances immediately.\n• Check all downstream dependencies (database, cache, third-party APIs) for health issues.\n• Roll back the most recent deployment if the incident started shortly after a release.\n• Notify affected clients/stakeholders with an estimated resolution time and update status page.', 'API timeout incidents in this environment are frequently related to:\n• Database query performance degradation causing slow responses that cascade into gateway timeouts.\n• Memory leaks in long-running service instances that gradually increase response times over hours.\n• Third-party payment provider outages (Momo, Sanlam) causing timeout cascades in the middleware layer.\n• Misconfigured auto-scaling policies that fail to spin up new instances fast enough during traffic spikes.\n• Connection pool exhaustion when database connections are not properly released after use.', '• Implement circuit breakers on all external API calls so a single slow dependency doesn\'t cascade.\n• Set conservative timeout limits (e.g. 5s) on all outbound API calls and handle timeout errors gracefully.\n• Add APM (Application Performance Monitoring) tracing to identify p95/p99 latency hotspots before they cause incidents.\n• Configure autoscaling triggers at 60% CPU/memory rather than 80% to give lead time for scale-out.\n• Run weekly load tests against staging to surface performance regressions before production deployment.', '2026-05-09 01:42:28');

-- --------------------------------------------------------

--
-- Table structure for table `approvals`
--

CREATE TABLE `approvals` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `type` varchar(20) NOT NULL,
  `title` text NOT NULL,
  `description` text DEFAULT NULL,
  `submitted_by` varchar(36) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `reviewed_by` varchar(36) DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `approvals`
--

INSERT INTO `approvals` (`id`, `process_id`, `type`, `title`, `description`, `submitted_by`, `status`, `reviewed_by`, `reviewed_at`, `created_at`) VALUES
(10, 3, 'change', 'momo rollback', 'the server needs a rollback ', '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', 'approved', '98cf9cbb-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 05:56:16', '2026-05-08 07:52:46'),
(11, 5, 'change', 'Failover to backup payment processor', 'Switching to backup processor to restore transactions while primary is down.', '98cfa543-4aa6-11f1-b9a2-f875a4659663', 'approved', '98cfa696-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 12:54:34', '2026-05-08 14:52:37'),
(12, 5, 'change', 'Rotate JWT secret and flush Redis auth cache', 'Emergency rotation required to restore authentication. Estimated 5 min downtime.', '98cfa696-4aa6-11f1-b9a2-f875a4659663', 'approved', '98cf036c-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 13:35:43', '2026-05-08 15:33:33'),
(13, 5, 'change', 'Failover to backup payment processor', 'Switching to backup processor to restore transactions while primary is down.', '98cfa543-4aa6-11f1-b9a2-f875a4659663', 'approved', '98cfa696-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 23:39:37', '2026-05-09 01:38:48'),
(14, 1, 'change', 'testing', 'testing', '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 'pending', NULL, NULL, '2026-05-09 05:47:33'),
(15, 1, 'access', 'testing', 'testing', '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 'rejected', '98ce7a57-4aa6-11f1-b9a2-f875a4659663', '2026-05-09 03:48:33', '2026-05-09 05:47:48'),
(16, 1, 'config', 'testing', 'testing', '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 'pending', NULL, NULL, '2026-05-09 05:48:08');

-- --------------------------------------------------------

--
-- Table structure for table `audit_log`
--

CREATE TABLE `audit_log` (
  `id` bigint(20) NOT NULL,
  `user_id` varchar(36) DEFAULT NULL,
  `process_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `audit_log`
--

INSERT INTO `audit_log` (`id`, `user_id`, `process_id`, `action`, `entity_type`, `entity_id`, `ip_address`, `created_at`) VALUES
(431, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cf019e-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 06:51:17'),
(432, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:07:09'),
(433, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:07:10'),
(434, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:07:12'),
(435, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:08:09'),
(436, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:08:11'),
(437, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:08:12'),
(438, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:09:09'),
(439, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:09:11'),
(440, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:09:12'),
(441, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:10:09'),
(442, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:10:11'),
(443, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:10:12'),
(444, '98cf019e-4aa6-11f1-b9a2-f875a4659663', 3, 'notification.send', 'notification', '12', NULL, '2026-05-08 07:10:38'),
(445, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:11:09'),
(446, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:11:10'),
(447, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:11:12'),
(448, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:12:09'),
(449, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:12:11'),
(450, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:12:12'),
(451, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:13:09'),
(452, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:13:10'),
(453, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:13:12'),
(454, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:14:09'),
(455, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:14:10'),
(456, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:14:12'),
(457, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:15:09'),
(458, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:15:11'),
(459, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:15:13'),
(460, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:16:09'),
(461, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:16:10'),
(462, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:16:12'),
(463, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:17:09'),
(464, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:17:11'),
(465, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:17:12'),
(466, NULL, 3, 'escalation.tier_1_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:18:17'),
(467, NULL, 3, 'escalation.tier_2_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:18:18'),
(468, NULL, 3, 'escalation.tier_3_fired', 'incident', '133', '127.0.0.1', '2026-05-08 07:18:20'),
(469, '98cf019e-4aa6-11f1-b9a2-f875a4659663', 3, 'incident.update', 'incident', '134', NULL, '2026-05-08 07:18:42'),
(470, '98cf019e-4aa6-11f1-b9a2-f875a4659663', 3, 'incident.update', 'incident', '133', NULL, '2026-05-08 07:18:47'),
(471, '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', 3, 'ticket.create', 'ticket', '10', NULL, '2026-05-08 07:43:16'),
(472, '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', 3, 'approval.create', 'approval', '10', NULL, '2026-05-08 07:52:46'),
(473, '98cf9cbb-4aa6-11f1-b9a2-f875a4659663', 3, 'approval.approved', 'approval', '10', NULL, '2026-05-08 07:56:16'),
(474, '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', 3, 'handover.submit', 'handover', '6', NULL, '2026-05-08 08:01:01'),
(475, '98cf9cbb-4aa6-11f1-b9a2-f875a4659663', 3, 'handover.acknowledge', 'handover', '6', NULL, '2026-05-08 08:02:30'),
(476, NULL, 1, 'escalation.tier_1_fired', 'incident', '127', '127.0.0.1', '2026-05-08 08:49:57'),
(477, NULL, 1, 'escalation.tier_2_fired', 'incident', '127', '127.0.0.1', '2026-05-08 08:50:13'),
(478, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cf036c-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 09:19:03'),
(479, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'notification.send', 'notification', '13', NULL, '2026-05-08 09:26:06'),
(480, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'incident.update', 'incident', '127', NULL, '2026-05-08 13:04:59'),
(481, '98cf019e-4aa6-11f1-b9a2-f875a4659663', 3, 'incident.update', 'incident', '133', NULL, '2026-05-08 13:22:51'),
(482, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cf9cbb-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 13:26:11'),
(483, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cfa696-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 13:29:42'),
(484, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'incident.update', 'incident', '139', NULL, '2026-05-08 13:30:26'),
(485, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'incident.update', 'incident', '139', NULL, '2026-05-08 13:30:42'),
(486, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'incident.update', 'incident', '139', NULL, '2026-05-08 13:30:44'),
(487, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '139', NULL, '2026-05-08 13:32:13'),
(488, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '139', NULL, '2026-05-08 13:32:25'),
(489, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cf019e-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 14:04:44'),
(490, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 14:06:31'),
(491, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cf036c-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 14:08:47'),
(492, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cfa543-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 14:10:13'),
(493, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cfa8ec-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 14:10:55'),
(494, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cfa7c8-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-08 14:12:09'),
(495, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '139', NULL, '2026-05-08 14:25:00'),
(496, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '140', NULL, '2026-05-08 14:25:03'),
(497, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '140', NULL, '2026-05-08 14:25:28'),
(498, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '139', NULL, '2026-05-08 14:25:35'),
(499, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.create', 'incident', '151', NULL, '2026-05-08 14:28:00'),
(500, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '151', NULL, '2026-05-08 14:34:53'),
(501, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.create', 'incident', '152', NULL, '2026-05-08 14:36:35'),
(502, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '152', NULL, '2026-05-08 14:37:48'),
(503, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '152', NULL, '2026-05-08 14:39:48'),
(504, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'ticket.create', 'ticket', '11', NULL, '2026-05-08 14:46:20'),
(505, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'approval.create', 'approval', '11', NULL, '2026-05-08 14:52:37'),
(506, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 5, 'approval.approved', 'approval', '11', NULL, '2026-05-08 14:54:34'),
(507, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 5, 'handover.submit', 'handover', '7', NULL, '2026-05-08 14:56:51'),
(508, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'handover.acknowledge', 'handover', '7', NULL, '2026-05-08 14:58:00'),
(509, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '152', NULL, '2026-05-08 14:59:41'),
(510, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.create', 'incident', '153', NULL, '2026-05-08 15:19:48'),
(511, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '153', NULL, '2026-05-08 15:23:34'),
(512, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'ticket.create', 'ticket', '12', NULL, '2026-05-08 15:27:32'),
(513, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '153', NULL, '2026-05-08 15:31:01'),
(514, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 5, 'approval.create', 'approval', '12', NULL, '2026-05-08 15:33:33'),
(515, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'approval.approved', 'approval', '12', NULL, '2026-05-08 15:35:43'),
(516, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '153', NULL, '2026-05-08 15:36:29'),
(517, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.create', 'incident', '154', NULL, '2026-05-08 15:56:34'),
(518, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '154', NULL, '2026-05-08 15:57:56'),
(519, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'notification.send', 'notification', '14', NULL, '2026-05-08 16:01:24'),
(520, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '154', NULL, '2026-05-08 16:02:16'),
(521, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'handover.submit', 'handover', '8', NULL, '2026-05-08 16:06:00'),
(522, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 5, 'handover.acknowledge', 'handover', '8', NULL, '2026-05-08 16:06:48'),
(523, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '154', NULL, '2026-05-09 01:29:42'),
(524, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.create', 'incident', '155', NULL, '2026-05-09 01:30:15'),
(525, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '155', NULL, '2026-05-09 01:31:00'),
(526, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '155', NULL, '2026-05-09 01:31:35'),
(527, '98cf036c-4aa6-11f1-b9a2-f875a4659663', 5, 'ticket.create', 'ticket', '13', NULL, '2026-05-09 01:36:45'),
(528, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 5, 'approval.create', 'approval', '13', NULL, '2026-05-09 01:38:48'),
(529, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 5, 'approval.approved', 'approval', '13', NULL, '2026-05-09 01:39:37'),
(530, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 5, 'handover.submit', 'handover', '9', NULL, '2026-05-09 01:41:01'),
(531, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 5, 'incident.update', 'incident', '155', NULL, '2026-05-09 01:42:41'),
(532, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'ticket.create', 'ticket', '14', NULL, '2026-05-09 05:45:57'),
(533, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'ticket.create', 'ticket', '15', NULL, '2026-05-09 05:46:15'),
(534, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'ticket.create', 'ticket', '16', NULL, '2026-05-09 05:46:30'),
(535, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'approval.create', 'approval', '14', NULL, '2026-05-09 05:47:33'),
(536, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'approval.create', 'approval', '15', NULL, '2026-05-09 05:47:48'),
(537, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'approval.create', 'approval', '16', NULL, '2026-05-09 05:48:08'),
(538, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'approval.rejected', 'approval', '15', NULL, '2026-05-09 05:48:33'),
(539, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cf036c-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-09 09:34:26'),
(540, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cfa543-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-09 09:35:24'),
(541, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cfa8ec-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-09 09:36:26'),
(542, '98ce7a57-4aa6-11f1-b9a2-f875a4659663', 1, 'user.email_change', 'user', '98cfa7c8-4aa6-11f1-b9a2-f875a4659663', NULL, '2026-05-09 09:36:59');

-- --------------------------------------------------------

--
-- Table structure for table `automation_rules`
--

CREATE TABLE `automation_rules` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `trigger_condition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`trigger_condition`)),
  `action` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`action`)),
  `is_active` tinyint(1) DEFAULT 1,
  `execution_count` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `automation_rules`
--

INSERT INTO `automation_rules` (`id`, `process_id`, `name`, `trigger_condition`, `action`, `is_active`, `execution_count`, `created_at`) VALUES
(20, 1, 'Escalate Sev 1 after 30min', '{\"severity\":1,\"unresolved_minutes\":30}', '{\"type\":\"escalate\",\"chain\":\"Sev1 Escalation\"}', 1, 2, '2026-05-08 06:23:27'),
(21, 1, 'Nightly SLA Digest', '{\"schedule\":\"0 22 * * *\"}', '{\"type\":\"email\",\"template\":\"nightly_digest\"}', 1, 0, '2026-05-08 06:23:27'),
(22, 3, 'Momo Server down', '{\"severity\":1,\"unresolved_minutes\":15}', '{\"type\":\"escalate\",\"chain_id\":13}', 1, 36, '2026-05-08 07:07:06');

-- --------------------------------------------------------

--
-- Table structure for table `downtime_events`
--

CREATE TABLE `downtime_events` (
  `id` int(11) NOT NULL,
  `incident_id` int(11) DEFAULT NULL,
  `process_id` int(11) NOT NULL,
  `started_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `ended_at` timestamp NULL DEFAULT NULL,
  `services_affected` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`services_affected`)),
  `impact_level` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `downtime_events`
--

INSERT INTO `downtime_events` (`id`, `incident_id`, `process_id`, `started_at`, `ended_at`, `services_affected`, `impact_level`) VALUES
(6, NULL, 3, '2026-05-08 07:57:10', '2026-05-08 05:57:10', '[\"momo server down\"]', 'high'),
(7, NULL, 5, '2026-05-08 15:01:45', '2026-05-08 13:01:45', '[\"Payment Gateway\",\"Checkout API\"]', 'medium'),
(8, NULL, 5, '2026-05-08 15:37:38', '2026-05-08 13:37:38', '[\"Auth Service\",\"Mobile API\"]', 'high'),
(9, NULL, 5, '2026-05-08 16:04:11', '2026-05-08 14:04:11', '[\"USSD Gateway\",\"Airtime API\"]', 'high'),
(10, NULL, 5, '2026-05-09 01:42:59', '2026-05-08 23:42:59', '[\"Payment Gateway\",\"Checkout API\"]', 'high'),
(11, NULL, 5, '2026-05-09 01:52:51', '2026-05-08 23:52:51', '[\"Primary gateway recovered after vendor patched DNS misconfiguration. Backup processor failover held for 47 minutes\"]', 'medium'),
(12, NULL, 5, '2026-05-09 04:06:44', '2026-05-09 02:06:44', '[\"Log Downtime Event\"]', 'medium'),
(13, NULL, 5, '2026-05-09 04:10:41', '2026-05-09 02:10:41', '[\"Log Downtime Event\"]', 'medium'),
(14, NULL, 5, '2026-05-09 04:26:11', '2026-05-09 02:26:11', '[\"Payment Gateway\",\"Checkout API\"]', 'medium'),
(15, NULL, 5, '2026-05-09 04:48:36', '2026-05-09 02:48:36', '[\"Payment Gateway\",\"Checkout API\"]', 'medium'),
(16, NULL, 1, '2026-05-09 03:44:00', NULL, '[\"Payment Gateway\",\"Checkout API\"]', 'medium'),
(17, NULL, 1, '2026-05-09 03:43:00', NULL, '[]', 'high'),
(18, NULL, 1, '2026-05-09 03:45:00', NULL, '[]', 'low');

-- --------------------------------------------------------

--
-- Table structure for table `escalation_chains`
--

CREATE TABLE `escalation_chains` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `severity` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `escalation_chains`
--

INSERT INTO `escalation_chains` (`id`, `process_id`, `name`, `severity`) VALUES
(14, 1, 'Sev1 Escalation', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `escalation_executions`
--

CREATE TABLE `escalation_executions` (
  `id` int(10) UNSIGNED NOT NULL,
  `incident_id` int(11) NOT NULL,
  `chain_id` int(11) NOT NULL,
  `tier_id` int(11) NOT NULL,
  `rule_id` int(11) DEFAULT NULL,
  `channel` varchar(20) NOT NULL DEFAULT 'email',
  `recipient_email` varchar(255) DEFAULT NULL,
  `status` enum('sent','skipped','failed') NOT NULL DEFAULT 'sent',
  `note` text DEFAULT NULL,
  `executed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `escalation_executions`
--

INSERT INTO `escalation_executions` (`id`, `incident_id`, `chain_id`, `tier_id`, `rule_id`, `channel`, `recipient_email`, `status`, `note`, `executed_at`) VALUES
(394, 127, 14, 22, 20, 'email', 'ops.ignite@d2ctelcare.com', 'sent', NULL, '2026-05-08 08:49:57'),
(395, 127, 14, 23, 20, 'email', 'mgr.ignite@d2ctelcare.com', 'sent', NULL, '2026-05-08 08:50:13');

-- --------------------------------------------------------

--
-- Table structure for table `escalation_tiers`
--

CREATE TABLE `escalation_tiers` (
  `id` int(11) NOT NULL,
  `chain_id` int(11) NOT NULL,
  `tier_number` tinyint(4) NOT NULL,
  `user_id` varchar(36) DEFAULT NULL,
  `trigger_after_minutes` int(11) NOT NULL,
  `channels` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`channels`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `escalation_tiers`
--

INSERT INTO `escalation_tiers` (`id`, `chain_id`, `tier_number`, `user_id`, `trigger_after_minutes`, `channels`) VALUES
(22, 14, 1, '98cf8173-4aa6-11f1-b9a2-f875a4659663', 0, '[\"email\"]'),
(23, 14, 2, '98cf911b-4aa6-11f1-b9a2-f875a4659663', 15, '[\"email\"]');

-- --------------------------------------------------------

--
-- Table structure for table `handovers`
--

CREATE TABLE `handovers` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `shift` varchar(20) NOT NULL,
  `submitted_by` varchar(36) DEFAULT NULL,
  `handed_to` varchar(36) DEFAULT NULL,
  `open_incidents` text DEFAULT NULL,
  `actions_taken` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `acknowledged_at` timestamp NULL DEFAULT NULL,
  `handover_time` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `handovers`
--

INSERT INTO `handovers` (`id`, `process_id`, `shift`, `submitted_by`, `handed_to`, `open_incidents`, `actions_taken`, `notes`, `submitted_at`, `acknowledged_at`, `handover_time`) VALUES
(6, 3, 'day', '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', '98cf9cbb-4aa6-11f1-b9a2-f875a4659663', 'momo server down', 'testing ', 'it needs to be up and running ', '2026-05-08 08:01:01', '2026-05-08 06:02:30', '10:04:00'),
(7, 5, 'night', '98cfa696-4aa6-11f1-b9a2-f875a4659663', '98cfa543-4aa6-11f1-b9a2-f875a4659663', 'Momo Payment Gateway Down — Sev 1, in progress', 'Failover to backup processor approved and actioned. Vendor support contacted.', '', '2026-05-08 14:56:51', '2026-05-08 12:58:00', '17:00:00'),
(8, 5, 'day', '98cfa543-4aa6-11f1-b9a2-f875a4659663', '98cfa696-4aa6-11f1-b9a2-f875a4659663', 'Airtime Top-Up Outage — Sev 1, in progress', 'Vendor contacted, awaiting patch from their infra team. Monitoring every 5 minutes', '', '2026-05-08 16:06:00', '2026-05-08 14:06:48', '18:06:00'),
(9, 5, 'night', '98cfa696-4aa6-11f1-b9a2-f875a4659663', '98cfa543-4aa6-11f1-b9a2-f875a4659663', 'Momo Payment Gateway Down — Sev 1, in progress', 'Failover to backup processor approved and actioned. Vendor support contacted', '', '2026-05-09 01:41:01', NULL, '03:42:00');

-- --------------------------------------------------------

--
-- Table structure for table `incidents`
--

CREATE TABLE `incidents` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `title` text NOT NULL,
  `description` text DEFAULT NULL,
  `severity` tinyint(4) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'open',
  `assigned_to` varchar(36) DEFAULT NULL,
  `opened_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `resolved_at` timestamp NULL DEFAULT NULL,
  `rca` text DEFAULT NULL,
  `reminder_sent_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `incidents`
--

INSERT INTO `incidents` (`id`, `process_id`, `title`, `description`, `severity`, `status`, `assigned_to`, `opened_at`, `resolved_at`, `rca`, `reminder_sent_at`) VALUES
(127, 1, 'Ignite API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'open', '98cf911b-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 03:23:27', NULL, NULL, '2026-05-08 13:58:27'),
(128, 1, 'Ignite Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'in_progress', '98cf8173-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 02:23:27', NULL, NULL, NULL),
(129, 1, 'Ignite SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(130, 2, 'Sunking API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'open', NULL, '2026-05-08 03:23:27', NULL, NULL, '2026-05-08 13:58:28'),
(131, 2, 'Sunking Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'in_progress', '98cf966e-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 02:23:27', NULL, NULL, NULL),
(132, 2, 'Sunking SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(133, 3, 'RDG API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'resolved', '98cf9cbb-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 03:23:27', '2026-05-08 05:18:47', NULL, NULL),
(134, 3, 'RDG Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'resolved', '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 02:23:27', '2026-05-08 05:18:42', NULL, NULL),
(135, 3, 'RDG SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(136, 4, 'MobiHive API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'open', NULL, '2026-05-08 03:23:27', NULL, NULL, '2026-05-08 13:58:28'),
(137, 4, 'MobiHive Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'in_progress', '98cfa06d-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 02:23:27', NULL, NULL, NULL),
(138, 4, 'MobiHive SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(139, 5, 'Momo API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'resolved', NULL, '2026-05-08 03:23:27', '2026-05-08 12:25:00', NULL, NULL),
(140, 5, 'Momo Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'resolved', NULL, '2026-05-08 02:23:27', '2026-05-08 12:25:03', NULL, NULL),
(141, 5, 'Momo SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(142, 6, 'Sanlam API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'open', NULL, '2026-05-08 03:23:27', NULL, NULL, '2026-05-08 13:58:29'),
(143, 6, 'Sanlam Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'in_progress', '98cfaa23-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 02:23:27', NULL, NULL, NULL),
(144, 6, 'Sanlam SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(145, 7, 'MBA API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'open', NULL, '2026-05-08 03:23:27', NULL, NULL, '2026-05-08 13:58:29'),
(146, 7, 'MBA Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'in_progress', '98cfaf2c-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 02:23:27', NULL, NULL, NULL),
(147, 7, 'MBA SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(148, 8, 'Muzanu API Gateway Timeout', 'Upstream service not responding within SLA threshold.', 1, 'open', NULL, '2026-05-08 03:23:27', NULL, NULL, '2026-05-08 13:58:30'),
(149, 8, 'Muzanu Database Replication Lag', 'Read replica lagging by >30 seconds.', 2, 'in_progress', '98cfb40f-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 02:23:27', NULL, NULL, NULL),
(150, 8, 'Muzanu SSL Certificate Renewed', 'Routine certificate renewal completed.', 4, 'resolved', NULL, '2026-05-07 04:23:27', '2026-05-07 05:23:27', 'Automated renewal via certbot succeeded without intervention.', NULL),
(151, 5, 'Momo Payment Gateway Down', 'All payment transactions are failing with a 503 error. Customers cannot complete purchases.', 1, 'resolved', NULL, '2026-05-08 14:28:00', '2026-05-08 12:34:52', NULL, NULL),
(152, 5, 'Momo Payment Gateway Down', 'All payment transactions are failing with a 503 error. Customers cannot complete purchases', 1, 'resolved', '98cfa543-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 14:36:35', '2026-05-08 12:59:41', 'Primary gateway recovered after vendor patched DNS misconfiguration. Backup processor failover held for 47 minutes.', NULL),
(153, 5, 'Momo Mobile App Login Failure', 'Users are unable to log into the mobile app. Authentication service returning 401 on all requests', 2, 'resolved', '98cfa696-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 15:19:48', '2026-05-08 13:36:29', 'JWT secret had been rotated without updating the auth service config. Redis cache flushed and config redeployed. All logins restored.', NULL),
(154, 5, 'Momo Airtime Top-Up Service Outage', 'Customers unable to purchase airtime via USSD and mobile app. Vendor API returning 504', 1, 'resolved', '98cfa543-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 15:56:34', '2026-05-08 23:29:42', NULL, NULL),
(155, 5, 'Momo Payment Gateway Down', 'All payment transactions are failing with a 503 error. Customers cannot complete purchases', 1, 'resolved', '98cfa543-4aa6-11f1-b9a2-f875a4659663', '2026-05-09 01:30:15', '2026-05-08 23:42:41', 'Primary gateway recovered after vendor patched DNS misconfiguration. Backup processor failover held for 47 minutes', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `knex_migrations`
--

CREATE TABLE `knex_migrations` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `batch` int(11) DEFAULT NULL,
  `migration_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `knex_migrations`
--

INSERT INTO `knex_migrations` (`id`, `name`, `batch`, `migration_time`) VALUES
(1, '001_initial_schema.js', 1, '2026-04-19 08:08:37'),
(2, '002_notifications_direction.js', 2, '2026-04-22 12:01:32'),
(3, '003_super_admin_role.js', 3, '2026-04-23 07:35:41'),
(4, '004_verification_codes.js', 4, '2026-04-24 14:11:53'),
(5, '005_escalation_executions.js', 5, '2026-04-24 14:12:50'),
(6, '006_handover_time.js', 6, '2026-05-07 21:46:26'),
(7, '007_ticket_description.js', 7, '2026-05-08 07:34:05'),
(8, '008_incident_reminder.js', 8, '2026-05-08 13:54:22');

-- --------------------------------------------------------

--
-- Table structure for table `knex_migrations_lock`
--

CREATE TABLE `knex_migrations_lock` (
  `index` int(11) NOT NULL,
  `is_locked` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `knex_migrations_lock`
--

INSERT INTO `knex_migrations_lock` (`index`, `is_locked`) VALUES
(1, 0);

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `subject` text NOT NULL,
  `body` text NOT NULL,
  `sent_by` varchar(36) DEFAULT NULL,
  `recipient_count` int(11) DEFAULT 0,
  `bounce_count` int(11) DEFAULT 0,
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `direction` varchar(20) DEFAULT 'ops_to_client'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `process_id`, `subject`, `body`, `sent_by`, `recipient_count`, `bounce_count`, `sent_at`, `direction`) VALUES
(12, 3, 'momoserver down', 'whats IT doing about it \n', '98cf019e-4aa6-11f1-b9a2-f875a4659663', 1, 1, '2026-05-08 07:10:38', 'ops_to_client'),
(13, 5, 'TESTING ', 'Have you all received the alert\n', '98cf036c-4aa6-11f1-b9a2-f875a4659663', 1, 0, '2026-05-08 09:26:06', 'ops_to_client'),
(14, 5, 'momo outage', 'is it working ', '98cf036c-4aa6-11f1-b9a2-f875a4659663', 1, 0, '2026-05-08 16:01:24', 'ops_to_client');

-- --------------------------------------------------------

--
-- Table structure for table `oncall_schedules`
--

CREATE TABLE `oncall_schedules` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `user_id` varchar(36) DEFAULT NULL,
  `role` varchar(20) NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `oncall_schedules`
--

INSERT INTO `oncall_schedules` (`id`, `process_id`, `user_id`, `role`, `date`) VALUES
(30, 1, '98cf8173-4aa6-11f1-b9a2-f875a4659663', 'primary', '2026-05-08'),
(31, 1, '98cf911b-4aa6-11f1-b9a2-f875a4659663', 'backup', '2026-05-08'),
(32, 3, '98cf9b5c-4aa6-11f1-b9a2-f875a4659663', 'primary', '2026-05-08'),
(33, 3, '98cf9cbb-4aa6-11f1-b9a2-f875a4659663', 'backup', '2026-05-08'),
(34, 3, '98cf019e-4aa6-11f1-b9a2-f875a4659663', 'escalation', '2026-05-08'),
(35, 5, '98cfa543-4aa6-11f1-b9a2-f875a4659663', 'primary', '2026-05-08'),
(36, 5, '98cfa696-4aa6-11f1-b9a2-f875a4659663', 'backup', '2026-05-08');

-- --------------------------------------------------------

--
-- Table structure for table `processes`
--

CREATE TABLE `processes` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `processes`
--

INSERT INTO `processes` (`id`, `name`, `slug`, `created_at`) VALUES
(1, 'Ignite', 'ignite', '2026-05-08 06:23:26'),
(2, 'Sunking', 'sunking', '2026-05-08 06:23:26'),
(3, 'RDG', 'rdg', '2026-05-08 06:23:26'),
(4, 'MobiHive', 'mobihive', '2026-05-08 06:23:26'),
(5, 'Momo', 'momo', '2026-05-08 06:23:26'),
(6, 'Sanlam', 'sanlam', '2026-05-08 06:23:26'),
(7, 'MBA', 'mba', '2026-05-08 06:23:26'),
(8, 'Muzanu', 'muzanu', '2026-05-08 06:23:26');

-- --------------------------------------------------------

--
-- Table structure for table `service_checks`
--

CREATE TABLE `service_checks` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `service_name` varchar(100) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'healthy',
  `response_time_ms` int(11) DEFAULT NULL,
  `uptime_30d` decimal(5,2) DEFAULT NULL,
  `checked_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `service_checks`
--

INSERT INTO `service_checks` (`id`, `process_id`, `service_name`, `status`, `response_time_ms`, `uptime_30d`, `checked_at`) VALUES
(123, 1, 'Ignite API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(124, 1, 'Ignite Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(125, 1, 'Ignite Auth Service', 'healthy', 200, 99.50, '2026-05-08 06:23:27'),
(126, 2, 'Sunking API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(127, 2, 'Sunking Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(128, 2, 'Sunking Auth Service', 'healthy', 200, 99.50, '2026-05-08 06:23:27'),
(129, 3, 'RDG API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(130, 3, 'RDG Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(131, 3, 'RDG Auth Service', 'degraded', 850, 97.20, '2026-05-08 06:23:27'),
(132, 4, 'MobiHive API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(133, 4, 'MobiHive Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(134, 4, 'MobiHive Auth Service', 'healthy', 200, 99.50, '2026-05-08 06:23:27'),
(135, 5, 'Momo API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(136, 5, 'Momo Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(137, 5, 'Momo Auth Service', 'healthy', 200, 99.50, '2026-05-08 06:23:27'),
(138, 6, 'Sanlam API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(139, 6, 'Sanlam Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(140, 6, 'Sanlam Auth Service', 'healthy', 200, 99.50, '2026-05-08 06:23:27'),
(141, 7, 'MBA API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(142, 7, 'MBA Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(143, 7, 'MBA Auth Service', 'healthy', 200, 99.50, '2026-05-08 06:23:27'),
(144, 8, 'Muzanu API', 'healthy', 120, 99.80, '2026-05-08 06:23:27'),
(145, 8, 'Muzanu Database', 'healthy', 45, 99.90, '2026-05-08 06:23:27'),
(146, 8, 'Muzanu Auth Service', 'healthy', 200, 99.50, '2026-05-08 06:23:27');

-- --------------------------------------------------------

--
-- Table structure for table `tickets`
--

CREATE TABLE `tickets` (
  `id` int(11) NOT NULL,
  `process_id` int(11) NOT NULL,
  `incident_id` int(11) DEFAULT NULL,
  `title` text NOT NULL,
  `type` varchar(20) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'open',
  `assigned_to` varchar(36) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `resolved_at` timestamp NULL DEFAULT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tickets`
--

INSERT INTO `tickets` (`id`, `process_id`, `incident_id`, `title`, `type`, `status`, `assigned_to`, `created_at`, `resolved_at`, `description`) VALUES
(10, 3, NULL, 'server momo down', 'incident', 'open', '98cf019e-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 07:43:16', NULL, 'the server needs to be up and running '),
(11, 5, NULL, 'Payment vendor unreachable — escalate to vendor support', 'incident', 'closed', '98cfa543-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 14:46:20', NULL, 'Vendor support contact: +260 97 000 0000. Reference ticket MOB-2291.'),
(12, 5, NULL, 'Investigate auth service token expiry misconfiguration', 'incident', 'closed', '98cfa543-4aa6-11f1-b9a2-f875a4659663', '2026-05-08 15:27:32', NULL, 'Auth tokens appear to be expiring immediately. Check JWT secret rotation and Redis TTL settings'),
(13, 5, NULL, 'Payment vendor unreachable — escalate to vendor support', 'incident', 'closed', '98cfa543-4aa6-11f1-b9a2-f875a4659663', '2026-05-09 01:36:45', NULL, 'Vendor support contact: +260 97 000 0000. Reference ticket MOB-2291.'),
(14, 1, NULL, 'testing', 'change', 'open', NULL, '2026-05-09 05:45:57', NULL, 'testing'),
(15, 1, NULL, 'tseting', 'access', 'open', NULL, '2026-05-09 05:46:15', NULL, 'testing'),
(16, 1, NULL, 'testing', 'report', 'open', NULL, '2026-05-09 05:46:30', NULL, 'testing');

-- --------------------------------------------------------

--
-- Table structure for table `verification_codes`
--

CREATE TABLE `verification_codes` (
  `id` int(10) UNSIGNED NOT NULL,
  `code_key` varchar(320) NOT NULL,
  `code` varchar(16) NOT NULL,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
  `expires_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ai_insights`
--
ALTER TABLE `ai_insights`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ai_insights_incident_id_foreign` (`incident_id`);

--
-- Indexes for table `approvals`
--
ALTER TABLE `approvals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `approvals_process_id_foreign` (`process_id`),
  ADD KEY `approvals_submitted_by_foreign` (`submitted_by`),
  ADD KEY `approvals_reviewed_by_foreign` (`reviewed_by`);

--
-- Indexes for table `audit_log`
--
ALTER TABLE `audit_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `audit_log_user_id_foreign` (`user_id`),
  ADD KEY `audit_log_process_id_foreign` (`process_id`);

--
-- Indexes for table `automation_rules`
--
ALTER TABLE `automation_rules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `automation_rules_process_id_foreign` (`process_id`);

--
-- Indexes for table `downtime_events`
--
ALTER TABLE `downtime_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `downtime_events_incident_id_foreign` (`incident_id`),
  ADD KEY `downtime_events_process_id_foreign` (`process_id`);

--
-- Indexes for table `escalation_chains`
--
ALTER TABLE `escalation_chains`
  ADD PRIMARY KEY (`id`),
  ADD KEY `escalation_chains_process_id_foreign` (`process_id`);

--
-- Indexes for table `escalation_executions`
--
ALTER TABLE `escalation_executions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `escalation_executions_incident_id_tier_id_unique` (`incident_id`,`tier_id`),
  ADD KEY `escalation_executions_chain_id_foreign` (`chain_id`),
  ADD KEY `escalation_executions_tier_id_foreign` (`tier_id`),
  ADD KEY `escalation_executions_rule_id_foreign` (`rule_id`);

--
-- Indexes for table `escalation_tiers`
--
ALTER TABLE `escalation_tiers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `escalation_tiers_chain_id_foreign` (`chain_id`),
  ADD KEY `escalation_tiers_user_id_foreign` (`user_id`);

--
-- Indexes for table `handovers`
--
ALTER TABLE `handovers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `handovers_process_id_foreign` (`process_id`),
  ADD KEY `handovers_submitted_by_foreign` (`submitted_by`),
  ADD KEY `handovers_handed_to_foreign` (`handed_to`);

--
-- Indexes for table `incidents`
--
ALTER TABLE `incidents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `incidents_process_id_foreign` (`process_id`),
  ADD KEY `incidents_assigned_to_foreign` (`assigned_to`);

--
-- Indexes for table `knex_migrations`
--
ALTER TABLE `knex_migrations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `knex_migrations_lock`
--
ALTER TABLE `knex_migrations_lock`
  ADD PRIMARY KEY (`index`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `notifications_process_id_foreign` (`process_id`),
  ADD KEY `notifications_sent_by_foreign` (`sent_by`);

--
-- Indexes for table `oncall_schedules`
--
ALTER TABLE `oncall_schedules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `oncall_schedules_process_id_foreign` (`process_id`),
  ADD KEY `oncall_schedules_user_id_foreign` (`user_id`);

--
-- Indexes for table `processes`
--
ALTER TABLE `processes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `processes_slug_unique` (`slug`);

--
-- Indexes for table `service_checks`
--
ALTER TABLE `service_checks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `service_checks_process_id_foreign` (`process_id`);

--
-- Indexes for table `tickets`
--
ALTER TABLE `tickets`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tickets_process_id_foreign` (`process_id`),
  ADD KEY `tickets_incident_id_foreign` (`incident_id`),
  ADD KEY `tickets_assigned_to_foreign` (`assigned_to`);

--
-- Indexes for table `verification_codes`
--
ALTER TABLE `verification_codes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `verification_codes_code_key_unique` (`code_key`),
  ADD KEY `verification_codes_code_key_index` (`code_key`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ai_insights`
--
ALTER TABLE `ai_insights`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `approvals`
--
ALTER TABLE `approvals`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `audit_log`
--
ALTER TABLE `audit_log`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=543;

--
-- AUTO_INCREMENT for table `automation_rules`
--
ALTER TABLE `automation_rules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `downtime_events`
--
ALTER TABLE `downtime_events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `escalation_chains`
--
ALTER TABLE `escalation_chains`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `escalation_executions`
--
ALTER TABLE `escalation_executions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=396;

--
-- AUTO_INCREMENT for table `escalation_tiers`
--
ALTER TABLE `escalation_tiers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `handovers`
--
ALTER TABLE `handovers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `incidents`
--
ALTER TABLE `incidents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=156;

--
-- AUTO_INCREMENT for table `knex_migrations`
--
ALTER TABLE `knex_migrations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `knex_migrations_lock`
--
ALTER TABLE `knex_migrations_lock`
  MODIFY `index` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `oncall_schedules`
--
ALTER TABLE `oncall_schedules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `processes`
--
ALTER TABLE `processes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `service_checks`
--
ALTER TABLE `service_checks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=147;

--
-- AUTO_INCREMENT for table `tickets`
--
ALTER TABLE `tickets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `verification_codes`
--
ALTER TABLE `verification_codes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `ai_insights`
--
ALTER TABLE `ai_insights`
  ADD CONSTRAINT `ai_insights_incident_id_foreign` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `approvals`
--
ALTER TABLE `approvals`
  ADD CONSTRAINT `approvals_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `approvals_reviewed_by_foreign` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `approvals_submitted_by_foreign` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `audit_log`
--
ALTER TABLE `audit_log`
  ADD CONSTRAINT `audit_log_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `audit_log_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `automation_rules`
--
ALTER TABLE `automation_rules`
  ADD CONSTRAINT `automation_rules_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `downtime_events`
--
ALTER TABLE `downtime_events`
  ADD CONSTRAINT `downtime_events_incident_id_foreign` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `downtime_events_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `escalation_chains`
--
ALTER TABLE `escalation_chains`
  ADD CONSTRAINT `escalation_chains_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `escalation_executions`
--
ALTER TABLE `escalation_executions`
  ADD CONSTRAINT `escalation_executions_chain_id_foreign` FOREIGN KEY (`chain_id`) REFERENCES `escalation_chains` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `escalation_executions_incident_id_foreign` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `escalation_executions_rule_id_foreign` FOREIGN KEY (`rule_id`) REFERENCES `automation_rules` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `escalation_executions_tier_id_foreign` FOREIGN KEY (`tier_id`) REFERENCES `escalation_tiers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `escalation_tiers`
--
ALTER TABLE `escalation_tiers`
  ADD CONSTRAINT `escalation_tiers_chain_id_foreign` FOREIGN KEY (`chain_id`) REFERENCES `escalation_chains` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `escalation_tiers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `handovers`
--
ALTER TABLE `handovers`
  ADD CONSTRAINT `handovers_handed_to_foreign` FOREIGN KEY (`handed_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `handovers_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `handovers_submitted_by_foreign` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `incidents`
--
ALTER TABLE `incidents`
  ADD CONSTRAINT `incidents_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `incidents_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `notifications_sent_by_foreign` FOREIGN KEY (`sent_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `oncall_schedules`
--
ALTER TABLE `oncall_schedules`
  ADD CONSTRAINT `oncall_schedules_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `oncall_schedules_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `service_checks`
--
ALTER TABLE `service_checks`
  ADD CONSTRAINT `service_checks_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tickets`
--
ALTER TABLE `tickets`
  ADD CONSTRAINT `tickets_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `tickets_incident_id_foreign` FOREIGN KEY (`incident_id`) REFERENCES `incidents` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `tickets_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE;
COMMIT;

