Copilot Agent Architecture

Deep-dive into the OpenAI Agents SDK copilot — request lifecycle, tools, prompt composition, memory, and observability.

Architecture Overview

The AXIS Copilot is a conversational data analyst built on the OpenAI Agents SDK. It accepts natural language questions, translates them into DuckDB SQL queries, and returns formatted answers with optional charts. Every request flows through a layered pipeline of guardrails, context injection, tool execution, and output sanitization.

Request Lifecycle

Every copilot request passes through eight distinct stages. The pipeline is shared between the Pydantic-AI and OpenAI Agents SDK implementations via run_copilot_request() in request_router.py.

Agent Core (OAICopilotAgent)

The copilot is implemented as a module in app/copilot/oai_agent.py using the OpenAI Agents SDK. The agent is created once per process and reused across requests. Dynamic instructions are injected per-request via a callable.

OAIContext Fields

Both agent implementations share a base context (BaseCopilotContext) with these fields:

Agent Creation Pattern

agent = Agent(
    name="axis-copilot",
    instructions=lambda ctx: compose_system_prompt(ctx.context),
    tools=[summarize_data, query_data, run_sql, analyze_data,
           compare_data, plot_data, analyze_patterns, ...],
    hooks=CopilotRunHooks(),
    model=LLMProvider.get_model(),
)

Tool System

The copilot has 12 tools registered via the @function_tool decorator. Each tool follows a consistent pattern: cache check, guardrail validation, DuckDB execution, thought emission, and result truncation.

Tool Execution Pattern

Every tool uses the tool_span context manager for consistent caching, tracing, and thought emission:

@ft
async def run_sql(ctx: RunContextWrapper[OAIContext], sql: str) -> str:
    deps = ctx.context
    async with tool_span(
        deps, "run_sql", sql,
        f"Executing SQL query...",
        {"sql": sql_fingerprint(sql)},
    ) as (_tracer, _span, cached):
        if cached:
            return cached

        # Guardrail check
        blocked = check_sql_safety(sql)
        if blocked:
            return json.dumps({"error": blocked})

        # Execute with retry loop (up to 3 attempts)
        result = await _execute_with_retry(deps, sql)
        deps.set_cached("run_sql", sql, result)
        return result

SQL Retry Loop

The run_sql tool implements a 3-attempt auto-fix loop for SQL errors:

Prompt Composition

The system prompt is assembled dynamically per request by compose_system_prompt(). It stacks multiple layers, each contributing domain knowledge, rules, and context:

Prompt Size Budget

• Static layers (1-4): ~2,500 tokens — constant overhead
• Schema DDL: scales with column count (~10 tokens/column)
• Memory injection: hard-capped at 1,000 characters
• Skills: typically 200-500 tokens per skill, max 3
• SQL examples: 2-5 matched examples, ~100 tokens each

Schema Context Building

The schema context is the richest dynamic section of the prompt. It gives the LLM everything it needs to write correct SQL queries against the current dataset.

DDL Format Example

-- DuckDB table: eval_data (12,847 rows)
CREATE TABLE eval_data (
  dataset_id VARCHAR,        -- unique record identifier
  query VARCHAR,             -- input prompt
  actual_output VARCHAR,     -- LLM response text
  faithfulness_score DOUBLE, -- range: [0.12 .. 0.99], avg: 0.74
  relevance_score DOUBLE,    -- range: [0.05 .. 1.00], avg: 0.81
  environment VARCHAR,       -- values: production, staging, dev
  source_name VARCHAR,       -- values: alpha_bot, beta_bot
  timestamp TIMESTAMP
);

Injection Sources

Skill System

Skills are domain-specific instruction files that augment the copilot's capabilities for particular types of questions. They are written as Markdown files with YAML frontmatter.

SKILL.md Format

---
name: trend-analysis
description: Analyze time-series trends and detect anomalies
version: "1.0"
priority: 10
triggers:
  - trend
  - over time
  - time series
  - anomaly
  - spike
  - regression
---

## Trend Analysis Skill

When the user asks about trends over time, follow these steps:
1. Use `run_sql` to query with GROUP BY time bucket
2. Look for sustained direction changes (3+ consecutive periods)
3. Flag anomalies where values exceed 2 standard deviations
...

Trigger Matching

Skills are selected by word-boundary matching against the user message and recent conversation context. The matching is scored and capped at 3 skills per request:

• Word boundary: triggers match as whole words (not substrings)
• Scored: more trigger matches = higher priority
• Cap: maximum 3 skills injected per request
• Rendered: selected skills are wrapped in XML blocks for clear delineation

Skill Discovery

The skill registry loads from two locations, with custom overriding builtin:

1. Builtin: app/copilot/skills/builtin/ — shipped with AXIS
2. Custom: custom/config/skills/ — site-specific overrides (by name)

Cross-Session Memory

The copilot learns from past interactions. After each successful SQL-executing request, patterns are extracted and stored in DuckDB for future injection. Memory is keyed by schema fingerprint so different datasets maintain separate memory stores.

Data Model

Decay & Eviction

Schema Fingerprinting

Memory is keyed by a SHA-256 hash (first 12 chars) of the sorted column names and types. When the schema changes (columns added/removed/renamed), the fingerprint changes and the copilot starts fresh memory for the new schema:

def schema_fingerprint(store, table_name: str) -> str:
    meta = store.get_metadata(table_name)
    cols = sorted(
        (c["column_name"], c.get("column_type", ""))
        for c in meta.get("columns", [])
    )
    return hashlib.sha256(str(cols).encode()).hexdigest()[:12]

SQL Error Recovery

When a SQL query fails, the copilot attempts to automatically fix it using heuristic error parsing. The _parse_sql_error_hint() function extracts structured hints from DuckDB error messages, and _attempt_sql_fix() applies corrections.

Error Types Handled

Fix Description

After a successful auto-fix, _describe_sql_fix() generates a human-readable description that is both emitted as a thought and stored in cross-session memory for future reference:

# Example output from _describe_sql_fix():
"Column 'faitfulness_score' not found → replaced with 'faithfulness_score' (fuzzy match)"
"CAST error on 'latency' → wrapped with TRY_CAST to handle NULL values"

Request Pipeline (Detailed)

Here is the full data flow through the copilot's request pipeline, showing exactly which function handles each stage and what data flows between them.

Key Functions

Streaming & SSE

The copilot streams real-time feedback to the frontend via Server-Sent Events. The ThoughtStream class provides an async pub/sub system that agent tools and hooks publish into, while the SSE endpoint subscribes and forwards events to the client.

SSE Event Types

ThoughtType Enum

Each thought has a type that determines its display color in the frontend:

ThoughtStream Architecture

The stream uses an asyncio.Queue internally. Tools and hooks call emit_*() methods (e.g. emit_reasoning(), emit_tool_use()) which enqueue Thought dataclass instances. The SSE endpoint reads from the queue and yields formatted events:

class ThoughtStream:
    def __init__(self):
        self._queue: asyncio.Queue[Thought | None] = asyncio.Queue()

    async def emit_reasoning(self, content: str, node_name: str = None):
        await self._queue.put(Thought(
            type=ThoughtType.REASONING,
            content=content,
            node_name=node_name,
        ))

    async def subscribe(self):
        """Yield thoughts until None sentinel is received."""
        while True:
            thought = await self._queue.get()
            if thought is None:
                break
            yield thought

Tracing

The copilot integrates with Langfuse for observability. Every request creates a trace span hierarchy that captures tool calls, SQL queries, timings, and errors.

Span Hierarchy

SQL Fingerprinting

For privacy-safe tracing, SQL queries are fingerprinted before being sent to Langfuse. The sql_fingerprint() function replaces literal values with placeholders:

# Input:  SELECT * FROM eval_data WHERE source_name = 'alpha_bot' AND score > 0.8
# Output: SELECT * FROM eval_data WHERE source_name = ? AND score > ?

Tool Reference

Complete reference for all 12 copilot tools with inputs, outputs, and behavior details.

Configuration

The copilot is controlled by environment variables and YAML config files in the custom/config/ directory.

Environment Settings

YAML Config Files

Next Steps