gilles
2a44301269
- Migration SQLite initiale : agents, devices, metrics, events - API REST v1 complète : /agents, /network, /metrics, /events, /widgets - Endpoints widgets Glance : /api/v1/widgets/network et /api/v1/widgets/metrics - Spec OpenAPI générée et servie sur /api-docs/openapi.json - Gestion d'erreurs centralisée (AppError) - CORS permissif pour développement - push réseau met à jour le last_seen de l'agent Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
53 lines
1.8 KiB
SQL
53 lines
1.8 KiB
SQL
-- Agents enregistrés (scan-network, metric)
|
|
CREATE TABLE IF NOT EXISTS agents (
|
|
id TEXT PRIMARY KEY,
|
|
hostname TEXT NOT NULL,
|
|
agent_type TEXT NOT NULL CHECK(agent_type IN ('scan-network', 'metric')),
|
|
ip TEXT NOT NULL,
|
|
version TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'online' CHECK(status IN ('online', 'offline')),
|
|
last_seen TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Équipements réseau découverts
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
ip TEXT PRIMARY KEY,
|
|
mac TEXT,
|
|
hostname TEXT,
|
|
vendor TEXT,
|
|
state TEXT NOT NULL DEFAULT 'unknown' CHECK(state IN ('online', 'offline', 'sleep', 'unknown')),
|
|
services TEXT NOT NULL DEFAULT '[]', -- JSON array
|
|
open_ports TEXT NOT NULL DEFAULT '[]', -- JSON array
|
|
last_seen TEXT NOT NULL,
|
|
first_seen TEXT NOT NULL
|
|
);
|
|
|
|
-- Métriques système (dernière valeur par agent)
|
|
CREATE TABLE IF NOT EXISTS metrics (
|
|
agent_id TEXT NOT NULL REFERENCES agents(id),
|
|
timestamp TEXT NOT NULL,
|
|
cpu_percent REAL,
|
|
ram_percent REAL,
|
|
load_avg REAL,
|
|
temperature_c REAL,
|
|
disk_percent REAL,
|
|
net_rx_bps INTEGER,
|
|
net_tx_bps INTEGER,
|
|
extra TEXT DEFAULT '{}', -- JSON pour champs futurs
|
|
PRIMARY KEY (agent_id)
|
|
);
|
|
|
|
-- Événements système
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
agent_id TEXT NOT NULL,
|
|
event_type TEXT NOT NULL,
|
|
timestamp TEXT NOT NULL,
|
|
data TEXT NOT NULL DEFAULT '{}' -- JSON
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_events_agent ON events(agent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_events_ts ON events(timestamp);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_state ON devices(state);
|