Skip to content
TheChatArchiveThe Chat Archive
GLMGLM-5 / Pony Alpha· unknownHugging Face Frontier Reasoning Traces

Implement a schema change approval bot: impact analysis, reviewers, and rollout windows.

  1. User

    Implement a schema change approval bot: impact analysis, reviewers, and rollout windows.

  2. GLM Assistant

    The user wants a "schema change approval bot" implemented. This implies a system that:

    1. Analyzes the impact of proposed schema changes (SQL, Avro, Protobuf, etc.).
    2. Determines who should review the changes based on dependencies or ownership.
    3. Suggests or enforces rollout windows (maintenance windows) for applying these changes.

    I need to design the architecture and provide a concrete implementation (likely in Python) to demonstrate these concepts.

    Key Components:

    1. Schema Parser & Diff Engine: Needs to read a "proposed" schema and an "existing" schema, then calculate the diff. For simplicity, I'll focus on SQL (PostgreSQL/MySQL style) as it's the most common use case, but I'll abstract it enough to be extendable.
    2. Impact Analyzer: Classifies changes (Add, Drop, Modify). "Drop Column" is high risk. "Add Column" is usually low risk. "Modify Type" is medium risk.
    3. Dependency Graph/Codebase Search: To determine reviewers, the bot needs to know which services/tables use the column being changed.
      • Simplification: In a code example, I can't search a whole company's repo. I will mock a "Service Registry" or "Usage Map."
    4. Reviewer Assignment Logic: Based on the impact and dependencies.
      • Low risk -> Auto-approve or require 1 reviewer.
      • High risk -> Require specific team leads + DBAs.
    5. Rollout Window Manager: Checks the current time against allowed maintenance windows.
      • Simplification: A configuration of allowed hours.

    Design/Architecture:

    • Language: Python is great for this.
    • Input: A PR description or a file path containing the DDL (Data Definition Language).
    • Output: A report (Markdown/JSON) containing Impact, Required Reviewers, and Rollout Status.

    Step-by-Step Implementation Plan:

    1. Define Data Structures:
      • SchemaChange: (table, column, type, change_type).
      • Service: (name, owners, dependencies).
    2. Mock Data:
      • Create a mock "Production Schema" state.
      • Create a mock "Service Dependency" map.
    3. The Logic (The Bot):
      • analyze_change(): Parse SQL -> Identify operation -> Check risk.
      • identify_reviewers(): Look up dependencies -> Map to users.
      • check_rollout_window(): Time checks.
    4. The Interface: A CLI script or a class structure that simulates the workflow.

    Drafting the Code Logic (Mental Sandbox):

    • Parsing SQL: Real SQL parsing is hard. I'll use a regex approach or a simple library like sqlparse if available, but for a standalone snippet, a mock parser or simple string matching is safer to ensure the code runs without heavy dependencies. Actually, sqlparse is standard enough, but maybe I'll just mock the "parsed diff" to focus on the logic of the bot rather than writing a SQL compiler. Decision: I will implement a simple SchemaDiff class that takes before/after states to calculate the impact, simulating what a tool like Flyway or Skeema would do.

    • Impact Analysis:

      • ADD COLUMN -> Risk: Low (usually).
      • DROP COLUMN -> Risk: Critical (breaking change).
      • RENAME COLUMN -> Risk: High.
      • ADD INDEX -> Risk: Medium (locks table?).
      • CHANGE TYPE -> Risk: Medium/High (triggers rewrite?).
    • Reviewers:

      • Need a DependencyStore.
      • Input: Table: users.
      • Output: Services: [Auth-Service, Billing-Service].
      • Owners: [Alice, Bob].
    • Rollout Windows:

      • Config: allowed_windows: { "Mon-Wed": "02:00-04:00" }.

    Refining the Prompt Response: The user asked to implement it. I should provide a complete, runnable Python script that simulates this bot. I will structure it as a class SchemaApprovalBot.

    Features to Include in the Python Script:

    1. ChangeRequest object.
    2. ImpactAnalyzer class.
    3. ReviewerRecommender class.
    4. RolloutScheduler class.
    5. main execution block showing the workflow.

    Let's write the code.

    • Self-Correction during drafting: Don't make it too complex with actual DB connections. Mock the inputs. Focus on the decision logic.

    Structure of the explanation:

    1. Architecture Overview: Explain the three pillars (Analysis, People, Time).
    2. Implementation Strategy: How we map risks to actions.
    3. Code: The Python script.
    4. Integration Guide: How to plug this into CI/CD (GitHub/GitLab).

    Let's detail the "Impact Analysis" logic.

    • Backward Compatibility: Is the change backward compatible?
      • Dropping a column used by Service A is not.
      • Renaming requires code deploy coordination.

    Let's detail the "Rollout Window".

    • High risk changes should only happen during low traffic.
    • Low risk (add nullable column) can happen anytime (usually).

    Python Code Sketch:

    class SchemaChange:
        type: str # ADD_COLUMN, DROP_COLUMN, etc.
        table: str
        details: str
    
    class ImpactAnalyzer:
        def analyze(self, changes):
            # classify risk
            pass
    
    class Reviewers:
        def get_owners(self, table):
            # look up service owners
            pass
    
    class Rollout:
        def is_safe_now(self, risk_level):
            # check time
            pass
    

    I will use the datetime library for the rollout window logic. I will use a dictionary to represent the "Service Dependency Graph".

    Final Polish: Ensure the output is readable and looks like a real Bot report.

    • Constraint Checklist & Confidence Score:

      1. Impact analysis? Yes.
      2. Reviewers? Yes.
      3. Rollout windows? Yes. Confidence Score: 5/5.
    • Mental Sandbox Simulation:

      • Input: ALTER TABLE users DROP COLUMN email;
      • Bot:
        • Detects DROP COLUMN.
        • Risk: CRITICAL.
        • Dependencies: MarketingService uses users.email.
        • Reviewers: Team Marketing, Team DBA.
        • Rollout: Must be Sunday 3 AM.
      • Output: "APPROVAL REQUIRED. Blockers: Breaking change for MarketingService."

    This looks solid. I will write the response following this structure. I will implement a CLI-style Python script that mocks the DB state and dependency graph to demonstrate the logic. No external libraries needed (pure Python) so it runs everywhere immediately.

    I will also add a section on how to extend this for real-world use (e.g., parsing actual SQL files, integrating with GitHub APIs).

    One distinct detail: "Rollout Windows". I should implement a check that says "Is it currently within a maintenance window?" or "Suggest next window." I'll do "Suggest next window".

    Let's execute. This solution implements a Schema Change Approval Bot in Python. It is designed to be integrated into a CI/CD pipeline (like GitHub Actions or GitLab CI).

    The bot performs three main tasks:

    1. Impact Analysis: Parses SQL to classify changes (e.g., ADD COLUMN vs. DROP TABLE) and assigns a risk level.
    2. Reviewer Assignment: cross-references schema changes with a service dependency map to notify the correct teams.
    3. Rollout Windows: Checks if the deployment time falls within a allowed maintenance window.

    1. The Architecture

    • Input: A directory containing migration files (SQL) and a config.yaml defining ownership and windows.
    • Core: The SchemaBot engine.
    • Output: A JSON report and exit code (0 for pass, 1 for fail/block).

    2. The Implementation

    Create a file named schema_bot.py.

    import re
    import json
    import os
    import sys
    from datetime import datetime, time
    from typing import List, Dict, Optional
    
    # --- Data Models ---
    
    class RiskLevel:
        LOW = "LOW"          # e.g., Add Column
        MEDIUM = "MEDIUM"    # e.g., Create Index, Alter Column
        HIGH = "HIGH"        # e.g., Drop Column, Rename
        CRITICAL = "CRITICAL" # e.g., Drop Table
    
    class SchemaChange:
        def __init__(self, statement: str, change_type: str, table: str, risk: str):
            self.statement = statement
            self.change_type = change_type
            self.table = table
            self.risk = risk
    
        def to_dict(self):
            return {
                "statement": self.statement,
                "type": self.change_type,
                "table": self.table,
                "risk": self.risk
            }
    
    # --- Core Components ---
    
    class SQLParser:
        """
        Parses SQL files to detect schema changes and assess impact.
        """
        
        # Regex patterns to detect changes (Simplified for demonstration)
        PATTERNS = {
            r"^\s*CREATE\s+TABLE": ("CREATE_TABLE", RiskLevel.MEDIUM),
            r"^\s*DROP\s+TABLE": ("DROP_TABLE", RiskLevel.CRITICAL),
            r"^\s*ALTER\s+TABLE.*\s+ADD\s+COLUMN": ("ADD_COLUMN", RiskLevel.LOW),
            r"^\s*ALTER\s+TABLE.*\s+DROP\s+COLUMN": ("DROP_COLUMN", RiskLevel.HIGH),
            r"^\s*ALTER\s+TABLE.*\s+ALTER\s+COLUMN": ("MODIFY_COLUMN", RiskLevel.MEDIUM),
            r"^\s*ALTER\s+TABLE.*\s+ADD\s+INDEX": ("ADD_INDEX", RiskLevel.MEDIUM),
            r"^\s*CREATE\s+INDEX": ("ADD_INDEX", RiskLevel.MEDIUM),
        }
    
        def analyze(self, sql_content: str) -> List[SchemaChange]:
            changes = []
            # Split by semicolon to get individual statements
            statements = sql_content.split(';')
            
            for stmt in statements:
                clean_stmt = stmt.strip()
                if not clean_stmt: continue
    
                detected_type = None
                detected_risk = None
                table_name = "unknown"
    
                for pattern, (change_type, risk) in self.PATTERNS.items():
                    if re.match(pattern, clean_stmt, re.IGNORECASE):
                        detected_type = change_type
                        detected_risk = risk
                        
                        # Extract table name (very naive extraction)
                        tokens = clean_stmt.split()
                        if "TABLE" in tokens:
                            idx = tokens.index("TABLE")
                            if len(tokens) > idx + 1:
                                table_name = tokens[idx + 1].strip("`\"")
                        break
                
                if detected_type:
                    changes.append(SchemaChange(clean_stmt, detected_type, table_name, detected_risk))
            
            return changes
    
    class ReviewerRecommender:
        """
        Maps schema changes to specific reviewers based on service ownership.
        """
        def __init__(self, service_map: Dict[str, List[str]]):
            # Format: {"table_name": ["team-a", "team-b"]}
            self.service_map = service_map
    
        def get_reviewers(self, changes: List[SchemaChange]) -> List[str]:
            required_teams = set()
            
            for change in changes:
                # Check if we know who owns this table
                owners = self.service_map.get(change.table)
                if owners:
                    required_teams.update(owners)
                else:
                    # If unknown, require DBAs
                    required_teams.add("dba-team")
            
            return list(required_teams)
    
    class RolloutScheduler:
        """
        Validates if the current time fits within allowed maintenance windows.
        """
        def __init__(self, windows: Dict[str, Dict[str, str]]):
            # Format: {"default": {"start": "22:00", "end": "04:00"}}
            self.windows = windows
    
        def can_deploy(self, risk: str) -> bool:
            # Low risk changes can happen anytime
            if risk == RiskLevel.LOW:
                return True
    
            # High/Critical changes require specific windows
            now = datetime.now().time()
            
            # Logic to check against 'default' window
            # (In real world, you might map specific risks to specific windows)
            window = self.windows.get("default")
            if not window:
                return False # Fail safe
    
            start_time = datetime.strptime(window["start"], "%H:%M").time()
            end_time = datetime.strptime(window["end"], "%H:%M").time()
    
            # Handle overnight window (e.g., 22:00 to 04:00)
            if start_time > end_time:
                return now >= start_time or now <= end_time
            else:
                return start_time <= now <= end_time
    
    # --- The Bot Orchestrator ---
    
    class SchemaApprovalBot:
        def __init__(self, config: Dict):
            self.parser = SQLParser()
            self.recommender = ReviewerRecommender(config.get("service_map", {}))
            self.scheduler = RolloutScheduler(config.get("rollout_windows", {}))
    
        def run(self, migration_path: str):
            print(f"🔍 Analyzing migrations in: {migration_path}")
            
            report = {
                "status": "PENDING",
                "changes": [],
                "required_reviewers": [],
                "errors": []
            }
    
            # 1. Read SQL files
            if not os.path.exists(migration_path):
                report["errors"].append("Migration file not found.")
                report["status"] = "ERROR"
                return report
    
            with open(migration_path, 'r') as f:
                content = f.read()
    
            # 2. Analyze Impact
            changes = self.parser.analyze(content)
            if not changes:
                report["status"] = "NO_CHANGES"
                return report
    
            report["changes"] = [c.to_dict() for c in changes]
            
            # Calculate Max Risk
            max_risk = RiskLevel.LOW
            risk_order = [RiskLevel.LOW, RiskLevel.MEDIUM, RiskLevel.HIGH, RiskLevel.CRITICAL]
            for c in changes:
                if risk_order.index(c.risk) > risk_order.index(max_risk):
                    max_risk = c.risk
    
            # 3. Assign Reviewers
            report["required_reviewers"] = self.recommender.get_reviewers(changes)
    
            # 4. Check Rollout Window
            is_safe_time = self.scheduler.can_deploy(max_risk)
    
            # 5. Final Decision
            if max_risk in [RiskLevel.HIGH, RiskLevel.CRITICAL] and not is_safe_time:
                report["status"] = "BLOCKED_ROLLOUT_WINDOW"
                report["errors"].append(f"Change risk is {max_risk}, but current time is outside maintenance window.")
            elif max_risk == RiskLevel.CRITICAL:
                report["status"] = "NEEDS_MANUAL_APPROVAL"
            else:
                report["status"] = "AUTO_APPROVED"
    
            return report
    
    # --- Main Execution ---
    
    if __name__ == "__main__":
        # Mock Configuration (Usually loaded from YAML)
        bot_config = {
            "service_map": {
                "users": ["identity-team", "backend-core"],
                "orders": ["payments-team", "backend-core"],
                "logs": ["devops-team"]
            },
            "rollout_windows": {
                "default": {
                    "start": "22:00", # 10 PM
                    "end": "04:00"    # 4 AM
                }
            }
        }
    
        # Create a dummy migration file for demonstration
        migration_file = "migration_test.sql"
        with open(migration_file, "w") as f:
            f.write("""
            ALTER TABLE users ADD COLUMN last_login TIMESTAMP; -- Low Risk
            DROP TABLE orders; -- Critical Risk
            """)
    
        # Run the Bot
        bot = SchemaApprovalBot(bot_config)
        result = bot.run(migration_file)
    
        print("\n--- BOT REPORT ---")
        print(json.dumps(result, indent=2))
    
        # Cleanup
        os.remove(migration_file)
    
        # Exit with code based on status
        if result["status"] in ["BLOCKED_ROLLOUT_WINDOW", "ERROR"]:
            sys.exit(1)
        sys.exit(0)
    

    3. How It Works

    A. Impact Analysis (SQLParser)

    The parser uses Regular Expressions to scan the SQL statement.

    • Example: If it sees DROP TABLE, it classifies the risk as CRITICAL.
    • Example: If it sees ADD COLUMN, it classifies it as LOW.

    B. Reviewers (ReviewerRecommender)

    It looks at the tables being modified.

    • Logic: If the orders table is touched, it looks at the service_map in the config and mandates the payments-team.
    • Safety Net: If a table is not found in the map, it defaults to the dba-team.

    C. Rollout Windows (RolloutScheduler)

    It compares the current system time against the configured window.

    • Logic: If the risk is LOW, it allows deployment anytime.
    • Logic: If the risk is CRITICAL (like dropping a table), it checks if the current time is between 22:00 and 04:00. If you run this at 2:00 PM, it blocks the deployment.

    4. Example Output

    If you run the script above during business hours (e.g., 14:00), the bot will block the DROP TABLE operation.

    --- BOT REPORT ---
    {
      "status": "BLOCKED_ROLLOUT_WINDOW",
      "changes": [
        {
          "statement": "ALTER TABLE users ADD COLUMN last_login TIMESTAMP",
          "type": "ADD_COLUMN",
          "table": "users",
          "risk": "LOW"
        },
        {
          "statement": "DROP TABLE orders",
          "type": "DROP_TABLE",
          "table": "orders",
          "risk": "CRITICAL"
        }
      ],
      "required_reviewers": [
        "identity-team",
        "backend-core",
        "payments-team"
      ],
      "errors": [
        "Change risk is CRITICAL, but current time is outside maintenance window."
      ]
    }
    

    5. Integration Strategy

    To make this a real bot:

    1. GitHub Actions: Create a workflow that runs on Pull Requests containing SQL files.
      # .github/workflows/schema_check.yml
      on: [pull_request]
      jobs:
        check_schema:
          runs-on: ubuntu-latest
          steps:
            - uses: actions/checkout@v2
            - name: Run Schema Bot
              run: python schema_bot.py
      
    2. Slack Notification: If the status is BLOCKED_... or NEEDS_MANUAL_APPROVAL, use a webhook to ping the required_reviewers.
    3. Database Gate: Configure your deployment tool (like Flyway or Alembic) to only execute migrations if the Bot returns exit code 0.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.