DEV Community: Jamal Saad

The API Endpoint Explosion: What If AI Agents Could use SQL on User Data Directly?

Jamal Saad — Fri, 14 Aug 2026 13:25:26 +0000

Imagine you're architecting an MCP server for a financial platform.

A customer connects their AI agent and asks:

"What's my current account balance?"

Simple.

You expose an API:

GET /accounts/{accountId}/balance

Then the customer asks:

"Show me my latest 20 transactions."

Another endpoint:

GET /accounts/{accountId}/transactions

Then:

"Which credit cards do I have?"

Another endpoint:

GET /customers/{customerId}/cards

Then:

"Show me every document generated for my accounts during the last six months."

Yet another endpoint.

Then:

"Show me transactions over $500, made using cards expiring this year, together with the account they belong to and any related documents."

Now the design starts falling apart under real-world complexity.

You end up designing bespoke endpoints, forcing the client to stitch together response payloads, or attempting to predict every relational query upfront.

Tomorrow, the query requirement shifts again.

The API Endpoint Explosion

This is how traditional enterprise APIs scale into maintenance nightmares.

You start with a clean core of REST operations.

Before long, you're maintaining hundreds—sometimes thousands—of hyper-specific endpoints.

Something like:

GET /accounts
GET /accounts/{id}
GET /accounts/{id}/balance
GET /accounts/{id}/transactions
GET /accounts/{id}/documents

GET /cards
GET /cards/{id}
GET /cards/{id}/transactions

GET /payments
GET /payments/{id}

GET /transfers
GET /statements
...

But an endpoint isn't just one line.

Here is what that looks like in a typical OpenAPI specification for a single endpoint:

/accounts/{accountId}/transactions:
  get:
    operationId: getAccountTransactions
    summary: Get transactions for an account

    parameters:
      - name: accountId
        in: path
        required: true
        schema:
          type: string

      - name: from
        in: query
        schema:
          type: string
          format: date-time

      - name: to
        in: query
        schema:
          type: string
          format: date-time

      - name: limit
        in: query
        schema:
          type: integer
          default: 50

    responses:
      "200":
        description: "List of transactions"
        content:
          application/json:
            schema:
              type: array
              items:
                $ref: "#/components/schemas/Transaction"

      "400":
        description: "Invalid request"

      "401":
        description: "Authentication required"

      "403":
        description: "Account access denied"

      "404":
        description: "Account not found"

      "500":
        description: "Internal server error"

/accounts/{accountId}/transactions:
  get:
    operationId: getAccountTransactions
    summary: Get transactions for an account

    parameters:
      - name: accountId
        in: path
        required: true
        schema:
          type: string

      - name: from
        in: query
        schema:
          type: string
          format: date-time

      - name: to
        in: query
        schema:
          type: string
          format: date-time

      - name: limit
        in: query
        schema:
          type: integer
          default: 50

    responses:
      "200":
        description: "List of transactions"
        content:
          application/json:
            schema:
              type: array
              items:
                $ref: "#/components/schemas/Transaction"

      "400":
        description: "Invalid request"

      "401":
        description: "Authentication required"

      "403":
        description: "Account access denied"

      "404":
        description: "Account not found"

      "500":
        description: "Internal server error"

And somewhere else:

Transaction:
  type: object
  properties:
    id:
      type: string

    accountId:
      type: string

    amount:
      type: number
      format: decimal

    currency:
      type: string

    merchant:
      type: string

    category:
      type: string

    createdAt:
      type: string
      format: date-time

Transaction:
  type: object
  properties:
    id:
      type: string

    accountId:
      type: string

    amount:
      type: number
      format: decimal

    currency:
      type: string

    merchant:
      type: string

    category:
      type: string

    createdAt:
      type: string
      format: date-time

Multiply this footprint by hundreds of operations. Every single endpoint carries boilerplate overhead: parameter parsing, validation, authentication checks, schema definitions, custom pagination, unit tests, and SDK updates.

200 endpoints
500 endpoints
1,000 endpoints

When AI agents consume these APIs, we end up attempting to pass down an entire static domain model wrapped inside API specifications.

We are effectively describing every action that developers predicted the user might want.

What If the Agent Received the Data Model Instead?

Instead of exposing hundreds of endpoints, what if your interface collapses into two primary capabilities?

get_schema()
query(sql)

get_schema() doesn't return only table and column names.

get_schema() goes beyond column names. It provides structural metadata, keys, indexes, and descriptions so the LLM can reason about relationships directly:

{
  "table": "transactions",
  "description": "Financial transactions belonging to the authenticated user.",

  "columns": [
    {
      "name": "id",
      "type": "UUID",
      "nullable": false,
      "description": "Unique transaction identifier."
    },
    {
      "name": "account_id",
      "type": "UUID",
      "nullable": false,
      "description": "Account on which the transaction occurred."
    },
    {
      "name": "card_id",
      "type": "UUID",
      "nullable": true,
      "description": "Card used for the transaction, when applicable."
    },
    {
      "name": "amount",
      "type": "DECIMAL(18,2)",
      "nullable": false,
      "description": "Transaction amount in the transaction currency."
    },
    {
      "name": "merchant",
      "type": "VARCHAR",
      "nullable": true,
      "description": "Merchant display name."
    },
    {
      "name": "category",
      "type": "ENUM('food', 'transport', 'utilities', 'entertainment', 'shopping')",
      "nullable": true,
      "description": "Normalized transaction category."
    },
    {
      "name": "created_at",
      "type": "TIMESTAMP",
      "nullable": false,
      "description": "Time at which the transaction was recorded."
    }
  ],

  "primary_key": [
    "id"
  ],

  "foreign_keys": [
    {
      "column": "account_id",
      "references": "accounts.id"
    },
    {
      "column": "card_id",
      "references": "cards.id"
    }
  ],

  "indexes": [
    {
      "name": "idx_transactions_account_created",
      "columns": [
        "account_id",
        "created_at"
      ]
    },
    {
      "name": "idx_transactions_card",
      "columns": [
        "card_id"
      ]
    }
  ]
}

With structured metadata across entities (accounts, transactions, cards, documents), the LLM gets exact context on types, foreign keys, and indexes.

This gives the model all the context it needs to construct accurate, performant queries.

SQL Becomes the Agent's Data API

Now the customer asks: "Show me my five largest restaurant transactions this month."

The agent generates:

SELECT
    merchant,
    amount,
    created_at
FROM transactions
WHERE category = 'restaurant'
  AND created_at >= DATE_TRUNC('month', CURRENT_DATE)
ORDER BY amount DESC
LIMIT 5;

Nobody had to create:

GET /transactions/largest-restaurants-this-month

The user invented the question.

The agent translated it.

The database answered it.

The Real Difference Appears When Data Needs to Be Combined

Simple filtering isn't the most interesting example.

Joins are.

Imagine the customer asks:

"Show me every transaction above $500 made with a card expiring this year. Include the account name, card type, merchant, amount and any document generated for that transaction."

With SQL:

SELECT
    a.name AS account_name,
    c.card_type,
    c.expires_at,
    t.merchant,
    t.amount,
    t.created_at,
    d.filename,
    d.document_type
FROM transactions t

JOIN accounts a
    ON a.id = t.account_id

JOIN cards c
    ON c.id = t.card_id

LEFT JOIN documents d
    ON d.transaction_id = t.id

WHERE t.amount > 500
  AND EXTRACT(YEAR FROM c.expires_at)
      = EXTRACT(YEAR FROM CURRENT_DATE)

ORDER BY t.created_at DESC;

To do this with traditional REST services, an agent would have to issue N+1 requests: fetch transactions, query corresponding accounts, look up individual card details, and pull related documents.

GET /transactions?minAmount=500

GET /accounts/{accountId}

GET /cards/{cardId}

GET /transactions/{transactionId}/documents

Instead of re-inventing query engine mechanics through custom HTTP parameters, we leverage SQL directly.

You Cannot Predict Every Join a User Will Want

Human UI workflows are fixed. Agent workflows are dynamic.

Users ask questions that span multiple dimensions:

"Compare monthly spending between my personal and business accounts for the last two years, grouped by category, but exclude transactions that were later refunded."

or:

"Find documents linked to transactions made using cards that have since been cancelled."

or:

"Show merchants where my average transaction value increased by more than 30% compared with last year."

or:

"Find accounts with incoming transfers that were followed by an outgoing payment within 24 hours."

The combinatorial explosion of possible queries makes static endpoint design unviable. Data access and write operations require distinct architectural boundaries.

The Interface Can Become Surprisingly Small

Replacing hundreds of specialized endpoints with a clean data interface:

// DATA INTERFACE
get_schema()
query(sql)

getAccounts()
getAccount()
getBalance()
getTransactions()
getTransactionsByDate()
getTransactionsByCategory()
getCards()
getDocuments()
getDocumentsByTransaction()
getMonthlySpending()
getSpendingByCategory()
...

The separation of concerns becomes clear:

• Schema & Metadata: Defines entity semantics.

• SQL: Expresses arbitrary query intent.

• Database Storage Layer (e.g., KalamDB): Enforces tenant-isolation, security bounds, and execution constraints.

But Giving an AI Database Access Sounds Terrifying

Exposing production database credentials directly to an LLM is a major security risk. That is not the design here.

The agent isn't given unrestricted access to the application's database.

It is given controlled access to the authenticated user's data boundary.

Conceptually:

User
  │
  ▼
AI Agent
  │
  ▼
MCP Server
  │ authenticated user context
  ▼
KalamDB (Tenant Isolation Layer)
  └── Tenant Scope: user_123
          ├── accounts
          ├── transactions
          ├── cards
          └── documents

When:

When user_123 executes a query, execution is locked inside that tenant's boundaries.

SELECT *
FROM transactions;

We do not rely on the LLM to append WHERE user_id = 'user_123'. Tenancy security must be enforced deterministically below the model layer.

Start With Read-Only

Start with a strict read-only model:

get_schema()
query()

No INSERT, UPDATE, or DELETE permissions.

State mutation requires strict business rules, idempotency checks, fraud controls, and transactional guarantees. Read-only query execution provides immense value while minimizing operational risk.

The User Can Build Features You Never Built

Once an agent can safely query the user's data, your application's UI stops being the only interface to that data.

The customer could say:

"Create a pie chart showing where I spent my money this year."

The agent queries:

SELECT
    category,
    SUM(amount) AS total
FROM transactions
WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY category
ORDER BY total DESC;

Then the agent renders the visualization.

Or export custom analytics directly:

"Create an Excel workbook containing all my 2025 transactions, with one sheet per account."

The agent fetches data directly and builds the spreadsheet without requiring custom backend export features.

Data Export Becomes Almost Trivial

Today many applications technically allow customers to export their data.

Standard data export features often force users through clunky batch workflows:

Settings → Privacy → Request Export → Wait → Download ZIP

Settings → Privacy → Request Export → Wait → Download ZIP

An agent backed by a schema-aware query engine renders custom exports trivial. Users can request CSVs, PDFs, or formatted reports on demand.

Now Add Real-Time Data

KalamDB makes another interesting capability possible.

SQL queries can also become subscriptions.

For example:

SELECT
    id,
    account_id,
    merchant,
    amount,
    created_at
FROM transactions
WHERE amount > 1000;

An application could subscribe to matching changes.

Conceptually:

KalamDB
   │
   │ transaction committed
   ▼
SQL Subscription
   │
   ▼
Application / Agent

Rather than polling endpoints continuously, the agent subscribes directly to live data events matching query constraints.

APIs Don't Disappear

REST, RPC, and GraphQL remain essential for state changes. We draw a clear line between Data Access and Transactional Actions.

Reading balance data is a query task. Money transfers, card freezes, and account applications belong in strict RPC/REST tools.

An MCP interface could therefore look like:

// DATA
get_schema()
query(sql)

// ACTIONS
transfer_money(...)
freeze_card(...)
request_new_card(...)

This is the model I find particularly interesting.

SQL handles the long tail of questions.

Explicit APIs handle consequential actions.

Schema + Permissions + SQL

This architectural pattern simplifies the data layer into three components:

• Schema & Metadata: Tells the agent what entities exist, what fields mean, data types, relationships, and index efficiency.

• Permissions: Enforces what data the authenticated user is permitted to query.

• SQL: Expresses how to assemble answers to arbitrary user questions.

That's a very different abstraction from describing hundreds of predetermined endpoints.

From API-First to Data-First Applications

Traditional application stack:

Database → Backend → REST / GraphQL API → Application → User

Data-first agent architecture:

                     ┌── Product UI
                     ├── AI Agent
User Data Layer ─────┼── User scripts
                     ├── Analytics
                     └── User-built applications

The product UI becomes one interface to the user's data.

Not necessarily the only interface.

Why We're Building KalamDB This Way

In KalamDB, we anchor data around tenant-isolated boundaries. When AI agents interact with data, we provide them with structured schema metadata, relationships, tenant permissions, SQL capabilities, and real-time streaming subscriptions.

The Most Interesting API Might Be the One You Never Build

Imagine six months after launching your application a customer asks:

"Can you add an API that returns monthly spending grouped by merchant, excluding refunded transactions, together with the card and account used for each payment?"

Traditionally:

Feature Request → API Design → Implementation → Auth → Tests → Docs → Deployment

Feature Request → API Design → Implementation → Auth → Tests → Docs → SDK Update → Deployment

With a safe SQL query layer over tenant data, the capability already exists. You don't need to build or deploy new endpoints for unexpected queries.

Instead of building APIs around fixed assumptions, we build systems that allow users and AI agents to safely query data in ways we didn't predict.

Check out more at kalamdb.org.

Why modern real-time apps require 5 different systems — and why I built one instead

Jamal Saad — Mon, 09 Mar 2026 10:29:09 +0000

Modern apps are expected to feel live.

Chat apps, AI products, collaborative tools, notifications, activity feeds — they all need fast writes, real-time updates, and storage that does not become expensive as data grows.

But the usual backend stack gets complicated fast.

You store data in one system.
You push live updates with another.
You archive old data somewhere cheaper.
You add extra glue code to keep everything in sync.

That complexity is exactly what pushed me to build KalamDB.

KalamDB is an open-source, SQL-first real-time database designed around a simple idea: keep recent data fast, move older data to cheaper storage, and let developers subscribe to changes directly from the database. The project is explicitly aimed at speed, efficiency, and minimal resource use, with a goal of reducing CPU, memory, storage, and infrastructure cost.

At the core of KalamDB is a two-tier storage model.

New writes first land in RocksDB hot storage, which gives very fast acknowledgments and keeps recent data close for quick reads. Later, that data is flushed into columnar Parquet files in filesystem or object storage, where it becomes much cheaper to keep for the long term. Queries can read across both tiers, so applications get fresh data from hot storage and older history from cold storage without needing separate systems.

KalamDB also supports three table types, and that matters a lot for cost and design.

USER tables isolate data per user and flush each user’s partition into separate Parquet files. That makes them a strong fit for chat history, tenant data, and privacy-sensitive workloads.

SHARED tables store global data once for everyone, which works well for configuration data, reference datasets, and cross-user analytics.

STREAM tables are designed for short‑lived real-time events like typing indicators, presence, AI thinking signals, or notifications. They stay in memory or RocksDB hot storage, use TTL-based eviction, and are never flushed to Parquet.

Another big part of the design is the table-per-user architecture.

Instead of putting all users into one giant shared table and filtering constantly, KalamDB stores each user’s data in isolated partitions. That makes real-time subscriptions simpler and helps the system scale more naturally for workloads like conversations, notifications, and AI history. The idea is to reduce complexity while keeping performance predictable as the number of users grows.

What it feels like to use KalamDB

Instead of constantly polling the database for updates, applications can subscribe to changes directly from SQL.

For example, a chat application can subscribe to new messages in a conversation:

SUBSCRIBE TO chat.messages
WHERE conversation_id = 1
OPTIONS (last_rows = 20);

Whenever a new row is inserted into the table, the client instantly receives the update through a WebSocket connection.

The same idea can be used from application code using the JavaScript SDK:

import { createClient, Auth, SeqId } from 'kalam-link';

const client = createClient({
  url: 'http://localhost:8080',
  authProvider: async () => Auth.jwt('<ACCESS_TOKEN>'),
});

const stopMessages = await client.live(
  `SELECT id, room, role, content, created_at
    FROM chat.messages`,
  (rows) => {
    // `chat.messages` can be a USER table.
    // The same query runs for every signed-in customer, but KalamDB only
    // returns that caller's rows.
    renderRows(rows);
  },
  {
    subscriptionOptions: {
      last_rows: 200,
      from: SeqId.from('7262216745594062848'),
    },
  },
);

await client.query(
  'INSERT INTO chat.messages (room, role, content) VALUES ($1, $2, $3)',
  ['main', 'user', 'hello from the browser']
);

This is the type of pattern KalamDB was designed for: real-time apps where data storage and live updates happen in the same system.

That is the real reason I built KalamDB.

Not to add another database to the world, but to cut down the amount of infrastructure developers have to stitch together just to build something real-time.

Less glue code.
Less storage waste.
Less operational overhead.
And a simpler path from fast writes to cheaper long-term storage.

KalamDB is still in development, and I would describe file storage/BLOB support and high availability as part of the direction, not current production-ready features yet. But the core idea is already clear: a database built to make real-time apps simpler and cheaper to run.

If you want to explore KalamDB:

🌐 Website: kalamdb.org
⭐ GitHub: KalamDB on GitHub
🚀 Quick Start: Run KalamDB locally
🧠 Docs: Learn the SQL syntax