Docs

Custom Backends for AI-Powered Grids and Charts

Give the LLM a SQL view of a REST API, a service, or files through an Apache Calcite adapter or a copy in a database.
Note
Preview Feature

This is a preview version of AI integration features. You need to enable it with the feature flag com.vaadin.experimental.aiComponents. Preview versions may lack some planned features, and breaking changes may be introduced in any Vaadin version. We encourage you to try it out and provide feedback in the Forum so that we can make it stable.

The AI-powered Grid and Chart let users explore data in natural language. The LLM reads a schema description and writes SQL. A DatabaseProvider runs the query. When your data lives behind a REST API, in a service, or in files, it may have no SQL interface.

This guide covers two ways to add that interface: an Apache Calcite adapter or a copy of the data in a database. Calcite’s documentation covers adapter configuration and development. This guide focuses on connecting the resulting SQL interface to Vaadin.

Prerequisites

  • An application with an AI-powered Grid or Chart, set up as described on AI-Powered Grid or AI-Powered Chart.

  • The com.vaadin.experimental.aiComponents feature flag enabled: add com.vaadin.experimental.aiComponents=true to src/main/resources/vaadin-featureflags.properties.

  • Access to the backend, through an adapter or a client that can read the data to copy into a database.

Choose an Approach

For a data source with a JDBC driver, use a JDBC provider like the one on the Controllers page. For a backend that runs SQL through its own API, implement DatabaseProvider with that client. Override executeQuery(String sql, QueryPage page) and count(String sql) when it pages or counts in its own way.

For data without a SQL interface, compare the two approaches:

Calcite Adapter Database Copy

Query execution

The adapter sends supported operations to the backend, and Calcite runs the rest

The database runs queries against the copied data

Freshness

Queries read from the backend, subject to any caching in the adapter or client

The data as of the last refresh of the copy

Access rights

Configure the adapter’s backend access for the users whose data it serves

The copy needs its own access controls. A shared copy exposes the same rows to every user who can query it

Several sources

Calcite can expose several sources as tables for queries that join or aggregate their data

Tables that are copied from several sources can be joined in the database

What you maintain

Adapter configuration and a database provider. A custom adapter also requires implementation and maintenance

A scheduled job that fills the copy, and a JDBC provider

Use Calcite when a suitable built-in adapter can query the backend with the required performance and access controls. If none is available, compare the work of building a custom adapter with copying the data into a database. A custom adapter is small when it loads all the rows and lets Calcite do the rest. One that sends filters, sorting, and paging to the backend, as large data requires, takes much more work. A copy suits data that can be refreshed periodically and queried independently of the original backend.

Run the Queries with Calcite

Calcite presents data from an adapter as SQL tables and exposes a JDBC interface. Your DatabaseProvider uses that interface to run the LLM’s queries.

Choose and Configure an Adapter

Start with Calcite’s built-in adapters. These include adapters for MongoDB, Elasticsearch, and Cassandra, as well as a file adapter. Follow the selected adapter’s documentation for its dependencies, connection settings, schema configuration, and supported operations. Calcite’s model reference and JDBC connection reference describe the shared configuration options.

Calcite and Vaadin both depend on the Jackson annotations, in different versions. Spring Boot’s dependency management aligns them. In a project without Spring Boot, check the resolved version with mvn dependency:tree. If it’s the older one from Calcite, Vaadin’s JSON handling fails with a NoClassDefFoundError. In that case, exclude com.fasterxml.jackson.core:jackson-annotations from every Calcite dependency you declare.

The adapter translates the SQL operations it supports into the backend’s own queries, so the backend’s rules apply to them. Not every SQL operation has an exact counterpart in the backend, for example a LIKE pattern or a date comparison. For such an operation, the adapter’s translation decides the result, or the adapter rejects the query. Check which operations the adapter’s documentation lists as supported, and test the kinds of queries that users ask, as described in Check Paging and Query Performance.

For development, follow the Calcite adapter tutorial. For a REST API or a service, the smallest custom adapter is a table that implements ScannableTable and returns the objects from your backend client as rows. Calcite then filters, sorts, and groups all the rows in memory, so this suits data that is small enough to load in full. The same Vaadin integration applies to built-in and custom adapters.

Connect the Adapter to a Database Provider

Adapt the JDBC provider on the Controllers page to use the Calcite connection configured for your adapter. Return the adapter’s SQL schema description from getSchema() and run queries through executeQuery(String sql). The provider returns rows as maps whose keys are the SQL column labels.

Configure the adapter and its backend credentials for read-only access to the data users may query. Follow the Controllers page’s guidance on schema scope and error messages.

Several details matter when the SQL comes from the LLM and the grid:

  • Use a quoting policy compatible with the double quotes that the grid uses for names. Calcite’s lex=MYSQL_ANSI setting also makes unquoted names case-insensitive.

  • LLMs often use SQL keywords such as value or year as column aliases. If your parser configuration allows it, add calcite-babel and select Calcite’s Babel parser to accept more such names.

  • LLMs also group by a column alias or position and write !=, which Calcite rejects by default. Calcite also pads the string literals of a CASE or UNION with spaces to the same length, so such a label no longer equals a column value. The conformance=MYSQL_5 setting accepts that SQL and doesn’t pad the literals.

  • Remove a trailing semicolon before running a query. Calcite rejects it, and the chart passes the LLM’s SQL to executeQuery(String sql) unchanged.

  • Convert JDBC date values to LocalDate and timestamp values to LocalDateTime for the grid’s date renderers.

  • Relay useful SQL parse and validation errors in a ToolException so the LLM can correct its query. Calcite reports them as a SqlParseException or a CalciteContextException in the cause chain of the SQLException. Keep row values and backend details out of those messages, and use a generic message for other failures. The message of the SQLException itself can include the error text of your backend, so don’t relay it as it is.

For example, these connection properties accommodate the names and the SQL that the LLM and the grid may use. Apply them when creating the Calcite connection alongside the adapter’s configuration:

Source code
Java
var properties = new Properties();
properties.setProperty("lex", "MYSQL_ANSI");
properties.setProperty("conformance", "MYSQL_5");
properties.setProperty("parserFactory",
        "org.apache.calcite.sql.parser.babel.SqlBabelParserImpl#FACTORY");

Describe the Schema for the LLM

The text that getSchema() returns is all the LLM knows about your data. Follow the general advice on the Controllers page. Describe the tables and columns exposed by the adapter, their relationships, and any restrictions that affect queries.

Name Apache Calcite as the SQL dialect and use its SQL reference for syntax and types. Tell the LLM how to write date and timestamp literals, for example:

Source code
Java
return """
        Tables:
        products(id INT, name VARCHAR, category VARCHAR, price DECIMAL)
        orders(id INT, product_id INT, quantity INT, ordered_on DATE)
        orders.product_id refers to products.id.
        Dialect: Apache Calcite.
        Compare dates with DATE literals, for example ordered_on >= DATE '2025-01-01'.
        Compare timestamps with TIMESTAMP literals, for example TIMESTAMP '2025-01-01 00:00:00'.
        Put aliases that are SQL keywords in double quotes, for example ordered_on AS "date".
        """;

Even with the Babel parser, some names such as date and order require double quotes. This applies to column names and to aliases chosen by the LLM. Also avoid column names that are SQL functions written without parentheses, such as user or current_date. An unquoted SELECT user FROM tickets returns the function’s value instead of the column. Rename these columns in the exposed schema or tell the LLM to quote them.

Use the Provider

Create the provider in the view and pass it to a GridAIController or a ChartAIController. For example, with the JDBC provider adapted as described above and a DataSource configured for Calcite:

Source code
Java
var provider = new CalciteDatabaseProvider(calciteDataSource);
var controller = new GridAIController(grid, provider);

Attach the controller to the orchestrator as described on AI-Powered Grid and AI-Powered Chart.

A provider created in the view can keep backend credentials and cached data specific to one user. Share a provider between users only when every user may see the same data. If the provider keeps a JDBC connection, keep it transient and recreate it after session deserialization. Ensure the provider’s other fields are serializable or can be restored as well. The provider can be called concurrently, for example when the grid loads a page while the LLM’s next query is validated.

The controller also calls the provider from background threads when it validates the LLM’s SQL. This happens with a streaming LLM provider or background execution. VaadinSession.getCurrent() and UI.getCurrent() return null there, and Spring Security’s SecurityContextHolder has no authentication by default. Capture the user’s credentials when the view creates the provider or its backend client, instead of looking them up during a query.

Check Paging and Query Performance

The grid uses executeQuery(String sql, QueryPage page) and count(String sql) to load rows lazily. Their defaults add sorting, paging, and counting to the SQL before calling executeQuery(String sql).

Check that the adapter pushes the grid’s sorting, paging, and counting to the backend. A table that only implements ScannableTable pushes nothing: each query reads all the table’s rows from the backend, so cache them for a short time. Override the provider’s paging and counting methods when the backend can perform them more efficiently.

A grid render can query the backend several times: for a sample row, for the row count, and for each page. The chart calls executeQuery(String sql) for each series query and receives its full result. Queries also run when the LLM proposes them for validation. Account for these repeated calls when choosing an adapter and any caching. Keep caches scoped to the user’s access rights.

Test the provider directly with SQL before involving an LLM. Include joins, date conditions, <> and LIKE conditions, aggregates, sorted and unsorted pages, and counts. Check both the returned rows and the work the backend performs.

Copy the Data into a Database

The application can keep a copy of the data in a database and let the database run the LLM’s SQL. Queries then run independently of the original backend, and the application controls when to refresh the copy. The trade-off is that the grid and the chart show the data as of the last refresh of the copy.

The copy doesn’t carry over the access rights of the backend either. One account reads the backend to fill the copy, and every user of the grid or the chart can query all its rows. Copy only data that all these users may see.

Any database with a JDBC driver can hold the copy, such as a schema of its own in the database the application already uses, or an embedded database.

Fill the copy with a scheduled job that replaces its rows in one transaction, so that the grid and the chart never read a half-filled table.

Query the copy with a JDBC provider like the one on the Controllers page, and describe the tables of the copy in its getSchema(). Give the provider a database account that can only read the copy, as that page advises.

Updated –