Semantic Layer & Modeling

Semantic Modeler & Model Definition Language (MDL)

Transform messy, cryptically named database tables into clean, curated business concepts with calculated measures, virtual dimensions, and explicit entity relationships.

What is Model Definition Language (MDL)?

MDL is a declarative, versionable JSON manifest that describes the semantic contract between your database and AI agents. It abstracts away physical table column names (e.g. c_fst_nm becomes first_name), declares joins, and defines mathematical equations for computed metrics.

json

{
  "models": [
    {
      "name": "Orders",
      "tableReference": { "schema": "public", "table": "raw_orders" },
      "columns": [
        { "name": "order_id", "type": "INTEGER", "isPk": true },
        { "name": "customer_id", "type": "INTEGER" },
        { "name": "total_amount", "type": "DOUBLE" },
        { "name": "tax_amount", "type": "DOUBLE" },
        {
          "name": "net_revenue",
          "type": "DOUBLE",
          "calculated": true,
          "expression": "total_amount - tax_amount"
        }
      ]
    }
  ],
  "relationships": [
    {
      "name": "OrdersToCustomers",
      "models": ["Orders", "Customers"],
      "joinType": "MANY_TO_ONE",
      "condition": "Orders.customer_id = Customers.id"
    }
  ]
}

Visual React Flow Modeler

The built-in ModelerWizard (/configure) renders your database tables as interactive nodes on an infinite canvas. You can:

Drag-and-Drop Joins

Connect primary keys to foreign keys visually with magnetic relation handles.

Alias Customization

Rename tables and columns into human-readable English for agent clarity.

Schema Sync & Refresh

Automatically discover new tables or altered column types from the live DB with one click.

Calculated Columns & Inlining

Instead of forcing LLMs to remember how to calculate complex business ratios (like Gross Margin, Customer Lifetime Value, or VAT Deductions), you declare them once in the model:

Measure: gross_profit = (item_price * quantity) - item_cost

Query: "Show me total gross profit by category in 2024"

WASM Inlining: The engine automatically rewrites the formula directly into the SQL SELECT SUM((item_price * quantity) - item_cost) clause.

Relationship Mapping & Cardinality

Define ONE_TO_MANY, MANY_TO_ONE, and ONE_TO_ONE relationships. The Graph BFS engine relies on these definitions to safely traverse multi-table joins without generating duplicate rows.

Importing from dbt Projects

If your team already uses dbt, upload your compiled manifest.json to instantly populate models, column descriptions, and lineage relationships without manual entry:

Orcha's parser (lib/dbt-parser.ts) extracts models, tests, sources, and column docs into native MDL format seamlessly.