database_init/01_init_morphguard_db.sql

-- MorphGuard Database Initialization
-- Creates TimescaleDB tables for metrics collection

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

-- System metrics table for general system information
CREATE TABLE IF NOT EXISTS device_metrics (
    time TIMESTAMPTZ NOT NULL,
    cpu_percent FLOAT,
    memory_percent FLOAT
);

-- Select this as a TimescaleDB hypertable
SELECT create_hypertable('device_metrics', 'time');

-- GPU metrics table for ML hardware stats
CREATE TABLE IF NOT EXISTS gpu_metrics (
    time TIMESTAMPTZ NOT NULL,
    memory_used_mb FLOAT,
    utilization FLOAT,
    temperature_c FLOAT
);

-- Select this as a TimescaleDB hypertable
SELECT create_hypertable('gpu_metrics', 'time');

-- Detection metrics for model performance
CREATE TABLE IF NOT EXISTS detection_metrics (
    time TIMESTAMPTZ NOT NULL,
    model_name TEXT,
    confidence_score FLOAT,
    is_morphed BOOLEAN,
    processing_time_ms FLOAT,
    image_hash TEXT
);

-- Select this as a TimescaleDB hypertable
SELECT create_hypertable('detection_metrics', 'time');

-- Demorph metrics for tracking quality of demorphing results
CREATE TABLE IF NOT EXISTS demorph_metrics (
    time TIMESTAMPTZ NOT NULL,
    model_name TEXT,
    processing_time_ms FLOAT,
    original_image_hash TEXT,
    result_image_hash TEXT,
    method TEXT
);

-- Select this as a TimescaleDB hypertable
SELECT create_hypertable('demorph_metrics', 'time');

-- Create indexes for faster queries
CREATE INDEX ON device_metrics (time DESC);
CREATE INDEX ON gpu_metrics (time DESC);
CREATE INDEX ON detection_metrics (time DESC, model_name);
CREATE INDEX ON demorph_metrics (time DESC, model_name);

-- Create retention policy (keep data for 30 days)
SELECT add_retention_policy('device_metrics', INTERVAL '30 days');
SELECT add_retention_policy('gpu_metrics', INTERVAL '30 days');
SELECT add_retention_policy('detection_metrics', INTERVAL '90 days');
SELECT add_retention_policy('demorph_metrics', INTERVAL '90 days');

-- Grant permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO morphguard;