Metadata for AI & BI

AI business engines — text-to-SQL agents, BI copilots, semantic-search layers — are only as good as the metadata they can find. When they ask “what does this table mean, what does this column hold, how do these objects relate?”, the answer has to live somewhere machine-readable.

SQLAnvil’s answer: declare it once in the model, and it persists into the warehouse’s own catalog on every run. Descriptions aren’t a sidecar file or a proprietary metadata store — they land as native COMMENTs and object descriptions, exactly where every catalog-aware tool already looks.

Declaring metadata

Every action config accepts a description (the object) and a columns: {} block (its fields):

config {
  type: "table",
  description: "One row per customer order. Source of truth for revenue reporting.",
  columns: {
    order_id: "Unique order identifier, assigned at checkout.",
    customer_id: "The ordering customer — joins to public.customers.order_id.",
    order_date: "Date the order was placed (warehouse local time).",
    amount: "Order total in USD, after discounts, before tax."
  }
}

SELECT ...

The same fields work in actions.yaml entries and via .columns() in JS — see the reference. Declarations accept columns: {} too, so external sources can be documented alongside your models (see scope below).

What persists, where

Metadata is applied by the adapter after every run, so it survives full-replace rebuilds — a table that is dropped and recreated gets its comments re-applied in the same run:

Warehouse	Object description	Column descriptions	Ends up in
PostgreSQL	COMMENT ON TABLE / VIEW / MATERIALIZED VIEW	COMMENT ON COLUMN	pg_description, information_schema
Supabase	same as Postgres	same as Postgres	Postgres catalogs (visible in Studio and any catalog-aware tool)
BigQuery	table description	column descriptions, including nested fields, plus policy tags	Table metadata, INFORMATION_SCHEMA
MySQL / MariaDB	ALTER TABLE … COMMENT	column comments	information_schema

Notes:

• MySQL views cannot carry comments (a MySQL limitation, not a SQLAnvil one) — the adapter skips them rather than failing.
• Declarations aren’t executed, so nothing is written to the source system — but their descriptions and columns are part of the compiled graph and the catalog artifacts, so agents still see them.

Where an AI engine reads it

In the warehouse — the point of persisting natively is that nothing SQLAnvil-specific is needed to read it back:

-- Postgres / Supabase
SELECT c.column_name, pgd.description
FROM information_schema.columns c
JOIN pg_catalog.pg_statio_all_tables st ON st.relname = c.table_name
LEFT JOIN pg_catalog.pg_description pgd
  ON pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position
WHERE c.table_name = 'daily_sales';

-- BigQuery (nested fields included)
SELECT field_path, description
FROM `my-project.analytics.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE table_name = 'daily_sales';

-- MySQL / MariaDB
SELECT COLUMN_NAME, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'analytics' AND TABLE_NAME = 'daily_sales';

Outside the warehouse — the same metadata is available without a connection:

• target/catalog/*.parquet — columns.parquet holds every column descriptor in the project; dependencies.parquet holds the object graph. Queryable with sqlanvil query, no warehouse needed.
• sqlanvil docs — a self-contained HTML catalog of models, descriptions, and columns.
• On SQLAnvil Cloud, agents can reach project and run state over the MCP server.

Keys and relationships

Key and relationship metadata comes from two complementary mechanisms: declare constraints into the warehouse catalog with post_operations, and enforce them with uniqueKey/assertions.

Declaring constraints: post_operations

A post_operations { } block runs right after the object is built — on every run — so constraints land in the warehouse catalog and persist across rebuilds:

-- Postgres / Supabase / MySQL: real, enforced constraints
post_operations {
  ALTER TABLE ${self()} ADD PRIMARY KEY (order_id)
  ---
  ALTER TABLE ${self()} ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES ${ref("customers")} (customer_id)
}

-- BigQuery: the same, with NOT ENFORCED (BigQuery stores these as catalog
-- metadata for exactly this purpose — query engines and AI tools read them)
post_operations {
  ALTER TABLE ${self()} ADD PRIMARY KEY (order_id) NOT ENFORCED
  ---
  ALTER TABLE ${self()} ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES ${ref("customers")} (customer_id) NOT ENFORCED
}

One caveat by table type: a full-replace table is recreated each run, so plain ADD works every time. An incremental table keeps its constraints between runs — guard the ALTER so it only fires on the initial full build:

post_operations {
  ${when(!incremental(),
    `ALTER TABLE ${self()} ADD PRIMARY KEY (order_id)`)}
}

Enforcing keys: uniqueKey and assertions

• uniqueKey on incremental tables declares the merge key — rows are upserted on it, so uniqueness is maintained by construction. On MySQL, SQLAnvil creates the required unique index automatically.
• assertions: { uniqueKey: [...], nonNull: [...] } turn key claims into tested guarantees, verified on every run. Multiple key sets? Use the plural form: assertions: { uniqueKeys: [["order_id"], ["customer_id", "order_date"]] }.
• ${ref()} builds the object graph: which models feed which — exported in dependencies.parquet and drawn as the DAG in Cloud.

Together: post_operations puts PK/FK facts where catalog-reading engines look, uniqueKey/assertions make those facts verified rather than aspirational, and the ref() graph supplies lineage.

A practical pattern

For models an AI engine will query, treat metadata as part of the model’s contract:

config {
  type: "incremental",
  uniqueKey: ["order_id"],
  description: "One row per order; incrementally merged on order_id.",
  columns: {
    order_id: "Primary key. Unique order identifier.",
    customer_id: "Foreign key to public.customers.customer_id.",
    amount: "Order total in USD, after discounts, before tax."
  },
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "customer_id", "amount"]
  }
}

SELECT ...

post_operations {
  ${when(!incremental(),
    `ALTER TABLE ${self()} ADD PRIMARY KEY (order_id)`)}
}

Descriptions carry the semantics, post_operations writes the key facts into the catalog, uniqueKey + assertions make them tested claims, and every run keeps the warehouse in sync with the code.