50000x · 02 — Phase D envelope auto-refresh — APPLY BLOCKED (CHANNEL)

What the phase set out to do

Wire a third statement-level AFTER INSERT trigger on iu_collection_template_instance_lineage so that when the orchestrator composes a productized instance, the existing migration 023 drift-gated refresh wrapper (fn_iu_three_axis_envelope_refresh_if_stale) is called with a distinct actor iu_auto_instantiate_trigger — closing the gap documented in 40000x carry-forward §D.

What was authored

• sql/iu-core/036_envelope_auto_refresh_on_auto_compose.sql
  • fn_iu_three_axis_envelope_auto_refresh_lineage_trigger() — STATEMENT level, gate-controlled fast-path, EXCEPTION-swallowing.
  • trg_iu_three_axis_envelope_auto_refresh_lineage AFTER INSERT on iu_collection_template_instance_lineage FOR EACH STATEMENT.
• sql/iu-core/rollback/036_envelope_auto_refresh_on_auto_compose.rollback.sql
  • Drops trigger first then function. IF EXISTS guards.

Pure additive; no existing 023/024/033 object modified. Inherits the already-audited iu_core.three_axis_auto_refresh_enabled gate (no new gate). Statement-level → bulk scaleout = O(1) refresh attempts.

Why it did not apply

Live discovery during the first apply attempt:

ERROR: permission denied for table iu_collection_template_instance_lineage

Root cause:

\dp iu_collection_template_instance_lineage
  workflow_admin = arwdDxt/workflow_admin    -- owner
  directus       = arwdx/workflow_admin      -- NO 't' (TRIGGER) privilege
  context_pack_readonly = r/workflow_admin

The application connection role directus (which prior macros 034 and 035 used successfully because those were data-only seeds via INSERT/UPDATE/DELETE) is granted arwdx — SELECT/INSERT/UPDATE/DELETE/REFERENCES — but not TRIGGER. The TRIGGER privilege is held only by the owner workflow_admin. This is a deliberate separation-of-duty boundary in the role grants for productized tables (migrations 031, 032, 033 all created their tables as workflow_admin).

The transaction rolled back atomically when the CREATE TRIGGER failed; a post-failure check confirms fn_iu_three_axis_envelope_auto_refresh_lineage_trigger absent and the DB clean.

Why this is CHANNEL-BLOCKED, not a design defect

Two unblocking paths exist; both are out-of-scope for this macro:

1. One-time GRANT TRIGGER ON iu_collection_template_instance_lineage TO directus; issued by a workflow_admin-credentialed session. After the grant, mig 036 applies clean from the directus role (which is the role this clone has credentials for).
2. Apply mig 036 as workflow_admin directly. Requires workflow_admin password / role-switch, which is not present in the Mac shell environment of this macro.

The Auto-Scope Ladder dictates packaging this as carry-forward without asking the user.

Carry-forward unblock recipe

Insert into the next macro's preflight:

-- Run AS workflow_admin (one-time):
GRANT TRIGGER ON public.iu_collection_template_instance_lineage TO directus;
-- THEN as directus (existing channel):
psql -U directus -d directus -v ON_ERROR_STOP=on \
  -f sql/iu-core/036_envelope_auto_refresh_on_auto_compose.sql

Post-apply SSOT bumps that this macro deliberately did NOT make:

• sql/iu-core/runtime/110_iu_core_dot_conformance_scan.sql:
  • Add ('function', 'fn_iu_three_axis_envelope_auto_refresh_lineage_trigger')
  • Add ('trigger', 'trg_iu_three_axis_envelope_auto_refresh_lineage')
  • Bump VALUES tuple: function 65 → 66, trigger 6 → 7
• tests/test_iu_core_ddl.py::EXPECTED_COUNTS: function 65 → 66, trigger 6 → 7; total 181 → 183.

State invariants preserved

• DB live: trigger function does NOT exist; trigger does NOT exist. ✓
• SSOT: 181-object D9 conformance (unchanged). ✓
• Tests: 1305 OK (unchanged). ✓
• Working tree: only new files (migration + rollback) untracked at start; now committed as 6cffa59. ✓