81 lines
3.2 KiB
SQL
81 lines
3.2 KiB
SQL
-- =============================================================================
|
|
-- DB 마이그레이션: B01_Dashboard 역할별 대시보드
|
|
-- 파일: 004_dashboard.sql
|
|
-- =============================================================================
|
|
|
|
USE aislo_db;
|
|
|
|
ALTER TABLE users
|
|
MODIFY role ENUM('SYSTEM_ADMIN', 'MASTER', 'MEMBER', 'ADMIN', 'USER') NOT NULL DEFAULT 'USER';
|
|
|
|
UPDATE users SET role = 'ADMIN' WHERE role = 'MASTER';
|
|
UPDATE users SET role = 'USER' WHERE role = 'MEMBER';
|
|
|
|
ALTER TABLE users
|
|
MODIFY role ENUM('SYSTEM_ADMIN', 'ADMIN', 'USER') NOT NULL DEFAULT 'USER',
|
|
MODIFY status ENUM('PENDING_EMAIL', 'NO_COMPANY', 'PENDING', 'ACTIVE', 'INACTIVE', 'REJECTED')
|
|
NOT NULL DEFAULT 'PENDING_EMAIL',
|
|
ADD COLUMN IF NOT EXISTS department VARCHAR(100) NULL AFTER position,
|
|
ADD COLUMN IF NOT EXISTS last_login DATETIME NULL AFTER status,
|
|
ADD COLUMN IF NOT EXISTS auth_expires_at DATETIME NULL AFTER last_login;
|
|
|
|
ALTER TABLE companies
|
|
ADD COLUMN IF NOT EXISTS created_by INT NULL AFTER business_status;
|
|
|
|
CREATE TABLE IF NOT EXISTS system_audit_logs (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INT NOT NULL,
|
|
action VARCHAR(50) NOT NULL,
|
|
resource_type VARCHAR(50) NULL,
|
|
resource_id INT NULL,
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
ip_address VARCHAR(50) NULL,
|
|
user_agent TEXT NULL,
|
|
INDEX idx_system_audit_logs_timestamp (timestamp),
|
|
INDEX idx_system_audit_logs_user_id (user_id),
|
|
INDEX idx_system_audit_logs_action (action),
|
|
CONSTRAINT fk_system_audit_logs_user_id FOREIGN KEY (user_id) REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS system_resources (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
cpu_usage_percent FLOAT NULL,
|
|
memory_usage_percent FLOAT NULL,
|
|
disk_usage_percent FLOAT NULL,
|
|
active_user_count INT NOT NULL DEFAULT 0,
|
|
active_project_count INT NOT NULL DEFAULT 0,
|
|
total_storage_mb FLOAT NOT NULL DEFAULT 0,
|
|
INDEX idx_system_resources_timestamp (timestamp)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_automations (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
project_id CHAR(36) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
logic_type VARCHAR(50) NOT NULL,
|
|
config_json JSON NOT NULL,
|
|
status ENUM('DRAFT', 'ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'DRAFT',
|
|
created_by INT NOT NULL,
|
|
updated_by INT NULL,
|
|
last_executed_at DATETIME NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL,
|
|
INDEX idx_project_automations_project_id (project_id),
|
|
INDEX idx_project_automations_status (status),
|
|
CONSTRAINT fk_project_automations_project_id FOREIGN KEY (project_id) REFERENCES projects(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_project_automations_created_by FOREIGN KEY (created_by) REFERENCES users(id)
|
|
ON DELETE RESTRICT,
|
|
CONSTRAINT fk_project_automations_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
|
|
ON DELETE SET NULL
|
|
);
|
|
|
|
ALTER TABLE join_requests
|
|
ADD COLUMN IF NOT EXISTS review_comment TEXT NULL AFTER reviewed_at;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
|
|
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
|