OrgFlow MCP

import 'dotenv/config' import { Server } from '@modelcontextprotocol/sdk/server/index.js'; import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js'; import { CallToolRequestSchema, ListToolsRequestSchema, Tool, } from '@modelcontextprotocol/sdk/types.js'; import fs from 'fs/promises'; import path from 'path'; import { fileURLToPath } from 'url'; import { Client } from "@modelcontextprotocol/sdk/client/index.js"; import { StreamableHTTPClientTransport } from "@modelcontextprotocol/sdk/client/streamableHttp.js"; const __filename = fileURLToPath(import.meta.url); const __dirname = path.dirname(__filename); interface DatasetItem { id?: string; [key: string]: any; } interface QueryParams { filter?: Record<string, any>; search?: string; limit?: number; offset?: number; sortBy?: string; sortOrder?: 'asc' | 'desc'; } interface GetDataParams { dataId: string; params?: QueryParams; } interface CrossReferenceParams { queryType: string; inputId: string; } interface ModifyDataBatchParams { operations: { dataId: string; op: 'insert' | 'update'; record: Record<string, any>; }[]; } interface DeleteDataParams { dataId: string; filter: Record<string, any>; } interface CreateDatasetParams { dataId: string; data: any[]; description?: string; } interface DeleteDatasetParams { dataId: string; confirm?: boolean; } interface TriggerZapierToolParams { toolName: string; arguments: Record<string, any>; } interface TriggerGoogleSheetsToolParams { toolName: string; arguments: Record<string, any>; } interface BulkOperationParams { operations: { type: 'create_dataset' | 'modify_data' | 'delete_data' | 'delete_dataset' | 'trigger_zapier_tool' | 'trigger_google_sheets_tool'; params: any; }[]; } interface ServerStats { totalDatasets: number; totalRecords: number; zapierConnected: boolean; googleSheetsConnected: boolean; uptime: number; lastActivity: string; } class OrganizationalDataServer { private server: Server; private datasets: Map<string, DatasetItem[]> = new Map(); private zapierClient: Client | null = null; private googleSheetsClient: Client | null = null; private startTime: number = Date.now(); private lastActivity: number = Date.now(); private toolCache: Map<string, Tool[]> = new Map(); private cacheExpiry: Map<string, number> = new Map(); private readonly CACHE_DURATION = 5 * 60 * 1000; // 5 minutes private datasetPaths: Record<string, string> = { employees: '../../data/employees.json', companies: '../../data/companies.json', departments: '../../data/departments.json', domains: '../../data/domains.json', projects: '../../data/projects.json', customers: '../../data/customers.json', assets: '../../data/assets.json', policies: '../../data/policies.json', roles: '../../data/roles.json', accesslogs: '../../data/accesslogs.json', training: '../../data/training.json', performance: '../../data/performance.json', tickets: '../../data/tickets.json', vendors: '../../data/vendors.json', locations: '../../data/locations.json', }; constructor() { this.server = new Server( { name: 'organizational-data-server', version: '1.2.0', }, { capabilities: { tools: {}, }, } ); this.setupRequestHandlers(); } async initialize(): Promise<void> { await this.loadDatasets(); await this.initializeZapierClient(); await this.initializeGoogleSheetsClient(); } private async initializeZapierClient(): Promise<void> { try { if (!process.env.ZAPIER_MCP_URL) { console.error(' ZAPIER_MCP_URL not configured, Zapier integration disabled'); return; } this.zapierClient = new Client( { name: "organizational-zapier-client", version: "1.0.0", }, { capabilities: {}, } ); const transport = new StreamableHTTPClientTransport(new URL(process.env.ZAPIER_MCP_URL)); await this.zapierClient.connect(transport); console.error('✓ Connected to Zapier MCP server'); // Cache Zapier tools await this.cacheExternalTools('zapier'); } catch (error) { console.error('✗ Failed to connect to Zapier MCP server:', error); this.zapierClient = null; } } private async initializeGoogleSheetsClient(): Promise<void> { try { if (!process.env.GOOGLE_SHEETS_MCP_URL) { console.error(' GOOGLE_SHEETS_MCP_URL not configured, Google Sheets integration disabled'); return; } this.googleSheetsClient = new Client( { name: "organizational-sheets-client", version: "1.0.0", }, { capabilities: {}, } ); const transport = new StreamableHTTPClientTransport(new URL(process.env.GOOGLE_SHEETS_MCP_URL)); await this.googleSheetsClient.connect(transport); console.error('✓ Connected to Google Sheets MCP server'); // Cache Google Sheets tools await this.cacheExternalTools('google_sheets'); } catch (error) { console.error('✗ Failed to connect to Google Sheets MCP server:', error); this.googleSheetsClient = null; } } private async cacheExternalTools(service: 'zapier' | 'google_sheets'): Promise<void> { try { const client = service === 'zapier' ? this.zapierClient : this.googleSheetsClient; if (!client) return; const tools = await client.listTools(); this.toolCache.set(service, tools.tools); this.cacheExpiry.set(service, Date.now() + this.CACHE_DURATION); console.error(`✓ Cached ${tools.tools.length} ${service} tools`); } catch (error) { console.error(`✗ Failed to cache ${service} tools:`, error); } } private async getExternalTools(service: 'zapier' | 'google_sheets'): Promise<Tool[]> { const cacheKey = service; const cached = this.toolCache.get(cacheKey); const expiry = this.cacheExpiry.get(cacheKey); // Return cached tools if still valid if (cached && expiry && Date.now() < expiry) { return cached; } // Refresh cache await this.cacheExternalTools(service); return this.toolCache.get(cacheKey) || []; } private async loadDatasets(): Promise<void> { const dataDir = path.resolve(__dirname, '../../data'); try { await fs.access(dataDir); } catch { await fs.mkdir(dataDir, { recursive: true }); console.error('✓ Created data directory'); } // Load predefined datasets for (const [dataId, relativePath] of Object.entries(this.datasetPaths)) { try { const fullPath = path.resolve(__dirname, relativePath); const data = await fs.readFile(fullPath, 'utf-8'); const jsonData = JSON.parse(data); const dataset = Array.isArray(jsonData) ? jsonData : [jsonData]; this.datasets.set(dataId, dataset); console.error(`✓ Loaded ${dataset.length} items for ${dataId}`); } catch (error) { console.error(`✗ Failed to load ${dataId}: ${error}`); this.datasets.set(dataId, []); try { const fullPath = path.resolve(__dirname, relativePath); await fs.writeFile(fullPath, '[]', 'utf-8'); console.error(`✓ Created empty ${dataId}.json file`); } catch (writeError) { console.error(`✗ Failed to create ${dataId}.json: ${writeError}`); } } } // Load custom datasets try { const files = await fs.readdir(dataDir); const jsonFiles = files.filter(file => file.endsWith('.json')); for (const file of jsonFiles) { const dataId = file.replace('.json', ''); if (this.datasets.has(dataId)) continue; try { const fullPath = path.join(dataDir, file); const data = await fs.readFile(fullPath, 'utf-8'); const jsonData = JSON.parse(data); const dataset = Array.isArray(jsonData) ? jsonData : [jsonData]; this.datasets.set(dataId, dataset); this.datasetPaths[dataId] = `../../data/${file}`; console.error(`✓ Loaded custom dataset ${dataId} with ${dataset.length} items`); } catch (error) { console.error(`✗ Failed to load custom dataset ${dataId}: ${error}`); } } } catch (error) { console.error('✗ Error scanning data directory:', error); } } private setupRequestHandlers(): void { this.server.setRequestHandler(ListToolsRequestSchema, async () => { const tools: Tool[] = [ // Core dataset operations { name: 'get_data', description: 'Query organizational data with advanced filtering, search, and pagination', inputSchema: { type: 'object', properties: { dataId: { type: 'string', description: 'Dataset to query (employees, companies, etc.) or custom dataset' }, params: { type: 'object', properties: { filter: { type: 'object', description: 'Advanced filtering with operators like $regex, $gt, $lt, $in, etc.' }, search: { type: 'string', description: 'Text search across all fields' }, limit: { type: 'number', description: 'Maximum number of results (default: 100)' }, offset: { type: 'number', description: 'Number of results to skip (default: 0)' }, sortBy: { type: 'string', description: 'Field to sort by' }, sortOrder: { type: 'string', enum: ['asc', 'desc'], description: 'Sort order (default: asc)' }, }, }, }, required: ['dataId'], }, }, { name: 'resolve_cross_reference', description: 'Resolve relationships between datasets (employee-company, customer-employee, etc.)', inputSchema: { type: 'object', properties: { queryType: { type: 'string', enum: ['customer_to_employee_info', 'employee_to_company_info', 'asset_to_employee_info'], description: 'Type of cross-reference to resolve' }, inputId: { type: 'string', description: 'ID to resolve from' }, }, required: ['queryType', 'inputId'], }, }, { name: 'modify_data', description: 'Insert or update records in datasets with batch operations and deduplication', inputSchema: { type: 'object', properties: { operations: { type: 'array', items: { type: 'object', properties: { dataId: { type: 'string' }, op: { type: 'string', enum: ['insert', 'update'] }, record: { type: 'object' } }, required: ['dataId', 'op', 'record'] } } }, required: ['operations'] }, }, { name: 'delete_data', description: 'Delete records from datasets using advanced filters', inputSchema: { type: 'object', properties: { dataId: { type: 'string' }, filter: { type: 'object', description: 'Filter criteria for records to delete' }, }, required: ['dataId', 'filter'] }, }, { name: 'create_dataset', description: 'Create new JSON dataset with validation and automatic file management', inputSchema: { type: 'object', properties: { dataId: { type: 'string', description: 'Dataset name (alphanumeric, underscore, hyphen only)' }, data: { type: 'array', items: { type: 'object' }, description: 'Array of records to store' }, description: { type: 'string', description: 'Optional dataset description' } }, required: ['dataId', 'data'] }, }, { name: 'delete_dataset', description: 'Delete entire dataset with automatic backup creation', inputSchema: { type: 'object', properties: { dataId: { type: 'string', description: 'Dataset to delete (custom datasets only)' }, confirm: { type: 'boolean', description: 'Must be true to confirm deletion' } }, required: ['dataId', 'confirm'] }, }, { name: 'list_datasets', description: 'List all datasets with metadata and sample records', inputSchema: { type: 'object', properties: {}, }, }, { name: 'backup_dataset', description: 'Create timestamped backup of dataset', inputSchema: { type: 'object', properties: { dataId: { type: 'string' }, }, required: ['dataId'] }, }, { name: 'get_server_stats', description: 'Get server statistics and connection status', inputSchema: { type: 'object', properties: {}, }, }, { name: 'bulk_operations', description: 'Execute multiple operations atomically with transaction-like behavior', inputSchema: { type: 'object', properties: { operations: { type: 'array', items: { type: 'object', properties: { type: { type: 'string', enum: ['create_dataset', 'modify_data', 'delete_data', 'delete_dataset', 'trigger_zapier_tool', 'trigger_google_sheets_tool'] }, params: { type: 'object', description: 'Operation-specific parameters' } }, required: ['type', 'params'] } } }, required: ['operations'] }, }, // External integrations { name: 'trigger_zapier_tool', description: 'Execute Zapier tools for Google Drive, Slack, and other integrations', inputSchema: { type: 'object', properties: { toolName: { type: 'string', description: 'Zapier tool name (e.g., google_drive_retrieve_files_from_google_drive)' }, arguments: { type: 'object', description: 'Tool-specific arguments', properties: { instructions: { type: 'string', description: 'Clear instructions for the operation' }, maxResults: { type: 'number', description: 'Maximum results to return' }, orderBy: { type: 'string', description: 'Result ordering (e.g., modifiedTime desc)' }, fileId: { type: 'string', description: 'Google Drive file ID' }, folderId: { type: 'string', description: 'Google Drive folder ID' }, query: { type: 'string', description: 'Search query' } } } }, required: ['toolName'] }, }, { name: 'list_zapier_tools', description: 'List all available Zapier tools with caching', inputSchema: { type: 'object', properties: { refresh: { type: 'boolean', description: 'Force refresh of tool cache' } }, }, }, { name: 'trigger_google_sheets_tool', description: 'Execute Google Sheets operations with enhanced error handling', inputSchema: { type: 'object', properties: { toolName: { type: 'string', description: 'Google Sheets tool name' }, arguments: { type: 'object', description: 'Tool-specific arguments for sheets operations' } }, required: ['toolName'] }, }, { name: 'list_google_sheets_tools', description: 'List all available Google Sheets tools with caching', inputSchema: { type: 'object', properties: { refresh: { type: 'boolean', description: 'Force refresh of tool cache' } }, }, }, // Data analysis and reporting { name: 'analyze_dataset', description: 'Perform statistical analysis on dataset fields', inputSchema: { type: 'object', properties: { dataId: { type: 'string' }, fields: { type: 'array', items: { type: 'string' }, description: 'Fields to analyze' }, analysisType: { type: 'string', enum: ['summary', 'distribution', 'correlations', 'trends'], description: 'Type of analysis to perform' } }, required: ['dataId', 'fields', 'analysisType'] }, }, { name: 'export_dataset', description: 'Export dataset in various formats (CSV, Excel, JSON)', inputSchema: { type: 'object', properties: { dataId: { type: 'string' }, format: { type: 'string', enum: ['csv', 'excel', 'json'], description: 'Export format' }, filter: { type: 'object', description: 'Optional filter for export' } }, required: ['dataId', 'format'] }, } ]; return { tools }; }); this.server.setRequestHandler(CallToolRequestSchema, async (request) => { const { name, arguments: args } = request.params; this.lastActivity = Date.now(); try { switch (name) { case 'get_data': return await this.handleGetData(args as unknown as GetDataParams); case 'resolve_cross_reference': return await this.handleCrossReference(args as unknown as CrossReferenceParams); case 'modify_data': return await this.handleModifyData(args as unknown as ModifyDataBatchParams); case 'delete_data': return await this.handleDeleteData(args as unknown as DeleteDataParams); case 'create_dataset': return await this.handleCreateDataset(args as unknown as CreateDatasetParams); case 'delete_dataset': return await this.handleDeleteDataset(args as unknown as DeleteDatasetParams); case 'list_datasets': return await this.handleListDatasets(); case 'backup_dataset': return await this.handleBackupDataset(args as unknown as { dataId: string }); case 'get_server_stats': return await this.handleGetServerStats(); case 'bulk_operations': return await this.handleBulkOperations(args as unknown as BulkOperationParams); case 'trigger_zapier_tool': return await this.handleTriggerZapierTool(args as unknown as TriggerZapierToolParams); case 'list_zapier_tools': return await this.handleListZapierTools(args as unknown as { refresh?: boolean }); case 'trigger_google_sheets_tool': return await this.handleTriggerGoogleSheetsTools(args as unknown as TriggerGoogleSheetsToolParams); case 'list_google_sheets_tools': return await this.handleListGoogleSheetsTools(args as unknown as { refresh?: boolean }); case 'analyze_dataset': return await this.handleAnalyzeDataset(args as unknown as any); case 'export_dataset': return await this.handleExportDataset(args as unknown as any); default: throw new Error(`Unknown tool: ${name}`); } } catch (error) { console.error(`Error in ${name}:`, error); return { content: [ { type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}`, }, ], }; } }); } // Enhanced handlers with improved error handling and features public async handleGetServerStats() { const stats: ServerStats = { totalDatasets: this.datasets.size, totalRecords: Array.from(this.datasets.values()).reduce((sum, dataset) => sum + dataset.length, 0), zapierConnected: this.zapierClient !== null, googleSheetsConnected: this.googleSheetsClient !== null, uptime: Date.now() - this.startTime, lastActivity: new Date(this.lastActivity).toISOString() }; return { content: [{ type: 'text', text: JSON.stringify(stats, null, 2) }], }; } public async handleTriggerZapierTool(params: TriggerZapierToolParams) { if (!this.zapierClient) { throw new Error('Zapier client not initialized. Please check ZAPIER_MCP_URL environment variable.'); } try { const { toolName, arguments: toolArgs = {} } = params; if (!toolName) { throw new Error('toolName is required'); } // Enhanced argument processing for common operations const safeArgs = { ...toolArgs }; // Auto-add instructions for Google Drive operations if (toolName.includes('google_drive') && !safeArgs.instructions) { safeArgs.instructions = this.generateGoogleDriveInstructions(toolName, safeArgs); } // Add default limits for list operations if (toolName.includes('list') || toolName.includes('retrieve')) { safeArgs.maxResults = safeArgs.maxResults || 50; } console.error(`🔧 Calling Zapier tool: ${toolName}`); const result = await this.zapierClient.callTool({ name: toolName, arguments: safeArgs }); return { content: [{ type: "text", text: `✅ Zapier Tool "${toolName}" executed successfully:\n${JSON.stringify(result.content, null, 2)}` }] }; } catch (err) { const errorMessage = err instanceof Error ? err.message : 'Unknown error'; console.error(`❌ Error calling Zapier tool ${params.toolName}:`, err); return { content: [{ type: "text", text: `❌ Error calling Zapier tool "${params.toolName}": ${errorMessage}` }] }; } } public async handleListZapierTools(params: { refresh?: boolean } = {}) { if (!this.zapierClient) { return { content: [{ type: "text", text: "❌ Zapier client not initialized. Please check ZAPIER_MCP_URL environment variable." }] }; } try { if (params.refresh) { await this.cacheExternalTools('zapier'); } const tools = await this.getExternalTools('zapier'); const toolsList = tools.map(tool => ({ name: tool.name, description: tool.description, inputSchema: tool.inputSchema })); return { content: [{ type: "text", text: `📋 Available Zapier Tools (${toolsList.length}):\n${JSON.stringify(toolsList, null, 2)}` }] }; } catch (err) { const errorMessage = err instanceof Error ? err.message : 'Unknown error'; console.error('❌ Error listing Zapier tools:', err); return { content: [{ type: "text", text: `❌ Error listing Zapier tools: ${errorMessage}` }] }; } } public async handleTriggerGoogleSheetsTools(params: TriggerGoogleSheetsToolParams) { if (!this.googleSheetsClient) { throw new Error('Google Sheets client not initialized. Please check GOOGLE_SHEETS_MCP_URL environment variable.'); } try { const { toolName, arguments: toolArgs = {} } = params; if (!toolName) { throw new Error('toolName is required'); } console.error(`📊 Calling Google Sheets tool: ${toolName}`); const result = await this.googleSheetsClient.callTool({ name: toolName, arguments: toolArgs }); return { content: [{ type: "text", text: `✅ Google Sheets Tool "${toolName}" executed successfully:\n${JSON.stringify(result.content, null, 2)}` }] }; } catch (err) { const errorMessage = err instanceof Error ? err.message : 'Unknown error'; console.error(`❌ Error calling Google Sheets tool ${params.toolName}:`, err); return { content: [{ type: "text", text: `❌ Error calling Google Sheets tool "${params.toolName}": ${errorMessage}` }] }; } } public async handleListGoogleSheetsTools(params: { refresh?: boolean } = {}) { if (!this.googleSheetsClient) { return { content: [{ type: "text", text: "❌ Google Sheets client not initialized. Please check GOOGLE_SHEETS_MCP_URL environment variable." }] }; } try { if (params.refresh) { await this.cacheExternalTools('google_sheets'); } const tools = await this.getExternalTools('google_sheets'); const toolsList = tools.map(tool => ({ name: tool.name, description: tool.description, inputSchema: tool.inputSchema })); return { content: [{ type: "text", text: `📊 Available Google Sheets Tools (${toolsList.length}):\n${JSON.stringify(toolsList, null, 2)}` }] }; } catch (err) { const errorMessage = err instanceof Error ? err.message : 'Unknown error'; console.error('❌ Error listing Google Sheets tools:', err); return { content: [{ type: "text", text: `❌ Error listing Google Sheets tools: ${errorMessage}` }] }; } } public async handleAnalyzeDataset(params: any) { const { dataId, fields, analysisType } = params; const dataset = this.datasets.get(dataId); if (!dataset) { throw new Error(`Dataset '${dataId}' not found`); } const analysis = this.performAnalysis(dataset, fields, analysisType); return { content: [{ type: 'text', text: JSON.stringify(analysis, null, 2) }], }; } // Complete the handleExportDataset method public async handleExportDataset(params: any) { const { dataId, format, filter } = params; let dataset = this.datasets.get(dataId); if (!dataset) { throw new Error(`Dataset '${dataId}' not found`); } // Apply filter if provided if (filter) { dataset = this.applyFilter(dataset, filter); } let exportContent: string; let mimeType: string; switch (format) { case 'csv': exportContent = this.convertToCSV(dataset); mimeType = 'text/csv'; break; case 'excel': // For Excel, we'll return instructions since we can't generate actual Excel files exportContent = JSON.stringify({ message: 'Excel export requested', data: dataset, instructions: 'Use the data to create an Excel file in your application' }, null, 2); mimeType = 'application/json'; break; case 'json': exportContent = JSON.stringify(dataset, null, 2); mimeType = 'application/json'; break; default: throw new Error(`Unsupported format: ${format}`); } return { content: [{ type: 'text', text: `📤 Dataset '${dataId}' exported as ${format}:\n\n${exportContent}` }], }; } // Missing handler methods public async handleGetData(params: GetDataParams) { const { dataId, params: queryParams = {} } = params; let dataset = this.datasets.get(dataId); if (!dataset) { throw new Error(`Dataset '${dataId}' not found`); } // Apply filtering if (queryParams.filter) { dataset = this.applyFilter(dataset, queryParams.filter); } // Apply search if (queryParams.search) { dataset = this.applySearch(dataset, queryParams.search); } // Apply sorting if (queryParams.sortBy) { dataset = this.applySort(dataset, queryParams.sortBy, queryParams.sortOrder || 'asc'); } // Apply pagination const offset = queryParams.offset || 0; const limit = queryParams.limit || 100; const paginatedDataset = dataset.slice(offset, offset + limit); const result = { data: paginatedDataset, total: dataset.length, offset, limit, hasMore: offset + limit < dataset.length }; return { content: [{ type: 'text', text: JSON.stringify(result, null, 2) }], }; } public async handleCrossReference(params: CrossReferenceParams) { const { queryType, inputId } = params; try { let result: any; switch (queryType) { case 'customer_to_employee_info': result = await this.resolveCrossReference('customers', 'employees', inputId, 'assignedEmployeeId'); break; case 'employee_to_company_info': result = await this.resolveCrossReference('employees', 'companies', inputId, 'companyId'); break; case 'asset_to_employee_info': result = await this.resolveCrossReference('assets', 'employees', inputId, 'assignedTo'); break; default: throw new Error(`Unsupported query type: ${queryType}`); } return { content: [{ type: 'text', text: JSON.stringify(result, null, 2) }], }; } catch (error) { throw new Error(`Cross-reference failed: ${error instanceof Error ? error.message : 'Unknown error'}`); } } public async handleModifyData(params: ModifyDataBatchParams) { const results: any[] = []; for (const operation of params.operations) { try { const { dataId, op, record } = operation; let dataset = this.datasets.get(dataId); if (!dataset) { throw new Error(`Dataset '${dataId}' not found`); } if (op === 'insert') { // Generate ID if not provided if (!record.id) { record.id = this.generateId(); } // Check for duplicates const existingIndex = dataset.findIndex(item => item.id === record.id); if (existingIndex !== -1) { throw new Error(`Record with ID '${record.id}' already exists`); } dataset.push(record); results.push({ operation: 'insert', dataId, id: record.id, status: 'success' }); } else if (op === 'update') { if (!record.id) { throw new Error('ID is required for update operations'); } const existingIndex = dataset.findIndex(item => item.id === record.id); if (existingIndex === -1) { throw new Error(`Record with ID '${record.id}' not found`); } dataset[existingIndex] = { ...dataset[existingIndex], ...record }; results.push({ operation: 'update', dataId, id: record.id, status: 'success' }); } // Save to file await this.saveDataset(dataId); } catch (error) { results.push({ operation: operation.op, dataId: operation.dataId, status: 'error', error: error instanceof Error ? error.message : 'Unknown error' }); } } return { content: [{ type: 'text', text: JSON.stringify(results, null, 2) }], }; } public async handleDeleteData(params: DeleteDataParams) { const { dataId, filter } = params; const dataset = this.datasets.get(dataId); if (!dataset) { throw new Error(`Dataset '${dataId}' not found`); } const initialCount = dataset.length; const filteredDataset = dataset.filter(item => !this.matchesFilter(item, filter)); const deletedCount = initialCount - filteredDataset.length; this.datasets.set(dataId, filteredDataset); await this.saveDataset(dataId); return { content: [{ type: 'text', text: JSON.stringify({ dataId, deletedCount, remainingCount: filteredDataset.length }, null, 2) }], }; } public async handleCreateDataset(params: CreateDatasetParams) { const { dataId, data, description } = params; // Validate dataset name if (!/^[a-zA-Z0-9_-]+$/.test(dataId)) { throw new Error('Dataset name must contain only alphanumeric characters, underscores, and hyphens'); } if (this.datasets.has(dataId)) { throw new Error(`Dataset '${dataId}' already exists`); } // Validate data if (!Array.isArray(data)) { throw new Error('Data must be an array'); } // Add IDs to records that don't have them const processedData = data.map(record => ({ ...record, id: record.id || this.generateId() })); this.datasets.set(dataId, processedData); // Create file path const fileName = `${dataId}.json`; const filePath = `../../data/${fileName}`; this.datasetPaths[dataId] = filePath; await this.saveDataset(dataId); return { content: [{ type: 'text', text: JSON.stringify({ dataId, recordCount: processedData.length, description, status: 'created' }, null, 2) }], }; } public async handleDeleteDataset(params: DeleteDatasetParams) { const { dataId, confirm } = params; if (!confirm) { throw new Error('Confirmation required to delete dataset'); } // Don't allow deletion of predefined datasets const predefinedDatasets = ['employees', 'companies', 'departments', 'domains', 'projects', 'customers', 'assets', 'policies', 'roles', 'accesslogs', 'training', 'performance', 'tickets', 'vendors', 'locations']; if (predefinedDatasets.includes(dataId)) { throw new Error('Cannot delete predefined datasets'); } if (!this.datasets.has(dataId)) { throw new Error(`Dataset '${dataId}' not found`); } // Create backup before deletion await this.handleBackupDataset({ dataId }); // Delete from memory this.datasets.delete(dataId); // Delete file const relativePath = this.datasetPaths[dataId]; if (relativePath) { try { const fullPath = path.resolve(__dirname, relativePath); await fs.unlink(fullPath); } catch (error) { console.error(`Failed to delete file for dataset ${dataId}:`, error); } } delete this.datasetPaths[dataId]; return { content: [{ type: 'text', text: JSON.stringify({ dataId, status: 'deleted', backup: 'created' }, null, 2) }], }; } public async handleListDatasets() { const datasets = Array.from(this.datasets.entries()).map(([dataId, data]) => ({ dataId, recordCount: data.length, sampleRecord: data.length > 0 ? data[0] : null, fields: data.length > 0 ? Object.keys(data[0]) : [], type: this.datasetPaths[dataId]?.includes('../../data/') ? 'predefined' : 'custom' })); return { content: [{ type: 'text', text: JSON.stringify(datasets, null, 2) }], }; } public async handleBackupDataset(params: { dataId: string }) { const { dataId } = params; const dataset = this.datasets.get(dataId); if (!dataset) { throw new Error(`Dataset '${dataId}' not found`); } const timestamp = new Date().toISOString().replace(/[:.]/g, '-'); const backupFileName = `${dataId}_backup_${timestamp}.json`; const backupPath = path.resolve(__dirname, '../../backups', backupFileName); // Ensure backup directory exists const backupDir = path.dirname(backupPath); try { await fs.access(backupDir); } catch { await fs.mkdir(backupDir, { recursive: true }); } await fs.writeFile(backupPath, JSON.stringify(dataset, null, 2)); return { content: [{ type: 'text', text: JSON.stringify({ dataId, backupFile: backupFileName, timestamp, recordCount: dataset.length }, null, 2) }], }; } public async handleBulkOperations(params: BulkOperationParams) { const results: any[] = []; for (const operation of params.operations) { try { let result: any; switch (operation.type) { case 'create_dataset': result = await this.handleCreateDataset(operation.params); break; case 'modify_data': result = await this.handleModifyData(operation.params); break; case 'delete_data': result = await this.handleDeleteData(operation.params); break; case 'delete_dataset': result = await this.handleDeleteDataset(operation.params); break; case 'trigger_zapier_tool': result = await this.handleTriggerZapierTool(operation.params); break; case 'trigger_google_sheets_tool': result = await this.handleTriggerGoogleSheetsTools(operation.params); break; default: throw new Error(`Unsupported operation type: ${operation.type}`); } results.push({ operation: operation.type, status: 'success', result: result.content[0].text }); } catch (error) { results.push({ operation: operation.type, status: 'error', error: error instanceof Error ? error.message : 'Unknown error' }); } } return { content: [{ type: 'text', text: JSON.stringify(results, null, 2) }], }; } // Utility methods private generateGoogleDriveInstructions(toolName: string, args: any): string { if (toolName.includes('retrieve_files')) { return `List files in Google Drive${args.folderId ? ` from folder ${args.folderId}` : ''}${args.query ? ` matching "${args.query}"` : ''}`; } if (toolName.includes('create_folder')) { return `Create a new folder in Google Drive${args.parentId ? ` inside folder ${args.parentId}` : ''}`; } if (toolName.includes('upload')) { return `Upload file to Google Drive${args.folderId ? ` to folder ${args.folderId}` : ''}`; } return `Execute Google Drive operation: ${toolName}`; } private applyFilter(dataset: DatasetItem[], filter: Record<string, any>): DatasetItem[] { return dataset.filter(item => this.matchesFilter(item, filter)); } private matchesFilter(item: DatasetItem, filter: Record<string, any>): boolean { return Object.entries(filter).every(([key, value]) => { if (typeof value === 'object' && value !== null) { // Handle operators like $regex, $gt, $lt, $in, etc. if (value.$regex) { const regex = new RegExp(value.$regex, value.$options || 'i'); return regex.test(String(item[key] || '')); } if (value.$gt !== undefined) { return (item[key] || 0) > value.$gt; } if (value.$lt !== undefined) { return (item[key] || 0) < value.$lt; } if (value.$in) { return value.$in.includes(item[key]); } } return item[key] === value; }); } private applySearch(dataset: DatasetItem[], search: string): DatasetItem[] { const searchLower = search.toLowerCase(); return dataset.filter(item => Object.values(item).some(value => String(value || '').toLowerCase().includes(searchLower) ) ); } private applySort(dataset: DatasetItem[], sortBy: string, sortOrder: 'asc' | 'desc'): DatasetItem[] { return [...dataset].sort((a, b) => { const aVal = a[sortBy]; const bVal = b[sortBy]; if (aVal < bVal) return sortOrder === 'asc' ? -1 : 1; if (aVal > bVal) return sortOrder === 'asc' ? 1 : -1; return 0; }); } private async resolveCrossReference( sourceDataset: string, targetDataset: string, inputId: string, foreignKey: string ): Promise<any> { const sourceData = this.datasets.get(sourceDataset); const targetData = this.datasets.get(targetDataset); if (!sourceData || !targetData) { throw new Error(`Source or target dataset not found`); } const sourceRecord = sourceData.find(item => item.id === inputId); if (!sourceRecord) { throw new Error(`Record with ID '${inputId}' not found in ${sourceDataset}`); } const foreignId = sourceRecord[foreignKey]; if (!foreignId) { return { sourceRecord, targetRecord: null }; } const targetRecord = targetData.find(item => item.id === foreignId); return { sourceRecord, targetRecord }; } private generateId(): string { return Math.random().toString(36).substring(2, 15) + Math.random().toString(36).substring(2, 15); } private async saveDataset(dataId: string): Promise<void> { const dataset = this.datasets.get(dataId); const relativePath = this.datasetPaths[dataId]; if (!dataset || !relativePath) { throw new Error(`Dataset '${dataId}' not found or path not configured`); } const fullPath = path.resolve(__dirname, relativePath); await fs.writeFile(fullPath, JSON.stringify(dataset, null, 2)); } private convertToCSV(data: DatasetItem[]): string { if (data.length === 0) return ''; const headers = Object.keys(data[0]); const csvHeaders = headers.join(','); const csvRows = data.map(item => headers.map(header => { const value = item[header]; return typeof value === 'string' && value.includes(',') ? `"${value}"` : String(value || ''); }).join(',') ); return [csvHeaders, ...csvRows].join('\n'); } private performAnalysis(dataset: DatasetItem[], fields: string[], analysisType: string): any { const result: any = { dataset: dataset.length, fields: fields.length, type: analysisType }; switch (analysisType) { case 'summary': result.summary = fields.map(field => ({ field, count: dataset.filter(item => item[field] !== undefined).length, unique: new Set(dataset.map(item => item[field])).size, sample: dataset.slice(0, 3).map(item => item[field]) })); break; case 'distribution': result.distribution = fields.map(field => { const values = dataset.map(item => item[field]).filter(v => v !== undefined); const counts: Record<string, number> = {}; values.forEach(v => counts[String(v)] = (counts[String(v)] || 0) + 1); return { field, distribution: counts }; }); break; case 'trends': result.trends = { message: 'Trend analysis requires time-series data' }; break; default: result.analysis = { message: `Analysis type '${analysisType}' not fully implemented` }; } return result; } async run(): Promise<void> { const transport = new StdioServerTransport(); await this.server.connect(transport); console.error('🚀 Organizational Data Server running on stdio'); } } // Main execution async function main() { const server = new OrganizationalDataServer(); await server.initialize(); await server.run(); } main().catch(console.error);