Implement Issue #4: Idempotency Analysis & Migration Safety Validation

Summary

This MR implements comprehensive idempotency analysis and migration safety validation tools for Graphile Migrate, addressing Issue #4 (closed).

🚀 New MCP Tools Added

1. test_migration_idempotency - Tests migrations by running them multiple times to detect idempotency violations
2. analyze_migration_safety - Performs static analysis for safety and idempotency issues using pattern detection
3. suggest_idempotency_fixes - Provides line-by-line fix suggestions with automated rewriting capabilities
4. validate_all_migrations - Validates entire project migration sets with comprehensive risk assessment

🏗️ Core Architecture

• idempotency-analyzer.ts - Pattern detection engine for SQL idempotency analysis
• sql-analyzer.ts - Safety analysis utilities for destructive operations and performance concerns
• 4 new tool modules - Complete MCP tool implementations with comprehensive error handling
• Extended schemas - New Zod types for all tool inputs/outputs with validation
• Updated server.ts - Registered all new tools with proper routing

✨ Key Features

• Line-by-line analysis with specific suggestions and confidence scoring
• Comprehensive pattern recognition (IF NOT EXISTS, CONCURRENTLY, ON CONFLICT, etc.)
• Risk assessment and safety scoring with detailed recommendations
• Automated fix application for high-confidence suggestions
• Project-wide validation with parallel execution support
• Performance impact analysis based on table sizes and operation complexity
• Best practices compliance scoring with actionable recommendations

🧪 Testing

• Complete TDD test suite with 6 test files covering all scenarios
• Integration tests for all tools with comprehensive edge cases
• Mock database operations and file system interactions
• Edge case handling (empty files, syntax errors, connection failures)
• Error handling validation with proper error messaging

📊 Analysis Capabilities

Idempotency Patterns Detected:

• CREATE TABLE/INDEX without IF NOT EXISTS
• INSERT without ON CONFLICT handling
• DROP operations without IF EXISTS
• CREATE TYPE without error handling
• Non-concurrent index creation
• Unqualified UPDATE/DELETE operations

Safety Risk Detection:

• Destructive operations (DROP, DELETE without WHERE)
• Data loss risks (ALTER COLUMN type changes)
• Performance concerns (blocking index creation, large table operations)
• Best practice violations (missing constraints, poor naming)

Performance Analysis:

• Table size impact assessment
• Lock duration estimation
• Concurrent execution recommendations
• Maintenance window suggestions

🔗 Resolves

Closes #4 (closed) - Idempotency Analysis & Migration Safety Validation

Test Plan

• All 4 tools function correctly when tested directly
• Build succeeds without errors
• Code formatting passes Prettier checks
• Comprehensive test coverage for all scenarios
• CI pipeline validation
• Integration tests with real PostgreSQL databases using testcontainers

Implementation Notes

The implementation follows a modular architecture with clear separation of concerns:

1. Core utilities handle SQL parsing and pattern detection
2. Tool modules implement MCP protocol compatibility
3. Schema definitions ensure type safety and validation
4. Test suite provides comprehensive coverage with TDD approach

All tools return consistent MCP-compliant responses with proper error handling and user-friendly messaging.