62 lines
2.1 KiB
MySQL
62 lines
2.1 KiB
MySQL
|
CREATE TABLE IF NOT EXISTS drift_sources (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(100) NOT NULL,
|
||
|
|
connector_type VARCHAR(50) NOT NULL,
|
||
|
|
config_json JSONB DEFAULT '{}'::jsonb,
|
||
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS drift_devices (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
source_id INTEGER REFERENCES drift_sources(id) ON DELETE SET NULL,
|
||
|
|
external_id VARCHAR(200),
|
||
|
|
name VARCHAR(200),
|
||
|
|
customer_name VARCHAR(200),
|
||
|
|
site_name VARCHAR(200),
|
||
|
|
service_name VARCHAR(200),
|
||
|
|
metadata_json JSONB DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE (source_id, external_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS drift_events (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
source_id INTEGER REFERENCES drift_sources(id) ON DELETE SET NULL,
|
||
|
|
source_event_id VARCHAR(200),
|
||
|
|
severity VARCHAR(50) NOT NULL DEFAULT 'info',
|
||
|
|
status VARCHAR(50) NOT NULL DEFAULT 'new',
|
||
|
|
customer_name VARCHAR(200),
|
||
|
|
site_name VARCHAR(200),
|
||
|
|
device_name VARCHAR(200),
|
||
|
|
service_name VARCHAR(200),
|
||
|
|
message TEXT,
|
||
|
|
started_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
resolved_at TIMESTAMP,
|
||
|
|
duration_minutes INTEGER,
|
||
|
|
raw_json JSONB DEFAULT '{}'::jsonb,
|
||
|
|
tags JSONB DEFAULT '[]'::jsonb,
|
||
|
|
ticket_id INTEGER,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS drift_event_history (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
event_id INTEGER REFERENCES drift_events(id) ON DELETE CASCADE,
|
||
|
|
status VARCHAR(50) NOT NULL,
|
||
|
|
message TEXT,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS drift_ticket_links (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
event_id INTEGER REFERENCES drift_events(id) ON DELETE CASCADE,
|
||
|
|
sag_id INTEGER NOT NULL,
|
||
|
|
link_type VARCHAR(50) NOT NULL DEFAULT 'created',
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE (event_id, sag_id)
|
||
|
|
);
|