bogdan autoSMART: Phase 2 — Archive + On-demand retention + Collectors update

73016a1 2 weeks ago History

-- autoSMART — Rename v1 tables to archive
-- Run on-demand after validating collectors work with schema v2
-- Date: 2026-05-20
-- Safe: tables are read-only at this point

BEGIN;

-- Rename tables
ALTER TABLE IF EXISTS smart_readings RENAME TO smart_readings_archive_v1;
ALTER TABLE IF EXISTS smart_thresholds RENAME TO smart_thresholds_archive_v1;
ALTER TABLE IF EXISTS alert_history RENAME TO alert_history_archive_v1;

-- Rename sequences
ALTER SEQUENCE IF EXISTS smart_readings_id_seq RENAME TO smart_readings_archive_v1_id_seq;
ALTER SEQUENCE IF EXISTS smart_thresholds_id_seq RENAME TO smart_thresholds_archive_v1_id_seq;
ALTER SEQUENCE IF EXISTS alert_history_id_seq RENAME TO alert_history_archive_v1_id_seq;

-- Rename indexes
ALTER INDEX IF EXISTS smart_readings_pkey RENAME TO smart_readings_archive_v1_pkey;
ALTER INDEX IF EXISTS idx_smart_readings_hdd_id RENAME TO idx_smart_readings_archive_v1_hdd_id;
ALTER INDEX IF EXISTS idx_smart_readings_timestamp RENAME TO idx_smart_readings_archive_v1_timestamp;
ALTER INDEX IF EXISTS idx_smart_readings_serial RENAME TO idx_smart_readings_archive_v1_serial;
ALTER INDEX IF EXISTS idx_smart_readings_device_path RENAME TO idx_smart_readings_archive_v1_device_path;
ALTER INDEX IF EXISTS idx_smart_readings_type RENAME TO idx_smart_readings_archive_v1_type;
ALTER INDEX IF EXISTS idx_smart_readings_checksum RENAME TO idx_smart_readings_archive_v1_checksum;
ALTER INDEX IF EXISTS idx_smart_readings_previous RENAME TO idx_smart_readings_archive_v1_previous;
ALTER INDEX IF EXISTS idx_smart_readings_parameters RENAME TO idx_smart_readings_archive_v1_parameters;
ALTER INDEX IF EXISTS idx_smart_readings_changed_params RENAME TO idx_smart_readings_archive_v1_changed_params;

ALTER INDEX IF EXISTS smart_thresholds_pkey RENAME TO smart_thresholds_archive_v1_pkey;
ALTER INDEX IF EXISTS uq_parameter_name RENAME TO uq_archive_v1_parameter_name;

ALTER INDEX IF EXISTS alert_history_pkey RENAME TO alert_history_archive_v1_pkey;
ALTER INDEX IF EXISTS idx_alert_history_hdd_id RENAME TO idx_alert_history_archive_v1_hdd_id;
ALTER INDEX IF EXISTS idx_alert_history_sent_at RENAME TO idx_alert_history_archive_v1_sent_at;
ALTER INDEX IF EXISTS idx_alert_history_severity RENAME TO idx_alert_history_archive_v1_severity;
ALTER INDEX IF EXISTS idx_alert_history_serial RENAME TO idx_alert_history_archive_v1_serial;

-- Rename constraints
ALTER TABLE smart_readings_archive_v1 RENAME CONSTRAINT smart_readings_hdd_id_fkey TO smart_readings_archive_v1_hdd_id_fkey;
ALTER TABLE smart_readings_archive_v1 RENAME CONSTRAINT smart_readings_previous_reading_id_fkey TO smart_readings_archive_v1_previous_reading_id_fkey;
ALTER TABLE alert_history_archive_v1 RENAME CONSTRAINT alert_history_hdd_id_fkey TO alert_history_archive_v1_hdd_id_fkey;
ALTER TABLE alert_history_archive_v1 RENAME CONSTRAINT alert_history_related_reading_id_fkey TO alert_history_archive_v1_related_reading_id_fkey;
ALTER TABLE alert_history_archive_v1 RENAME CONSTRAINT alert_history_related_prediction_id_fkey TO alert_history_archive_v1_related_prediction_id_fkey;

COMMIT;

-- Summary
DO $$
BEGIN
    RAISE NOTICE '✅ Archive rename complete!';
    RAISE NOTICE 'Tables renamed:';
    RAISE NOTICE '  • smart_readings → smart_readings_archive_v1';
    RAISE NOTICE '  • smart_thresholds → smart_thresholds_archive_v1';
    RAISE NOTICE '  • alert_history → alert_history_archive_v1';
    RAISE NOTICE '';
    RAISE NOTICE 'These tables are now read-only archives.';
    RAISE NOTICE 'All new data flows through schema v2.';
    RAISE NOTICE '';
    RAISE NOTICE 'To delete archives (after 4-6 week validation):';
    RAISE NOTICE '  DROP TABLE smart_readings_archive_v1 CASCADE;';
    RAISE NOTICE '  DROP TABLE smart_thresholds_archive_v1 CASCADE;';
    RAISE NOTICE '  DROP TABLE alert_history_archive_v1 CASCADE;';
END $$;