forked from Eeveid/lightOps
69 lines
2.6 KiB
SQL
69 lines
2.6 KiB
SQL
CREATE TABLE IF NOT EXISTS user_permissions (
|
|
user_id INTEGER NOT NULL,
|
|
permission TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
PRIMARY KEY(user_id, permission),
|
|
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS agent_access (
|
|
user_id INTEGER NOT NULL,
|
|
agent_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
PRIMARY KEY(user_id, agent_id),
|
|
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY(agent_id) REFERENCES agents(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS alert_rules (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
metric TEXT NOT NULL,
|
|
operator TEXT NOT NULL DEFAULT '>=',
|
|
threshold REAL NOT NULL,
|
|
duration_seconds INTEGER NOT NULL DEFAULT 0,
|
|
severity TEXT NOT NULL DEFAULT 'warning',
|
|
agent_id TEXT,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY(agent_id) REFERENCES agents(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_alert_rules_enabled ON alert_rules(enabled);
|
|
CREATE INDEX IF NOT EXISTS idx_alert_rules_agent ON alert_rules(agent_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS alert_events (
|
|
id TEXT PRIMARY KEY,
|
|
rule_id TEXT NOT NULL,
|
|
agent_id TEXT NOT NULL,
|
|
metric TEXT NOT NULL,
|
|
value REAL NOT NULL,
|
|
threshold REAL NOT NULL,
|
|
severity TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
message TEXT NOT NULL,
|
|
first_seen_at TEXT NOT NULL,
|
|
last_seen_at TEXT NOT NULL,
|
|
resolved_at TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY(rule_id) REFERENCES alert_rules(id) ON DELETE CASCADE,
|
|
FOREIGN KEY(agent_id) REFERENCES agents(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_alert_events_status ON alert_events(status, last_seen_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_alert_events_agent ON alert_events(agent_id, last_seen_at DESC);
|
|
|
|
INSERT OR IGNORE INTO settings(key, value) VALUES
|
|
('security.terminal_enabled', 'true'),
|
|
('security.file_write_enabled', 'true'),
|
|
('security.require_danger_confirm', 'true'),
|
|
('agent.offline_after_seconds', '120'),
|
|
('metrics.retention_days', '30'),
|
|
('alerts.enabled', 'true');
|
|
|
|
INSERT OR IGNORE INTO alert_rules(id, name, metric, operator, threshold, severity) VALUES
|
|
('builtin-cpu-high', 'CPU 使用率过高', 'cpu_usage', '>=', 90, 'warning'),
|
|
('builtin-memory-high', '内存使用率过高', 'memory_usage', '>=', 90, 'warning'),
|
|
('builtin-disk-high', '磁盘使用率过高', 'disk_usage', '>=', 92, 'critical');
|