Templates Specification

Status: v1.23 (frozen contract — additive-only changes after this point) Owner: datapipelines.co core Depends on: Type System spec, Pipeline Contract spec, Configuration Reference, Metadata DB spec Last updated: 2026-11-01

1. Purpose

A Template is a versioned, Freemarker-encoded SQL generator. Pipelines reference templates by {id, version} instead of inlining SQL — templates are reusable, independently versioned, editable in their own UI screen, and sharable across pipelines.

This spec defines:

• The Template entity model.
• The Freemarker integration (allowed constructs, security constraints).
• The library macro system for sharing SQL patterns across templates.
• Template versioning rules and immutability.
• The render lifecycle (template + context → SQL string).
• Validation rules.

2. Design Principles

1. Templates are first-class entities, not embedded in pipelines. Separation lets pipelines stay small, templates be reused, edits be isolated, and versioning be tracked cleanly.
2. Templates target one dialect. A template written for Postgres is not directly executable against Oracle — different SQL syntax, different function names, different type names. The dialect field declares the target.
3. Templates are immutable per version. Editing a template creates a new version. Pipelines reference {id, version} — that reference always produces the same SQL for the same context, forever.
4. Freemarker is the template engine. Mature, well-known, embeddable in Java/Kotlin, supports macros/import for libraries. We configure it conservatively for security (§4.3).
5. Templates do not declare their variables — the pipeline does. A template has no parameter schema of its own. The pipeline's parameters block is the single declaration point (Pipeline Contract §6), and the full parameter map (after defaults are applied) is the render context passed to every template the pipeline references. A template is therefore a pure function of the calling pipeline's parameters; the same template can be reused by pipelines that declare those parameters differently. Templates keep a free-text description so humans and agents can discover what a template expects.
6. No arbitrary code execution. Templates render SQL strings, not arbitrary program logic. Freemarker's dangerous constructs are disabled (§4.2) and the body is scanned at save time (§7).
7. Nothing invalid is ever stored. Every create/update validates fully before the row is written — the universal save-time validation principle in Pipeline Contract §2. For templates that means parse-level validation (§7.1); the render-level check belongs to pipeline save, because only a pipeline knows the parameters.

3. Template Entity

3.1 JSON structure

{
  "schema_version": 1,
  "id": "acme/finance/fetch_orders.sql",
  "version": 2,
  "engine": "freemarker",
  "type": "sql",
  "dialect": "POSTGRES",
  "display_name": "Fetch Orders in Date Range",
  "description": "Pulls orders between start_date and end_date (DATE), with an include_cancelled (BOOLEAN) switch. Intended for pipelines that declare those three parameters.",
  "imports": [
    {"id": "acme/lib/date_filters.sql", "version": 1, "alias": "dates"}
  ],
  "body": "SELECT\n  order_id,\n  customer_id,\n  total_amount,\n  order_date,\n  status\nFROM orders\nWHERE <@dates.date_range column=\"order_date\" start=start_date end=end_date />\n<#if !include_cancelled>\n  AND status <> 'CANCELLED'\n</#if>",
  "created_at": "2026-08-01T10:00:00Z",
  "created_by": "user-uuid",
  "is_library": false
}

Note that the body contains no <#import> directive. The engine synthesizes the imports from the imports array at render time (§6.3); the body simply calls <@dates.date_range .../> using the declared alias.

3.2 Field reference

Render context. There is no params_schema field. The variables a body may reference are exactly the keys of the calling pipeline's parameters map, defaults applied — see Pipeline Contract §7.4.

3.3 Script templates: contract, invariants, tests (7b)

A template of type jsonata or javascript is a script: the body is one JSONata expression or the statements of JavaScript main(input), evaluated as a pure function of its input object by the scripting engine seam (engine: none — there is no Freemarker anywhere: no render, no imports, no is_library, and a ${, <# or <@ in the body is refused with template.validation.freemarker_forbidden, D-T9). The design record is transform-nodes-design; this section is the product spec of its §2.2. For JavaScript, input is in scope, helpers are nested, and the body returns the result; the editor renders function main(input) { and } as immutable labels outside the stored statements. Console lines appear inside each case result. The mandatory empty-input case runs through the real engine at save and refuses a body that returns nothing.

A script version carries three blocks, stored on template_versions as contract_json / invariants_json / tests_json (non-null exactly on the script types — chk_script_blocks) and inside the version's body_hash through the hash expression's CASE arm, so an sql/html hash is byte-identical before and after. The blocks bind strictly: an unknown field inside any block is a refusal (template.contract_invalid, details.rule: "unknown_field"), never a silent drop. A JSON value of the wrong SHAPE is the same refusal with details.rule: "wrong_type" (#333): a number or boolean where a block declares a string (before #333 "now": 12 bound as the string "12"), a string or float where it declares an integer. The message names the field and the expected shape, never the value; details.field and details.path are clipped to 160 characters, as is the key and Jackson's text an unknown_field echoes. The same coercion rule holds on the template wire reader (id, engine, display_name, description, body, schema_version, is_library) under the same code and rule; TemplateDraft's tolerance of unknown TOP-LEVEL keys (lifecycle keys on a re-import) is unchanged.

contract

invariants — a list, possibly empty, of { name, expr, message }. expr is a JSONata expression over { rows, rejects, inputs } that must evaluate to boolean true, on every test case and every real execution. Each expr is compiled at save (template.invariant_invalid on a syntax error; a non-boolean result on any case is invariant_invalid with the case named).

tests — a non-empty list of { name, input, expect }. input is the production input object with meta optional (the runner supplies { node_id: "test", context_key: null }) and mirrors production exactly (R2): in row mode, rows is the batch and inputs holds the value inputs only — the table input is NOT listed (row_case_lists_table); the runner builds inputs.<table> = the case's rows for the invariants. A case may carry "now": "<ISO-8601 TIMESTAMP>": the runner passes it as the evaluation's pinned clock; absent, the $now()/$millis() builtins refuse (a script is a pure function of its inputs — the clock is an input). expect is exactly one of { output } — the function's return value, compared after canonicalisation — or { refusal: "<code>" } — the run must refuse with that code (expect_shape). One case whose every table input and rows are empty is mandatory (empty_case_missing): a script with no test for zero rows does not save.

The blocks ride every surface that carries a template: templates_create/templates_update and the REST create/update accept them (refused on sql/html with template.blocks_not_allowed), templates_get and the GETs return them, and the export/import and promotion payloads carry them (the import hash guard recomputes over them).

Example (the design record's own — a row-mode script over staged orders):

{
  "contract": {
    "mode": "row",
    "inputs": {
      "orders":    { "kind": "table", "columns": [
                       { "name": "order_id",     "type": "INTEGER" },
                       { "name": "amount_cents", "type": "INTEGER" },
                       { "name": "customer_id",  "type": "STRING", "nullable": true } ] },
      "tz":        { "kind": "value", "type": "STRING" },
      "min_total": { "kind": "value", "type": "DECIMAL", "precision": 12, "scale": 2 }
    },
    "output": { "kind": "table", "columns": [
                  { "name": "order_id",    "type": "INTEGER" },
                  { "name": "amount",      "type": "DECIMAL", "precision": 12, "scale": 2 },
                  { "name": "customer_id", "type": "STRING" } ] },
    "rejects": true
  },
  "invariants": [
    { "name": "customer_present",
      "expr": "$count(rows[customer_id = null]) = 0",
      "message": "every accepted row carries a customer id" },
    { "name": "one_to_one",
      "expr": "$count(rows) + $count(rejects) = $count(inputs.orders)",
      "message": "every input row is accepted or rejected, never lost" }
  ],
  "tests": [
    { "name": "empty input",
      "input":  { "rows": [], "inputs": { "tz": "UTC", "min_total": 0.00 } },
      "expect": { "output": { "rows": [], "rejects": [] } } },
    { "name": "wrong shape is refused",
      "input":  { "rows": [ { "order_id": "x" } ], "inputs": { "tz": "UTC", "min_total": 0.00 } },
      "expect": { "refusal": "pipeline.script.input_contract_violation" } }
  ]
}

Save and release run the suite (§8.1 of the record). Every case runs through the real engine on the bounded evaluation pool under datapipelines.script.evaluate-timeout-seconds; its output passes the type gate against the contract (a DECIMAL output is rounded half-even to its declared scale, R1); the invariants run on it; the result is compared to expect after canonicalisation — exact equality or a refusal code, no tolerances. The suite checks suite-timeout-seconds before starting each case, after compilation; it does not interrupt a case already running or reject an overrun after the final case. See Configuration §3.28. The first failure refuses the save with template.test_failed naming the case and the assertion (a diff bounded to 2 000 chars). Release re-runs the suite on the version being released — a suite that passed at save fails at release only if the engine changed, which is the case worth catching.

3.4 Implements and drift

The semantic link between a script and the workspace's rules — the transform-nodes design record §2.3, §8.2 and §8.3 (R9), in the product voice. A rule an agent recorded as a learned fact — "a rainy day is precipitation_mm >= 2.5", "tips are excluded from revenue" — and the script that computes it are two halves of one decision. implements joins them, so whoever finds the rule finds the code, and whoever reads the code sees when the rule moved under it.

What can be cited. A script version (jsonata / javascript) may cite the learned facts it implements: a list of fact ids, each a WORKSPACE fact of kind definition, exclusion or preference that the writing workspace can see (Metadata DB §4.18's one visibility predicate). Anything else — an id that exists nowhere, another workspace's rule, a DATASOURCE fact (a script never sees a datasource, so it cannot implement a fact about one) or a malformed id — is refused before anything is written with template.implements_unresolved (400; details.fact_id names the first offender, details.reason is unknown, malformed or too_many). unknown is deliberately one answer for "absent", "not yours" and "not citable": never "it exists but you cannot see it". At most 50 citations per version. A retired fact may still be cited — the version then reads needs_review at once. On sql / html the field itself is template.blocks_not_allowed.

Not content. A citation is a claim about meaning, never a change of behaviour, so it lives in its own table (template_implements, Metadata DB §4.21) and outside body_hash: writing it never opens a draft and never bumps a version, and it is the ONE thing a RELEASED version accepts after release (§5.1). The write rides the existing update — templates_update / PUT /templates, same hash precondition:

• implements stated → exactly that list lands on the version the write produced ([] clears);
• implements absent → inherited (owner ruling 2026-09-25): an in-place draft write keeps the draft's citations, a write that opens a new draft copies the released version's forward, and the §5.1 no-op keeps its own — so an edit that never mentions citations (the script editor's Save draft is one) cannot silently drop the link;
• an update whose content equals the released version and that states implements is the §5.1 no-op for content — status: RELEASED, no draft — and the citations land on the released version. While a draft is open the precondition makes every write the draft's.

Drift (§8.2 of the record, D-S6 applied to code). When a cited fact is retired — including superseded: a superseded fact is a retired one with retired_reason = 'superseded', and its successor is the visible fact whose supersedes names it — every version citing it reads needs_review: true with retired_facts naming each retired citation and its superseded_by. The mark is computed ON READ by the template's own query (every read path: get, list, the explorer tree and search, the editor header, the pipeline editor's node card; the sidebar's REST tree takes the same expression once per page through the citation read the release warning uses, REST §24) — never stored, never auto-remapped, never an edit of the version. The name is shared with the fact trust state needs_review on purpose: both mean "re-verify before you lean on it". Clearing it is a deliberate act: cite the successor, or drop the citation, with an update. Pinning a needs_review version at pipeline release is a warning, never a refusal — the release response's warnings carries pipeline.release.template_needs_review (Pipeline Contract §13.13) and the release dialog shows a row per such pin; a fact edit never blocks a release on its own.

Discovery (§8.3). Two reverse reads, one per direction, both over the citation table's fact index:

• from the rule: every WORKSPACE fact on semantics_list, and every rule under definitions on datasources_list / datasources_get (MCP and REST), carries implemented_by: [{template_id, version}] — the live (DRAFT or RELEASED) versions citing it that the reader's template.read lens admits (a promoter's lens: RELEASED versions of the admitted names), [] when none; a DISCARDED version is never listed;
• from the template list: templates_list / GET /api/v1/templates accept implements=<fact_id> and keep the templates whose listed version cites it — an id the workspace cannot see matches nothing.

The authoring rule this enables (the skill's learn-first step): search the facts, find the definition, find the script that implements it, reuse before writing.

Moving between environments. Export, import and the promotion payload carry implements as ids; the importing workspace keeps the ids that resolve THERE (the citable rule above) and drops the rest without refusing (owner ruling 2026-09-25) — learned facts are environment-local and never promoted, so a cross-deployment promotion lands with no citations, and a same-workspace export → import round trip is lossless. An import that writes nothing (the §9.2 idempotent re-import of an identical version) writes no citations either.

4. Freemarker Integration

4.1 Freemarker version

Pinned via Gradle version catalog (see Module Structure spec). At time of writing, the latest stable Freemarker is 2.3.34 — actual pinning happens at implementation time against current stable. The Configuration incompatible-improvements version in §4.3 must be kept equal to the pinned artifact version.

4.2 Allowed Freemarker constructs

Permitted:

• ${var} interpolation — type-aware rendering (Date → ISO 8601, BigDecimal → plain string, etc.).
• <#if>, <#else>, <#elseif> conditionals.
• <#list items as item> loops over collections.
• <#assign var=value> local variables.
• <#macro name param1 param2> macro definitions.
• <@alias.macro_name .../> calls into imported library namespaces (§6).
• <#function> definitions.
• <#switch>, <#case>.
• Built-ins: ?c (computer-format), ?string("..."), ?lower_case, ?upper_case, ?size, ?has_content, ?default(...), etc.

The scan operates on the PARSED template, not on regex-stripped source (normative). A scanner that strips comments and matches text with regex is bypassable — verified against the pinned Freemarker: hiding <#-- / --> inside FTL string literals (<#assign a="<#--">…<#assign b="-->">) makes a regex comment-stripper delete a ?eval/<#include> the engine still executes, and a leading [#ftl] switches the parser to square-bracket syntax an angle-bracket regex never sees. The body is already parsed for syntax validation (§7); the forbidden-construct scan MUST walk that same FreeMarker AST (per-element expression text via TemplateElement.getDescription(), plus LibraryLoad/Import nodes) so the parser and the scanner agree by construction — a payload hidden in a string literal cannot survive, because FreeMarker's canonical AST rendering escapes </> inside literals ("<#--" prints as "\l#--"), so a literal cannot re-inject tag syntax into the scanned text. (On the pinned FreeMarker the public expression-walk API is package-private and Environment needs a render, which §7.1 forbids at save; the AST walk therefore takes a deliberate, @Deprecated-but-only-available TemplateElement/_CoreAPI dependency (the TemplateElement type appears in a few signatures, but the walk mechanics live in one file) guarded by a jar-version drift test so an upgrade fails the build rather than silently opening a hole.) The scanner additionally, at the source level, rejects: square-bracket tag/interpolation syntax ([#…, [=…) — the load-bearing guard against a [#ftl] parser switch, since pinning tagSyntax alone does not stop it (§4.3); and a leading <#ftl …> header — the header is consumed by the parser and produces no AST node, so the AST scan cannot see it, and <#ftl attributes={…}> evaluates expressions at parse time on the save thread (a measured multi-second, uninterruptible CPU burn from a tiny body, needing no forbidden built-in — the save-path DoS the length cap cannot bound). v1 disallows the header outright (§6.2); it is unnecessary for SQL bodies and is also incompatible with the synthesized import prologue. A mis-cased or malformed built-in (?EVAL, fullwidth ?ｅｖａｌ) is a parse error → template.validation.syntax_error (still rejected, never stored). The body is length-capped at save (datapipelines.templates.max-body-chars, Configuration §3.9) — over-cap bodies are rejected with template.validation.syntax_error before parsing, bounding parse cost and heap. A declared name reached through one of FreeMarker's data-model special variables — ${.vars.x}, ${.data_model.x}, .globals, .main, .namespace, .locals — is a reference to x and is reported (2026-09-28, the 194c security pass); a .-prefixed member of any other value (y.x) cannot resolve to the parameter and is not. An <#assign>/<#local>/<#global> whose value expression references a declared (or already-tainted) name taints its TARGET as a reference to that parameter from that point on — transitive through chained assignments, order-respecting, cleared by shadowing like any direct reference — so the indirection <#assign x = region>${x} (which writes the caller's value into the SQL text through a name the scan otherwise never reports) is refused (2026-09-28, #285; the AST-level assignment descriptions the taint parses are pinned in FreemarkerAstDriftTest). A literal value taints nothing: <#assign x = 1>${x} stays legal.

Forbidden — rejected at save time by the body scan (template.validation.dangerous_construct, §7):

• ?eval — evaluates a string as a Freemarker expression. There is no configuration switch that disables ?eval in Freemarker 2.3.x (verified against the Configurable setting list, §4.3), so the save-time scan is the only guard and is therefore normative, not belt-and-braces. With §4.3's class resolver and object wrapper in place, ?eval cannot reach Java classes even if one slipped through — its blast radius is confined to expressions over the render context.
• ?interpret and ?eval_json — sibling constructs to ?eval that compile a string value (which may be a render-context value, i.e. an API-supplied pipeline parameter) into template source and execute it. Like ?eval they have no configuration switch, so the save-time scan is the sole guard: they are the constructs that would let a context value become source, defeating "a context value is data, never source". Rejected on the same footing as ?eval.
• ?api — Java API access on wrapped objects. Also disabled by configuration (§4.3).
• ?new — instantiates arbitrary Java classes. Hard-disabled by configuration (§4.3).
• Execute, ObjectConstructor, freemarker.template.utility.JythonRuntime — the classic Freemarker SSTI vectors. Unreachable because class resolution is disabled entirely (§4.3).
• <#import> and <#include> inside a body. Imports are declarative (imports, §6.3) and synthesized by the engine; a literal directive in a body would bypass alias/version/is_library validation, so it is rejected.
• Any filesystem- or classpath-backed template loading (freemarker.cache.FileTemplateLoader, ClassTemplateLoader). Templates come only from the registry (§4.3).

4.3 Security configuration

Templates are authored by authenticated users but are still untrusted input to the render engine — configure for the hostile case. Freemarker's own FAQ (item 23, "allowing users to upload templates") is the reference; every setting below was checked against the Freemarker 2.3.x Configurable / Configuration / TemplateClassResolver javadoc.

val fmVersion = Configuration.VERSION_2_3_34   // keep equal to the pinned catalog version (§4.1)

val freemarkerConfig = Configuration(fmVersion).apply {
    // 1. Templates come only from the registry, keyed "id@version" (§6.3).
    //    No FileTemplateLoader, no ClassTemplateLoader — nothing filesystem- or classpath-backed.
    templateLoader = RegistryTemplateLoader(templateRegistry)

    // 2. No class resolution at all — kills ?new, ObjectConstructor, Execute, JythonRuntime.
    //    ALLOWS_NOTHING_RESOLVER, NOT SAFER_RESOLVER: Freemarker's own FAQ states
    //    SAFER_RESOLVER is "not restrictive enough" for untrusted templates.
    newBuiltinClassResolver = TemplateClassResolver.ALLOWS_NOTHING_RESOLVER

    // 3. No ?api. Freemarker's default is already false; set it explicitly so a
    //    future default change cannot silently open it.
    isAPIBuiltinEnabled = false

    // 4. The render context holds only canonical-typed scalars and lists (§4.4),
    //    so the wrapper never needs to expose Java members at all.
    objectWrapper = SimpleObjectWrapper(fmVersion)

    // 5. Render failures propagate as errors — never partially into the SQL string.
    templateExceptionHandler = TemplateExceptionHandler.RETHROW_HANDLER
    logTemplateExceptions = false

    // 6. Pin the tag/interpolation syntax. NOTE (verified against the pinned jar): this pin does
    //    NOT by itself stop a leading [#ftl] from switching the parser to square-bracket syntax —
    //    `[#ftl][#include "y"]` still parses to a live Include node even with ANGLE_BRACKET pinned.
    //    The pin only renders a BARE `[#include …]` inert text. So the LOAD-BEARING guard is §4.2's
    //    outright source-level refusal of `[#`/`[=`; this pin is defense in depth, not the barrier.
    tagSyntax = Configuration.ANGLE_BRACKET_TAG_SYNTAX
    interpolationSyntax = Configuration.LEGACY_INTERPOLATION_SYNTAX
}

Verified (2026-08-07, against the Apache Freemarker 2.3.x javadoc and FAQ):

• Configurable.setNewBuiltinClassResolver(TemplateClassResolver) exists; TemplateClassResolver.ALLOWS_NOTHING_RESOLVER ("doesn't allow resolving any classes"), SAFER_RESOLVER, and UNRESTRICTED_RESOLVER are the three published constants.
• Configurable.setAPIBuiltinEnabled(boolean) exists and is initialized to false by the Configurable constructor.
• Configurable.setTemplateExceptionHandler(TemplateExceptionHandler) exists; RETHROW_HANDLER is a published handler.
• No setting named builtin_classes exists, and no externalsCatchAll property exists on BeansWrapperConfiguration (its exposure knobs are setExposureLevel(int), setExposeFields(boolean), setMemberAccessPolicy(MemberAccessPolicy)). Both appeared in the v1.0 draft of this section and were invented; they are deleted.
• Freemarker "cannot enforce CPU/memory limits" (FAQ) — the render timeout below therefore cannot be a Freemarker setting.

Implementation gate — verify before merging the engine module:

1. Accessor forms only: confirm the Kotlin property spellings (isAPIBuiltinEnabled, newBuiltinClassResolver, logTemplateExceptions) resolve against the pinned artifact, and the SimpleObjectWrapper(Version) constructor arity. The requirements above are fixed; only the call spelling is subject to this check.
2. If the render context ever grows beyond canonical scalars/collections, SimpleObjectWrapper must be replaced by a DefaultObjectWrapperBuilder configured with a WhitelistMemberAccessPolicy — not by relaxing the resolver.
3. Re-read the Freemarker SECURITY.md advisories for the pinned version and add any new hardening knob it introduces.

Render guards. Two limits apply to every render, both configured centrally — see Configuration §3.9; this doc never restates their defaults:

• datapipelines.templates.render-timeout-ms — wall-clock cap on a single render. Freemarker has no timeout setting, so the render runs on a worker and a watchdog interrupts it at the cap. The implementation-gate question is now answered against the pinned version: a plain Thread.interrupt() does NOT abort a Freemarker render — a <#list 1..2000000000> keeps running after interrupt()+cancel(true), and merely abandoning the worker leaks one core-burning thread per runaway, without bound. Therefore the engine MUST (a) register freemarker.core.ThreadInterruptionSupportTemplatePostProcessor (present in the pinned jar) so interruption actually aborts the render, and (b) run renders on a bounded worker pool with a rejection policy, so a leaked worker cannot accumulate. Exceeding the cap fails the node with pipeline.node.template_render_failed.
• Output size — a render whose accumulated output exceeds the staging batch memory budget is aborted under the same error code. Memory accounting rules are owned by Staging §8. Note the output writer cap alone does not bound heap: a template can accumulate in a <#assign s=s+s> variable without writing a byte, so the bounded worker + interruption above (not the writer cap) is what stops in-memory growth.

4.4 Type-aware interpolation

${var} rendering must respect the canonical Type System types:

For SQL contexts requiring quoted values, the template author wraps the interpolation in the dialect's quoting: '${start_date}' (single quotes for date literals in standard SQL).

4.5 Binding declared parameters (v1.6, 042)

A parameter DECLARED in the pipeline's parameters block is a value, and is referenced in SQL as a bind parameter: :start_date. The executor translates every :name into a positional parameter on a prepared statement (spring-jdbc's NamedParameterUtils, pinned) and binds the coerced value — DATE as LocalDate, BIGDECIMAL as BigDecimal, and so on, with no quoting syntax around it. A STRING value is therefore never parsed as SQL: a payload of x' OR '1'='1 matches no row, and a ; DROP TABLE … payload is one inert scalar. The execution semantics — including the loud failure for a :name no parameter declares — are in §8.4.

Pipeline save enforces the form: a template that interpolates a declared parameter inside ${} is refused with template.validation.parameter_interpolated (Pipeline Contract §13.9), and the message names both the ${name} written and the :name form to write (§7.2). The refusal follows indirection too: a value assigned from the parameter and interpolated through its alias (<#assign x = region>${x}) is the same refusal, naming the parameter (2026-09-28, #285). The same scan follows the two other binders that carry a value into SQL text: a <#list> loop variable listed from the parameter (<#list region?split(",") as r>'${r}' — the CSV-to-IN-list idiom) and a user-macro call whose argument is the parameter (<@where v=region/>), each reported as the parameter itself (the 279 merge's security pass, 2026-09-28). A DERIVED value is refused too — <#assign clause = region?has_content?then("AND region = :region", "")>${clause} is refused although only the bound reaches SQL; write it as <#if region?has_content>AND region = :region</#if>.

What stays the author's responsibility. ${} interpolation is for structure — table names, dynamic IN lists, ORDER BY fragments — and remains exactly as trusting as it always was: interpolate only what the author controls, never a declared parameter (which the save-time rule now refuses). Freemarker's ${} does NOT escape SQL, and there is deliberately no ?sql_escape built-in: for values binding made it unnecessary, and for structure an escape built-in would invite the belief that interpolation is now safe.

5. Template Versioning

5.1 Rules

• Versions are integers, monotonically increasing per template id.
• A RELEASED (or DISCARDED) version is immutable — body, imports, dialect, engine cannot be changed once released; no write path touches a non-draft row's content, and the entity purge is refused unless the sole version is a DRAFT. The one exception is not content: a script version's implements citations (§3.4, 7e) are outside body_hash and may be written on a RELEASED version through the same update (an implements-only write opens no draft). The sole DRAFT is overwritten in place by templates_update / PUT /templates (117, §5.3): same id, same version number, new content and a new body_hash; templates_purge_draft deletes it. The render caches key on content, not on {id, version} alone (132, §8.3) — a draft you updated is the body that renders and runs next, on every instance, without a restart.
• Deleting a template (soft delete) does not affect existing versions. Pipelines referencing deleted templates' versions continue to work until those pipelines are explicitly modified. The delete is refused with 409 template.in_use while any pipeline, parameter-set or visualization version pins any version of the template (§5.4) — the refusal names who blocks; it does not change delete semantics. Since #320 the same refusal guards every verb that removes a version: the DRAFT purges (POST /api/v1/templates/draft/discard, DELETE /api/v1/templates/version, the UI purge) ask the exact-pin question of the draft's own version, or the any-version question when the draft is the only version and the purge takes the template with it (Versioning §3.5.3); before #320 they ran no pin check at all.
• Versions never reused, never renumbered.

5.2 Lifecycle

Create template        → version 1, DRAFT      (current_version stays null)
Release (human, UI)    → version 1 RELEASED    (current_version = 1)
Update template body   → version 2 DRAFT       (1 is preserved, immutable)
Release again          → version 2 RELEASED    (current_version = 2)
Soft-delete            → version 2 marked deleted; pipelines referencing v1/v2 still work

Creation lands a DRAFT (Versioning §3.6, ruling D55, 2026-09-08) — POST /templates, templates_create and the editor's create alike, with status: "DRAFT" in the response and templates.current_version null until a human releases it. A DRAFT pipeline may pin a DRAFT template version and render against it while iterating; a pipeline's RELEASE is what requires every pinned template version to be RELEASED (§6 of versioning — templates lock first), so the ordering is unchanged. The exception is the path that is not authoring: POST /templates/import (promotion, and the seeders that ride it) lands RELEASED.

5.3 What "update" means

PUT /templates (the id in the body — rest-api §8's addressing rule: the name never travels in a URL path segment) creates a new version. The request body contains the new body, imports, etc. The server:

1. Runs the full §7 validation set — Freemarker parse only, forbidden-construct scan, and import-graph resolution. No render is attempted: a template does not know its callers' parameters, so there is no context to render against (§7.1).
2. Stores as a new version (current version + 1).
3. Does NOT modify or remove previous versions.

The MCP twin is templates_update (MCP §6.2.36): the same parse-only validation and the SAME draft write (TemplateDraftService.write), with the If-Match hash precondition carried as the required expected_hash argument instead of a header. A payload naming a different type than the template's established one is refused with template.validation.type_immutable on every surface. templates_purge_draft is NOT the edit verb — it hard-deletes a never-released draft and is refused once any pipeline pins the template.

Existing pipelines are unaffected: they pin {id, version}, so a new version is invisible to them until a pipeline is edited to reference it — at which point that pipeline's save re-runs the dry-render check (Pipeline Contract §12.6).

5.4 Used-by: the reverse arrow (v1.9, 040)

A node's template pin has a forward direction (pipeline → template version) that the whole lifecycle above protects. Used-by is the arrow pointing back, and it answers two different questions — never one query:

The split is load-bearing in both directions: a working-version-only delete guard would let a still-pinned historical version be removed, and an any-version used-by listing would nag about pins no working pipeline carries anymore. Each answer names the pipeline, the node id within it, and the pipeline version carrying the pin — a fact an author can go and change, not just a count. On the set arm it names the set, the parameter, and the set version carrying the pin (the record's §8.4); the two scans compose in application's TemplateUsage, so the tool, the screen and the guard cannot disagree about what a reference is.

The pipeline read carries the forward-side signal: when a node's pinned template has a newer released version, the MCP pipelines_get response states it (upgrade_available: node, template id, pinned version, latest released). Surfaced, never applied — moving a pin is a pipeline edit and stays the author's decision. A pin of a template draft version is not an upgrade signal (the author is ahead of release, which is information, not a prompt).

6. Library Templates

6.1 Motivation

Companies have common SQL patterns: date-range filters, status filters, common JOIN shapes, aggregate templates. Duplicating these across pipeline templates is error-prone and unmaintainable.

Library templates solve this. A library template defines <#macro>s that other templates import and call.

6.2 Example library template

<#-- acme/lib/date_filters.sql version 1 — is_library: true -->
<#macro date_range column start end>
  ${column} BETWEEN '${start}' AND '${end}'
</#macro>

<#macro recent_only column days=30>
  ${column} >= CURRENT_DATE - INTERVAL '${days}' DAY
</#macro>

Marked is_library: true. The body field is still required — it is where the macros live. What a library must not have is output outside macro definitions: everything at the top level is <#macro> / <#function> / comments. A library that emits text of its own would inject that text into every importer. A leading <#ftl …> header is not permitted (§4.2) — it is a save-path DoS surface and is incompatible with the synthesized import prologue (which must precede it), so v1 disallows it for templates and libraries alike.

6.3 Importing in a regular template

A template declares its libraries in the imports array. Each entry binds one library version to one namespace alias:

"imports": [
  {"id": "acme/lib/date_filters.sql", "version": 1, "alias": "dates"}
]

The body then calls the macros through the alias — and contains no import directive:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE <@dates.date_range column="order_date" start=start_date end=end_date />
  AND status = 'ACTIVE'

The render engine:

1. Resolves each imports entry to a stored library version and registers it with the template loader under the key "{id}@{version}".
2. Synthesizes the equivalent <#import "/{id}@{version}" as {alias}> prologue from the array — root-based (Template Hierarchy §4.4: with / legal in ids, a bare name would resolve relative to a hierarchical importer's directory and silently mis-resolve; the loader strips exactly the one leading / on the way back in) — the author never writes it, so an alias can never point at an unvalidated, unpinned, or non-library template. Because alias and id are interpolated into that synthesized directive, they are validated as strict identifiers before synthesis: alias matches [a-zA-Z_][a-zA-Z0-9_]* and id matches the §7 id rule; version is a positive integer. A value that fails — an alias or id carrying Freemarker metacharacters, whitespace, or a directive fragment, or a non-positive version — is a prologue-injection attempt and is rejected with template.validation.dangerous_construct (§4.2/§7); the refusal message never echoes the offending value back into logs. The loader independently re-checks the key on the read path, failing closed on any row that reached storage by another route.
3. Resolves transitive imports the same way (a library's own imports array), building the full closure.
4. Renders the main body against the pipeline's parameter map.

Because the loader only ever resolves "{id}@{version}" keys against the registry, there is no template name a body could reference to escape the registry — which is why §4.2 forbids literal <#import>/<#include>.

6.4 Import resolution rules

• Imports are resolved by {id, version} from the template registry — exact version, no ranges, no "latest".
• Version pinning is explicit — bumping a library to v2 does NOT silently change templates that imported v1. Authors must update the importing template to use v2 (which creates a new version of the importer).
• An imported template must exist at that exact version (template.validation.import_not_found) and must have is_library: true (template.validation.import_not_library).
• Aliases must be unique within a template's imports array (template.validation.duplicate_alias). Two libraries may share macro names as long as their aliases differ — that is what namespacing is for.
• Imports are transitive: library A can import library B. Transitive depth is capped at 10 (template.validation.import_depth_exceeded). The cap is a fixed constant, not a config key.
• Cycles forbidden: A imports B, B imports A → template.validation.import_cycle.

6.5 Library promotion across environments

Libraries are promoted like regular templates — export bundle includes them. Importing pipelines fail validation in the target env if a referenced library version is missing (pipeline.import.missing_template).

7. Validation Rules

All checks below run at template create/update time, before anything is written (D2 — Pipeline Contract §2). The canonical HTTP status and wording for each code live in the central catalog, Pipeline Contract §13.9.

7.1 Save-time validation is parse-only

• At template save — everything in the table above: Freemarker syntax validity, the forbidden-construct scan, and full import-graph resolution (existence, is_library, alias uniqueness, depth, cycles). No render is performed. A template declares no parameters (§2.5), so at save time there is no context to render against and no basis for a synthetic one. There is deliberately no "sample context" anywhere in this contract.
• At script save (7b) — the §3.3 order, after the static rules: body parse through the scripting engine, contract validation, invariant compile, then the test suite — every case on the bounded evaluation pool, gated, invariant-checked and compared to expect after canonicalisation. A draft is re-validated on every update, and release re-runs the suite on the version being released; both refuse with template.test_failed on the first failing case. A script's cases are its "sample context" — declared, versioned and hash-covered, never synthetic.
• At pipeline save — the render-level check. Pipeline validation dry-renders every template its nodes reference against the pipeline's declared parameters (defaults where present, type-appropriate sample values otherwise). See §7.2.
• At execution — nothing new is checked. Both gates above have already run; a render failure at execution time is a bug or an environment drift, and surfaces as pipeline.node.template_render_failed (§8.2).

7.2 The dry-render rule (owned by pipeline validation)

The rule that catches template/pipeline drift lives on the pipeline side, because only a pipeline knows the parameters:

At pipeline save, for every template referenced by every node, the template (plus its resolved import closure) must render successfully against the pipeline's declared parameters. Any Freemarker variable the body references that has no corresponding pipeline parameter is an undefined-variable render failure, and validation fails with pipeline.validation.template_parameter_undeclared.

Normative definition: Pipeline Contract §7.4 and §12.6. This catches typos and template-pipeline drift at write time, not at execution time.

A declared parameter referenced inside ${} is refused (042 B2). Pipeline save also scans each referenced template's parse tree: a declared parameter name found inside a ${} interpolation fails with template.validation.parameter_interpolated (HTTP 400, Pipeline Contract §13.9) — a declared parameter is a value and must be referenced as :name (§4.5), and the message names both forms. The scan is AST-based for the same evasion-proofing reason as §4.2's construct scan, and honours macro-parameter and loop-variable shadowing; a backslash "escape" is pinned to be a live interpolation in 2.3.34, so no spelling hides one. The declared set includes every calculator output key (078 A1), which is refused in a conditional's test (<#if x??>, <#elseif x>) as well: a derived value gating SQL structure is the same hole.

A consequence worth stating plainly: a template that is perfectly valid on its own can still be un-referenceable by a given pipeline. That is intended — the template is reusable, and each pipeline proves for itself that it supplies what the template needs.

8. Render Lifecycle

8.0 Evaluate beside render (7b)

Render is the sql/html verb; evaluate is the script verb. templates_render and POST /api/v1/templates/render refuse a script type with template.render_not_applicable (details.use: "templates_evaluate"). templates_evaluate and POST /api/v1/templates/evaluate take { name|id, version?, input } (the §9.6 addressing form; version omitted = the working version, exactly as render), apply the contract's input check (the record's §5.1), evaluate on the bounded evaluation pool under datapipelines.script.evaluate-timeout-seconds, gate the output against the contract, run every invariant, and answer { output, rejects, invariants: [ { name, passed, message } ] } — no staging, no Context. A refusal is the code with its detail (the §5.1 input check, the type gate, or the engine's own). The evaluate of a draft script version counts as its render for the render-before-you-run check (139) — without it a draft pipeline pinning a draft script would be refused at execute forever.

8.1 Single-template render

Input: TemplateRef{id, version}, Context{the pipeline's full parameter map, defaults applied}
Process:
  1. Look up template body + imports from registry by {id, version}
  2. Resolve the import closure transitively; register each library
     with the loader under "{id}@{version}"
  3. Synthesize the <#import ... as alias> prologue from the imports array
  4. Render body + prologue via Freemarker, under the §4.3 configuration
     and the §4.4 render guards (timeout, output size)
  5. Return SQL string
Output: SQL string

There is no per-template type-check step: the context is the pipeline's parameter map, already validated and coerced against the pipeline's own parameters declarations (Pipeline Contract §6.3) before any render begins.

8.2 Error behavior

• Template {id, version} not found → pipeline.node.template_not_found (should not happen — write-time validation should have caught this).
• Variable in body not in context → pipeline.node.template_render_failed (details include the variable name and template id+version). Should not happen either: the pipeline's dry-render (§7.2) covers it.
• Render exceeds render-timeout-ms or the output size cap → pipeline.node.template_render_failed (details name which guard tripped).
• Forbidden construct somehow present (shouldn't be — caught at save time) → pipeline.node.template_render_failed.

8.3 Performance and caching

• Parsed templates are cached in memory after first render. Cache capacity is datapipelines.templates.cache-size — see Configuration §3.9. This doc does not restate the default.
• Two cache tiers, both per workspace engine and both keyed on content, not on {id, version} alone (132):
  • the resolved-version cache (RepositoryTemplateRegistry) holds only non-DRAFT rows — a RELEASED or DISCARDED version can never change, so an entry can never go stale; a DRAFT is re-read on every lookup (one indexed row read, ≈0.15 ms on a local Postgres, paid only while the version is a draft and edited) and is cached from the read that first sees it RELEASED;
  • the parsed-template cache (InterruptibleConfiguration) is keyed {id}@{version}#{body_hash} — a draft overwritten in place changes its hash, misses, and is parsed afresh; a released version's hash never changes, so it keeps hitting. The loader's lastModified is the row's write stamp (updated_at, else created_at), so Freemarker's own staleness check would see the change too.
• Invalidation is therefore by construction on every instance: nothing is evicted on write, no cross-instance bus is needed — another instance simply reads the draft's row too. An old entry remains valid for in-flight executions (an evicted or superseded tree is unshared). Before 132 both tiers were keyed on {id}@{version} under the pre-117 premise that every stored version is immutable; after the first render of a draft, every later templates_render and execution silently ran that first body until a restart.
• Render itself is fast (<10ms typical for templates up to ~5KB body). The render-timeout-ms guard (§4.3) exists for pathological bodies, not for the normal path.

8.4 Named-parameter binding at execution (v1.6, 042)

The rendered SQL may carry :name references. The executor translates them before the driver sees anything — spring-jdbc 6.2.19's NamedParameterUtils, pinned and standalone, not a hand-rolled SQL scanner — and binds the coerced context values on a prepared statement. Two properties define the contract:

• Missing names fail loudly. A :name the execution context does not declare fails with pipeline.node.sql_parameter_missing (HTTP 500, Pipeline Contract §13.4) before anything executes — never a silently-null predicate, which would return wrong data instead of an error.
• Values bind as values. DATE binds as LocalDate, BIGDECIMAL as BigDecimal, and so on — no quoting syntax is used around a bound reference. DATE ':start_date' is wrong in the same way '${start_date}' now is.

What the translator does with dialect syntax (pinned by NamedParameterTranslationTest against the pinned jar, cited rather than re-derived):

• Postgres/H2/DuckDB/SQLite :: casts survive untouched — col::text and :param coexist in one statement. The house habit of writing CAST(:x AS …) is belt-and-braces, not a requirement.
• '…' literals, "…" identifiers, MySQL `…` backtick identifiers, and -- / /* … */ comments are skipped correctly.
• Four constructs are not understood, and a colon inside them is mis-read as a parameter: MySQL # comments, MSSQL [a:b] identifiers, Oracle q'[…]' strings, and PostgreSQL $$…$$ dollar-quoting. Such a template fails loudly (sql_parameter_missing, naming the mis-read name) when that name has no value, and is visibly mangled in templates_render when it does. The fix is the author's: rephrase the construct. Values themselves never re-enter the parser, so the injection property §4.5 exists for is unaffected by these.

H2 and a bound parameter inside a grouped expression. A parameter inside a CASE (or any expression) that is repeated verbatim in GROUP BY can fail H2's "must be in the GROUP BY list" validation at execution — the shipped nyc/mobility/sample_rain_vs_dry.sql hit exactly this after its 042 migration and was fixed by grouping on ordinals (GROUP BY 1, 2; the derived-table spelling — computing the expression once in a subquery and grouping by the column — works too). When a grouped expression carries a :name, prefer one of those two shapes.

9. CRUD Operations (Brief — full HTTP in REST API spec)

The name never travels in a URL path segment (Template Hierarchy §9.6 — a name may contain /, and the container refuses an encoded %2F below routing). The type list filter is sql/html (046).

The /render endpoint is an editor preview affordance only — the caller explicitly supplies the variable map to render against. It is not a validation step and it never runs implicitly: template save is parse-only (§7.1), and the authoritative render check is the pipeline dry-render (§7.2).

10. UI Editor Integration

The template workspace (in web module, since #398 — GET /templates/{name}, ui-screens §4.7) provides:

• Read-only source with highlighting — the viewed version's body renders as a read-only pane; the preview output on the Render tab is highlighted with the shared dependency-free SQL tokenizer. The editable textarea (an author, on the working draft) is deliberately plain: highlighting an editing surface needs an overlay/contenteditable round of its own.
• Render preview via the /render endpoint — the author types a variable map by hand in the Render tab (Key/Value rows or JSON) and sees the rendered SQL against the STORED version the page is viewing. Purely an authoring aid (§9).
• Validation feedback on write — the script face's Save draft runs the §7 checks (the suite included) and names each refusal's pane; the create modal binds the same binder, so a refusal the API would give is a refusal there. Undefined-variable feedback is not available here; it appears when the template is wired into a pipeline (§7.2).
• Version history — the workspace's Versions tab (the house table): status, provenance, the per-version "N uses" phrase, and the lifecycle verbs (release, purge, discard, restore) through their §4.3d dialogs. "Restore an old version" is drafting-and-releasing: a version is immutable, so re-publishing copies it into a new draft (the Source tab's Edit on a selected release does exactly that). There is no diff view between versions.
• Library templates — the Imports table on the Source tab shows the version's {id, version} import rows read-only. There is no in-browser picker: imports is written through the API (the editor never writes <#import> into the body, and no UI writes it at all).

What the §10 bullets below described before #398 and were never built (they describe no route in the web module; listed here so the record is honest rather than carrying the claim): a library browser with import picking, a version diff view, and a test render against a scratch tempdb. The render preview above is the only scratch render the UI has ever had.

11. Stability Promise

The Template entity is versioned, additive-only.

11.1 Frozen in v1

• The Template JSON shape (top-level fields, imports entry shape {id, version, alias}).
• The rule that templates declare no parameters of their own — the pipeline's parameters is the single declaration point.
• The Freemarker integration contract (allowed/forbidden constructs).
• The is_library convention.
• The import resolution rules, including body-never-imports.
• The version-immutability guarantee.

11.2 Not frozen

• New optional fields (tags, metadata, etc.) may be added non-breakingly.
• The script blocks (§3.3) may grow: new optional fields on the contract/input/test shapes, and 7e's implements citation, land non-breakingly; a block's fields are refused when UNKNOWN (unknown_field), so growth is a deliberate doc-plus-code move, not a silent one (7b).
• Conditional-requirement relaxations are named explicitly (amended 2026-09-02, 046): making a previously required field conditional on another — as 046 did to dialect, required iff type='sql' — is not covered by "new optional fields" above, because §11.1 froze the field's presence as part of the shape. Such a relaxation is compatible in practice when no existing payload becomes invalid and every stored row already satisfies the new rule (every pre-046 template backfills to type='sql' with its dialect intact), but the claim must be written down in the change log of the round that takes it, not assumed under §11.2's first bullet.
• The render cache and timeout configuration are deployment-specific (Configuration §3.9).
• Future template languages beyond Freemarker are out of scope but possible (would be a new engine value).

12. Implementation Notes

12.1 Where this lives

templates Gradle module:

• co.datapipelines.templates.Template data class
• co.datapipelines.templates.TemplateVersion data class
• co.datapipelines.templates.TemplateImport data class (id, version, alias)
• co.datapipelines.templates.TemplateRegistry — lookup by id+version, caching
• co.datapipelines.templates.RegistryTemplateLoader — the Freemarker TemplateLoader of §4.3; resolves only "{id}@{version}" keys against the registry
• co.datapipelines.templates.TemplateEngine — wraps Freemarker, exposes render(ref, context): String, owns the §4.3 render guards
• co.datapipelines.templates.TemplateValidator — runs §7 checks
• co.datapipelines.templates.LibraryResolver — resolves transitive imports, enforces depth/cycle/alias rules

The module's public API carries no parameter-schema types.

12.2 Persistence

templates and template_versions live in the app's metadata DB. The DDL is defined once, in Metadata DB §4 — that spec is the sole DDL authority (D4) and this section deliberately carries no CREATE TABLE block.

What metadata-db must express for this spec to hold:

• template_versions carries engine and is_library alongside dialect, imports (JSONB), and body.
• There is no params_schema column on either table.
• imports JSONB is an array of {id, version, alias} (§3.2).
• (template_id, version) is the primary key; versions are never updated in place (§5.1).

12.3 Testing

• Unit tests for TemplateValidator covering every check in §7, including the negative cases: duplicate alias, import of a non-library template, depth 11, and a two-node cycle.
• Round-trip tests: every sample template in docs/examples/ must parse, validate, and resolve its import closure. Render assertions belong to the pipeline fixtures that supply the parameters.
• Forbidden-construct tests: every Freemarker SSTI vector (from public security advisories) must be rejected at save time, and a literal <#import> in a body must be rejected.
• Hardening tests: with the §4.3 configuration, ?new, ?api, Execute, and ObjectConstructor must all fail at render even when the save-time scan is bypassed — the two layers are tested independently.
• Library import tests: transitive imports, depth limits, cycle detection, missing library handling, alias namespacing of same-named macros in two libraries.
• Render-guard tests: a runaway <#list> trips render-timeout-ms; an oversized output trips the size cap.
• Performance tests: render latency for templates up to a 50KB body with a wide import fan-out at the maximum transitive depth (§6.4 caps depth at 10 — an earlier "20 imports deep" here contradicted that cap; read it as ≤10 deep, up to ~20 imports wide). Also: a save-time adversarial-input suite — a body at max-body-chars, deeply-nested parens/<#if>, and a wide import DAG — must complete within a bounded time (guards the §4.2 AST scan and §6.4 traversal against the quadratic/exponential blowups a regex scanner and an unmemoized walk exhibit).

13. Open Questions / Future Additions

Out of scope for v1:

• Alternative engines: support Pebble, Handlebars, or Thymeleaf for SQL alongside Freemarker. New engine value on templates. Not v1.
• Parameterized SQL output: instead of rendering to a single SQL string, render to {sql, params} for prepared-statement execution. Closes the SQL-injection gap. v1.1 candidate. Shipped as bound :name parameters (2026-09-01, 042) — see §4.5: the render stays a single SQL string, but declared parameters are written as :name and the executor binds them on prepared statements, so the {sql, params} framing was never needed.
• Multi-dialect templates: a single template with dialect-conditional sections (<#if dialect == "ORACLE">...<#else>...</#if>). v2 candidate.
• Template testing framework: declarative test cases per template (given this context, render should match this expected SQL). Useful for regression testing.
• Template composition visualizer: UI to show how imports resolve into the final rendered SQL.

Appendix A: Worked Example

Library: acme/lib/aggregate.sql v1 (is_library: true)

<#macro sum_by group_by_column value_column table>
  SELECT
    ${group_by_column} AS group_key,
    SUM(${value_column}) AS total
  FROM ${table}
  GROUP BY ${group_by_column}
</#macro>

<#macro count_by group_by_column table>
  SELECT
    ${group_by_column} AS group_key,
    COUNT(*) AS record_count
  FROM ${table}
  GROUP BY ${group_by_column}
</#macro>

Nothing outside the macro definitions — the library emits no output of its own (§6.2).

Pipeline template: acme/finance/monthly_revenue.sql v3 (is_library: false)

Its imports array:

"imports": [
  {"id": "acme/lib/aggregate.sql", "version": 1, "alias": "agg"}
]

Its body — no <#import> directive; the engine synthesizes <#import "acme/lib/aggregate.sql@1" as agg>:

WITH revenue AS (
  <@agg.sum_by
     group_by_column="customer_id"
     value_column="total_amount"
     table="stg_orders" />
)
SELECT
  c.customer_name,
  r.total AS revenue,
  r.total / 100.0 AS revenue_display
FROM revenue r
JOIN stg_customers c ON r.group_key = c.customer_id
WHERE r.total >= ${min_total}
ORDER BY r.total DESC

(Plain ${min_total} — not ${min_total?c}. §4.4's type-aware rendering already emits a BIGDECIMAL(12,2) as the plain, computer-safe decimal string 1000.00 with its declared scale; ?c is unnecessary here and actively wrong — every published Freemarker CFormat drops trailing zeros, so ?c would render 1000 and lose the declared scale §4.4 promises. Reserve ?c for cases where you explicitly want scale-less computer format.)

Render context

The template declares nothing. The context is the calling pipeline's parameter map, which must therefore declare min_total:

"parameters": {
  "min_total": {
    "type": "BIGDECIMAL", "required": false, "default": "1000.00",
    "precision": 12, "scale": 2
  }
}

At pipeline save, the dry-render (§7.2) renders this template against {min_total: BigDecimal("1000.00")} and succeeds. Had the pipeline omitted min_total, the save would fail with pipeline.validation.template_parameter_undeclared.

Rendered SQL

WITH revenue AS (
  SELECT
    customer_id AS group_key,
    SUM(total_amount) AS total
  FROM stg_orders
  GROUP BY customer_id
)
SELECT
  c.customer_name,
  r.total AS revenue,
  r.total / 100.0 AS revenue_display
FROM revenue r
JOIN stg_customers c ON r.group_key = c.customer_id
WHERE r.total >= 1000.00
ORDER BY r.total DESC

Appendix B: Change Log