import express, { Request, Response } from 'express';
import cors from 'cors';
import { SupersetClient } from './superset-client.js';
import 'dotenv/config';
// Environment configuration
const SUPERSET_URL = process.env.SUPERSET_URL || 'http://192.168.1.223:8088';
const SUPERSET_USERNAME = process.env.SUPERSET_USERNAME || 'admin';
const SUPERSET_PASSWORD = process.env.SUPERSET_PASSWORD || '@@hehe2024xX';
const MCP_PORT = parseInt(process.env.MCP_PORT || '5151');
// Initialize Superset client
const supersetClient = new SupersetClient(
SUPERSET_URL,
SUPERSET_USERNAME,
SUPERSET_PASSWORD
);
// ============ TOOL DEFINITIONS ============
interface ToolDefinition {
name: string;
description: string;
inputSchema: {
type: string;
properties: Record<string, { type: string; description: string }>;
required?: string[];
};
handler: (params: Record<string, unknown>) => Promise<{ content: { type: string; text: string }[]; isError?: boolean }>;
}
const tools: ToolDefinition[] = [
// ============ DASHBOARD TOOLS ============
{
name: 'list_dashboards',
description: 'Get all available dashboards from Superset',
inputSchema: { type: 'object', properties: {} },
handler: async () => {
try {
const dashboards = await supersetClient.listDashboards();
return { content: [{ type: 'text', text: JSON.stringify(dashboards, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'get_dashboard',
description: 'Get detailed information about a specific dashboard including its charts',
inputSchema: {
type: 'object',
properties: { id_or_slug: { type: 'string', description: 'Dashboard ID or slug' } },
required: ['id_or_slug']
},
handler: async (params) => {
try {
const dashboard = await supersetClient.getDashboard(params.id_or_slug as string);
return { content: [{ type: 'text', text: JSON.stringify(dashboard, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'create_dashboard',
description: 'Create a new dashboard in Superset',
inputSchema: {
type: 'object',
properties: {
title: { type: 'string', description: 'Dashboard title' },
slug: { type: 'string', description: 'URL-friendly slug (optional)' }
},
required: ['title']
},
handler: async (params) => {
try {
const dashboard = await supersetClient.createDashboard(params.title as string, params.slug as string | undefined);
return { content: [{ type: 'text', text: `Dashboard created!\n${JSON.stringify(dashboard, null, 2)}` }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'add_chart_to_dashboard',
description: 'Add an existing chart to a dashboard. Use this to insert charts into dashboards.',
inputSchema: {
type: 'object',
properties: {
dashboard_id: { type: 'number', description: 'Dashboard ID to add chart to' },
chart_id: { type: 'number', description: 'Chart ID to add to the dashboard' }
},
required: ['dashboard_id', 'chart_id']
},
handler: async (params) => {
try {
const result = await supersetClient.addChartToDashboard(
params.dashboard_id as number,
params.chart_id as number
);
return { content: [{ type: 'text', text: result.message }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'add_charts_to_dashboard',
description: 'Add MULTIPLE charts to a dashboard in a single operation. This is the RECOMMENDED way to add charts because it prevents race conditions and allows proper layout configuration. Use this instead of calling add_chart_to_dashboard multiple times.',
inputSchema: {
type: 'object',
properties: {
dashboard_id: { type: 'number', description: 'Dashboard ID to add charts to' },
charts: {
type: 'string',
description: 'JSON array of chart configurations. Each chart should have: chartId (required), width (optional, default: 12, use 4/6/8/12 for grid layout), height (optional, default: 50), row (optional, charts with same row number will be placed side by side). Example: [{"chartId": 1, "width": 12, "row": 0}, {"chartId": 2, "width": 6, "row": 1}, {"chartId": 3, "width": 6, "row": 1}]'
}
},
required: ['dashboard_id', 'charts']
},
handler: async (params) => {
try {
// Parse charts JSON
let charts: Array<{ chartId: number; width?: number; height?: number; row?: number }>;
try {
charts = JSON.parse(params.charts as string);
} catch {
return { content: [{ type: 'text', text: 'Error: Invalid charts JSON. Expected array of {chartId, width?, height?, row?}' }], isError: true };
}
if (!Array.isArray(charts) || charts.length === 0) {
return { content: [{ type: 'text', text: 'Error: charts must be a non-empty array' }], isError: true };
}
const result = await supersetClient.addChartsToDashboard(
params.dashboard_id as number,
charts
);
return { content: [{ type: 'text', text: result.message }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
// ============ CHART TOOLS ============
{
name: 'list_charts',
description: 'Get all available charts from Superset',
inputSchema: { type: 'object', properties: {} },
handler: async () => {
try {
const charts = await supersetClient.listCharts();
return { content: [{ type: 'text', text: JSON.stringify(charts, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'get_chart_data',
description: 'Fetch data from an existing chart',
inputSchema: {
type: 'object',
properties: { chart_id: { type: 'number', description: 'Chart ID' } },
required: ['chart_id']
},
handler: async (params) => {
try {
const data = await supersetClient.getChartData(params.chart_id as number);
return { content: [{ type: 'text', text: JSON.stringify(data, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'get_chart',
description: 'Get full chart configuration including params structure. Use this to inspect how existing charts are configured.',
inputSchema: {
type: 'object',
properties: { chart_id: { type: 'number', description: 'Chart ID' } },
required: ['chart_id']
},
handler: async (params) => {
try {
const chart = await supersetClient.getChart(params.chart_id as number);
return { content: [{ type: 'text', text: JSON.stringify(chart, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'create_chart',
description: 'Create a new chart in Superset. IMPORTANT: You MUST provide metrics and x_axis for the chart to display data. Use get_dataset_schema first to get available columns.',
inputSchema: {
type: 'object',
properties: {
name: { type: 'string', description: 'Chart name' },
viz_type: { type: 'string', description: 'Visualization type: "echarts_timeseries_bar", "echarts_timeseries_line", "pie", "table", "big_number_total"' },
datasource_id: { type: 'number', description: 'Dataset ID (use list_datasets to find)' },
datasource_type: { type: 'string', description: 'Datasource type (default: "table")' },
metrics: { type: 'string', description: 'REQUIRED: JSON array of metrics. Example: [{"aggregate":"SUM","column":{"column_name":"total"},"label":"Total Revenue"}] or [{"expressionType":"SQL","sqlExpression":"COUNT(*)","label":"Count"}]' },
x_axis: { type: 'string', description: 'REQUIRED: Column name for X-axis, e.g., "tanggal" or "created_at"' },
groupby: { type: 'string', description: 'JSON array of dimension columns to group by, e.g., ["category", "status"]' },
time_grain: { type: 'string', description: 'Time grain for time series: "PT1H" (hourly), "P1D" (daily), "P1W" (weekly), "P1M" (monthly). Default: P1D' },
time_range: { type: 'string', description: 'Time filter: "Last 7 days", "Last 30 days", "Last month", "No filter". Default: "No filter"' },
additional_params: { type: 'string', description: 'Additional chart params as JSON for advanced configuration' }
},
required: ['name', 'viz_type', 'datasource_id', 'metrics', 'x_axis']
},
handler: async (params) => {
try {
// Build chart params
const chartParams: Record<string, unknown> = {
viz_type: params.viz_type as string,
time_range: (params.time_range as string) || 'No filter',
};
// Parse and add metrics (REQUIRED)
if (params.metrics) {
try {
chartParams.metrics = JSON.parse(params.metrics as string);
} catch {
console.error('Failed to parse metrics JSON');
}
}
// Add X-axis column (REQUIRED) - set both x_axis and granularity_sqla for time series compatibility
if (params.x_axis) {
chartParams.x_axis = params.x_axis as string;
chartParams.granularity_sqla = params.x_axis as string;
chartParams.time_grain_sqla = (params.time_grain as string) || 'P1D';
}
// Parse and add groupby columns
if (params.groupby) {
try {
chartParams.groupby = JSON.parse(params.groupby as string);
} catch {
console.error('Failed to parse groupby JSON');
}
}
// Parse and merge additional params
if (params.additional_params) {
try {
const additionalParams = JSON.parse(params.additional_params as string);
Object.assign(chartParams, additionalParams);
} catch {
console.error('Failed to parse additional_params JSON');
}
}
const chart = await supersetClient.createChart(
params.name as string,
params.viz_type as string,
params.datasource_id as number,
(params.datasource_type as string) || 'table',
chartParams
);
return { content: [{ type: 'text', text: `Chart created!\n${JSON.stringify(chart, null, 2)}` }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'create_chart_auto',
description: 'Create a chart with AUTO-DETECTED visualization configuration. Analyzes dataset schema to automatically determine X-axis, metrics, and dimensions. Use this when you want the chart to display data immediately without manual configuration. You can still override any auto-detected values.',
inputSchema: {
type: 'object',
properties: {
name: { type: 'string', description: 'Chart name' },
viz_type: { type: 'string', description: 'Visualization type: "echarts_timeseries_bar", "echarts_timeseries_line", "pie", "table", "big_number_total"' },
datasource_id: { type: 'number', description: 'Dataset ID (use list_datasets to find)' },
datasource_type: { type: 'string', description: 'Datasource type (default: "table")' },
x_axis: { type: 'string', description: 'OPTIONAL: Override auto-detected X-axis column (for time series charts)' },
metrics: { type: 'string', description: 'OPTIONAL: Override auto-detected metrics. JSON array format.' },
groupby: { type: 'string', description: 'OPTIONAL: Override auto-detected dimensions/groupby. JSON array format. Required for pie charts.' },
time_grain: { type: 'string', description: 'Time grain: "PT1H", "P1D", "P1W", "P1M". Default: P1D' },
time_range: { type: 'string', description: 'Time filter. Default: "No filter"' }
},
required: ['name', 'viz_type', 'datasource_id']
},
handler: async (params) => {
try {
// Auto-analyze dataset for visualization configuration
console.log('🔍 Analyzing dataset for auto-configuration...');
const analysis = await supersetClient.analyzeDatasetForVisualization(params.datasource_id as number);
const isPieChart = (params.viz_type as string) === 'pie';
// Build chart params with auto-detected values, allowing manual overrides
const chartParams: Record<string, unknown> = {
viz_type: params.viz_type as string,
time_range: (params.time_range as string) || 'No filter',
};
if (isPieChart) {
// PIE CHART: Uses groupby columns instead of x_axis
if (params.groupby) {
try {
chartParams.groupby = JSON.parse(params.groupby as string);
} catch {
console.error('Failed to parse groupby JSON, using auto-detected');
}
}
if (!chartParams.groupby && analysis.suggestedDimensionColumns.length > 0) {
chartParams.groupby = analysis.suggestedDimensionColumns.slice(0, 1);
console.log('📌 Auto-detected groupby for pie:', chartParams.groupby);
}
// Metrics for pie chart
if (params.metrics) {
try {
chartParams.metrics = JSON.parse(params.metrics as string);
} catch {
console.error('Failed to parse metrics JSON, using auto-detected');
}
}
if (!chartParams.metrics && analysis.suggestedMetricColumns.length > 0) {
chartParams.metrics = analysis.suggestedMetricColumns.slice(0, 1).map(m => ({
expressionType: 'SIMPLE',
column: { column_name: m.column_name },
aggregate: m.aggregate,
label: m.label
}));
}
console.log('🥧 Building pie chart with groupby:', chartParams.groupby);
} else {
// TIME SERIES / BAR / LINE CHARTS: Uses x_axis
let xAxis = params.x_axis as string;
if (!xAxis) {
// Prefer time column for time series, otherwise use first dimension
if (analysis.suggestedTimeColumn) {
xAxis = analysis.suggestedTimeColumn;
} else if (analysis.suggestedDimensionColumns.length > 0) {
xAxis = analysis.suggestedDimensionColumns[0];
console.log('📌 Using dimension as x_axis:', xAxis);
}
}
if (xAxis) {
chartParams.x_axis = xAxis;
// Only set granularity_sqla if it's a time column
if (analysis.suggestedTimeColumn === xAxis) {
chartParams.granularity_sqla = xAxis;
chartParams.time_grain_sqla = (params.time_grain as string) || 'P1D';
}
}
// Metrics
if (params.metrics) {
try {
chartParams.metrics = JSON.parse(params.metrics as string);
} catch {
console.error('Failed to parse metrics JSON, using auto-detected');
}
}
if (!chartParams.metrics && analysis.suggestedMetricColumns.length > 0) {
chartParams.metrics = analysis.suggestedMetricColumns.slice(0, 2).map(m => ({
expressionType: 'SIMPLE',
column: { column_name: m.column_name },
aggregate: m.aggregate,
label: m.label
}));
}
// Groupby/dimensions
if (params.groupby) {
try {
chartParams.groupby = JSON.parse(params.groupby as string);
} catch {
console.error('Failed to parse groupby JSON, using auto-detected');
}
}
if (!chartParams.groupby && analysis.suggestedDimensionColumns.length > 0) {
chartParams.groupby = analysis.suggestedDimensionColumns.slice(0, 1);
}
}
console.log('📊 Final chart config:', JSON.stringify({
viz_type: params.viz_type,
x_axis: chartParams.x_axis,
metrics: chartParams.metrics,
groupby: chartParams.groupby
}, null, 2));
const chart = await supersetClient.createChart(
params.name as string,
params.viz_type as string,
params.datasource_id as number,
(params.datasource_type as string) || 'table',
chartParams
);
return {
content: [{
type: 'text',
text: `Chart created with auto-configuration!\n\nAuto-detected:\n- X-axis: ${analysis.suggestedTimeColumn || 'none'}\n- Metrics: ${analysis.suggestedMetricColumns.map(m => m.label).join(', ') || 'none'}\n- Dimensions: ${analysis.suggestedDimensionColumns.join(', ') || 'none'}\n\nChart:\n${JSON.stringify(chart, null, 2)}`
}]
};
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
// ============ DATASET TOOLS ============
{
name: 'list_datasets',
description: 'Get all available datasets (tables) from Superset',
inputSchema: { type: 'object', properties: {} },
handler: async () => {
try {
const datasets = await supersetClient.listDatasets();
const simplified = datasets.map((d) => ({
id: d.id,
table_name: d.table_name,
schema: d.schema,
database: d.database?.database_name,
}));
return { content: [{ type: 'text', text: JSON.stringify(simplified, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'get_dataset_schema',
description: 'Get column definitions for a specific dataset',
inputSchema: {
type: 'object',
properties: { dataset_id: { type: 'number', description: 'Dataset ID' } },
required: ['dataset_id']
},
handler: async (params) => {
try {
const columns = await supersetClient.getDatasetSchema(params.dataset_id as number);
return { content: [{ type: 'text', text: JSON.stringify(columns, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
// ============ SQL TOOLS ============
{
name: 'list_databases',
description: 'Get all available database connections in Superset',
inputSchema: { type: 'object', properties: {} },
handler: async () => {
try {
const databases = await supersetClient.listDatabases();
return { content: [{ type: 'text', text: JSON.stringify(databases, null, 2) }] };
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
},
{
name: 'execute_sql',
description: 'Execute a SQL query against a Superset database',
inputSchema: {
type: 'object',
properties: {
database_id: { type: 'number', description: 'Database connection ID' },
sql: { type: 'string', description: 'SQL query to execute' },
schema: { type: 'string', description: 'Schema name (optional)' }
},
required: ['database_id', 'sql']
},
handler: async (params) => {
try {
const result = await supersetClient.executeSQL(
params.database_id as number,
params.sql as string,
params.schema as string | undefined
);
return {
content: [{
type: 'text',
text: `Query executed!\nStatus: ${result.status}\nColumns: ${result.columns.join(', ')}\n\nData:\n${JSON.stringify(result.data.slice(0, 50), null, 2)}${result.data.length > 50 ? '\n... (showing first 50 rows)' : ''}`
}]
};
} catch (error) {
return { content: [{ type: 'text', text: `Error: ${error instanceof Error ? error.message : 'Unknown error'}` }], isError: true };
}
}
}
];
// ============ JSON-RPC HANDLER (STATELESS) ============
interface JsonRpcRequest {
jsonrpc: string;
id?: string | number | null;
method: string;
params?: Record<string, unknown>;
}
interface JsonRpcResponse {
jsonrpc: string;
id: string | number | null;
result?: unknown;
error?: {
code: number;
message: string;
data?: unknown;
};
}
async function handleJsonRpcRequest(request: JsonRpcRequest): Promise<JsonRpcResponse> {
const { id, method, params } = request;
console.log(`📥 MCP Request: ${method}`);
// Handle MCP protocol methods
switch (method) {
case 'initialize':
return {
jsonrpc: '2.0',
id: id ?? null,
result: {
protocolVersion: '2024-11-05',
capabilities: {
tools: { listChanged: false }
},
serverInfo: {
name: 'superset-mcp',
version: '1.0.0'
}
}
};
case 'notifications/initialized':
// This is a notification, no response needed but we'll acknowledge
return {
jsonrpc: '2.0',
id: id ?? null,
result: {}
};
case 'tools/list':
return {
jsonrpc: '2.0',
id: id ?? null,
result: {
tools: tools.map(t => ({
name: t.name,
description: t.description,
inputSchema: t.inputSchema
}))
}
};
case 'tools/call': {
const toolName = (params?.name as string) || '';
const toolArgs = (params?.arguments as Record<string, unknown>) || {};
const tool = tools.find(t => t.name === toolName);
if (!tool) {
return {
jsonrpc: '2.0',
id: id ?? null,
error: {
code: -32601,
message: `Tool not found: ${toolName}`
}
};
}
try {
const result = await tool.handler(toolArgs);
return {
jsonrpc: '2.0',
id: id ?? null,
result
};
} catch (error) {
return {
jsonrpc: '2.0',
id: id ?? null,
error: {
code: -32603,
message: error instanceof Error ? error.message : 'Unknown error'
}
};
}
}
case 'ping':
return {
jsonrpc: '2.0',
id: id ?? null,
result: {}
};
default:
return {
jsonrpc: '2.0',
id: id ?? null,
error: {
code: -32601,
message: `Method not found: ${method}`
}
};
}
}
// Express app setup
const app = express();
// CORS configuration for Open WebUI
app.use(cors({
origin: '*',
methods: ['GET', 'POST', 'DELETE', 'OPTIONS'],
allowedHeaders: ['Content-Type', 'Authorization', 'mcp-session-id'],
exposedHeaders: ['mcp-session-id'],
credentials: true,
}));
app.use(express.json());
// Health check endpoint
app.get('/health', (_req: Request, res: Response) => {
res.json({
status: 'ok',
server: 'superset-mcp',
version: '1.0.0',
mode: 'stateless',
tools: tools.map(t => t.name)
});
});
// MCP endpoint - COMPLETELY STATELESS
app.post('/mcp', async (req: Request, res: Response) => {
try {
const body = req.body;
// Handle batch requests
if (Array.isArray(body)) {
const responses = await Promise.all(body.map(handleJsonRpcRequest));
res.json(responses);
return;
}
// Handle single request
const response = await handleJsonRpcRequest(body);
// Set session header for compatibility
res.setHeader('mcp-session-id', 'stateless-session');
res.json(response);
} catch (error) {
console.error('❌ MCP error:', error);
res.status(500).json({
jsonrpc: '2.0',
id: null,
error: {
code: -32603,
message: error instanceof Error ? error.message : 'Internal error'
}
});
}
});
// SSE endpoint for streaming (optional, returns empty for stateless)
app.get('/mcp', (req: Request, res: Response) => {
res.setHeader('Content-Type', 'text/event-stream');
res.setHeader('Cache-Control', 'no-cache');
res.setHeader('Connection', 'keep-alive');
res.setHeader('mcp-session-id', 'stateless-session');
// Just keep the connection alive
const keepAlive = setInterval(() => {
res.write(': keepalive\n\n');
}, 30000);
req.on('close', () => {
clearInterval(keepAlive);
});
});
// DELETE endpoint (no-op for stateless)
app.delete('/mcp', (_req: Request, res: Response) => {
res.json({ message: 'Session cleanup acknowledged (stateless mode)' });
});
// Export for use in index.ts
export { app, MCP_PORT, SUPERSET_URL };
