# Templates Specification **Status:** v1.23 (frozen contract — additive-only changes after this point) **Owner:** datapipelines.co core **Depends on:** [Type System spec](/docs/type-system.md), [Pipeline Contract spec](/docs/pipeline-contract.md), [Configuration Reference](/docs/configuration.md), [Metadata DB spec](/docs/metadata-db.md) **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](/docs/pipeline-contract.md#6-parameters-input-map-declaration)), 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](/docs/pipeline-contract.md#2-design-principles). 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 ```json { "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", "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 | Field | Type | Required | Description | |---|---|---|---| | `schema_version` | integer | yes | Currently `1` — the only value v1 accepts; any other is **rejected at save time** with `template.validation.schema_version_unsupported` (§7). Independent of the entity `version` below — see [Pipeline Contract §15.4](/docs/pipeline-contract.md#154-which-version-counter-governs-what). | | `id` | string | yes | Stable identifier — a path of **2–10** `/`-separated segments, each `[a-z0-9][a-z0-9_.-]{0,63}`, ≤ 200 chars total ([Template Hierarchy §4.1](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)); the full path IS the name, and **a folder is required** (`test/scratch`, not `scratch`). Auto-generated if omitted on create — under `test/`, since a name nobody chose is scratch and §4.1 admits no root-level asset. | | `version` | integer | yes | Monotonically increasing per template id. Server-assigned. | | `engine` | string (enum) | optional (default `"freemarker"`) | Template engine, **matched to the type** (since 7b): `"freemarker"` iff `sql`/`html`, `"none"` iff `jsonata`/`javascript` — any other pairing is **rejected at save time** with `template.validation.engine_unsupported` (§7). Values are catalogued in [Enums §6](/docs/enums.md#6-templateengine--template-language). | | `type` | string (enum) | optional (default `"sql"`) | The template's kind: `sql`, `html`, or one of the two script types `jsonata` / `javascript` ([Enums §6A](/docs/enums.md#6a-templatetype--template-kind); the script types since 7b, transform-nodes design §2.1). Accepted on **create only** — defaulting to `sql` when absent — and identical on every version of the template; a write payload carrying a different type is refused with `template.validation.type_immutable` (§7). `sql` renders SQL through the escaping-free configuration and is the only kind a pipeline node may reference; `html` renders through a second, auto-escaping configuration ([Template Hierarchy §6](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)) and takes **no** `dialect`; a script type renders nothing — the body is a JSONata expression or the statements of `main(input)` evaluated as a pure function of its input (§3.3), with `engine: none`, no `dialect`, no `imports` and `is_library: false`, and its version carries the contract/invariants/tests blocks. JavaScript requires the installed isolate; absent, save refuses with `script.js.unavailable`. An unknown value is refused with `template.validation.type_invalid`. | | `dialect` | string (enum) | yes — **iff `type` is `sql`** | One of `POSTGRES`, `ORACLE`, `MSSQL`, `MYSQL`, `H2`, `DUCKDB`, `SQLITE`. Required on `sql` (absence is `template.validation.dialect_invalid`); a `dialect` present on any other type (`html` or a script type) is refused with `template.validation.dialect_not_allowed` (§7). | | `display_name` | string | yes | Human-readable name. | | `description` | string | yes | Free-text, for human and agent discoverability. Not machine-validated: it is the only place a template can hint at the parameters it expects, since it declares none (§2.5). | | `imports` | array of `{id, version, alias}` | yes (may be empty) | Library templates whose macros this template calls, and the namespace alias each is bound to. See §6. Only meaningful when `engine` is `"freemarker"` — non-empty on a script type is refused with `template.validation.freemarker_forbidden` (§7). | | `body` | string | yes | Template source. Syntax depends on `engine` (Freemarker by default). Multi-line. Must **not** contain `<#import>` or `<#include>` directives (§6.3). | | `created_at` | ISO 8601 timestamp | yes | Server-assigned. | | `created_by` | string (UUID) | yes | User ID of creator. | | `is_library` | boolean | yes | `true` if this template exists to be imported by others. A library body contains **only `<#macro>` / `<#function>` definitions — no output outside macro definitions** (§6.2); `body` is still required. `false` if the template is executable directly by a pipeline node. `true` on a script type is refused with `template.validation.freemarker_forbidden` (§7). | | `implements` | array of learned-fact ids | optional — **script types only** | The workspace rules this script version implements (§3.4, 7e): ids of `WORKSPACE` facts of kind `definition` / `exclusion` / `preference` visible from the workspace, at most 50. **Not content** — outside `body_hash`. Read back sorted; `[]` on a script that cites none, `null` on `sql`/`html` (present there, it is `template.blocks_not_allowed`). On an update, absent is INHERITED and `[]` clears (§3.4). | | `needs_review` | boolean | read-only | `true` when a fact this version cites has been retired (§3.4) — computed on read, never stored; always present, `false` on every `sql`/`html` version. | | `retired_facts` | array of `{fact_id, retired_reason, superseded_by}` | read-only | The retired citations behind `needs_review` — `superseded_by` is the fact that replaced it (null for a plain retirement). Omitted when empty. | **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](/docs/pipeline-contract.md#74-template-variable-resolution). ### 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](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-09-transform-nodes-design.md); 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`** | Field | Rule | |---|---| | `mode` | `row` \| `table` \| `value`. A `row` body reads `rows`; a `table`/`value` body reads `inputs.`. `row` requires exactly one `table` input (`template.contract_invalid`, detail `row_mode_inputs`). | | `inputs` | Name → `{ kind: "table", columns[] }` or `{ kind: "value", type, precision?, scale? }`. Names obey `[a-z_][a-z0-9_]*` (`name_invalid`). At least one input (`inputs_empty`). `columns[]` entries are `{ name, type, precision?, scale?, nullable? }`; `type` is a `LogicalType` wire name; `nullable` defaults to false. `BINARY` and `NULL` are refused as declared types (`type_unsupported`). precision/scale follow type-system.md §4 (`precision_scale_invalid`). | | `output` | `{ kind: "table", columns[] }` for `row`/`table`; `{ kind: "value", type, … }` or `{ kind: "object" }` for `value`. A mode/output.kind mismatch is `mode_output_mismatch`. | | `rejects` | Boolean, default false; legal only with a table output (`rejects_without_table`). When true the function returns `{ rows: [...], rejects: [ { row, reason } ] }` and `reason` is a non-empty string. | **`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.` = the case's `rows` for the invariants. A case may carry `"now": ""`: 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: "" }` — 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): ```json { "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](/docs/configuration.md#328-script-evaluation-7b-7). 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](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-09-transform-nodes-design.md) §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](/docs/metadata-db.md#418-learned_facts)'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](/docs/metadata-db.md#421-template_implements)) 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](/docs/rest-api.md)) — 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](/docs/pipeline-contract.md#1313-versioning--draft-release-lifecycle--promotion)) 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=`** 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](/docs/module-structure.md)). 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](/docs/configuration.md#39-templates)) — 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. ```kotlin 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](/docs/configuration.md#39-templates); 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](/docs/staging.md). 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: | Canonical type in context | Rendered as | |---|---| | `INTEGER` | Plain integer string (`42`) | | `BIGINTEGER` | Plain integer string (`9223372036854775807`) | | `DECIMAL(p,s)` (exact) | Plain decimal string with declared scale (`12345.67`) | | `DECIMAL(p)` (approximate) | Plain decimal string (`3.141592653589793`) | | `BIGDECIMAL(p,s)` | Plain decimal string with declared scale | | `BOOLEAN` | `true` / `false` (lowercase) | | `STRING` | Verbatim string | | `DATE` | ISO 8601 date (`2026-08-05`) | | `TIME` | ISO 8601 time (`14:30:00`) | | `TIMESTAMP` | ISO 8601 datetime, UTC, with `Z` (`2026-08-05T14:30:00Z`) | | `BINARY` | Base64-encoded string | 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`. **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](/docs/versioning.md#353-the-reverse-arrows-into-other-families-320)); 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](/docs/versioning.md#36-the-one-write-rule-copy-on-write), 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](/docs/mcp-server.md#6236-templates_update)): 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](/docs/pipeline-contract.md#126-template-validations)). ### 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**: | Question | Scans | Drives | |---|---|---| | *Who is using `t@2` right now?* | each pipeline's **working version** — the draft when one exists, else the latest released (the §7 read rule, [Versioning §7.1](/docs/versioning.md#71-authoring-reads-return-the-working-version-039)) — **and, since #194 lane D, each parameter set's working version** (the same rule over `parameter_set_versions`) **and, since #320, each visualization's working version** (its `transform.template` pin, over `visualization_versions`) | the `templates_used_by` MCP tool (pipeline rows, set rows **and `visualization_references`**), the template screen's per-version in-use count and "Used by" card (sets and visualizations included), the pipeline read's upgrade signal — and, since lane D, the set read's `upgrade_available` per pinned template | | *Is it safe to remove `t`?* | **any pipeline version, ever**, **any parameter-set version, ever** and **any visualization version, ever** — historical versions are immutable and executable, so their pins are real references (the discard and the draft purge of a template that keeps other versions ask the exact version, LIVE dependents only) | the `409 template.in_use` refusal on every removing verb (§5.1), whose `details` carry `referencing_parameter_sets` and `referencing_visualizations` beside `pinned_by` | 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 ```ftl <#-- acme/lib/date_filters.sql version 1 — is_library: true --> <#macro date_range column start end> ${column} BETWEEN '${start}' AND '${end}' <#macro recent_only column days=30> ${column} >= CURRENT_DATE - INTERVAL '${days}' DAY ``` 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: ```json "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**: ```ftl 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](/docs/pipeline-contract.md#2-design-principles)). The canonical HTTP status and wording for each code live in the central catalog, [Pipeline Contract §13.9](/docs/pipeline-contract.md#139-template). | Code | Check | |---|---| | `template.validation.dialect_invalid` | `dialect` is in the allowed enum — and is present whenever the type is `sql` | | `template.validation.type_invalid` | `type` is one of `sql`, `html`, `jsonata`, `javascript` (046, [Template Hierarchy §5.4](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md); the script types since 7b) | | `template.validation.dialect_not_allowed` | No `dialect` on a type that declares none — `html` or a script type (046, [Template Hierarchy §7](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)) | | `template.validation.type_immutable` | A write payload carries a `type` other than the template's established one — the type is chosen at create and identical on every version (046, [Template Hierarchy §5.3](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)) | | `template.validation.engine_unsupported` | `engine` matches the type: `"freemarker"` iff `sql`/`html`, `"none"` iff a script type (7b, record §2.1) | | `template.validation.freemarker_forbidden` | A script type carries `imports` or `is_library: true`, or its body contains a Freemarker construct (`${`, `<#`, `<@`) — D-T9; `details.rule` names which (`imports` / `is_library` / `body`) | | `template.validation.schema_version_unsupported` | `schema_version` is a value v1 supports (only `1`) | | `template.contract_invalid` | A script block failed a §3.3 rule; `details.rule` names which (`blocks_missing`, `unknown_field`, `wrong_type`, `inputs_empty`, `name_invalid`, `row_mode_inputs`, `type_unsupported`, `precision_scale_invalid`, `mode_output_mismatch`, `rejects_without_table`, `empty_case_missing`, `row_case_lists_table`, `expect_shape`) | | `template.invariant_invalid` | An invariant does not compile, or produced a non-boolean on a test case — `details` names the invariant (and the case) | | `template.test_failed` | A test case failed at save or at release — `details` names the case and the assertion (a diff bounded to 2 000 chars), or the invariant that is false | | `template.blocks_not_allowed` | `contract` / `invariants` / `tests` on an `sql`/`html` template — the blocks belong to the script types only | | `template.render_not_applicable` | `templates_render` / `POST /api/v1/templates/render` on a script type — there is nothing to render; `details.use` points at `templates_evaluate` | | `template.validation.id_invalid` | `id` is a path of 2–10 segments, each `[a-z0-9][a-z0-9_.-]{0,63}`, ≤ 200 chars total — a folder is required ([Template Hierarchy §4.1](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)). `details.reason` is `folder_required` when a folder is the only thing missing, `grammar` otherwise | | `template.validation.syntax_error` | Freemarker parses the body without syntax errors — or, for a script type, the scripting engine parses it (with the engine's line/column) | | `template.validation.dangerous_construct` | Body uses a forbidden Freemarker construct — including a literal `<#import>`/`<#include>` (see §4.2) | | `template.validation.duplicate_alias` | No two `imports` entries share an `alias` | | `template.validation.import_not_found` | Every `imports` entry resolves to an existing template at that exact version | | `template.validation.import_not_library` | Every imported template has `is_library: true` | | `template.validation.import_cycle` | Import graph is acyclic | | `template.validation.import_depth_exceeded` | Transitive import depth ≤ 10 | | `template.validation.is_library_without_macros` | `is_library: true` requires at least one `<#macro>` **or** `<#function>` definition and no output outside those definitions (FreeMarker represents both as one node type; a `<#function>`-only library is legal) | ### 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](/docs/pipeline-contract.md#74-template-variable-resolution) and [§12.6](/docs/pipeline-contract.md#126-template-validations). 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](/docs/pipeline-contract.md#63-wire-encoding-of-input-parameter-values)) 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](/docs/configuration.md#39-templates). 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) | Operation | Method & Path | |---|---| | Create template | `POST /templates` | | Get working version | `GET /templates?name={id}` | | Get specific version | `GET /templates/versions?name={id}&version={n}` | | Update (writes the draft branch) | `PUT /templates` — `id` in the body; MCP twin `templates_update` (§6.2.36) | | List | `GET /templates?dialect={d}&type={t}&q={search}&implements={fact_id}` — `implements` keeps the templates whose listed version cites that learned fact (§3.4, 7e) | | Delete (soft; `409 template.in_use` while referenced — §5.4) | `DELETE /templates?name={id}` | | Preview render with a caller-supplied context | `POST /templates/render` — `name`, `version`, `context` in the body | The name never travels in a URL path segment ([Template Hierarchy §9.6](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md) — 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](/docs/configuration.md#39-templates)). - 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](/docs/metadata-db.md) — 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>...`). 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`) ```ftl <#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 count_by group_by_column table> SELECT ${group_by_column} AS group_key, COUNT(*) AS record_count FROM ${table} GROUP BY ${group_by_column} ``` 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: ```json "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>`: ```ftl 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`: ```json "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 ```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 | Date | Version | Author | Change | |---|---|---|---| | 2026-10-22 | v1.23 | 8b (#8) | The JavaScript statements body, immutable frame, isolate availability and pooled save verdict ship. | | 2026-10-21 | v1.22 | #640 the script-node rename (script-node-rename lane, V50, owner ruling 2026-10-20) | **The script template types**: "script types", §"Script templates", the CHECK `chk_script_blocks`, the script face and its editor, the `script.*` codes. A rename only: no behaviour, route, permission or key change beyond the names, and no alias (the ruling). The visualization block's `transform` key, `transform.template` / `transform.inputs` and the CSS/SVG sense keep their word. | | 2026-10-16 | v1.21 | 533 (#533) the sidebar tree's mark | §3.4 (additive clause): the sidebar's REST tree is a read path of `needs_review` — its template leaves take the mark from the same retired-citation expression, in ONE citation read per page keyed by name and listed version (the port the pipeline release's warning uses), not from the template projection; [REST §24](/docs/rest-api.md) v2.96 carries it as the node's `needs_review`. No rule, code or status changed. | | 2026-10-02 | v1.20 | 333 (#333) the strict contract readers | §3.3: a JSON number or boolean where a template field or a transform block declares a STRING (and a string or float where it declares an integer) is refused, never bound as text — Jackson's `StringDeserializer` takes a scalar as text unless the `Textual` coercion config fails it, and `ALLOW_COERCION_OF_SCALARS` off never reached that bind. `template.contract_invalid` gains `details.rule: "wrong_type"` (the keys `rule`, `field`, `path` are unchanged); an `unknown_field` echo is clipped to 160 characters. No new code, no status change; `TemplateDraft`'s `ignoreUnknown` stays. | | 2026-09-30 | v1.19 | 320 (#320) dependency guards | §5.1/§5.4: the `template.in_use` guard now covers **visualizations** (a `transform.template` pin) beside pipelines and parameter sets, and every verb that removes a version — the DRAFT purges included, which ran no pin check before. `templates_used_by` gains `visualization_references`; the template screen's per-version count and "Used by" card list sets and visualizations; the lifecycle dialogs name them. The pipeline purge's exclusive-draft offer skips a draft template a set or a visualization also pins ([Versioning §3.5.3](/docs/versioning.md#353-the-reverse-arrows-into-other-families-320)). | | 2026-09-25 | v1.14 | 7e (#7) the semantic link | New **§3.4 Implements and drift**: a transform version cites the WORKSPACE `definition`/`exclusion`/`preference` facts it implements (`template.implements_unresolved` otherwise, not-found semantics; `blocks_not_allowed` on `sql`/`html`); citations are outside `body_hash`, editable on a released version, INHERITED when an update omits them (owner ruling 2026-09-25); a retired cited fact marks every citing version `needs_review` on read with `retired_facts` naming it and its successor, cleared only by a deliberate re-cite; release warns (`pipeline.release.template_needs_review`), never refuses; `implemented_by` on the fact surfaces and `implements=` on the list for discovery; import keeps only the ids that resolve in the importing workspace (owner ruling 2026-09-25). §3.2 gains `implements`, `needs_review`, `retired_facts`; §5.1 names the one post-release write; §9 the list filter. Status caught up (it read v1.12 after v1.13's row). | | 2026-09-23 | v1.13 | 7b (#7) transform templates | **The two transform types land**: `jsonata` and `javascript` as `TemplateType` values (§3.2; `javascript` refused at save until round two). `engine` becomes type-conditional (`none` iff a transform type); `dialect` is forbidden outside `sql`; `imports`/`is_library`/Freemarker-in-body are `template.validation.freemarker_forbidden` (D-T9). New **§3.3**: the contract/invariants/tests blocks — strict binding (`unknown_field`), every model rule with its detail name, the mandatory empty case (R2's `row_case_lists_table`, the pinned clock), and the save/release test-suite rule on the bounded evaluation pool. §7's table gains `freemarker_forbidden`, `contract_invalid`, `invariant_invalid`, `test_failed`, `blocks_not_allowed`, `render_not_applicable`; §8 gains **§8.0 Evaluate beside render** (`templates_evaluate` / `POST /api/v1/templates/evaluate`, the render refusal, and the evaluate-counts-as-render rule for Check B); §11.2 notes the blocks' growth rule. The `TemplateTypeBehaviour` object owns the per-type rules (record §2.4), with the sql/html refusals proven byte-identical by the golden. | | 2026-09-14 | v1.12 | 132 draft cache | §5.1's immutability rule is now exact: a RELEASED/DISCARDED version is immutable, the sole DRAFT is overwritten in place (117) and purged. §8.3 rewritten: both render-cache tiers key on content — the registry caches non-DRAFT rows only and re-reads a draft on every lookup; the parsed-template cache keys on `{id}@{version}#{body_hash}`; the loader's `lastModified` is the row's write stamp; invalidation is by construction on every instance, no bus. Fixes the 2026-09-14 acceptance defect where a rendered-then-updated draft kept rendering and executing its first body. | | 2026-09-11 | v1.11 | 117 templates_update | §5.3 gains the MCP twin: `templates_update` ([MCP §6.2.36](/docs/mcp-server.md#6236-templates_update)) — the same parse-only validation and the same `TemplateDraftService.write` the REST PUT makes, the `If-Match` hash carried as the required `expected_hash` argument, and the type-immutability refusal named on every surface. §9's CRUD table names the twin. `templates_purge_draft` is explicitly not the edit verb. | | 2026-09-08 | v1.10 | 099 draft-first (D55) | §5.2's lifecycle: **create lands version 1 as a DRAFT** (`POST /templates`, `templates_create`, the editor), `templates.current_version` is null until a human releases, and the release step is now shown in the sequence. A DRAFT pipeline may pin a DRAFT template version while iterating — unchanged; the pipeline's RELEASE is still what requires released pins (versioning §6). The import path (promotion, the seeders) lands RELEASED. | | 2026-09-06 | v1.10 | 078 contract gaps | §7.2's interpolation scan: the declared set gains every calculator output key (a CALCULATOR node's `context_key`, typed by its kind's output type), refused inside `${}` with `template.validation.parameter_interpolated` like a declared parameter — and additionally in a conditional's test (`<#if x??>`, `<#elseif x>`), because a derived value gating SQL structure is the same hole one directive earlier. Declared parameters in directive tests stay legal (042 B1 unchanged). Additive. | | 2026-09-02 | v1.9 | 040 template used-by | New **§5.4 Used-by**: the reverse arrow from a template version to its pinning pipelines — two questions kept apart (working-version scan for "who uses `t@2` now", any-version scan for "is it safe to remove"), the `templates_used_by` MCP tool and the per-version in-use count as surfaces, and the pipeline read's `upgrade_available` signal. §5.1/§9: template delete is refused with `409 template.in_use` (§13.9) while any pipeline version pins any version — the refusal carries the referencing pipelines, nodes and versions. | | 2026-09-02 | v1.8 | 046 typed templates | Template JSON gains optional `type` (`sql` \| `html`, default `sql`, chosen at create and immutable across versions — [Template Hierarchy §5](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)); `dialect` becomes conditional — required iff `type='sql'`, forbidden on `html` (§3.1/§3.2). §7 gains `type_invalid`, `dialect_not_allowed` and `type_immutable`; §9's routes corrected to the §9.6 name-query addressing and gain the `type` list filter; §11.2 amended to name conditional-requirement relaxations explicitly — the relaxation of `dialect` is this round's own invocation of that clause (no existing payload becomes invalid; every stored template backfills to `sql` with its dialect intact). | | 2026-09-05 | v1.9 | mandatory folders (077) | §3.2 `id` requires a **folder**: the path is now **2–10** `/`-separated segments, not 1–10 ([Template Hierarchy §4.1](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md#41-grammar)). A bare `fetch_orders.sql` is refused; `nyc/mobility/fetch_orders.sql` is the form, `test/…` is the sanctioned scratch folder and `/lib/` holds shared macros. An id auto-generated on create is now minted under `test/`, because this path runs after validation and a flat generated name would be one the next save, the tree UI and the deploy gate all refuse. §7's `template.validation.id_invalid` is unchanged as a code and gains `details.reason` (`folder_required` \| `grammar`). A **narrowing** of the frozen contract, taken pre-release on the owner ruling of 2026-09-05 — §4.5 gives no rename, so a root-level name created after the tag is permanent — and shipped with the deploy gate `V12__folder_required.sql` for stored names, because a template id is re-checked at RENDER (§4.6). | | 2026-09-01 | v1.7 | 042 implementation | New §4.5: declared parameters are values and bind as `:name` — the old §4.4 injection paragraph is replaced by the split of responsibilities it states. §7.2: pipeline save refuses a declared parameter inside `${}` with `template.validation.parameter_interpolated` (AST scan, scope-aware, no escape spelling per the 2.3.34 pin). New §8.4: execution-time binding semantics — loud `pipeline.node.sql_parameter_missing` for an undeclared `:name`, and the dialect-construct translation table pinned by `NamedParameterTranslationTest` (`::` casts survive; MySQL `#` comments, MSSQL `[a:b]`, Oracle `q'…'`, PG `$$…$$` mis-parse). §13: the "parameterized SQL output" v1.1 candidate is struck as shipped. | | 2026-08-05 | v1.0 | initial draft | Initial templates spec: entity, Freemarker config (security-hardened), library macros, versioning, validation | | 2026-08-05 | v1.1 | propagation | Added `engine` field to Template entity (default `"freemarker"`; future-proofing for Pebble/Handlebars/raw-SQL). Updated `body` and `imports` field descriptions to be engine-aware. Renamed `__staging__` → `tempdb` in UI editor section. | | 2026-08-07 | v1.2 | consistency campaign | Per [SPEC-REVIEW-2026-08 §2.8](https://github.com/msabiransari/datapipelines/blob/main/docs/SPEC-REVIEW-2026-08.md#28-templatesmd): removed `params_schema` entirely (D3 — pipeline `parameters` is the single declaration point; template save is parse-only, dry-render moved to pipeline save); `imports` entries become `{id, version, alias}` with engine-synthesized `<#import>` and body-never-imports (D12); `is_library` corrected to "no output outside macro definitions", `body` required (D12); `template.import.cycle_detected` → `template.validation.import_cycle`, `body_malformed` → `syntax_error`, added `duplicate_alias` / `import_not_library` (D5); §4.3 Freemarker hardening rewritten against the verified 2.3.x API (invented `externalsCatchAll` / `builtin_classes` deleted; `ALLOWS_NOTHING_RESOLVER` replaces `SAFER_RESOLVER`) with an implementation-gate checklist; render guards reference [Configuration §3.9](/docs/configuration.md#39-templates) (D8); §12.2 DDL replaced by a pointer to [Metadata DB](/docs/metadata-db.md) (D4); §10 tempdb dialect derived from `settings.tempdb.engine`; fixed the §3.2 link to [Pipeline Contract §6](/docs/pipeline-contract.md#6-parameters-input-map-declaration). | | 2026-08-10 | v1.6 | P3 build (Gate C security re-review, TPL-SEC-10) | **A leading `<#ftl …>` header is now disallowed** (§4.2 source-level refusal + §6.2) — it produces no AST node so the scan is blind to it, and `<#ftl attributes={…}>` evaluates expressions at parse time on the save thread (a measured multi-second uninterruptible CPU burn from a ~65-byte body, needing no forbidden built-in — a save-path DoS the length cap can't bound); it is also incompatible with the synthesized import prologue (which must precede it). This reverses the v1.4 "optional leading `<#ftl>` header allowed" allowance, which was the mistake. §4.2 deprecated-API wording softened ("confined to one file" → the type appears in a few signatures, the walk mechanics are in one file). rest-api §8.1/§8.4 examples add the required `display_name`. | | 2026-08-10 | v1.5 | P3 build (Gate C fix cycle findings) | Doc-sync of what the AST-scan implementation proved against the pinned jar: §4.2 records that a string literal cannot re-inject tag syntax (FreeMarker escapes `<`/`>` in literals → the comment-strip bypass is impossible by construction) and that the scan takes a deliberate `@Deprecated`-but-only-available `TemplateElement`/`_CoreAPI` dependency (public expression-walk is package-private; `Environment` needs a render §7.1 forbids), drift-guarded; §4.3 corrected — pinning `tagSyntax` does NOT stop a leading `[#ftl]` (verified), so §4.2's outright `[#`/`[=` refusal is the load-bearing guard, the pin is defense in depth; §7 `is_library_without_macros` accepts a `<#function>`-only library (§6.2 reading; one FreeMarker node type); a mis-cased/malformed built-in is a `syntax_error` not `dangerous_construct` (parse error). | | 2026-08-09 | v1.4 | P3 build (Gate C: 1 CRITICAL + 3 HIGH) | §4.2: forbidden-construct scan made **AST-based, not regex-over-stripped-source** — a comment-strip regex is bypassable by `<#--`/`-->` hidden in FTL string literals and by a leading `[#ftl]` square-bracket switch (both verified against the pinned jar; the CRITICAL that let `?eval`/`?interpret`/`<#include>` through the sole normative save-time gate); scanner also rejects `[#`/`[=` and the body is length-capped (`datapipelines.templates.max-body-chars`, Configuration §3.9). §4.3: `tagSyntax`/`interpolationSyntax` pinned; render-guard gate answered — plain `Thread.interrupt()` does NOT abort a Freemarker render, so `ThreadInterruptionSupportTemplatePostProcessor` + a bounded worker pool are required (abandonment alone leaks a core-burning thread per runaway; the writer cap does not bound `<#assign s=s+s>` heap). §6.2: optional leading `<#ftl>` header allowed. §12.3: "20 imports deep" corrected to ≤10 (contradicted the §6.4 cap) + adversarial-input timing suite. rest-api §8.4: template `dialect` may change across versions (existing pipelines pin a version). | | 2026-08-09 | v1.3 | P3 build (Gate B) | `?interpret`/`?eval_json` added to §4.2's forbidden list (context-value-to-source siblings of `?eval`, no config switch). §3.2 `engine` and `schema_version` now rejected at save when unsupported — new codes `template.validation.engine_unsupported` / `template.validation.schema_version_unsupported` (§7, [Pipeline Contract §13.9](/docs/pipeline-contract.md#139-template)) close the silent-mis-render gap. §6.3: `imports` `alias`/`id`/`version` validated as strict identifiers before prologue synthesis (prologue-injection attempt → `dangerous_construct`, message never echoes the value; loader re-checks fail-closed). Appendix A worked example corrected `${min_total?c}` → `${min_total}` (`?c` drops the declared scale §4.4 promises). | | 2026-09-28 | v1.18 | 194d (#194) parameter sets — renumbered at merge after 279's v1.16 and the 279 pass's v1.17 | **§5.4's two scans cover PARAMETER SETS** (the record's §8.4): a set's working-version pins join the used-by listing and the screen's per-version in-use counts, the ANY-version set pins join the `template.in_use` delete guard (`details.referencing_parameter_sets` beside `pinned_by`), and `parameter_sets_get` answers the upgrade signal per pinned template. The composition lives in `application`'s `TemplateUsage`; `templates` consumes it and is otherwise untouched. | | 2026-09-28 | v1.17 | the 279 merge's security pass | §4.5's scan follows the `<#list>` loop-variable and user-macro-call binders (a value listed or passed into a macro is reported as the parameter), and the doc says a derived value is refused with the `<#if>` rewrite. Falsified in `TemplateDryRendererImplTest`; `UnifiedCall` pinned in `FreemarkerAstDriftTest`. | | 2026-09-28 | v1.16 | 285 (#285) the assignment taint | The scan follows `<#assign>`/`<#local>`/`<#global>` indirection: a target whose value expression references a declared (or already-tainted) name is itself a reference to that parameter — `<#assign x = region>${x}` is now `template.validation.parameter_interpolated`, transitively through chained assignments, order-respecting, cleared by shadowing, with a literal value tainting nothing. Option (b) of the issue is already covered by the 194c special-variable reporting; option (c) is refused by the parameter-engine record §5.2. The assignment node's class and description shapes are pinned in `FreemarkerAstDriftTest`; the new scan cases are red-first in `TemplateDryRendererImplTest`. | | 2026-09-28 | v1.15 | the 194c security pass | §4.5's interpolation scan reports a declared name reached through a FreeMarker data-model special variable (`${.vars.x}`, `${.data_model.x}`, `.globals`, `.main`, `.namespace`, `.locals`) — the `.`-prefix exemption for `y.x` no longer exempts them; `template.validation.parameter_interpolated` fires for both spellings. Falsified in `TemplateDryRendererImplTest`. |