supabase-schema.sql

-- CloudPilot AI Real-time Dashboard Schema
-- Run this in your Supabase SQL Editor

-- Enable realtime on public schema
ALTER PUBLICATION supabase_realtime ADD TABLE cloud_metrics;
ALTER PUBLICATION supabase_realtime ADD TABLE resources;

-- Resources table (cloud instances)
CREATE TABLE IF NOT EXISTS resources (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    provider TEXT NOT NULL CHECK (provider IN ('aws', 'gcp', 'azure')),
    instance_id TEXT NOT NULL,
    name TEXT NOT NULL,
    instance_type TEXT NOT NULL,
    region TEXT NOT NULL,
    state TEXT NOT NULL CHECK (state IN ('running', 'stopped', 'terminated')),
    hourly_usd FLOAT8 NOT NULL CHECK (hourly_usd >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Cloud metrics table for real-time streaming dashboard
CREATE TABLE IF NOT EXISTS cloud_metrics (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    provider TEXT NOT NULL CHECK (provider IN ('aws', 'gcp', 'azure')),
    instance_id TEXT NOT NULL,
    cpu FLOAT8 NOT NULL CHECK (cpu >= 0 AND cpu <= 100),
    memory FLOAT8 NOT NULL CHECK (memory >= 0 AND memory <= 100),
    network_in FLOAT8 NOT NULL CHECK (network_in >= 0),
    network_out FLOAT8 NOT NULL CHECK (network_out >= 0),
    cost_usd FLOAT8 NOT NULL CHECK (cost_usd >= 0),
    state TEXT NOT NULL CHECK (state IN ('running', 'stopped', 'terminated')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for efficient querying
CREATE INDEX IF NOT EXISTS idx_resources_provider ON resources (provider);
CREATE INDEX IF NOT EXISTS idx_resources_state ON resources (state);
CREATE INDEX IF NOT EXISTS idx_resources_instance_id ON resources (instance_id);

CREATE INDEX IF NOT EXISTS idx_cloud_metrics_timestamp ON cloud_metrics (timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_cloud_metrics_provider ON cloud_metrics (provider);
CREATE INDEX IF NOT EXISTS idx_cloud_metrics_instance ON cloud_metrics (instance_id);
CREATE INDEX IF NOT EXISTS idx_cloud_metrics_state ON cloud_metrics (state);

-- Enable Row Level Security (RLS)
ALTER TABLE resources ENABLE ROW LEVEL SECURITY;
ALTER TABLE cloud_metrics ENABLE ROW LEVEL SECURITY;

-- Create policies that allow all operations for now (adjust as needed for production)
CREATE POLICY "Allow all operations on resources" ON resources
    FOR ALL USING (true) WITH CHECK (true);

CREATE POLICY "Allow all operations on cloud_metrics" ON cloud_metrics
    FOR ALL USING (true) WITH CHECK (true);

-- Grant permissions to anon and authenticated users
GRANT ALL ON resources TO anon, authenticated;
GRANT ALL ON cloud_metrics TO anon, authenticated;

-- Users table for authentication
CREATE TABLE IF NOT EXISTS users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    company TEXT,
    role TEXT NOT NULL CHECK (role IN ('devops', 'finance', 'admin')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for users
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);
CREATE INDEX IF NOT EXISTS idx_users_role ON users (role);

-- Enable RLS for users
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy for users
CREATE POLICY "Allow all operations on users" ON users
    FOR ALL USING (true) WITH CHECK (true);

-- Grant permissions
GRANT ALL ON users TO anon, authenticated;

-- Function to clean up old metrics (keep only last 24 hours)
CREATE OR REPLACE FUNCTION cleanup_old_metrics()
RETURNS void AS $$
BEGIN
    DELETE FROM cloud_metrics 
    WHERE timestamp < NOW() - INTERVAL '24 hours';
END;
$$ LANGUAGE plpgsql;

-- Create a trigger to automatically clean up old data every hour
-- (This would typically be done via a cron job in production)

COMMENT ON TABLE resources IS 'Cloud infrastructure resources (instances, containers, etc.)';
COMMENT ON TABLE cloud_metrics IS 'Real-time metrics for cloud resources';
COMMENT ON TABLE users IS 'Application users and authentication';

-- Sample data insertion (uncomment to add demo data)
/*
INSERT INTO resources (provider, instance_id, name, instance_type, region, state, hourly_usd) VALUES 
('aws', 'i-0e4acfa45d3b8c1a2', 'Web Server Production', 't3.medium', 'ap-south-1', 'running', 0.15),
('aws', 'i-07f8b9e2a1c4d5e6f', 'Backend API Server', 'm5.large', 'ap-south-1', 'running', 0.33),
('gcp', 'gcp-vm-34f2a8b1', 'Analytics Engine', 'n1-standard-2', 'asia-south1', 'running', 0.22),
('gcp', 'gcp-vm-98c3d4e5', 'Data Processing', 'n1-standard-4', 'asia-south1', 'stopped', 0.44),
('azure', 'az-vm-44d9c7b2', 'Database Server', 'Standard_D2s_v3', 'Central India', 'running', 0.27),
('azure', 'az-vm-f7e8a9b0', 'Backup Storage', 'Standard_B2s', 'Central India', 'stopped', 0.18);
*/