# Mantara — Pipeline Architecture Plan v2

## Product Goal

A user describes a business system in natural language and gets a full PostgreSQL schema in under 90 seconds.

**Core principle: LLM owns schema intent; Python owns SQL generation.**

---

## Architecture (Corrected)

The LLM generates structured JSON only. Python validates business rules, then deterministically renders SQL from that JSON. This eliminates the failure class where JSON and SQL drift apart.

```
User Input
    │
    ▼
┌─────────────────────────────────────────────┐
│  LLM (Structured Outputs)                   │
│  Generates mantara.schema.v1 JSON            │
│  via Pydantic model → guaranteed structure  │
└─────────────────────────────────────────────┘
    │
    ▼
┌─────────────────────────────────────────────┐
│  business_validator.py                      │
│  Snake_case, submenu ID convention,         │
│  table structure, REFERENCES consistency,   │
│  JSON Schema compliance                     │
└─────────────────────────────────────────────┘
    │
    ▼
┌─────────────────────────────────────────────┐
│  renderer.py (deterministic — no LLM)       │
│  JSON → PostgreSQL DDL                      │
│  Handles: menu/submenu tables, ENUMs,       │
│  business tables, deferred FKs, COMMENTs    │
└─────────────────────────────────────────────┘
    │
    ▼
  JSON + SQL output
```

**Why this is better than "LLM generates both JSON and SQL":**

1. SQL is rendered directly from the JSON object, so they stay in sync by construction (assuming a correct renderer)
2. No regex parsing of fenced code blocks — Structured Outputs guarantees the schema
3. SQL formatting is deterministic — same JSON in, same SQL out, every time
4. Retries only involve the JSON step — SQL rendering is instant and free
5. Validation catches errors before any SQL is generated

---

## The 3 Approaches (Updated)

### Approach 1: Single-Call with Structured Outputs ← **IMPLEMENTED**

One LLM call produces structured JSON. Python renders SQL deterministically.

```
User Input → [LLM + Structured Outputs] → MantaraSchema JSON
                                              ↓
                                     [business_validator.py]
                                              ↓
                                     [renderer.py → SQL]
                                              ↓
                                       JSON + SQL output
```

**Files:** 7 Python modules + prompts + schemas

| Metric | Estimated Range |
|--------|-----------------|
| LLM Calls | 1 |
| Latency | 15-40 sec (estimate, to be validated) |
| Accuracy | 65-80% (estimate, to be validated by evals) |
| Build Time | 1-2 days |
| Token Cost | Low-Medium |
| Max Schema Size | ~15 tables (estimate) |

**Pros:**
- Structured Outputs guarantees valid JSON — no parsing failures
- SQL is deterministic — rendered by Python, not LLM
- JSON and SQL stay in sync by construction (single source of truth)
- Simple to debug — inspect the JSON, the SQL follows from it
- Single LLM call = lowest latency and cost

**Cons:**
- No error correction — if JSON has bad domain modeling, no second pass catches it
- Single prompt must handle all complexity (entity extraction + schema design + column typing)
- Accuracy drops on complex schemas (30+ tables) because one call must do everything
- No targeted retry — full regeneration on failure

**Best for:** Prototyping, simple schemas (<15 tables), validating that the system prompt produces good JSON

---

### Approach 2: Multi-Step Chain (4 Steps)

Break work into 4 focused steps. Each step = 1 LLM call with a specialized prompt, except the SQL renderer which is pure Python.

```
User Input
    │
    ▼
[ Step 1: Input Analyzer / Router ]     ── gpt-4o-mini (fast, cheap)
    │  Detect type F/S, extract entities, estimate complexity
    ▼
[ Step 2: Schema Planner ]              ── gpt-4o (strong reasoning)
    │  Entity list, relationships, ENUM catalog, menu/submenu structure
    ▼
[ Step 3: JSON Generator ]              ── gpt-4o + Structured Outputs
    │  Full mantara.schema.v1 JSON → validate against schema + business rules
    ▼
[ Step 4: SQL Renderer ]                ── Python (deterministic, no LLM)
    │  renderer.py generates DDL from validated JSON
    ▼
[ Validator / Repair Loop ]             ── only if Step 3 JSON fails business checks
    │  Re-run Step 3 with error feedback, max 2 retries
    ▼
  Output JSON + SQL
```

**Files:** ~12 files (pipeline steps, prompts, models, llm client, renderer)

| Metric | Estimated Range |
|--------|-----------------|
| LLM Calls | 3 (+2 retries max = 5) |
| Latency | 35-60 sec (estimate) |
| Accuracy | 80-90% (estimate, to be validated by evals) |
| Build Time | 4-7 days |
| Token Cost | Medium |
| Max Schema Size | ~40 tables (estimate) |

**Key insight:** Step 2 (Schema Planner) is the most critical step — invest the most prompt engineering there. The repair loop is the highest-ROI addition — a cheap validation + retry catches most errors.

**Pros:**
- Focused prompts → each LLM call is simpler and more reliable
- Validation between steps catches errors early
- Targeted retries → only regenerate JSON, not everything
- Cheap models for simple steps, expensive models for hard steps
- SQL rendering is instant, free, and never wrong (given correct JSON)
- 4 steps instead of 5 — the SQL generator LLM step is eliminated

**Cons:**
- Higher total latency (sequential steps add up)
- Context loss between steps (planner output must be fully self-contained)
- 3 prompts to tune instead of 1
- Bad Step 2 output cascades to downstream steps

**Best for:** Production systems, complex schemas (15-40 tables), when reliability matters

---

### Approach 3: Multi-Agent (Parallel Agents)

Decompose work into independent agents running in parallel. An orchestrator coordinates.

```
User Input
    │
    ▼
[ ORCHESTRATOR ]
    │
    ├──────────────────────────────┐
    │         PHASE 1 (parallel)   │
    ▼              ▼               ▼
[ ENUM Agent ] [ Entity Agent ] [ Relationship Agent ]
    │              │               │
    └──────┬───────┴───────────────┘
           ▼
    [ MERGER (no LLM — pure logic) ]
           │
           ▼
    [ JSON Generator — Structured Outputs ]
           │
           ▼
    [ CROSS-VALIDATOR + renderer.py ]
           │
           ▼
      Output JSON + SQL
```

| Metric | Estimated Range |
|--------|-----------------|
| LLM Calls | 5 (+2 repairs max = 7) |
| Latency | 20-40 sec (estimate, parallel cuts wall-clock time) |
| Accuracy | 85-93% (estimate, to be validated by evals) |
| Build Time | 7-14 days |
| Token Cost | High |
| Max Schema Size | 80+ tables (estimate) |

**Note:** SQL generation remains deterministic Python in all approaches. Only the JSON generation benefits from parallelism.

**Best for:** Production at scale, complex schemas (30-80+ tables), latency-critical

---

## Side-by-Side Comparison

All estimates — to be validated by evals.

| Dimension | Approach 1 | Approach 2 | Approach 3 |
|-----------|-----------|-----------|-----------|
| LLM Calls | 1 | 3-5 | 5-7 |
| Latency (simple) | 15-25s | 30-45s | 20-30s |
| Latency (complex) | 25-40s | 45-65s | 30-45s |
| Accuracy (simple) | ~75% | ~85% | ~88% |
| Accuracy (complex) | ~60% | ~82% | ~90% |
| Dev Time | 1-2 days | 4-7 days | 7-14 days |
| Error Recovery | Full retry | Step-level retry | Agent-level retry |
| Debuggability | Excellent | Good | Moderate |
| Token Cost | Low-Medium | Medium | High |
| SQL consistency | Deterministic | Deterministic | Deterministic |

---

## Phased Strategy

### Phase 1 (Week 1): Approach 1 — DONE

**Status: Implemented.**

Single LLM call with Structured Outputs + deterministic SQL renderer. Validates the system prompt produces correct JSON. Python renders all SQL.

**Deliverable:** Working CLI that takes input → calls OpenAI with Structured Outputs → validates JSON → renders SQL → saves output files.

### Phase 2 (Week 2-3): Approach 2 — Production Pipeline

Add input analysis (routing + entity extraction) and schema planning as separate LLM steps before JSON generation. Add repair loop for failed validations.

**Key decisions for Phase 2:**
- Schema Planner prompt is the highest-leverage engineering work
- Repair loop: re-run JSON generation with error context (max 2 retries)
- Input Analyzer can use a cheap model (gpt-4o-mini / gpt-5-mini)

### Phase 3 (Week 4+): Approach 3 — Only If Profiling Justifies It

Parallelize only if sequential latency is a measured problem. Most likely parallelization: ENUM extraction + entity design can run concurrently.

---

## What Was Built (Phase 1 — Approach 1)

### File Structure

```
v1/
├── app.py                       # Streamlit web UI
├── main.py                      # CLI entry point
├── config.py                    # API keys, model selection, paths, retry config
├── models.py                    # Pydantic models for MantaraSchema (Structured Outputs)
├── llm_client.py                # Thin wrapper → delegates to active backend
├── generator.py                 # v1 pipeline orchestrator
├── generator_v2.py              # v2 multi-step pipeline (analyze → plan → generate → repair)
├── renderer.py                  # Deterministic JSON → PostgreSQL DDL
├── business_validator.py        # Business rule validation (11 checks)
├── backends/
│   ├── __init__.py              # SchemaGenerator protocol + factory
│   └── openai_backend.py       # OpenAI Structured Outputs (default backend)
├── tests/                       # 73 unit tests (no API key needed)
├── eval.py                      # Automated eval harness
├── test_prompts/                # 4 test prompt files for eval
├── sample_fsd/                  # Sample FSD inputs (logistics CSV)
├── prompts/
│   └── system_prompt.md         # Mantara system prompt (ontology v1)
├── schemas/
│   └── mantara_schema_v1.json    # JSON Schema for validation
├── output/                      # Generated .json and .sql files (not committed)
├── .env.example                 # API key template
├── .gitignore
├── requirements.txt
├── ARCHITECTURE.md              # Detailed architecture doc
└── plan.md                      # Architecture plan + quality improvement log
```

### Module Responsibilities

| Module | What It Does | LLM? |
|--------|-------------|------|
| `models.py` | Pydantic models mirroring mantara.schema.v1 — Column, ForeignKey, Table, Submenu, Menu, EnumType, MantaraSchema | No |
| `llm_client.py` | Thin wrapper — loads system prompt, delegates to active backend | Yes (1 call) |
| `backends/openai_backend.py` | OpenAI Structured Outputs call with retry/backoff/timeout | Yes |
| `backends/__init__.py` | `SchemaGenerator` protocol + `get_backend()` factory | No |
| `business_validator.py` | 11 checks: snake_case naming, submenu ID convention, table structure (id + submenu_id), REFERENCES schema consistency, referenced table existence, PostgreSQL syntax (no MySQL-isms), FLOAT-for-money detection, CHECK constraint enforcement, ENUM naming, JSON Schema compliance | No |
| `renderer.py` | Deterministic JSON → SQL: CREATE SCHEMA, menu/submenu tables + INSERTs + COMMENTs, ENUM types, business tables with deferred FK handling, section separators | No |
| `generator.py` | Orchestrates: LLM → validate → render → return {json_str, sql_str, validation, elapsed} | Orchestrator |
| `main.py` | CLI: `python main.py "description"`, `--model`, `--input file`, `--no-save` | No |
| `eval.py` | Eval harness: runs test prompts, scores outputs, prints results table | Orchestrator |
| `config.py` | Loads .env, exposes API key, model, timeout, retry, backend config | No |

### Business Validation Checks (11 checks)

| # | Check | Module Function |
|---|-------|-----------------|
| 1 | schema_name, table names, column names are snake_case | `validate_naming()` |
| 2 | Enum type names end with `_enum` | `validate_naming()` |
| 3 | Enum values are snake_case | `validate_naming()` |
| 4 | submenu_id follows `menu_id * 100 + seq` convention | `validate_submenu_ids()` |
| 5 | Every business table has `id` as first column, `submenu_id` as second | `validate_table_structure()` |
| 6 | REFERENCES clauses use the correct schema_name prefix | `validate_ref_consistency()` |
| 7 | Referenced tables actually exist in the schema | `validate_referenced_tables()` |
| 8 | No MySQL syntax (ON UPDATE CURRENT_TIMESTAMP, AUTO_INCREMENT, etc.) | `validate_postgresql_syntax()` |
| 9 | Money/quantity columns use NUMERIC, not FLOAT | `validate_numeric_types()` |
| 10 | Money/quantity columns have CHECK constraints (e.g., `CHECK (price >= 0)`) | `validate_check_constraints()` |
| 11 | Full JSON Schema compliance against `mantara_schema_v1.json` | `validate_schema_compliance()` |

### Renderer Capabilities

The deterministic SQL renderer (`renderer.py`) handles:

- `CREATE SCHEMA` with proper naming
- Menu table: `CREATE TABLE`, `COMMENT ON TABLE`, `INSERT` seed data
- Submenu table: `CREATE TABLE` with FK to menu, `COMMENT`, `INSERT` grouped by menu
- ENUM types: `CREATE TYPE ... AS ENUM` with optional description comments
- Business tables: `CREATE TABLE` with columns and inline constraints, `COMMENT ON TABLE`
- **Forward-reference FK handling:** Detects forward references (table B references table A, but A is created later) and automatically generates `ALTER TABLE ... ADD CONSTRAINT` at the end (section: "ADDITIONAL FOREIGN KEY CONSTRAINTS")
- **Duplicate FK prevention:** Columns with inline REFERENCES are not duplicated in the `foreign_keys` list
- **Inline column comments:** `Column.comment` renders as `-- comment` AFTER the comma (comma before comment, so `--` doesn't eat it)
- **MySQL syntax stripping:** Automatically removes `ON UPDATE CURRENT_TIMESTAMP` from constraints
- **Assumptions / open questions:** Rendered as SQL header comments
- **CHECK constraint auto-injection:**
  - `CHECK (col >= 0)` on 30+ price/amount/quantity column name patterns (price, cost, amount, budget, salary, fee, quantity, etc.)
  - `CHECK (end_date >= start_date)` for 5 date pair patterns (start/end, check_in/check_out, planned_start/planned_end, etc.)
  - Skips columns that already have a CHECK constraint (no duplicates)
- Section separators (`-- ======...`) grouping tables by menu
- SQL string escaping (single quotes)

### Rendering Checks (SQL Output Verification)

These 3 checks run against the rendered SQL output in the Streamlit UI (not validator functions — they verify the renderer did its job):

| # | Check | What It Verifies |
|---|-------|-----------------|
| 1 | `CREATE SCHEMA present` | SQL contains `CREATE SCHEMA {schema_name};` |
| 2 | `Menu table with INSERT data` | SQL contains `INSERT INTO` + `.menu` (seed data rendered) |
| 3 | `COMMENT ON TABLE for every table` | `COMMENT ON TABLE` count ≥ table_count + 2 (business tables + menu + submenu) |

**Total checks in Streamlit UI: 9 validator + 3 rendering = 12**

### Domain Knowledge Patterns (System Prompt §4.8)

Permanent rules encoded in the system prompt that guide the LLM toward richer, more correct domain modeling. Each was added after a review round exposed a gap.

| Section | Pattern | What It Ensures |
|---------|---------|-----------------|
| §4.8.1 | Header + Line Items (Order Pattern) | Orders MUST have `order_items`; invoices MUST have `invoice_lines`; any header entity gets a detail table |
| §4.8.2 | Many-to-Many (Junction Table Pattern) | Supplier↔Material → `supplier_materials`; Hotel↔Amenity → `hotel_amenities`; proper junction tables with FKs to both sides |
| §4.8.3 | Rich Entity Modeling | Materials need SKU, unit_of_measure, reorder_level; Equipment needs asset_code, serial_number, daily_cost; no thin entities |
| §4.8.4 | Master Data vs Transaction/Assignment Tables | Workers/equipment/materials are master data; project_labor, equipment_assignments are transaction tables with hours, status, notes |
| §4.8.4a | Invoice/Payment Party Clarity | Invoices must have explicit `client_id` + `vendor_id` (or `payer_id`/`payee_id`), never ambiguous `party_id` |
| §4.8.4b | Milestone/Timeline Depth | Milestones need status, planned_date, actual_date, sequence_number, assigned_to — not just name + date |
| §4.8.4c | Budget Category Modeling | Known small sets → ENUM; open-ended → lookup table; never bare VARCHAR for categories |
| §4.8.5 | CHECK Constraints | `CHECK (price >= 0)` on money/quantity columns; `CHECK (end_date >= start_date)` on date ranges |
| §4.8.6 | Data Types for Business Values | `NUMERIC(12,2)` for money — never FLOAT/DOUBLE PRECISION; FLOAT only for measurements (lat/long, temperature) |
| §4.8.7 | Indexes and Unique Constraints | FK columns should have indexes; natural keys get UNIQUE constraints (e.g., `UNIQUE(hotel_id, room_type_name)`) |
| §4.8.8 | Referential Integrity Consistency | All REFERENCES use `schema_name.table(column)` format consistently |
| §4.9 | PostgreSQL-Only Syntax | TIMESTAMPTZ preferred; `updated_at` trigger note; date range CHECKs; forbidden: AUTO_INCREMENT, DATETIME, TINYINT, ON UPDATE CURRENT_TIMESTAMP |

### Assumptions & Open Questions Feature

The LLM can populate two optional fields in the JSON output:

- **`assumptions`** — Domain assumptions the LLM made (e.g., "Single-tenant system", "All currency in USD")
- **`open_questions`** — Ambiguities the LLM couldn't resolve (e.g., "Should appointments support recurring schedules?")

These are rendered as:
- SQL header comments (`-- ASSUMPTIONS:`, `-- OPEN QUESTIONS:`)
- Collapsible expanders in the Streamlit UI
- Serialized in the JSON output (excluded when null)

---

## API Approach

### Current (Phase 1)

- **API:** OpenAI Chat Completions with Structured Outputs (`client.beta.chat.completions.parse()`)
- **Response format:** Pydantic model (`MantaraSchema`) passed as `response_format`
- **Default model:** `gpt-4o` (configurable via `MANTARA_MODEL` env var)
- **No regex parsing** — Structured Outputs guarantees the JSON conforms to the Pydantic model

### Migration Path

When the project moves beyond PoC:

- **API:** Evaluate the OpenAI Responses API (`client.responses.create()`) as an alternative to the current Chat Completions beta endpoint. Check SDK support at migration time.
- **Structured Outputs:** `json_schema` format with strict mode
- **Models:** Use the strongest available model for schema generation; cheaper models for classification/validation steps in Phase 2.

The migration is a single-file change in `llm_client.py` — no other modules are affected.

---

## How to Run

```bash
# 1. Install dependencies
pip install -r requirements.txt

# 2. Set your API key
echo "OPENAI_API_KEY=sk-..." > .env

# 3. Generate a schema
python main.py "Build a hospital management system"

# 4. Check output/
ls output/
# → 20260308_143022_build_a_hospital_management_system.json
# → 20260308_143022_build_a_hospital_management_system.sql

# Other options
python main.py --model gpt-4o-mini "Build a todo app"
python main.py --input prompt.txt
python main.py --no-save "Build an LMS"
```

---

## Verification

1. Run: `python main.py "Build a hospital management system"`
2. Check `output/` for `.json` and `.sql` files
3. Verify SQL has: CREATE SCHEMA, menu/submenu tables with INSERTs, COMMENT ON TABLE for every table, ENUMs, business tables with `id SERIAL PRIMARY KEY` + `submenu_id`, deferred FKs
4. Run the 4 test prompts from `test_prompts/` and check against the validation checklist
5. Optionally execute the `.sql` against a PostgreSQL 14+ instance to verify it runs clean

---

## Completion Status

### Done

- [x] v1 pipeline — single LLM call + validation + deterministic SQL rendering
- [x] v2 pipeline — multi-step (analyze → plan → generate → repair loop)
- [x] Streamlit web UI with v1/v2 toggle, file upload, validation checklist
- [x] 73 unit tests for renderer, validator, and models
- [x] Eval harness — run test prompts, score outputs, print results
- [x] Retry/backoff + timeout handling
- [x] Backend abstraction (SchemaGenerator protocol + factory)
- [x] FSD input support (CSV, PDF, DOCX, TXT)
- [x] 11 business rule validation checks (referenced-table existence, MySQL syntax, FLOAT detection, CHECK enforcement)
- [x] Live API tested — 4/4 test prompts pass, logistics FSD (67 tables) passes
- [x] SQL verified — tested on OneCompiler PostgreSQL, all statements execute clean

### What's next

- [ ] Local model backend (Qwen2.5, Llama, Mistral) with constrained decoding
- [ ] Prompt versioning and A/B testing
- [ ] Parallel agent approach for very complex schemas (80+ tables)
- [ ] Index generation in renderer (CREATE INDEX on FK columns)
- [ ] Migration script generation (diffs between schema versions)

---

## Quality Improvement Log

Each review round identified issues. Fixes were encoded permanently into the system prompt, validators, and renderer so all future generations benefit.

### Round 1 (Construction Material Platform — v1)
**Rating: 3/10 → identified critical bugs**

| Issue | Root Cause | Fix | Where Stored |
|-------|-----------|-----|-------------|
| Missing `order_items` table | LLM didn't create line-item tables | Added §4.8.1 Header + Line Items pattern | System prompt, v2 planner, v1 addendum |
| `-- comment` eats SQL comma | Renderer put comma after `--` | Comma now placed BEFORE inline comment | `renderer.py` |
| `ON UPDATE CURRENT_TIMESTAMP` | LLM used MySQL syntax | Renderer strips it; validator flags it | `renderer.py`, `business_validator.py` |
| No `supplier_materials` junction | LLM skipped many-to-many | Added §4.8.2 Junction Table pattern | System prompt, v2 planner |
| `FLOAT` for money | LLM defaulted to FLOAT | Added §4.8.6 + `validate_numeric_types()` | System prompt, `business_validator.py` |
| Materials too thin (name + type only) | No domain depth guidance | Added §4.8.3 Rich Entity Modeling | System prompt |

### Round 2 (Construction Management Platform — v2)
**Rating: 7.5/10 → good but needs depth**

| Issue | Root Cause | Fix | Where Stored |
|-------|-----------|-----|-------------|
| `labor_management` embedded worker names | No normalization guidance | Added §4.8.4 Master vs Assignment pattern | System prompt, v2 planner, v1 addendum |
| Invoice party ambiguity (payee_id) | No party model rules | Added §4.8.4a Invoice Party Clarity | System prompt |
| `project_timeline` too thin | No milestone depth rules | Added §4.8.4b Milestone/Timeline Depth | System prompt |
| `budget_category` VARCHAR vs ENUM | No guidance on controlled values | Added §4.8.4c Budget Category Modeling | System prompt |
| Missing `project_materials` | Materials not linked to projects | Reinforced in §4.8.4 assignment pattern | System prompt |
| Missing date range CHECKs | CHECK rules didn't mention dates | Added `CHECK (end_date >= start_date)` | System prompt §4.8.5, §4.9 |
| No FK indexes | No index guidance | Added §4.8.7 Indexes and Unique Constraints | System prompt, v2 planner |
| `TIMESTAMP` vs `TIMESTAMPTZ` | No timezone preference | Added TIMESTAMPTZ preference to §4.9 | System prompt |
| `updated_at` not auto-updating | No trigger note | Added trigger note to §4.9 | System prompt |
| "DEFERRED FOREIGN KEYS" misleading | Section title implied PG DEFERRABLE | Renamed to "ADDITIONAL FOREIGN KEY CONSTRAINTS" | `renderer.py` |
| Assignment tables too thin | No depth guidance | Added hours_allocated, actual_hours, status, notes | System prompt §4.8.4 |
| Equipment missing asset_code | Rich entity list incomplete | Added asset_code, serial_number, daily_cost | System prompt §4.8.4 |

### Round 3 (Hotel Aggregator App)
**Rating: 7/10 — good structure, needs domain depth**

| Issue | Root Cause | Fix | Where Stored |
|-------|-----------|-----|-------------|
| No room availability model | Missing inventory/capacity pattern | Needs room_type with room_count, availability calendar | System prompt §4.8.3 (to encode) |
| Amenity not normalized | Should be amenities master + hotel_amenities junction | Covered by §4.8.2 junction pattern but LLM missed it | System prompt §4.8.2 |
| `hotel.rating` is derived aggregate | Storing computed values without noting source | Needs derived/aggregate column guidance | System prompt (to encode) |
| Reservation missing fields | guest_count, booking_reference, number_of_rooms, special_requests missing | Needs reservation depth pattern | System prompt §4.8.3 (to encode) |
| Reviews not tied to stays | Should have reservation_id on review table | Needs review→stay linkage rule | System prompt (to encode) |
| Missing unique constraints | e.g., UNIQUE(hotel_id, room_type_name) | Covered by §4.8.7 but needs reinforcement | System prompt §4.8.7 |
| Missing FK indexes | FK columns need CREATE INDEX | Covered by §4.8.7 but needs reinforcement | System prompt §4.8.7 |
| SERIAL vs BIGINT GENERATED ALWAYS AS IDENTITY | SERIAL is legacy PostgreSQL | Consider adding IDENTITY preference to §4.9 | System prompt §4.9 (to encode) |