P3D — Birth System B3-A Trigger Installation — Agent Prompt (DRAFT rev3)

Date: 2026-05-12 Rev: 3 (focused trigger-only, no gate, no PL/pgSQL body) Self-contained. Scope: install birth triggers ONLY.

Effort: medium.

---

Mission

Install birth INSERT triggers for approved BIRTH_REQUIRED ∩ species-mapped collections. Nothing else — no gate function, no schema changes, no new functions.

---

Scope constants

trigger_function_name = fn_birth_registry_auto (verify exists + OID, do NOT modify)
trigger_naming_convention = trg_birth_<table_name>
trigger_timing = AFTER INSERT FOR EACH ROW
approved_policy_artifact = knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-coverage-classification-report.md
approved_policy_revision = 1 (verify before use)
db_schema = DISCOVER

---

Hard boundaries

DDL allowed: CREATE TRIGGER only.
No CREATE/ALTER FUNCTION. No ALTER TABLE. No DROP.
No CREATE OR REPLACE (anything).
No business data mutation.
No gate function creation (that's B3-F).
No TAC/IU/Nuxt changes. No nested dispatch.

---

Phase 0 — Environment + DB + schema discovery (self-contained inline)

---

Phase 1 — Table-family + concept resolution

1A. Table families: collection_registry, species_collection_map, birth_registry + information_schema catalogs. Existence verified.

1B. Concept registry

Resolve from live schema (semantic hints, not assertions):

concept_id	table_family	semantic_hint	on_unresolved
collection_key	collection_registry	Collection identifier	ABORT
collection_governance_role	collection_registry	Governance role	ABORT
species_map_collection_key	species_collection_map	Collection in mapping	ABORT
trigger_function_oid	pg_proc	OID of fn_birth_registry_auto	ABORT

1C. fn_birth_registry_auto verification

-- PATTERN ONLY
SELECT oid, proname FROM pg_proc WHERE proname = 'fn_birth_registry_auto';

Capture OID. If absent → ABORT.

---

Phase 2 — Policy matching (3-set intersection)

2A. Load approved_target_set

Read classification report from KB. Verify revision matches approved_policy_revision. Extract BIRTH_REQUIRED list.

2B. Derive live_birth_required_set

From governance_role + schema shape (same weighted classification logic). PATTERN ONLY SQL, compiled after resolution.

2C. Derive live_species_mapped_set

-- PATTERN ONLY
SELECT DISTINCT <species_map_collection_key> FROM <species_collection_map>;

2D. Compute sets

trigger_target_set = approved_target_set ∩ live_birth_required_set ∩ live_species_mapped_set
species_queue_set = (approved_target_set ∩ live_birth_required_set) − live_species_mapped_set
policy_diff = approved_target_set ⊕ live_birth_required_set

IF policy_diff non-empty → STOP. Report POLICY_MISMATCH with exact diff. Return to GPT.

---

Phase 3 — Pre-install audit

For each collection in trigger_target_set, check existing trigger state:

-- PATTERN ONLY
SELECT tgname, tgfoid FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = <resolved_schema>
  AND c.relname = '<collection>'
  AND t.tgtype & 4 = 4  -- AFTER
  AND t.tgtype & 8 = 8; -- ROW-level (bit check for INSERT may vary — Agent must verify)

Classify per collection:

NO existing birth trigger → INSTALL
Existing trigger with tgfoid = fn_birth_registry_auto OID → ALREADY_EXISTS_OK
Existing trigger with tgfoid ≠ fn_birth_registry_auto OID → BLOCKED_EXISTING_CONFLICT (do not overwrite)

If any BLOCKED_EXISTING_CONFLICT → report, exclude from install, continue others.

---

Phase 4 — Execution log + compiled SQL preview

Log all: sets, diffs, per-collection audit, compiled CREATE TRIGGER statements. compiled_sql_logged=true.

---

Phase 5 — Installation transaction

BEGIN;

-- Advisory lock
SELECT pg_try_advisory_xact_lock(hashtext('p3d_birth_b3a_trigger_installation'));

-- For each INSTALL collection:
CREATE TRIGGER trg_birth_<collection>
  AFTER INSERT ON <resolved_schema>.<collection>
  FOR EACH ROW EXECUTE FUNCTION fn_birth_registry_auto();

-- Verify each installed: check pg_trigger for correct table + function OID binding
-- If any verification fails → ROLLBACK

-- Species queue: for each collection in species_queue_set:
--   INSERT system_issues row RETURNING <pk>
--   Capture exact PK for rollback

-- Persist: installed trigger names + species queue PKs to log

COMMIT;

---

Phase 6 — Post-commit verification + rollback keys

Verify total birth trigger count. Upload rollback keys to KB.

Rollback:

DROP TRIGGER <exact_trigger_name> ON <exact_table>;  -- for each installed
DELETE FROM system_issues WHERE <pk> IN (<captured_species_queue_pks>);  -- exact IDs

---

Report + final response

b3a_status=PASS|PARTIAL|BLOCKED|LOCK_BUSY|POLICY_MISMATCH
trigger_target_count=<N>
triggers_installed=<N>
triggers_already_existed=<N>
triggers_blocked_conflict=<N>
triggers_skipped_no_mapping=<N>
total_birth_triggers_after=<N>
species_queue_issues_created=<N>
species_queue_captured_pks=<list>
policy_mismatch_detected=true|false
compiled_sql_logged=true|false
no_business_data_mutation=true
no_function_created=true

---

B3-A Trigger Install | rev3 | Focused, no gate, no PL/pgSQL body | 2026-05-12