Configuration Schema Migration Weakness

Configuration Migration Tracking:

CREATE TABLE config_migration_state (
    id SERIAL PRIMARY KEY,
    migration_id UUID DEFAULT gen_random_uuid(),
    config_category VARCHAR(100),
    total_configs INTEGER,
    migrated_configs INTEGER,
    validated_configs INTEGER,
    migration_started TIMESTAMP WITH TIME ZONE,
    migration_completed TIMESTAMP WITH TIME ZONE,
    rollback_available BOOLEAN DEFAULT true
);

CREATE TABLE config_validation_rules (
    id SERIAL PRIMARY KEY,
    config_key_pattern VARCHAR(255),
    validation_type VARCHAR(50) CHECK (validation_type IN ('RANGE', 'ENUM', 'REGEX', 'CUSTOM')),
    validation_rule JSONB,
    is_blocking BOOLEAN DEFAULT true,
    error_message TEXT
);

-- Validation function
CREATE OR REPLACE FUNCTION validate_config_value(
    p_key VARCHAR,
    p_value TEXT
) RETURNS BOOLEAN AS $
DECLARE
    v_rule config_validation_rules;
    v_valid BOOLEAN := true;
BEGIN
    FOR v_rule IN 
        SELECT * FROM config_validation_rules
        WHERE p_key ~ config_key_pattern
    LOOP
        CASE v_rule.validation_type
            WHEN 'RANGE' THEN
                v_valid := p_value::NUMERIC BETWEEN 
                    (v_rule.validation_rule->>'min')::NUMERIC AND 
                    (v_rule.validation_rule->>'max')::NUMERIC;
            WHEN 'ENUM' THEN
                v_valid := p_value = ANY(
                    ARRAY(SELECT jsonb_array_elements_text(v_rule.validation_rule->'values'))
                );
            WHEN 'REGEX' THEN
                v_valid := p_value ~ (v_rule.validation_rule->>'pattern');
        END CASE;
        
        IF NOT v_valid AND v_rule.is_blocking THEN
            RAISE EXCEPTION 'Config validation failed: %', v_rule.error_message;
        END IF;
    END LOOP;
    
    RETURN v_valid;
END;
$ LANGUAGE plpgsql;

Dual-System Reconciliation:

CREATE TABLE config_reconciliation_log (
    id BIGSERIAL PRIMARY KEY,
    check_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    old_system_count INTEGER,
    new_system_count INTEGER,
    conflicts_found INTEGER,
    conflicts_resolved INTEGER,
    manual_review_required INTEGER
);

-- Automated reconciliation
CREATE OR REPLACE FUNCTION reconcile_config_systems()
RETURNS void AS $
DECLARE
    v_conflict RECORD;
    v_conflicts_found INTEGER := 0;
    v_conflicts_resolved INTEGER := 0;
BEGIN
    -- Find conflicts
    FOR v_conflict IN
        SELECT 
            o.config_key,
            o.config_value as old_value,
            n.config_value as new_value,
            o.last_modified as old_modified,
            n.last_modified as new_modified
        FROM configuration_audit o
        JOIN configuration_audit n ON o.config_key = n.config_key
        WHERE o.config_source = 'old_system'
        AND n.config_source = 'new_system'
        AND o.config_value != n.config_value
    LOOP
        v_conflicts_found := v_conflicts_found + 1;
        
        -- Auto-resolve based on rules
        IF v_conflict.new_modified > v_conflict.old_modified THEN
            -- New system is more recent
            UPDATE configuration_audit
            SET validation_status = 'superseded'
            WHERE config_key = v_conflict.config_key
            AND config_source = 'old_system';
            
            v_conflicts_resolved := v_conflicts_resolved + 1;
        END IF;
    END LOOP;
    
    -- Log results
    INSERT INTO config_reconciliation_log 
        (conflicts_found, conflicts_resolved, manual_review_required)
    VALUES 
        (v_conflicts_found, v_conflicts_resolved, v_conflicts_found - v_conflicts_resolved);
END;
$ LANGUAGE plpgsql;

Critical Configuration Protection:

CREATE TABLE critical_config_changes (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(255),
    old_value TEXT,
    new_value TEXT,
    change_type VARCHAR(50),
    risk_score INTEGER,
    requires_approval BOOLEAN,
    approval_status VARCHAR(50),
    approved_by VARCHAR(255),
    scheduled_deployment TIMESTAMP WITH TIME ZONE
);

-- Prevent dangerous changes
CREATE OR REPLACE FUNCTION prevent_dangerous_config_changes()
RETURNS TRIGGER AS $
BEGIN
    -- Check for zero quotas
    IF NEW.config_key LIKE '%quota%' AND 
       NEW.config_value::NUMERIC = 0 AND
       OLD.config_value::NUMERIC > 0 THEN
        RAISE EXCEPTION 'Cannot set quota to zero without approval';
    END IF;
    
    -- Check for service disabling
    IF NEW.config_key LIKE '%enabled%' AND
       NEW.config_value = 'false' AND
       OLD.config_value = 'true' AND
       EXISTS (
           SELECT 1 FROM critical_services cs
           WHERE NEW.config_key LIKE '%' || cs.service_name || '%'
       ) THEN
        RAISE EXCEPTION 'Cannot disable critical service without approval';
    END IF;
    
    -- Check for extreme value changes
    IF NEW.config_key LIKE '%limit%' OR NEW.config_key LIKE '%timeout%' THEN
        IF ABS(NEW.config_value::NUMERIC - OLD.config_value::NUMERIC) / 
           NULLIF(OLD.config_value::NUMERIC, 0) > 0.5 THEN
            -- More than 50% change requires approval
            INSERT INTO critical_config_changes 
                (config_key, old_value, new_value, change_type, risk_score, requires_approval)
            VALUES 
                (NEW.config_key, OLD.config_value, NEW.config_value, 'MAJOR_CHANGE', 8, true);
            
            RAISE EXCEPTION 'Major configuration change requires approval';
        END IF;
    END IF;
    
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

Configuration Canary Deployment:

CREATE TABLE config_canary_deployments (
    id SERIAL PRIMARY KEY,
    deployment_id UUID DEFAULT gen_random_uuid(),
    config_changes JSONB,
    target_percentage INTEGER DEFAULT 1,
    current_percentage INTEGER DEFAULT 0,
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    success_criteria JSONB,
    failure_threshold INTEGER DEFAULT 5,
    current_failures INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'PENDING'
);

-- Monitor canary health
CREATE OR REPLACE FUNCTION check_config_canary_health(p_deployment_id UUID)
RETURNS VARCHAR AS $
DECLARE
    v_canary config_canary_deployments;
    v_error_rate DECIMAL;
    v_performance_degradation DECIMAL;
BEGIN
    SELECT * INTO v_canary 
    FROM config_canary_deployments 
    WHERE deployment_id = p_deployment_id;
    
    -- Check error rates in canary group
    SELECT error_count::DECIMAL / total_requests 
    INTO v_error_rate
    FROM service_metrics
    WHERE group_type = 'canary'
    AND deployment_id = p_deployment_id;
    
    IF v_error_rate > (v_canary.success_criteria->>'max_error_rate')::DECIMAL THEN
        UPDATE config_canary_deployments
        SET current_failures = current_failures + 1,
            status = CASE 
                WHEN current_failures + 1 >= failure_threshold THEN 'FAILED'
                ELSE status
            END
        WHERE deployment_id = p_deployment_id;
        
        RETURN 'UNHEALTHY';
    END IF;
    
    RETURN 'HEALTHY';
END;
$ LANGUAGE plpgsql;

Additional Best Practices:

• Implement configuration versioning with Git
• Use configuration schemas with type validation
• Create configuration diff tools
• Implement gradual rollout for config changes
• Maintain configuration dependencies graph