D36 NVSZ Macro A — 04 Migration Substrate

Migration file: /tmp/d36_macroA_migration.sql (authored locally, scp'd to contabo:/tmp/, docker cp into postgres:/tmp/, applied via psql -U workflow_admin -d directus -v ON_ERROR_STOP=1 -f).

Apply mode: single TX (BEGIN/COMMIT), atomic. Re-apply attempts 1–4 rolled back cleanly until live-vocab constraints satisfied (see 01-birth-execution-plan §3).

Sections applied (16)

#	Section	Result
0	CREATE SCHEMA iu_core AUTHORIZATION workflow_admin + GRANT USAGE to directus + readonly	CREATE SCHEMA
1	iu_core.iu_staging_record (24 cols, 5 CHECK constraints, 4 indexes, GRANTs)	CREATE TABLE
2	iu_core.iu_staging_payload (12 cols, 4 CHECK constraints incl 10 MiB cap, 2 indexes, GRANTs)	CREATE TABLE
3	public.collection_registry_vector_policy (Q5 sidecar)	CREATE TABLE
4	public.v_collection_vector_eligibility (Q5 read-side view)	CREATE VIEW
5	iu_core.v_iu_staging_record + iu_core.v_iu_staging_payload_observability	CREATE VIEW x2
6	3 gates into tac_birth_gate_config (mode='block')	INSERT 0 3
7	2 retention policies into iu_core_retention_policy (keep_days=90)	INSERT 0 2
8	5 event types into event_type_registry (event_domain='staging')	INSERT 0 5
9	1 species SPE-NVS into entity_species	INSERT 0 1
10	2 collections COL-IUS-001/002 into collection_registry	INSERT 0 2 (+ Birth Gate WARNING on code format)
11	2 rows into species_collection_map	INSERT 0 2
12	2 rows into collection_registry_vector_policy (vector_eligible=false)	INSERT 0 2
13	3 birth_registry rows via direct DML (ON CONFLICT skipped; +44 total via trigger fan-out)	INSERT 0 0 explicit + auto-trigger
14	4 DOTs into dot_iu_command_catalog (category='lifecycle')	INSERT 0 4
15	4 functions fn_iu_staging_{create,approve,consume,reject} + 1 healthcheck fn (all SECURITY DEFINER, REVOKE FROM PUBLIC, GRANT EXECUTE TO directus)	CREATE FUNCTION x5

Key DDL highlights

iu_staging_record CHECK constraints

CHECK (vector_excluded = true)                     -- Rule N1
CHECK (staging_kind IN (8 values))                 -- Q2/Q3 vocab
CHECK (lifecycle_status IN (6 values))             -- pending/approved/consumed/rejected/expired/cleaned
CHECK (source_kind IN ('agent','user','system','import'))
CHECK (approved consistency)                       -- if approved → approved_at + approved_by set
CHECK (consumed consistency)                       -- if consumed → consumed_at + consumed_by_run_id set
CHECK (expires_at > created_at)

iu_staging_payload CHECK constraints

CHECK (payload_kind IN ('json','text','blob_ref'))
CHECK (exactly one of payload_json/payload_text/blob_ref set, matching kind)
CHECK (byte_len <= 10485760)                       -- Q10: 10 MiB cap
UNIQUE (staging_record_id, part_index)

Sidecar policy CHECK

CHECK (NOT semantic_search_eligible OR vector_eligible)   -- semantic implies vector

Function semantics

All write functions check iu_core.staging_writes_enabled gate. Gate is false by default — so direct callers get {"status":"refused","reason":"gate_closed:..."}. Proof rows opened/closed the gate inside a single TX (see report 06).

fn_iu_staging_create idempotency: md5(source_ref || '|' || staging_kind || '|' || suffix). UNIQUE constraint on iu_staging_record.idempotency_key. Re-call returns {"status":"idempotent_replay","staging_record_id":"..."} with original UUID. Verified live (proof1_replay).

Lifecycle transitions:

• pending → approved via fn_iu_staging_approve (gate-checked, lifecycle-checked)
• approved → consumed via fn_iu_staging_consume
• pending → rejected via fn_iu_staging_reject
• Invalid transitions return {"status":"invalid_transition","from":"..."} — verified (proof1_invalid_transition: tried to approve already-consumed → returned invalid_transition correctly)

Healthcheck function

iu_core.fn_iu_staging_healthcheck() is STABLE, callable by directus + context_pack_readonly. Returns 10 keys (V1..V8 + green + V3_total_staging_collections + V6 placeholder).

Apply timeline

Attempt	Failure	Fix
1	tac_birth_gate_config.mode='gate' invalid	Change to 'block'
2	event_type_registry ON CONFLICT (3-col) — PK is 2-col	Change ON CONFLICT to (event_domain, event_type); fix delivery_lane to 'delayed'; fix event_stream='create' to 'birth'
3	collection_registry.coverage_scope_status='productized' invalid	Change to UPPERCASE 'IN_SCOPE'; add coverage_status='BIRTH_REQUIRED'
4	collection_registry.birth_code_strategy='uuid_pk' + others	Change to 'column', 'migration_seed', 'required_detailed', 'GRP-GOVERNANCE'
5	dot_iu_command_catalog.category='d36-no-vector-staging' invalid	Change to 'lifecycle'
6	success	COMMIT

Each failed attempt rolled back atomically (BEGIN…ERROR aborts the TX cleanly).