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

A developer argues that traditional REST APIs are becoming a maintenance nightmare for AI agents, proposing that agents should be able to query user data directly via SQL. The post illustrates the complexity of maintaining hundreds of hyper-specific endpoints and suggests a shift toward more flexible data access for AI systems.

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. 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. 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. 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. 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. 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. 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. 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 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. 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. 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. 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. 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. 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. 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. 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. 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 https://kalamdb.org .