1// Tool: text_to_sql
2// Description: "Convert natural language question to SQL query"
3
4// OpenAI prompt for SQL generation:
5const sqlPrompt = `
6You are a SQL expert. Convert the question to a SQL query.
7
8Database schema:
9- customers (id, name, email, created_at, plan)
10- orders (id, customer_id, amount, status, created_at)
11- products (id, name, price, category)
12- order_items (id, order_id, product_id, quantity)
13
14Question: "${$json.question}"
15
16Rules:
171. Use standard SQL syntax
182. Include appropriate JOINs
193. Add WHERE clauses for filters
204. Use aggregations when needed
215. Limit results to 100 rows
22
23Return only the SQL query, no explanation.`;

1// Tool: execute_sql
2// Description: "Execute a SQL query safely and return results"
3
4// PostgreSQL or MySQL node:
5// Query: {{ $json.sqlQuery }}
6
7// SAFETY: Add read-only guards
8function validateQuery(sql) {
9  const dangerous = ['DROP', 'DELETE', 'UPDATE', 'INSERT', 'ALTER', 'TRUNCATE'];
10  const upperSQL = sql.toUpperCase();
11  
12  for (const keyword of dangerous) {
13    if (upperSQL.includes(keyword)) {
14      return { safe: false, reason: `Query contains ${keyword} - write operations not allowed` };
15    }
16  }
17  return { safe: true };
18}
19
20const validation = validateQuery($json.sqlQuery);
21if (!validation.safe) {
22  return { json: { error: validation.reason } };
23}
24
25// Execute query via database node

1// Tool: analyze_data
2// Description: "Analyze query results and provide insights"
3
4const analysisPrompt = `
5Analyze this data and provide insights:
6
7Query: ${$json.originalQuestion}
8SQL: ${$json.sqlQuery}
9Results (${$json.results.length} rows):
10${JSON.stringify($json.results.slice(0, 20), null, 2)}
11
12Provide:
131. Direct answer to the question
142. Key metrics (totals, averages, etc.)
153. Notable patterns or outliers
164. Recommendations if appropriate
17
18Format as a clear, concise report.`;

1// Agent System Prompt
2const agentSystem = `
3You are a data analyst assistant with access to our database.
4
5Available tools:
61. text_to_sql - Convert question to SQL
72. execute_sql - Run SQL query (read-only)
83. analyze_data - Analyze query results
9
10Workflow:
111. Understand the user's question
122. Generate appropriate SQL
133. Execute the query
144. Analyze and explain results
155. Present findings clearly
16
17Always explain your analysis in Vietnamese.
18Include key numbers and insights.
19`;

1// Tool: get_schema
2// Description: "Get database table schema information"
3
4// Query to get schema info:
5const schemaQuery = `
6SELECT table_name, column_name, data_type 
7FROM information_schema.columns 
8WHERE table_schema = 'public'
9ORDER BY table_name, ordinal_position;
10`;
11
12// Format for agent:
13const schema = $json.results.reduce((acc, row) => {
14  if (!acc[row.table_name]) acc[row.table_name] = [];
15  acc[row.table_name].push(`${row.column_name} (${row.data_type})`);
16  return acc;
17}, {});
18
19const formatted = Object.entries(schema)
20  .map(([table, cols]) => `${table}: ${cols.join(', ')}`)
21  .join('\n');
22
23return { json: { schema: formatted } };

1// Tool: generate_report
2// Combine query results into formatted report
3
4const reportPrompt = `
5Create a report from this data analysis:
6
7Topic: ${$json.topic}
8Data: ${JSON.stringify($json.analysisResults)}
9
10Format as Markdown report:
11# Report: [Title]
12## Summary
13[Key findings]
14## Metrics
15| Metric | Value |
16|--------|-------|
17## Details
18[Detailed analysis]
19## Recommendations
20[Action items]
21`;