npm package: @neural-technologies-indonesia/nl2sql
Description: Core reasoning pipeline for text-to-SQL.
Latest version: 0.0.3

# 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
```ts
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`.

```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:
  ```ts
  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.