text-to-sql-reasoner

Core reasoning flow for text-to-SQL that can be exposed by any transport (Streamlit, REST API, MCP, etc.).

[Input Port]
   ↓
[Prompt Composer]
   ↓
[Retrieval (RAG)]
   ↓
[Reasoning LLM]
   ↓
[Query Candidate]
   ↓
[MCP: run_query]
   ↓
[Execution Feedback]
   ├─ slow / failed ─► retry loop
   └─ success & fast ─► output port

What lives here (and how it works)

• Pure orchestrator in TypeScript (Bun-friendly) with clean interfaces for prompt composing, retrieval, LLM reasoning, query execution, and feedback policy.
• Transport-agnostic: callers plug in their own input/output (UI, REST, MCP, etc.).
• Defaults: prompt composer (DefaultPromptComposer), feedback policy (SimpleFeedbackPolicy), MCP executor (MCPQueryExecutor), Milvus retriever (MilvusRetriever), and OpenAI LLM wrapper (OpenAILLM).
• Flow: retrieve context → compose prompt → ask LLM for SQL candidates → execute via MCP run_query → accept/retry via feedback policy.

Example wiring

import {
  ReasoningPipeline,
  SimpleFeedbackPolicy,
  DefaultPromptComposer,
  OpenAILLM,
  MCPQueryExecutor,
} from "@neural-technologies-indonesia/nl2sql";

const pipeline = new ReasoningPipeline({
  composer: new DefaultPromptComposer(),
  retriever: /* plug a retriever implementation here */,
  llm: new OpenAILLM({ model: "gpt-4o-mini" }),
  executor: new MCPQueryExecutor({
    runQuery: async (sql) => {
      // call MCP run_query tool here
      return { durationMs: 120, rows: 1 };
    },
  }),
  policy: new SimpleFeedbackPolicy({ durationBudgetMs: 5000 }),
});

const result = await pipeline.run({ question: "How many users signed up today?" });
console.log(result);

Port it anywhere

• REST / gRPC / WebSocket: parse request → build PromptInput → call pipeline.run → return FlowOutput.
• Streamlit or CLI: same core call; render logs for debugging.
• MCP: wrap pipeline.run behind a tool; reuse the included MCPQueryExecutor to hit run_query.

Inference backends

• Implemented: OpenAI (default focus).
• Future slots: MLX LM (Apple), Ollama, vLLM — add new adapters implementing ReasoningLLM without changing the pipeline.

Env

• Copy .env.example and set OPENAI_API_KEY (and optionally OPENAI_BASE_URL, OPENAI_MODEL).
• Milvus retriever defaults: MILVUS_ADDRESS=milvus:19530, MILVUS_COLLECTION=rag_tables, MILVUS_VECTOR_FIELD=embedding, OPENAI_EMBEDDING_MODEL=text-embedding-3-small.

Quick start (run-node)

• Make sure ../mcp-server is available (defaults to ../mcp-server/index.ts).
• Add OPENAI_API_KEY to example/.env.
• Run: bun run example/run-node.ts.

import {
  DefaultPromptComposer,
  OpenAILLM,
  ReasoningPipeline,
  SimpleFeedbackPolicy,
  createMcpUseAdapters,
} from "@neural-technologies-indonesia/nl2sql";
import dotenv from "dotenv";
import path from "node:path";
import { fileURLToPath } from "node:url";

const __dirname = path.dirname(fileURLToPath(import.meta.url));
dotenv.config({ path: path.join(__dirname, ".env") });

const serverCwd =
  process.env.MCP_SERVER_CWD ??
  path.resolve(__dirname, "..", "..", "mcp-server");
const serverEntry = path.join(serverCwd, "index.ts");

const { executor, retriever, listIndexes, close } = await createMcpUseAdapters({
  serverCwd,
  command: "bun",
  args: ["run", serverEntry],
  env: Object.fromEntries(
    Object.entries(process.env).filter(
      (entry): entry is [string, string] => typeof entry[1] === "string"
    )
  ),
});

const pipeline = new ReasoningPipeline({
  composer: new DefaultPromptComposer(),
  retriever,
  llm: new OpenAILLM({ model: process.env.OPENAI_MODEL ?? "gpt-4o-mini" }),
  executor,
  indexLookup: { listIndexes },
  policy: new SimpleFeedbackPolicy({ durationBudgetMs: 5000 }),
});

const result = await pipeline.run({
  question:
    "Show monthly entity counts per tier since 2020. For each tier and month, return total entities and how many match metadata tier. Ignore groups with total ≤ 1000, ordered by most recent month and highest totals.",
});

console.log(JSON.stringify(result, null, 2));

await close();

Examples

• example/bun-server.ts: starts a Bun HTTP server exposing POST /query that runs the pipeline (OpenAI + Milvus retriever + MCP-backed executor). Run with bun run example/bun-server.ts.
• example/streamlit_app.py: minimal Streamlit UI that POSTs to the Bun server. Configure API_URL in .streamlit/secrets.toml or edit the default (http://localhost:8787/query).

Use the sibling MCP server (../mcp-server) via mcp-use

• That folder ships a FastMCP server exposing run_query/run_query_explain/list_indexes for Postgres. Start it with your env: DATABASE_URL=... bun run start from ../mcp-server.
• Install the client dependency here: bun add mcp-use (or npm install mcp-use).
• If your MCP server lives somewhere else, point the helper there with env vars: MCP_SERVER_CWD=/path/to/server, optionally MCP_SERVER_CMD=bun and MCP_SERVER_ARGS="run start". The example Bun server picks these up automatically.
• Wire the pipeline to that server with the provided helper:

  import {
    ReasoningPipeline,
    DefaultPromptComposer,
    SimpleFeedbackPolicy,
    OpenAILLM,
    createMcpUseAdapters,
  } from "@neural-technologies-indonesia/nl2sql";
  
  const { executor, retriever, close } = await createMcpUseAdapters({
    // optional overrides:
    // serverCwd: path.resolve("../mcp-server"),
    // command: "bun",
    // args: ["run", "start"],
  });
  
  const pipeline = new ReasoningPipeline({
    composer: new DefaultPromptComposer(),
    retriever,
    llm: new OpenAILLM({ model: process.env.OPENAI_MODEL ?? "gpt-4o-mini" }),
    executor,
    policy: new SimpleFeedbackPolicy({ durationBudgetMs: 5000 }),
  });
  
  const result = await pipeline.run({ question: "How many users signed up today?" });
  console.log(result);
  await close();

• createMcpUseAdapters calls the MCP tools for execution (no duplicated logic here) and wires the retriever directly to Milvus using the provided/env config.

Next steps

• Swap in real retriever and LLM clients.
• Add schema guards to prompt composer (e.g., restrict tables/columns).
• Extend feedback policy with statistical latency bounds or result validation.