An Agent That Understands Database Structure

Editorial: An Agent That Understands Database Structure

Why Is Database-Native Function Generation Difficult?

Challenge 1: One-to-many mappings.

A SQL-level function, such as date_trunc('hour', timestamp), is not represented by a single piece of code inside the database kernel. It is a combination of multiple underlying function units. PostgreSQL's date_trunc(), for example, requires timestamptz_trunc for timestamptz inputs, interval_trunc for interval inputs, and timestamptz_trunc_zone for time-zone-aware timestamptz truncation. These units are also scattered across files, and their names differ entirely from the SQL-level function name.

Challenge 2: Dependency hell.

A function unit must reference substantial amounts of existing database-kernel code, including macros, internal functions, and data structures. Implementing date_trunc() without reusing referenced units requires more than 6,000 lines of code; with reuse, it needs only about 315. The agent must therefore identify the correct references precisely.

Challenge 3: Functions differ in complexity.

Mathematical functions may require only a thin wrapper, while aggregate functions require custom aggregation logic and complex management of function units. A single workflow cannot handle every case equally well.

What Is Wrong with Existing Approaches?

• Direct LLM generation: Asking GPT or Claude to generate a function directly can produce outdated macros or references to nonexistent APIs because the model's training data mixes different contexts. This approach also treats functions of different complexity alike.
• Retrieval-augmented generation (RAG): Providing additional function-unit context helps the model identify references, but it does not guarantee that the generated code will integrate correctly with the database. Logical hallucinations can persist even with context.
• Agent-based methods: Agents spend substantial time traversing repositories. Different databases store function declarations in different file formats: PostgreSQL uses pg_proc.dat, while SQLite uses src/func.c. Agents repeatedly search from scratch rather than reuse prior experience.

Existing approaches are either too uninformed about database structure, too general-purpose to navigate repositories efficiently, or too shallow to ensure correct integration. DBCooker's central insight is to first teach the system the database's structure through feature extraction, then provide scaffolding through planning, fill-in-the-blank synthesis, and validation. The LLM can then focus on generating high-quality code within explicit constraints.

1. Research Background and Problem

1.1 What Is a Database-Native Function?

A database-native function is embedded directly in the database kernel and exposed to users through a SQL interface. The source contrasts this with a user-defined function (UDF) defined at the SQL level: native functions are implemented in C/C++ within the kernel and deeply integrated into the database's compilation and execution system.

Formally, a database-native function $f$ can be represented as:

Here, $f_{dec}$ is the SQL-level function declaration, $f_{new\_unit}$ denotes new function units to implement, and $f_{ref\_unit}$ denotes existing referenced units on which they depend, such as internal macros and helper functions.

1.2 The Complexity of Manual Implementation: date_trunc()

Implementing a native function requires more than writing its logic. Developers must also handle:

• Registration: Correctly declare function units in the system catalog, including parameter types, return types, and mappings to internal functions.
• Placement: Locate the correct source files for the implementation.
• References: Correctly use dozens or even hundreds of internal macros and helper functions. For date_trunc(), a from-scratch implementation without reuse requires 6,235 lines of code and 225 functions. Reusing existing units reduces this to about 315 lines, a 94.95% reduction.

In PostgreSQL, following only two dependency hops reaches 119,161 lines of related code. DuckDB's GitHub repository has 3,791 function-related issues. This complexity exceeds what can sustainably be managed by hand.

1.3 Three Limitations of Existing LLM Approaches: O1-O3

O1. Databases Have High Reference Density, but Native Functions Have Low Reference Density

As Figure 2(a) shows, database repositories contain enormous numbers of file and function references, but native functions depend on only a small fraction of them. Across the three evaluated database repositories, the average numbers of references per file are 2,619.56, 1,594.65, and 872.32, respectively. The corresponding averages per function unit are only 13.73, 47.58, and 33.11. This high repository-level reference density makes precise identification of the correct referenced functions essential during synthesis.

O2. General-Purpose Agent Frameworks Pay Limited Attention to Database-Specific Operations

Most existing code-synthesis frameworks, including advanced agent-based systems, are general-purpose tools designed for many kinds of repositories. They often overlook structural characteristics of database projects, such as function definitions in PostgreSQL's pg_proc.dat, which makes database-native function synthesis inefficient.

As Figure 2(b) shows, Claude Code spends most of its time on file-level search: 63.70% on repository searches and file reads, compared with only 4.95% on file updates that implement function logic. This imbalance highlights excessive repository traversal at the expense of code construction and validation. A database-aware synthesis strategy is needed to navigate repository structure efficiently while balancing generation and correctness.

O3. Declaration Errors Are the Most Frequent Errors in Database-Function Synthesis

The analysis examines the types and frequencies of errors produced by a leading language model, Claude Sonnet 4.5, and an agent framework, Claude Code, when synthesizing PostgreSQL functions. As Figure 2(c) shows, both approaches, especially the standalone language model, produce many declaration-related errors. Examples include duplicate declarations within the same file, such as redefining int4div in div, and incorrect function references with mismatched arguments, such as passing too few arguments to pg_regcomp in regexp_matches.

On average, declaration errors account for 81.76% of all errors across the two approaches. Test-case errors also occur when generated functions fail to produce the expected output. These findings motivate improved module design to reduce declaration problems, unresolved references, and test failures during synthesis.

2. DBCooker System Overview

2.1 Function Characterization

Database code is complex, and passing it directly to an AI model can induce hallucinations. DBCooker processes the code through three modules:

1. Function Declaration Collection: Collect function definitions from documentation and system catalogs.
2. Distinctive Function Unit Identification: Use graph-based unit extraction to remove irrelevant code and retain core logical units.
3. Cross-Unit Reference Analysis: Retrieve complete definitions for macros and types, and declarations for ordinary function references.

2.2 Function Synthesis Operations

Built on the extracted function characteristics, this module provides specialized synthesis operations to support database correctness:

1. Pseudocode-Based Coding Plan Generation: Use function metadata and the database hierarchy as inputs. Match metadata from similar functions to identify relevant referenced units.
2. Progressive Code Synthesis: Retrieve matching templates based on declaration type. When synthesis fails, use a probabilistic adaptation mechanism to adjust the synthesis process and generate code through either template completion or more flexible exploration.
3. Three-Stage Code Validation: Check syntax with a parser to ensure that the code is parseable for the target database; use native database build tools to verify compliance with internal conventions, including registration consistency; and strengthen the test suite with automatically generated semantic tests covering input types and boundary cases.

2.3 Adaptive Function Synthesis

An LLM-based controller uses context-aware reasoning to select the next tool adaptively. A global memory of historical workflows guides and accelerates the synthesis of similar functions.

3. Module Details

3.1 Function Declaration Collection

Documentation-Based Function Collection

To capture the SQL-level semantics of native functions, DBCooker automatically parses and analyzes official database documentation to collect textual function declarations. It examines the documentation hierarchy to locate sections describing native functions, then uses automated scripts to parse and normalize the extracted content into a unified JSON representation.

For example, from PostgreSQL's Chapter 9, Functions and Operators, DBCooker extracts date_trunc, its description, "truncate a timestamp to a specified precision," and examples such as:

date_trunc('hour', timestamp '2001-02-16 20:38:40')
-- Result: 2001-02-16 20:00:00

Catalog-Based Declaration Extraction

To capture implementation-level specifications precisely, DBCooker extracts authoritative code-level declarations from internal system catalogs and registration files, such as PostgreSQL's pg_proc table and pg_proc.dat file. It queries and consolidates catalog entries, systematically retrieving key attributes such as input arguments and return types, then normalizes them into the same JSON format.

For example, querying PostgreSQL's system catalog for date_trunc returns an entry with two input arguments, proargtypes = text timestamptz, and a timestamptz result, prorettype = timestamptz.

3.2 Distinctive Function Unit Identification

To follow the implicit conventions involved in synthesizing new database functions, DBCooker uses two modules to identify the essential function units $f_{unit}$ that must be implemented across multiple files.

Graph-Based Unit Extraction

To uncover the implementation beneath an encapsulated SQL function interface, DBCooker systematically identifies all relevant function units $f_{unit}$ through graph-based extraction.

For a native database function $f$ with SQL-level declaration $f_{dec}$, it first performs keyword-based retrieval to locate the corresponding function entry and its registration unit $f^{reg}_{unit}$. Starting from that registration unit, automated static analysis constructs a reference graph:

Here, $V_f$ is the set of function units, and $\varepsilon_f$ encodes references through function calls or class-inheritance relationships. The graph expands recursively until no additional units are found or the number of referenced units exceeds a predefined threshold.

In Figure 4(a), DBCooker starts with the SQL keyword date_part in DuckDB to locate the registration entry in function_list.cpp. Reference traversal then identifies key units such as DatePartFun in date_functions.hpp. It excludes function_set.hpp because the associated component, ScalarFunctionSet, is a broadly reused reference unit across scalar functions.

Pairwise Unit Pruning

To identify common code patterns across function units and support synthesis of similar native functions, DBCooker derives stable, pruned patterns from the extracted unit set.

1. Group by function declaration. First, group the extracted units $f_{unit}$ by their SQL-level declarations $f_{dec}$, considering input and output types as well as functional categories. For example, timestamptz_part and extract_timestamptz belong to the same group because both are date/time functions that accept timestamp arguments.

2. Compare content pairwise. Randomly select pairs within each group and compare them along call paths in $G_f = (V_f, \varepsilon_f)$. For a pair $f^{(a)}_{unit}$ and $f^{(b)}_{unit}$, normalize the code by abstracting identifiers such as variable names. Exact keyword matching then extracts shared pruned code blocks:

Replace the remaining nonshared blocks with placeholders that represent variation across implementations.

3. Refine over multiple rounds. Perform multiple pairwise comparisons within each group to identify representative pruned units robustly, with the number of rounds bounded by group size. The source gives the following stopping condition when the proportion of pruned components along a reference path decreases relative to round $t-1$:

In Figure 4(b), DBCooker first categorizes date_sub() under Time Functions based on its declaration. It then randomly selects date_sub() and date_part() from the group for comparison. Following their reference paths, it prunes units to derive DUCKDB_SCALAR_FUNCTION_SET(...) in function_list.cpp, stopping at date_functions.hpp when the pruned units become substantially smaller.

Further rounds compare other pairs, such as date_sub() and date_diff(), to extract diverse pruned units robustly. Finally, the top $k$ most frequently occurring units $f^{pruned}_{unit}$ are selected as representative units, capturing stable, reusable structure for the fill-in-the-blank synthesis model referenced as Section 5.2 in the underlying paper.

3.3 Cross-Unit Reference Analysis

Cross-unit reference analysis systematically captures dependencies among function units in complex database repositories, extracting referenced units $f_{unit}^{ref}$ for each native function $f$.

1. Static dependency extraction. Given the reference graph $\mathcal{G}_f = (\mathcal{V}_f, \mathcal{E}_f)$, iteratively apply automated static analysis along graph paths to identify the referenced units $\{f_{unit}^{ref}\}$ of every $f_{unit} \in \mathcal{V}_f$. Examples include the assertion reference D_ASSERT and the executor reference BinaryExecutor within DatePartFunction.

2. Type-specific pruning. Including all referenced units in full, such as entire class definitions, introduces redundancy and excessive detail. Predefined type-specific pruning rules retain only necessary context while preserving core structural elements. For example, retain the declaration list of ScalarFunctionSet, but omit detailed method implementations such as GetFunctionByArguments.

3. Adaptive expansion. The resulting reference set provides an initial representation of $f_{unit}^{ref}$ that can grow during synthesis. Retrieve additional content on demand through automated static analysis, such as the full implementation of GetFunctionByArguments.

4. Synthesis Operations

4.1 Pseudocode Plan Generation

A pseudocode plan is a structured implementation skeleton that DBCooker constructs before generating code. It determines what to write, where to write it, and which references are needed, turning unconstrained generation into guided synthesis.

Plan components. Each plan explicitly includes three kinds of information:

1. Decomposed function units and their destination files: Specific .c or .cpp files and insertion locations within the database source tree.
2. Natural-language descriptions of each code block: High-level descriptions of inputs, processing, and outputs, avoiding premature commitment to C syntax details.
3. Potentially required reference units for each block: Internal macros such as PG_GETARG_TEXT_PP, helper functions such as pg_database_encoding_max_length, and type definitions.

Multiple candidates and deterministic selection. The LLM is prompted to generate several candidate plans in parallel. A single candidate is unreliable: it may invent a reference such as PG_GETARG_TXT() or omit a necessary branch, such as time truncation for interval inputs.

DBCooker filters plans using a deterministic scoring function with three normalized components:

The normalized scores are combined as a weighted sum. Plans below a threshold, such as 0.5, are discarded. Rule-based scoring is more reliable than LLM self-evaluation, which can suffer from uncertainty and self-preference bias. It objectively evaluates structural fidelity and complexity.

4.2 Progressive Code Synthesis

After selecting a plan, DBCooker begins code synthesis. A fill-in-the-blank synthesis model works with a probabilistic adaptation mechanism, combining optimistic reuse with a conservative fallback.

Fill-in-the-blank workflow. Using function metadata, such as mathematical, date, string, or JSON categories, the system retrieves existing functions of the same type. Pairwise pruning from function characterization yields representative units whose structural skeletons are retained, including registration macros such as DUCKDB_SCALAR_FUNCTION_SET(...) and argument-unpacking patterns. Function-specific logic is replaced with placeholders.

The LLM fills those placeholders instead of regenerating reusable boilerplate for registration, argument-type handling, and return-value packaging. For PostgreSQL string functions, for example, a template supplies PG_GETARG_TEXT_PP argument unpacking and PG_RETURN_TEXT_P return packaging, leaving the model to generate only the string-transformation logic. Multiple candidates are combined through self-consistency, such as majority voting or semantic consolidation, to obtain the final result.

Probabilistic adaptation. Templates are not always suitable. If a retrieved template contains an incorrect reference path or an incompatible structure, repeatedly using it only causes repeated failures. DBCooker therefore applies a probability-decay strategy:

• Start with P₀ = 1, relying entirely on template-based completion.
• After each failed synthesis attempt, decrease the probability exponentially as P(n) = αⁿ, where α is a decay factor. This gradually reduces template dependence and increases from-scratch exploration.
• The source describes a full fallback to explore-from-scratch mode when P reaches 0: the LLM generates the complete implementation under the plan's guidance without a prefilled template.

This design dynamically balances reuse and fallback. Templates reduce generation complexity and improve the chance of first-attempt success, while fallback avoids endless retries when the template itself is unsuitable.

4.3 Three-Stage Progressive Validation

Correctness requirements for database-native functions are deterministic: the code must compile, the function must be registered correctly in the system catalog, and runtime outputs must match expectations. Probabilistic LLM generation cannot inherently guarantee these conditions. DBCooker therefore validates progressively, from local to global and from shallow to deep checks.

Stage 1: Syntax-level validation. Use a parser such as ANTLR for basic lexical and syntactic checks on each synthesized file. The source describes this stage as checking declaration validity, references, and parseability. It filters obvious errors such as unmatched parentheses and missing semicolons, but cannot detect cross-file dependencies or semantic problems.

Stage 2: Compliance-level validation. Use native database build tools, such as PostgreSQL's make install, to compile the synthesized code within a configured timeout. This verifies not only compilability but also registration consistency, such as whether pg_proc.dat declarations match implementation signatures in .c files, and cross-file dependency correctness, including whether external macros and functions exist and can be linked. Missing registration entries and signature mismatches that escape single-file syntax checks become visible here.

Stage 3: Semantic-level validation. Use an LLM to generate SQL tests covering varied input types and boundary cases, execute the synthesized functions, and compare actual with expected outputs. Test generation combines three sources:

1. Expert instructions: Direct attention to error-prone areas such as type boundaries, NULL handling, and precision.
2. Existing test suites: Provide examples of test formats and assertion styles.
3. Decomposed internal code blocks: Guide tests toward the specific newly implemented logic.

This stage can detect deep semantic errors that survive parsing and compilation, such as precision loss caused by using int64 where double is needed.

Key ablation result. Removing validation causes PostgreSQL compliance accuracy to fall from 78.62% to 6.9%, the steepest decline among the ablation variants. This reflects PostgreSQL's strong dependence on validation feedback because of its many cross-file dependencies and execution branches. Without layered feedback, the LLM relies on pretrained knowledge and can hallucinate macros or internal function names, or miss type-related semantic errors. Syntax and compliance checks also protect the semantic-testing stage: expensive SQL execution is worthwhile only after the code passes the first two stages.

5. Adaptive Tool Orchestration

5.1 Motivation

Database-function categories differ greatly in implementation complexity and therefore require different synthesis strategies.

Mathematical functions, such as sqrt() and ceil(), are often thin wrappers around C standard-library functions. Registration macros invoke the corresponding libc functions with little custom logic or cross-unit referencing. A simple code-generation-to-compilation workflow may suffice.

Aggregate functions, such as json_agg() and covar_pop(), are quite different. They maintain internal state, such as accumulators or JSON buffers; define transition and finalization functions; and register multiple callbacks correctly with the aggregation framework. These requirements involve substantial cross-unit references and complex state management.

A fixed code-to-test pipeline is insufficient for aggregates. An agent may need to generate a pseudocode plan before coding to clarify the state machine, reference aggregation-specific macros such as WAGGREGATE rather than the scalar-function FUNCTION, and iteratively validate and repair the implementation.

The central motivation is that no single synthesis strategy fits every function category. The system should dynamically orchestrate operations according to complexity. It can skip pseudocode planning and semantic validation for simple mathematical functions to reduce cost, while using a complete plan → code → syntax validation → compliance validation → semantic validation → iterative repair cycle for complex aggregates.

5.2 Operations as Tools

DBCooker packages each synthesis operation as a callable tool with a uniform interface. Each tool contains three modules.

Metadata. Define the tool's name, parameters, functional description, and applicable scenarios so that the LLM understands its capabilities and limits. For example, syntax_check checks single-file syntax rather than cross-file dependencies, while compliance_check requires a complete build environment and a timeout.

Core logic. Implement the actual operation, such as invoking ANTLR, running make install, or generating and executing LLM-produced tests. Each tool is an independent function with standardized inputs and outputs, enabling loose coupling and composition.

Optimization strategy. Add quality-improvement mechanisms within each tool. The plan-generation tool uses majority voting to consolidate LLM candidates, while the synthesis tool uses self-reflection to repair generated code based on compiler errors. These mechanisms operate transparently within tools without increasing orchestration complexity.

5.3 Memory-Augmented Stepwise Orchestration

DBCooker does not rely solely on zero-shot LLM decisions. An orchestration memory provides distribution-aware guidance from prior experience, combined with stepwise dynamic tool selection to balance flexibility and stability.

Memory structure. The memory pool M stores information about historically successful syntheses. Each record is a triple (f_dec, C, s):

• f_dec: Declaration metadata, including function name, category, and parameter signature.
• C: The tool-call trace, including tools used and their call counts.
• s: An LLM-generated summary of the synthesis process, highlighting successful steps and obstacles.

Distribution-aware quality control inserts a new trace only when it contributes new statistical information, such as updating a function category's minimum, median, or maximum number of tool calls. This prevents redundant records from expanding the memory pool and slowing retrieval.

Orchestration workflow, corresponding to Algorithm 1 in the paper:

1. Retrieve three reference traces for functions in the same category: those with the minimum, median, and maximum tool-call counts. These provide low-, medium-, and high-complexity precedents.
2. Based on the current context, including declarations, generated code, and the last validation result, and the three traces, the LLM selects the next tool. For example, failed compliance validation may lead to another synthesis call for repair.
3. After execution, the LLM analyzes the result and updates the context. Repeat until synthesis finishes or reaches the step limit.
4. Once the attempt ends, whether successfully or unsuccessfully, generate a trace summary and attempt to insert it into memory for future use.

This strategy combines the LLM's context-aware flexibility, which can handle unfamiliar functions and exceptional situations, with structured guidance from historical traces, which reduces unproductive exploration, such as repeatedly invoking unnecessary checks for simple functions.

The source contrasts this with agent-based approaches such as Claude Code, which lack category-specific historical experience and repeatedly scan the repository from scratch, spending approximately 63.70% of operational time searching and reading unrelated files. DBCooker's memory narrows the useful operation space so that synthesis can proceed with fewer tool calls.

6. SQLite hypot: A Visual Walkthrough of an Agent Conversation

6.1 Task Objective

Implement SQLite's built-in mathematical function hypot(X, Y) = sqrt(X² + Y²). Use the platform's hypot() for numerical stability, follow the existing math2Func convention, handle NULL values and type conversion, and add tests.

6.2 Conversation Timeline

6.3 Key Decision Points

6.4 Final Code Change

--- a/src/func.c
+++ b/src/func.c
@@ -3631,6 +3631,7 @@
     MFUNCTION(atan,              1, atan,      math1Func   ),
     MFUNCTION(atan2,             2, atan2,     math2Func   ),
+    MFUNCTION(hypot,             2, hypot,     math2Func   ),
     MFUNCTION(cos,               1, cos,       math1Func   ),

6.5 Outcome Summary

6.6 Issues and Observations

1. test_subagent loop: It repeatedly reports pre-existing syntax errors in unrelated extension files under ext/fts5/, ext/fts3/, and ext/session/. These errors have nothing to do with the hypot change; the files require the amalgamation compilation context. The agent is forced to repeat checks and explanations, consuming many tokens.
2. Agent performance: The agent correctly identifies the task and chooses the simplest solution: one registration line, reusing existing infrastructure. Its exploration path is clear: search → understand the pattern → modify → build → test.
3. Unfinished work: The task requests "add focused tests," but the agent performs only manual SQL command-line checks and does not create a formal .test file.