Metadata-Version: 2.5
Name: azem
Version: 0.1.1
Summary: Compile an Apache Ossie semantic model into correct SQL, attest it against a live database, and serve it to agents.
License-Expression: MIT
License-File: LICENSE
Keywords: agent,mcp,ossie,semantic-layer,sql
Requires-Python: >=3.11
Requires-Dist: clickhouse-connect[sqlalchemy]>=1.9
Requires-Dist: jsonschema>=4.26
Requires-Dist: psycopg[binary]>=3.2
Requires-Dist: pydantic>=2.0
Requires-Dist: pyyaml>=6.0
Requires-Dist: sqlalchemy>=2.0
Requires-Dist: sqlglot>=30.0
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Provides-Extra: mcp
Requires-Dist: fastmcp>=4.0.10; extra == 'mcp'
Description-Content-Type: text/markdown

# azem

Turn a data definition into correct SQL and agent tools.

Write your data once as an [Apache Ossie](https://github.com/apache/ossie)
semantic model. azem compiles that model into correct SQL, proves the model
against a live database, and serves it to agents over MCP.

The agent does not guess joins, filters, or metric formulas. That logic lives in
the model and the compiler.

## Why

An agent that writes SQL from scratch gets three things wrong:

1. It guesses joins.
1. It forgets metric filters, such as `status = 'completed'`.
1. It picks the wrong column, such as the product list price instead of the price at time of sale.

azem removes all three. The model declares the joins and the metric formula.
The compiler builds the SQL. The attestation layer proves the model still
matches the database.

## How it works

```
 Ossie YAML        Compiler          Agent tools          Database
+-----------+    +-----------+     +---------------+    +-----------+
| datasets  |    | base      |     | list/describe |    | read-only |
| joins     | -> | join path | ->  | search/metric | -> | SQL       |
| metrics   |    | fan-out   |     | compile/run   |    |           |
+-----------+    +-----------+     +---------------+    +-----------+
       \                                                /
        \------------ attest (prove it still holds) ---/
```

Five steps:

1. **Define** the data in Ossie YAML.
1. **Connect** to PostgreSQL, SQLite, or ClickHouse (read-only).
1. **Compile** a structured request into SQL.
1. **Attest** the model against the live database.
1. **Serve** the model to an agent over MCP.

## Quick start

Requirements: Python 3.11 or later, and SQLite, PostgreSQL, or ClickHouse.
SQLite needs no server, so it is the fastest start.

```bash
# 1. Install azem with the MCP extra
uv sync --extra dev --extra mcp

# 2. Load the example data into SQLite
sqlite3 ecommerce.db < examples/ecommerce.sql

# 3. Validate the model
uv run azem validate examples/ecommerce.yaml

# 4. Attest the model against the database
uv run azem attest examples/ecommerce.yaml \
  --db sqlite:///ecommerce.db \
  --out attestation.yaml

# 5. Serve the model to an agent
uv run azem serve examples/ecommerce.yaml \
  --db sqlite:///ecommerce.db \
  --attestation attestation.yaml
```

`sqlite3` is the SQLite command-line tool. The Python `sqlite3` module also
works. The read-only mode is `PRAGMA query_only = ON`.

### PostgreSQL instead

PostgreSQL needs a server. `compose.yaml` starts one on port 15432. Use these
commands in place of steps 2, 4, and 5:

```bash
# 2. Start PostgreSQL and load the example data
docker compose up -d
psql 'postgresql://admin:pass@localhost:15432/postgres' -f examples/ecommerce.sql

# 4. Attest the model against the database
uv run azem attest examples/ecommerce.yaml \
  --db 'postgresql+psycopg://admin:pass@localhost:15432/postgres' \
  --out attestation.yaml

# 5. Serve the model to an agent
uv run azem serve examples/ecommerce.yaml \
  --db 'postgresql+psycopg://admin:pass@localhost:15432/postgres' \
  --attestation attestation.yaml
```

### ClickHouse instead

ClickHouse needs a server. `compose.yaml` starts one on ports 8123 (HTTP) and
9000 (native). Use these commands in place of steps 2, 4, and 5:

```bash
# 2. Start ClickHouse and load the example data
docker compose up -d clickhouse-db
docker exec -i clickhouse-db clickhouse-client \
  --user default --password changeme --multiquery \
  < examples/ecommerce.clickhouse.sql

# 4. Attest the model against the database
uv run azem attest examples/ecommerce.yaml \
  --db 'clickhousedb://default:changeme@localhost:8123/default' \
  --out attestation.yaml

# 5. Serve the model to an agent
uv run azem serve examples/ecommerce.yaml \
  --db 'clickhousedb://default:changeme@localhost:8123/default' \
  --attestation attestation.yaml
```

ClickHouse does not enforce `REFERENCES` or `PRIMARY KEY`, so the seed file
omits them. The connector sets `readonly=2` and caps scanned rows and bytes on
every connect. The seed uses `LowCardinality(String)`, `Enum8`, and small
integer types. The compiler emits `LEFT ANY JOIN` for many-to-one joins.

The example asks a real question. Revenue counts only completed orders and uses
the price at time of sale:

```
$ uv run azem compile examples/ecommerce.yaml examples/request.json
SELECT regions.region_name AS region_name,
       SUM(CASE WHEN orders.status = 'completed'
                THEN order_items.quantity * order_items.unit_price END) AS revenue
FROM order_items AS order_items
LEFT JOIN orders AS orders ON order_items.order_id = orders.order_id
LEFT JOIN customers AS customers ON orders.customer_id = customers.customer_id
LEFT JOIN regions AS regions ON customers.region_id = regions.region_id
WHERE orders.order_date BETWEEN '2026-07-01' AND '2026-09-30'
GROUP BY regions.region_name
ORDER BY revenue DESC NULLS LAST
LIMIT 5
```

The compiler added the join path, the `completed` filter, and the price at time
of sale. The agent did not guess any of them. `examples/request.json` holds this
request.

## The model

The model is one Apache Ossie document. Datasets point at physical tables.
Relationships point from the many side to the one side. Metrics hold aggregate
SQL and reference columns as `dataset.column`.

```yaml
version: 0.2.0.dev0
name: ecommerce_analytics
datasets:
  - name: orders
    source: orders
    primary_key: [order_id]
    fields:
      - name: status
        expression: { dialects: [{ dialect: ANSI_SQL, expression: status }] }
        datatype: String
        ai_context:
          instructions: "Values: completed, cancelled, pending."
  - name: order_items
    source: order_items
    fields:
      - name: unit_price
        expression: { dialects: [{ dialect: ANSI_SQL, expression: unit_price }] }
        datatype: Decimal
relationships:
  - { name: order_items_to_orders, from: order_items, to: orders,
      from_columns: [order_id], to_columns: [order_id] }
metrics:
  - name: revenue
    datatype: Decimal
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: >
            SUM(CASE WHEN orders.status = 'completed'
                     THEN order_items.quantity * order_items.unit_price END)
```

See `examples/ecommerce.yaml` for the full model.

## Command line

| Command                                                         | Purpose                                   |
| --------------------------------------------------------------- | ----------------------------------------- |
| `azem validate <model>`                                     | Validate the model against the Ossie spec |
| `azem compile <model> <request.json>`                       | Compile a structured request into SQL     |
| `azem attest <model> --db <url> [--out report.yaml]`        | Check the model against the database      |
| `azem serve <model> --db <url> [--attestation report.yaml]` | Serve the model over MCP                  |

A request file looks like this:

```json
{
  "metrics": ["revenue"],
  "dimensions": ["regions.region_name"],
  "filters": [
    {"field": "orders.order_date", "op": "between", "value": ["2026-07-01", "2026-09-30"]}
  ],
  "order_by": [{"field": "revenue", "direction": "desc"}],
  "limit": 5
}
```

Operators: `=`, `!=`, `<`, `<=`, `>`, `>=`, `in`, `not_in`, `between`, `like`,
`not_like`, `is_null`, `is_not_null`.

## Python API

```python
from azem import Connection, SemanticModel

model = SemanticModel.from_file("examples/ecommerce.yaml")

with Connection.from_url("sqlite:///ecommerce.db") as conn:
    sql = model.compile({"metrics": ["revenue"], "dimensions": ["regions.region_name"]}, "sqlite")
    rows = conn.run(sql)
    report = model.attest(conn)
    print(report.result, report.summary)
```

## Agent tools

`ToolSet` defines each tool once. `azem serve` exposes them over MCP.
`as_anthropic()` and `as_openai()` return the same tools for those APIs.

| Tool               | Purpose                                               |
| ------------------ | ----------------------------------------------------- |
| `list_datasets`    | Names and one-line descriptions                       |
| `describe_dataset` | Fields, types, keys, allowed values                   |
| `search_model`     | Find datasets, fields, metrics by name or synonym     |
| `get_metric`       | Formula, description, synonyms                        |
| `find_join_path`   | Shortest valid join path; never a guessed join        |
| `sample_values`    | Real distinct values for spelling and case            |
| `compile_query`    | Structured request in, correct SQL out                |
| `validate_sql`     | Check agent SQL: SELECT only, known tables, row limit |
| `run_query`        | Run SQL after the safety check                        |

### Connect an agent

```json
{
  "mcpServers": {
    "azem": {
      "command": "uv",
      "args": ["run", "--extra", "mcp", "azem", "serve",
               "examples/ecommerce.yaml",
               "--db", "postgresql+psycopg://admin:pass@localhost:15432/postgres",
               "--attestation", "attestation.yaml"],
      "cwd": "/path/to/azem"
    }
  }
}
```

`serve` checks the attestation report at startup. If the result failed, or the
model changed since the report, the server stops. Use `--force` to override.

## Compiler rules

**Base dataset.** The compiler parses the metric and collects the datasets it
references. It picks the dataset that reaches the others by many-to-one steps.
For `revenue`, that is `order_items`. For `order_count`, that is `orders`.

**Fan-out.** A step from the one side to the many side copies rows. That
inflates a plain `SUM`. The compiler allows the step only when every aggregate
in the metric uses `DISTINCT`. Otherwise it stops and names the datasets.

**Dialect.** Each field and metric can hold one expression per dialect. The
compiler picks the matching dialect, then ANSI SQL. SQL is rendered with
`sqlglot`.

## Attestation

Checks come from the model. No extra YAML is needed.

| Declared           | Check                           |
| ------------------ | ------------------------------- |
| `source`           | table runs                      |
| field `expression` | expression runs                 |
| `primary_key`      | unique and not null             |
| relationship       | no orphan foreign keys          |
| relationship       | the one-side key is unique      |
| metric             | query runs and returns a number |

The report is YAML. It records the model hash, the database, the time, the
result, and every check.

```yaml
attestation:
  model: ecommerce_analytics
  spec_version: 0.2.0.dev0
  model_hash: sha256:b9f0d671...
  database: postgresql+psycopg://admin:***@localhost:15432/postgres
  run_at: '2026-10-03T01:29:35+00:00'
  result: passed
  summary: { passed: 31, failed: 0, warned: 0 }
```

The model hash binds the report to one model revision. Run attest in CI on every
model change. Serve only after a pass.

## Install

```bash
uv add azem
```

With the MCP server extra:

```bash
uv add "azem[mcp]"
```

To run the `azem` command-line tool:

```bash
uv tool install azem
```

Or install from source:

```bash
git clone <repo> azem
cd azem
uv sync --extra dev --extra mcp
```

## Development

```bash
# Unit tests and the SQLite smoke test (no server needed)
uv run --extra dev pytest

# Full suite with the example PostgreSQL and ClickHouse databases
docker compose up -d
psql 'postgresql://admin:pass@localhost:15432/postgres' -f examples/ecommerce.sql
AZEM_TEST_DB_URL='postgresql+psycopg://admin:pass@localhost:15432/postgres' \
AZEM_TEST_CLICKHOUSE_URL='clickhousedb://default:changeme@localhost:8123/default' \
  uv run --extra dev --extra mcp pytest
```

The SQLite smoke test always runs. PostgreSQL tests skip when
`AZEM_TEST_DB_URL` is not set. ClickHouse tests skip when
`AZEM_TEST_CLICKHOUSE_URL` is not set. Server tests skip when the `mcp`
extra is not installed.

## Architecture

- `src/azem/spec/` — vendored Apache Ossie models and JSON Schema.
- `src/azem/model/` — load, validate, and join-path search.
- `src/azem/compiler/` — build SQL from a structured request.
- `src/azem/safety.py` — gate agent-written SQL.
- `src/azem/connectors.py` — database connections (PostgreSQL, SQLite, ClickHouse).
- `src/azem/attest.py` — check the model against the live database.
- `src/azem/tools.py` — agent tool definitions and adapters.
- `src/azem/server.py` — FastMCP 4 server.

The `model/` and `compiler/` packages know nothing about databases or agents.

## Status

v0.1.0 is the MVP. The 0.1.0 connector goal is three first-class databases:
PostgreSQL, SQLite, and ClickHouse. 92 tests pass. The 12 golden answers are
correct.

Done:

- Ossie model load and validation
- PostgreSQL, SQLite, and ClickHouse connections, read-only enforced
- Compiler with fan-out guard and dialect rendering
- SQL safety layer
- Derived attestation with report
- 9 MCP tools
- FastMCP 4 server with an attestation gate

Next (still in 0.1.0):

- Explicit attestations: accepted values, SQL checks, reconcile, freshness
- Multi-metric queries with CTE pre-aggregation
- Time grains: day, month, quarter
- Auto-draft a model from the live information schema

Later:

- MySQL connector
- SQLite statement timeout

## License

MIT. The vendored Apache Ossie files stay under Apache-2.0. See
`src/azem/spec/LICENSE-APACHE`.
