The Data Access Service, Part 1: Giving AI Agents Data Intelligence

AI agents are only as useful as the data they can reach. An agent that can reason brilliantly but cannot access your databases, understand your schema, or respect your governance policies is an agent that stays in the lab. The Data Access Service (DAS) is FireFoundry's answer to this problem: a platform service that gives AI agents intelligent, governed access to your data -- across 12+ database backends, through a single API, with a semantic layer that turns raw schemas into business knowledge.

Code examples in this article are simplified for clarity. See the Query Explainer tutorial for a complete working example.

The Data Problem

Most organizations store data across a sprawl of systems: PostgreSQL for transactional data, Snowflake for analytics, BigQuery for data science, MySQL for legacy applications, Oracle for ERP. When you ask an AI agent to answer a business question -- "Which customers in California have placed more than ten orders this quarter?" -- that agent needs to know which database to connect to, what the schema looks like, what the column names actually mean, and whether it even has permission to run the query.

Today, most teams solve this by hardcoding connection strings, writing one-off SQL adapters, and hoping that the LLM can figure out column names from context. This approach has predictable failure modes:

• Brittle connections: Each database requires its own driver, authentication flow, and connection management. A new backend means new plumbing.
• No semantic context: The LLM sees cust_st and has to guess that it means "customer state." When it guesses wrong, the query returns garbage.
• No governance: If every agent manages its own database connection, who controls what data is accessible? Who audits what queries ran? Who enforces timeouts to prevent runaway queries from taking down the database?
• No performance insight: When a query is slow, the agent has no way to understand why or improve it.

The Data Access Service solves all four problems through a single, unified interface.

What Is the Data Access Service?

DAS is a platform service that sits between your AI agents and your databases. It provides a unified client library that abstracts away backend-specific details and adds layers of intelligence on top: a semantic layer that maps raw schemas to business language, governed access with connection-level permissions and audit trails, and query analysis tools that let agents reason about performance.

Supported backends include PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, BigQuery, Redshift, ClickHouse, DuckDB, SQLite, MariaDB, and CockroachDB -- with more being added. Your agents interact with all of them through the same API.

Getting started takes a few lines of code:

import { DataAccessClient } from '@firefoundry/data-access-client';

const dasClient = new DataAccessClient({
  serviceUrl: process.env.FF_DATA_SERVICE_URL || 'http://localhost:8080',
});

That single client handles connection pooling, authentication, retries, and backend-specific query translation. Your agent code never imports a database driver directly.

The Semantic Layer: Data That Explains Itself

Raw database schemas are designed for machines, not for understanding. A table called ord_ln_itm with columns sku_cd, qty, and unt_prc is perfectly functional for a SQL query, but it gives an AI agent almost nothing to work with. The agent needs to know that this table represents Order Line Items, that sku_cd is the Product SKU, and that unt_prc should be displayed as currency.

DAS solves this with a dictionary layer -- a set of annotations on top of your existing schema that provide business names, descriptions, semantic types, tags, and usage notes for every table and column.

Table-Level Annotations

The dictionaryTables method returns business metadata for every table in a connection:

const tables = await dasClient.dictionaryTables({ connection: 'firekicks' });

// Each entry in tables.tables[] includes:
// - table:        "customers"
// - businessName: "Customer Directory"
// - description:  "All registered customers with contact and geographic info"
// - tags:         ["core", "PII", "customer-360"]

Tags are particularly powerful. An agent building a customer-360 view can filter for tables tagged customer-360. An agent that needs to handle PII carefully can identify those tables up front. The dictionary turns schema discovery from guesswork into structured navigation.

Column-Level Annotations

Column-level annotations go deeper, providing the context an agent needs to write correct queries and interpret results:

const cols = await dasClient.dictionaryColumns({
  connection: 'firekicks',
  table: 'customers',
});

// Each entry in cols.columns[] includes:
// - column:       "cust_st"
// - businessName: "Customer State"
// - description:  "US state abbreviation for the customer's primary address"
// - semanticType: "us_state_code"
// - usageNotes:   "Always a 2-character uppercase code. Filter with = not LIKE."

The semanticType field is what makes this a true ontology layer rather than just documentation. When an agent sees semanticType: "us_state_code", it knows this column contains standardized state abbreviations, not free-text city names or ZIP codes. The usageNotes field provides query-writing guidance that prevents common mistakes -- in this case, telling the agent to use exact match rather than a LIKE pattern.

Together, these annotations mean that an AI agent can look at an unfamiliar database and immediately understand what each table represents, what each column contains, how to query it correctly, and what governance constraints apply. The semantic layer transforms a raw schema into a knowledge-enriched data catalog that agents can navigate programmatically.

Semantic Views: Reshape Data Without Touching the Database

Your data warehouse team will not let you modify their schema? You do not need to. DAS stored definitions let you create virtual views that reshape how the AI sees the data -- without a single DDL statement on the source database. This is the killer feature for organizations where the DBA says "no" to schema changes but the AI team needs clean, business-friendly data.

What Views Do

Stored definitions are virtual database objects -- views, scalar functions (UDFs), and table-valued functions (TVFs) -- that live in the Data Access Service and are expanded at query time. They do not require changes to the underlying database. Here is what they enable:

• Column aliasing with business-friendly names: Rename cryptic abbreviations like oh.ord_id to order_id, oh.cust_id to customer_id, and oh.ord_dt to order_date.
• Computed and derived columns: Add calculations that do not exist in the source, such as total_amount - subtotal AS tax_and_shipping or CASE expressions that decode status codes like 'P' into 'pending'.
• Pre-joined tables: Combine data from multiple source tables into one clean view. Instead of making the AI figure out that order_items.product_id = products.product_id, a single product_performance view encodes the join once.
• Pre-filtered datasets: Bake in business rules. A revenue view can filter on order_status IN ('shipped', 'delivered') so the AI never accidentally counts cancelled orders.
• Pre-aggregated metrics: Roll up total orders, total revenue, and average rating per product -- so the AI queries a flat table instead of writing GROUP BY logic.

Defining a View

Views are defined using SQL submitted to the DAS admin API. The service parses the SQL, converts it to an AST (Abstract Syntax Tree), validates it against the connection's schema, and stores both the original SQL and the AST. Here is an example that transforms a legacy ord_hdr table into a clean order_summary view:

// The DAS admin API creates views from SQL definitions.
// This view transforms cryptic source columns into business-friendly names
// and pre-joins orders with order items for a clean analytics surface.

// Source tables: ord_hdr (order header), ord_ln_itm (order line items)
// Result: A clean "order_summary" view the AI can query directly

// SQL definition submitted to DAS:
// SELECT
//   oh.ord_id      AS order_id,
//   oh.cust_id     AS customer_id,
//   oh.ord_dt      AS order_date,
//   oh.ord_total    AS total_amount,
//   oh.ord_total - oh.subtotal AS tax_and_shipping,
//   CASE oh.ord_stat_cd
//     WHEN 'P' THEN 'pending'
//     WHEN 'S' THEN 'shipped'
//     WHEN 'D' THEN 'delivered'
//     WHEN 'C' THEN 'cancelled'
//   END AS order_status
// FROM ord_hdr oh

// Once created, agents query it like any other table:
const result = await dasClient.query('firekicks', {
  sql: 'SELECT * FROM order_summary WHERE order_status = $1',
  params: ['delivered'],
});

The AI agent never sees ord_hdr, ord_stat_cd, or the CASE expression. It sees a table called order_summary with columns named order_id, customer_id, order_date, total_amount, tax_and_shipping, and order_status. Clean, self-documenting, and queryable with simple SQL.

Namespace Isolation

Views live in namespaces that control visibility across three levels:

• system -- Shared across all callers. These are company-wide standard views that everyone can query. A product_performance scorecard that all agents use belongs here.
• app:{name} -- Visible to agents in a specific application. For example, app:sales-dashboard views are only available to agents running inside the sales dashboard app.
• agent:{id} -- Private to a single agent instance. An agent that discovers a useful query pattern can save it in its own namespace without affecting anyone else.

Different teams can create their own semantic lenses over the same underlying data without stepping on each other. When the service resolves a view name, it checks the agent namespace first, then the app namespace, then falls back to system -- so an agent can override a system view with its own version without affecting other agents.

Parameterized Views (Table-Valued Functions)

Table-valued functions (TVFs) are parameterized views -- views that take arguments. For example, a customer_orders function takes a customer ID parameter, so agents can call SELECT * FROM customer_orders($1) with the customer ID bound at query time. This is more flexible than a static view because the filter is dynamic:

// TVF: customer_orders(cust_id) returns all orders for a given customer
// pre-joined with line-item count and shipping status.
// Agents call it like a table, passing the customer ID as a parameter.

const result = await dasClient.query('firekicks', {
  sql: 'SELECT * FROM customer_orders($1)',
  params: [42],
});

TVFs are particularly useful for multi-tenant scenarios where agents need a reusable query pattern parameterized by the caller's context -- customer ID, region, date range, or any other dimension.

Composability

Views can reference other views, up to 10 levels deep. Build a product_performance view that joins products with order items and reviews; then build a top_products view that filters product_performance for high performers. Each layer adds business logic without touching source tables. The service expands nested views recursively at query time, so the database receives a single, flattened SQL statement.

The key point: the warehouse database receives zero DDL. No CREATE VIEW, no ALTER TABLE, no new indexes. DAS stores the view definitions internally as AST and expands them at query time -- transparently, as subqueries. The source system never knows. Your DBA keeps their pristine schema, and your AI team gets the clean, business-friendly data surface they need.

Query Execution and EXPLAIN Analysis

The core job of DAS is running queries. But DAS does not just execute SQL and return rows -- it gives agents the tools to understand query behavior and performance.

The explainSQL method runs a query with an EXPLAIN plan, returning both the results and detailed performance information:

const result = await dasClient.explainSQL('firekicks', {
  sql: 'SELECT * FROM customers WHERE state = $1',
  params: ['CA'],
  analyze: true,
});

// result.planLines  - The full EXPLAIN ANALYZE output, line by line
// result.sql        - The executed SQL (with parameters bound)
// result.durationMs - Wall-clock execution time
// result.queryId    - Unique ID for audit trail and debugging

Setting analyze: true tells DAS to run the query with EXPLAIN ANALYZE, which executes the query and reports actual row counts and timing for each step of the plan. This is invaluable for AI agents that need to reason about performance -- for example, an agent that detects a sequential scan on a large table and suggests adding an index, or an agent that rewrites a subquery as a join after seeing the optimizer's cost estimates.

The queryId returned with every result ties into FireFoundry's audit trail. Every query executed through DAS is logged with the connection name, the SQL, the parameters, the execution time, and the identity of the agent that ran it. This is not optional -- it is built into the service. If a compliance team needs to know what data an agent accessed last Tuesday, the answer is one API call away.

Schema Introspection

Before an agent can write queries, it needs to know what is in the database. DAS provides direct schema introspection methods that return the structural metadata for any connection:

// Get all tables and their basic schema info
const schema = await dasClient.getSchema('firekicks');

// Get detailed column metadata for a specific table
const columns = await dasClient.getColumns('firekicks', 'customers');
// columns[] includes: name, dataType, nullable, defaultValue, primaryKey, foreignKey

Schema introspection and the semantic dictionary layer are complementary. getSchema and getColumns give you the structural truth -- data types, nullability, keys, constraints. The dictionary layer gives you the business truth -- what those structures mean in human terms. An agent building a query typically uses both: the dictionary to understand intent, and the schema to ensure type correctness.

Error Handling with Typed Errors

Database operations fail. Connections drop, permissions get revoked, queries time out, SQL has syntax errors. DAS provides a typed error hierarchy that lets agents handle each failure mode precisely:

import {
  ConnectionError,
  PermissionDeniedError,
  QueryError,
  TimeoutError,
} from '@firefoundry/data-access-client';

try {
  const result = await dasClient.explainSQL('firekicks', {
    sql: 'SELECT * FROM customers WHERE state = $1',
    params: ['CA'],
    analyze: true,
  });
} catch (err) {
  if (err instanceof TimeoutError) {
    // Query exceeded the connection's configured timeout.
    // The agent might simplify the query or add filters.
  } else if (err instanceof PermissionDeniedError) {
    // The agent's identity does not have access to this connection.
    // Surface this to the user rather than retrying.
  } else if (err instanceof ConnectionError) {
    // The database is unreachable. Retry with backoff or fail gracefully.
  } else if (err instanceof QueryError) {
    // SQL syntax error or invalid parameters.
    // The agent can inspect err.message and attempt to fix the query.
  }
}

Typed errors are not cosmetic. They are what allow an AI agent to recover intelligently rather than failing opaquely. A TimeoutError tells the agent to simplify or paginate. A QueryError tells it to fix the SQL. A PermissionDeniedError tells it to stop retrying and inform the user. Without typed errors, every failure looks the same, and the agent has no basis for choosing a recovery strategy.

Real-World Example: The FireKicks Demo

FireKicks is a sample e-commerce dataset that ships with the FireFoundry developer environment. It models a sneaker marketplace with tables for customers, orders, products, inventory, and shipping -- the kind of schema that every business analyst knows by heart. It is the dataset used in both the Query Explainer tutorial and the AI-Powered Data Science tutorial.

Here is how DAS powers a typical "chat with your data" flow in FireIQ, using the FireKicks dataset:

• Step 1 -- Discover: The agent calls dictionaryTables to learn what tables exist and what they represent. It sees "Customer Directory," "Order History," "Product Catalog" -- not cust, ord, prod.
• Step 2 -- Understand: The user asks "Which states have the most high-value customers?" The agent calls dictionaryColumns on the customers and orders tables to find the relevant columns, learning that cust_st is "Customer State" and ord_total is "Order Total (USD)."
• Step 3 -- Query: The agent writes SQL joining customers and orders, grouping by state and filtering for order totals above a threshold. It runs the query through explainSQL with analyze: true.
• Step 4 -- Analyze: The EXPLAIN plan shows a sequential scan on the orders table. The agent notes this in its response and suggests that an index on ord_total would improve performance for this query pattern.
• Step 5 -- Respond: The agent returns the results to the user with a plain-English explanation of what it found, how it queried the data, and what the performance characteristics were.

This entire flow -- discovery, understanding, querying, analysis, and explanation -- happens through a single DAS client. No direct database connections, no hardcoded schema knowledge, no unaudited queries.

Getting Started

The Data Access Service is available to all FireFoundry beta partners. To start using it:

• Install the client: npm install @firefoundry/data-access-client
• Configure a connection: Register your database in the FireFoundry console with its connection details, permissions, and timeout policies.
• Add semantic annotations: Use the console's dictionary editor to annotate your tables and columns with business names, descriptions, semantic types, and tags.
• Start querying: Instantiate a DataAccessClient and call the methods described in this article. The client handles connection management, authentication, and backend translation.

For a hands-on walkthrough, the Query Explainer tutorial builds a complete agent that uses DAS to explain SQL query performance step by step. The AI-Powered Data Science tutorial shows how to combine DAS with a code sandbox to let agents run data analysis pipelines against live databases.

The Data Access Service is the data backbone of the FireFoundry platform. Every time an AI agent needs to touch a database -- whether it is answering a question, building a report, or analyzing a dataset -- DAS is the service that makes that interaction intelligent, governed, and observable. Your data is already there. DAS gives your agents the intelligence to use it.

Ready to connect your data? If you are already in the beta, configure a connection in the console and start querying today. If you are not yet in the beta, request access and we will get you set up.