Semantic D1 MCP

Instructions

Implementation Reference

• 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 foreign keys without proper ON DELETE.
  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/application/use-cases/ValidateSchemaUseCase.ts:100-136 (handler)

  Use case execute method: caches/fetches database schema by environment, runs validation, counts issues by severity, and returns formatted 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/presentation/mcp/MCPServer.ts:241-255 (handler)

  MCP server handler for validate_database_schema tool call: extracts environment argument, invokes ValidateSchemaUseCase, and returns JSON response.
  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/presentation/mcp/MCPServer.ts:124-137 (registration)

  Registration of validate_database_schema tool in MCP listTools response, specifying name, description, and input schema.
  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:127-136 (schema)

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