Metadata-Version: 2.4
Name: de-agentic
Version: 2.1.3
Summary: Dependency-light CLI for data engineers: profile files, run read-only SQL, read schemas, and ask a local or cloud LLM.
Author: DE Agentic Team
License-Expression: MIT
Project-URL: Homepage, https://github.com/longbuivan/llm-based-data-engineering-agents
Project-URL: Documentation, https://github.com/longbuivan/llm-based-data-engineering-agents/blob/main/docs/CONFIGURATION.md
Project-URL: Repository, https://github.com/longbuivan/llm-based-data-engineering-agents
Project-URL: Issues, https://github.com/longbuivan/llm-based-data-engineering-agents/issues
Project-URL: Changelog, https://github.com/longbuivan/llm-based-data-engineering-agents/releases
Keywords: data-engineering,sql,data-profiling,schema,llm,clickhouse,snowflake,duckdb,sqlite,cli,local-llm,read-only
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Information Technology
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Scientific/Engineering :: Information Analysis
Classifier: Topic :: Software Development :: Quality Assurance
Classifier: Topic :: Utilities
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pyyaml>=6.0
Requires-Dist: pydantic>=2.0
Provides-Extra: pretty
Requires-Dist: rich>=13.0; extra == "pretty"
Provides-Extra: data
Requires-Dist: duckdb>=0.9; extra == "data"
Requires-Dist: pandas>=2.0; extra == "data"
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: ruff>=0.6; extra == "dev"
Requires-Dist: build>=1.0; extra == "dev"
Requires-Dist: twine>=5.0; extra == "dev"
Provides-Extra: test
Requires-Dist: sqlparse>=0.4; extra == "test"
Requires-Dist: loguru>=0.7; extra == "test"
Requires-Dist: python-dotenv>=1.0; extra == "test"
Provides-Extra: legacy
Requires-Dist: typer>=0.9; extra == "legacy"
Requires-Dist: rich>=13.0; extra == "legacy"
Requires-Dist: duckdb>=0.9; extra == "legacy"
Requires-Dist: pandas>=2.0; extra == "legacy"
Requires-Dist: langchain>=0.2; extra == "legacy"
Requires-Dist: langchain-anthropic>=0.1; extra == "legacy"
Requires-Dist: langchain-openai>=0.1; extra == "legacy"
Requires-Dist: langchain-community>=0.0; extra == "legacy"
Requires-Dist: loguru>=0.7; extra == "legacy"
Requires-Dist: sqlalchemy>=2.0; extra == "legacy"
Requires-Dist: sqlparse>=0.4; extra == "legacy"
Requires-Dist: python-dotenv>=1.0; extra == "legacy"
Dynamic: license-file

# DE Agentic

A dependency-light CLI for data engineers. Profile data files, run read-only
SQL, read schemas and relationships, and ask a local or cloud LLM questions
about your data.

**Runs on the standard library alone.** No API key, no Docker, no
`pip install` needed to try it.

```bash
pip install de-agentic
de init-db
de demo
```

---

## ▶ Start here

**Thirty seconds, no install:**

```bash
python3 de.py demo
```

Creates a sample database, profiles a file, runs a query, reads the schema and
answers a question — standard library only.

Prefer a `de` command on your PATH?

```bash
pip install de-agentic
de demo
```

The four commands that matter:

```bash
de profile customers.csv                      # column types, nulls %, ranges
de query  "SELECT * FROM customers LIMIT 5"    # read-only SQL
de schema                                     # tables, columns, relationships
de ask   "how do I find null values?"         # your model, or offline help
```

`profile`, `query`, `schema` and `init-db` never contact a model and never
need one. `ask` falls back to local guidance when no model is configured.

Plug in a real model whenever you want — llama.cpp, Ollama, OpenAI or Anthropic:

```bash
de doctor      # which endpoints are reachable? (read-only, ~1s)
de setup       # detect one and write .env
de demo --ai   # full walkthrough, step 5 answered by your model
de tc          # the model writes SQL, we run it and check the answer
```

llama.cpp, Ollama and OpenAI all speak the same OpenAI-compatible
`/v1/chat/completions`, so one code path covers them. Precedence is
**flags > environment > `.env` > `config.yaml` > defaults**.

---

## What it does

| Command | What it does | Needs a model? |
|---|---|---|
| `de profile <file>` | types, null %, ranges, distinct values, patterns | no |
| `de query "<sql>"` | read-only SQL, with a row limit | no |
| `de schema` | tables, columns, nullability, likely relationships, index suggestions | no |
| `de ask "<question>"` | answers from the schema, grounded in real values | yes (falls back to offline tips) |
| `de tc` | end-to-end: model writes SQL, we run it, compare to a reference | yes |
| `de doctor` | which model endpoints are reachable | no |
| `de setup` | detect a model server and write `.env` | no |
| `de init-db` | create the sample SQLite database | no |
| `de demo` | all of the above, end to end | optional (`--ai`) |
| `de shell` | interactive mode | optional |

**Everything is read-only.** `INSERT`, `UPDATE`, `DELETE`, `DROP` and friends
are rejected with an explanation before the connection runs, and SQLite is
opened with `mode=ro`. Identifiers are always quoted, so a table name can never
become injected SQL.

### Databases

`sqlite`, `duckdb`, `clickhouse_cloud` and `snowflake`. There is **no** Postgres,
MySQL or MongoDB support.

```bash
de schema -d ./warehouse.db      # any local SQLite or DuckDB file
de schema -d configured          # the `database:` block of config.yaml
```

Passwords are never stored: the YAML holds the *name* of an environment
variable, and the connection fails with a clear message if it is unset.

---

## 📖 Full getting-started guide: [QUICKSTART.md](QUICKSTART.md)

> **Which docs are current.**
>
> Start with **Quick Start** and **Usage** below — they are verified against a
> real install.
>
> Current reference docs: **[docs/CONFIGURATION.md](docs/CONFIGURATION.md)**
> (models and databases), **[docs/API.md](docs/API.md)** (Python),
> **[docs/EXTENDING.md](docs/EXTENDING.md)**, and
> **[docs/TROUBLESHOOTING.md](docs/TROUBLESHOOTING.md)**.
>
> `docs/COMMAND_REFERENCE.md`, `docs/TESTING.md`, `docs/QUICK_TEST_REFERENCE.md`,
> `docs/DEPLOYMENT.md`, `docs/MODEL_SELECTION.md`, `docs/AGENT_MODES_OPTIONS.md`
> and `docs/FIX_CLI_OPTIONS.md` still document the legacy
> `python -m src.cli run ...` interface, which needs the optional `legacy`
> extra. They are kept for reference while that tree is migrated.
>
> `docs/MIGRATION.md` is the guide for moving off them, and
> `docs/QUALITY_REPORT.md` / `docs/USER_TESTING.md` are dated records of the
> simplification work, not living instructions.


## Project history

This began as a LangChain-based agent system with task modes for ingestion,
modeling, warehousing, debugging and architecture review. That design needed a
heavy dependency tree, so it was simplified into the `de` CLI you see above.

The earlier implementation is still in the tree as `src/`, behind the optional
`legacy` extra, and is kept for reference while it is migrated. It is not
required for anything in this README, and new work should target `de`.

`core/`, `operations/`, `ai/` and `cli.py` contain **no LangChain, torch or
vendor SDK** — every provider is reached over plain HTTP.

## 🚀 Quick Start

### Prerequisites
- Python 3.9 or newer
- Optional: [Ollama](https://ollama.ai) or another local model server
- Optional: an API key for OpenAI or Anthropic
- Optional: Docker, for the container image

### Install

```bash
pip install de-agentic
```

That is the whole thing. The base install depends only on `pyyaml` and
`pydantic`; `profile`, `query`, `schema` and `init-db` then run on the
standard library alone. Two extras are available:

```bash
pip install "de-agentic[pretty]"   # rich tables (falls back to plain text)
pip install "de-agentic[data]"     # DuckDB files, Parquet/Excel profiling
```

`de` is now on your PATH. Check it:

```bash
de --version
```

Prefer not to install anything? `python3 de.py demo` runs the same
walkthrough from a clone using only the standard library.

### Create the sample database

The commands below read `sample.sqlite`, which is generated on demand:

```bash
de init-db      # customers, products, orders, order_items
```

### Everyday commands

```bash
de profile customers.csv                       # column types, nulls, ranges
de query  "SELECT * FROM customers LIMIT 5"    # read-only SQL
de schema                                      # tables, columns, relationships
de ask    "how do I find null values?"         # answered by your model
```

`ask` works offline too: without a model it falls back to local guidance and
tells you how to configure one.

### Connect a model

```bash
de doctor      # which endpoints are reachable?
de setup       # detect one and write .env
```

Then re-run `de ask`, or try the full walkthrough with a real model:

```bash
de demo        # end to end
de demo --ai   # same, with step 5 answered by your model
```

llama.cpp, Ollama and OpenAI all speak the same OpenAI-compatible
`/v1/chat/completions`, so one code path covers them. Precedence is
**environment > `.env` > `config.yaml` > defaults**.

Per-command overrides are available when you want to switch providers
without editing configuration:

```bash
de ask "..." --provider openai --model gpt-4o-mini
```

### Using Docker

#### Quick Start with Docker Compose

```bash
# Build and start all services (app + postgres + ollama)
docker compose up -d

# Check status
docker compose ps

# View logs
docker compose logs -f de-agentic
```

#### Test the Deployment

This stack exists for the legacy agent interface, which needs the optional
`legacy` extra that the base image does not install. If you only want the
`de` commands, use the single-container form below instead.

```bash
# Interactive shell with the CLI (requires -it)
docker compose exec -it de-agentic de --help

# Create the sample database and inspect it
docker compose exec de-agentic de init-db
docker compose exec de-agentic de schema
docker compose exec de-agentic de query "SELECT * FROM customers LIMIT 5"
```

#### Single Container (No Dependencies)

The image installs this package from source, so the commands inside the
container are the same `de` commands:

```bash
# Build the image
docker build -t de-agentic .

# Standard-library walkthrough, no model required
docker run --rm de-agentic de demo

# Or use the CLI directly
docker run --rm de-agentic de schema
docker run --rm de-agentic de query "SELECT 1 AS x"

# Profile a local file (mount volume)
docker run --rm -v "$PWD/customers.csv:/data/customers.csv" \
  de-agentic de profile /data/customers.csv
```

Note: the container's default command is `de demo`, not the legacy
`python -m src.cli`, which needs optional dependencies the base image
does not install.

**📚 For complete deployment guide, see [docs/DEPLOYMENT.md](docs/DEPLOYMENT.md)**

## 📖 Usage

### Command Line Interface

#### Core modes (free, instant, no model required)

These run on the standard library and need no model server:

```bash
de init-db                                       # create sample.sqlite
de query  "SELECT COUNT(*) FROM customers"       # read-only SQL
de schema                                        # tables, columns, relationships
de profile customers.csv                         # column types, nulls, ranges
```

#### Model-backed commands

`ask` and `demo --ai` route through whichever provider is configured. Use
`de doctor` to see what is reachable and `de setup` to configure one.

```bash
de ask "which tables have no primary key?"       # answered by your model
de demo --ai                                     # full walkthrough, model answers
de tc                                            # end-to-end test of the SQL path
```

Select a provider per command without editing any config file:

```bash
de ask "..." --provider openai --model gpt-4o-mini
de ask "..." --provider ollama  --model llama3.2:1b
```

> 📖 **[docs/CONFIGURATION.md](docs/CONFIGURATION.md)** is the full reference:
> which file holds what, every `LLM_*` and `database:` key, ClickHouse Cloud
> and Snowflake, and how to check what resolved. Worth reading once — the
> `database:` block is read from your current directory while `.env` is read
> from the install directory, which surprises people.

#### Legacy CLI

The earlier agent-mode interface (`run query`, `run debug`, `run reverse`,
...) is still in the tree as `src.cli`, but it is mid-migration and needs the
optional `legacy` extra:

```bash
pip install "de-agentic[legacy]"
de-agentic --help
```

Running `de-agentic` without that extra prints one line naming what is missing
and exits with status 2 — it does not raise a traceback. `de` itself needs no
extra and is unaffected.

It is not required for anything documented above. New code should target the
`de` commands.

#### Legacy agent modes

Available only with the `legacy` extra, via `de-agentic`. Listed for
reference; these are mid-migration and not covered by CI.

```bash
de-agentic run debug   --error="Connection timeout"
de-agentic run quality --file=data.csv --model=gpt-4o-mini
de-agentic run model   --description="E-commerce system"
de-agentic run ingest  --source="API" --target="postgres"
de-agentic run warehouse --requirements="Real-time analytics"
de-agentic run architect --description="Current pipeline"
```

Per-command `--model` works the same way here as with `de`.

### Python API

The same operations the CLI uses, with no model and no LLM framework required.
Library modules return data; the CLI only renders it.

```python
from operations.profiling import profile_file
from operations.query import run_query
from operations.schema import describe_database
from core.database import Database

p = profile_file("customers.csv")
p["row_count"], p["columns"]            # 40, ['customer_id', 'name', ...]
p["profiles"][0]                        # {'column': 'customer_id', 'type': 'int',
                                       #  'nulls': 0, 'null_pct': '0.0%',
                                       #  'distinct': 40, 'min': '1', ...}

result = run_query("sample.sqlite", "SELECT * FROM orders LIMIT 10")
result["columns"], result["rows"], result["truncated"], result["row_count"]

described = describe_database("sample.sqlite")
described["tables"][0]["table"], described["tables"][0]["columns"]
described["relationships"]              # guessed from `<table>_id` columns
described["index_suggestions"]

with Database("sample.sqlite") as db:          # read-only, context-managed
    db.tables(), db.columns("orders"), db.count("orders")
```

📖 **[docs/API.md](docs/API.md)** documents every function and its return shape.

The `src.agents` / `src.tasks` classes that older examples reference need the
`legacy` extra and are **not** part of the supported API.

### Available Models

Any OpenAI-compatible server works, plus Anthropic. The model id must match
what the server reports at `GET {base_url}/models` — `de doctor` lists them.

| Provider | Default model | Cost | Notes |
|---|---|---|---|
| **Ollama** | `llama3.2:1b` | free | local, fully offline |
| **llama.cpp** | *(yours)* | free | local, any GGUF file |
| **OpenAI** | `gpt-4o-mini` | paid | fast, good default for daily work |
| **Anthropic** | `claude-3-5-sonnet-latest` | paid | long context |

Pricing changes often — check each provider's own pricing page rather than
trusting a table in a README.

## 🏗️ Architecture

```
de-agentic/
├── src/
│   ├── agents/          # Agent implementations
│   ├── tasks/           # Task definitions
│   ├── skills/          # Reusable skills
│   ├── tools/           # Integration tools
│   ├── workflows/       # Workflow orchestration
│   └── utils/           # Utilities
├── config/              # Configuration files
├── examples/            # Usage examples
├── tests/               # Test suite
└── docs/                # Documentation
```

## 🔧 Configuration

### LLM Provider Configuration (.env)

```bash
# Default: Local Ollama (free, offline)
LLM_PROVIDER=ollama
OLLAMA_BASE_URL=http://localhost:11434
OLLAMA_MODEL=llama2

# OpenAI (requires API key)
LLM_PROVIDER=openai
OPENAI_API_KEY=sk-proj-...
OPENAI_MODEL=gpt-4o-mini

# Anthropic (requires API key)
LLM_PROVIDER=anthropic
ANTHROPIC_API_KEY=sk-ant-...
ANTHROPIC_MODEL=claude-3.5-sonnet
```

### Agent Configuration (config/agent_config.yaml)

Edit `config/agent_config.yaml` to customize:
- Enable/disable specific tasks and skills
- Adjust mental model parameters
- Configure database connections
- Set execution limits
- Fine-tune agent behavior

### Database Setup

The `demo.db` DuckDB database includes:
- **customers** table: 10 sample customers
- **orders** table: 30 sample orders
- **products** table: 10 sample products
- Total revenue: $6,813.97

Recreate anytime with: `de init-db --force`

## 🤝 Contributing

Contributions are welcome! Please read our contributing guidelines first.

## 📚 Documentation

Comprehensive guides available in the `docs/` directory:

- **[QUICKSTART.md](QUICKSTART.md)**: 5-minute getting started guide
- **[DEPLOYMENT.md](docs/DEPLOYMENT.md)**: Complete deployment and testing guide (Docker, remote, production)
- **[MODEL_SELECTION.md](docs/MODEL_SELECTION.md)**: Complete guide to choosing and using different LLM models
- **[COMMAND_REFERENCE.md](docs/COMMAND_REFERENCE.md)**: All CLI commands with examples
- **[AGENT_MODES_OPTIONS.md](docs/AGENT_MODES_OPTIONS.md)**: Detailed comparison of all 10 execution modes
- **[DESIGN.md](DESIGN.md)**: Architecture and design principles

## 🎯 Use Cases

### Daily Data Engineering Tasks
- Query databases without remembering SQL syntax
- Profile new data files instantly
- Debug pipeline errors with AI assistance
- Generate data models from requirements

### Cost Optimization Strategy
```bash
# Development/Testing (free, local)
--model=llama2

# Daily work (cheap, cloud)
--model=gpt-4o-mini

# Critical issues (premium, best quality)
--model=gpt-4
```

### Example workflow

```bash
# 1. Profile a new file (free, instant, no model)
de profile new_data.csv

# 2. Read the schema
de schema

# 3. Run a check with a cheap model
de ask "does new_data.csv have quality issues?" --model gpt-4o-mini

# 4. Escalate to a stronger model for a hard question
de ask "how should I fix the encoding problem?" --model gpt-4

# 5. Verify with SQL (free, instant)
de query "SELECT count(*) FROM new_data"
```

## 🚀 Recent Updates

### v2.1.3 - Config discovery fixed for installed users
- ✅ `.env` and `config.yaml` are now resolved from the **working directory**
  first, instead of the package directory. With a pip install that was
  `site-packages/`, so a project-root `config.yaml` and `.env` were silently
  ignored — the `llm:` section fell back to provider `echo` with no warning
  while `database:` still worked, because it uses a different loader
- ✅ `de setup` writes `.env` to the working directory and reports the real path
- ✅ PyPI metadata: description, keywords and classifiers now describe this
  package; the project URLs pointed at a `yourusername` placeholder repo
- ✅ `License ::` classifier removed — it is rejected under PEP 639 and broke
  the build for anyone installing from source
- ✅ README no longer credits LangChain, Typer, SQLAlchemy, NumPy, Great
  Expectations or SQLGlot, none of which the shipped code uses; the Python API
  example now uses the modules that actually import without the `legacy` extra
- ✅ LLM tests isolated from the developer's own `.env`, so they no longer fail
  on a configured machine

### v2.1.2 - Bug fixes and configuration docs
- ✅ `de-agentic` no longer dies with a `ModuleNotFoundError` traceback when the `legacy` extra is absent; it names the missing packages, prints the `pip install` line and exits 2
- ✅ Table headers are no longer truncated with U+2026, which rendered as `customer…` on non-UTF-8 Windows consoles; values wrap instead
- ✅ `de init-db --db configured` creates the configured file instead of one literally named `configured`
- ✅ `database.path` now defaults to `sample.sqlite`, matching the CLI, so `--db configured` points at the file `init-db` creates
- ✅ Removed a duplicated `path`/`timeout` declaration in `DatabaseConfig`
- ✅ `.env.example` rewritten: it advertised Postgres, MySQL, MongoDB and HuggingFace, none of which are supported
- ✅ New `docs/CONFIGURATION.md` covering both model and database setup, and it documents which `config.yaml` sections are read at runtime
- ✅ `test_config.py` is isolated from the developer's shell; exported `LLM_*` variables no longer fail it

### v2.1.1 - Documentation accuracy
- ✅ Recent Updates now covers the 2.1.x line instead of stopping at v1.2.0
- ✅ Added the MIT `LICENSE` file that `pyproject.toml` and the README referenced
- ✅ Legacy reference docs are banner-marked and point at `MIGRATION.md`
- ✅ Release workflow publishes only for strict `vX.Y.Z` tags

### v2.1.0 - Packaging, CI and install docs
- ✅ GitHub Actions: lint, a 3.9-3.12 test matrix, and a build that verifies the wheel
- ✅ Tag-driven release: publish to PyPI via trusted publishing, plus a GitHub release
- ✅ The wheel now declares its dependencies; `import core.config` works after install
- ✅ `harness` data files and the `src` package ship correctly
- ✅ `de doctor` no longer crashes when no model is configured
- ✅ README and Dockerfile now match what actually installs and runs

### v1.2.0 - Model Selection Feature
- ✅ Added `--model` parameter for per-command model selection
- ✅ Auto-provider detection (gpt* → openai, claude* → anthropic)
- ✅ Cost optimization through flexible model selection
- ✅ Comprehensive documentation (docs/MODEL_SELECTION.md)

### v1.1.0 - Local Setup & CLI Enhancement
- ✅ DuckDB local database with sample data
- ✅ Ollama integration for local LLM support
- ✅ Legacy execution modes (4 core + 6 agent, behind the `legacy` extra)
- ✅ Enhanced CLI with rich terminal output
- ✅ Upgraded to Typer 0.21.0 for better compatibility

### v1.0.0 - Initial Release
- ✅ 7 task categories with modular architecture
- ✅ Mental model principles (ReAct, CoT, Planning, Reflection, Memory)
- ✅ Multiple LLM provider support
- ✅ Docker deployment support

## 📄 License

MIT License - see LICENSE file for details

## 🙏 Acknowledgments

The `de` CLI is written against the **standard library** — no LLM framework, no
vendor SDK. Optional, used only when installed:

- **[rich](https://github.com/Textualize/rich)** — pretty tables (`[pretty]` extra)
- **[duckdb](https://github.com/duckdb/duckdb)** — DuckDB file support (`[data]` extra)
- **[pandas](https://github.com/pandas-dev/pandas)** — Parquet and Excel profiling (`[data]` extra)

Model servers and providers are spoken to over plain HTTP:
[llama.cpp](https://github.com/ggml-org/llama.cpp),
[Ollama](https://ollama.ai), OpenAI and Anthropic. No client library is used for
any of them.

The earlier LangChain-based implementation lives on in `src/` behind the
`legacy` extra; its dependencies are not needed by `de`.

## 💡 Pro Tips

1. **Most commands need no model.** `profile`, `query`, `schema` and `init-db`
   are free and instant — reach for `ask` only when you want natural language.
2. **`de tc` verifies the model, not just the prompt.** It makes the model write
   SQL, runs it, and compares against a reference query, so a plausible-looking
   wrong answer fails instead of passing.
3. **Cost control.** `--model gpt-4o-mini` for daily work, a larger model for
   hard questions — per command, no config edit.
4. **Fully offline.** Point `LLM_PROVIDER=ollama` (or `llamacpp`) at a local
   server and nothing leaves your machine.
5. **Read-only is enforced, not promised.** Writes are rejected before the
   connection runs, so you cannot mutate a warehouse by accident.

## 🐛 Troubleshooting

**Not sure what's configured?**

```bash
de doctor          # which endpoints answer, and what is currently set
de doctor --json   # same, machine-readable; exit 1 if unreachable
de setup --dry-run # what a config change would write
```

**Ollama not responding**

```bash
ollama list             # is it running, and do you have a model?
ollama pull llama3.2:1b # the model id must match what the server reports
```

**`HTTP 404 (model not found)`** — the model id does not match what the server
lists at `GET {base_url}/models`. `de doctor` prints them.

**Database problems**

```bash
de init-db --force     # recreate the sample database
de schema              # list the real table names (identifiers are case-sensitive)
```

**SQL must be quoted**

```bash
de query "SELECT * FROM customers LIMIT 5"
```

📖 **[docs/TROUBLESHOOTING.md](docs/TROUBLESHOOTING.md)** has the full list.

## 📞 Support

- 📖 **[docs/](docs/)** — start with [CONFIGURATION.md](docs/CONFIGURATION.md)
- 🐛 [Issues](https://github.com/longbuivan/llm-based-data-engineering-agents/issues)

---

**Ready to get started?**

```bash
pip install de-agentic
de init-db
de demo
```
# llm-based-data-engineering-agents
