analyze_database_schema

Instructions

Implementation Reference

• src/application/use-cases/AnalyzeSchemaUseCase.ts:125-148 (handler)

  Core handler logic for analyzing database schema: manages caching based on environment, fetches schema from repository if not cached, and prepares the response.

  async execute(request: AnalyzeSchemaRequest): Promise<SchemaAnalysisResponse> {
  	const environment = request.environment;
  	const includeSamples = request.includeSamples ?? true;
  	const maxSampleRows = request.maxSampleRows ?? 5;
  
  	// Observable: Cache key based on environment
  	const cacheKey = `schema:${environment}`;
  
  	// Check cache first (avoid repeated API calls)
  	const cachedSchema = await this.cache.get<DatabaseSchema>(cacheKey);
  	if (cachedSchema) {
  		return this.formatResponse(cachedSchema, includeSamples, maxSampleRows);
  	}
  
  	// Fetch schema from repository
  	const databaseId = this.databaseConfig.getDatabaseId(environment);
  	const schema = await this.repository.fetchDatabaseSchema(databaseId);
  
  	// Cache for future requests (10-minute TTL)
  	await this.cache.set(cacheKey, schema, AnalyzeSchemaUseCase.CACHE_TTL_SECONDS);
  
  	// Format and return response
  	return this.formatResponse(schema, includeSamples, maxSampleRows);
  }

• src/presentation/mcp/MCPServer.ts:191-212 (handler)

  MCP protocol handler for 'analyze_database_schema' tool call: parses arguments, invokes AnalyzeSchemaUseCase, and formats MCP response.

  private async handleAnalyzeSchema(args: unknown) {
  	const { environment, includeSamples, maxSampleRows } = args as {
  		environment: string;
  		includeSamples?: boolean;
  		maxSampleRows?: number;
  	};
  
  	const result = await this.analyzeSchemaUseCase.execute({
  		environment: parseEnvironment(environment),
  		includeSamples,
  		maxSampleRows,
  	});
  
  	return {
  		content: [
  			{
  				type: 'text',
  				text: JSON.stringify(result, null, 2),
  			},
  		],
  	};
  }

• src/presentation/mcp/MCPServer.ts:81-101 (schema)

  Input schema definition for the 'analyze_database_schema' tool, specifying parameters like environment, includeSamples, and maxSampleRows.

  inputSchema: {
  	type: 'object',
  	properties: {
  		environment: {
  			type: 'string',
  			enum: ['development', 'staging', 'production'],
  			description: 'Database environment to analyze',
  		},
  		includeSamples: {
  			type: 'boolean',
  			default: true,
  			description: 'Include sample data from tables (max 5 rows per table)',
  		},
  		maxSampleRows: {
  			type: 'number',
  			default: 5,
  			description: 'Maximum number of sample rows per table',
  		},
  	},
  	required: ['environment'],
  },

• src/presentation/mcp/MCPServer.ts:77-102 (registration)

  Registration of the 'analyze_database_schema' tool in the MCP ListTools response.

  {
  	name: 'analyze_database_schema',
  	description:
  		'Analyze D1 database schema structure, tables, columns, indexes, and relationships with optional sample data',
  	inputSchema: {
  		type: 'object',
  		properties: {
  			environment: {
  				type: 'string',
  				enum: ['development', 'staging', 'production'],
  				description: 'Database environment to analyze',
  			},
  			includeSamples: {
  				type: 'boolean',
  				default: true,
  				description: 'Include sample data from tables (max 5 rows per table)',
  			},
  			maxSampleRows: {
  				type: 'number',
  				default: 5,
  				description: 'Maximum number of sample rows per table',
  			},
  		},
  		required: ['environment'],
  	},
  },

• src/application/use-cases/AnalyzeSchemaUseCase.ts:155-206 (helper)

  Helper function to format the raw DatabaseSchema into the detailed analysis response, including optional sample data fetching.

  private async formatResponse(
  	schema: DatabaseSchema,
  	includeSamples: boolean,
  	maxSampleRows: number,
  ): Promise<SchemaAnalysisResponse> {
  	const tables: TableAnalysis[] = [];
  
  	for (const table of schema.tables) {
  		const tableAnalysis: TableAnalysis = {
  			name: table.name,
  			type: table.type,
  			columnCount: table.columns.length,
  			columns: table.columns.map((col) => ({
  				name: col.name,
  				type: col.type,
  				nullable: col.isNullable,
  				isPrimaryKey: col.isPrimaryKey,
  				defaultValue: col.defaultValue,
  			})),
  			indexes: table.indexes.map((idx) => ({
  				name: idx.name,
  				columns: [...idx.columns],
  				isUnique: idx.isUnique,
  				isPrimaryKey: idx.isPrimaryKey,
  			})),
  			foreignKeys: table.foreignKeys.map((fk) => ({
  				column: fk.column,
  				referencedTable: fk.referencesTable,
  				referencedColumn: fk.referencesColumn,
  				onDelete: fk.onDelete,
  				onUpdate: fk.onUpdate,
  			})),
  		};
  
  		// Fetch sample data if requested
  		if (includeSamples) {
  			const databaseId = this.databaseConfig.getDatabaseId(schema.environment);
  			const samples = await this.fetchSampleData(databaseId, table.name, maxSampleRows);
  			tableAnalysis.samples = samples;
  		}
  
  		tables.push(tableAnalysis);
  	}
  
  	return {
  		databaseName: schema.name,
  		environment: schema.environment,
  		tableCount: schema.tables.length,
  		tables,
  		fetchedAt: schema.fetchedAt,
  	};
  }