ext0

Self-serve data reports for business users. ext0 is a lightweight npm library whose agentic workflow converts natural-language requests into governed, read-only database queries: four layers of safeguards vet each one, and deterministic code holds the final veto.

Every company burns developer hours on report tickets. ext0 deletes that queue: business users ask for reports in plain English, and the agentic workflow hands back a CSV that opens straight in Excel. No SQL, no ticket, no waiting.

It is built on one principle: AI output is untrusted input. Every generated query must pass a hard-coded, parser-backed security gate that cannot be disabled, and the connection itself cannot write. The model proposes, code disposes.

Highlights

• Built for Node + TypeScript servers, ships as a small ESM package with one runtime dependency
• Database agnostic: SQLite, PostgreSQL, MySQL and MongoDB behind a single adapter interface
• Four safety layers: LLM watchdog, hard-coded final gate, database dry-run, read-only connection
• Confirm step: the user approves or corrects a plain-English restatement before anything runs
• Field mapping: your app's form field names bind to real columns and return as the CSV headers
• Multi-part questions answered by one staged query, or refused clearly when the parts share no link
• Constant-memory CSV streaming: rows flow to disk one at a time, so peak memory stays flat at any row count
• Zero-boilerplate setup: live schema introspection, built-in CSV writer, sensible defaults
• Benchmarked honestly: Spider/BIRD-style execution accuracy plus a deterministic attack suite
• One structured log line per report with a pluggable sink
• Use it as plain function calls from the server you already run — a POST /report route is a copy-paste example

Architecture

request ──> WRITER ──> confirm? ──> WATCHDOG ──> FINAL GATE ──> EXPLAIN ──> RUN ──> CSV
             (LLM)     (user)        (LLM)        (code)         (DB)     (read-only)
               ▲                       │             │             │          │
               └────────── one bounded retry: rejection reason ◄──┴──────────┘

| Stage | Kind | Job | Constants | |---|---|---|---| | Writer | LLM, temp 0 | request + schema become a 1-line summary and ONE query | 30s timeout, 1 transport retry on 429/5xx | | Confirm | user | approve or correct the summary before anything runs | max 3 edit rounds, session TTL 10 min | | Watchdog (Guard 1) | LLM, separate call | judges the proposed query against the request, never writes queries | sees only request + query, fail-closed, can be disabled | | Final gate (Guard 2) | deterministic code | parse and prove: read-only, single statement, allowlist, forced row limit | always on, no flag exists, row limit default 50,000 | | EXPLAIN | database | dry-run plan check, invalid or over-broad queries die before any read | validity always, scan threshold tunable | | Run | read-only connection | the physical floor, cannot write even if everything above failed | | | CSV | zero-dep code | rows and documents become CRLF CSV, Excel-ready | streamed one row at a time, atomic write |

A rejection at any gate feeds its reason back to the writer for one regeneration (maxRetries, default 1). The pipeline is fixed code. The LLM never drives control flow.

Safety model

Two layers are load-bearing and deliberately dumb: the read-only DB account, which physically cannot write, and the final gate, which parses the query and proves its properties instead of pattern-matching text. The watchdog and the EXPLAIN threshold are helpful extras, never the only defense. The allowlist scopes what is readable down to individual columns; deny does the inverse when you only need to hide a few. Set one of them, or expose only curated views, whenever the connection can reach sensitive tables.

| Tunable | Hard-wired, no flag exists | |---|---| | rowLimit value (default 50,000) | that a row limit exists | | allow / deny: tables, collections, columns | the final gate always runs | | explainThreshold: max full scans in the plan | read-only enforcement | | guard1 on/off, maxRetries, maxConfirmRounds, sessionTtlMs | EXPLAIN validity check |

Project layout

src/
├── pipeline.ts        control flow: writer -> confirm -> guards -> explain -> run -> csv
├── writer.ts          Agent 1: request + schema -> summary + one query
├── llm.ts             plain-fetch provider calls (anthropic, gemini, openai-compatible), temp 0, 30s cap
├── guards/
│   ├── agentGuard.ts  Guard 1: watchdog LLM
│   └── finalGate.ts   Guard 2 entry: fail-closed defaults, always on
├── adapters/
│   ├── QueryTarget.ts the four-method seam every database plugs into
│   ├── sql.ts         sqlite / postgres / mysql
│   └── mongo.ts       JSON query descriptions, never code
├── fieldMap.ts        form-field mapping: load, prompt lines, header rename
└── csv.ts             zero-dep streaming writer
prompts/               writer and watchdog system prompts, editable and versioned
tests/                 181 offline cases
bench/                 accuracy, safety and memory harness
sample-db/             seeded SQLite company database
scripts/               runnable demos (sql, confirm flow, mongo)

Quick start

npm i @malharmorey/ext0

Wire a model and a read-only connection. Everything else has defaults.

import { createExt0, sqlAdapter } from "@malharmorey/ext0";

const ext0 = createExt0({
  llm: { provider: "anthropic", model: "claude-haiku-4-5", apiKey: process.env.ANTHROPIC_API_KEY },
  db:  sqlAdapter({ connection: readOnlyConnection }),
});

const report = await ext0.generateReport("products under 50 euros with name and price");
// { file: "reports/report-....csv", query, summary, meta }

That is the whole setup — the schema is introspected live from the connection. To limit what is readable, add allow (an allowlist) or deny (a blocklist); see the configuration reference.

Confirm flow

For end users you usually want the two-phase flow. The tool first restates what it understood, then the user confirms or corrects it.

const step = await ext0.interpret("orders from big customers last month");
// step.summary: "This will pull all orders placed in June 2026 ..."

const out  = await ext0.proceed(step.sessionId, { confirm: true });
const redo = await ext0.proceed(step.sessionId, { edit: "big means over 10k" });

Sessions are single-use, capped at 3 edit rounds, and expire after 10 minutes. They live in process memory, sized for a single server instance.

Serving over HTTP

ext0 is a library, not a server — call it from whatever server you already run. Build one instance at startup and reuse it (so the schema cache is shared), then a minimal POST /report route is all you need:

import { createServer } from "node:http";
import { createReadStream, unlink } from "node:fs";
import { basename } from "node:path";
import { createExt0, sqlAdapter } from "@malharmorey/ext0";

const ext0 = createExt0({ llm, db: sqlAdapter({ connection }) }); // built once, reused

createServer(async (req, res) => {
  let body = "";
  for await (const chunk of req) body += chunk;
  const { request } = JSON.parse(body);

  const { file, meta, summary, query } = await ext0.generateReport(request);
  if (!file) {
    res.writeHead(422, { "content-type": "application/json" });
    return res.end(JSON.stringify({ error: meta.blockedBy, summary, query, meta }));
  }
  res.writeHead(200, {
    "content-type": "text/csv; charset=utf-8",
    "content-disposition": `attachment; filename="${basename(file)}"`,
  });
  createReadStream(file)
    .pipe(res)
    .on("finish", () => unlink(file, () => {})); // the download is the delivery
}).listen(3000);

Configuration reference

| Key | Default | Meaning | |---|---|---| | llm | required | { provider, model, apiKey } — anthropic, gemini, or an OpenAI-compatible provider (openai, groq, openrouter, mistral, deepseek, xai, ollama); or your own (system, user) => Promise<string>. Plain fetch, no SDK | | db | required | a QueryTarget adapter | | allow | "*" | "*", or { table: "*" \| ["col", ...] }, tables/collections and columns readable | | deny | none | inverse of allow: { table: "*" \| ["col", ...] } hides whole tables/collections or single columns; mutually exclusive with allow | | rowLimit | 50000 | forced cap on returned rows, value tunable, existence not | | maxRetries | 1 | regenerations after a rejection | | maxConfirmRounds | 3 | edit rounds per confirm session | | sessionTtlMs | 600000 | confirm session lifetime | | guard1 | true | the watchdog LLM call, disable to save one call per report | | explainThreshold | unset | max full-table scans allowed in the plan, unset means validity check only | | prompt | built-in | full writer system-prompt override, {{schema}} substituted | | rules | none | house rules appended to the prompt ("fiscal year starts in February") | | fieldMap | none | form-field to column mapping | | onLog | stdout | per-report log sink | | outDir | "reports" | where CSVs are written | | reportTtlMs | unset | sweep our own report-*.csv older than this on each report; off by default | | schemaCacheMs | unset | reuse the introspected schema for this long instead of re-reading it every request |

Field mapping

Your app's form has fixed field names and users phrase requests with them. fieldMap binds each form field to exactly one column. One-to-one, no synonyms, duplicates rejected at load.

fieldMap: { "Customer Name": "customers.name", "Revenue": "v_finance.revenue" }
// or a CSV the business maintains in Excel:
fieldMap: "./fields.csv"   // header: field,column

The mapping is injected into the writer's context, filtered to allowlisted tables, and result headers are renamed back to the form field names in plain code. The CSV says Revenue, not revenue_recognized. Aggregated and derived columns are covered too: the writer labels every output column with a human-readable name, so no raw column name reaches a header. If a field could come from several sources, map it to the one canonical column, or allowlist only the curated view so the ambiguity never reaches the model.

Database adapters

The pipeline talks to data through a four-method interface. Adding a database means one file.

interface QueryTarget {
  describeSchema(): string | Promise<string>;                    // context for the writer
  validate(query, { allow, rowLimit }): ValidationResult;        // the final gate, pure code
  explain(query, { threshold? }): ExplainResult | Promise<...>;  // DB dry-run
  run(query): AsyncIterable<Row> | Promise<AsyncIterable<Row>>;  // read-only, streamed row by row
}

| Adapter | Query form | Gate enforces | EXPLAIN | Introspection | |---|---|---|---|---| | sqlAdapter (sqlite) | SQL, parsed to AST | single SELECT, allowlist across subquery/UNION/JOIN/CTE, column rules with alias resolution, LIMIT forced into the AST | EXPLAIN QUERY PLAN, SCAN count | sqlite_master, tables and views, value hints | | sqlAdapter (postgres) | same | same, parsed with the pg grammar | EXPLAIN (FORMAT JSON), Seq Scan count | information_schema | | sqlAdapter (mysql) | same | same, parsed with the mysql grammar | EXPLAIN, type=ALL count | information_schema | | mongoAdapter | JSON description, never code | find XOR aggregate, $where/$function/$accumulator/$out/$merge banned at any depth, $lookup/$unionWith sources allowlisted, limit forced on both shapes, restricted collections require an inclusion projection | cursor explain, COLLSCAN count | collection sampling |

Drivers are never bundled. Drivers with a sqlite-like shape pass a connection. PostgreSQL and MySQL pass one async function wrapped around your own driver:

sqlAdapter({ dialect: "postgres", run: async (sql) => (await pool.query(sql)).rows });
sqlAdapter({ dialect: "mysql",    run: async (sql) => (await conn.query(sql))[0] });

The PostgreSQL and MySQL paths are currently verified against recorded driver output rather than live servers. The MongoDB adapter is verified against a live Atlas cluster.

Multi-step requests

Several related questions in one prompt still produce ONE statement. The writer stages the work with CTEs on SQL or pipeline stages on Mongo and returns one combined result, and the gate rejects multiple statements regardless of intent.

"revenue per region and each region's best-selling product"
-> WITH region_revenue AS (...), product_sales AS (... ROW_NUMBER() ...) SELECT ...
-> one CSV

Error contract

Two channels by design.

| Channel | How it surfaces | Examples | UX handling | |---|---|---|---| | Refusal, the query was the problem | resolves normally, file absent, reason in meta.blockedBy | allowlist violation, over-broad plan, watchdog rejection | show blockedBy and summary, ask the user to narrow or rephrase | | Exception, rephrasing cannot fix it | throws, the message says what to do | expired session, edit cap, LLM or DB down, config errors at startup | map "expired" and "rephrase" to friendly prompts, everything else to "temporarily unavailable" |

Refusal strings are written to be shown to people: "table employees is not in the allowlist", "query plan needs 3 full table scans (allowed: 1), narrow the query".

Observability

Every report emits exactly one structured line, success and refusal alike:

[ext0] {"time":"...","ok":true,"request":"...","query":"...","rows":49,"retries":0,"ms":4880}

The default sink is stdout, which Cloud Run and similar platforms capture natively, making retention a platform setting. Pass onLog: (entry) => ... to route entries anywhere. A throwing sink never breaks a report.

Benchmark

A committed 18-case set (natural-language request plus gold query), scored as Execution Accuracy (the Spider/BIRD EX metric): predicted and gold result sets must match, so differently phrased but correct SQL passes. Safety is a separate deterministic suite fed straight to the gate with no LLM in the loop.

| model (temp 0) | cases | execution | result match (EX) | safety | p50 / p95 | |---|---|---|---|---|---| | claude-haiku-4-5 | 18 | 100% | 72.2% | 24/24 | 5.6s / 15.8s | | claude-haiku-4-5, after prompt fix | 18 | 100% | 100% | 24/24 | 4.2s / 5.3s |

The first run's misses shared one cause, a prompt rule that added an identifier column the request did not ask for. One line fixed it. Both rows stay in BENCHMARKS.md because the number should be re-earned, not curated. The set is small and grows each version. pnpm bench reruns it.

The offline test suite holds 181 cases: SQL gate attacks (destructive statements, statement smuggling, PRAGMA/ATTACH/VACUUM escapes, allowlist escape via subquery/UNION/JOIN/CTE, limit stripping), Mongo gate attacks ($where injection, $out and $merge, lookup escapes, projection rules), dialect cases, access scoping, and the pipeline, confirm, session, log, streaming and field-map suites. A query that fails the gate never reaches a connection, in tests included.

Development

pnpm i && pnpm seed
pnpm test                 # offline suites, accuracy needs an API key
pnpm demo "products under 50 euros with name and price"
pnpm mongo-demo "total order value by status"
pnpm bench                # EX benchmark
pnpm bench:safety         # 24-attack gate suite
pnpm lint
pnpm build

License

MIT