Shadow Database Management & Testing Infrastructure

Overview

Implement comprehensive shadow database management tools that enable safe migration testing and development workflows. Shadow databases are crucial for validating migrations without affecting the main database, making this a key developer experience feature.

Claude Agent Success Criteria

A Claude agent should be able to:

• ✅ Create and manage shadow databases for safe testing
• ✅ Test migrations against shadow databases before applying to main
• ✅ Compare shadow and main database states
• ✅ Clean up development databases automatically

Detailed Acceptance Criteria

1. Shadow Database Setup Tool

Implement setup_shadow_database MCP tool:

{
  name: "setup_shadow_database",
  description: "Create and configure shadow database for safe migration testing",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      recreate: {
        type: "boolean",
        description: "Drop and recreate existing shadow database (default: false)"
      },
      seedData: {
        type: "boolean",
        description: "Seed shadow database with test data (default: false)"
      }
    },
    required: ["projectPath"]
  }
}

Return Structure:

interface ShadowDatabaseSetup {
  success: boolean;
  data: {
    shadowDatabase: {
      name: string;
      connectionString: string;
      created: boolean;
      migrationsApplied: number;
      seedDataLoaded: boolean;
    };
    operations: {
      databaseCreated: boolean;
      migrationsRun: boolean;
      seedingCompleted: boolean;
      duration: number;
    };
    recommendations: string[];
  };
}

• Generate unique shadow database names
• Create shadow database with proper permissions
• Apply all committed migrations to shadow database
• Support optional test data seeding
• Validate shadow database configuration

2. Shadow Database Testing Tool

Implement test_against_shadow MCP tool:

{
  name: "test_against_shadow",
  description: "Test migration against shadow database before applying to main",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      migrationSql: {
        type: "string",
        description: "SQL to test (defaults to current.sql content)"
      }
    },
    required: ["projectPath"]
  }
}

Test Results:

interface ShadowTestResult {
  success: boolean;
  data: {
    test: {
      migration: {
        content: string;
        size: number;
      };
      execution: {
        successful: boolean;
        duration: number;
        statementsExecuted: number;
        warnings: string[];
      };
      idempotency: {
        tested: boolean;
        passed: boolean;
        iterations: number;
      };
    };
    shadowDatabase: {
      beforeState: SchemaSnapshot;
      afterState: SchemaSnapshot;
      changes: string[];
    };
    validation: {
      errors: ValidationError[];
      warnings: ValidationWarning[];
      suggestions: string[];
    };
  };
}

• Execute migration against shadow database
• Capture before/after schema state
• Run optional idempotency tests
• Detect and report any issues
• Provide rollback information if needed

3. Shadow Database Comparison Tool

Implement compare_with_shadow MCP tool:

{
  name: "compare_with_shadow",
  description: "Compare main database schema with shadow database",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      }
    },
    required: ["projectPath"]
  }
}

Comparison Results:

interface ShadowComparison {
  success: boolean;
  data: {
    comparison: {
      identical: boolean;
      differenceCount: number;
      mainDatabase: DatabaseInfo;
      shadowDatabase: DatabaseInfo;
    };
    differences: {
      onlyInMain: SchemaObject[];
      onlyInShadow: SchemaObject[];
      different: SchemaDifference[];
    };
    recommendations: {
      syncToMain: string[];
      syncToShadow: string[];
      potentialIssues: string[];
    };
  };
}

• Compare schema objects between main and shadow
• Identify objects unique to each database
• Detect differences in shared objects
• Suggest synchronization strategies
• Highlight potential migration issues

4. Development Database Cleanup Tool

Implement cleanup_development_databases MCP tool:

{
  name: "cleanup_development_databases",
  description: "Clean up shadow and test databases to free resources",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      includeShadow: {
        type: "boolean",
        description: "Clean up shadow databases (default: true)"
      },
      includeTest: {
        type: "boolean",
        description: "Clean up test databases (default: true)"
      },
      dryRun: {
        type: "boolean",
        description: "Preview cleanup without deleting (default: false)"
      }
    },
    required: ["projectPath"]
  }
}

Cleanup Results:

interface CleanupResult {
  success: boolean;
  data: {
    discovered: {
      shadowDatabases: string[];
      testDatabases: string[];
      temporaryDatabases: string[];
    };
    cleaned: {
      deleted: string[];
      errors: string[];
      skipped: string[];
    };
    summary: {
      totalDeleted: number;
      spaceSaved: number;
      duration: number;
    };
  };
}

• Discover shadow and test databases automatically
• Support dry-run mode for safety
• Delete databases with proper error handling
• Report space savings and cleanup results
• Preserve databases that are still in use

5. Shadow Database Utilities

Create supporting utilities in src/shadow/:

Shadow Database Manager:

interface ShadowDatabaseManager {
  create(projectPath: string, options?: CreateOptions): Promise<ShadowDatabase>;
  exists(projectPath: string): Promise<boolean>;
  connect(projectPath: string): Promise<DatabaseConnection>;
  destroy(projectPath: string): Promise<boolean>;
  reset(projectPath: string): Promise<void>;
  applyMigrations(projectPath: string): Promise<MigrationResult>;
}

interface CreateOptions {
  recreate?: boolean;
  seedData?: boolean;
  copyFromMain?: boolean;
}

interface ShadowDatabase {
  name: string;
  connectionString: string;
  created: Date;
  lastUsed: Date;
  migrations: string[];
}

Database Schema Snapshots:

interface SchemaSnapshot {
  timestamp: Date;
  database: string;
  tables: TableSnapshot[];
  indexes: IndexSnapshot[];
  functions: FunctionSnapshot[];
  constraints: ConstraintSnapshot[];
  checksum: string;
}

interface TableSnapshot {
  name: string;
  schema: string;
  columns: ColumnSnapshot[];
  rowCount: number;
  checksum: string;
}

• Implement shadow database lifecycle management
• Create schema snapshot functionality
• Support database seeding and copying
• Handle cleanup and resource management
• Provide detailed logging and monitoring

Testing Requirements

Unit Tests

• Test shadow database creation and management
• Test schema snapshot and comparison logic
• Test migration execution against shadow databases
• Test cleanup and resource management

Integration Tests

• Test with real PostgreSQL shadow databases
• Test migration workflows end-to-end
• Test concurrent shadow database operations
• Test resource cleanup and management

Test Fixtures

Create test scenarios with:

• Various migration complexity levels
• Different database sizes and structures
• Error scenarios (permissions, disk space, etc.)
• Cleanup scenarios with multiple shadow databases

Claude Agent Guidance

Typical Shadow Database Workflow

// 1. Setup shadow database
const setup = await tools.setup_shadow_database({
  projectPath: "/path/to/project",
  seedData: true
});

// 2. Test migration against shadow
const test = await tools.test_against_shadow({
  projectPath: "/path/to/project"
});

// 3. Compare with main database
const comparison = await tools.compare_with_shadow({
  projectPath: "/path/to/project"
});

// 4. Cleanup when done
const cleanup = await tools.cleanup_development_databases({
  projectPath: "/path/to/project",
  dryRun: false
});

How to Verify Success

# Test shadow database creation
node -e "
const shadow = require('./dist/tools/setup-shadow');
shadow.setupShadowDatabase('/path/to/project').then(console.log);
"

# Test migration against shadow
node -e "
const test = require('./dist/tools/test-against-shadow');
test.testMigration('/path/to/project').then(console.log);
"

Expected Behavior

• Shadow database should be created quickly and reliably
• Migration testing should catch issues before main database
• Comparisons should highlight important differences
• Cleanup should free resources without errors

Error Handling Examples

• Insufficient Permissions: "Cannot create shadow database. User needs CREATE DATABASE privileges."
• Disk Space: "Shadow database creation failed: insufficient disk space."
• Connection Limit: "Cannot connect to shadow database: too many connections."

Definition of Done

• All shadow database tools implemented and working
• Migration testing provides comprehensive validation
• Database comparison detects all meaningful differences
• Cleanup tools work safely and efficiently
• Integration tests pass with real databases
• >80% test coverage maintained
• Performance optimized for typical development usage
• CLAUDE.md updated with shadow database examples

Estimated Effort

3-4 days

Dependencies

• Issue #1 (closed): Setup MCP Server Foundation
• Issue #2 (closed): Graphile Migrate CLI Integration
• Issue #5 (closed): Schema Analysis & Database Comparison Tools

Technical Notes for Claude Agents

Key Files to Create

• src/tools/setup-shadow.ts - Shadow database setup tool
• src/tools/test-against-shadow.ts - Shadow testing tool
• src/tools/compare-with-shadow.ts - Shadow comparison tool
• src/tools/cleanup-databases.ts - Database cleanup tool
• src/shadow/manager.ts - Shadow database management
• src/shadow/snapshots.ts - Schema snapshot utilities

Shadow Database Naming Strategy

• Use consistent naming pattern: {project}_{timestamp}_shadow
• Include project identifier to avoid conflicts
• Support cleanup by pattern matching
• Handle long project names gracefully

Resource Management

• Implement connection pooling for shadow databases
• Monitor and limit resource usage
• Provide warnings for resource-intensive operations
• Auto-cleanup stale shadow databases
• Handle PostgreSQL connection limits gracefully

Performance Considerations

• Optimize schema snapshot generation
• Use parallel operations where safe
• Implement progressive schema comparison
• Cache shadow database connections
• Provide progress feedback for long operations