# CoreDataHub Master Reference Version: 1.0 Last Updated: 2026-06-10 This document is the authoritative architecture and database reference for CoreDataHub. CoreDataHub Project Reference Project Overview CoreDataHub is a distributed hosting, cloud, backup and infrastructure management platform. Architecture is designed around: • Panel Server • Load Balancer • Web Nodes • Mail Nodes • Database Nodes • Agent Framework All infrastructure is managed from a centralized control panel. ________________________________________ Infrastructure Architecture Panel Server Purpose: • Main Control Panel • API Layer • Authentication • Billing • Hosting Management • Agent Management Domain: coredatahub.co.in Apache: Installed on Panel Node ________________________________________ Load Balancer Node Purpose: • Traffic Distribution • Future HA Architecture ________________________________________ Web Nodes Current Node: WEB-NODE-1 IP: 192.168.1.204 Purpose: • Hosting Storage • Website Hosting • Agent Execution Hosting Root: /hosting Agent Root: /opt/coredatahub-agent Agent User: myadmin Privilege Model: sudo ________________________________________ Database Nodes Purpose: • MySQL Services • Replication • Backup Current Database: coredatahub_panel (Master Database) +-----------------------------+ | Tables_in_coredatahub_panel | +-----------------------------+ | activity_logs | | agent_tokens | | api_tokens | | backups | | bandwidth_usage | | cluster_info | | compute_instances | | deployments | | dns_records | | domains | | file_activity_logs | | ftp_accounts | | ftp_users | | git_repositories | | hosting_accounts | | hosting_databases | | hosting_plans | | invoices | | jwt_refresh_tokens | | login_attempts | | mail_domains | | mailboxes | | menu_items | | node_agents | | nodes | | notification_reads | | notifications | | payments | | permissions | | plans | | products | | provisioning_jobs | | provisioning_logs | | redirects | | reseller_plans | | reseller_subscriptions | | reseller_wallet | | role_permissions | | roles | | server_stats | | servers | | ssl_certificates | | subscriptions | | support_attachments | | support_chat_attachments | | support_chat_messages | | support_chat_sessions | | support_messages | | support_tickets | | system_settings | | terminal_commands | | terminal_sessions | | usage_alerts | | user_permissions | | user_products | | user_roles | | users | | websites | +-----------------------------+ 58 rows in set (0.00 sec) -- MySQL dump 10.13 Distrib 8.0.46, for Linux (x86_64) -- -- Host: 192.168.1.201 Database: coredatahub_panel -- ------------------------------------------------------ -- Server version 8.0.46-0ubuntu0.22.04.2 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `activity_logs` -- DROP TABLE IF EXISTS `activity_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `activity_logs` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `hosting_id` int DEFAULT NULL, `action` varchar(255) NOT NULL, `ip_address` varchar(50) DEFAULT NULL, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `hosting_id` (`hosting_id`) ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `activity_logs` -- LOCK TABLES `activity_logs` WRITE; /*!40000 ALTER TABLE `activity_logs` DISABLE KEYS */; INSERT INTO `activity_logs` VALUES (1,1,NULL,'User Logged In','192.168.1.4','2026-03-01 20:14:26'),(2,1,NULL,'User Logged In','192.168.1.4','2026-03-01 20:17:15'),(3,1,NULL,'User Logged In','192.168.1.4','2026-03-02 01:52:51'),(4,1,NULL,'User Logged In','192.168.1.4','2026-03-02 12:21:24'),(5,1,NULL,'User Logged In','192.168.1.4','2026-03-04 16:07:43'),(6,1,NULL,'User Logged In','192.168.1.4','2026-03-04 17:08:26'),(7,1,NULL,'User Logged In','192.168.1.4','2026-03-04 19:32:02'),(8,1,NULL,'User Logged In','192.168.1.4','2026-03-05 22:46:37'),(9,1,NULL,'User Logged In','192.168.1.4','2026-03-07 22:31:55'),(10,8,NULL,'User Logged In','115.187.41.162','2026-03-09 21:03:54'),(11,8,NULL,'User Logged In','115.187.41.162','2026-03-11 13:18:17'),(12,8,NULL,'User Logged In','115.187.41.162','2026-03-11 13:26:48'),(13,8,NULL,'User Logged In','115.187.41.162','2026-03-11 15:39:47'),(14,8,NULL,'User Logged In','115.187.41.162','2026-03-11 19:26:50'),(15,8,NULL,'User Logged In','115.187.41.162','2026-03-11 21:34:59'),(16,8,NULL,'User Logged In','115.187.41.162','2026-03-11 22:33:23'),(17,8,NULL,'User Logged In','115.187.41.162','2026-03-11 23:07:36'),(18,8,NULL,'User Logged In','115.187.41.162','2026-03-13 00:25:25'),(19,8,NULL,'User Logged In','115.187.41.162','2026-03-14 11:23:44'),(20,8,NULL,'User Logged In','115.187.41.162','2026-03-14 14:58:08'),(21,8,NULL,'User Logged In','115.187.41.162','2026-03-14 16:21:31'),(22,8,NULL,'User Logged In','115.187.41.162','2026-03-15 00:55:04'),(23,8,NULL,'User Logged In','115.187.41.162','2026-03-15 12:12:59'),(24,8,NULL,'User Logged In','115.187.41.162','2026-03-15 13:43:30'),(25,8,NULL,'User Logged In','115.187.41.162','2026-03-15 19:17:16'),(26,8,NULL,'User Logged In','115.187.41.162','2026-03-15 19:27:59'),(27,8,NULL,'User Logged In','115.187.41.162','2026-03-15 19:59:23'),(28,8,NULL,'User Logged In','115.187.41.162','2026-03-15 20:09:22'),(29,2,NULL,'User Logged In','115.187.41.162','2026-06-07 00:53:28'),(30,1,NULL,'Login from NEW IP: 115.187.41.162','115.187.41.162','2026-06-07 00:57:16'),(31,1,NULL,'User Logged In','115.187.41.162','2026-06-07 00:57:16'),(32,2,NULL,'User Logged In','192.168.1.203','2026-06-07 19:50:20'),(33,2,NULL,'User Logged In','192.168.1.203','2026-06-08 05:35:23'),(34,2,NULL,'User Logged In','192.168.1.203','2026-06-08 05:47:02'),(35,2,NULL,'User Logged In','192.168.1.203','2026-06-08 06:48:08'),(36,2,NULL,'User Logged In','192.168.1.203','2026-06-08 07:00:29'),(37,2,NULL,'User Logged In','192.168.1.203','2026-06-08 07:58:53'),(38,2,NULL,'User Logged In','192.168.1.203','2026-06-08 08:02:21'),(39,2,NULL,'User Logged In','192.168.1.203','2026-06-08 08:03:20'),(40,2,NULL,'User Logged In','192.168.1.203','2026-06-08 08:07:32'),(41,2,NULL,'User Logged In','192.168.1.203','2026-06-08 08:08:33'),(42,2,NULL,'User Logged In','192.168.1.203','2026-06-08 08:10:28'),(43,2,NULL,'User Logged In','192.168.1.203','2026-06-08 08:11:42'),(44,2,NULL,'User Logged In','192.168.1.203','2026-06-08 08:16:05'); /*!40000 ALTER TABLE `activity_logs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `agent_tokens` -- DROP TABLE IF EXISTS `agent_tokens`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `agent_tokens` ( `id` bigint NOT NULL AUTO_INCREMENT, `server_id` int NOT NULL, `agent_uuid` varchar(100) NOT NULL, `api_token` varchar(255) NOT NULL, `is_active` tinyint(1) DEFAULT '1', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_agent_uuid` (`agent_uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `agent_tokens` -- LOCK TABLES `agent_tokens` WRITE; /*!40000 ALTER TABLE `agent_tokens` DISABLE KEYS */; /*!40000 ALTER TABLE `agent_tokens` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `api_tokens` -- DROP TABLE IF EXISTS `api_tokens`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `api_tokens` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `token_hash` varchar(255) NOT NULL, `expires_at` datetime DEFAULT NULL, `created_at` datetime DEFAULT CURRENT_TIMESTAMP, `last_used_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `api_tokens` -- LOCK TABLES `api_tokens` WRITE; /*!40000 ALTER TABLE `api_tokens` DISABLE KEYS */; /*!40000 ALTER TABLE `api_tokens` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `backups` -- DROP TABLE IF EXISTS `backups`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `backups` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int DEFAULT NULL, `backup_file` varchar(255) DEFAULT NULL, `backup_date` datetime DEFAULT NULL, `size_mb` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `backups` -- LOCK TABLES `backups` WRITE; /*!40000 ALTER TABLE `backups` DISABLE KEYS */; /*!40000 ALTER TABLE `backups` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `bandwidth_usage` -- DROP TABLE IF EXISTS `bandwidth_usage`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `bandwidth_usage` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `date` date NOT NULL, `bytes` bigint DEFAULT '0', `requests` int DEFAULT '0', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `hosting_id` (`hosting_id`,`date`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `bandwidth_usage` -- LOCK TABLES `bandwidth_usage` WRITE; /*!40000 ALTER TABLE `bandwidth_usage` DISABLE KEYS */; INSERT INTO `bandwidth_usage` VALUES (1,1,'2026-03-14',0,0,'2026-03-14 17:00:01'),(2,2,'2026-03-14',0,0,'2026-03-14 17:00:01'),(3,3,'2026-03-14',0,0,'2026-03-14 17:00:02'),(4,4,'2026-03-14',0,0,'2026-03-14 17:00:02'),(5,5,'2026-03-14',0,0,'2026-03-14 17:00:02'),(6,6,'2026-03-14',0,0,'2026-03-14 17:00:02'),(7,7,'2026-03-14',0,0,'2026-03-14 17:00:03'),(16,7,'2026-03-15',0,0,'2026-03-14 18:30:03'); /*!40000 ALTER TABLE `bandwidth_usage` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `cluster_info` -- DROP TABLE IF EXISTS `cluster_info`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `cluster_info` ( `cluster_id` varchar(50) NOT NULL, `cluster_name` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`cluster_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `cluster_info` -- LOCK TABLES `cluster_info` WRITE; /*!40000 ALTER TABLE `cluster_info` DISABLE KEYS */; /*!40000 ALTER TABLE `cluster_info` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `compute_instances` -- DROP TABLE IF EXISTS `compute_instances`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `compute_instances` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `instance_name` varchar(255) NOT NULL, `instance_type` enum('shared_hosting','reseller','vps','dedicated','container') NOT NULL, `server_id` int NOT NULL, `ip_address` varchar(50) DEFAULT NULL, `cpu_cores` int DEFAULT '0', `ram_mb` int DEFAULT '0', `disk_mb` bigint DEFAULT '0', `status` enum('provisioning','running','stopped','suspended','terminated') DEFAULT 'provisioning', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_user` (`user_id`), KEY `idx_server` (`server_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `compute_instances` -- LOCK TABLES `compute_instances` WRITE; /*!40000 ALTER TABLE `compute_instances` DISABLE KEYS */; /*!40000 ALTER TABLE `compute_instances` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `deployments` -- DROP TABLE IF EXISTS `deployments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `deployments` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `repository_id` bigint unsigned DEFAULT NULL, `deployment_type` enum('git','zip','manual','rollback','container') NOT NULL, `initiated_by` int NOT NULL, `target_server_id` int DEFAULT NULL, `source_reference` varchar(500) DEFAULT NULL, `deployed_version` varchar(255) DEFAULT NULL, `deployment_log` longtext, `status` enum('queued','running','completed','failed','cancelled') DEFAULT 'queued', `started_at` datetime DEFAULT NULL, `completed_at` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_hosting` (`hosting_id`), KEY `idx_repository` (`repository_id`), KEY `idx_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `deployments` -- LOCK TABLES `deployments` WRITE; /*!40000 ALTER TABLE `deployments` DISABLE KEYS */; /*!40000 ALTER TABLE `deployments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `dns_records` -- DROP TABLE IF EXISTS `dns_records`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `dns_records` ( `id` int NOT NULL AUTO_INCREMENT, `domain_id` int DEFAULT NULL, `record_type` varchar(10) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `content` varchar(255) DEFAULT NULL, `ttl` int DEFAULT '3600', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `dns_records` -- LOCK TABLES `dns_records` WRITE; /*!40000 ALTER TABLE `dns_records` DISABLE KEYS */; INSERT INTO `dns_records` VALUES (1,1,'A','@','115.187.41.162',3600,'2026-03-11 18:49:19'),(2,1,'A','www','115.187.41.162',3600,'2026-03-11 18:49:19'),(3,1,'A','mail','115.187.41.162',3600,'2026-03-11 18:49:19'),(4,1,'MX','@','webmail.advancesolutionsystem.in',3600,'2026-03-11 18:49:19'); /*!40000 ALTER TABLE `dns_records` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `domains` -- DROP TABLE IF EXISTS `domains`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `domains` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int DEFAULT NULL, `domain` varchar(255) DEFAULT NULL, `type` enum('primary','addon','parked','subdomain') DEFAULT NULL, `status` enum('active','suspended') DEFAULT 'active', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `hosting_id` (`hosting_id`,`domain`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `domains` -- LOCK TABLES `domains` WRITE; /*!40000 ALTER TABLE `domains` DISABLE KEYS */; INSERT INTO `domains` VALUES (1,7,'advancesolutionsystem.in','primary','active','2026-03-11 18:48:12'); /*!40000 ALTER TABLE `domains` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `file_activity_logs` -- DROP TABLE IF EXISTS `file_activity_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `file_activity_logs` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `user_id` int NOT NULL, `action` enum('upload','download','delete','rename','move','copy','extract','compress','edit','chmod','chown') NOT NULL, `file_path` varchar(1000) NOT NULL, `old_value` varchar(1000) DEFAULT NULL, `new_value` varchar(1000) DEFAULT NULL, `ip_address` varchar(50) DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_hosting` (`hosting_id`), KEY `idx_user` (`user_id`), KEY `idx_action` (`action`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `file_activity_logs` -- LOCK TABLES `file_activity_logs` WRITE; /*!40000 ALTER TABLE `file_activity_logs` DISABLE KEYS */; /*!40000 ALTER TABLE `file_activity_logs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ftp_accounts` -- DROP TABLE IF EXISTS `ftp_accounts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `ftp_accounts` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `username` varchar(100) DEFAULT NULL, `password_hash` varchar(255) DEFAULT NULL, `directory` varchar(255) DEFAULT NULL, `web_node_ip` varchar(50) DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ftp_accounts` -- LOCK TABLES `ftp_accounts` WRITE; /*!40000 ALTER TABLE `ftp_accounts` DISABLE KEYS */; /*!40000 ALTER TABLE `ftp_accounts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ftp_users` -- DROP TABLE IF EXISTS `ftp_users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `ftp_users` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `username` varchar(150) DEFAULT NULL, `system_user` varchar(150) DEFAULT NULL, `password` varchar(255) NOT NULL, `dir` varchar(255) NOT NULL, `quota` int DEFAULT '0', `status` enum('active','suspended') DEFAULT 'active', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `hosting_id` (`hosting_id`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ftp_users` -- LOCK TABLES `ftp_users` WRITE; /*!40000 ALTER TABLE `ftp_users` DISABLE KEYS */; INSERT INTO `ftp_users` VALUES (17,7,'vdt','uadvace130_vdt','$1$UWKB2UBG$SOLVPQC.Hmq5bmmtaARfN1','/home/uadvace130',0,'active','2026-03-15 16:08:09'); /*!40000 ALTER TABLE `ftp_users` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `git_repositories` -- DROP TABLE IF EXISTS `git_repositories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `git_repositories` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `repository_name` varchar(255) NOT NULL, `repository_url` varchar(500) NOT NULL, `branch_name` varchar(100) DEFAULT 'main', `deploy_path` varchar(500) NOT NULL, `authentication_type` enum('ssh_key','personal_access_token','public') DEFAULT 'public', `auto_deploy` tinyint(1) DEFAULT '0', `last_commit_hash` varchar(100) DEFAULT NULL, `last_commit_message` text, `status` enum('active','disabled') DEFAULT 'active', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_hosting` (`hosting_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `git_repositories` -- LOCK TABLES `git_repositories` WRITE; /*!40000 ALTER TABLE `git_repositories` DISABLE KEYS */; /*!40000 ALTER TABLE `git_repositories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `hosting_accounts` -- DROP TABLE IF EXISTS `hosting_accounts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hosting_accounts` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `domain` varchar(255) NOT NULL, `plan_id` int NOT NULL, `web_server_id` int NOT NULL, `mail_server_id` int DEFAULT NULL, `document_root` varchar(255) NOT NULL, `disk_quota_mb` int NOT NULL DEFAULT '25000', `bandwidth_quota_mb` bigint NOT NULL DEFAULT '300000', `current_disk_usage_mb` bigint NOT NULL DEFAULT '0', `current_bandwidth_usage_mb` bigint NOT NULL DEFAULT '0', `php_version` varchar(20) NOT NULL DEFAULT '8.3', `ftp_enabled` tinyint(1) NOT NULL DEFAULT '1', `ssh_enabled` tinyint(1) NOT NULL DEFAULT '0', `last_backup_at` datetime DEFAULT NULL, `status` enum('pending','active','suspended','terminated') NOT NULL DEFAULT 'pending', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_domain` (`domain`), KEY `idx_user_id` (`user_id`), KEY `idx_plan_id` (`plan_id`), KEY `idx_web_server_id` (`web_server_id`), KEY `idx_mail_server_id` (`mail_server_id`), CONSTRAINT `fk_hosting_mail_server` FOREIGN KEY (`mail_server_id`) REFERENCES `servers` (`id`) ON DELETE RESTRICT, CONSTRAINT `fk_hosting_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_hosting_web_server` FOREIGN KEY (`web_server_id`) REFERENCES `servers` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `hosting_accounts` -- LOCK TABLES `hosting_accounts` WRITE; /*!40000 ALTER TABLE `hosting_accounts` DISABLE KEYS */; /*!40000 ALTER TABLE `hosting_accounts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `hosting_databases` -- DROP TABLE IF EXISTS `hosting_databases`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hosting_databases` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `db_name` varchar(100) NOT NULL, `db_user` varchar(100) NOT NULL, `db_password` varchar(255) NOT NULL, `status` enum('active','deleted') DEFAULT 'active', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `hosting_id` (`hosting_id`,`db_name`), UNIQUE KEY `hosting_id_2` (`hosting_id`,`db_user`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `hosting_databases` -- LOCK TABLES `hosting_databases` WRITE; /*!40000 ALTER TABLE `hosting_databases` DISABLE KEYS */; INSERT INTO `hosting_databases` VALUES (1,1,'client4_db1','client4_usr1','f122d96d3594c204','active','2026-02-28 17:27:22'),(2,2,'client5_db1','client5_usr1','dfd51ff0e0460850','deleted','2026-02-28 18:04:27'); /*!40000 ALTER TABLE `hosting_databases` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `hosting_plans` -- DROP TABLE IF EXISTS `hosting_plans`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hosting_plans` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `disk_mb` int DEFAULT NULL, `bandwidth_mb` int DEFAULT NULL, `max_domains` int DEFAULT NULL, `max_databases` int DEFAULT NULL, `max_email_accounts` int DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `billing_cycle` enum('monthly','yearly') DEFAULT NULL, `is_active` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `hosting_plans` -- LOCK TABLES `hosting_plans` WRITE; /*!40000 ALTER TABLE `hosting_plans` DISABLE KEYS */; /*!40000 ALTER TABLE `hosting_plans` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `invoices` -- DROP TABLE IF EXISTS `invoices`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `invoices` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int DEFAULT NULL, `invoice_number` varchar(50) DEFAULT NULL, `total_amount` decimal(10,2) DEFAULT NULL, `gst_amount` decimal(10,2) DEFAULT NULL, `grand_total` decimal(10,2) DEFAULT NULL, `status` enum('pending','paid','overdue') DEFAULT 'pending', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `invoices` -- LOCK TABLES `invoices` WRITE; /*!40000 ALTER TABLE `invoices` DISABLE KEYS */; INSERT INTO `invoices` VALUES (1,6,'INV1772997207',100.00,18.00,118.00,'pending','2026-03-08 19:13:27'),(2,7,'INV1773006038',100.00,18.00,118.00,'pending','2026-03-08 21:40:38'),(3,8,'INV1773052510',100.00,18.00,118.00,'pending','2026-03-09 10:35:10'); /*!40000 ALTER TABLE `invoices` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `jwt_refresh_tokens` -- DROP TABLE IF EXISTS `jwt_refresh_tokens`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `jwt_refresh_tokens` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `refresh_token` varchar(255) NOT NULL, `expires_at` datetime NOT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `last_used_at` datetime DEFAULT NULL, `revoked` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `refresh_token` (`refresh_token`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `jwt_refresh_tokens` -- LOCK TABLES `jwt_refresh_tokens` WRITE; /*!40000 ALTER TABLE `jwt_refresh_tokens` DISABLE KEYS */; INSERT INTO `jwt_refresh_tokens` VALUES (1,2,'609b99bb8df053b9dbc035b171a8a0d7221a86a88abcf59e7773ed0c5bc5ee82','2026-06-15 01:20:20','2026-06-07 19:50:20',NULL,0),(2,2,'678b9c2949af909c05df6cbd9c190a6c15db9c0034afa42045d07cb2f08af96e','2026-06-15 11:05:23','2026-06-08 05:35:23',NULL,0),(3,2,'b633f7c0144780d2bded19c75e4c54dae8133f0a4a1e74ed6b29b361b6e99571','2026-06-15 11:17:02','2026-06-08 05:47:02',NULL,0),(4,2,'3a1f7c2e3ff2f530c072b5a63246636cbbdfed86c2e5fd5138b14cc4175d2491','2026-06-15 12:18:08','2026-06-08 06:48:08',NULL,0),(5,2,'d870651fa1650d6d2875dc734ec582ac59571573eab649876da8c5132007d182','2026-06-15 12:30:29','2026-06-08 07:00:29',NULL,0),(6,2,'4e920251122a03c2a814ce4edb4d80ad3d0467634b637ff7aa6d03583e400d26','2026-06-15 13:28:53','2026-06-08 07:58:53',NULL,0),(7,2,'7473e42a166a4280a867c5ee26869b7b63a13f22f232889f2dd30291320b6c59','2026-06-15 13:32:21','2026-06-08 08:02:21',NULL,0),(8,2,'281ad9e1d440645c4259cb07174e34e791b905ce1fa779fd9d4ec8ca7d16cb9a','2026-06-15 13:33:20','2026-06-08 08:03:20',NULL,0),(9,2,'a4f76013d9f09142470f8849a668099a7428f9f023f95ea3f453763645306234','2026-06-15 13:37:32','2026-06-08 08:07:32',NULL,0),(10,2,'efb4befd9223908d4d5f2a4309ce61f9895de13260f08718ac061cc2c5e06648','2026-06-15 13:38:33','2026-06-08 08:08:33',NULL,0),(11,2,'64ef9afdedea60f71c9693d1b1fb407b350852ae9ef3bf75218460017ea21b52','2026-06-15 13:40:28','2026-06-08 08:10:28',NULL,0),(12,2,'102275cf19e2e89f9e5f2c606ea3cb5a96bab5194ebb5b199d73fc05ce6c6fac','2026-06-15 13:41:42','2026-06-08 08:11:42',NULL,0),(13,2,'19261a0e84b484be776e7378f152e8945b9cc762fe607b7f68cd865ce94e1a3e','2026-06-15 13:46:05','2026-06-08 08:16:05',NULL,0); /*!40000 ALTER TABLE `jwt_refresh_tokens` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `login_attempts` -- DROP TABLE IF EXISTS `login_attempts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `login_attempts` ( `id` int NOT NULL AUTO_INCREMENT, `email` varchar(150) DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `attempts` int DEFAULT '1', `last_attempt` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `email` (`email`), KEY `ip_address` (`ip_address`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `login_attempts` -- LOCK TABLES `login_attempts` WRITE; /*!40000 ALTER TABLE `login_attempts` DISABLE KEYS */; INSERT INTO `login_attempts` VALUES (2,'vrush.dahak@gmail.com','192.168.1.203',1,'2026-03-14 11:22:40'),(3,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:47:15'),(4,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:47:34'),(5,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:47:48'),(6,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:48:14'),(7,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:49:43'),(8,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:50:12'),(9,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:55:17'),(10,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:55:29'),(11,'vrush.dahake@gmail.com','192.168.1.203',1,'2026-06-07 00:55:45'); /*!40000 ALTER TABLE `login_attempts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `mail_domains` -- DROP TABLE IF EXISTS `mail_domains`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `mail_domains` ( `id` int NOT NULL AUTO_INCREMENT, `domain` varchar(255) DEFAULT NULL, `hosting_id` int DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `mail_domains` -- LOCK TABLES `mail_domains` WRITE; /*!40000 ALTER TABLE `mail_domains` DISABLE KEYS */; /*!40000 ALTER TABLE `mail_domains` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `mailboxes` -- DROP TABLE IF EXISTS `mailboxes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `mailboxes` ( `id` int NOT NULL AUTO_INCREMENT, `domain_id` int DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password_hash` varchar(255) DEFAULT NULL, `quota_mb` int DEFAULT '1024', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `mailboxes` -- LOCK TABLES `mailboxes` WRITE; /*!40000 ALTER TABLE `mailboxes` DISABLE KEYS */; /*!40000 ALTER TABLE `mailboxes` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `menu_items` -- DROP TABLE IF EXISTS `menu_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `menu_items` ( `menu_id` int NOT NULL AUTO_INCREMENT, `parent_id` int DEFAULT NULL, `title` varchar(100) DEFAULT NULL, `route_name` varchar(100) DEFAULT NULL, `icon_name` varchar(100) DEFAULT NULL, `permission_code` varchar(100) DEFAULT NULL, `sort_order` int DEFAULT '0', `is_active` tinyint(1) DEFAULT '1', PRIMARY KEY (`menu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `menu_items` -- LOCK TABLES `menu_items` WRITE; /*!40000 ALTER TABLE `menu_items` DISABLE KEYS */; /*!40000 ALTER TABLE `menu_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `node_agents` -- DROP TABLE IF EXISTS `node_agents`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `node_agents` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `server_id` int NOT NULL, `agent_uuid` varchar(100) NOT NULL, `agent_version` varchar(50) NOT NULL, `api_token` varchar(255) NOT NULL, `operating_system` varchar(255) DEFAULT NULL, `kernel_version` varchar(255) DEFAULT NULL, `last_seen` datetime DEFAULT NULL, `cpu_usage` decimal(5,2) DEFAULT '0.00', `ram_usage` decimal(5,2) DEFAULT '0.00', `disk_usage` decimal(5,2) DEFAULT '0.00', `load_average` varchar(50) DEFAULT NULL, `status` enum('online','offline','warning') DEFAULT 'offline', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `agent_uuid` (`agent_uuid`), KEY `idx_server` (`server_id`), KEY `idx_status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `node_agents` -- LOCK TABLES `node_agents` WRITE; /*!40000 ALTER TABLE `node_agents` DISABLE KEYS */; INSERT INTO `node_agents` VALUES (1,1,'web-node-1','1.0.0','test-token',NULL,NULL,'2026-06-09 14:35:56',5.78,16.62,65.17,'5.77880859375,5.31103515625,5.86328125','online','2026-06-08 17:50:59','2026-06-09 14:35:56'); /*!40000 ALTER TABLE `node_agents` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `nodes` -- DROP TABLE IF EXISTS `nodes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `nodes` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `ip_address` varchar(45) NOT NULL, `ssh_user` varchar(100) DEFAULT 'myadmin', `status` enum('online','offline') DEFAULT 'online', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `nodes` -- LOCK TABLES `nodes` WRITE; /*!40000 ALTER TABLE `nodes` DISABLE KEYS */; INSERT INTO `nodes` VALUES (1,'Web Node 1','192.168.1.204','myadmin','online','2026-03-01 12:33:45'); /*!40000 ALTER TABLE `nodes` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `notification_reads` -- DROP TABLE IF EXISTS `notification_reads`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `notification_reads` ( `read_id` bigint unsigned NOT NULL AUTO_INCREMENT, `notification_id` bigint unsigned NOT NULL, `user_id` int NOT NULL, `read_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`read_id`), UNIQUE KEY `uk_notification_user` (`notification_id`,`user_id`), KEY `idx_user` (`user_id`), CONSTRAINT `fk_notification_reads_notification` FOREIGN KEY (`notification_id`) REFERENCES `notifications` (`notification_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `notification_reads` -- LOCK TABLES `notification_reads` WRITE; /*!40000 ALTER TABLE `notification_reads` DISABLE KEYS */; /*!40000 ALTER TABLE `notification_reads` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `notifications` -- DROP TABLE IF EXISTS `notifications`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `notifications` ( `notification_id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `notification_type` enum('ticket','chat','billing','invoice','payment','deployment','backup','ssl','dns','mail','server','node','security','system','announcement','ai') NOT NULL, `title` varchar(255) NOT NULL, `message` text, `reference_type` varchar(50) DEFAULT NULL, `reference_id` bigint DEFAULT NULL, `priority` enum('low','medium','high','critical') DEFAULT 'medium', `action_url` varchar(500) DEFAULT NULL, `created_by` int DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`notification_id`), KEY `idx_user` (`user_id`), KEY `idx_type` (`notification_type`), KEY `idx_created` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `notifications` -- LOCK TABLES `notifications` WRITE; /*!40000 ALTER TABLE `notifications` DISABLE KEYS */; /*!40000 ALTER TABLE `notifications` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `payments` -- DROP TABLE IF EXISTS `payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `payments` ( `id` int NOT NULL AUTO_INCREMENT, `invoice_id` int DEFAULT NULL, `payment_method` varchar(50) DEFAULT NULL, `transaction_id` varchar(150) DEFAULT NULL, `amount` decimal(10,2) DEFAULT NULL, `status` enum('success','failed','pending') DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `payments` -- LOCK TABLES `payments` WRITE; /*!40000 ALTER TABLE `payments` DISABLE KEYS */; /*!40000 ALTER TABLE `payments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `permissions` -- DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `permissions` ( `permission_id` int NOT NULL AUTO_INCREMENT, `permission_code` varchar(100) DEFAULT NULL, PRIMARY KEY (`permission_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `permissions` -- LOCK TABLES `permissions` WRITE; /*!40000 ALTER TABLE `permissions` DISABLE KEYS */; INSERT INTO `permissions` VALUES (1,'server.view'),(2,'server.manage'),(3,'node.view'),(4,'node.manage'),(5,'billing.view'),(6,'billing.manage'),(7,'ticket.view'),(8,'ticket.reply'),(9,'website.view'),(10,'website.manage'); /*!40000 ALTER TABLE `permissions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `plans` -- DROP TABLE IF EXISTS `plans`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `plans` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `price` decimal(10,2) NOT NULL, `max_hosting_accounts` int DEFAULT '1', `disk_quota_mb` int DEFAULT '1024', `inode_limit` int NOT NULL DEFAULT '50000', `max_mysql_dbs` int DEFAULT '1', `max_email_accounts` int DEFAULT '1', `max_domains` int DEFAULT '1', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `plans` -- LOCK TABLES `plans` WRITE; /*!40000 ALTER TABLE `plans` DISABLE KEYS */; INSERT INTO `plans` VALUES (1,'Starter',99.00,1,10240,50000,1,5,1,'2026-02-28 13:57:36'),(2,'Business',199.00,5,25600,50000,5,25,5,'2026-02-28 13:57:36'),(3,'Deluxe',299.00,20,51200,50000,20,100,20,'2026-02-28 13:57:36'),(4,'Pro',399.00,100,1048576,50000,100,100,100,'2026-03-13 07:33:26'),(5,'Enterprise',999.00,0,0,0,0,0,0,'2026-03-13 07:37:21'); /*!40000 ALTER TABLE `plans` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `products` -- DROP TABLE IF EXISTS `products`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `products` ( `product_id` int NOT NULL AUTO_INCREMENT, `product_code` varchar(50) DEFAULT NULL, `product_name` varchar(100) DEFAULT NULL, PRIMARY KEY (`product_id`), UNIQUE KEY `product_code` (`product_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `products` -- LOCK TABLES `products` WRITE; /*!40000 ALTER TABLE `products` DISABLE KEYS */; /*!40000 ALTER TABLE `products` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `provisioning_jobs` -- DROP TABLE IF EXISTS `provisioning_jobs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `provisioning_jobs` ( `id` bigint NOT NULL AUTO_INCREMENT, `server_id` int NOT NULL, `job_type` varchar(100) NOT NULL, `entity_type` varchar(50) NOT NULL, `entity_id` bigint DEFAULT NULL, `payload` json DEFAULT NULL, `status` enum('queued','processing','completed','failed') NOT NULL DEFAULT 'queued', `attempts` int NOT NULL DEFAULT '0', `result` longtext, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `started_at` datetime DEFAULT NULL, `completed_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_server` (`server_id`), KEY `idx_status` (`status`), KEY `idx_job_type` (`job_type`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `provisioning_jobs` -- LOCK TABLES `provisioning_jobs` WRITE; /*!40000 ALTER TABLE `provisioning_jobs` DISABLE KEYS */; INSERT INTO `provisioning_jobs` VALUES (2,1,'create_hosting','hosting_account',NULL,NULL,'failed',1,NULL,'2026-06-09 13:59:11',NULL,NULL),(3,1,'create_hosting','hosting_account',NULL,NULL,'failed',1,NULL,'2026-06-09 14:27:34',NULL,NULL),(4,1,'create_hosting','hosting_account',NULL,'{\"username\": \"rahul\"}','completed',0,'Completed successfully','2026-06-09 14:35:43','2026-06-09 14:35:57','2026-06-09 14:35:57'); /*!40000 ALTER TABLE `provisioning_jobs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `provisioning_logs` -- DROP TABLE IF EXISTS `provisioning_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `provisioning_logs` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int DEFAULT NULL, `action` varchar(100) DEFAULT NULL, `output` text, `status` enum('success','failed') DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `provisioning_logs` -- LOCK TABLES `provisioning_logs` WRITE; /*!40000 ALTER TABLE `provisioning_logs` DISABLE KEYS */; INSERT INTO `provisioning_logs` VALUES (1,1,'create','Enabling site client4.local.\nTo activate the new configuration, you need to run:\n systemctl reload apache2','success','2026-02-28 11:05:12'),(2,1,'create','Site client4.local already enabled','success','2026-02-28 11:34:24'),(3,2,'create','Enabling site client5.\nTo activate the new configuration, you need to run:\n systemctl reload apache2','success','2026-02-28 11:40:08'),(4,1,'suspend','','success','2026-02-28 12:46:06'),(5,1,'suspend','','success','2026-02-28 12:46:13'),(6,1,'unsuspend','usermod: unlocking the user\'s password would result in a passwordless account.\nYou should set a password with usermod -p to unlock this user\'s password.','success','2026-02-28 13:18:01'),(7,1,'unsuspend','usermod: unlocking the user\'s password would result in a passwordless account.\nYou should set a password with usermod -p to unlock this user\'s password.\nchown: cannot access \'/home/client4/public_html\': No such file or directory','failed','2026-02-28 13:19:01'),(8,1,'unsuspend','usermod: unlocking the user\'s password would result in a passwordless account.\nYou should set a password with usermod -p to unlock this user\'s password.\nchown: cannot access \'/home/client4/public_html\': No such file or directory','failed','2026-02-28 13:20:02'),(9,2,'suspend','','success','2026-02-28 13:21:02'),(10,1,'unsuspend','usermod: unlocking the user\'s password would result in a passwordless account.\nYou should set a password with usermod -p to unlock this user\'s password.\nchown: cannot access \'/home/client4/public_html\': No such file or directory','failed','2026-02-28 13:21:19'),(11,2,'unsuspend','usermod: unlocking the user\'s password would result in a passwordless account.\nYou should set a password with usermod -p to unlock this user\'s password.','success','2026-02-28 13:41:02'),(12,3,'create','Enabling site client5.com.\nTo activate the new configuration, you need to run:\n systemctl reload apache2','success','2026-02-28 14:50:03'),(13,4,'create','Enabling site client6.com.\nTo activate the new configuration, you need to run:\n systemctl reload apache2','success','2026-02-28 15:19:02'); /*!40000 ALTER TABLE `provisioning_logs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `redirects` -- DROP TABLE IF EXISTS `redirects`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `redirects` ( `id` int NOT NULL AUTO_INCREMENT, `domain_id` int DEFAULT NULL, `path` varchar(255) DEFAULT NULL, `target_url` varchar(500) DEFAULT NULL, `redirect_type` int DEFAULT '301', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `wildcard` tinyint DEFAULT '0', `priority` int DEFAULT '10', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `redirects` -- LOCK TABLES `redirects` WRITE; /*!40000 ALTER TABLE `redirects` DISABLE KEYS */; /*!40000 ALTER TABLE `redirects` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `reseller_plans` -- DROP TABLE IF EXISTS `reseller_plans`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `reseller_plans` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `disk` int DEFAULT NULL, `bandwidth` int DEFAULT NULL, `max_accounts` int DEFAULT NULL, `vps_limit` int DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `billing_cycle` enum('monthly','yearly') DEFAULT NULL, `status` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `reseller_plans` -- LOCK TABLES `reseller_plans` WRITE; /*!40000 ALTER TABLE `reseller_plans` DISABLE KEYS */; /*!40000 ALTER TABLE `reseller_plans` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `reseller_subscriptions` -- DROP TABLE IF EXISTS `reseller_subscriptions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `reseller_subscriptions` ( `id` int NOT NULL AUTO_INCREMENT, `reseller_id` int DEFAULT NULL, `plan_id` int DEFAULT NULL, `start_date` date DEFAULT NULL, `next_renewal` date DEFAULT NULL, `status` enum('active','suspended','cancelled') DEFAULT 'active', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `reseller_subscriptions` -- LOCK TABLES `reseller_subscriptions` WRITE; /*!40000 ALTER TABLE `reseller_subscriptions` DISABLE KEYS */; /*!40000 ALTER TABLE `reseller_subscriptions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `reseller_wallet` -- DROP TABLE IF EXISTS `reseller_wallet`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `reseller_wallet` ( `reseller_id` int NOT NULL, `disk_total` int DEFAULT '0', `disk_used` int DEFAULT '0', `bandwidth_total` int DEFAULT '0', `bandwidth_used` int DEFAULT '0', `max_accounts` int DEFAULT '0', `accounts_used` int DEFAULT '0', `vps_limit` int DEFAULT '0', `vps_used` int DEFAULT '0', `status` enum('active','suspended') DEFAULT 'active', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`reseller_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `reseller_wallet` -- LOCK TABLES `reseller_wallet` WRITE; /*!40000 ALTER TABLE `reseller_wallet` DISABLE KEYS */; /*!40000 ALTER TABLE `reseller_wallet` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `role_permissions` -- DROP TABLE IF EXISTS `role_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `role_permissions` ( `role_id` int DEFAULT NULL, `permission_id` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `role_permissions` -- LOCK TABLES `role_permissions` WRITE; /*!40000 ALTER TABLE `role_permissions` DISABLE KEYS */; /*!40000 ALTER TABLE `role_permissions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `roles` -- DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `roles` ( `role_id` int NOT NULL AUTO_INCREMENT, `role_name` varchar(100) DEFAULT NULL, PRIMARY KEY (`role_id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `roles` -- LOCK TABLES `roles` WRITE; /*!40000 ALTER TABLE `roles` DISABLE KEYS */; INSERT INTO `roles` VALUES (1,'super_admin'),(2,'infra_admin'),(3,'hosting_admin'),(4,'billing_admin'),(5,'support_admin'),(6,'sales_admin'),(7,'reseller'),(8,'client'); /*!40000 ALTER TABLE `roles` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `server_stats` -- DROP TABLE IF EXISTS `server_stats`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `server_stats` ( `id` int NOT NULL AUTO_INCREMENT, `server_id` int DEFAULT NULL, `cpu_usage` float DEFAULT NULL, `ram_usage` float DEFAULT NULL, `disk_usage` float DEFAULT NULL, `recorded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `server_stats` -- LOCK TABLES `server_stats` WRITE; /*!40000 ALTER TABLE `server_stats` DISABLE KEYS */; /*!40000 ALTER TABLE `server_stats` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `servers` -- DROP TABLE IF EXISTS `servers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `servers` ( `id` int NOT NULL AUTO_INCREMENT, `server_name` varchar(50) DEFAULT NULL, `ip_address` varchar(50) DEFAULT NULL, `role` enum('web','db','dns','mail') DEFAULT NULL, `ssh_user` varchar(50) DEFAULT NULL, `ssh_port` int DEFAULT '22', `max_accounts` int DEFAULT '500', `current_accounts` int DEFAULT '0', `status` enum('active','disabled') DEFAULT 'active', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `servers` -- LOCK TABLES `servers` WRITE; /*!40000 ALTER TABLE `servers` DISABLE KEYS */; INSERT INTO `servers` VALUES (1,'web-node-1','192.168.1.204','web','myadmin',22,500,5,'active','2026-03-05 20:38:18'),(2,'db-node-1','192.168.1.201','db','myadmin',22,500,0,'active','2026-03-05 20:38:18'),(3,'mailnode1','192.168.1.205','mail','myadmin',22,500,0,'active','2026-03-06 19:24:19'),(4,'dns-node-1','192.168.1.206','dns','myadmin',22,500,0,'active','2026-03-07 05:38:16'),(5,'dns-node-2','192.168.1.207','dns','myadmin',22,500,0,'active','2026-03-08 20:04:19'),(6,'web-node-2','192.168.1.208','web','myadmin',22,500,1,'active','2026-03-08 22:20:10'); /*!40000 ALTER TABLE `servers` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ssl_certificates` -- DROP TABLE IF EXISTS `ssl_certificates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `ssl_certificates` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `domain_id` int NOT NULL, `provider` enum('letsencrypt','sectigo','digicert','geotrust','globalsign','godaddy','custom') DEFAULT 'custom', `certificate_type` enum('single','wildcard','multi_domain') DEFAULT 'single', `certificate_text` longtext NOT NULL, `private_key` longtext NOT NULL, `ca_bundle` longtext, `serial_number` varchar(255) DEFAULT NULL, `issued_at` datetime DEFAULT NULL, `expires_at` datetime DEFAULT NULL, `auto_renew` tinyint(1) DEFAULT '0', `status` enum('active','expired','revoked','pending') DEFAULT 'active', `installed_on_nodes` text, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_hosting` (`hosting_id`), KEY `idx_domain` (`domain_id`), KEY `idx_expiry` (`expires_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ssl_certificates` -- LOCK TABLES `ssl_certificates` WRITE; /*!40000 ALTER TABLE `ssl_certificates` DISABLE KEYS */; /*!40000 ALTER TABLE `ssl_certificates` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `subscriptions` -- DROP TABLE IF EXISTS `subscriptions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `subscriptions` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int DEFAULT NULL, `plan_id` int DEFAULT NULL, `plan_type` enum('hosting','reseller','vps') DEFAULT NULL, `start_date` date DEFAULT NULL, `next_renewal` date DEFAULT NULL, `status` enum('active','suspended','cancelled') DEFAULT 'active', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `subscriptions` -- LOCK TABLES `subscriptions` WRITE; /*!40000 ALTER TABLE `subscriptions` DISABLE KEYS */; INSERT INTO `subscriptions` VALUES (1,6,1,'hosting','2026-03-09','2026-04-09','active'),(2,7,2,'hosting','2026-03-09','2026-04-09','active'),(3,8,2,'hosting','2026-03-09','2026-04-09','active'); /*!40000 ALTER TABLE `subscriptions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `support_attachments` -- DROP TABLE IF EXISTS `support_attachments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `support_attachments` ( `attachment_id` bigint NOT NULL AUTO_INCREMENT, `ticket_id` bigint NOT NULL, `message_id` bigint DEFAULT NULL, `file_name` varchar(255) DEFAULT NULL, `file_path` varchar(500) DEFAULT NULL, `uploaded_by` int DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`attachment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `support_attachments` -- LOCK TABLES `support_attachments` WRITE; /*!40000 ALTER TABLE `support_attachments` DISABLE KEYS */; /*!40000 ALTER TABLE `support_attachments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `support_chat_attachments` -- DROP TABLE IF EXISTS `support_chat_attachments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `support_chat_attachments` ( `attachment_id` bigint NOT NULL AUTO_INCREMENT, `session_id` bigint DEFAULT NULL, `message_id` bigint DEFAULT NULL, `file_name` varchar(255) DEFAULT NULL, `file_path` varchar(500) DEFAULT NULL, `uploaded_by` int DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`attachment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `support_chat_attachments` -- LOCK TABLES `support_chat_attachments` WRITE; /*!40000 ALTER TABLE `support_chat_attachments` DISABLE KEYS */; /*!40000 ALTER TABLE `support_chat_attachments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `support_chat_messages` -- DROP TABLE IF EXISTS `support_chat_messages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `support_chat_messages` ( `message_id` bigint NOT NULL AUTO_INCREMENT, `session_id` bigint NOT NULL, `sender_id` int NOT NULL, `sender_type` enum('client','reseller','staff','admin','ai') DEFAULT NULL, `message` longtext, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`message_id`), KEY `session_id` (`session_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `support_chat_messages` -- LOCK TABLES `support_chat_messages` WRITE; /*!40000 ALTER TABLE `support_chat_messages` DISABLE KEYS */; /*!40000 ALTER TABLE `support_chat_messages` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `support_chat_sessions` -- DROP TABLE IF EXISTS `support_chat_sessions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `support_chat_sessions` ( `session_id` bigint NOT NULL AUTO_INCREMENT, `session_no` varchar(30) DEFAULT NULL, `client_id` int DEFAULT NULL, `reseller_id` int DEFAULT NULL, `assigned_to` int DEFAULT NULL, `department` enum('support','hosting','billing','infrastructure','sales') DEFAULT NULL, `status` enum('waiting','active','closed') DEFAULT 'waiting', `started_at` datetime DEFAULT NULL, `ended_at` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`session_id`), UNIQUE KEY `session_no` (`session_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `support_chat_sessions` -- LOCK TABLES `support_chat_sessions` WRITE; /*!40000 ALTER TABLE `support_chat_sessions` DISABLE KEYS */; /*!40000 ALTER TABLE `support_chat_sessions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `support_messages` -- DROP TABLE IF EXISTS `support_messages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `support_messages` ( `message_id` bigint NOT NULL AUTO_INCREMENT, `ticket_id` bigint NOT NULL, `sender_id` int NOT NULL, `is_internal` tinyint(1) DEFAULT '0', `message` longtext, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`message_id`), KEY `ticket_id` (`ticket_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `support_messages` -- LOCK TABLES `support_messages` WRITE; /*!40000 ALTER TABLE `support_messages` DISABLE KEYS */; /*!40000 ALTER TABLE `support_messages` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `support_tickets` -- DROP TABLE IF EXISTS `support_tickets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `support_tickets` ( `ticket_id` bigint NOT NULL AUTO_INCREMENT, `ticket_no` varchar(30) DEFAULT NULL, `client_id` int DEFAULT NULL, `reseller_id` int DEFAULT NULL, `subject` varchar(255) NOT NULL, `priority` enum('low','medium','high','critical') DEFAULT 'medium', `department` enum('support','hosting','billing','infrastructure','sales') DEFAULT 'support', `status` enum('open','assigned','waiting_customer','resolved','closed') DEFAULT 'open', `assigned_to` int DEFAULT NULL, `created_by` int NOT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`ticket_id`), UNIQUE KEY `ticket_no` (`ticket_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `support_tickets` -- LOCK TABLES `support_tickets` WRITE; /*!40000 ALTER TABLE `support_tickets` DISABLE KEYS */; /*!40000 ALTER TABLE `support_tickets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `system_settings` -- DROP TABLE IF EXISTS `system_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `system_settings` ( `setting_key` varchar(100) NOT NULL, `setting_value` text, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`setting_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `system_settings` -- LOCK TABLES `system_settings` WRITE; /*!40000 ALTER TABLE `system_settings` DISABLE KEYS */; INSERT INTO `system_settings` VALUES ('api_url','https://coredatahub.co.in/api','2026-06-07 18:12:56','Primary API URL'),('app_name','CoreDataHub Studio','2026-06-07 18:13:56',NULL),('app_version','1.0.0','2026-06-07 18:13:56',NULL),('default_timezone','Asia/Kolkata','2026-06-07 18:13:56',NULL),('dns_node_1','192.168.1.206','2026-06-07 18:12:56','Primary DNS Server'),('dns_node_2','192.168.1.207','2026-06-07 18:12:56','Secondary DNS Server'),('load_balancer','192.168.1.210','2026-06-07 18:12:56','HAProxy Load Balancer'),('mail_node','192.168.1.205','2026-06-07 18:12:56','Mail Server'),('mysql_server','192.168.1.201','2026-06-07 18:13:56',NULL),('panel_url','https://cpane.coredatahub.co.in','2026-06-07 18:12:56','Primary control panel URL'),('public_ip','115.187.41.162','2026-06-07 18:12:56','Datacenter public IP'),('support_email','support@coredatahub.co.in','2026-06-07 18:13:56',NULL),('web_node_1','192.168.1.204','2026-06-07 18:12:56','Web Hosting Node 1'),('web_node_2','192.168.1.208','2026-06-07 18:12:56','Web Hosting Node 2'); /*!40000 ALTER TABLE `system_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `terminal_commands` -- DROP TABLE IF EXISTS `terminal_commands`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `terminal_commands` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `session_id` bigint unsigned NOT NULL, `command_text` text NOT NULL, `execution_result` longtext, `exit_code` int DEFAULT '0', `execution_time_ms` int DEFAULT '0', `executed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_session` (`session_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `terminal_commands` -- LOCK TABLES `terminal_commands` WRITE; /*!40000 ALTER TABLE `terminal_commands` DISABLE KEYS */; /*!40000 ALTER TABLE `terminal_commands` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `terminal_sessions` -- DROP TABLE IF EXISTS `terminal_sessions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `terminal_sessions` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `user_id` int NOT NULL, `server_id` int NOT NULL, `session_token` varchar(255) DEFAULT NULL, `terminal_type` enum('restricted','developer','administrator') DEFAULT 'restricted', `start_directory` varchar(500) DEFAULT NULL, `started_at` datetime NOT NULL, `ended_at` datetime DEFAULT NULL, `status` enum('active','closed','terminated') DEFAULT 'active', `client_ip` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `session_token` (`session_token`), KEY `idx_hosting` (`hosting_id`), KEY `idx_user` (`user_id`), KEY `idx_server` (`server_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `terminal_sessions` -- LOCK TABLES `terminal_sessions` WRITE; /*!40000 ALTER TABLE `terminal_sessions` DISABLE KEYS */; /*!40000 ALTER TABLE `terminal_sessions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `usage_alerts` -- DROP TABLE IF EXISTS `usage_alerts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `usage_alerts` ( `id` int NOT NULL AUTO_INCREMENT, `hosting_id` int NOT NULL, `type` enum('disk','inode','bandwidth') NOT NULL, `usage_percent` int NOT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `usage_alerts` -- LOCK TABLES `usage_alerts` WRITE; /*!40000 ALTER TABLE `usage_alerts` DISABLE KEYS */; /*!40000 ALTER TABLE `usage_alerts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user_permissions` -- DROP TABLE IF EXISTS `user_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user_permissions` ( `user_permission_id` bigint NOT NULL AUTO_INCREMENT, `user_id` bigint NOT NULL, `permission_id` bigint NOT NULL, `granted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_permission_id`), UNIQUE KEY `uk_user_permission` (`user_id`,`permission_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user_permissions` -- LOCK TABLES `user_permissions` WRITE; /*!40000 ALTER TABLE `user_permissions` DISABLE KEYS */; /*!40000 ALTER TABLE `user_permissions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user_products` -- DROP TABLE IF EXISTS `user_products`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user_products` ( `user_id` int NOT NULL, `product_id` int NOT NULL, PRIMARY KEY (`user_id`,`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user_products` -- LOCK TABLES `user_products` WRITE; /*!40000 ALTER TABLE `user_products` DISABLE KEYS */; /*!40000 ALTER TABLE `user_products` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user_roles` -- DROP TABLE IF EXISTS `user_roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user_roles` ( `user_role_id` bigint NOT NULL AUTO_INCREMENT, `user_id` bigint NOT NULL, `role_id` bigint NOT NULL, `assigned_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_role_id`), UNIQUE KEY `uk_user_role` (`user_id`,`role_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user_roles` -- LOCK TABLES `user_roles` WRITE; /*!40000 ALTER TABLE `user_roles` DISABLE KEYS */; /*!40000 ALTER TABLE `user_roles` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `first_name` varchar(100) DEFAULT NULL, `last_name` varchar(100) DEFAULT NULL, `full_name` varchar(150) NOT NULL, `company_name` varchar(150) NOT NULL, `street_address` varchar(255) NOT NULL, `city` varchar(100) NOT NULL, `state` varchar(100) NOT NULL, `pincode` varchar(10) NOT NULL, `country` varchar(100) DEFAULT NULL, `phone` varchar(20) NOT NULL, `gstno` varchar(20) DEFAULT NULL, `email` varchar(150) NOT NULL, `password` varchar(255) NOT NULL, `role` enum('admin','reseller','client') DEFAULT 'client', `reseller_id` int DEFAULT NULL, `is_active` tinyint(1) DEFAULT '1', `twofa_code` varchar(10) DEFAULT NULL, `twofa_expiry` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `plan_id` int NOT NULL DEFAULT '1', `customer_code` varchar(20) DEFAULT NULL, `linux_username` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), UNIQUE KEY `customer_code` (`customer_code`), UNIQUE KEY `linux_username` (`linux_username`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `users` -- LOCK TABLES `users` WRITE; /*!40000 ALTER TABLE `users` DISABLE KEYS */; INSERT INTO `users` VALUES (1,NULL,NULL,'Rahul Das','CoreDataHub','HQ','KOLKATA','WEST BENGAL','700051',NULL,'+91 98303 15678',NULL,'rd538707@gmail.com','$2y$10$KbVvpsEvwD5fOGbRjEsh7eExjACQ.vX7jud9afliLEZQpD4m8FzY6','client',NULL,1,NULL,NULL,'2026-02-27 17:56:49',2,'CDH000001','cdh00001'),(2,NULL,NULL,'Admin User','CoreDataHub','Anjangarh, Birati','Kolkata','West Bengal','700051',NULL,'+91 9830315678',NULL,'admin@coredatahub.co.in','$2y$10$O6u7uEjNztwU7X4wq7pEpuodrPGTv1nVxKABXVV/uXbFTn65s6g8G','admin',NULL,1,NULL,NULL,'2026-02-28 19:34:23',1,'CDH000002','cdh00002'),(6,NULL,NULL,'Rahul Das','','61/1 Anjangarh, Birati','Kolkata','West Bengal','700051',NULL,'9830315678','','advssys@gmail.com','$2y$10$mN12YYk548RlQ7vdSSyVi.67ICTONxmfs6XvQnicH1PF2XDQJR8iK','client',NULL,1,NULL,NULL,'2026-03-08 19:13:27',1,'CDH000006','cdh00006'),(7,NULL,NULL,'Navin','','Anjangarh','Kolkata','West Bengal','700051',NULL,'9830315678','','admin@ikc2corp.in','$2y$10$rkB.XwSzkzIHtz/BIob7NupP66Sj1O6CzfJXr1HSAPuSx0sv7k3VG','client',NULL,1,NULL,NULL,'2026-03-08 21:40:38',2,'CDH000007','cdh00007'),(8,NULL,NULL,'V D','','Anjangarh','Kolkata','West Bengal','700051',NULL,'9830315678','','vrush.dahake@gmail.com','$2y$10$sTyv.Q6SA2hlL9rI7BUSVuyC9HWdk62ZQ9XdswWZdyEn085nrmEzu','client',NULL,1,NULL,NULL,'2026-03-09 10:35:10',2,'CDH000008','cdh00008'); /*!40000 ALTER TABLE `users` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `websites` -- DROP TABLE IF EXISTS `websites`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `websites` ( `id` bigint NOT NULL AUTO_INCREMENT, `hosting_id` bigint NOT NULL, `domain_id` bigint NOT NULL, `server_id` bigint NOT NULL, `document_root` varchar(255) DEFAULT NULL, `php_version` varchar(20) DEFAULT NULL, `status` enum('active','suspended') DEFAULT 'active', `created_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `websites` -- LOCK TABLES `websites` WRITE; /*!40000 ALTER TABLE `websites` DISABLE KEYS */; /*!40000 ALTER TABLE `websites` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2026-06-10 7:41:52 ________________________________________ Agent Architecture Agent Path: /opt/coredatahub-agent Structure: /opt/coredatahub-agent ├── agent.php ├── config.php ├── lib/ ├── logs/ └── scripts/ Current Components: • ApiClient.php • MetricsCollector.php • JobProcessor.php ________________________________________ Agent APIs Panel Path: /var/www/coredatahub-site/api/agent/ Implemented: • heartbeat.php • get-jobs.php • job-complete.php • job-failed.php Optional: • register.php ________________________________________ Provisioning System Status Flow: queued → processing → completed or queued → processing → failed Table: provisioning_jobs Important Fields: • id • server_id • job_type • entity_type • entity_id • payload • status • attempts • result • created_at • started_at • completed_at ________________________________________ Hosting Architecture Design Philosophy: cPanel-style hosting architecture. Relationship: User → Hosting Account → Domains ________________________________________ users Important Fields: • id • role • customer_code • linux_username linux_username is authoritative. Do NOT duplicate linux_username in hosting_accounts. ________________________________________ hosting_accounts Represents: One hosting subscription. Contains: • user_id • plan_id • web_server_id • mail_server_id • document_root • disk_quota_mb • bandwidth_quota_mb • php_version • status Suggested Statuses: pending provisioning active suspended terminated ________________________________________ domains Relationship: domains.hosting_id → hosting_accounts.id Domain Types: • primary • addon • parked • subdomain Structure Example: Hosting Account ↓ Primary Domain ↓ Addon Domains ↓ Subdomains ________________________________________ hosting_plans Contains: • name • disk_mb • bandwidth_mb • max_domains • max_databases • max_email_accounts • price • billing_cycle ________________________________________ Hosting Filesystem Authoritative Model: /hosting/linux_username/ Example: /hosting/cdh0007/ Contains: public_html domains logs mail backups ssl Example: /hosting/cdh0007/ ├── public_html ├── domains/ ├── logs/ ├── backups/ ├── ssl/ └── mail/ ________________________________________ Provisioning Workflow Create Hosting ↓ hosting_accounts ↓ domains ↓ provisioning_jobs ↓ Agent ↓ Filesystem Provisioning ↓ hosting_accounts.status='active' ________________________________________ Current Milestones Completed: ✓ JWT Authentication ✓ Dynamic Menu ✓ Role & Permission System ✓ Hosting Plans ✓ Hosting Accounts ✓ Domains ✓ Agent Registration ✓ Heartbeat System ✓ Job Queue ✓ Automatic Job Pickup ✓ Automatic Folder Provisioning ✓ Job Completion Tracking Current Focus: HostingService::createAccount() Future: • Apache Virtual Hosts • DNS Automation • SSL Automation • Mail Automation • FTP Automation • Backup Automation ________________________________________ Development Rules 1. Use queue-based provisioning. Never run long provisioning tasks directly from UI requests. 2. Agent performs infrastructure work. Panel performs orchestration. 3. users.linux_username is the authoritative Linux account identifier. 4. Domains belong to Hosting Accounts. 5. Hosting Accounts belong to Users. 6. Agent provisioning should use: entity_type='hosting_account' entity_id= instead of relying on payload data whenever possible. 7. Prefer normalized database design over duplicated data.