data/semantic_layer_mealgen.yml

# Semantic layer for the mealgen dev dataset.
#
# This file is the agent's only source of "business meaning." It is read
# by inspect_schema and used by the LLM to plan run_sql / compare_periods
# / decompose_metric calls. Write descriptions for the model, not for
# humans — terse, concrete, and surface every footgun.
#
# STATUS: dev placeholder. The real production dataset (GitHub Archive)
# is TBD. When it lands, this file gets rewritten end-to-end. Tools and
# prompts must not assume the mealgen schema.

dataset:
  name: mealgen
  status: dev
  description: |
    Grocery items scraped from US supermarket websites
    (currently Whole Foods and Trader Joe's), with separately-collected
    nutrition facts. Snapshot extracted from a personal Postgres dev
    instance (postgres-mealgen) into parquet.
  parquet_dir: data/dev
  notes:
    - "items has no time dimension — treat it as a snapshot."
    - "item_nutrition.created_at is the only time column. Span: 2025-12-27 to 2026-03-28."
    - "Set PARQUET_DIR=data/dev in .env for the agent to read this dataset."

tables:
  items:
    file: items.parquet
    grain: "One row per (store, external_id). 8627 rows."
    description: "Grocery-item catalog snapshot. No time column."
    columns:
      - name: id
        type: BIGINT
        description: "Surrogate primary key."
      - name: store
        type: VARCHAR
        description: "Source supermarket. Two values: WHOLE_FOODS, TRADER_JOES."
        enum: [WHOLE_FOODS, TRADER_JOES]
      - name: name
        type: VARCHAR
        description: "Product name as listed on the storefront."
      - name: external_id
        type: VARCHAR
        description: "The store's own product identifier. Unique within a store."
      - name: price
        type: DOUBLE
        description: "Listed retail price in USD. Range $0.23–$179.99, mean ~$7.91. Always populated."
      - name: unit_size
        type: VARCHAR
        description: "Free-text package size like '3 Oz', '1 lb', '12 ct'. Not normalized — string parsing required."
      - name: category_path
        type: VARCHAR
        description: |
          Mixed depth: sometimes a top-level label ('Beverages'), sometimes a delimited path
          ('Food > Meat, Seafood & Plant-based > Beef, Pork & Lamb'). Use
          split_part(category_path, ' > ', 1) for a stable top-level slice.
      - name: image_url
        type: VARCHAR
        description: "URL of the product image. Not useful for analysis."
      - name: tags_json
        type: VARCHAR
        description: |
          JSON array of lifestyle tags (e.g. '["Gluten Free","Game Night"]'). Mostly populated
          for Trader Joe's. Filter with `tags_json LIKE '%Gluten Free%'` or json_extract.
      - name: raw_json
        type: VARCHAR
        description: "Original scraped payload. Skip unless a specific field is needed."
    joins:
      - "items.id = item_nutrition.item_id (LEFT JOIN; only ~46% of items have nutrition)"

  item_nutrition:
    file: item_nutrition.parquet
    grain: "One row per items.id (UNIQUE). 3984 rows."
    description: "Parsed nutrition facts attached to an item. The only table with a time dimension."
    columns:
      - name: id
        type: BIGINT
        description: "Surrogate primary key."
      - name: item_id
        type: BIGINT
        description: "Foreign key to items.id. Unique."
      - name: nutrition
        type: VARCHAR
        description: |
          JSON document OR free-text note. About 171 of 3984 rows (4%) are plain-text notes,
          not JSON — always filter `WHERE nutrition LIKE '{%'` before json_extract or you'll get
          'Malformed JSON' errors. Shape when valid:
            {"parsed": {"calories": int, "total_fat_g": float, "saturated_fat_g": float,
                        "sodium_mg": float, "total_carbohydrate_g": float, "dietary_fiber_g": float,
                        "total_sugars_g": float, "added_sugars_g": float, "protein_g": float,
                        "vitamin_d_mcg": float, "calcium_mg": float, "iron_mg": float, ...},
             "serving_count": int, "serving_size_text": str, "serving_size_grams": float}
          Cast extracted values to DOUBLE.
      - name: created_at
        type: TIMESTAMP
        description: "When this nutrition record was first written. Use this for time-window comparisons."
      - name: updated_at
        type: TIMESTAMP
        description: "When this row was last updated."
    joins:
      - "item_nutrition.item_id = items.id"

# Named metrics the agent can pass to compare_periods / decompose_metric.
# `time_column: null` means the metric is static (no before/after possible).
metrics:
  item_count:
    description: "Number of distinct items in the catalog."
    sql: "SELECT COUNT(*) AS value FROM items"
    time_column: null

  avg_price:
    description: "Mean retail price across items, USD."
    sql: "SELECT AVG(price) AS value FROM items"
    time_column: null

  total_catalog_value:
    description: "Sum of all item prices, USD. Proxy for catalog breadth weighted by price."
    sql: "SELECT SUM(price) AS value FROM items"
    time_column: null

  nutrition_coverage_pct:
    description: "Share of items that have a nutrition record. Lower means the nutrition pipeline lags catalog growth."
    sql: |
      SELECT 100.0 * COUNT(n.id) / COUNT(i.id) AS value
      FROM items i LEFT JOIN item_nutrition n ON i.id = n.item_id
    time_column: null
    decomposable: false  # uses table aliases (i, n) incompatible with GROUP BY rewrite

  new_nutrition_records:
    description: "Count of nutrition records first added in a time window. Time-aware."
    sql: |
      SELECT COUNT(*) AS value FROM item_nutrition
      WHERE created_at >= :start AND created_at < :end
    time_column: item_nutrition.created_at

  avg_calories_new_records:
    description: "Mean calories on JSON-valid nutrition records added in a window."
    sql: |
      SELECT AVG(CAST(json_extract(nutrition, '$.parsed.calories') AS DOUBLE)) AS value
      FROM item_nutrition
      WHERE created_at >= :start AND created_at < :end AND nutrition LIKE '{%'
    time_column: item_nutrition.created_at

# Named dimensions usable in decompose_metric. Each dimension is a SQL
# expression evaluated on the metric's grain (or a documented join).
dimensions:
  store:
    description: "Source supermarket."
    sql: "items.store"
    cardinality: 2

  category:
    description: "Top-level product category from category_path."
    sql: "split_part(items.category_path, ' > ', 1)"
    cardinality: ~30

  price_band:
    description: "Coarse price tier."
    sql: |
      CASE
        WHEN items.price < 3 THEN 'cheap'
        WHEN items.price < 10 THEN 'mid'
        WHEN items.price < 30 THEN 'premium'
        ELSE 'luxury'
      END
    cardinality: 4