snapraid_smart_logging/schema_v2.sql

-- PostgreSQL schema for SMART data logging (Version 2)
-- Now includes a devices metadata table

-- Device metadata table (populated manually)
CREATE TABLE IF NOT EXISTS devices (
    device_wwn VARCHAR(50) PRIMARY KEY,  -- e.g., '0x5000c500744487c5'
    device_path VARCHAR(255) NOT NULL,   -- e.g., '/dev/sda'
    serial_number VARCHAR(255) NOT NULL,
    model VARCHAR(255),
    manufacturer VARCHAR(100),
    capacity_bytes BIGINT,
    size_tb DECIMAL(10, 2),
    disk_role VARCHAR(50),  -- e.g., 'd1', 'd2', 'parity', '2-parity', or '-' for unused
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Main table for SMART metrics snapshots
CREATE TABLE IF NOT EXISTS smart_metrics (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    device_wwn VARCHAR(50) NOT NULL,  -- Foreign key to devices table

    -- Basic metrics (from SnapRAID smart output)
    temperature_celsius INTEGER,
    power_on_days INTEGER,
    error_count INTEGER,

    -- Calculated failure probability
    failure_probability_pct DECIMAL(5, 2),  -- 0.00 to 100.00

    -- Raw SMART attributes (key-value pairs as JSONB)
    smart_attributes JSONB,

    -- Foreign key constraint
    CONSTRAINT fk_device FOREIGN KEY (device_wwn) REFERENCES devices(device_wwn),

    -- Unique constraint
    CONSTRAINT unique_device_timestamp UNIQUE (device_wwn, timestamp)
);

-- Indexes for efficient querying
CREATE INDEX IF NOT EXISTS idx_smart_metrics_device_wwn ON smart_metrics(device_wwn);
CREATE INDEX IF NOT EXISTS idx_smart_metrics_timestamp ON smart_metrics(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_smart_attributes_gin ON smart_metrics USING gin (smart_attributes);
CREATE INDEX IF NOT EXISTS idx_devices_serial ON devices(serial_number);
CREATE INDEX IF NOT EXISTS idx_devices_path ON devices(device_path);

-- View for latest metrics per device with metadata
CREATE OR REPLACE VIEW smart_latest AS
SELECT DISTINCT ON (d.device_wwn)
    d.device_wwn,
    d.device_path,
    d.serial_number,
    d.model,
    d.manufacturer,
    d.size_tb,
    d.disk_role,
    sm.timestamp,
    sm.temperature_celsius,
    sm.power_on_days,
    sm.error_count,
    sm.failure_probability_pct,
    sm.smart_attributes
FROM devices d
LEFT JOIN smart_metrics sm ON d.device_wwn = sm.device_wwn
ORDER BY d.device_wwn, sm.timestamp DESC NULLS LAST;

-- View for high-risk devices (failure probability > 50%)
CREATE OR REPLACE VIEW smart_high_risk AS
SELECT *
FROM smart_latest
WHERE failure_probability_pct > 50.0
ORDER BY failure_probability_pct DESC;

-- View for summary (like snapraid smart output)
CREATE OR REPLACE VIEW smart_summary AS
SELECT
    device_path,
    serial_number,
    temperature_celsius as temp_c,
    power_on_days,
    error_count,
    failure_probability_pct as fp_pct,
    size_tb,
    disk_role,
    model,
    timestamp as last_updated
FROM smart_latest
ORDER BY
    CASE disk_role
        WHEN 'parity' THEN 1
        WHEN '2-parity' THEN 2
        ELSE 3
    END,
    device_path;

-- Comments
COMMENT ON TABLE devices IS 'Device metadata (WWN, serial, model, etc.) - manually populated';
COMMENT ON TABLE smart_metrics IS 'Historical SMART metrics collected from Scrutiny InfluxDB';
COMMENT ON COLUMN smart_metrics.failure_probability_pct IS 'BackBlaze-based 1-year failure probability (0-100%)';
COMMENT ON COLUMN smart_metrics.smart_attributes IS 'Raw SMART attributes as JSON (attribute_id -> value)';
COMMENT ON VIEW smart_latest IS 'Latest SMART metrics for each device with metadata';
COMMENT ON VIEW smart_high_risk IS 'Devices with >50% annual failure probability';
COMMENT ON VIEW smart_summary IS 'Summary view matching snapraid smart output format';