Text2SQL Deterministic Pipeline + ReAct Dual-Path: Architecture Breakdown of datapulse AI BI Workbench

Introduction: Pain Points of AI BI and the Starting Point of Dual-Path Design

Using LLMs for BI analysis sounds tempting, but the implementation is full of pitfalls. The three most common problems developers encounter are almost identical:

Hallucination. When the database has no data, the LLM will still confidently fabricate a number.

Untrustworthy SQL. Even when a query is generated, it may silently sneak in dangerous statements like INSERT, UPDATE, DROP, or construct multi-statement injections.

Uncontrollable output. The model might embed a command into a JSON field and then "obediently execute" it.

The core design tension of datapulse sits on the axis of determinism vs. flexibility. Data retrieval demands determinism—the same question at the same moment must yield the same result; analytical exploration demands flexibility—allowing multi-turn conversations and letting users try different queries. Mixing these two paths often satisfies neither: the deterministic pipeline gets slowed down by ReAct's casualness, and the agent gets constrained by tedious validation logic.

So datapulse chooses to separate them, each taking its own path, and uses a lightweight router to glue them together.

Design Decision 1: Deterministic Text2SQL Pipeline vs. ReAct Agent

Our initial approach was a unified ReAct agent: throw all questions into it and let the model decide whether to call the run_sql tool. The result quickly exposed two problems.

First, speed. Analytical questions only need a single SELECT, but ReAct mode goes through the full cycle of think → call tool → observe result → think again → call again, resulting in unacceptable latency.

Second, reliability. Deterministic data-retrieval tasks placed inside an exploratory loop cause the model to generate unnecessary "thinking" steps, and it may even forget previously retrieved data due to overly long context, repeatedly executing the same query.

So we split them into two paths.

Deterministic pipeline targets data-retrieval questions: when a user asks "what was last month's sales," the system directly generates SQL, executes it, and returns the result—no extra conversation.

ReAct agent targets analytical questions: when a user says "help me look for noteworthy trends in this dataset," the model can call tools multiple times and explore step by step.

The key to this decision lies in routing. router.ts maintains a single analysis-keyword list—questions containing one of 19 open-ended exploration words such as "why", "reason", "suggest", "analyze", "strategy", "optimize", or "predict" go to the ReAct agent; every other retrieval/aggregation question (e.g. "what was last month's sales" or "category ranking last month") defaults to the deterministic pipeline.

The router itself is simple, but it determines how long a query's pipeline will be. Retrieval questions go straight into the deterministic pipeline—short path, low cost, reproducible results; exploration questions enter the ReAct agent, letting the model autonomously decide the number of calls.

Design Decision 2: SELECT-only Dual Guard (Regex + AST)

Handing SQL generation to an LLM makes security the top priority. We tried three approaches.

Regex-only guard. Initially we only implemented a simple regex check:

if (!/^\s*select\b/i.test(sql)) throw new Error('Only SELECT statements are allowed')

It looked fine, but it was quickly bypassed: the LLM sometimes outputs queries starting with comments, like -- please help me query\ndelete from users;. The regex matches from the first non-whitespace character, completely bypassing the guard.

Validation follows the dialect. datapulse supports three data sources, but no single SQL validator fits all of them: SQLite has no mature general-purpose AST library, so we lean on better-sqlite3's prepare to pre-compile statements—queries with invalid columns or misspelled names fail right at compile time; PostgreSQL and MySQL, on the other hand, go through node-sql-parser for real AST parsing that recognizes statement types at the syntax level. Validation goes with the dialect instead of forcing one unified scheme.

Dual guard. On top of that, a layer of hardening works across all dialects.

The first layer is regex, but with comment preprocessing:

const sql = stripLeadingComments(trimmed)
if (!/^\s*select\b/i.test(sql)) throw new Error('Only SELECT statements are allowed')

The stripLeadingComments function recursively strips -- line comments and /* */ block comments, ensuring the type check lands on the actual SQL statement rather than an empty string bypassed by comments.

The second layer is multi-statement injection detection:

if (!/;\s*$/.test(sql) && sql.includes(';')) throw new Error('multiple statements are not allowed')

The core logic of this rule is: a valid SQL query allows at most one semicolon at the end; if a semicolon appears in the middle, it indicates a risk of multi-statement injection, and the query is rejected outright.

Together, the two layers cover the two most typical attack surfaces—"comment bypass" and "multi-statement injection"—while maintaining compatibility with all three dialects.

Design Decision 3: Three-Dialect Real-Time Introspection and Differentiated Caching Strategy

datapulse supports three data sources: SQLite, PostgreSQL, and MySQL. At first we considered a "universal schema cache" approach—caching all table column information and describing it uniformly in a single table. But reality quickly slapped us down.

Users import CSVs, and CSV column names and types are dynamically inferred—they can't be hardcoded in advance. Users' PostgreSQL and MySQL database structures also vary, and schemas change frequently. Maintaining a single static schema to serve three data sources would be too costly and prone to staleness at any time.

So we switched to real-time introspection: before each query, dynamically fetch the schema based on the current data source type. But real-time fetching comes with a cost, so we implemented differentiated caching strategies based on data source characteristics.

SQLite: mtime caching. SQLite is a local file, and schema changes can be detected via file modification time. We use stat().mtimeMs as a version number—if mtime hasn't changed, use the cache; if it has, re-fetch. This approach is nearly zero-cost and sufficiently sensitive to file-level changes.

PostgreSQL / MySQL: TTL caching. Remote databases don't have the concept of "file modification time," so we switch to a 60-second TTL. The cache expires automatically and is repopulated by the next query. 60 seconds is an empirical value: nearly imperceptible to users, and it won't cause a query storm on the database.

Three dialects, two strategies—each with low complexity on its own, but combined they cover all common scenarios.

Design Decision 4: Phased Self-Correction—Generation, Validation, and Answer Each with Independent Retry

The deterministic pipeline isn't as simple as "generate one SQL, execute, return." A robust system must allow failures and recover automatically afterward.

In pipeline.ts, the entire flow is split into three stages, each with independent error-handling capabilities:

• SQL generation stage. The LLM may output invalid SQL. We don't immediately error out to the user; instead, we feed the error back to the model and let it regenerate. This process retries up to a certain number of times until a SQL that passes the guard is generated, or it's determined that the issue can't be fixed.
• SQL execution stage. The database may return syntax errors or permission errors. Similarly, errors are collected and fed back to the generation stage, attempting to regenerate with a corrected prompt.
• Answer generation stage. Even if the query succeeds, the LLM may output an unreadable answer due to overly long context or instruction confusion. finalize.ts has dedicated logic to handle this situation.

The core benefit of phased design is: a failure in one stage won't drag down the entire query. A SQL generation failure won't cause already-retrieved data to be lost, and an answer generation failure won't re-run the query. Each failure is fixed within the smallest possible scope, keeping the overall system stable.

Design Decision 5: ECharts Dashboard Auto-Generation—from LLM Output to Standalone HTML

The data is retrieved; how do we present it? datapulse's choice is: let the LLM generate an ECharts configuration, then render it into a standalone HTML file.

The agent loop in generate.ts is elegantly simple: the model can initiate multiple queries (multi-turn conversation), aggregate the results each time, and finally output a JSON configuration. This JSON describes key information such as chart type, data mapping, and axis labels.

render.ts is responsible for converting these configurations into standalone, runnable HTML pages. Its cleverness lies in being zero-dependency—ECharts is loaded via CDN, requiring no build tools and no package manager. Simply open the HTML file and the chart appears. For an analysis tool that needs to be shared and validated quickly, this is crucial.

The entire flow: LLM outputs JSON config → render.ts inlines rendering → standalone HTML file. Every step is deterministic, with no additional LLM calls, eliminating the possibility of "render-stage hallucination."

The "Imperfect" Design of CSV Import

datapulse supports users uploading CSV files. The system automatically infers the data type of each column and writes to SQLite. This feature has several design trade-offs worth recording.

Type inference is heuristic. By default the system infers each column's type from every row: a column is INTEGER if it parses as an integer, REAL if it parses as a float, otherwise TEXT (sampling only the first N rows is also supported). Heuristic inference means a rare mixed-type column may be bucketed into a looser type, but for typical CSVs the result is correct. We have no intention of introducing a full type-inference engine—the cost far outweighs the benefit.

Writes are single-transaction. The entire CSV file is written in one transaction—either all succeed or all roll back. This is a straightforward trade-off: we sacrifice per-row memory efficiency in exchange for data consistency guarantees. For CSVs in the tens of MB range, this approach is more than sufficient.

These designs aren't the most elegant, but they are "good enough" engineering decisions—solving the right problems at the right time with the right complexity.

Summary: Trade-offs and Insights in Architecture Design

Looking back at datapulse's architecture, three principles are transferable to other projects:

First, deterministic pipelines take priority over flexible agents. If a task can be solved with deterministic steps, don't let it enter a ReAct loop. Deterministic pipelines have clear advantages in speed, cost, and predictability; agents should serve as a supplement, not the default path.

Second, dual guards are the baseline for LLM-written SQL. Regex preprocessing for comments plus multi-statement injection detection—these two layers together cover the most common security risks at minimal cost. AST parsing is more precise, but its engineering complexity is also higher, and in multi-dialect scenarios it becomes a burden rather than an asset.

Third, caching strategies must be differentiated by data source characteristics. No single caching strategy fits all scenarios. SQLite uses mtime; remote databases use TTL—each has clear overhead and correctness trade-offs. Forcing a unified strategy often demands unnecessary compatibility costs.

There is no silver bullet in architecture design, only optimal choices under specific constraints. datapulse's dual-path design, dual guards, and differentiated caching—each is an answer to a specific problem, not a template to be blindly copied.

---

Source Code Navigation

• src/agent/sqlTool.ts — SELECT-only dual guard and read-only query tool implementation
• src/agent/text2sql/pipeline.ts — Deterministic pipeline and phased self-correction
• src/agent/text2sql/router.ts — Retrieval/exploration dual-path router
• src/agent/text2sql/sqlite.ts — SQLite introspection and mtime caching
• src/agent/text2sql/postgres.ts — PostgreSQL introspection and TTL caching
• src/agent/text2sql/mysql.ts — MySQL introspection and TTL caching
• src/agent/text2sql/generator.ts — SQL generation stage
• src/agent/text2sql/finalize.ts — Answer generation and guardrails
• src/bi/generate.ts — ECharts dashboard agent loop
• src/bi/render.ts — ECharts config to HTML rendering
• src/import/csvImport.ts — CSV import and type inference

Repository: github.com/erishen/datapulse