Semantic D1 MCP

Instructions

Implementation Reference

• src/presentation/mcp/MCPServer.ts:123-138 (registration)

  Registration of the 'validate_database_schema' MCP tool, including name, description, and input schema definition.
  { name: 'validate_database_schema', description: 'Validate database schema integrity and detect potential issues (missing primary keys, orphaned foreign keys, etc.)', inputSchema: { type: 'object', properties: { environment: { type: 'string', enum: ['development', 'staging', 'production'], description: 'Database environment to validate', }, }, required: ['environment'], }, },
• src/presentation/mcp/MCPServer.ts:241-255 (handler)

  Handler method in MCP server that processes tool calls for 'validate_database_schema' by invoking the ValidateSchemaUseCase.
  private async handleValidateSchema(args: unknown) { const { environment } = args as { environment: string }; const result = await this.validateSchemaUseCase.execute({ environment: parseEnvironment(environment), }); return { content: [ { type: 'text', text: JSON.stringify(result, null, 2), }, ], };
• src/application/use-cases/ValidateSchemaUseCase.ts:100-136 (handler)

  Execute method of ValidateSchemaUseCase that fetches the database schema (with caching), performs validation, and constructs the response.
  async execute(request: ValidateSchemaRequest): Promise<SchemaValidationResponse> { 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, ValidateSchemaUseCase.CACHE_TTL_SECONDS); } // Validate schema and collect issues const issues = this.validateSchema(schema); // Count issues by severity const errorCount = issues.filter((i) => i.severity === ValidationSeverity.ERROR).length; const warningCount = issues.filter((i) => i.severity === ValidationSeverity.WARNING).length; const infoCount = issues.filter((i) => i.severity === ValidationSeverity.INFO).length; return { databaseName: schema.name, environment: schema.environment, isValid: errorCount === 0, errorCount, warningCount, infoCount, issues, validatedAt: new Date(), }; }
• src/application/use-cases/ValidateSchemaUseCase.ts:143-232 (handler)

  Core schema validation logic implementing checks for missing primary keys, orphaned/invalid foreign keys, missing indexes, and nullable FK issues.
  private validateSchema(schema: DatabaseSchema): ValidationIssue[] { const issues: ValidationIssue[] = []; // Validate each table for (const table of schema.tables) { // Check for tables without primary key const hasPrimaryKey = table.columns.some((col) => col.isPrimaryKey); if (!hasPrimaryKey) { issues.push({ severity: ValidationSeverity.WARNING, category: 'Missing Primary Key', message: `Table '${table.name}' has no primary key`, table: table.name, details: { recommendation: 'Add a primary key column for better query performance and data integrity', }, }); } // Check for orphaned foreign keys (references non-existent tables) for (const fk of table.foreignKeys) { const referencedTableExists = schema.tables.some((t) => t.name === fk.referencesTable); if (!referencedTableExists) { issues.push({ severity: ValidationSeverity.ERROR, category: 'Orphaned Foreign Key', message: `Foreign key references non-existent table '${fk.referencesTable}'`, table: table.name, column: fk.column, details: { referencedTable: fk.referencesTable, referencedColumn: fk.referencesColumn, }, }); } else { // Check if referenced column exists const referencedTable = schema.tables.find((t) => t.name === fk.referencesTable); const referencedColumnExists = referencedTable?.columns.some( (col) => col.name === fk.referencesColumn, ); if (!referencedColumnExists) { issues.push({ severity: ValidationSeverity.ERROR, category: 'Invalid Foreign Key', message: `Foreign key references non-existent column '${fk.referencesColumn}' in table '${fk.referencesTable}'`, table: table.name, column: fk.column, details: { referencedTable: fk.referencesTable, referencedColumn: fk.referencesColumn, }, }); } } } // Check for tables with no indexes (potential performance issue) if (table.indexes.length === 0 && table.type === 'table') { issues.push({ severity: ValidationSeverity.INFO, category: 'No Indexes', message: `Table '${table.name}' has no indexes`, table: table.name, details: { recommendation: 'Consider adding indexes on frequently queried columns', }, }); } // Check for nullable foreign key columns (potential data integrity issue) for (const fk of table.foreignKeys) { const fkColumn = table.columns.find((col) => col.name === fk.column); if (fkColumn?.isNullable && fk.onDelete !== 'SET NULL') { issues.push({ severity: ValidationSeverity.WARNING, category: 'Nullable Foreign Key', message: `Nullable foreign key column '${fk.column}' should have ON DELETE SET NULL`, table: table.name, column: fk.column, details: { currentOnDelete: fk.onDelete, recommendation: 'SET NULL', }, }); } } } return issues; }
• src/presentation/mcp/MCPServer.ts:127-137 (schema)

  Input schema definition for the 'validate_database_schema' tool.
  inputSchema: { type: 'object', properties: { environment: { type: 'string', enum: ['development', 'staging', 'production'], description: 'Database environment to validate', }, }, required: ['environment'], },