107 lines
3.6 KiB
SQL
107 lines
3.6 KiB
SQL
CREATE TABLE IF NOT EXISTS teams (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
color VARCHAR(7) DEFAULT '#0d6efd',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
team_id INT NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
severity ENUM('info','low','medium','high','critical') DEFAULT 'info',
|
|
event_type VARCHAR(50) DEFAULT 'general',
|
|
occurred_at DATETIME NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
event_id INT NOT NULL,
|
|
author VARCHAR(100) NOT NULL,
|
|
body TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS network_nodes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
label VARCHAR(255) NOT NULL,
|
|
ip_address VARCHAR(45),
|
|
node_type ENUM('host','server','router','firewall','switch','cloud','endpoint','other') DEFAULT 'host',
|
|
status ENUM('online','offline','unknown','compromised','monitoring') DEFAULT 'unknown',
|
|
group_name VARCHAR(100) DEFAULT 'default',
|
|
notes VARCHAR(1000) DEFAULT '',
|
|
pos_x FLOAT DEFAULT 0,
|
|
pos_y FLOAT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS network_links (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
source_id INT NOT NULL,
|
|
target_id INT NOT NULL,
|
|
link_type ENUM('direct','vpn','wireless','monitored') DEFAULT 'direct',
|
|
label VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (source_id) REFERENCES network_nodes(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (target_id) REFERENCES network_nodes(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS network_shapes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
label VARCHAR(255) NOT NULL DEFAULT '',
|
|
shape_type ENUM('rectangle','ellipse','zone') DEFAULT 'rectangle',
|
|
pos_x FLOAT DEFAULT 0,
|
|
pos_y FLOAT DEFAULT 0,
|
|
width FLOAT DEFAULT 200,
|
|
height FLOAT DEFAULT 150,
|
|
color VARCHAR(7) DEFAULT '#1e3a5f',
|
|
border_color VARCHAR(7) DEFAULT '#3b82f6',
|
|
opacity FLOAT DEFAULT 0.15,
|
|
z_index INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS documents (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
doc_type VARCHAR(50) NOT NULL,
|
|
team_id INT NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT,
|
|
occurred_at DATETIME NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS file_attachments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
event_id INT NOT NULL,
|
|
original_name VARCHAR(255) NOT NULL,
|
|
stored_name VARCHAR(255) NOT NULL,
|
|
mime_type VARCHAR(100),
|
|
file_size INT,
|
|
uploaded_by VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS event_tags (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
event_id INT NOT NULL,
|
|
tag VARCHAR(50) NOT NULL,
|
|
UNIQUE KEY unique_event_tag (event_id, tag),
|
|
FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
|
|
);
|
|
|
|
INSERT IGNORE INTO teams (name, color) VALUES
|
|
('Blue Team', '#0d6efd'),
|
|
('Red Team', '#dc3545'),
|
|
('SOC', '#ffc107'),
|
|
('Threat Intel', '#198754');
|
|
|
|
-- Ensure the neptune user has all privileges on neptune database
|
|
GRANT ALL PRIVILEGES ON neptune.* TO 'neptune'@'%';
|
|
FLUSH PRIVILEGES; |