PerchIQX

Appearance

MCP Tools Overview ​

PerchIQX provides 5 comprehensive MCP tools for Cloudflare D1 database introspection. Each tool is designed with semantic intent principles, focusing on what you want to accomplish rather than technical implementation details.

Available Tools ​

ToolPurposeKey Features
analyze_database_schemaComplete schema introspectionTables, columns, indexes, foreign keys, sample data
get_table_relationshipsRelationship analysisForeign keys, cardinality, referential integrity
validate_database_schemaSchema validationAnti-patterns, missing keys, naming conventions
suggest_database_optimizationsOptimization recommendationsMissing indexes, primary keys, performance tips
compare_schemasSchema drift detectionCross-environment comparison, ICE-scored differences, migration planning

analyze_database_schema ​

Analyze complete database schema structure with metadata and optional sample data.

Parameters ​

typescript
{
  environment: "development" | "staging" | "production",  // required
  includeSamples?: boolean,                                // default: true
  maxSampleRows?: number                                   // default: 5
}

Returns ​

  • Complete schema analysis
  • Table structures with columns, types, constraints
  • Indexes and foreign keys
  • Sample data from each table (if enabled)
  • Schema metadata and statistics

Example Usage ​

json
{
  "name": "analyze_database_schema",
  "arguments": {
    "environment": "development",
    "includeSamples": true,
    "maxSampleRows": 5
  }
}

Use Cases ​

  • Understanding existing database structure
  • Generating schema documentation
  • Planning migrations or refactoring
  • Onboarding new team members

Learn more →

get_table_relationships ​

Extract and analyze foreign key relationships between tables.

Parameters ​

typescript
{
  environment: "development" | "staging" | "production",  // required
  tableName?: string                                       // optional filter
}

Returns ​

  • Foreign key relationships with cardinality (one-to-many, many-to-one)
  • Referential integrity rules (CASCADE, SET NULL, etc.)
  • Relationship metadata and statistics
  • Dependency graph information

Example Usage ​

json
{
  "name": "get_table_relationships",
  "arguments": {
    "environment": "production",
    "tableName": "users"
  }
}

Use Cases ​

  • Understanding data model relationships
  • Planning cascading deletes or updates
  • Identifying circular dependencies
  • Visualizing database architecture

Learn more →

validate_database_schema ​

Validate database schema for common issues and anti-patterns.

Parameters ​

typescript
{
  environment: "development" | "staging" | "production"  // required
}

Returns ​

  • Schema validation results
  • Missing primary keys
  • Foreign keys without indexes
  • Naming convention violations
  • Tables without relationships
  • Severity levels (error, warning, info)

Example Usage ​

json
{
  "name": "validate_database_schema",
  "arguments": {
    "environment": "production"
  }
}

Use Cases ​

  • Pre-deployment schema checks
  • Database health monitoring
  • Identifying schema debt
  • Enforcing best practices

Learn more →

suggest_database_optimizations ​

Generate schema optimization recommendations based on structure analysis.

Parameters ​

typescript
{
  environment: "development" | "staging" | "production"  // required
}

Returns ​

  • Prioritized optimization suggestions (high/medium/low)
  • Missing index recommendations
  • Primary key suggestions
  • Schema improvement opportunities
  • Performance optimization tips
  • Impact analysis for each suggestion

Example Usage ​

json
{
  "name": "suggest_database_optimizations",
  "arguments": {
    "environment": "production"
  }
}

Use Cases ​

  • Performance tuning
  • Query optimization planning
  • Database maintenance
  • Cost reduction (fewer queries)

Learn more →

compare_schemas ​

Compare database schemas between environments to detect drift and plan migrations with ICE-scored differences.

Parameters ​

typescript
{
  sourceDatabaseId: string,                              // required
  sourceEnvironment: "development" | "staging" | "production",  // required
  targetDatabaseId: string,                              // required
  targetEnvironment: "development" | "staging" | "production"   // required
}

Returns ​

  • ICE-scored differences with derived priorities (high/medium/low)
  • Missing tables between source and target
  • Missing columns in existing tables
  • Type mismatches between columns
  • Missing indexes and foreign keys
  • Migration plan with ordered SQL statements
  • Execution metrics (table counts, execution time)

Example Usage ​

json
{
  "name": "compare_schemas",
  "arguments": {
    "sourceDatabaseId": "dev-database-id",
    "sourceEnvironment": "development",
    "targetDatabaseId": "prod-database-id",
    "targetEnvironment": "production"
  }
}

ICE Scoring ​

Each difference is scored using the ICE (Insight-Context-Execution) methodology:

  • Insight (I): Semantic depth of the difference (0-10)
  • Context (C): Environmental criticality and risk (0-10)
  • Execution (E): Action clarity and implementation ease (0-10)
  • Combined Score: (I × C × E) / 100 → Priority derivation

Priority Thresholds:

  • High: Combined score ≥ 6.0 (critical changes)
  • Medium: Combined score 3.0-5.9 (important changes)
  • Low: Combined score < 3.0 (minor changes)

Use Cases ​

  • Drift Detection: Identify schema divergence between environments
  • Migration Planning: Generate ordered migration scripts
  • Pre-deployment Validation: Ensure production readiness
  • Environment Synchronization: Keep dev/staging/prod aligned
  • Schema Version Control: Track schema evolution over time

Example Response ​

json
{
  "result": {
    "summary": {
      "totalDifferences": 5,
      "missingTables": 1,
      "missingColumns": 2,
      "typeMismatches": 1,
      "missingIndexes": 1,
      "isIdentical": false
    },
    "differences": [
      {
        "type": "missing_table",
        "severity": "critical",
        "description": "Table 'audit_logs' exists in development but not in production",
        "iceScore": {
          "insight": 9,
          "context": 10,
          "execution": 8,
          "combined": 7.2,
          "priority": "high"
        },
        "migrationSql": "CREATE TABLE audit_logs (...)"
      }
    ]
  },
  "sourceTableCount": 15,
  "targetTableCount": 14,
  "executionTimeMs": 342
}

Learn more →

Common Patterns ​

Schema Drift Detection ​

Use compare_schemas to detect differences between environments:

typescript
// Compare development against production
compare_schemas({
  sourceDatabaseId: "dev-db-id",
  sourceEnvironment: "development",
  targetDatabaseId: "prod-db-id",
  targetEnvironment: "production"
})

// Review ICE-scored differences
// Generate migration plan
// Apply changes with confidence

Multi-Environment Analysis ​

Compare schemas across environments:

typescript
// Check development schema
analyze_database_schema({ environment: "development" })

// Compare with production
analyze_database_schema({ environment: "production" })

// Or use direct comparison
compare_schemas({
  sourceDatabaseId: "dev-db-id",
  sourceEnvironment: "development",
  targetDatabaseId: "prod-db-id",
  targetEnvironment: "production"
})

Validation + Optimization Workflow ​

  1. Validate schema for issues
  2. Analyze structure in detail
  3. Optimize based on recommendations
typescript
// Step 1: Find issues
validate_database_schema({ environment: "production" })

// Step 2: Get optimization suggestions
suggest_database_optimizations({ environment: "production" })

// Step 3: Analyze specific tables
get_table_relationships({
  environment: "production",
  tableName: "orders"  // Focus on problem area
})

Documentation Generation ​

Use analyze_database_schema with samples to auto-generate documentation:

typescript
analyze_database_schema({
  environment: "production",
  includeSamples: true,
  maxSampleRows: 3  // Show example data
})

Error Handling ​

All tools provide clear error messages:

  • Invalid environment: Environment not configured in .env
  • Database not found: Check database ID and credentials
  • Permission denied: Verify API token has D1 read access
  • Network errors: Cloudflare API connectivity issues

Response Format ​

All tools return structured JSON responses:

json
{
  "success": true,
  "data": {
    // Tool-specific data
  },
  "metadata": {
    "environment": "production",
    "timestamp": "2025-10-15T12:00:00Z",
    "duration_ms": 245
  }
}

Performance Considerations ​

  • Schema analysis is cached for 5 minutes
  • Sample data limits are configurable (default: 5 rows)
  • Relationship analysis uses efficient SQLite introspection queries
  • Large schemas (100+ tables) may take 1-2 seconds

Next Steps ​