suggest_schema_optimizations
Analyze database schema to identify performance improvements like missing or redundant indexes for Cloudflare D1 optimization.
Instructions
Analyze schema and suggest performance optimizations (missing indexes, redundant indexes, etc.)
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| environment | Yes | Database environment to analyze for optimizations |
Implementation Reference
- Primary handler executing the tool logic: fetches/caches schema, extracts relationships, runs optimization analysis, maps to response.
async execute(request: SuggestOptimizationsRequest): Promise<OptimizationSuggestionsResponse> { const environment = request.environment; // Observable: Cache key based on environment const cacheKey = `schema:${environment}`; // Check cache first (avoid repeated API calls) let schema = await this.cache.get<DatabaseSchema>(cacheKey); if (!schema) { // Fetch schema from repository const databaseId = this.databaseConfig.getDatabaseId(environment); schema = await this.repository.fetchDatabaseSchema(databaseId); // Cache for future requests (10-minute TTL) await this.cache.set(cacheKey, schema, SuggestOptimizationsUseCase.CACHE_TTL_SECONDS); } // Extract relationships from schema const relationships = this.relationshipAnalyzer.extractRelationships([...schema.tables]); // Get optimization suggestions from domain service const optimizations = this.optimizationService.analyzeSchema([...schema.tables], relationships); // Map domain Optimization entities to response DTOs const suggestions = optimizations.map((opt) => this.mapOptimizationToSuggestion(opt)); return { databaseName: schema.name, environment: schema.environment, optimizationCount: suggestions.length, optimizations: suggestions, analyzedAt: new Date(), }; } - Core analysis method generating optimizations by checking for missing PKs, missing FK indexes, and nullable FKs.
analyzeSchema(tables: TableInfo[], relationships: Relationship[]): Optimization[] { const optimizations: Optimization[] = []; optimizations.push(...this.checkMissingPrimaryKeys(tables)); optimizations.push(...this.checkMissingIndexes(tables, relationships)); optimizations.push(...this.checkNullableForeignKeys(tables)); return optimizations; } - src/presentation/mcp/MCPServer.ts:139-154 (registration)Tool registration in MCP listTools handler: defines name, description, and JSON input schema.
{ name: 'suggest_schema_optimizations', description: 'Analyze schema and suggest performance optimizations (missing indexes, redundant indexes, etc.)', inputSchema: { type: 'object', properties: { environment: { type: 'string', enum: ['development', 'staging', 'production'], description: 'Database environment to analyze for optimizations', }, }, required: ['environment'], }, }, - MCP-specific tool handler: validates args, invokes use case, formats MCP text response.
private async handleSuggestOptimizations(args: unknown) { const { environment } = args as { environment: string }; const result = await this.suggestOptimizationsUseCase.execute({ environment: parseEnvironment(environment), }); return { content: [ { type: 'text', text: JSON.stringify(result, null, 2), }, ], }; - src/presentation/mcp/MCPServer.ts:168-169 (registration)Switch case registration dispatching tool calls to handler.
case 'suggest_schema_optimizations': return await this.handleSuggestOptimizations(request.params.arguments);