Building an AI Agent That Queries Operational Data (Not Just Chats)

es';

export class TransactionRetriever { private dbClient: DatabaseClient;

constructor(dbClient: DatabaseClient) { this.dbClient = dbClient; }

async execute(tenantId: string, filters: { accountId?: string; dateRange?: { start: Date; end: Date }; status?: string; }): Promise<QueryResult<TransactionRecord>> { // 1. Validate inputs to prevent injection or malformed ranges if (filters.dateRange && filters.dateRange.start > filters.dateRange.end) { throw new Error('Invalid date range: start must precede end.'); }

// 2. Enforce tenant isolation at the query level
const query = this.dbClient.table('transactions')
  .where('tenant_id', tenantId)
  .where('account_id', filters.accountId)
  .whereBetween('created_at', [filters.dateRange?.start, filters.dateRange?.end])
  .where('status', filters.status)
  .select('id', 'amount', 'status', 'created_at');

const records = await query.execute();

return {
  data: records,
  metadata: {
    count: records.length,
    executionTimeMs: 0, // Calculated in wrapper
    tenantId
  }
};

} }

```typescript
// builders/AttributionEngine.ts
export class AttributionEngine {
  async calculateInfluencedRevenue(tenantId: string, dealId: string): Promise<number> {
    // Complex joins across orders, CRM links, and snapshots
    // Encapsulated logic ensures consistent calculation rules
    const result = await this.dbClient.raw(`
      SELECT SUM(o.amount) as total
      FROM orders o
      JOIN deal_attribution da ON o.id = da.order_id
      WHERE da.deal_id = ? AND o.tenant_id = ?
    `, [dealId, tenantId]);

    return result[0]?.total || 0;
  }
}

2. The Agent Loop and Tool Registration

The agent maintains a registry of available tools. When a user query arrives, the model analyzes the intent and selects the appropriate tool with parameters.

// agent/OperationalAgent.ts
export class OperationalAgent {
  private tools: Map<string, any>;
  private provider: LLMProvider;

  constructor(provider: LLMProvider) {
    this.provider = provider;
    this.tools = new Map();
  }

  registerTool(name: string, instance: any) {
    this.tools.set(name, instance);
  }

  async processQuery(tenantId: string, userMessage: string, context?: PageContext): Promise<string> {
    // 1. Inject context into system prompt
    const systemPrompt = this.buildSystemPrompt(context);

    // 2. Request tool call from provider
    const response = await this.provider.chat({
      model: this.provider.getModel(),
      messages: [
        { role: 'system', content: systemPrompt },
        { role: 'user', content: userMessage }
      ],
      tools: this.getToolDefinitions()
    });

    if (response.toolCall) {
      // 3. Execute tool safely
      const toolInstance = this.tools.get(response.toolCall.name);
      if (!toolInstance) {
        throw new Error(`Unknown tool: ${response.toolCall.name}`);
      }

      // Tools are read-only by design; write operations require separate approval flow
      const result = await toolInstance.execute(tenantId, response.toolCall.arguments);

      // 4. Format result for user
      return this.formatResponse(result, userMessage);
    }

    return response.content;
  }

  private getToolDefinitions(): ToolDefinition[] {
    return [
      {
        name: 'retrieve_transactions',
        description: 'Search orders by account, date range, or status.',
        parameters: {
          accountId: { type: 'string', description: 'Customer account ID' },
          dateRange: { type: 'object', properties: { start: 'string', end: 'string' } }
        }
      },
      {
        name: 'calculate_attribution',
        description: 'Get influenced revenue for a specific deal.',
        parameters: {
          dealId: { type: 'string', description: 'CRM Deal ID' }
        }
      }
    ];
  }
}

3. Context Injection

Operational agents gain significant utility when aware of the user's current state. A context layer injects UI state into the system prompt, allowing the agent to resolve ambiguous references.

// context/ContextInjector.ts
export interface PageContext {
  currentView: 'order_detail' | 'dashboard' | 'customer_profile';
  entityId?: string;
  activeFilters?: Record<string, any>;
}

export class ContextInjector {
  static augmentPrompt(basePrompt: string, context?: PageContext): string {
    if (!context) return basePrompt;

    const contextBlock = `
      CURRENT USER CONTEXT:
      - View: ${context.currentView}
      - Focused Entity: ${context.entityId || 'None'}
      - Active Filters: ${JSON.stringify(context.activeFilters || {})}
      
      Use this context to resolve ambiguous queries. If the user asks "when was this delivered?" 
      and the context shows an order detail view, assume they refer to the focused entity.
    `;

    return `${basePrompt}\n${contextBlock}`;
  }
}

4. Multi-Provider Factory

Enterprise environments often require flexibility in model selection due to vendor approvals or performance preferences. A factory pattern abstracts the provider implementation, allowing runtime switching without code changes.

// providers/ProviderFactory.ts
export interface LLMProvider {
  getModel(): string;
  chat(request: ChatRequest): Promise<ChatResponse>;
}

export class ProviderFactory {
  static create(config: ProviderConfig): LLMProvider {
    switch (config.vendor) {
      case 'openai':
        return new OpenAIProvider(config.apiKey, config.model);
      case 'anthropic':
        return new AnthropicProvider(config.apiKey, config.model);
      case 'openrouter':
        return new OpenRouterProvider(config.apiKey, config.model);
      default:
        throw new Error(`Unsupported provider: ${config.vendor}`);
    }
  }
}

Architecture Rationale:

• Parameterized Builders: Prevent SQL injection and ensure consistent query patterns.
• Structured Returns: Tools return typed objects, enabling the model to format responses accurately without parsing raw text.
• Read-Only Default: The architecture enforces read access. Write operations require a separate human-in-the-loop flow to prevent unintended state changes.
• Tenant Enforcement: Every builder requires tenantId, ensuring data isolation is baked into the tool layer, not left to the model.

Pitfall Guide

1. The SQL Mirage

   • Explanation: Allowing the model to generate SQL queries directly. This risks malformed queries, performance degradation, and security vulnerabilities.
   • Fix: Strictly enforce tool definitions. The model should only output tool names and parameters, never executable code.

2. Tenant Bleed

   • Explanation: Queries fail to filter by tenant ID, exposing data across customer boundaries.
   • Fix: Require tenantId as a mandatory parameter in all tool definitions and validate it in the builder implementation. Never trust the model to include it.

3. Context Blindness

   • Explanation: The agent ignores the user's current UI state, forcing repetitive clarification.
   • Fix: Implement a context injection layer that passes page state, selected entities, and active filters into the system prompt.

4. Write Access Creep

   • Explanation: The agent gains the ability to modify data, leading to accidental updates or deletions.
   • Fix: Design tools as read-only. For write operations, implement a "draft and approve" workflow where the agent suggests actions but requires explicit user confirmation.

5. Unstructured Tool Output

   • Explanation: Tools return raw text or HTML, making it difficult for the model to extract precise values.
   • Fix: Tools must return structured JSON objects with defined schemas. This allows the model to reference exact fields and reduces formatting errors.

6. Provider Lock-in

   • Explanation: Hardcoding a specific LLM vendor limits flexibility and complicates enterprise deployments.
   • Fix: Use a factory pattern to abstract provider implementations. Store credentials encrypted per-tenant and allow configuration-based switching.

7. Token Bloat

   • Explanation: Returning excessive data from tools consumes context window and increases latency.
   • Fix: Implement pagination and limits in query builders. Return only the fields necessary for the agent to answer the query.

Production Bundle

Action Checklist

• Define Tool Schemas: Create strict JSON schemas for all tools, including parameter types and descriptions.
• Implement Query Builders: Build domain-specific retrievers that validate inputs and enforce tenant isolation.
• Enforce Read-Only Mode: Ensure all tools return data only. Separate write operations into a distinct approval flow.
• Add Context Injection: Wire up the UI state to inject page context into the agent's system prompt.
• Configure Provider Factory: Set up encrypted credential storage and implement the factory pattern for multi-provider support.
• Add Audit Logging: Log all tool executions, including parameters and results, for compliance and debugging.
• Test Multi-Tenancy: Verify that queries cannot leak data across tenant boundaries using negative test cases.
• Implement Rate Limiting: Add throttling to tool executions to prevent abuse and manage database load.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High Compliance / Regulated Data	Tool-Chain with Read-Only Tools	Guarantees data fidelity and prevents unauthorized writes.	Medium (Development effort for builders)
Unstructured Documentation	RAG / Vector Search	Better suited for semantic search over text.	Low (Vector DB costs)
Enterprise Vendor Restrictions	Multi-Provider Factory	Allows switching between approved vendors without code changes.	Low (Abstraction overhead)
Complex Analytical Queries	AttributionEngine / Custom Builders	Encapsulates complex joins and business logic safely.	Medium (Builder complexity)
Cost-Sensitive Deployment	OpenRouter / Model Routing	Enables routing to cheaper models for simple queries.	Low (Routing logic)

Configuration Template

{
  "agent": {
    "version": "1.0.0",
    "provider": {
      "vendor": "anthropic",
      "model": "claude-3-sonnet-20240229",
      "apiKeyEnv": "LLM_API_KEY"
    },
    "tools": [
      {
        "name": "retrieve_transactions",
        "builder": "TransactionRetriever",
        "timeoutMs": 5000,
        "maxResults": 50
      },
      {
        "name": "calculate_attribution",
        "builder": "AttributionEngine",
        "timeoutMs": 10000,
        "maxResults": 1
      }
    ],
    "security": {
      "enforceTenantIsolation": true,
      "allowWriteOperations": false,
      "auditLogging": true
    }
  }
}

Quick Start Guide

1. Define Your First Tool: Create a QueryResult interface and implement a simple builder like TransactionRetriever with tenant validation.
2. Register the Tool: Instantiate the builder and register it with the OperationalAgent using a descriptive name and schema.
3. Configure the Provider: Set up the ProviderFactory with your preferred LLM vendor and API key.
4. Run a Test Query: Send a query like "Show me orders for Account 123" and verify the agent calls the tool and returns accurate data.
5. Add Context: Inject page context for order detail views and test ambiguous queries like "When was this delivered?" to verify context resolution.