validate_database_schema

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'],
  },