15000x · 03 — Migration 031 substrate (Product Template Registry)

Authored artifacts

Path	Purpose
sql/iu-core/031_product_template_registry.sql	forward DDL
sql/iu-core/rollback/031_product_template_registry.rollback.sql	rollback with REFUSED guard
sql/iu-core/sandbox/280_product_template_registry_probe.sql	9 BEGIN/ROLLBACK probes

Surface additions

Tables (+2)

public.iu_collection_template_registry (
  collection_id     uuid PRIMARY KEY REFERENCES iu_piece_collection(id) ON DELETE RESTRICT,
  is_template       boolean NOT NULL DEFAULT true,
  template_label    text NOT NULL CHECK (length(btrim(template_label)) > 0),
  template_doc      text,
  registered_by     text NOT NULL,
  registered_at     timestamptz NOT NULL DEFAULT now()
);

public.iu_collection_template_instance_lineage (
  instance_collection_id  uuid PRIMARY KEY REFERENCES iu_piece_collection(id) ON DELETE RESTRICT,
  template_collection_id  uuid NOT NULL REFERENCES iu_piece_collection(id) ON DELETE RESTRICT,
  instantiated_by         text NOT NULL,
  instantiated_at         timestamptz NOT NULL DEFAULT now(),
  instance_note           text,
  CONSTRAINT iu_template_lineage_self_not_allowed CHECK (instance_collection_id <> template_collection_id)
);

CREATE INDEX idx_iu_template_lineage_template ON iu_collection_template_instance_lineage(template_collection_id);

ON DELETE RESTRICT — collections are soft-deleted, RESTRICT is safer.

Views (+2)

• public.v_iu_product_template — active templates with instance counts
• public.v_iu_product_template_instance — lineage with digest_matches_template (the 12000x Phase F invariant as a queryable column)

Functions (+2)

• fn_iu_collection_mark_as_template(uuid, text, text, text) → jsonb
• fn_iu_collection_record_template_instance(uuid, uuid, text, text) → jsonb

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

Refusal taxonomy:

Status	Caused by
invalid_input	NULL id / blank label / blank actor
collection_not_found	id doesn't exist
instance_not_found / template_not_found	respective id doesn't exist
template_not_marked	record_instance called on non-template
self_lineage_refused	instance_id == template_id
ok	success

Live apply transcript

BEGIN
CREATE TABLE       -- iu_collection_template_registry
CREATE TABLE       -- iu_collection_template_instance_lineage
CREATE INDEX
CREATE VIEW        -- v_iu_product_template
CREATE VIEW        -- v_iu_product_template_instance
CREATE FUNCTION    -- fn_iu_collection_mark_as_template
CREATE FUNCTION    -- fn_iu_collection_record_template_instance
COMMIT

D9 conformance verdict (post-apply)

 D9_conformance | config     | 12 | 12 | t
 D9_conformance | event_type | 21 | 21 | t
 D9_conformance | function   | 61 | 61 | t
 D9_conformance | route      | 16 | 16 | t
 D9_conformance | table      | 27 | 27 | t
 D9_conformance | trigger    |  6 |  6 | t
 D9_conformance | view       | 26 | 26 | t
 D8_unregistered: 0 rows

7/7 PASS at 169 objects, zero drift.

Sandbox 280 probe (9/9 PASS)

• 280.1 invalid_null_id → t
• 280.2 invalid_blank_label → t
• 280.3 collection_not_found → t
• 280.4 record_invalid_input → t
• 280.5 self_lineage_refused → t
• 280.6 template_not_marked → t
• 280.7 fn_present (both) → t
• 280.8 view_template_selectable → t
• 280.9 view_instance_selectable → t

All inside BEGIN…ROLLBACK; no durable side effects.

Rollback contract

1. Drop views
2. Drop functions
3. Check row counts; if any rows + iu_core.force_rollback_031 GUC not set → RAISE EXCEPTION REFUSED
4. Drop tables only after guard accepts

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