02 — Live Birth Pipeline Survey

Channel. directus PG database, queried via VPS MCP query_pg (read-only role, AST-validated, statement_timeout 5s, hard LIMIT 500). 2026-05-25.

Birth-related functions in public schema (19 total)

#	Function	Role
1	fn_birth_auto_certify	BEFORE UPDATE on birth_registry — auto-flips certified once inspect stamps complete
2	fn_birth_change_flag_matrix	AFTER INSERT/UPDATE/DELETE on birth_registry (statement-level)
3	fn_birth_gate	BEFORE INSERT on collection_registry (and other catalogs) — checks tac_birth_gate_config against profile
4	fn_birth_onboarding_full_scan	full-scan reporter (manual/scheduled)
5	fn_birth_onboarding_full_scan_hc	healthcheck variant
6	fn_birth_registry_auto	the canonical auto-birth function — fires from AFTER INSERT triggers on catalog tables
7	fn_birth_registry_auto_id	id-keyed variant
8	fn_description_birth_guard	BEFORE INSERT/UPDATE on description — validates description policy
9	fn_iu_birth_gate_layer1	IU-specific birth gate L1 (Pack 1)
10	fn_iu_birth_gate_layer2	IU-specific birth gate L2 (Pack 1)
11	fn_pre_birth_check	pre-birth validation (callable from DOT tools)
12	fn_refresh_orphan_species	counts orphan species
13	fn_refresh_species_count	refresh species counter
14	fn_refresh_species_per_level	refresh per-level species counter
15	fn_rule_birth_violations	rule-engine birth violation reporter
16	fn_tac_birth_gate_lu	TAC birth gate (LU layer)
17	fn_tac_birth_gate_uv	TAC birth gate (UV layer)
18	gen_code_collection_registry	auto-codes COL-NNN before insert
19	trg_fn_refresh_orphan_species	trigger wrapper for #12

Also referenced but in other schemas / not strictly birth: fn_validate_dot_origin, fn_collection_onboarding_soft_gate, fn_auto_label_provenance, fn_auto_label_assignment, fn_refresh_collection_count, trg_fn_refresh_orphan_col.

Triggers on the 4 governance tables (28 total, 21 enabled, 4 disabled, 3 RI constraints)

collection_registry (16 triggers; 5 RI constraint triggers + 11 functional)

Trigger	Stage	Function	Enabled
trg_auto_code_collection_registry	BEFORE INSERT	gen_code_collection_registry	O
trg_before_birth_gate_collection_registry	BEFORE INSERT	fn_birth_gate	O
trg_validate_dot_origin_collection_registry	BEFORE INSERT/UPDATE OF _dot_origin	fn_validate_dot_origin	O
trg_desc_guard_collection_registry	BEFORE INSERT/UPDATE OF description	fn_description_birth_guard	O
birth_trigger_collection_registry	AFTER INSERT	fn_birth_registry_auto('code')	O
trg_birth_collection_registry	AFTER INSERT	fn_birth_registry_auto('code')	O — duplicate of birth_trigger_collection_registry, idempotent via ON CONFLICT DO NOTHING
trg_desc_provenance_collection_registry	AFTER INSERT	fn_auto_label_provenance	O
trg_label_assign_collection_registry	AFTER INSERT/UPDATE OF name,description	fn_auto_label_assignment	O
trg_collection_onboarding_soft_gate	AFTER INSERT/UPDATE OF coverage_* fields	fn_collection_onboarding_soft_gate	O
trg_refresh_collection_count	AFTER INSERT/DELETE (statement)	fn_refresh_collection_count	O
trg_refresh_orphan_col	AFTER INSERT/DELETE (statement)	trg_fn_refresh_orphan_col	O
trg_count_collection_registry	AFTER INSERT/DELETE/UPDATE/TRUNCATE (statement)	refresh_registry_count	D (disabled)
RI_ConstraintTrigger_a_322529/30	RI FKey on collection_registry_vector_policy	—	O
RI_ConstraintTrigger_c_58144/45	RI FKey on collection_groups	—	O

entity_species (7 triggers)

Trigger	Stage	Function	Enabled
trg_desc_guard_entity_species	BEFORE INSERT/UPDATE OF description	fn_description_birth_guard	O
birth_trigger_entity_species	AFTER INSERT	fn_birth_registry_auto('code')	O
trg_birth_entity_species	AFTER INSERT	fn_birth_registry_auto('code')	O — duplicate, ON CONFLICT-safe
trg_desc_provenance_entity_species	AFTER INSERT	fn_auto_label_provenance	O
trg_refresh_orphan_species	AFTER INSERT/DELETE/UPDATE	trg_fn_refresh_orphan_species	O
trg_refresh_species_count	AFTER INSERT/DELETE (statement)	fn_refresh_species_count	O
trg_count_entity_species	AFTER INSERT/DELETE (statement)	update_record_count	D

Note: entity_species has no fn_validate_dot_origin trigger. That is why SPE-NVS row in birth_registry carries the clean origin D36-Macro-A while COL-IUS-001/002 rows carry SUSPECT:D36-Macro-A. The asymmetry is real and pre-existing — not specific to NVSZ.

species_collection_map (2 triggers)

Trigger	Stage	Function	Enabled
birth_trigger_species_collection_map	AFTER INSERT	fn_birth_registry_auto() (no code arg → synthetic key)	O
trg_count_species_collection_map	AFTER INSERT/DELETE (statement)	update_record_count	D

birth_registry (3 triggers)

Trigger	Stage	Function	Enabled
trg_birth_auto_certify	BEFORE UPDATE	fn_birth_auto_certify	O
trg_birth_change_flag_matrix	AFTER INSERT/DELETE/UPDATE (statement)	fn_birth_change_flag_matrix	O
trg_count_birth_registry	AFTER INSERT/DELETE (statement)	update_record_count	D

Auto-birth function body (live retrieval)

CREATE OR REPLACE FUNCTION public.fn_birth_registry_auto()
RETURNS trigger
LANGUAGE plpgsql AS $function$
DECLARE
  v_entity_code text; v_code_field text; v_species_code text;
  v_comp_level text; v_gov_role text; v_dot_origin text; v_row_json jsonb;
BEGIN
  v_row_json := to_jsonb(NEW);
  IF TG_NARGS > 0 THEN
    v_code_field := TG_ARGV[0];
    v_entity_code := v_row_json->>v_code_field;
  END IF;
  IF v_entity_code IS NULL OR v_entity_code = '' THEN
    v_entity_code := TG_TABLE_NAME || '::' || (v_row_json->>'id')::text;
  END IF;
  IF EXISTS (SELECT 1 FROM birth_registry
              WHERE entity_code = v_entity_code
                AND collection_name = TG_TABLE_NAME) THEN
    RETURN NEW;
  END IF;
  v_dot_origin := v_row_json->>'_dot_origin';
  IF v_dot_origin IS NULL OR v_dot_origin = '' THEN
    v_dot_origin := 'PG:trg_birth_' || TG_TABLE_NAME;
  END IF;
  SELECT scm.species_code, es.composition_level
    INTO v_species_code, v_comp_level
    FROM species_collection_map scm
    LEFT JOIN entity_species es ON es.species_code = scm.species_code
    WHERE scm.collection_name = TG_TABLE_NAME
      AND scm.is_primary = true LIMIT 1;
  SELECT governance_role INTO v_gov_role
    FROM collection_registry
    WHERE collection_name = TG_TABLE_NAME LIMIT 1;
  INSERT INTO birth_registry (
    entity_code, collection_name, species_code, composition_level,
    dot_origin, born_at, governance_role, certified
  ) VALUES (
    v_entity_code, TG_TABLE_NAME, v_species_code, v_comp_level,
    v_dot_origin, now(), COALESCE(v_gov_role, 'excluded'), false
  ) ON CONFLICT (entity_code) DO NOTHING;
  RETURN NEW;
END;
$function$;

Properties:

• Idempotent (ON CONFLICT (entity_code) DO NOTHING).
• Reads species_code from species_collection_map (so NVSZ rows correctly resolved to SPE-NVS).
• Reads governance_role from collection_registry.
• Default _dot_origin fallback is PG:trg_birth_<TG_TABLE_NAME> if the row didn't supply one.
• certified=false on every birth — certification is a separate gated step (trg_birth_auto_certify).

This is the canonical QT-001-equivalent auto-backfill mechanism for catalog INSERTs.

DOT origin validator (live retrieval)

CREATE OR REPLACE FUNCTION public.fn_validate_dot_origin()
RETURNS trigger LANGUAGE plpgsql AS $function$
DECLARE
  dot_code text;
  -- TODO TD-411: move to dot_origin_whitelist table for meta-driven whitelist
  _whitelist constant text[] := ARRAY['DIRECTUS', 'LEGACY', 'MIGRATION'];
BEGIN
  IF NEW._dot_origin IS NULL THEN RETURN NEW; END IF;
  IF NEW._dot_origin = ANY(_whitelist) THEN RETURN NEW; END IF;
  IF NEW._dot_origin LIKE 'SUSPECT:%' THEN RETURN NEW; END IF;
  dot_code := split_part(NEW._dot_origin, '|', 1);
  IF NOT EXISTS (SELECT 1 FROM dot_tools WHERE code = dot_code) THEN
    NEW._dot_origin := 'SUSPECT:' || NEW._dot_origin;
  END IF;
  RETURN NEW;
END;
$function$;

The function:

• Never blocks the INSERT.
• Whitelists three meta-origins (DIRECTUS, LEGACY, MIGRATION).
• Otherwise checks dot_tools for the leading code; if missing → prefixes SUSPECT:.
• Has explicit TODO TD-411 to move the whitelist to a dot_origin_whitelist table.

dot_tools has 309 rows, none named D36-Macro-A — hence the SUSPECT prefix on COL-IUS-001/002.

Birth Gate config (tac_birth_gate_config) — 14 rows

11 long-standing gates (BG-LU-02..06, BG-UV-01..06) at modes block/warn, all enabled, dated 2026-04-28. Three new D36 Macro A rows dated 2026-05-25:

checker_id	mode	enabled	rationale
iu_core.no_vector_staging_excluded	block	true	D36 Macro A — defense-in-depth panic gate. Default ON (block when disabled).
iu_core.staging_cleanup_enabled	block	false	D36 Macro A — cleanup gate. Default OFF (block). Carry-forward.
iu_core.staging_writes_enabled	block	false	D36 Macro A — master write gate for iu_staging_*. Default OFF (block).

The two enabled=false gates are intentional: the staging write/cleanup paths are not yet operational; the gates are pre-wired and will be flipped on in a later macro.

DOT catalog (dot_iu_command_catalog) — schema sanity

Columns: command_name, category, mutating, reversible, target_functions, registered_at. No NVSZ-named commands present (search %nvs%, %staging%, %birth% returned 0 rows). NVSZ Macro A registered 4 DOT command rows per the macro report; they likely live under a different command_name pattern. This audit did not deep-dive DOT catalog since DOTs are not the birth-pipeline question.

Schema home for the staging tables

iu_staging_record and iu_staging_payload live in the iu_core schema, not public. Their pg_trigger results returned 0 triggers for both — there is no trigger on the staging tables themselves (no birth trigger on staging data rows; intentional, per Case 3).

Data inside the staging tables on 2026-05-25:

Table	Row count	Notes
iu_core.iu_staging_record	3	proof rows from Macro A: lifecycle consumed / pending / rejected, owner_actor d36-macroA-proof
iu_core.iu_staging_payload	4	child rows tied to staging records

Summary

The birth pipeline IS present, wired, and active on the three governance tables. Auto-birth from catalog INSERT is the canonical "Case 3" path. The function chain is idempotent. The only governance signal raised by Macro A is the soft SUSPECT: prefix on _dot_origin for COL-IUS-001/002 because D36-Macro-A is not a dot_tools row.