# Tatasteel Agent — Redis Result Cache LLD

> **Jira:** TBD
> **Status:** Draft
> **Author:** Abhilash
> **Created:** 2026-04-02

---

## 1. Problem Statement

The current pipeline routes user questions through **Chanakya**, which delegates data queries to the **Tatasteel Agent**. The agent runs NL-to-SQL, retrieves results, and returns the full payload — raw dataset rows, widget JSON, and a text summary — directly into Chanakya's LLM context. Chanakya synthesises an executive response from this combined payload.

This creates significant **token overhead**. As datasets grow, the LLM context fills with raw tabular data that the model doesn't need to generate insights.

---

## 2. Proposed Solution

Introduce **Redis** as an intermediary cache between the Tatasteel Agent and Chanakya. The agent stores its full result in Redis and returns only a `result_id` and a concise text summary to Chanakya's LLM context. Chanakya synthesises from the summary. If it needs the raw data, it fetches it from Redis via a dedicated tool. Widgets continue flowing to the frontend through the existing SSE pipeline — **no frontend changes required**.

**Token reduction: 97–99% per query.**

---

## 3. Functional Requirements

| # | Requirement | Description |
|---|---|---|
| FR-01 | Redis Storage on Agent Return | Tatasteel Agent must store the full result payload in Redis before returning to Chanakya |
| FR-02 | Slim Return Contract | Agent return must carry only `result_id` and `summary`; raw rows and widgets must not enter Chanakya's LLM context |
| FR-03 | On-Demand Raw Data Fetch | Chanakya must have a `fetch_agent_result` tool to retrieve full payloads from Redis when the LLM decides it needs them |
| FR-04 | Redis Unavailability Fallback | If Redis is down, fall back to the existing full-text return pattern; log the event but do not fail the request |
| FR-05 | TTL-Based Expiry | Each Redis key must have a 3600-second TTL; expired results must return a user-friendly error prompting re-query |
| FR-06 | Structured Summary Quality | Summaries must be generated deterministically from query results — no LLM call; must include totals, top categories, and status splits in 3–6 sentences |

---

## 4. Non-Functional Requirements

| # | Requirement | Description |
|---|---|---|
| NFR-01 | Latency | Redis read/write must add < 10 ms to the query path |
| NFR-02 | Token Budget | Chanakya LLM context per query must stay ≤ 200 tokens for agent results |
| NFR-03 | Security | Redis keys must be UUID-based; payloads must include `user_email` for ownership checks |
| NFR-04 | Observability | Log every Redis write, every `fetch_agent_result` call, and every fallback event |

---

## 5. What Changes vs What Does Not

| Component | Change? | Detail |
|---|---|---|
| Tatasteel — DB query & widget generation | **None** | Existing NL2SQL pipeline unchanged |
| Tatasteel — result storage | **New** | Stores payload in Redis before returning |
| Tatasteel — return value | **Changed** | Returns `{result_id, summary}` instead of full blob |
| Chanakya — `ask_tatasteel` tool | **Minor** | Parses `{result_id, summary}` from return value |
| Chanakya — `fetch_agent_result` tool | **New** | Fetches from Redis when LLM decides it needs full data |
| Chanakya — system prompt | **Minor** | Inform LLM about `result_id` + `summary` pattern and when to call `fetch_agent_result` |
| Frontend | **None** | No changes needed |
| Redis | **New** | Added as a service to docker compose |

---

## 6. Sequence Diagram

```mermaid
sequenceDiagram
    participant User
    participant FE as Frontend
    participant Chan as Chanakya
    participant TS as Tatasteel Agent
    participant DB as Tatasteel DB
    participant Cache as Redis Cache

    User->>FE: Ask question
    FE->>Chan: POST user_email, question
    Chan->>TS: ask_tatasteel(question)

    TS->>DB: NL2SQL, execute query
    DB-->>TS: raw rows

    TS->>TS: Build widget JSON and summary

    TS->>Cache: SETEX agent_result TTL=3600
    Note right of Cache: Stores result_id, agent,<br/>question, query, summary,<br/>response, generated_at, user_email
    Cache-->>TS: OK

    TS-->>FE: SSE widget events
    TS-->>Chan: result_id and summary only

    Chan->>Chan: LLM synthesises insight from summary

    alt Summary insufficient
        Chan->>Cache: GET agent_result by result_id
        Cache-->>Chan: full response payload
        Chan->>Chan: LLM uses full response
    end

    Chan-->>FE: SSE text chunk events
    FE-->>User: Display widgets and insight text
```

---

## 7. Step-by-Step Lifecycle

### Step 1 — User Submits Question

The user sends a question through the Chat UI. The request reaches Chanakya with `user_email`, `tenant`, and `question`.

---

### Step 2 — Chanakya Delegates to Tatasteel Agent

Chanakya's `ask_tatasteel` tool fires. After this change, the tool contract shifts — Chanakya now expects `{result_id, summary}` back.

> **No change to how `ask_tatasteel` calls the agent.** Only the return parsing changes.

---

### Step 3 — Tatasteel Agent Executes Query

The agent runs its existing NL2SQL pipeline (schema lookup → SQL generation → DB execution → widget JSON → summary). All of this is unchanged.

---

### Step 4 — Agent Stores Result in Redis

Before returning, the agent calls `_store_agent_result` — a utility function that accepts the question, generated SQL, full agent response, summary, and user email. It generates a UUID `result_id`, packages everything into a JSON payload, and writes it to Redis under the key `agent_result:{result_id}` with a 3600s TTL.

> **Fallback:** If the Redis write fails, the agent logs the event and falls through to returning the full payload in the legacy shape (FR-04).

---

### Step 5 — Agent Returns Slim Payload to Chanakya

The agent returns a JSON string with exactly two fields: `result_id` (the UUID stored in Redis) and `summary` (the concise 3–6 sentence text). This is the only data that enters Chanakya's LLM context (~160 tokens).

---

### Step 6 — Chanakya LLM Synthesises Response

The LLM receives the `result_id` + summary and synthesises an executive insight from the summary text. For most queries, this is sufficient.

The system prompt must tell the LLM that every `ask_tatasteel` result contains a `result_id` and a `summary`. It should only call `fetch_agent_result` when the summary is insufficient — for example, when the user asks for specific row-level values or cross-agent number comparisons.

---

### Step 7 — Chanakya Fetches Full Data from Redis

If the LLM determines the summary is insufficient, it calls the `fetch_agent_result` tool with the `result_id`. The tool retrieves the full payload from Redis, verifies `user_email` ownership, and returns the `response` object.

> **TTL expiry:** If the Redis key is gone, the tool returns `"Result expired. Ask the user to re-run the query."` Chanakya surfaces this to the user.

---

### Step 8 — Chanakya Streams Response to Frontend

Chanakya streams `text_chunk` SSE events to the frontend. The frontend renders the insight text alongside the widgets already received from Tatasteel.

---

## 8. Redis Schema

**Key pattern:** `agent_result:{result_id}`

**Value (JSON string):**
```json
{
    "result_id":    "550e8400-e29b-41d4-a716-446655440000",
    "agent":        "tatasteel",
    "question":     "Show early warnings by category and status",
    "query":        "SELECT category, status, COUNT(*) FROM ts_early_warnings GROUP BY category, status",
    "summary":      "Early Warnings total 332 across 11 categories. Top: Site Conditions-Other (72 open, 40 closed), Design Maturity (67 open, 6 closed), Programme (43 open, 10 closed). A significant majority remain open.",
    "response":     { "dataset": [...], "widgets": [...] },
    "generated_at": "2026-04-02T11:18:24Z",
    "user_email":   "executive@tatasteel.com"
}
```

**TTL:** 3600 seconds (1 hour)

| Field | Type | Purpose |
|---|---|---|
| `result_id` | UUID string | Key identity; returned to Chanakya |
| `agent` | string | Which agent produced this result |
| `question` | string | Original user question; useful for observability |
| `query` | string | Generated SQL |
| `summary` | string | 3–6 sentence human-readable summary; the only data Chanakya LLM sees by default |
| `response` | object | Full agent response — contains `dataset` (raw query rows) and `widgets` (chart/table JSON) |
| `generated_at` | ISO timestamp | When the result was created |
| `user_email` | string | Owner check on `fetch_agent_result` |

---

## 9. Tool / Function Contracts

### 9.1 `_store_agent_result` — New Utility (Tatasteel Agent)

A new internal async utility on the Tatasteel Agent side.

**Inputs:** Redis client, agent key (e.g. `"tatasteel"`), original question, generated SQL, full agent response object, summary string, user email, TTL (default 3600s).

**Behaviour:** Generates a UUID `result_id`, builds the cache payload (Section 8), writes it to Redis under `agent_result:{result_id}` with the given TTL, returns the `result_id` to the caller.

**On Redis failure:** Raises a connection exception; caller catches it and falls through to legacy return behaviour (FR-04).

---

### 9.2 `_ask_nl2sql_agent` — Modified Return (Tatasteel Agent)

Currently returns a full text blob (raw rows + widget JSON + summary) to Chanakya.

**New behaviour (Redis available):** Calls `_store_agent_result` after query execution, then returns a slim JSON string with only two fields: `result_id` and `summary`.

**Fallback (Redis unavailable):** Returns the legacy full text summary unchanged. Chanakya's `ask_tatasteel` parser handles both shapes.

---

### 9.3 `fetch_agent_result` — New Chanakya Tool

A new PydanticAI tool registered on the Chanakya agent.

**Input:** `result_id` — the UUID returned by the Tatasteel Agent alongside the summary.

**Behaviour:**
1. Looks up `agent_result:{result_id}` in Redis.
2. Key missing (expired/invalid) → returns `"Result expired. Ask the user to re-run the query."`
3. `user_email` mismatch → returns `"Access denied."`
4. Valid → returns the full payload (`response`, `query`, `summary`, metadata).

**LLM guidance (docstring):** Call this only when the summary is insufficient — e.g. exact row-level values or cross-agent comparisons. Do not call it when the summary already answers the question.

---

### 9.4 `ask_tatasteel` — Updated Return Parsing (Chanakya)

Currently passes the raw Tatasteel return string directly into LLM context.

**New behaviour:** Parse the return as JSON. If it contains `result_id` and `summary`, forward only those two fields to the LLM context (~160 tokens). If the return is a legacy plain string or unexpected shape (Redis was down), pass it through unchanged and log a warning.

---

## 10. Fallback Behaviour (Redis Unavailable)

```
Redis unavailable
       ↓
_store_agent_result raises ConnectionError
       ↓
Tatasteel Agent catches exception → logs redis_fallback=True
       ↓
Returns full summary string to Chanakya (legacy shape)
       ↓
ask_tatasteel parser detects legacy shape (no result_id key)
       ↓
Chanakya LLM receives full payload as before → request completes, user unaffected
```

> If fallback rate exceeds ~5% of requests in a 5-minute window, trigger an infrastructure alert.

---

## 11. Implementation Tasks

### TASK-01 — Design and Finalise Redis Cache Schema

| Sub-task | Description |
|---|---|
| Define all fields | Confirm the final set of fields for the Redis JSON payload (Section 8) |
| Define `response` structure | Agree on what goes inside the `response` object — dataset rows, widget JSON, or both |
| Confirm TTL | 3600s default; confirm if it should be configurable per agent or global |
| Confirm key pattern | Finalise `agent_result:{result_id}` as the key format |

---

### TASK-02 — Implement Cache Store and Retrieve Functions

| Sub-task | Description |
|---|---|
| Add Redis to Docker Compose | Add `redis:7-alpine` service with healthcheck; add `REDIS_URL` env var to Tatasteel and Chanakya services |
| Redis client startup | Initialise async Redis client on agent startup; wire into `RequestContext`; handle unavailability gracefully |
| `_store_agent_result` function | Async utility: generates UUID, builds payload, writes to Redis with TTL, returns `result_id` |
| `fetch_agent_result` tool | Chanakya PydanticAI tool: retrieves by `result_id`, verifies `user_email`, handles expired keys |
| Fallback handling | Catch Redis exceptions; log `redis_fallback=True`; return legacy shape |

---

### TASK-03 — Modify Tatasteel Response Format and Chanakya Parsing

| Sub-task | Description |
|---|---|
| Update `_ask_nl2sql_agent` return | Call `_store_agent_result` after execution; return `{result_id, summary}` JSON; add fallback path |
| Update `ask_tatasteel` parser in Chanakya | Detect new vs. legacy shape; forward only `result_id` + `summary` to LLM context if new shape |
| Validate token reduction | Confirm Chanakya LLM context for an agent result is ≤ 200 tokens |

---

### TASK-04 — Prompt Changes

| Sub-task | Description |
|---|---|
| Chanakya system prompt update | Add instruction explaining the `result_id` + `summary` pattern and when to call `fetch_agent_result` |
| `fetch_agent_result` tool docstring | Clearly define when the LLM should and should not call this tool |
| Validate LLM behaviour | Test with sample questions to confirm the LLM does not over-fetch from Redis |

---

**Total Estimate: ~30h (~2–3 days)**

---

## 12. Future Phase — Any NL2SQL Agent Extension

The same pattern applies to any future NL2SQL agent (e.g. Insurance Agent):

- [ ] Pass the appropriate `agent_key` when calling `_store_agent_result`
- [ ] No changes to Redis schema, key pattern, TTL, or `fetch_agent_result` — all are already generic
- [ ] Update the agent's return to `{result_id, summary}` shape
- [ ] Validate summary quality for the new agent's query types