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

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.