Local Agentic Workflow: Natural Language Analysis of 4.4 Million Crash Records

From Questions to Insights: A Schema-Based Agentic Workflow Makes Crash Data Analysis More Accessible

Traffic crash databases are typically large and complex, and transportation agencies rely on them to identify safety priorities, evaluate countermeasures, and allocate resources. However, making the insights in this data easily accessible to frontline practitioners requires flexible, reliable analysis methods. Existing dashboards only support predefined analyses, and more customized needs often require experts familiar with database queries and institutional conventions. A recent study proposed CrashDB-QA, a fully local, database-schema-based agentic workflow designed to analyze crash databases directly via natural language.

Why Is Natural Language Analysis of Crash Data Needed?

Transportation agencies maintain massive volumes of crash records, which are the foundation of safety decisions. But traditionally, non-technical users find it difficult to query these databases directly, because they need to understand the database structure, field encodings, and institution-specific analytical conventions. Natural language interfaces can lower the barrier, but they must ensure that the generated queries are accurate and comply with business norms. CrashDB-QA was designed precisely to address this pain point.

Database Schema is a set of metadata describing the database structure, including table names, field names, field types, primary/foreign key relationships, and constraints. In a crash database, the schema defines how fields such as crash time, location, collision type, and injury severity are organized. For a natural language query system, the schema is the bridge between user questions and SQL queries: the system needs to understand which field and which coded value correspond to "drunk driving crash" in order to generate a correct query. CrashDB-QA uses the schema as its core grounding source, making the generated SQL structurally valid and semantically consistent with the agency's data dictionary.

CrashDB-QA Workflow: Specialized Components Each Doing Their Part

The system contains multiple specialized components: understanding user questions, generating and validating read-only SQL, executing queries, and explaining results. A shared knowledge base anchors query construction in the agency's data dictionary and safety analysis conventions, while recording intermediate outputs to support inspection and auditing. An optional extension, CrashDB-QA+, also checks consistency among the question, query, and results, and provides evidence for iterative repair.

This modular design makes every step traceable, improving both accuracy and system trustworthiness.

Agentic Workflow refers to a system architecture in which multiple specialized components (usually driven by large language models) collaborate according to a predefined process to complete complex tasks. Unlike a single call to a large model, an agentic workflow decomposes the task into stages such as understanding, planning, execution, and validation, where each stage can be handled by a different model or rule module, and intermediate results are passed through shared state. The advantage of this design is that every step is inspectable, auditable, and replaceable, making it especially suitable for professional domains with high requirements for accuracy and traceability. CrashDB-QA adopts exactly this architecture, transforming natural language questions into validated read-only SQL queries.

Read-Only SQL refers to the subset of SQL that contains only query statements such as SELECT, and excludes INSERT, UPDATE, DELETE, or DDL operations. When letting AI systems access production databases, restricting access to read-only is a basic security principle that prevents the model from generating statements that accidentally modify or delete data. CrashDB-QA also validates SQL after generation to ensure the statements comply with read-only constraints and institutional norms, thereby safeguarding data security while granting natural language access.

Experimental Evaluation: How Do Local Models Perform?

The research team evaluated the workflow on a frozen snapshot containing 4.4 million Wisconsin crash records, built a benchmark of 300 questions spanning 10 analytical categories, and tested 9 local open-source models. The results showed that domain knowledge grounding significantly improved query accuracy, and a code-specialized 7B model achieved the best balance between accuracy and latency among the tested models.

Compositional analysis (such as comparisons between groups or time periods) is harder to handle than routine retrieval. The validation mechanism improved performance when the Judge used authoritative field definitions and the Refiner received execution evidence, whereas simply adding more checking steps did not bring consistent benefits. This indicates that effective validation needs to rely on high-quality business knowledge and concrete execution feedback.

Domain grounding refers to introducing authoritative domain knowledge (such as data dictionaries, business rules, field definitions, and analytical conventions) during model generation, so that outputs are anchored in domain facts rather than relying solely on the model's parametric memory. In text-to-SQL tasks, grounding is usually implemented by injecting relevant schema fragments, field descriptions, and example queries into the prompt. This study shows that grounding quality directly determines query accuracy: system performance improved significantly when the validation component used authoritative field definitions and the repair component received actual execution feedback; simply adding more checking steps without introducing high-quality domain knowledge yielded little benefit.

Implications and Limitations

CrashDB-QA demonstrates the potential of locally deployed, domain-grounded workflows for broadening access to crash analysis, and also points out the reasoning and validation capabilities required for reliable use. However, compositional analysis remains difficult, and future work may require more powerful reasoning models or more refined validation strategies. In addition, the study covers data from only one state, so its generalizability needs further verification.

Overall, this research provides a feasible path toward democratizing traffic data analysis, while emphasizing that domain knowledge and validation mechanisms are indispensable when deploying AI assistants in professional domains.

One-line Summary

CrashDB-QA uses a local agentic workflow to enable natural language querying of crash databases, with domain grounding being key.

Paragraph Summary

CrashDB-QA is a fully local, database-schema-based agentic workflow designed to let non-technical users analyze crash databases directly via natural language. The system includes specialized components for question understanding, read-only SQL generation and validation, query execution, and result explanation, and uses a shared knowledge base to anchor query construction in the agency's data dictionary and safety analysis conventions. The study built a 300-question benchmark on 4.4 million Wisconsin crash records, tested 9 local open-source models, and found that domain grounding significantly improves query accuracy, with a code-specialized 7B model achieving the best balance between accuracy and latency. Compositional analysis remains harder than routine retrieval, and validation mechanisms only work when combined with authoritative field definitions and execution evidence; simply adding more checking steps yields no consistent benefit.