Add Jinja Support for SQL Templates in MXCP

[miguelbranco80]

Wecould add Jinja templating support to MXCP queries to allow for powerful, governed dynamic SQL generation. This aligns with how users work with tools like dbt, and enables support for ref-style resolution and parameter injection while preserving MXCP’s safety guarantees.

Goals

Core Features

• Jinja templating support in queries
• Integration with manifest.json from dbt to resolve:
  • ref(model_name) → resolves to schema.alias
  • source(pkg, source, table) → resolves to schema.table

Additional Features

These aren't in manifest.json but are widely used:

• var(name, default) → resolve from mxcp-site.yaml or CLI flags
• env_var(name, default) → read env vars
• config(**kwargs) → no-op (safe to ignore)
• doc(name) → optionally inline docs from manifest.nodes[].meta.docs
• metrics(name) → optionally map to pre-registered SQL blocks

Reference Implementation Snippet

ref_map = {
  node["alias"]: f"{node['schema']}.{node['alias']}"
  for node in manifest["nodes"].values()
}

source_map = {
  (src["package_name"], src["source_name"], tbl["name"]): f"{src['schema']}.{tbl['name']}"
  for src in manifest["sources"].values()
  for tbl in src["tables"]
}

env.globals.update({
  "ref": lambda name: ref_map[name],
  "source": lambda pkg, src, tbl: source_map[(pkg, src, tbl)],
  "var": lambda k, d=None: site_vars.get(k, d),
  "env_var": lambda k, d=None: os.environ.get(k, d),
  "config": lambda **kw: "",
  "doc": lambda name: load_doc_from_manifest(name),
  "metrics": lambda name: metric_sql_map[name],
})

Jinja Features to Support

Feature	Description
params()	Access to bound parameters for runtime queries
sql_identifier	Filter to safely quote SQL identifiers
sql_literal	Filter to safely quote literal values
assert_allowed	Filter to restrict values against an allowlist
if / elif / else	Basic conditional logic
for loops	Iterate over lists for column generation
macros	Reusable logic snippets for aggregations, filters

These would support tools that build dynamic aggregations, filters, or joins while preserving governance.

Benefits

• Declarative intent, dynamic execution
• Familiar syntax for dbt users
• Works with MXCP’s audit and policy model
• Enables flexible templates under strict control

Tradeoffs

• dbt vs MXCP: dbt enforces explicit modeling; this allows dynamic logic, which increases expressiveness but can be harder to govern
• LLM-generated templates may be syntactically invalid unless constraints are applied
• Adds complexity to validation and security

Open Questions

• Which Jinja globals are essential for v1?
• How should we enforce template safety: sandbox, static validation, or SQL rendering?
• Is manifest-based ref() support enough to start?