# Mantara

Schema generator that takes a natural language description of a business system and produces a complete PostgreSQL schema — both as structured JSON (mantara.schema.v1) and executable SQL DDL.

The idea is simple: describe what your system does, and get back a ready-to-run database. No manual ER diagramming, no writing CREATE TABLE statements by hand.

## How it works

There are two pipelines depending on the complexity of the input.

**v1 — Single Call**
For simple descriptions ("build a salon management system"). One LLM call produces structured JSON, Python validates it and renders SQL deterministically. Usually finishes in 15-30 seconds.

**v2 — Multi-Step**
For complex inputs like full Functional Specification Documents. Breaks the work into 3 focused LLM calls:
1. Input Analysis (gpt-4o-mini) — classifies the input, extracts entities and relationships
2. Schema Planning (gpt-4o) — designs the full menu/submenu/table structure
3. JSON Generation (gpt-4o + Structured Outputs) — produces the final mantara.schema.v1 JSON

If the generated JSON fails business validation, a repair loop re-runs generation with error feedback (max 2 retries). SQL rendering is always deterministic Python — the LLM never writes SQL.

```
User Input → LLM (Structured Outputs) → MantaraSchema JSON
                                              ↓
                                     business_validator.py
                                              ↓
                                     renderer.py → SQL
                                              ↓
                                       .json + .sql output
```

The key thing: JSON and SQL can't go out of sync because SQL is rendered from the JSON object, not generated separately.

## Web UI

There's a Streamlit app for interactive use. Supports typed descriptions, pasted FSDs, and file uploads (CSV, PDF, DOCX, TXT).

```bash
streamlit run app.py
```

Features:
- v1/v2 pipeline toggle
- Model selection (gpt-4o, gpt-4o-mini)
- Live step-by-step progress for v2
- Validation checklist
- Download buttons for .sql and .json
- Auto-switches to v2 for large inputs (>2000 chars)

## Getting started

```bash
pip install -r requirements.txt
cp .env.example .env
# add your OpenAI API key to .env
```

**CLI:**
```bash
python main.py "Build a hospital management system"
python main.py --input sample_fsd/logistics_fsd.csv
python main.py --model gpt-4o-mini "Build a todo app"
python main.py --no-save "Build a restaurant system"
```

**Web UI:**
```bash
streamlit run app.py
```

Output files go to `output/`.

## Project structure

```
├── app.py                   # Streamlit web UI
├── main.py                  # CLI entry point
├── config.py                # env vars, paths, model config
├── models.py                # Pydantic models for MantaraSchema
├── llm_client.py            # delegates to active backend
├── generator.py             # v1 pipeline orchestrator
├── generator_v2.py          # v2 multi-step pipeline
├── renderer.py              # JSON → PostgreSQL DDL (deterministic)
├── business_validator.py    # 8 business rule checks
├── backends/
│   ├── __init__.py          # SchemaGenerator protocol + factory
│   └── openai_backend.py    # OpenAI Structured Outputs backend
├── prompts/
│   └── system_prompt.md     # system prompt / ontology
├── schemas/
│   └── mantara_schema_v1.json
├── tests/                   # 48 unit tests (no API key needed)
├── eval.py                  # automated eval harness
├── test_prompts/            # 4 test prompts for eval
├── sample_fsd/              # sample FSD inputs
└── requirements.txt
```

## Validation

Every generated schema goes through 8 business rule checks before SQL is rendered:

1. Snake_case naming (schema, tables, columns)
2. ENUM type names end with `_enum`
3. ENUM values are snake_case (no symbols)
4. Submenu IDs follow `menu_id * 100 + seq` convention
5. Every table has `id` as first column, `submenu_id` as second
6. REFERENCES clauses use the correct schema prefix
7. Referenced tables actually exist in the schema
8. Full JSON Schema compliance against mantara_schema_v1.json

## Testing

```bash
# unit tests — no API key needed
pytest tests/ -v

# eval harness — needs API key
python eval.py
python eval.py --prompt hospital
python eval.py --model gpt-4o-mini
```

## Configuration

Set in `.env`:

| Variable | Default | What it does |
|----------|---------|--------------|
| `OPENAI_API_KEY` | (required) | your API key |
| `MANTARA_MODEL` | `gpt-4o` | model for generation |
| `MANTARA_MAX_TOKENS` | `16000` | max response tokens |
| `MANTARA_TEMPERATURE` | `0.2` | LLM temperature |
| `MANTARA_TIMEOUT` | `180` | API timeout (seconds) |
| `MANTARA_MAX_RETRIES` | `2` | retries on transient errors |
| `MANTARA_BACKEND` | `openai` | active LLM backend |

## Backend abstraction

The LLM call sits behind a `SchemaGenerator` protocol. Right now only the OpenAI backend exists, but adding a new one (say, for local models) is just:

1. Create `backends/your_backend.py` implementing `generate()`
2. Register it in `backends/__init__.py`
3. Set `MANTARA_BACKEND=your_backend`

Everything else (validation, rendering, CLI, UI) stays the same.

## Limitations

- v1 works best for schemas under ~15 tables. For anything bigger, v2 is significantly more reliable.
- v2 can handle 60+ tables but takes 2-3 minutes for very complex inputs.
- The pipeline depends on OpenAI's Structured Outputs — local model support would need constrained decoding (outlines, guidance, etc).
- Generated schemas are structurally valid but domain modeling quality depends on how well the input describes the system. Garbage in, garbage out.