-- ===================================================================== -- ARCA Hosting — Esquema de Base de Datos (MySQL 8) -- Fase 0/1: núcleo, auth, auditoría, multi-tenant base y clientes. -- Las tablas de fases futuras (dominios, hosting, facturación, etc.) -- se incluyen ya para que las relaciones (FK) queden estables desde el -- inicio y no requieran migraciones destructivas más adelante. -- ===================================================================== SET NAMES utf8mb4; SET time_zone = '+00:00'; CREATE DATABASE IF NOT EXISTS arca_hosting CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE arca_hosting; -- --------------------------------------------------------------------- -- MULTI-TENANT: empresas/marcas que operan sobre la misma instalación -- --------------------------------------------------------------------- CREATE TABLE companies ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, legal_name VARCHAR(200) NULL, tax_id VARCHAR(50) NULL COMMENT 'RFC', email VARCHAR(150) NULL, phone VARCHAR(30) NULL, currency CHAR(3) NOT NULL DEFAULT 'MXN', timezone VARCHAR(60) NOT NULL DEFAULT 'America/Mexico_City', logo_path VARCHAR(255) NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- USUARIOS DEL PANEL (staff), ROLES Y PERMISOS -- --------------------------------------------------------------------- CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, two_factor_secret VARCHAR(255) NULL, two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0, status ENUM('active','suspended','disabled') NOT NULL DEFAULT 'active', failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME NULL, last_login_at DATETIME NULL, last_login_ip VARCHAR(45) NULL, must_change_password TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE roles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL UNIQUE, description VARCHAR(255) NULL ) ENGINE=InnoDB; CREATE TABLE permissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `key` VARCHAR(100) NOT NULL UNIQUE COMMENT 'ej. clients.create, invoices.void', label VARCHAR(150) NOT NULL ) ENGINE=InnoDB; CREATE TABLE role_permission ( role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (role_id, permission_id), FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE, FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE company_user ( company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (company_id, user_id), FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT ) ENGINE=InnoDB; -- Control de intentos de acceso (fuerza bruta) independiente del usuario -- (permite bloquear también por IP aunque el email no exista) CREATE TABLE login_attempts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(150) NOT NULL, ip_address VARCHAR(45) NOT NULL, success TINYINT(1) NOT NULL, user_agent VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_login_attempts_email (email, created_at), INDEX idx_login_attempts_ip (ip_address, created_at) ) ENGINE=InnoDB; -- Bitácora general de todo el sistema (append-only) CREATE TABLE audit_log ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL COMMENT 'ej. client.created, invoice.voided', entity_type VARCHAR(80) NULL, entity_id BIGINT UNSIGNED NULL, ip_address VARCHAR(45) NULL, details_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_audit_company (company_id, created_at), INDEX idx_audit_entity (entity_type, entity_id), FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- CLIENTES (Fase 1 — activo hoy) -- --------------------------------------------------------------------- CREATE TABLE clients ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, company_name VARCHAR(150) NULL COMMENT 'Empresa del cliente', legal_name VARCHAR(200) NULL COMMENT 'Razón social', tax_id VARCHAR(50) NULL COMMENT 'RFC', contact_name VARCHAR(150) NOT NULL COMMENT 'Representante/contacto', email VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, whatsapp VARCHAR(30) NULL, address VARCHAR(255) NULL, city VARCHAR(100) NULL, state VARCHAR(100) NULL, country VARCHAR(100) NULL DEFAULT 'México', postal_code VARCHAR(15) NULL, notes TEXT NULL, status ENUM('active','suspended','expired','cancelled') NOT NULL DEFAULT 'active', password_hash VARCHAR(255) NULL COMMENT 'Acceso al portal de cliente (fase 8)', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL COMMENT 'Soft delete', FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_clients_company (company_id), INDEX idx_clients_status (status), INDEX idx_clients_email (email) ) ENGINE=InnoDB; CREATE TABLE client_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL, file_name VARCHAR(255) NOT NULL, file_path VARCHAR(255) NOT NULL, uploaded_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- SERVIDORES E INFRAESTRUCTURA (activado en Fase 4, tabla ya definida) -- --------------------------------------------------------------------- CREATE TABLE servers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, hostname VARCHAR(150) NOT NULL, ip_address VARCHAR(45) NOT NULL, provider_type ENUM('cpanel_whm','resellerpanel','plesk','other') NOT NULL DEFAULT 'cpanel_whm', api_credentials_encrypted TEXT NULL COMMENT 'cifrado con libsodium', status ENUM('online','offline','maintenance') NOT NULL DEFAULT 'online', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE server_metrics ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, server_id BIGINT UNSIGNED NOT NULL, cpu_percent DECIMAL(5,2) NULL, ram_percent DECIMAL(5,2) NULL, disk_percent DECIMAL(5,2) NULL, is_reachable TINYINT(1) NOT NULL DEFAULT 1, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE, INDEX idx_metrics_server_time (server_id, recorded_at) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- DOMINIOS (Fase 3) -- --------------------------------------------------------------------- CREATE TABLE domains ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, domain_name VARCHAR(190) NOT NULL, tld VARCHAR(30) NOT NULL, registrar VARCHAR(80) NULL, registered_at DATE NULL, expires_at DATE NULL, auto_renew TINYINT(1) NOT NULL DEFAULT 1, nameservers JSON NULL, dns_provider ENUM('cloudflare','other') NULL DEFAULT 'cloudflare', ssl_status ENUM('none','pending','active','expired') NOT NULL DEFAULT 'none', cost DECIMAL(10,2) NULL, price DECIMAL(10,2) NULL, status ENUM('active','expired','pending_transfer','cancelled') NOT NULL DEFAULT 'active', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_domain (domain_name), FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- SERVICIOS / HOSTING (Fase 2 y 4) -- --------------------------------------------------------------------- CREATE TABLE service_plans ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, type ENUM('hosting','email','m365','gws','ssl','cloudflare','dev','maintenance', 'backup','consulting','mobile_app','domain') NOT NULL, price DECIMAL(10,2) NOT NULL, billing_cycle ENUM('monthly','quarterly','biannual','annual','one_time') NOT NULL DEFAULT 'monthly', FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE client_services ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, service_plan_id BIGINT UNSIGNED NOT NULL, server_id BIGINT UNSIGNED NULL, cpanel_username VARCHAR(60) NULL, disk_used_mb INT UNSIGNED NULL, bandwidth_used_mb INT UNSIGNED NULL, status ENUM('pending','active','suspended','terminated') NOT NULL DEFAULT 'pending', next_due_date DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY (service_plan_id) REFERENCES service_plans(id) ON DELETE RESTRICT, FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- FACTURACIÓN (Fase 5) + CFDI -- --------------------------------------------------------------------- CREATE TABLE invoices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, folio VARCHAR(30) NOT NULL, subtotal DECIMAL(12,2) NOT NULL, tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'MXN', status ENUM('draft','pending','paid','overdue','cancelled') NOT NULL DEFAULT 'draft', due_date DATE NOT NULL, cfdi_uuid VARCHAR(60) NULL COMMENT 'UUID de timbrado fiscal, si aplica', cfdi_xml_path VARCHAR(255) NULL, cfdi_pdf_path VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_invoice_folio (company_id, folio), FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE invoice_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(10,2) NOT NULL DEFAULT 1, unit_price DECIMAL(12,2) NOT NULL, total DECIMAL(12,2) NOT NULL, FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, invoice_id BIGINT UNSIGNED NOT NULL, gateway ENUM('stripe','paypal','mercadopago','openpay','manual') NOT NULL, gateway_reference VARCHAR(150) NULL, amount DECIMAL(12,2) NOT NULL, status ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending', paid_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- TICKETS DE SOPORTE (Fase 8) -- --------------------------------------------------------------------- CREATE TABLE tickets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, subject VARCHAR(200) NOT NULL, category VARCHAR(80) NULL, priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium', status ENUM('open','pending','answered','closed') NOT NULL DEFAULT 'open', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE ticket_messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ticket_id BIGINT UNSIGNED NOT NULL, author_type ENUM('client','staff') NOT NULL, author_id BIGINT UNSIGNED NOT NULL, message TEXT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- COLA DE TAREAS (jobs) — Fase 7 -- --------------------------------------------------------------------- CREATE TABLE jobs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, queue VARCHAR(60) NOT NULL DEFAULT 'default', job_type VARCHAR(100) NOT NULL COMMENT 'ej. SendInvoiceEmail, ProvisionHosting', payload_json JSON NOT NULL, attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending', available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_jobs_status_time (status, available_at) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- API KEYS (Fase 9) -- --------------------------------------------------------------------- CREATE TABLE api_keys ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, label VARCHAR(100) NOT NULL, key_hash VARCHAR(255) NOT NULL, scopes_json JSON NULL, last_used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Datos semilla mínimos para poder iniciar sesión hoy mismo -- --------------------------------------------------------------------- INSERT INTO companies (name, legal_name, currency, timezone) VALUES ('ARCA Hosting', 'ARCA Hosting S.A. de C.V.', 'MXN', 'America/Mexico_City'); INSERT INTO roles (name, description) VALUES ('superadmin', 'Acceso total al sistema'), ('admin', 'Administrador de la empresa'), ('support', 'Agente de soporte'); -- Password por defecto: "ArcaAdmin#2026" (DEBE cambiarse en el primer login) -- Hash generado con password_hash(..., PASSWORD_BCRYPT) INSERT INTO users (name, email, password_hash, must_change_password) VALUES ('Administrador', 'admin@arcahosting.mx', '$2y$10$tNpiOlJXs3z82agZpWfQvezfUXRLEL6YqsjLLzo1qLhyhxLyCNXIi', 1); INSERT INTO company_user (company_id, user_id, role_id) VALUES (1, 1, 1);