> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vigilos.co/llms.txt
> Use this file to discover all available pages before exploring further.

# Semantic Models

> Understand how semantic models create a business-friendly layer over your raw database tables.

## What Is a Semantic Model?

A semantic model is a metadata layer that sits between your raw database tables and the Vigilos AI agent. It translates database schemas into business vocabulary, so when you ask "What was our total revenue last quarter?", the AI knows exactly which table, column, and aggregation to use.

Without a semantic model, the AI would see column names like `txn_amt` or `cust_nm` and have no context for what they mean. The semantic model provides that context through friendly names, descriptions, column roles, relationships, and reusable measures.

<Info>
  Semantic models do not move or copy your data. They only store metadata about your tables. All queries run directly against your live database.
</Info>

## Key Components

### Entities

An entity represents a business object derived from a database table or view. Each entity maps to a single source table and includes:

* **Name** - a business-friendly label (e.g., "Customers" instead of a raw table name)
* **Description** - plain language explanation of what this entity represents
* **Database** - the database where the source table lives
* **Source table** - the actual table or view in your database
* **Primary key column** - used for joins and relationship validation

You can add as many entities as needed to a single semantic model. A typical model might have entities for Orders, Customers, Products, and Regions.

### Columns

Each entity contains columns that map to actual database columns or computed expressions.

**Direct columns** reference a column from the underlying table:

| Property           | Description                                                                     |
| ------------------ | ------------------------------------------------------------------------------- |
| **Source column**  | The raw column name in the database                                             |
| **Name**           | Business-friendly display name                                                  |
| **Description**    | What this column represents                                                     |
| **Data type**      | `string`, `number`, `date`, `boolean`, `array`, or `object`                     |
| **Column role**    | `dimension` (for grouping), `measure` (for aggregation), or `key` (for joining) |
| **Display format** | Formatting rules for currencies, percentages, or numbers                        |

**Computed columns** use an expression instead of a direct source column. These are useful for:

* Extracting values from JSON fields
* Formatting or transforming dates
* Computing derived values like `price * quantity`

You can also hide columns to keep them available for joins or filters without exposing them in the AI's suggestions, and control the display order of columns in the schema browser.

### Relationships

Relationships connect entities to each other, enabling the AI to write multi-table queries with correct JOINs.

Each relationship defines:

* **From entity and column** - the foreign key side
* **To entity and column** - the primary key side
* **Cardinality** - the type of relationship

<AccordionGroup>
  <Accordion title="Cardinality types">
    | Cardinality      | Example                                                                  |
    | ---------------- | ------------------------------------------------------------------------ |
    | **One-to-one**   | A customer has one profile                                               |
    | **One-to-many**  | A customer has many orders                                               |
    | **Many-to-one**  | Many orders belong to one customer                                       |
    | **Many-to-many** | Products belong to many categories, and categories contain many products |
  </Accordion>

  <Accordion title="Relationship validation">
    When you create a relationship, Vigilos samples your actual data to verify its integrity. The validation report includes:

    * **Samples tested** - how many rows were checked
    * **Match rate** - percentage of foreign key values that found a match
    * **Matched count** - number of successfully joined rows
    * **Orphaned count** - foreign key values with no matching primary key
    * **Null count** - rows where the join column is null
    * **Sample matches** - a preview of matched rows
    * **Semantic warnings** - potential issues like low match rates or high null counts

    Validation status is tracked as `pending`, `valid`, `invalid`, or `warning`.
  </Accordion>
</AccordionGroup>

### Measures

Measures are reusable aggregation formulas that the AI and visual builder can reference by name.

| Property           | Description                                                                 |
| ------------------ | --------------------------------------------------------------------------- |
| **Name**           | A descriptive label like "Total Revenue" or "Average Order Value"           |
| **Expression**     | The aggregation formula, e.g., `SUM(amount)`, `COUNT(DISTINCT customer_id)` |
| **Data type**      | `number`, `currency`, or `percent`                                          |
| **Display format** | How values should be formatted in results                                   |

**Compound measures** reference other measures, letting you build calculations like:

* **Average Order Value** = `Total Revenue / Total Orders`
* **Profit Margin** = `(Total Revenue - Total Cost) / Total Revenue`

<Tip>
  Define measures for any metric your team asks about frequently. This ensures consistent definitions across all queries and dashboards.
</Tip>

## Model Lifecycle

<Steps>
  <Step title="Create the model">
    Give your semantic model a name and select the database connection it will use.
  </Step>

  <Step title="Add entities">
    Browse your database schema and select the tables you want to include. Each table becomes an entity with a friendly name and description.
  </Step>

  <Step title="Configure columns">
    For each entity, set column names, descriptions, roles (dimension, measure, or key), data types, and display formats. Create computed columns for derived values.
  </Step>

  <Step title="Define relationships">
    Connect entities by mapping foreign key columns to primary key columns. Set the cardinality and validate the relationship against live data.
  </Step>

  <Step title="Create measures">
    Define reusable aggregations like SUM, COUNT, and AVG. Build compound measures that reference other measures for derived metrics.
  </Step>

  <Step title="Use with AI">
    Your model is ready. Navigate to Ask AI, select your semantic model, and start asking questions in plain English.
  </Step>
</Steps>