P3D — Birth System B3-A3 Trigger Install — Agent Prompt (DRAFT)

Date: 2026-05-12 Self-contained: Agent reads ONLY this document. Scope: Install birth INSERT triggers for live clean candidates.

Mission

Install trg_birth_<collection> AFTER INSERT triggers binding to fn_birth_registry_auto for all live clean candidates. Catalog verification only — no test INSERTs.

---

Scope constants

trigger_function_name = fn_birth_registry_auto (resolve OID live, do NOT modify)
scoped_sibling_function = fn_birth_registry_auto_id (resolve OID live — for exclusion check only)
scoped_sibling_collections = governance_relations, law_dot_enforcement, law_jurisdiction (accepted-wired, SKIP)
exempt_collection = birth_registry (EXEMPT per B3-A2)
trigger_naming = trg_birth_<collection_name>
trigger_timing = AFTER INSERT FOR EACH ROW
db_schema = DISCOVER

---

Hard boundaries

DDL: CREATE TRIGGER only.
No CREATE/ALTER/DROP FUNCTION. No ALTER TABLE. No DROP TRIGGER.
No INSERT/UPDATE/DELETE on any table.
No function modification. No policy update. No species mutation.
No duplicate-trigger cleanup (B3-A-DUP separate).
No Phase 5C2. No UI change.

---

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

Discover DB container, credentials, schema from docker/compose/env.

---

Phase 1 — Function OID resolution

-- PATTERN ONLY
SELECT proname, oid FROM pg_proc
WHERE proname IN ('fn_birth_registry_auto', 'fn_birth_registry_auto_id')
  AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = <resolved_schema>);

Store: contract_oid (fn_birth_registry_auto), sibling_oid (fn_birth_registry_auto_id).

If contract function absent → ABORT.

---

Phase 2 — Table-family + concept resolution

Verify existence: collection_registry, species_collection_map. Resolve concepts: collection_key, coverage_status, coverage_scope_status from collection_registry. Resolve species_map_collection_key from species_collection_map.

---

Phase 3 — Compute live clean candidate set

-- PATTERN ONLY — compile after resolution
WITH governed AS (
  SELECT <collection_key> AS cname
  FROM <collection_registry>
  WHERE <coverage_status> = 'BIRTH_REQUIRED'
    AND <coverage_scope_status> = 'IN_SCOPE'
),
has_table AS (
  SELECT table_name AS cname
  FROM information_schema.tables
  WHERE table_schema = <resolved_schema> AND table_type = 'BASE TABLE'
),
has_species AS (
  SELECT DISTINCT <species_map_collection_key> AS cname
  FROM <species_collection_map>
),
already_wired_contract AS (
  SELECT DISTINCT c.relname AS cname
  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 t.tgenabled != 'D'
    AND t.tgfoid = <contract_oid>
),
already_wired_scoped_sibling AS (
  SELECT DISTINCT c.relname AS cname
  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 t.tgenabled != 'D'
    AND t.tgfoid = <sibling_oid>
    AND c.relname IN ('governance_relations','law_dot_enforcement','law_jurisdiction')
),
out_of_scope_sibling AS (
  SELECT DISTINCT c.relname AS cname
  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 t.tgenabled != 'D'
    AND t.tgfoid = <sibling_oid>
    AND c.relname NOT IN ('governance_relations','law_dot_enforcement','law_jurisdiction')
),
name_collision AS (
  SELECT DISTINCT c.relname AS cname
  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 t.tgname = 'trg_birth_' || c.relname
)
SELECT g.cname
FROM governed g
JOIN has_table t ON t.cname = g.cname
JOIN has_species s ON s.cname = g.cname
WHERE g.cname NOT IN (SELECT cname FROM already_wired_contract)
  AND g.cname NOT IN (SELECT cname FROM already_wired_scoped_sibling)
  AND g.cname NOT IN (SELECT cname FROM name_collision)
  AND g.cname != 'birth_registry';

-- Required safety check before install:
-- SELECT count(*) FROM out_of_scope_sibling;
-- If count > 0, STOP as BLOCKED_OUT_OF_SCOPE_SIBLING and report the table list.

Store as live_clean_candidates. Report count + list.

---

Phase 4 — Execution log + compiled SQL preview

RUN_STARTED_AT=$(date -u +"%Y-%m-%dT%H:%M:%SZ")

For each candidate, compile SQL using safe identifier quoting (format('%I', ...) or equivalent). Do not concatenate unquoted identifiers.

Pattern:

CREATE TRIGGER trg_birth_<candidate>
  AFTER INSERT ON <resolved_schema>.<candidate>
  FOR EACH ROW EXECUTE FUNCTION fn_birth_registry_auto();

The emitted SQL must quote schema, table, and trigger identifiers safely.

Log ALL compiled CREATE TRIGGER + rollback DROP TRIGGER statements. compiled_sql_logged=true.

---

Phase 5 — Installation transaction

BEGIN;

-- Advisory lock
SELECT pg_try_advisory_xact_lock(hashtext('p3d_birth_b3a3_trigger_install'));
-- false → ROLLBACK, LOCK_BUSY

-- For each candidate: CREATE TRIGGER
-- Capture: installed_triggers list (trigger_name, collection, function_oid)

-- In-transaction verification per trigger:
SELECT t.tgname, c.relname, t.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 = '<candidate>'
  AND t.tgname = 'trg_birth_<candidate>'
  AND t.tgfoid = <contract_oid>;
-- Must return exactly 1 row. If 0 → ROLLBACK.

-- Confirm: birth_registry NOT in installed list
-- Confirm: no duplicate (collection not in already_wired_contract or already_wired_scoped_sibling)
-- Confirm: out_of_scope_sibling count = 0

-- ALL verified → COMMIT
COMMIT;

---

Phase 6 — Post-commit verification

-- Total birth triggers (contract + sibling)
SELECT count(DISTINCT c.relname)
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 t.tgenabled != 'D'
  AND t.tgfoid IN (<contract_oid>, <sibling_oid>);

---

Phase 7 — KB report + rollback keys

Rollback (exact trigger names only):

-- For each installed trigger:
DROP TRIGGER IF EXISTS trg_birth_<candidate> ON <resolved_schema>.<candidate>;

No existing triggers touched. No function dropped.

---

Report path

knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3a3-trigger-install-execution-report.md

Final response

b3a3_status=PASS|PARTIAL|BLOCKED|LOCK_BUSY
live_candidate_count=<N>
triggers_installed=<N>
triggers_skipped_already_wired=<N>
triggers_skipped_name_collision=<N>
out_of_scope_sibling_count=0
birth_registry_excluded=true
no_birth_registry_trigger_created=true
scoped_sibling_preserved=true
duplicate_cleanup_performed=false
function_oid_used=<oid>
compiled_sql_logged=true|false
rollback_sql_logged=true|false
post_commit_total_birth_triggers=<N>
no_insert_performed=true
no_function_mutation=true
no_policy_mutation=true
no_species_mutation=true
report_path=<above>
log_path=<path>
next_recommended_action=GPT_REVIEW_B3A3_THEN_B3F_GATE_OR_B4_BACKFILL

---

B3-A3 Trigger Install | DRAFT | Self-contained, catalog verify only | 2026-05-12