-- PM Data Pipeline Database Schema
-- PostgreSQL schema for storing Performance Monitoring XML data
-- Table to track processed files
CREATE TABLE IF NOT EXISTS pm_files (
id SERIAL PRIMARY KEY,
filename VARCHAR(255) NOT NULL UNIQUE,
checksum VARCHAR(64) NOT NULL UNIQUE,
generation_time TIMESTAMP WITH TIME ZONE,
node_id VARCHAR(100),
vendor VARCHAR(100),
equipment_type VARCHAR(100),
software_version VARCHAR(100),
reporting_period VARCHAR(50),
file_format_version VARCHAR(50),
collection_frequency VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
processed_at TIMESTAMP WITH TIME ZONE
);
-- Network elements table
CREATE TABLE IF NOT EXISTS network_elements (
id SERIAL PRIMARY KEY,
file_id INTEGER REFERENCES pm_files(id) ON DELETE CASCADE,
ne_name VARCHAR(255) NOT NULL,
ne_type VARCHAR(100),
site VARCHAR(255),
region VARCHAR(100),
country VARCHAR(100),
management_ip INET,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Measurement intervals table
CREATE TABLE IF NOT EXISTS measurement_intervals (
id SERIAL PRIMARY KEY,
file_id INTEGER REFERENCES pm_files(id) ON DELETE CASCADE,
start_time TIMESTAMP WITH TIME ZONE NOT NULL,
end_time TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Interfaces table
CREATE TABLE IF NOT EXISTS interfaces (
id SERIAL PRIMARY KEY,
interval_id INTEGER REFERENCES measurement_intervals(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
if_index INTEGER,
if_type VARCHAR(100),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Interface counters table
CREATE TABLE IF NOT EXISTS interface_counters (
id SERIAL PRIMARY KEY,
interface_id INTEGER REFERENCES interfaces(id) ON DELETE CASCADE,
counter_name VARCHAR(100) NOT NULL,
value NUMERIC(20, 4),
unit VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- IP counters table
CREATE TABLE IF NOT EXISTS ip_counters (
id SERIAL PRIMARY KEY,
interval_id INTEGER REFERENCES measurement_intervals(id) ON DELETE CASCADE,
counter_name VARCHAR(100) NOT NULL,
value NUMERIC(20, 4),
unit VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- TCP counters table
CREATE TABLE IF NOT EXISTS tcp_counters (
id SERIAL PRIMARY KEY,
interval_id INTEGER REFERENCES measurement_intervals(id) ON DELETE CASCADE,
counter_name VARCHAR(100) NOT NULL,
value NUMERIC(20, 4),
unit VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- System counters table
CREATE TABLE IF NOT EXISTS system_counters (
id SERIAL PRIMARY KEY,
interval_id INTEGER REFERENCES measurement_intervals(id) ON DELETE CASCADE,
counter_name VARCHAR(100) NOT NULL,
value NUMERIC(20, 4),
unit VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- BGP peers table
CREATE TABLE IF NOT EXISTS bgp_peers (
id SERIAL PRIMARY KEY,
interval_id INTEGER REFERENCES measurement_intervals(id) ON DELETE CASCADE,
address INET NOT NULL,
as_number INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- BGP counters table
CREATE TABLE IF NOT EXISTS bgp_counters (
id SERIAL PRIMARY KEY,
peer_id INTEGER REFERENCES bgp_peers(id) ON DELETE CASCADE,
counter_name VARCHAR(100) NOT NULL,
value NUMERIC(20, 4),
unit VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Threshold alerts table
CREATE TABLE IF NOT EXISTS threshold_alerts (
id SERIAL PRIMARY KEY,
file_id INTEGER REFERENCES pm_files(id) ON DELETE CASCADE,
severity VARCHAR(50),
timestamp TIMESTAMP WITH TIME ZONE,
parameter VARCHAR(100),
value NUMERIC(20, 4),
threshold NUMERIC(20, 4),
description TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Data quality indicators table
CREATE TABLE IF NOT EXISTS data_quality (
id SERIAL PRIMARY KEY,
file_id INTEGER REFERENCES pm_files(id) ON DELETE CASCADE,
indicator_name VARCHAR(100) NOT NULL,
value NUMERIC(20, 4),
unit VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Configuration table for fetch interval
CREATE TABLE IF NOT EXISTS config (
key VARCHAR(100) PRIMARY KEY,
value TEXT NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Insert default fetch interval if not exists
INSERT INTO config (key, value) VALUES ('fetch_interval_minutes', '5')
ON CONFLICT (key) DO NOTHING;
-- Indexes for performance optimization
CREATE INDEX IF NOT EXISTS idx_pm_files_checksum ON pm_files(checksum);
CREATE INDEX IF NOT EXISTS idx_pm_files_node_id ON pm_files(node_id);
CREATE INDEX IF NOT EXISTS idx_pm_files_generation_time ON pm_files(generation_time);
CREATE INDEX IF NOT EXISTS idx_network_elements_file_id ON network_elements(file_id);
CREATE INDEX IF NOT EXISTS idx_network_elements_ne_name ON network_elements(ne_name);
CREATE INDEX IF NOT EXISTS idx_measurement_intervals_file_id ON measurement_intervals(file_id);
CREATE INDEX IF NOT EXISTS idx_measurement_intervals_start_time ON measurement_intervals(start_time);
CREATE INDEX IF NOT EXISTS idx_measurement_intervals_end_time ON measurement_intervals(end_time);
CREATE INDEX IF NOT EXISTS idx_measurement_intervals_time_range ON measurement_intervals USING GIST (tstzrange(start_time, end_time));
CREATE INDEX IF NOT EXISTS idx_interfaces_interval_id ON interfaces(interval_id);
CREATE INDEX IF NOT EXISTS idx_interfaces_name ON interfaces(name);
CREATE INDEX IF NOT EXISTS idx_interfaces_if_index ON interfaces(if_index);
CREATE INDEX IF NOT EXISTS idx_interface_counters_interface_id ON interface_counters(interface_id);
CREATE INDEX IF NOT EXISTS idx_interface_counters_counter_name ON interface_counters(counter_name);
CREATE INDEX IF NOT EXISTS idx_interface_counters_name_value ON interface_counters(interface_id, counter_name);
CREATE INDEX IF NOT EXISTS idx_ip_counters_interval_id ON ip_counters(interval_id);
CREATE INDEX IF NOT EXISTS idx_ip_counters_counter_name ON ip_counters(counter_name);
CREATE INDEX IF NOT EXISTS idx_tcp_counters_interval_id ON tcp_counters(interval_id);
CREATE INDEX IF NOT EXISTS idx_tcp_counters_counter_name ON tcp_counters(counter_name);
CREATE INDEX IF NOT EXISTS idx_system_counters_interval_id ON system_counters(interval_id);
CREATE INDEX IF NOT EXISTS idx_system_counters_counter_name ON system_counters(counter_name);
CREATE INDEX IF NOT EXISTS idx_bgp_peers_interval_id ON bgp_peers(interval_id);
CREATE INDEX IF NOT EXISTS idx_bgp_peers_address ON bgp_peers(address);
CREATE INDEX IF NOT EXISTS idx_bgp_counters_peer_id ON bgp_counters(peer_id);
CREATE INDEX IF NOT EXISTS idx_bgp_counters_counter_name ON bgp_counters(counter_name);
CREATE INDEX IF NOT EXISTS idx_threshold_alerts_file_id ON threshold_alerts(file_id);
CREATE INDEX IF NOT EXISTS idx_threshold_alerts_timestamp ON threshold_alerts(timestamp);
CREATE INDEX IF NOT EXISTS idx_threshold_alerts_parameter ON threshold_alerts(parameter);
CREATE INDEX IF NOT EXISTS idx_data_quality_file_id ON data_quality(file_id);
CREATE INDEX IF NOT EXISTS idx_data_quality_indicator_name ON data_quality(indicator_name);
