<p align="center">
  <h1 align="center">AdvancedCalc Engine</h1>
  <p align="center">Agentic Excel analysis and calculation platform</p>
  <p align="center">
    <a href="https://github.com/surajpanwar/advanced-calc-engine/actions/workflows/ci.yml">
      <img src="https://github.com/surajpanwar/advanced-calc-engine/actions/workflows/ci.yml/badge.svg" alt="CI">
    </a>
    <img src="https://img.shields.io/badge/python-3.11-blue" alt="Python 3.11">
    <img src="https://img.shields.io/badge/tests-754%20passing-green" alt="Tests">
    <img src="https://img.shields.io/badge/license-MIT-blue" alt="License: MIT">
  </p>
</p>

---

## Overview

Excel workbooks are the de-facto tool for ad-hoc analysis — and the hardest
to automate safely. AdvancedCalc Engine is an **agentic workflow system
built on secure MCP tooling**: it inspects a workbook, plans a calculation,
executes it through whitelisted MCP tools, writes results back safely, and
validates the outcome — with a full audit trail and a backup before every
modification.

The workflow is **deterministic**: a task is decomposed into an Architect →
Engineer → Validator pipeline by rule-based code in `app/workflow/`, with
no LLM in the loop. A hardened **MCP server** exposes fifteen whitelisted
tools that the workflow executes. Safety is the product: path confinement,
formula sanitization, mandatory backups, and audit logging are enforced in
code, not by convention.

This is an end-to-end example of a deterministic agentic engineering
pipeline: structured plan/execute/verify orchestration, typed tool
contracts, defense-in-depth security, and automated verification. The
current implementation requires no LLM; a future phase could add
LLM-driven planning without changing the tool surface or the validation
gates.

## Key Features

- **Fifteen whitelisted MCP tools** over stdio — read-only metadata,
  calculation and analytics tools plus two guarded mutation tools and the
  agentic workflow runner.
- **Multi-agent architecture** — four OpenCode subagents with strict
  separation of concerns (plan / execute / verify / audit), formalized as
  a deterministic in-process workflow with per-run correlation ids.
- **Safe Excel mutation** — a timestamped backup is created before every
  write; the workbook is reopened and validated afterwards.
- **Security-first design** — paths are confined to `data/input`, formulas
  and cell references are sanitized, arbitrary code execution is impossible.
- **Audit trail** — every operation, success or failure, is recorded as a
  JSONL event with no secrets or workbook contents; workflow runs share a
  single `workflow_id` across all their events.
- **Typed contracts** — Pydantic request/response models for every tool.
- **Agentic workflow runner** — a natural-language task is parsed into an
  execution plan, executed, and independently validated without an LLM;
  the same pipeline is exercised end to end by `advanced-calc demo` and by
  the agent evaluation harness.
- **754-test suite** with 98% coverage, plus a live MCP smoke test.
- **CLI** (`advanced-calc`) for serving, smoke-testing, auditing,
  validating, running the agentic demo, and evaluating the ML layer
  (`ml-eval`) — CI runs on Linux and Windows with a 90% coverage gate.

## Architecture

```
┌────────────────────────────────────────────────────────────────┐
│                     OpenCode agent orchestration                │
│                                                                 │
│  Architect ──plan──▶ Engineer ──modify──▶ Validator             │
│      │                  │                     │                 │
│      └──────────────────┴─── audit events ────┴──▶ Audit log    │
│                                                                 │
│              Security Auditor (reviews, never modifies)         │
└──────────────────────────────┬─────────────────────────────────┘
                               │ MCP (stdio)
┌──────────────────────────────▼─────────────────────────────────┐
│                    MCP server (app/mcp_server.py)               │
│  read_excel_metadata   rolling_average    percentage_change     │
│  rolling_volatility    write_formulas     validate_workbook     │
│  summary_statistics    correlation_matrix anomaly_detection     │
│  linear_forecast       ml_forecast        ml_anomaly_detection  │
│  data_quality_report   write_output                             │
│  run_analysis_workflow (Architect → Engineer → Validator)       │
└──────────────────────────────┬─────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────┐
│                     Application layer (app/tools)               │
│  Calculation engine   Excel metadata   Formula writer           │
│  Workbook validator                                            │
└──────────────────────────────┬─────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────┐
│                      Core layer (app/core)                      │
│  security.py   backup.py   audit.py   config.py                 │
└─────────────────────────────────────────────────────────────────┘
```

The layering is strict: `mcp_server.py` contains transport logic only.
Excel business logic lives in `app/tools`, and every cross-cutting concern
(security, backups, audit, configuration) lives in `app/core`. Agents
never touch the filesystem directly — they only call MCP tools.

## Agentic Workflow

| Agent | Role | Can modify? |
|---|---|---|
| Architect | Understands the request, inspects workbook structure, produces an execution plan | No |
| Engineer | Executes the approved plan through the MCP tools, verifies modifications | Approved cells only |
| Validator | Independently verifies results and reports PASS/FAIL | No |
| Security Auditor | Reviews the application for vulnerabilities and unsafe tool usage | No |

The workflow is enforced by agent definitions in `.opencode/agents/` and
formalized as deterministic code in `app/workflow/` (no LLM required):

1. **Architect** (`app/workflow/architect.py`) analyzes the workbook via
   `read_excel_metadata`, parses the task with `task_parser.py`, and
   produces a numbered execution plan plus a validation spec. It never
   modifies anything.
2. **Engineer** (`app/workflow/engineer.py`) executes only whitelisted
   plan steps through the MCP tool handlers (write steps use `write_output`
   with automatic backup), and records every outcome.
3. **Validator** (`app/workflow/validator.py`) independently recomputes the
   result — raw openpyxl cell reads and pure-Python math, sharing no code
   path with the calculation engine — and reports PASS or FAIL without
   ever modifying the workbook.
4. The **orchestrator** (`app/workflow/orchestrator.py`) runs the whole
   pipeline, generates a per-run UUID `workflow_id`, and threads it through
   every audit event of the run.
5. Every step is recorded in the audit log; the **Security Auditor**
   reviews the tool surface and configuration for risk.

Planning failures (unsupported or ambiguous tasks) raise structured
`ACE_*` errors; execution or validation failures are returned as a `FAIL`
response with the validator's reason.

The pipeline is fully deterministic today — planning, execution, and
validation are plain Python with no LLM in the loop. A future phase could
replace the rule-based planner with LLM-driven reasoning while keeping the
same whitelisted tool surface, structured `ACE_*` errors, and independent
validation gates.

The repository's `AGENTS.md` codifies the core rules: analyze before
modifying, never blindly modify a workbook, always back up before
destructive operations, validate after modification, never ignore errors,
and never execute arbitrary user code.

## MCP Tools

The MCP server (`python -m app.mcp_server`) exposes fifteen tools:

| Tool | Description | Mutates? |
|---|---|---|
| `read_excel_metadata` | Read workbook structure: sheets, columns, sample rows | No |
| `rolling_average` | Rolling mean of a column (configurable window, default 14) | No |
| `percentage_change` | Period-over-period percentage change of a column | No |
| `rolling_volatility` | Rolling standard deviation of percentage change | No |
| `write_formulas` | Write Excel formulas into a sheet; backup is created first | Yes |
| `validate_workbook` | Validate sheet existence and expected cells | No |
| `summary_statistics` | Count, missingness, mean, median, std, min/max, quartiles | No |
| `correlation_matrix` | Pearson correlation matrix of numeric columns | No |
| `anomaly_detection` | z-score or IQR anomaly detection on a column | No |
| `linear_forecast` | Linear trend fit (numpy) plus future period forecasts | No |
| `ml_forecast` | Seeded ML forecast with temporal holdout evaluation against persistence/mean/linear baselines | No |
| `ml_anomaly_detection` | Seeded isolation-forest anomaly detection with optional injected-anomaly precision/recall/F1 evaluation | No |
| `data_quality_report` | Per-column type, completeness, uniqueness, numeric stats | No |
| `write_output` | Write computed rows into `data/output`; backup before overwrite | Yes |
| `run_analysis_workflow` | Run a task through the Architect → Engineer → Validator pipeline | No* |

\* The workflow itself never mutates the source workbook; a task that asks
to write results produces a new file under `data/output`.

`ml_forecast` fits a seeded bagged-ridge forecaster on causal lag and
rolling features, splits the series temporally (train prefix, holdout
suffix), reports RMSE / MAE / MAPE / R² for the model and the
persistence, mean and linear baselines on the same holdout, and
recursively forecasts future periods. The seed, test fraction, minimum
points and row cap come from the `ACE_ML_*` settings; when fewer than
two supervised training rows survive feature warm-up it falls back
deterministically to the same linear trend fit used by
`linear_forecast`.

`ml_anomaly_detection` grows a seeded isolation forest (subsampled,
random-threshold isolation trees driven entirely by
`numpy.random.default_rng(seed)`) and flags the most isolated
`contamination` share of a column's values, ties broken by position so
the result is fully reproducible. With `evaluate=true` it injects known
far-outlier anomalies on an in-memory copy of the series, reruns the
detection, and reports precision, recall and F1 against the injected
labels — the workbook is never touched. The seed, minimum points and
row cap come from the `ACE_ML_*` settings; insufficient data raises a
structured `ACE_CALCULATION` error.

All inputs are validated through Pydantic request models; all outputs are
serialized through typed response models with explicit JSON-safe
conversion (NumPy scalars, timestamps, and non-finite floats). Failures
are returned as structured MCP errors carrying an `ACE_*` taxonomy code
(`ACE_VALIDATION`, `ACE_SECURITY`, `ACE_NOT_FOUND`, `ACE_CALCULATION`)
instead of raw exception strings.

## ML Evaluation Harness (`ml-eval`)

`advanced-calc ml-eval` runs the complete read-only ML evaluation of a
workbook column in one command and prints the metrics tables:

```bash
advanced-calc ml-eval
# options: --file, --sheet, --column, --periods, --window, --seed,
#          --output, --no-artifact
```

It reuses the exact same deterministic pipelines as the `ml_forecast`
and `ml_anomaly_detection` MCP tools (`app/ml/harness.py`): the seeded
bagged-ridge forecaster is evaluated on a temporal holdout (train prefix
/ test suffix, never shuffled) and compared against the persistence,
mean and linear baselines evaluated on the same holdout targets; the
seeded isolation forest injects known far outliers on an in-memory copy
of the series and reports precision, recall and F1 against them. A
reproducible JSON artifact (no timestamps, everything derived from the
seed) is written to `data/output/ml_evaluation.json` unless
`--no-artifact` is given, and the run is recorded in the audit log as an
`ml_eval` event.

### Measured results — `data/input/sales_data.xlsx` (seed 42)

The demo workbook holds 60 daily sales rows (2026-01-01 → 2026-03-01,
first 12 rows preserved from the original sample, rows 13–60 a rising
sales trend). With the default configuration (`ACE_ML_RANDOM_SEED=42`,
`ACE_ML_TEST_FRACTION=0.3`, lags 1–3, rolling window 7, 25 bagged ridge
estimators) the evaluation splits into 35 training / 17 holdout rows:

| Model | RMSE | MAE | MAPE % | R² |
|---|---|---|---|---|
| **ML model (bagged ridge)** | **600.144635** | **559.906208** | **1.099238** | **0.987647** |
| persistence | 1118.822911 | 1105.882353 | 2.230778 | 0.957067 |
| mean | 28648.482558 | 28135.014006 | 55.684029 | -27.149278 |
| linear | 13495.661774 | 13159.096333 | 25.941149 | -5.246725 |

Anomaly evaluation (isolation forest, `n_estimators=100`,
`max_samples=256`, `contamination=0.1`, 5 injected outliers): precision
**0.833333**, recall **1.000000**, F1 **0.909091** (6 points flagged, 5
of which are the injected outliers).

The first 12 rows repeat a flat pattern, so the mean and linear
baselines are badly wrong on the trend — while the ML model's causal lag
and rolling features track it with a 1.1% average error, roughly half
the persistence baseline's error. Every number above is fully
reproducible: the harness is deterministic for a fixed seed, and the
artifact contains the complete report (`data/output/ml_evaluation.json`).

### Model persistence decision

Phase 10.5 evaluated whether fitted estimators should be persisted to
`data/models/`. **It is not implemented**: both estimators are fully
deterministic for a given seed and are re-fit on demand (a stored model
could never differ from a fresh fit), the system is stateless with no
cross-request model reuse or serving path, and reproducibility is
already guaranteed by the seed plus the audit trail. Skipping
serialization removes the entire unsafe-deserialization surface
(pickle/joblib object graphs, user-supplied model artifacts) without
losing anything — see `SECURITY.md` for the full rationale.

## Security Model

Security is enforced in `app/core/security.py` and applies to every tool:

- **Path confinement** — every workbook path is resolved and must stay
  inside the configured `input_dir`. `..` traversal, drive-relative paths,
  cross-drive paths, and UNC paths outside the root are rejected
  (Windows-specific cases handled explicitly).
- **Formula sanitization** — formulas must start with `=`, are length- and
  control-character-checked, and may not contain DDE markers (`|`),
  external workbook references (`[`), or functions that exfiltrate data or
  execute commands (`HYPERLINK`, `WEBSERVICE`, `IMPORT*`, `EXEC`,
  `POWERSHELL`, `CALL`, `REGISTER`, …).
- **Cell reference validation** — references must match Excel bounds
  (columns up to `XFD`, rows up to `1048576`).
- **Whitelisted surface** — only the fifteen tools above are registered;
  arbitrary Python execution is never exposed to agents or users.
- **Limits** — formula length (512 chars) and cells per write (500) are
  configurable caps.

The repository's Security Auditor agent and `SECURITY.md` document the
threat model and reporting process.

## Backup & Audit Behavior

**Backups** (`app/core/backup.py`)

- `write_formulas` never touches a workbook without first creating a
  timestamped copy in `data/backups`.
- If any validation step fails *before* the backup, the workbook is never
  modified; a rejected write leaves no trace.

**Audit log** (`app/core/audit.py`)

- Every operation — reads, calculations, writes, backups, validations —
  is written to `logs/audit.jsonl` as a JSON line with timestamp, agent,
  operation, status, file, sheet, and details.
- Workflow runs carry a `workflow_id` on every event they produce, so a
  single analysis can be traced end to end.
- Failures are recorded together with their error message, then re-raised.
- The log never contains secrets, environment variables, workbook
  contents, or cell values.

Inspect the trail with the CLI:

```bash
advanced-calc audit
advanced-calc audit --workflow <workflow_id>
advanced-calc stats
```

## Installation

Requirements: Python 3.11+ and [uv](https://docs.astral.sh/uv/).

```bash
git clone https://github.com/surajpanwar/advanced-calc-engine.git
cd advanced-calc-engine
uv sync --dev
```

This creates the virtual environment, installs the package in editable
mode, and exposes the `advanced-calc` CLI.

Optional: copy `.env.example` to `.env` and adjust paths if you do not
want the defaults (directories inside the project root).

## Quickstart

Place a workbook in `data/input/` (a sample `sales_data.xlsx` with 60
daily rows is included).

Run the live MCP smoke test — it starts the server exactly as OpenCode
does, lists the fifteen tools, and reads the sample workbook:

```bash
advanced-calc smoke
```

Start the MCP server over stdio (used by any MCP client):

```bash
advanced-calc serve
# or
python -m app.mcp_server
```

Run the agentic workflow demo — the Architect parses the task, the
Engineer computes and writes the 5-day rolling volatility, and the
Validator independently checks the result:

```bash
advanced-calc demo
```

The demo prints the workflow trace, the generated `workflow_id`, and the
path of the output workbook in `data/output/`. Follow the trail with:

```bash
advanced-calc audit --workflow <workflow_id>
```

Validate a workbook from the CLI (relative paths resolve against
`data/input/`):

```bash
advanced-calc validate sales_data.xlsx Sheet1 A1 B1
```

Inspect the audit trail:

```bash
advanced-calc audit --tail 10
advanced-calc stats
```

Run the ML evaluation harness against the demo workbook and regenerate
the reproducible metrics artifact:

```bash
advanced-calc ml-eval
```

The command prints the forecast metrics table (RMSE / MAE / MAPE / R²
for the ML model and the persistence, mean and linear baselines), the
anomaly detection metrics (precision / recall / F1 against injected
outliers), and writes `data/output/ml_evaluation.json` — see
[ML Evaluation Harness](#ml-evaluation-harness-ml-eval) for the
measured results and methodology.

## Live Demo / Workflow Trace

Watch the full pipeline run end to end, then replay the audit trail for
that exact run:

```bash
advanced-calc demo
advanced-calc audit --workflow <WORKFLOW_ID>
```

`advanced-calc demo` runs a live task through the Architect → Engineer →
Validator pipeline and prints the trace — the plan, each executed step,
and an independent PASS result with the generated `workflow_id` and output
workbook:

![Workflow trace](docs/demo-workflow.png)

`advanced-calc audit --workflow <WORKFLOW_ID>` replays every event of that
single run — planning, calculations, writes, backups, validation — linked
by one shared `workflow_id`:

![Audit trail](docs/demo-audit.png)

What the demo proves:

- **Deterministic agent orchestration** — the same task always produces
  the same Architect → Engineer → Validator sequence, executed in code
  with no LLM in the loop.
- **Workflow_id correlation** — one id threads through every audit event,
  so an entire run is traceable end to end.
- **Validation before completion** — the run reports success only after
  the Validator independently recomputes and confirms the result.
- **Output workbook generation** — the task produces a real workbook in
  `data/output/`, written through the guarded, backup-first tools.
- **Auditable execution** — every step, success or failure, is recorded in
  `logs/audit.jsonl` for replay and inspection.

The workflow is fully deterministic today; optional LLM-driven planning
on top of the same whitelisted tool surface is future work.

## OpenCode MCP Setup

`opencode.json` registers the local MCP server with OpenCode:

```json
{
  "$schema": "https://opencode.ai/config.json",
  "default_agent": "plan",
  "mcp": {
    "advanced-calc-engine": {
      "type": "local",
      "command": [".venv/Scripts/python.exe", "-m", "app.mcp_server"],
      "cwd": ".",
      "enabled": true
    }
  }
}
```

The configuration is **portable** — it contains no machine-specific
paths. The `command` and `cwd` are resolved relative to the directory
where OpenCode is launched (the project root), so the only prerequisite
is the project virtual environment:

1. `uv sync --dev` must have been run (creates `.venv/`).
2. The bootstrap check is simply:

   ```bash
   .venv/Scripts/python.exe -m app.mcp_server   # Windows
   .venv/bin/python -m app.mcp_server           # macOS / Linux
   ```

If your environment differs (different venv location, no venv, or
macOS/Linux), update the `command` array accordingly — e.g.
`[".venv/bin/python", "-m", "app.mcp_server"]` on Unix. The fifteen MCP
tools are then available to OpenCode agents exactly as before.

> Note: `advanced-calc serve` is equivalent to launching
> `python -m app.mcp_server`; use whichever you prefer.

## Testing

The suite exercises every layer, from pure functions to a live MCP
handshake:

```bash
uv run pytest --cov=app --cov-report=term-missing --cov-fail-under=90
```

Coverage areas:

- Security: path traversal, drive/UNC edge cases, formula and cell
  reference sanitization, rejected writes leave no trace.
- Calculations: window validation, missing columns, numeric accuracy.
- Backups: collision handling, restore fidelity, no backup on failure.
- Audit: SUCCESS/FAIL events, error messages, no cell values in logs,
  workflow_id propagation.
- MCP handlers: request validation, JSON-safe serialization, audited
  failures.
- End-to-end: the full agent workflow including audit-trail assertions.
- Agent evaluation: ten deterministic golden cases (`tests/agent_eval/`)
  with known expected values, plus validator-independence tests that
  sabotage plans and workbooks to prove the Validator catches mistakes.
- ML evaluation: harness shape, determinism, guard propagation and
  fallback behaviour (`tests/test_ml_harness.py`), and CLI wiring,
  artifact writing, error handling and audit propagation for
  `ml-eval` (`tests/test_cli.py`).

## CI/CD

GitHub Actions (`.github/workflows/ci.yml`) runs on every push to `main`
and every pull request on **both `ubuntu-latest` and `windows-latest`**:

1. `uv sync --dev` — installs the project and dev toolchain.
2. `pytest` with coverage — **90% coverage gate** (`--cov-fail-under=90`).
3. `ruff check .` — lint (app code; tests are excluded by config).
4. `mypy app` — static type checking.

The Linux job additionally exercises the symlink-related path security
tests that skip on Windows.

## Project Structure

```
advanced-calc-engine/
├── app/
│   ├── cli.py                    # Typer CLI: serve, smoke, audit, stats, validate, demo, ml-eval
│   ├── mcp_server.py             # MCP transport; registers the 15 tools
│   ├── core/
│   │   ├── security.py           # Path/formula/cell-reference validation
│   │   ├── backup.py             # Mandatory pre-mutation backups
│   │   ├── audit.py              # JSONL audit trail (+ workflow_id context)
│   │   ├── errors.py             # ACE_* structured MCP error taxonomy
│   │   └── config.py             # ACE_-prefixed settings (pydantic-settings)
│   ├── models/                   # Pydantic request/response contracts
│   ├── ml/                       # Deterministic ML layer (evaluation,
│   │   │                         # features, forecast, anomaly detection,
│   │   │                         # evaluation harness)
│   ├── workflow/                 # Agentic orchestration (Phase 9.3)
│   │   ├── orchestrator.py       # run_analysis_workflow runner
│   │   ├── architect.py          # Plan builder (read-only)
│   │   ├── engineer.py           # Whitelisted executor
│   │   ├── validator.py          # Independent recomputation (PASS/FAIL)
│   │   ├── task_parser.py        # Deterministic natural-language parsing
│   │   └── models.py             # Plan/validation-spec models
│   └── tools/
│       ├── calculation_engine.py # Rolling average, % change, volatility
│       ├── analytics.py          # Summary stats, correlation, anomalies, forecast, quality
│       ├── excel_metadata.py     # Workbook structure reader
│       ├── formula_writer.py     # Safe formula writes (backup first)
│       ├── output_writer.py      # Safe output writes into data/output
│       └── workbook_validator.py # Sheet/cell validation
├── data/
│   ├── input/                    # Workbooks the tools may access
│   ├── output/                   # Computed outputs + ml_evaluation.json (gitignored)
│   └── backups/                  # Pre-mutation backups (gitignored)
├── logs/                         # audit.jsonl (gitignored)
├── tests/                        # 754 tests + MCP smoke test
│   └── agent_eval/               # Golden cases + validator independence
├── .opencode/agents/             # Architect, Engineer, Validator, Security Auditor
├── .github/workflows/ci.yml      # Linux + Windows CI
├── AGENTS.md                     # Agent rules and project conventions
├── pyproject.toml                # Metadata, pinned deps, entry point, tool config
└── main.py                       # Thin entry point delegating to the CLI
```

## Roadmap

- **Phase 9.2 — Product / ML surface**: summary statistics, correlation,
  anomaly detection (z-score / IQR), linear forecasting, data quality
  reports, output writing, structured `ACE_*` errors, overwrite guard
  and post-write verification. *(Implemented)*
- **Phase 9.3 — Agentic orchestration**: deterministic Architect →
  Engineer → Validator workflow, per-run `workflow_id` correlation
  across audit events, golden-case agent evaluation harness, and the
  `demo` CLI. *(Implemented)*
- **Phase 9.4 — MCP extensions**: MCP resources and prompts; optionally
  an LLM-driven planner on top of the deterministic workflow.
  *(Planned)*
- **Phase 9.5–9.7 — Hardening & delivery**: property-based and fuzz
  testing, structured logging and metrics, Docker packaging and
  release automation. *(Planned)*
- **Phase 10.1–10.2 — ML evaluation layer**: pure metrics, baselines,
  temporal splits, leak-free features, and the seeded `ml_forecast`
  pipeline with holdout evaluation. *(Implemented)*
- **Phase 10.3 — ML anomaly detection**: seeded isolation-forest
  `ml_anomaly_detection` with injected-anomaly precision/recall/F1
  evaluation. *(Implemented)*
- **Phase 10.5 — ML evaluation harness**: the `advanced-calc ml-eval`
  CLI running the combined forecast + anomaly evaluation with a
  reproducible metrics artifact; the model-persistence decision
  (not implemented — see [ML Evaluation Harness](#ml-evaluation-harness-ml-eval)).
  *(Implemented)*
- **Phase 10.4 / 10.6 / Phase 11 — ML extensions**: clustering,
  classification and further validator/workflow integration.
  *(Planned)*

## License

[MIT](LICENSE)