sysmonstm/scripts/init-db.sql

-- TimescaleDB initialization script
-- Creates hypertables for time-series metrics storage

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Raw metrics table (high resolution, short retention)
CREATE TABLE IF NOT EXISTS metrics_raw (
    time        TIMESTAMPTZ NOT NULL,
    machine_id  TEXT NOT NULL,
    hostname    TEXT NOT NULL,
    metric_type TEXT NOT NULL,
    value       DOUBLE PRECISION NOT NULL,
    labels      JSONB DEFAULT '{}'::jsonb
);

-- Convert to hypertable with 1-hour chunks
SELECT create_hypertable('metrics_raw', 'time',
    chunk_time_interval => INTERVAL '1 hour',
    if_not_exists => TRUE
);

-- Create indexes for common queries
CREATE INDEX IF NOT EXISTS idx_metrics_raw_machine
    ON metrics_raw (machine_id, time DESC);
CREATE INDEX IF NOT EXISTS idx_metrics_raw_type
    ON metrics_raw (metric_type, time DESC);

-- Aggregated metrics table (1-minute resolution, longer retention)
CREATE TABLE IF NOT EXISTS metrics_1m (
    time        TIMESTAMPTZ NOT NULL,
    machine_id  TEXT NOT NULL,
    hostname    TEXT NOT NULL,
    metric_type TEXT NOT NULL,
    avg_value   DOUBLE PRECISION NOT NULL,
    min_value   DOUBLE PRECISION NOT NULL,
    max_value   DOUBLE PRECISION NOT NULL,
    sample_count INTEGER NOT NULL
);

SELECT create_hypertable('metrics_1m', 'time',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

CREATE INDEX IF NOT EXISTS idx_metrics_1m_machine
    ON metrics_1m (machine_id, time DESC);

-- Aggregated metrics table (1-hour resolution, long retention)
CREATE TABLE IF NOT EXISTS metrics_1h (
    time        TIMESTAMPTZ NOT NULL,
    machine_id  TEXT NOT NULL,
    hostname    TEXT NOT NULL,
    metric_type TEXT NOT NULL,
    avg_value   DOUBLE PRECISION NOT NULL,
    min_value   DOUBLE PRECISION NOT NULL,
    max_value   DOUBLE PRECISION NOT NULL,
    sample_count INTEGER NOT NULL
);

SELECT create_hypertable('metrics_1h', 'time',
    chunk_time_interval => INTERVAL '1 week',
    if_not_exists => TRUE
);

CREATE INDEX IF NOT EXISTS idx_metrics_1h_machine
    ON metrics_1h (machine_id, time DESC);

-- Machines registry
CREATE TABLE IF NOT EXISTS machines (
    machine_id  TEXT PRIMARY KEY,
    hostname    TEXT NOT NULL,
    first_seen  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata    JSONB DEFAULT '{}'::jsonb,
    health      TEXT NOT NULL DEFAULT 'UNKNOWN'
);

-- Alert rules configuration
CREATE TABLE IF NOT EXISTS alert_rules (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    metric_type TEXT NOT NULL,
    operator    TEXT NOT NULL CHECK (operator IN ('gt', 'lt', 'gte', 'lte', 'eq')),
    threshold   DOUBLE PRECISION NOT NULL,
    severity    TEXT NOT NULL CHECK (severity IN ('warning', 'critical')),
    enabled     BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Alert history
CREATE TABLE IF NOT EXISTS alerts (
    id          SERIAL,
    time        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    machine_id  TEXT NOT NULL,
    rule_id     INTEGER REFERENCES alert_rules(id),
    rule_name   TEXT NOT NULL,
    metric_type TEXT NOT NULL,
    value       DOUBLE PRECISION NOT NULL,
    threshold   DOUBLE PRECISION NOT NULL,
    severity    TEXT NOT NULL,
    resolved_at TIMESTAMPTZ,
    PRIMARY KEY (id, time)
);

SELECT create_hypertable('alerts', 'time',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

-- Retention policies
-- Raw data: 24 hours
SELECT add_retention_policy('metrics_raw', INTERVAL '24 hours', if_not_exists => TRUE);

-- 1-minute aggregates: 7 days
SELECT add_retention_policy('metrics_1m', INTERVAL '7 days', if_not_exists => TRUE);

-- 1-hour aggregates: 90 days
SELECT add_retention_policy('metrics_1h', INTERVAL '90 days', if_not_exists => TRUE);

-- Alerts: 30 days
SELECT add_retention_policy('alerts', INTERVAL '30 days', if_not_exists => TRUE);

-- Continuous aggregates for automatic downsampling
CREATE MATERIALIZED VIEW IF NOT EXISTS metrics_1m_agg
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', time) AS time,
    machine_id,
    hostname,
    metric_type,
    AVG(value) AS avg_value,
    MIN(value) AS min_value,
    MAX(value) AS max_value,
    COUNT(*) AS sample_count
FROM metrics_raw
GROUP BY time_bucket('1 minute', time), machine_id, hostname, metric_type
WITH NO DATA;

-- Refresh policy for continuous aggregate
SELECT add_continuous_aggregate_policy('metrics_1m_agg',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute',
    if_not_exists => TRUE
);

-- Insert default alert rules
INSERT INTO alert_rules (name, metric_type, operator, threshold, severity)
VALUES
    ('High CPU Usage', 'CPU_PERCENT', 'gt', 80.0, 'warning'),
    ('Critical CPU Usage', 'CPU_PERCENT', 'gt', 95.0, 'critical'),
    ('High Memory Usage', 'MEMORY_PERCENT', 'gt', 85.0, 'warning'),
    ('Critical Memory Usage', 'MEMORY_PERCENT', 'gt', 95.0, 'critical'),
    ('High Disk Usage', 'DISK_PERCENT', 'gt', 80.0, 'warning'),
    ('Critical Disk Usage', 'DISK_PERCENT', 'gt', 90.0, 'critical')
ON CONFLICT (name) DO NOTHING;