azem

Turn a data definition into correct SQL and agent tools.

Write your data once as an 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.
2. It forgets metric filters, such as status = 'completed'.
3. 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.
2. Connect to PostgreSQL, SQLite, or ClickHouse (read-only).
3. Compile a structured request into SQL.
4. Attest the model against the live database.
5. 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.

# 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:

# 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:

# 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.

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:

{
  "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

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

{
  "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.

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

uv add azem

With the MCP server extra:

uv add "azem[mcp]"

To run the azem command-line tool:

uv tool install azem

Or install from source:

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

Development

# 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.