generate-mdl

# Generate Wren MDL — CLI Agent Workflow

Builds an MDL project by discovering database schema and converting it
into Wren's YAML project format. The agent uses whatever database tools
are available in its environment for introspection; the wren CLI handles
type normalization, validation, and build.

For memory and query workflows after setup, run `wren skills get usage`.

## Prerequisites

- `wren` CLI installed (`pip install "wrenai[<datasource>]"`)
- A working database connection (credentials available to the agent)
- A connection profile (set up via `wren profile add`) or connection info ready

## Phase 0 — Detect existing project

**Goal:** If the current directory is already inside a wren project, let the user decide how to proceed.

Check whether `wren_project.yml` exists in the current working directory
(or any parent up to the repository root). If found:

1. Tell the user that an existing wren project was detected and show its path.
2. Ask:
   - **Reset** — wipe the existing project (`models/`, `views/`,
     `relationships.yml`, `instructions.md`, and rebuild `wren_project.yml`)
     and regenerate from scratch in the same directory.
   - **New path** — keep the existing project untouched and choose a
     different directory for the new project. Ask the user for the new path,
     then `wren context init --path <new_path>` and continue from Phase 1
     using that path.

If no existing project is detected, proceed directly to Phase 1.

## Phase 1 — Establish connection and scope

**Goal:** Confirm the agent can reach the database and agree on scope with the user.

1. Verify connectivity using whichever tool is available:
   - If SQLAlchemy: `engine.connect()` test
   - If database driver: simple query like `SELECT 1`
   - If wren profile exists: `wren profile debug` to check config
   - If raw SQL via wren: `wren --sql "SELECT 1"` (requires profile or connection file)

2. Ask the user:
   - Which **schema(s)** or **dataset(s)** to include (skip if only one exists)
   - Whether to include **all tables** or a subset
   - The **datasource type** for wren (e.g., `postgres` (including Aurora), `mysql` (including Aurora), `bigquery`, `snowflake`) — needed for type normalization dialect

## Phase 2 — Discover schema

**Goal:** Collect table names, column names, column types, and constraints.

Use whatever introspection method is available. Here are common approaches
ranked by convenience:

### Option A: SQLAlchemy (recommended if available)

```python
from sqlalchemy import create_engine, inspect

engine = create_engine(connection_url)
inspector = inspect(engine)

tables = inspector.get_table_names(schema="public")

for table in tables:
    columns = inspector.get_columns(table, schema="public")
    # columns → [{"name": "id", "type": INTEGER(), "nullable": False, ...}]

    pk = inspector.get_pk_constraint(table, schema="public")
    # pk → {"constrained_columns": ["id"], "name": "orders_pkey"}

    fks = inspector.get_foreign_keys(table, schema="public")
    # fks → [{"constrained_columns": ["customer_id"],
    #          "referred_table": "customers",
    #          "referred_columns": ["id"]}]
```

### Option B: Database-specific driver

- **psycopg / asyncpg (Postgres):** Query `information_schema.columns` and `information_schema.table_constraints`
- **google-cloud-bigquery:** `client.list_tables()`, `client.get_table()` → `table.schema`
- **snowflake-connector-python:** `SHOW COLUMNS IN TABLE`, `SHOW PRIMARY KEYS IN TABLE`
- **clickhouse-driver:** `DESCRIBE TABLE`, `system.tables`

### Option C: Raw SQL via wren

If no driver is available but a wren profile is configured, query
`information_schema` through wren itself:

```bash
wren --sql "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'" -o json
wren --sql "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders'" -o json
```

Note: this goes through the MDL layer, so it only works if you already
have a minimal MDL or if the database supports `information_schema` as
regular tables. For bootstrapping from zero, Option A or B is preferred.

## Phase 3 — Normalize types

**Goal:** Convert raw database types to wren-core-compatible types.

### Python import (recommended for batch processing)

```python
from wren.type_mapping import parse_type, parse_types

# Single type
normalized = parse_type("character varying(255)", "postgres")  # → "VARCHAR(255)"

# Batch — entire table at once
columns = [
    {"column": "id", "raw_type": "int8"},
    {"column": "name", "raw_type": "character varying"},
    {"column": "total", "raw_type": "numeric(10,2)"},
]
normalized_cols = parse_types(columns, dialect="postgres")
# Each dict now has a "type" key with the normalized value
```

### CLI (if Python import not available)

Single type:
```bash
wren utils parse-type --type "character varying(255)" --dialect postgres
# → VARCHAR(255)
```

Batch (stdin JSON):
```bash
echo '[{"column":"id","raw_type":"int8"},{"column":"name","raw_type":"character varying"}]' \
  | wren utils parse-types --dialect postgres
```

## Phase 4 — Scaffold and write MDL project

**Goal:** Create the YAML project structure.

### Step 1 — Initialize project

```bash
wren context init --path /path/to/project
```

This creates:
```text
project/
├── wren_project.yml
├── models/              # business-facing tables/models
├── views/               # named SQL statements
├── cubes/               # pre-aggregation cubes (measures + dimensions)
├── relationships.yml
└── instructions.md
```

> **When to define cubes:** If the user asks aggregation questions like
> "revenue by month" or "top customers", define cubes alongside models —
> they give agents a structured query API instead of forcing them to
> hand-write `GROUP BY` / `DATE_TRUNC` SQL. See the
> [Cube guide](https://github.com/Canner/WrenAI/blob/main/docs/core/guides/cubes.md).

> **IMPORTANT: `catalog` and `schema` in `wren_project.yml`**
>
> These are Wren Engi