Idempotency Analysis & Migration Safety Validation

Overview

Implement advanced validation tools that analyze SQL migrations for idempotency issues and safety concerns. This is a critical capability that helps Claude agents ensure migrations are safe and can be run multiple times without side effects.

Claude Agent Success Criteria

A Claude agent should be able to:

✅ Test any migration for idempotency by running it multiple times
✅ Analyze SQL code and detect non-idempotent patterns
✅ Suggest specific fixes for common idempotency issues
✅ Validate all migrations in a project for safety

Detailed Acceptance Criteria

1. Idempotency Testing Tool

Implement test_migration_idempotency MCP tool:

{
  name: "test_migration_idempotency",
  description: "Test migration idempotency by running it multiple times",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      migrationFile: {
        type: "string",
        description: "Specific migration to test (defaults to current.sql)"
      },
      iterations: {
        type: "number",
        description: "Number of times to run migration (default: 3)",
        minimum: 2,
        maximum: 10
      }
    },
    required: ["projectPath"]
  }
}

Return Structure:

interface IdempotencyTestResult {
  success: boolean;
  data: {
    migration: {
      file: string;
      content: string;
      size: number;
    };
    test: {
      iterations: number;
      passed: boolean;
      duration: number;
      details: IterationResult[];
    };
    issues: IdempotencyIssue[];
    recommendations: string[];
  };
}

interface IterationResult {
  iteration: number;
  success: boolean;
  duration: number;
  changes?: string[];
  errors?: string[];
}

Create isolated test database for each run
Execute migration multiple times sequentially
Compare database state after each iteration
Detect changes between iterations
Report specific failure points

2. Static SQL Analysis Tool

Implement analyze_migration_safety MCP tool:

{
  name: "analyze_migration_safety",
  description: "Analyze migration SQL for safety and idempotency issues",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      migrationFile: {
        type: "string", 
        description: "Migration file to analyze (defaults to current.sql)"
      },
      includeRecommendations: {
        type: "boolean",
        description: "Include fix recommendations (default: true)"
      }
    },
    required: ["projectPath"]
  }
}

Analysis Categories:

interface SafetyAnalysis {
  idempotency: {
    score: number; // 0-100
    issues: IdempotencyIssue[];
    patterns: SafetyPattern[];
  };
  performance: {
    potentialImpact: 'low' | 'medium' | 'high';
    concerns: PerformanceConcern[];
  };
  safety: {
    destructive: boolean;
    risks: SafetyRisk[];
  };
  bestPractices: {
    score: number;
    recommendations: string[];
  };
}

Parse SQL using regex-based analysis
Detect non-idempotent patterns
Identify performance risks
Check for destructive operations
Validate best practices adherence

3. Idempotency Issue Detection

Implement comprehensive pattern detection:

Anti-Patterns to Detect:

const ANTI_PATTERNS = [
  {
    pattern: /CREATE TABLE (?!.*IF NOT EXISTS)/gi,
    issue: 'non_idempotent_table_creation',
    message: 'CREATE TABLE without IF NOT EXISTS',
    fix: 'Add IF NOT EXISTS clause'
  },
  {
    pattern: /CREATE INDEX (?!.*IF NOT EXISTS)/gi,
    issue: 'non_idempotent_index_creation', 
    message: 'CREATE INDEX without IF NOT EXISTS',
    fix: 'Add IF NOT EXISTS clause'
  },
  {
    pattern: /ALTER TABLE.*ADD COLUMN (?!.*IF NOT EXISTS)/gi,
    issue: 'non_idempotent_column_addition',
    message: 'ADD COLUMN without IF NOT EXISTS',
    fix: 'Add IF NOT EXISTS clause'
  },
  {
    pattern: /DROP TABLE (?!.*IF EXISTS)/gi,
    issue: 'non_idempotent_table_drop',
    message: 'DROP TABLE without IF EXISTS',
    fix: 'Add IF EXISTS clause'
  }
];

Safe Patterns to Recognize:

CREATE OR REPLACE FUNCTION
DO $$ BEGIN ... EXCEPTION WHEN duplicate_object ... blocks
IF NOT EXISTS and IF EXISTS clauses
Conditional constraint additions
Implement regex-based pattern detection
Support comprehensive SQL pattern analysis
Recognize safe idempotent patterns
Provide context-aware suggestions

4. Fix Suggestion Engine

Implement suggest_idempotency_fixes MCP tool:

{
  name: "suggest_idempotency_fixes",
  description: "Analyze SQL and suggest specific idempotency improvements",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      migrationContent: {
        type: "string",
        description: "SQL content to analyze and fix"
      },
      analysisType: {
        type: "string",
        enum: ["basic", "comprehensive"],
        description: "Level of analysis to perform"
      }
    },
    required: ["projectPath", "migrationContent"]
  }
}

Fix Suggestions:

interface FixSuggestion {
  issue: string;
  line: number;
  column?: number;
  originalCode: string;
  suggestedCode: string;
  explanation: string;
  confidence: 'high' | 'medium' | 'low';
}

interface FixResult {
  originalSql: string;
  fixedSql: string;
  suggestions: FixSuggestion[];
  warnings: string[];
}

Example Transformations:

-- BEFORE (non-idempotent)
CREATE TABLE users (id SERIAL PRIMARY KEY);

-- AFTER (idempotent)
CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY);

-- BEFORE (complex case)
CREATE TYPE user_role AS ENUM ('admin', 'user');

-- AFTER (idempotent with error handling)  
DO $$ BEGIN
    CREATE TYPE user_role AS ENUM ('admin', 'user');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

Generate specific line-by-line suggestions
Provide confidence scores for suggestions
Handle complex SQL constructs
Preserve original formatting and comments

5. Comprehensive Project Validation

Implement validate_all_migrations MCP tool:

{
  name: "validate_all_migrations",
  description: "Validate all migrations in project for safety and idempotency",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      includeIdempotencyTest: {
        type: "boolean",
        description: "Run actual idempotency tests (default: false - static analysis only)"
      },
      includeSafetyAnalysis: {
        type: "boolean", 
        description: "Include safety analysis (default: true)"
      },
      parallelExecution: {
        type: "boolean",
        description: "Run validations in parallel (default: true)"
      }
    },
    required: ["projectPath"]
  }
}

Analyze all committed migrations
Include current.sql if present
Generate comprehensive validation report
Support parallel validation for performance
Provide project-wide recommendations

Testing Requirements

Unit Tests

Test SQL pattern recognition and parsing
Test fix suggestion generation
Test idempotency analysis algorithms
Test edge cases and complex SQL constructs

Integration Tests

Test with real PostgreSQL databases
Test idempotency with actual migration runs
Test with various SQL constructs and patterns
Test performance with large migrations

Test Fixtures

Create comprehensive test SQL files:

Idempotent migration examples
Non-idempotent migration examples
Complex SQL with functions, types, triggers
Edge cases and corner cases
Performance-impacting migrations

Claude Agent Guidance

Typical Usage Workflow

// 1. Analyze current migration
const analysis = await tools.analyze_migration_safety({
  projectPath: "/path/to/project"
});

// 2. Get specific fix suggestions  
const fixes = await tools.suggest_idempotency_fixes({
  projectPath: "/path/to/project",
  migrationContent: currentMigrationContent
});

// 3. Test actual idempotency
const test = await tools.test_migration_idempotency({
  projectPath: "/path/to/project",
  iterations: 3
});

// 4. Validate entire project
const validation = await tools.validate_all_migrations({
  projectPath: "/path/to/project",
  includeIdempotencyTest: true
});

How to Verify Success

# Test static analysis
node -e "
const analysis = require('./dist/tools/analyze-migration-safety');
console.log(analysis.analyzeSQL('CREATE TABLE test();'));
"

# Test fix suggestions  
node -e "
const fixes = require('./dist/tools/suggest-fixes');
console.log(fixes.suggestFixes('CREATE INDEX test_idx ON test(id);'));
"

Expected Behavior

Static analysis should catch common anti-patterns
Fix suggestions should be actionable and safe
Idempotency testing should detect actual issues
Validation reports should be comprehensive and clear

Error Handling Examples

Database Connection Failed: "Cannot test idempotency: database connection failed. Check connection string."
Invalid SQL: "SQL syntax error at line 15: unexpected token 'CREAT'"
Test Database Error: "Failed to create test database. Ensure user has CREATE permissions."

Definition of Done

All validation tools implemented with comprehensive pattern detection
Idempotency testing works with real databases
Fix suggestion engine provides actionable recommendations
Static analysis covers all major anti-patterns
Integration tests validate real-world scenarios
>80% test coverage maintained
Performance optimized for large migrations
CLAUDE.md updated with validation examples

Estimated Effort

3-4 days

Dependencies

Issue #1 (closed): Setup MCP Server Foundation
Issue #2 (closed): Graphile Migrate CLI Integration
Issue #3 (closed): Migration Status & Management Tools

Technical Notes for Claude Agents

Key Files to Create

src/tools/test-idempotency.ts - Idempotency testing tool
src/tools/analyze-safety.ts - Static analysis tool
src/tools/suggest-fixes.ts - Fix suggestion engine
src/tools/validate-all.ts - Comprehensive validation
src/analysis/patterns.ts - Pattern detection rules
src/analysis/parser.ts - SQL parsing utilities

SQL Analysis Approach

Use regex patterns for simple detection
Implement SQL AST parsing for complex analysis
Support PostgreSQL-specific syntax
Handle comments and string literals correctly
Preserve original formatting in suggestions

Database Testing Strategy

Use temporary test databases
Implement proper cleanup procedures
Support multiple PostgreSQL versions
Handle connection pooling and timeouts
Provide detailed failure diagnostics

Edited Aug 12, 2025 by John Haley