02 — Migration 023 · drift-gated auto-refresh hook

1. Why

iu_three_axis_envelope (migration 022) is populated by an operator-invoked fn_iu_three_axis_envelope_refresh. After the 2400x bootstrap there is no second call, no audit trail, no easy way to ask "how stale is the cache right now?". Migration 023 closes that gap without changing the cache's fundamental design: the view remains the SSOT for the projection; the table is a derived projection of the view.

2. Surface added (4 new objects)

Class	Name	Role
table	iu_three_axis_envelope_refresh_log	Append-only audit of every refresh-if-stale call (incl. dry-run + skipped). Indexes on started_at DESC and (actor, started_at DESC). CHECK enforces outcome in ('refreshed','skipped_in_sync','dry_run').
function	fn_iu_three_axis_envelope_refresh_if_stale(p_actor, p_dry_run, p_force)	Drift-gated wrapper. Reads fn_iu_three_axis_envelope_drift_check first; refreshes only when out-of-sync OR p_force=true. Always writes one log row.
view	v_iu_three_axis_envelope_refresh_status	One-row operator dashboard: last refresh + age + current drift + cache_healthy verdict.
config	dot_config.iu_core.three_axis_auto_refresh_enabled	Reserved gate (default false). No trigger is installed; this row exists so a future macro has a single flip-point.

Total DOT bump: +1 table / +1 view / +1 function / +1 config = +4 objects (132 → 136).

3. Wrapper behaviour matrix

p_dry_run = true                  -> outcome = 'dry_run'        (no write outside log)
p_force   = true                  -> outcome = 'refreshed'      (regardless of drift)
default (drift_check.in_sync)     -> outcome = 'skipped_in_sync'
default (drift_check NOT in_sync) -> outcome = 'refreshed'

Fail-closed on empty p_actor (check_violation). Delegates the real work to the 022 functions — no new refresh logic.

4. Status view shape

SELECT last_actor, last_outcome, last_started_at, last_finished_at,
       last_upserted_count, last_deleted_count, last_table_count,
       seconds_since_last_refresh, current_drift, current_in_sync,
       current_view_count, current_table_count, cache_healthy
  FROM v_iu_three_axis_envelope_refresh_status;

cache_healthy = (last_run exists AND current_drift.in_sync = true). current_drift is the JSONB returned by fn_iu_three_axis_envelope_drift_check.

5. Sandbox/220 — BEGIN/ROLLBACK probe

Eight probes, all PASS on the live schema (no commit):

#	Probe	Outcome
220.1	empty actor → check_violation	PASS
220.2	dry-run reports upserted=163 deleted=0	PASS
220.3	default call on clean cache → skipped_in_sync	PASS
220.4	forced call on clean cache → refreshed	PASS
220.5	seed synthetic drift → default call → refreshed (repair)	PASS
220.6	audit log carries 4 rows for sandbox actor (1 dry, 1 skip, 1 force, 1 repair)	PASS
220.7	status view reports cache_healthy=true after repair	PASS
220.8	outcome CHECK rejects 'BOGUS_OUTCOME'	PASS

6. Runtime/330 — live smoke (no IU mutation)

330.1 dry-run:                outcome=dry_run         upserted=163  deleted=0
330.2 live (drift-gated):     outcome=skipped_in_sync upserted=0    deleted=0
330.3 status snapshot:        cache_healthy=t         current_in_sync=t
330.4 audit log tail:         id=6 (dry_run), id=7 (skipped_in_sync)
330.5 fail-closed assertion:  PASS

The skipped_in_sync outcome on the live call proves the 2400x bootstrap left the cache coherent and no refresh was needed.

7. Why no trigger in 023

The mission allows triggers, but installing one on information_unit or iu_metadata_tag touches the hottest write paths in IU Core. Doing it inside a single 45–60m macro alongside the wrapper + audit log + status view is too much surface for one block. Migration 023 ships the gate row + wrapper; a future macro can wire the trigger to call fn_iu_three_axis_envelope_refresh_if_stale and flip the gate true. This keeps the 023 change reversible by a single rollback file with zero trigger detachment.

8. Rollback

sql/iu-core/rollback/023_three_axis_envelope_auto_refresh_hook.rollback.sql

Drops in safe order: view → function → config row → table. Does not touch any 022 object. Pre-condition: gate already false (which it is by default).

9. Reversibility proof

• rollback/022 still works against the new state (023 does not modify any 022 object).
• runtime/rollback/330 deletes only the smoke-actor rows from the audit log — does not touch any IU.
• Sandbox/220 wraps every probe in BEGIN ... ROLLBACK; no durable side effect.