18000x · 03 — Migration 032 substrate (Template Versioning + Observability)

Authored artifacts

Path	Purpose
sql/iu-core/032_template_versioning_and_observability.sql	forward DDL
sql/iu-core/rollback/032_template_versioning_and_observability.rollback.sql	rollback with REFUSED guard
sql/iu-core/sandbox/290_template_versioning_probe.sql	11 BEGIN/ROLLBACK probes

Surface additions

Table (+1)

public.iu_collection_template_version (
  collection_id           uuid PRIMARY KEY REFERENCES iu_piece_collection(id) ON DELETE RESTRICT,
  template_family         text NOT NULL,
  version_label           text NOT NULL,
  version_seq             integer NOT NULL,
  previous_collection_id  uuid REFERENCES iu_piece_collection(id) ON DELETE RESTRICT,
  version_status          text NOT NULL DEFAULT 'current'
       CHECK (version_status IN ('current','superseded','retired')),
  registered_by           text NOT NULL,
  registered_at           timestamptz NOT NULL DEFAULT now(),
  retired_at              timestamptz,
  retire_reason           text,
  CHECK (length(btrim(template_family)) > 0),
  CHECK (length(btrim(version_label)) > 0),
  CHECK (version_seq >= 1),
  CHECK (previous_collection_id IS NULL OR previous_collection_id <> collection_id),
  CHECK (length(btrim(registered_by)) > 0)
);
UNIQUE INDEX iu_template_version_family_seq_uniq (template_family, version_seq);
INDEX idx_iu_template_version_previous (previous_collection_id);

Sidecar PK=FK against iu_piece_collection (same pattern as mig 031). ON DELETE RESTRICT — collections are soft-deleted, RESTRICT is safer.

Views (+2)

• public.v_iu_template_version_chain — version chain with predecessor pointer + computed flags is_root_version, is_current_version, digest_drift_from_previous.
• public.v_iu_template_observability — single-row-per-template consolidated view: registry (031) + version (032) + instance lineage cardinality + digest match breakdown + flags template_without_version, version_without_template_marker.

Functions (+2)

• fn_iu_collection_register_template_version(uuid, text, text, integer, uuid, text) → jsonb
• fn_iu_collection_retire_template_version(uuid, text, text) → jsonb

Both SECURITY DEFINER, idempotent (ON CONFLICT (PK) DO UPDATE).

Refusal taxonomy:

Status	Caused by
invalid_input	NULL id / blank family / blank label / blank actor / bad version_seq
self_predecessor_refused	previous_collection_id == collection_id
collection_not_found	collection_id doesn't exist
template_not_marked	collection not in iu_collection_template_registry
previous_not_found / previous_not_marked	predecessor missing or not a template
family_seq_collision	(template_family, version_seq) owned by a different collection_id
version_not_found	retire called on unregistered collection_id
ok	success

Live apply transcript

BEGIN
CREATE TABLE       -- iu_collection_template_version
CREATE INDEX       -- iu_template_version_family_seq_uniq
CREATE INDEX       -- idx_iu_template_version_previous
COMMENT
CREATE VIEW        -- v_iu_template_version_chain
COMMENT
CREATE VIEW        -- v_iu_template_observability
COMMENT
CREATE FUNCTION    -- fn_iu_collection_register_template_version
COMMENT
CREATE FUNCTION    -- fn_iu_collection_retire_template_version
COMMENT
COMMIT

D9 conformance verdict (post-apply)

 D9_conformance | config     | 12 | 12 | t
 D9_conformance | event_type | 21 | 21 | t
 D9_conformance | function   | 63 | 63 | t
 D9_conformance | route      | 16 | 16 | t
 D9_conformance | table      | 28 | 28 | t
 D9_conformance | trigger    |  6 |  6 | t
 D9_conformance | view       | 28 | 28 | t
 D8_unregistered: 0 rows

7/7 PASS at 174 objects, zero drift.

Sandbox 290 probe (11/11 PASS)

• 290.1 invalid_null_id → t
• 290.2 invalid_blank_family → t
• 290.3 invalid_version_seq → t
• 290.4 self_predecessor_refused → t
• 290.5 collection_not_found → t
• 290.6 template_not_marked → t
• 290.7 retire_invalid_input → t
• 290.8 retire_version_not_found → t
• 290.9 fn_present (both) → t
• 290.10 view_chain_selectable → t
• 290.11 view_observability_selectable → t

All inside BEGIN…ROLLBACK; no durable side effects.

Rollback contract

1. Drop views (depend on version table)
2. Drop functions
3. Check row count; if any rows + iu_core.force_rollback_032 GUC not set → RAISE EXCEPTION REFUSED
4. Drop table only after guard accepts

Same convention as 001..004, 031 rollbacks (REFUSED on non-empty + force-toggle via session-level GUC).