96 lines
3.8 KiB
PL/PgSQL
96 lines
3.8 KiB
PL/PgSQL
-- Server management tables
|
|
-- Merged from control-plane-api/migrations/001_initial.sql
|
|
|
|
-- Servers table
|
|
CREATE TABLE IF NOT EXISTS servers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL UNIQUE,
|
|
template VARCHAR(100) NOT NULL,
|
|
pillar VARCHAR(50) NOT NULL DEFAULT 'worker',
|
|
provider VARCHAR(50) NOT NULL DEFAULT 'generic',
|
|
vps_server_id VARCHAR(100) NOT NULL,
|
|
ip_address VARCHAR(50) NOT NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'provisioning',
|
|
environment VARCHAR(50) DEFAULT 'production',
|
|
region VARCHAR(50) NOT NULL DEFAULT 'local',
|
|
plan VARCHAR(50) NOT NULL DEFAULT 'custom',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
last_heartbeat TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Scaling operations tracking table
|
|
CREATE TABLE IF NOT EXISTS scaling_operations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
operation_type VARCHAR(50) NOT NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
total_steps INTEGER NOT NULL,
|
|
completed_steps INTEGER DEFAULT 0,
|
|
details JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Backups table
|
|
CREATE TABLE IF NOT EXISTS backups (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
url VARCHAR(500) NOT NULL,
|
|
size_bytes BIGINT DEFAULT 0,
|
|
status VARCHAR(50) DEFAULT 'completed',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
expires_at TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
-- Server metrics table
|
|
CREATE TABLE IF NOT EXISTS server_metrics (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
server_id UUID REFERENCES servers(id) ON DELETE CASCADE,
|
|
cpu_usage DECIMAL(5,2),
|
|
memory_usage DECIMAL(5,2),
|
|
disk_usage DECIMAL(5,2),
|
|
connections_count INTEGER,
|
|
status VARCHAR(50),
|
|
recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Cluster events table (audit log)
|
|
CREATE TABLE IF NOT EXISTS cluster_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
event_type VARCHAR(100) NOT NULL,
|
|
server_id UUID REFERENCES servers(id) ON DELETE SET NULL,
|
|
details JSONB,
|
|
initiated_by VARCHAR(255) DEFAULT 'system',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_servers_status ON servers(status);
|
|
CREATE INDEX IF NOT EXISTS idx_servers_template ON servers(template);
|
|
CREATE INDEX IF NOT EXISTS idx_servers_provider ON servers(provider);
|
|
CREATE INDEX IF NOT EXISTS idx_servers_pillar ON servers(pillar);
|
|
CREATE INDEX IF NOT EXISTS idx_servers_created_at ON servers(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_backups_created_at ON backups(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_server_metrics_server_id ON server_metrics(server_id);
|
|
CREATE INDEX IF NOT EXISTS idx_server_metrics_recorded_at ON server_metrics(recorded_at);
|
|
CREATE INDEX IF NOT EXISTS idx_cluster_events_server_id ON cluster_events(server_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cluster_events_created_at ON cluster_events(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_scaling_operations_status ON scaling_operations(status);
|
|
|
|
-- Trigger to update updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_servers_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
DROP TRIGGER IF EXISTS update_servers_updated_at ON servers;
|
|
CREATE TRIGGER update_servers_updated_at BEFORE UPDATE ON servers
|
|
FOR EACH ROW EXECUTE FUNCTION update_servers_updated_at_column();
|
|
|
|
-- Insert default control plane server entry
|
|
INSERT INTO servers (name, template, pillar, provider, vps_server_id, ip_address, status, region, plan)
|
|
VALUES ('control-plane-1', 'control-plane-node', 'system', 'generic', 'local', '127.0.0.1', 'active', 'local', 'custom')
|
|
ON CONFLICT (name) DO NOTHING;
|