Templates Specification

Status: v1.12 (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-09-14

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.

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.

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).

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 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 version pins any version of the template (§5.4) — the refusal names who blocks; it does not change delete semantics.
• 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.

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 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.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 UI template editor (in web module) provides:

• Syntax highlighting for Freemarker + SQL.
• Live preview via the /render endpoint — the author types a variable map by hand and sees the rendered SQL. Purely an authoring aid (§9).
• Validation feedback — error markers for parse errors, forbidden constructs, and import-resolution failures (the §7 checks). Undefined-variable feedback is not available here; it appears when the template is wired into a pipeline (§7.2).
• Library browser — pick library templates to import from a searchable list; picking one appends an {id, version, alias} entry to imports (the editor never writes <#import> into the body).
• Version history — view diff between versions, restore old version (creates new version with old body).
• Test render against a scratch tempdb (optional) — available for templates whose dialect matches the dialect of the tempdb engine. The engine is declared per pipeline in settings.tempdb.engine (H2 in v1; DuckDB templates become testable this way when that engine lands) — see Pipeline Contract §12.6. The editor does not hardcode H2.

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.
• 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