Semantic D1 MCP

Instructions

Implementation Reference

• src/application/use-cases/AnalyzeSchemaUseCase.ts:108-227 (handler)

  Core handler implementation of the analyze_database_schema tool: fetches schema from D1 database, applies caching, formats analysis with tables/columns/indexes/FKs, optionally includes sample data.
  export class AnalyzeSchemaUseCase { private static readonly CACHE_TTL_SECONDS = 600; // 10 minutes constructor( private readonly repository: ICloudflareD1Repository, private readonly schemaAnalyzer: SchemaAnalyzer, private readonly databaseConfig: DatabaseConfig, private readonly cache: ICacheProvider, ) { Object.freeze(this); } /** * Execute schema analysis * * Semantic: Environment drives database selection and cache strategy */ 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); } /** * Format database schema into analysis response * * Semantic: Transforms domain entities into presentation DTOs */ 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, }; } /** * Fetch sample data from table * * Observable: Queries actual table rows for semantic representation */ private async fetchSampleData( databaseId: string, tableName: string, maxRows: number, ): Promise<unknown[]> { try { const sql = `SELECT * FROM "${tableName}" LIMIT ${maxRows}`; const result = await this.repository.executeSQLQuery(databaseId, sql); return result.results || []; } catch (error) { // If sample fetch fails, return empty array (don't fail entire analysis) return []; } } }
• src/presentation/mcp/MCPServer.ts:191-212 (handler)

  MCP server tool handler for analyze_database_schema: delegates to 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:78-102 (registration)

  Registration of the analyze_database_schema tool including name, description, and input schema in the server's listTools handler.
  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:38-101 (schema)

  TypeScript interfaces defining the input request and output response schemas for the schema analysis, including detailed table/column/index/FK structures.
  export interface AnalyzeSchemaRequest { environment: Environment; includeSamples?: boolean; maxSampleRows?: number; } /** * Response DTO for schema analysis * * Semantic: Complete schema analysis with metadata and optional samples */ export interface SchemaAnalysisResponse { databaseName: string; environment: Environment; tableCount: number; tables: TableAnalysis[]; fetchedAt: Date; } /** * Table analysis details */ export interface TableAnalysis { name: string; type: 'table' | 'view'; columnCount: number; columns: ColumnAnalysis[]; indexes: IndexAnalysis[]; foreignKeys: ForeignKeyAnalysis[]; samples?: unknown[]; } /** * Column analysis details */ export interface ColumnAnalysis { name: string; type: string; nullable: boolean; isPrimaryKey: boolean; defaultValue: string | null; } /** * Index analysis details */ export interface IndexAnalysis { name: string; columns: string[]; isUnique: boolean; isPrimaryKey: boolean; } /** * Foreign key analysis details */ export interface ForeignKeyAnalysis { column: string; referencedTable: string; referencedColumn: string; onDelete: 'CASCADE' | 'SET NULL' | 'SET DEFAULT' | 'RESTRICT' | 'NO ACTION' | null; onUpdate: 'CASCADE' | 'SET NULL' | 'SET DEFAULT' | 'RESTRICT' | 'NO ACTION' | null; }