Comp

# Complex Agent — Tool Architecture Analysis & Gaps

## Foundation: What Already Exists

`complex_agent.py` has a working semantic layer (`_SEMANTIC_LAYER`, lines 52–169) and three semantic discovery tools that directly match what the PDF describes:

| PDF Recommendation | Existing in Codebase |
|---|---|
| `semantic_search(query)` | `semantic_search_on_semantic_layer(query)` — keyword scoring against the layer |
| `get_column_metadata(tables)` | `retrieve_semantic_layer_details(table_name)` — returns full column docs |
| `find_relevant_tables(query)` | Embedded in `semantic_search_on_semantic_layer` — returns `recommended_tables` |
| Measures tool | `metric_formulas` block inside each table entry (e.g., `total_revenue`, `labor_cost_ratio`) |
| Business rules tool | `common_filters` inside each table entry (partial — filter patterns only) |
| Join metadata | `join_keys` block inside each table entry (1-hop only) |

The `_SEMANTIC_LAYER` dict **is** the semantic layer. It's just not yet broken out into focused tools.

---

## What's MISSING (Gaps to Fill)

### 1. No `gather_complex_context(query)` Composite Tool
The PDF's core recommendation. Currently the agent calls `semantic_search` → `retrieve_semantic_layer_details` → `get_table_info` **sequentially**. There is no single tool that fans out **concurrently** to get semantic matches + measures + business rules + joins in one call.

**What it should return:**
- semantic matches
- candidate tables
- candidate measures
- business rules
- possible joins (if table set is obvious)
- confidence / similarity metadata

### 2. No `get_measure_definition()` Tool
Measures are buried *inside* `retrieve_semantic_layer_details()` alongside 20+ column docs. The agent cannot ask "what is labor cost ratio?" without loading the entire table's context.

The `metric_formulas` dict is the data — it just needs its own tool surface.

**Examples of measures that need to be independently queryable:**
- `net_sales`
- `gross_sales`
- `labor_cost_ratio`
- `avg_order_value`
- `covers_per_seat`

### 3. No `get_business_rules()` Tool — BIGGEST GAP
`common_filters` covers filter patterns only (e.g., `{"channel": "filters={'fulfillment_type': 'delivery'}"}`). There is **nothing** encoding actual domain rules like:

- "voided tickets are excluded from revenue"
- "fiscal week starts Monday"
- "active location = has orders in last 30 days"
- "refunds are netted out of revenue"
- "delivery orders exclude the platform fee"

> Wrong SQL outputs are often **business-logic errors, not syntax errors.** This is the most impactful missing piece.

### 4. No `find_join_paths(tables)` Tool
`join_keys` only has direct 1-hop joins (e.g., `orders → locations` on `location_id`). Multi-hop is not modeled.

The `staff_schedule → orders` join is documented as free text:
```
"location_id (match schedule_date ≈ order_date for the same day)"
```
This is not queryable. There is no confidence score or "indirect join path" output.

**What this tool should return:**
- known join keys
- confidence / source
- possible join path if indirect
- surface missing join metadata clearly

### 5. Semantic Search is Keyword Scoring, Not Vector Similarity
`semantic_search_on_semantic_layer` does simple word matching:
- +2 per word hit in description
- +3 for metric key match
- +1 for column match

Rephrased follow-up questions ("what about last week?") will miss unless exact words overlap. This is the **follow-up question context loss** problem described in the PDF.

### 6. No `if data not found → fallback` Logic
When similarity search finds nothing, the agent currently returns all 5 tables as a fallback (line 229). There is no structured path to:

1. Rephrase the query slightly
2. Retry similarity search
3. Surface a clarification prompt to the user

---

## Recommended Tool Set (from PDF)

### Minimum tool set for the complex agent:

```
gather_complex_context(query, conversation_context=None)
→ runs concurrently inside:
- semantic similarity lookup
- measures lookup
- business rules lookup
- maybe table lookup

find_join_paths(tables)
expand_measure_definition(name)
get_rule_details(rule_name)
```

### Recommended agent flow:

1. Rewrite current turn into a **standalone question** if needed
2. Run **similarity search** against the semantic layer
3. Pull relevant **measure definitions**
4. Pull relevant **business rules**
5. Find tables / columns / joins
6. Construct SQL plan
7. Validate against gathered semantic context

---

## Where to Build From

| What to build | Build from |
|---|---|
| `gather_complex_context()` | Wrap existing `semantic_search_on_semantic_layer` + new concurrent lookups |
| `get_measure_definition()` | Extract `metric_formulas` from `_SEMANTIC_LAYER` into own tool |
| `get_business_rules()` | Add `business_rules` section to `_SEMANTIC_LAYER`, expose as tool |
| `find_join_paths()` | Extract + expand `join_keys` from `_SEMANTIC_LAYER`, add multi-hop + confidence |
| Semantic search upgrade | Replace keyword scoring with vector embeddings |

**Core file:** `complex_agent.py:52–289`
**Data model to extend:** `_SEMANTIC_LAYER` dict — add `business_rules` section per table
**Pattern:** Not a rewrite — a refactor + gap-fill

---

## Key Principle (from PDF)

> Use the **same semantic layer** as the source, but expose it through focused tools:
> - one tool for similarity retrieval
> - one for measures
> - one for business rules
> - one for joins
>
> That way the complex agent can retrieve **only what it needs at each step.**

Thread pool / concurrency should happen **inside tool execution**, not as hidden prefetch before the agent runs.