# sherman94062/retail-sage architecture

> Architecture diagram of the GitHub repository sherman94062/retail-sage, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-04-02). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/sherman94062/retail-sage
- Repository: https://github.com/sherman94062/retail-sage

## Overview

1. **Overall shape**
   - Retail-SAGE is a local, offline-capable analytics system with three main planes: a **data warehouse built in DuckDB**, an **AI agent that reasons over it**, and a **presentation/evaluation layer** for chat, benchmarking, and tests.

2. **Data source and storage boundary**
   - The underlying dataset is **TPC-DS SF1** (and optionally SF100 for larger scale).
   - Raw/generated data is created by scripts, stored as DuckDB-accessible data and/or exported Parquet, then queried locally by the rest of the stack. There is no separate server-side database service.

3. **Data pipeline**
   - `scripts/01_generate_data.py` creates the TPC-DS dataset.
   - `scripts/02_export_parquet.py` supports partitioned Parquet export for larger scale.
   - `scripts/03_verify_data.py` validates the dataset against the TPC-DS benchmark expectations.
   - This pipeline feeds the analytical model layer used by dbt and the agent.

4. **dbt transformation layer**
   - The `dbt/` project is the warehouse modeling boundary.
   - `models/staging/` contains 24 source-aligned staging models for the TPC-DS tables.
   - `models/intermediate/` normalizes and unifies business concepts like sales, returns, customer profile, and item performance.
   - `models/marts/` exposes query-friendly marts such as `fct_sales`, `fct_returns`, `dim_customer`, `dim_item`, `dim_store`, `dim_date`, plus derived metrics tables like `daily_channel_summary` and `customer_ltv`.

5. **Semantic layer**
   - `dbt/semantic_models/` defines MetricFlow semantic models for sales, customer, inventory, and channel metrics.
   - This layer is the governed metric boundary: it standardizes business definitions so the agent can reason in analytics terms rather than raw table joins.

6. **Agent core**
   - The `agent/` package is the autonomous reasoning system.
   - `agent/agent.py` runs the multi-turn Claude tool-use loop and returns a structured `AgentResult`.
   - The agent does not answer from a single SQL generation step; it iterates through search, schema inspection, SQL execution, and refinement until it can synthesize a result.

7. **Agent tools and execution**
   - `agent/tools.py` defines the tools the model can call, including SQL execution and schema/table inspection behaviors.
   - The tools form the execution boundary between natural language reasoning and the local DuckDB warehouse.
   - This is the core control loop: the model proposes a step, the tools ground it in data, and the agent continues or finalizes.

8. **Semantic memory**
   - `agent/memory.py` uses **ChromaDB** to store and retrieve query history, table descriptions, and column glossary content.
   - `scripts/04_seed_memory.py` populates that memory store.
   - The memory layer improves table discovery and reduces cold-start hallucination by giving the agent prior knowledge from the repository’s own schema and past analyses.

9. **Context and prompting**
   - `agent/context.py` builds schema context for the model from the warehouse and/or metadata.
   - `agent/prompts.py` contains the system prompt and few-shot examples that shape tool use and analytical behavior.
   - Together these define the agent’s operating policy: use memory, inspect schema, write SQL, verify results, then answer with specific numbers.

10. **User interfaces**
   - `ui/app.py` is the Streamlit UI for interactive chat.
   - The UI is not just a chat box; it also surfaces execution trace information like SQL history, token usage, cost, and progress messages, which makes the agent’s reasoning inspectable.

11. **Benchmarking and quality control**
   - `agent/benchmark.py` and `scripts/05_run_benchmark.py` evaluate the agent against the full **99-query TPC-DS benchmark**.
   - This creates a closed-loop quality system: data generation and transformations are verified, then the agent is tested on real analytical questions against ground truth.

12. **Tests and engineering boundaries**
   - `tests/` covers tool behavior, memory CRUD/search, and prompt construction.
   - The repository cleanly separates concerns into: **data preparation**, **warehouse modeling**, **semantic memory**, **agent orchestration**, and **UI/evaluation**.
   - That separation is what you’d want to reflect in an architecture diagram: data flows left-to-right from generation to DuckDB/dbt/semantic models, then into memory and agent reasoning, then out through CLI/UI and benchmark/test harnesses.

Main entry points: [`agent/agent.py`](https://github.com/sherman94062/retail-sage/blob/main/agent/agent.py) (Reasoning loop), [`scripts/01_generate_data.py`](https://github.com/sherman94062/retail-sage/blob/main/scripts/01_generate_data.py) (Generate data), [`tests`](https://github.com/sherman94062/retail-sage/tree/main/tests) (Test suite), [`dbt/models/marts`](https://github.com/sherman94062/retail-sage/tree/main/dbt/models/marts) (Retail marts)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_grp_data["Data pipeline"]
  node_n_generate["Generate data<br/>script"]
  node_n_export["Export Parquet<br/>script"]
  node_n_verify["Verify data<br/>script<br/>[03_verify_data.py]"]
end

subgraph group_grp_warehouse["Warehouse"]
  node_n_staging["Staging models<br/>dbt staging"]
  node_n_intermediate["Intermediate models<br/>dbt transforms"]
  node_n_marts["Retail marts<br/>dbt marts"]
  node_n_semantic["Semantic models<br/>metricflow"]
  node_n_duckdb[("DuckDB warehouse<br/>local store")]
end

subgraph group_grp_agent["Agent core"]
  node_n_context["Context build<br/>agent context<br/>[context.py]"]
  node_n_prompts["Prompt policy<br/>agent prompts<br/>[prompts.py]"]
  node_n_memory[("Semantic memory<br/>chroma store<br/>[memory.py]")]
  node_n_tools["Agent tools<br/>tooling<br/>[tools.py]"]
  node_n_loop{{"Reasoning loop<br/>agent runner<br/>[agent.py]"}}
  node_n_seed["Seed memory<br/>script<br/>[04_seed_memory.py]"]
end

subgraph group_grp_ui["UX and eval"]
  node_n_ui["Streamlit app<br/>ui<br/>[app.py]"]
  node_n_benchmark["Benchmark run<br/>eval"]
  node_n_eval_core["Agent benchmark<br/>eval core<br/>[benchmark.py]"]
  node_n_tests["Test suite<br/>tests"]
end

node_n_generate -->|"load data"| node_n_duckdb
node_n_generate -.->|"optional export"| node_n_export
node_n_export -->|"serve files"| node_n_duckdb
node_n_generate -->|"validate"| node_n_verify
node_n_duckdb -->|"source refs"| node_n_staging
node_n_staging -->|"normalize"| node_n_intermediate
node_n_intermediate -->|"publish marts"| node_n_marts
node_n_marts -->|"define metrics"| node_n_semantic
node_n_semantic -->|"schema context"| node_n_context
node_n_marts -->|"table metadata"| node_n_context
node_n_context -->|"assemble prompt"| node_n_prompts
node_n_prompts -->|"guide behavior"| node_n_loop
node_n_memory -->|"retrieve knowledge"| node_n_loop
node_n_seed -->|"populate"| node_n_memory
node_n_loop -->|"call tools"| node_n_tools
node_n_tools -->|"run sql"| node_n_duckdb
node_n_loop -->|"respond"| node_n_ui
node_n_eval_core -->|"score agent"| node_n_loop
node_n_benchmark -->|"invoke"| node_n_eval_core
node_n_tests -->|"verify behavior"| node_n_tools
node_n_tests -->|"verify behavior"| node_n_memory
node_n_tests -->|"verify behavior"| node_n_prompts

click node_n_generate "https://github.com/sherman94062/retail-sage/blob/main/scripts/01_generate_data.py"
click node_n_export "https://github.com/sherman94062/retail-sage/blob/main/scripts/02_export_parquet.py"
click node_n_verify "https://github.com/sherman94062/retail-sage/blob/main/scripts/03_verify_data.py"
click node_n_staging "https://github.com/sherman94062/retail-sage/tree/main/dbt/models/staging"
click node_n_intermediate "https://github.com/sherman94062/retail-sage/tree/main/dbt/models/intermediate"
click node_n_marts "https://github.com/sherman94062/retail-sage/tree/main/dbt/models/marts"
click node_n_semantic "https://github.com/sherman94062/retail-sage/tree/main/dbt/semantic_models"
click node_n_context "https://github.com/sherman94062/retail-sage/blob/main/agent/context.py"
click node_n_prompts "https://github.com/sherman94062/retail-sage/blob/main/agent/prompts.py"
click node_n_memory "https://github.com/sherman94062/retail-sage/blob/main/agent/memory.py"
click node_n_tools "https://github.com/sherman94062/retail-sage/blob/main/agent/tools.py"
click node_n_loop "https://github.com/sherman94062/retail-sage/blob/main/agent/agent.py"
click node_n_seed "https://github.com/sherman94062/retail-sage/blob/main/scripts/04_seed_memory.py"
click node_n_ui "https://github.com/sherman94062/retail-sage/blob/main/ui/app.py"
click node_n_benchmark "https://github.com/sherman94062/retail-sage/blob/main/scripts/05_run_benchmark.py"
click node_n_eval_core "https://github.com/sherman94062/retail-sage/blob/main/agent/benchmark.py"
click node_n_tests "https://github.com/sherman94062/retail-sage/tree/main/tests"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_n_generate,node_n_export,node_n_verify toneBlue
class node_n_staging,node_n_intermediate,node_n_marts,node_n_semantic,node_n_duckdb toneAmber
class node_n_context,node_n_prompts,node_n_memory,node_n_tools,node_n_loop,node_n_seed toneMint
class node_n_ui,node_n_benchmark,node_n_eval_core,node_n_tests toneRose
```

## Components

### Data pipeline

Generate, export, and verify the local TPC-DS dataset.

- [Generate data](https://github.com/sherman94062/retail-sage/blob/main/scripts/01_generate_data.py): script · Creates the TPC-DS dataset locally. · `scripts/01_generate_data.py`
- [Export Parquet](https://github.com/sherman94062/retail-sage/blob/main/scripts/02_export_parquet.py): script · Partitioned Parquet export for larger-scale local use. · `scripts/02_export_parquet.py`
- [Verify data](https://github.com/sherman94062/retail-sage/blob/main/scripts/03_verify_data.py): script · Validates generated data against TPC-DS expectations. · `scripts/03_verify_data.py`

### Warehouse

dbt modeling and semantic metric definitions over DuckDB-accessible data.

- [Staging models](https://github.com/sherman94062/retail-sage/tree/main/dbt/models/staging): dbt staging · Source-aligned models for TPC-DS tables. · `dbt/models/staging`
- [Intermediate models](https://github.com/sherman94062/retail-sage/tree/main/dbt/models/intermediate): dbt transforms · Normalizes sales, returns, customer, and item concepts. · `dbt/models/intermediate`
- [Retail marts](https://github.com/sherman94062/retail-sage/tree/main/dbt/models/marts): dbt marts · Query-friendly facts, dimensions, and derived metrics. · `dbt/models/marts`
- [Semantic models](https://github.com/sherman94062/retail-sage/tree/main/dbt/semantic_models): metricflow · Governed metric definitions for analytics terms. · `dbt/semantic_models`
- **DuckDB warehouse**: local store · Local analytical storage and execution boundary.

### Agent core

Reasoning loop, tools, memory, and prompt/context assembly.

- [Context build](https://github.com/sherman94062/retail-sage/blob/main/agent/context.py): agent context · Builds schema context from warehouse metadata. · `agent/context.py`
- [Prompt policy](https://github.com/sherman94062/retail-sage/blob/main/agent/prompts.py): agent prompts · System prompt and few-shot examples for tool use. · `agent/prompts.py`
- [Semantic memory](https://github.com/sherman94062/retail-sage/blob/main/agent/memory.py): chroma store · Stores query history, table descriptions, and glossary terms. · `agent/memory.py`
- [Agent tools](https://github.com/sherman94062/retail-sage/blob/main/agent/tools.py): tooling · Schema inspection and SQL execution boundary. · `agent/tools.py`
- [Reasoning loop](https://github.com/sherman94062/retail-sage/blob/main/agent/agent.py): agent runner · Multi-turn Claude tool-use orchestration that returns a structured result. · `agent/agent.py`
- [Seed memory](https://github.com/sherman94062/retail-sage/blob/main/scripts/04_seed_memory.py): script · Populates semantic memory from repository knowledge. · `scripts/04_seed_memory.py`

### UX and eval

Interactive app, benchmarking, and quality checks.

- [Streamlit app](https://github.com/sherman94062/retail-sage/blob/main/ui/app.py): ui · Interactive chat with execution trace and usage details. · `ui/app.py`
- [Benchmark run](https://github.com/sherman94062/retail-sage/blob/main/scripts/05_run_benchmark.py): eval · Runs the 99-query TPC-DS benchmark. · `scripts/05_run_benchmark.py`
- [Agent benchmark](https://github.com/sherman94062/retail-sage/blob/main/agent/benchmark.py): eval core · Benchmark logic and scoring for agent performance. · `agent/benchmark.py`
- [Test suite](https://github.com/sherman94062/retail-sage/tree/main/tests): tests · Covers tools, memory, and prompt construction. · `tests`

## Connections

- Generate data → DuckDB warehouse: load data · Creates the local analytical dataset consumed by the warehouse.
- Generate data → Export Parquet: optional export · Can be followed by Parquet export for larger-scale use.
- Export Parquet → DuckDB warehouse: serve files · Exports partitioned files that remain queryable by the local stack.
- Generate data → Verify data: validate · Checks generated data against benchmark expectations.
- DuckDB warehouse → Staging models: source refs · Staging models read from the local warehouse source boundary.
- Staging models → Intermediate models: normalize · Source-aligned tables are reshaped into shared business concepts.
- Intermediate models → Retail marts: publish marts · Intermediate logic feeds facts, dimensions, and derived metrics.
- Retail marts → Semantic models: define metrics · Marts underpin governed metric definitions.
- Semantic models → Context build: schema context · Semantic and schema metadata inform the agent's context building.
- Retail marts → Context build: table metadata · Warehouse structure is surfaced to the agent for reasoning.
- Context build → Prompt policy: assemble prompt · Context is combined with prompt policy before tool use.
- Prompt policy → Reasoning loop: guide behavior · Prompting shapes the multi-turn reasoning loop.
- Semantic memory → Reasoning loop: retrieve knowledge · Prior schema and query history are consulted during analysis.
- Seed memory → Semantic memory: populate · Seeds the memory store with repository-specific knowledge.
- Reasoning loop → Agent tools: call tools · The agent iterates through schema inspection and SQL execution.
- Agent tools → DuckDB warehouse: run sql · Tools execute against the local warehouse boundary.
- Reasoning loop → Streamlit app: respond · Structured results and traces are surfaced in the interactive app.
- Agent benchmark → Reasoning loop: score agent · Benchmarking exercises the agent on TPC-DS questions.
- Benchmark run → Agent benchmark: invoke · Script entrypoint runs the benchmark harness.
- Test suite → Agent tools: verify behavior · Tests cover tool execution and schema inspection.
- Test suite → Semantic memory: verify behavior · Tests cover memory CRUD and retrieval.
- Test suite → Prompt policy: verify behavior · Tests cover prompt construction and policy.
