Migration Status & Management Tools

Overview

Implement essential MCP tools for migration status checking and basic migration management. These are the core tools that Claude agents will use most frequently for understanding and managing migration state.

Claude Agent Success Criteria

A Claude agent should be able to:

• ✅ Check current migration status and get comprehensive project state
• ✅ Create new migrations with proper naming and structure
• ✅ Commit and uncommit migrations during iterative development
• ✅ Understand migration history and pending changes

Detailed Acceptance Criteria

1. Migration Status Tool

Implement get_migration_status MCP tool:

{
  name: "get_migration_status",
  description: "Get comprehensive migration status and project information",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      detailed: {
        type: "boolean",
        description: "Include detailed migration information (default: true)"
      },
      includeHistory: {
        type: "boolean", 
        description: "Include complete migration history (default: false)"
      }
    },
    required: ["projectPath"]
  }
}

Return Structure:

interface MigrationStatusResponse {
  success: boolean;
  data: {
    project: {
      name: string;
      path: string;
      configValid: boolean;
      hasCurrentMigration: boolean;
    };
    current: {
      exists: boolean;
      filename?: string;
      path?: string;
      content?: string;
      size?: number;
      lastModified?: Date;
    };
    committed: MigrationInfo[];
    pending: string[];
    database: {
      connected: boolean;
      migrationsApplied: number;
      lastMigrationApplied?: string;
    };
    history?: MigrationInfo[]; // Only if includeHistory is true
  };
}

• Parse and validate project configuration
• Read current.sql file if it exists
• List all committed migrations with metadata
• Check database connection and applied migrations
• Provide rich project status information

2. Create Migration Tool

Implement create_migration MCP tool:

{
  name: "create_migration", 
  description: "Create a new migration file with optional template",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      migrationName: {
        type: "string",
        description: "Descriptive name for the migration"
      },
      template: {
        type: "string",
        enum: ["table", "function", "index", "custom", "empty"],
        description: "Template to use for migration structure"
      },
      initialContent: {
        type: "string",
        description: "Initial SQL content for the migration"
      }
    },
    required: ["projectPath", "migrationName"]
  }
}

Templates to Support:

• table: CREATE TABLE with common patterns

• function: CREATE OR REPLACE FUNCTION template

• index: Index creation with idempotent patterns

• empty: Blank migration file

• custom: User-provided initial content

• Generate migration files with proper naming

• Support multiple migration templates

• Ensure idempotent patterns by default

• Add helpful comments and structure

3. Commit Migration Tool

Implement commit_migration MCP tool:

{
  name: "commit_migration",
  description: "Commit current.sql to a numbered migration file",
  inputSchema: {
    type: "object", 
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      message: {
        type: "string",
        description: "Commit message for migration history"
      },
      validate: {
        type: "boolean",
        description: "Validate migration before committing (default: true)"
      }
    },
    required: ["projectPath"]
  }
}

• Move current.sql to appropriately numbered committed file
• Update migration tracking
• Optional pre-commit validation
• Generate helpful commit messages if none provided

4. Uncommit Migration Tool

Implement uncommit_migration MCP tool:

{
  name: "uncommit_migration",
  description: "Move committed migration back to current.sql for editing", 
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string", 
        description: "Path to the Graphile Migrate project"
      },
      count: {
        type: "number",
        description: "Number of migrations to uncommit (default: 1)",
        minimum: 1,
        maximum: 10
      },
      force: {
        type: "boolean",
        description: "Force uncommit even if current.sql exists"
      }
    },
    required: ["projectPath"]
  }
}

• Move latest committed migration to current.sql
• Handle conflicts with existing current.sql
• Support uncommitting multiple migrations
• Provide clear warnings for destructive operations

5. Migration File Management

Create supporting utilities in src/graphile-migrate/files.ts:

interface MigrationFile {
  filename: string;
  path: string;
  number?: number;
  name: string;
  content: string;
  hash: string;
  size: number;
  createdAt: Date;
  modifiedAt: Date;
}

interface FileManager {
  readCurrentMigration(projectPath: string): Promise<MigrationFile | null>;
  writeCurrentMigration(projectPath: string, content: string): Promise<void>;
  listCommittedMigrations(projectPath: string): Promise<MigrationFile[]>;
  commitMigration(projectPath: string, message?: string): Promise<MigrationFile>;
  uncommitMigration(projectPath: string, count?: number): Promise<MigrationFile>;
}

• Implement file I/O operations for migrations
• Handle migration numbering automatically
• Support migration file metadata
• Include file integrity checking

6. Migration Templates

Create migration templates in src/templates/:

Table Template:

-- Create table: ${migrationName}
-- Auto-generated on ${timestamp}

CREATE TABLE IF NOT EXISTS app_public.${tableName} (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add indexes
CREATE INDEX IF NOT EXISTS ${tableName}_created_at_idx 
  ON app_public.${tableName}(created_at);

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON app_public.${tableName} TO :DATABASE_VISITOR;

Function Template:

-- Function: ${migrationName}
-- Auto-generated on ${timestamp}

CREATE OR REPLACE FUNCTION app_public.${functionName}()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Function implementation here
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- Grant execute permissions
GRANT EXECUTE ON FUNCTION app_public.${functionName}() TO :DATABASE_VISITOR;

• Create comprehensive template system
• Support variable substitution
• Include idempotent patterns by default
• Add helpful comments and documentation

Testing Requirements

Unit Tests

• Test migration status parsing and formatting
• Test migration file creation and templates
• Test commit/uncommit operations
• Test file system operations and error handling

Integration Tests

• Test with real Graphile Migrate projects
• Test migration workflow end-to-end
• Test error scenarios (missing files, permissions, etc.)
• Test template generation and customization

Test Fixtures

Create test projects with:

• Sample committed migrations
• Various current.sql states
• Different project configurations
• Error scenarios for robust testing

Claude Agent Guidance

Typical Usage Patterns

// Check project status
const status = await tools.get_migration_status({
  projectPath: "/path/to/project",
  detailed: true
});

// Create new migration
await tools.create_migration({
  projectPath: "/path/to/project", 
  migrationName: "add_user_profiles_table",
  template: "table"
});

// Commit when ready
await tools.commit_migration({
  projectPath: "/path/to/project",
  message: "Add user profiles table with indexes"
});

How to Verify Success

# Test the tools work correctly
node -e "
const server = require('./dist/index');
// Test get_migration_status
// Test create_migration  
// Test commit_migration
"

Expected Behavior

• Status tool should provide comprehensive project overview
• Create migration should generate proper idempotent SQL
• Commit should handle numbering automatically
• All operations should provide clear feedback

Error Handling Examples

• No Project Found: "No .gmrc file found. Initialize project with 'graphile-migrate init'"
• Invalid Template: "Unknown template 'xyz'. Available templates: table, function, index, empty"
• Commit Conflict: "Cannot commit: current.sql is empty or invalid"

Definition of Done

• All MCP tools implemented with comprehensive input validation
• Migration templates working with variable substitution
• File operations handle all edge cases correctly
• Error messages provide actionable guidance
• Integration tests pass with real projects
• >80% test coverage maintained
• CLAUDE.md updated with usage examples

Estimated Effort

2-3 days

Dependencies

• Issue #1 (closed): Setup MCP Server Foundation
• Issue #2 (closed): Graphile Migrate CLI Integration & Core Utilities

Technical Notes for Claude Agents

Key Files to Create

• src/tools/migration-status.ts - Status checking tool
• src/tools/create-migration.ts - Migration creation tool
• src/tools/commit-migration.ts - Migration commit tool
• src/tools/uncommit-migration.ts - Migration uncommit tool
• src/graphile-migrate/files.ts - File management utilities
• src/templates/ - Migration template directory

File System Operations

• Always use absolute paths for reliability
• Handle permissions and disk space issues gracefully
• Implement atomic operations where possible
• Provide progress feedback for long operations

Template Variables

Support these common variables in templates:

• ${migrationName} - Human-readable migration name
• ${tableName} - Extracted table name from migration name
• ${functionName} - Extracted function name
• ${timestamp} - Current timestamp
• ${author} - Git author or system user