1 contributor
-- autoSMART Database Schema v2.0 — Normalizare SMART parameters din JSONB
-- Version: 2.0
-- Description: Replaces blob-based JSONB storage with structured EAV model
-- Date: 2026-05-20
-- This schema REPLACES smart_readings with smart_collection_events + smart_param_values
-- Backward compat: v_smart_readings_compat view emulates old schema
-- ============================================================================
-- EXTENSIONS
-- ============================================================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
-- ============================================================================
-- TIMESTAMP TRIGGER (reusable)
-- ============================================================================
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- ============================================================================
-- SMART PARAMETER CATALOG — Registru parametri cu auto-discovery
-- ============================================================================
CREATE TABLE smart_param_catalog (
id SERIAL PRIMARY KEY,
param_name VARCHAR(100) NOT NULL,
param_id_ata INTEGER,
device_type VARCHAR(20) DEFAULT 'any',
unit VARCHAR(20),
description TEXT,
warning_threshold NUMERIC,
critical_threshold NUMERIC,
health_weight NUMERIC DEFAULT 1.0,
is_critical BOOLEAN DEFAULT false,
lower_is_better BOOLEAN DEFAULT true,
track_raw_value BOOLEAN DEFAULT true,
track_normalized BOOLEAN DEFAULT false,
first_seen TIMESTAMPTZ DEFAULT NOW(),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT uq_param_name UNIQUE (param_name)
);
CREATE INDEX idx_param_catalog_name ON smart_param_catalog(param_name);
CREATE INDEX idx_param_catalog_critical ON smart_param_catalog(is_critical) WHERE is_critical = true;
CREATE TRIGGER update_param_catalog_timestamp
BEFORE UPDATE ON smart_param_catalog
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
-- ============================================================================
-- SMART COLLECTION EVENTS — Metadate per-colectare (înlocuiește smart_readings)
-- ============================================================================
CREATE TABLE smart_collection_events (
id BIGSERIAL PRIMARY KEY,
hdd_id INTEGER NOT NULL REFERENCES hdd_inventory(id),
serial_number VARCHAR(100) NOT NULL,
node_id VARCHAR(50) NOT NULL,
collected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
collection_ok BOOLEAN DEFAULT true,
temperature SMALLINT,
checksum VARCHAR(64),
param_count SMALLINT,
notes TEXT
);
CREATE INDEX idx_sce_hdd_id_time ON smart_collection_events(hdd_id, collected_at DESC);
CREATE INDEX idx_sce_serial ON smart_collection_events(serial_number);
CREATE INDEX idx_sce_node_time ON smart_collection_events(node_id, collected_at DESC);
CREATE INDEX idx_sce_collected_at ON smart_collection_events(collected_at DESC);
CREATE INDEX idx_sce_checksum ON smart_collection_events(checksum) WHERE checksum IS NOT NULL;
-- ============================================================================
-- SMART PARAM VALUES — Tabel EAV partiționat lunar
-- ============================================================================
CREATE TABLE smart_param_values (
id BIGSERIAL,
event_id BIGINT NOT NULL,
hdd_id INTEGER NOT NULL,
param_id INTEGER NOT NULL REFERENCES smart_param_catalog(id),
collected_at TIMESTAMPTZ NOT NULL,
raw_value BIGINT,
normalized_value SMALLINT,
worst_value SMALLINT,
threshold_value SMALLINT,
when_failed VARCHAR(20),
PRIMARY KEY (collected_at, id)
) PARTITION BY RANGE (collected_at);
-- Creare partiții pentru perioada viitoare (2025-2027)
CREATE TABLE smart_param_values_2025_01 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE smart_param_values_2025_02 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE smart_param_values_2025_03 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
CREATE TABLE smart_param_values_2025_04 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');
CREATE TABLE smart_param_values_2025_05 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');
CREATE TABLE smart_param_values_2025_06 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');
CREATE TABLE smart_param_values_2025_07 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE smart_param_values_2025_08 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE smart_param_values_2025_09 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
CREATE TABLE smart_param_values_2025_10 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE TABLE smart_param_values_2025_11 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE smart_param_values_2025_12 PARTITION OF smart_param_values
FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
CREATE TABLE smart_param_values_2026_01 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE smart_param_values_2026_02 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE smart_param_values_2026_03 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE smart_param_values_2026_04 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE smart_param_values_2026_05 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE smart_param_values_2026_06 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE smart_param_values_2026_07 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE smart_param_values_2026_08 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE smart_param_values_2026_09 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE smart_param_values_2026_10 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE smart_param_values_2026_11 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE smart_param_values_2026_12 PARTITION OF smart_param_values
FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
-- Partiție default pentru date neprevăzute
CREATE TABLE smart_param_values_default PARTITION OF smart_param_values DEFAULT;
-- Indexuri pe partiții
CREATE INDEX idx_spv_hdd_param_time ON smart_param_values(hdd_id, param_id, collected_at DESC);
CREATE INDEX idx_spv_event_id ON smart_param_values(event_id);
CREATE INDEX idx_spv_param_time ON smart_param_values(param_id, collected_at DESC);
CREATE INDEX idx_spv_critical_values ON smart_param_values(hdd_id, collected_at DESC)
WHERE raw_value > 0;
-- ============================================================================
-- VIEWS
-- ============================================================================
-- View 1: Ultimele valori per disc per parametru
CREATE VIEW v_latest_param_values AS
SELECT DISTINCT ON (spv.hdd_id, spv.param_id)
spv.hdd_id,
spv.param_id,
spc.param_name,
spc.unit,
spc.is_critical,
spv.raw_value,
spv.normalized_value,
spv.collected_at,
hi.serial_number,
hi.model_name,
hi.current_node_id
FROM smart_param_values spv
JOIN smart_param_catalog spc ON spv.param_id = spc.id
JOIN hdd_inventory hi ON spv.hdd_id = hi.id
WHERE hi.status = 'active'
ORDER BY spv.hdd_id, spv.param_id, spv.collected_at DESC;
-- View 2: Starea curentă a fiecărui disc
CREATE VIEW v_drive_health_summary AS
SELECT
hi.id AS hdd_id,
hi.serial_number,
hi.model_name,
hi.manufacturer,
hi.current_node_id,
hi.current_device_path,
hi.status,
hi.size_gb,
sce_last.collected_at AS last_collection,
sce_last.temperature AS last_temperature,
sce_last.collection_ok AS last_collection_ok,
EXTRACT(EPOCH FROM (NOW() - sce_last.collected_at)) / 3600 AS hours_since_collection,
critical_counts.params_above_zero AS critical_params_triggered,
pred.risk_level,
pred.failure_probability,
pred.predicted_failure_date
FROM hdd_inventory hi
LEFT JOIN LATERAL (
SELECT collected_at, temperature, collection_ok
FROM smart_collection_events
WHERE hdd_id = hi.id
ORDER BY collected_at DESC
LIMIT 1
) sce_last ON true
LEFT JOIN LATERAL (
SELECT COUNT(*) AS params_above_zero
FROM smart_param_values spv
JOIN smart_param_catalog spc ON spv.param_id = spc.id
WHERE spv.hdd_id = hi.id
AND spc.is_critical = true
AND spv.raw_value > 0
AND spv.collected_at >= COALESCE((
SELECT collected_at FROM smart_collection_events
WHERE hdd_id = hi.id ORDER BY collected_at DESC LIMIT 1
), NOW()) - INTERVAL '5 minutes'
) critical_counts ON true
LEFT JOIN LATERAL (
SELECT risk_level, failure_probability, predicted_failure_date
FROM predictions
WHERE hdd_id = hi.id
ORDER BY timestamp DESC
LIMIT 1
) pred ON true
WHERE hi.status = 'active';
-- View 3: Trending parametru specific
CREATE VIEW v_param_trend AS
SELECT
hi.serial_number,
hi.model_name,
hi.current_node_id,
spc.param_name,
spc.unit,
spc.is_critical,
spc.warning_threshold,
spc.critical_threshold,
spv.raw_value,
spv.normalized_value,
spv.collected_at,
sce.node_id AS collected_on_node,
sce.temperature AS drive_temp_at_collection
FROM smart_param_values spv
JOIN smart_collection_events sce ON spv.event_id = sce.id
JOIN smart_param_catalog spc ON spv.param_id = spc.id
JOIN hdd_inventory hi ON spv.hdd_id = hi.id;
-- View 4: Cluster overview
CREATE VIEW v_cluster_overview AS
SELECT
hi.current_node_id AS node,
COUNT(*) AS total_drives,
COUNT(*) FILTER (WHERE dhs.last_collection > NOW() - INTERVAL '1 hour') AS drives_recently_collected,
COUNT(*) FILTER (WHERE dhs.critical_params_triggered > 0) AS drives_with_critical_params,
COUNT(*) FILTER (WHERE dhs.risk_level IN ('high', 'critical')) AS drives_high_risk,
MAX(dhs.last_collection) AS latest_collection,
MIN(dhs.last_collection) AS oldest_collection
FROM hdd_inventory hi
JOIN v_drive_health_summary dhs ON hi.id = dhs.hdd_id
WHERE hi.status = 'active'
GROUP BY hi.current_node_id;
-- View 5: Compatibilitate cu codul existent (emulează smart_readings)
CREATE VIEW v_smart_readings_compat AS
SELECT
sce.id,
sce.hdd_id,
sce.serial_number,
sce.collected_at AS timestamp,
sce.temperature,
sce.node_id,
sce.collection_ok,
jsonb_object_agg(
COALESCE(spc.param_name, ''),
spv.raw_value
) FILTER (WHERE spc.param_name IS NOT NULL) AS parameters_json
FROM smart_collection_events sce
LEFT JOIN smart_param_values spv ON spv.event_id = sce.id
LEFT JOIN smart_param_catalog spc ON spv.param_id = spc.id
GROUP BY sce.id, sce.hdd_id, sce.serial_number, sce.collected_at, sce.temperature, sce.node_id, sce.collection_ok;
-- ============================================================================
-- FUNCTIONS
-- ============================================================================
-- Upsert parametru în catalog (auto-discovery)
CREATE OR REPLACE FUNCTION upsert_param_catalog(
p_param_name VARCHAR(100),
p_param_id_ata INTEGER DEFAULT NULL,
p_unit VARCHAR(20) DEFAULT 'count'
) RETURNS INTEGER AS $$
DECLARE
v_id INTEGER;
BEGIN
INSERT INTO smart_param_catalog (param_name, param_id_ata, unit, first_seen)
VALUES (p_param_name, p_param_id_ata, p_unit, NOW())
ON CONFLICT (param_name) DO UPDATE
SET param_id_ata = COALESCE(EXCLUDED.param_id_ata, smart_param_catalog.param_id_ata),
updated_at = NOW()
RETURNING id INTO v_id;
RETURN v_id;
END;
$$ LANGUAGE plpgsql;
-- Inserare atomică event + valori (înlocuiește logica de collection)
CREATE OR REPLACE FUNCTION insert_collection_event(
p_hdd_id INTEGER,
p_serial VARCHAR(100),
p_node_id VARCHAR(50),
p_collected_at TIMESTAMPTZ,
p_temperature SMALLINT,
p_collection_ok BOOLEAN,
p_checksum VARCHAR(64),
p_params JSONB
) RETURNS BIGINT AS $$
DECLARE
v_event_id BIGINT;
v_param_id INTEGER;
v_param RECORD;
v_count SMALLINT := 0;
BEGIN
INSERT INTO smart_collection_events
(hdd_id, serial_number, node_id, collected_at,
collection_ok, temperature, checksum, param_count)
VALUES
(p_hdd_id, p_serial, p_node_id, p_collected_at,
p_collection_ok, p_temperature, p_checksum, 0)
RETURNING id INTO v_event_id;
FOR v_param IN SELECT * FROM jsonb_each(p_params)
LOOP
v_param_id := upsert_param_catalog(
v_param.key,
(v_param.value->>'id')::INTEGER,
'count'
);
INSERT INTO smart_param_values
(event_id, hdd_id, param_id, collected_at,
raw_value, normalized_value, worst_value, threshold_value, when_failed)
VALUES
(v_event_id, p_hdd_id, v_param_id, p_collected_at,
(v_param.value->>'raw_value')::BIGINT,
(v_param.value->>'value')::SMALLINT,
(v_param.value->>'worst')::SMALLINT,
(v_param.value->>'thresh')::SMALLINT,
v_param.value->>'when_failed');
v_count := v_count + 1;
END LOOP;
UPDATE smart_collection_events SET param_count = v_count WHERE id = v_event_id;
RETURN v_event_id;
END;
$$ LANGUAGE plpgsql;
-- Creare partiție lunară (cu idempotență)
CREATE OR REPLACE FUNCTION create_monthly_partition(p_year INTEGER, p_month INTEGER)
RETURNS VOID AS $$
DECLARE
v_table_name TEXT;
v_start_date DATE;
v_end_date DATE;
BEGIN
v_table_name := format('smart_param_values_%s_%s',
p_year, lpad(p_month::TEXT, 2, '0'));
v_start_date := make_date(p_year, p_month, 1);
v_end_date := v_start_date + INTERVAL '1 month';
IF NOT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_name = v_table_name AND table_schema = 'public'
) THEN
EXECUTE format(
'CREATE TABLE %I PARTITION OF smart_param_values
FOR VALUES FROM (%L) TO (%L)',
v_table_name, v_start_date, v_end_date
);
RAISE NOTICE 'Created partition: %', v_table_name;
END IF;
END;
$$ LANGUAGE plpgsql;
-- On-demand data deletion: delete all data for a specific HDD (by serial_number)
-- RETENTION POLICY: On-demand only (no automatic cleanup)
-- Called from frontend when user requests to remove a drive from monitoring
CREATE OR REPLACE FUNCTION delete_hdd_data_by_serial(
p_serial_number VARCHAR(100),
p_keep_catalog BOOLEAN DEFAULT true
) RETURNS TABLE(
deleted_values BIGINT,
deleted_events BIGINT,
deleted_catalog INTEGER
) AS $$
DECLARE
v_hdd_id INTEGER;
v_deleted_values BIGINT := 0;
v_deleted_events BIGINT := 0;
v_deleted_catalog INTEGER := 0;
BEGIN
-- Get HDD ID for this serial
SELECT id INTO v_hdd_id FROM hdd_inventory WHERE serial_number = p_serial_number;
IF v_hdd_id IS NULL THEN
RAISE NOTICE 'Serial % not found in inventory', p_serial_number;
RETURN QUERY SELECT 0::BIGINT, 0::BIGINT, 0::INTEGER;
RETURN;
END IF;
-- Delete from smart_param_values (cascade via event_id FK)
DELETE FROM smart_param_values spv
WHERE event_id IN (
SELECT id FROM smart_collection_events
WHERE hdd_id = v_hdd_id
);
GET DIAGNOSTICS v_deleted_values = ROW_COUNT;
-- Delete from smart_collection_events
DELETE FROM smart_collection_events
WHERE hdd_id = v_hdd_id OR serial_number = p_serial_number;
GET DIAGNOSTICS v_deleted_events = ROW_COUNT;
-- Optionally delete from hdd_inventory and catalog
IF NOT p_keep_catalog THEN
DELETE FROM smart_param_catalog spc
WHERE NOT EXISTS (
SELECT 1 FROM smart_param_values
WHERE param_id = spc.id
);
GET DIAGNOSTICS v_deleted_catalog = ROW_COUNT;
DELETE FROM hdd_inventory WHERE id = v_hdd_id;
END IF;
RAISE NOTICE 'Deleted % param values, % events for serial %',
v_deleted_values, v_deleted_events, p_serial_number;
RETURN QUERY SELECT v_deleted_values, v_deleted_events, v_deleted_catalog;
END;
$$ LANGUAGE plpgsql;
-- ============================================================================
-- PERMISSIONS
-- ============================================================================
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO autosmart;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO autosmart;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO autosmart;
-- Specific grants for collections
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE smart_param_catalog TO autosmart;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE smart_collection_events TO autosmart;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE smart_param_values TO autosmart;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO autosmart;
-- ============================================================================
-- FINAL MESSAGE
-- ============================================================================
DO $$
BEGIN
RAISE NOTICE '✅ autoSMART Schema v2.0 deployed successfully!';
RAISE NOTICE 'New tables: smart_param_catalog, smart_collection_events, smart_param_values (partitioned)';
RAISE NOTICE 'Views: v_latest_param_values, v_drive_health_summary, v_param_trend, v_cluster_overview, v_smart_readings_compat';
RAISE NOTICE 'Functions: upsert_param_catalog, insert_collection_event, create_monthly_partition, delete_hdd_data_by_serial';
RAISE NOTICE 'Next step: Run sql/migrate-v1-to-v2.sql to migrate existing data';
END $$;