P3D — Birth System B3-A Trigger Readiness Report

Date: 2026-05-12 Mode: READ-ONLY (no DB write, no DDL, no trigger creation, no function creation, no policy update, no migration) Source: live PG (directus@postgres, schema public) on VPS 38.242.240.89

---

Phase 0 — Live resolution

Item	Resolved
schema	public
collection_registry	EXISTS (166 rows)
coverage policy columns (6)	PRESENT
species_collection_map	EXISTS
information_schema.tables	system catalog
information_schema.triggers / pg_trigger	system catalog
public.fn_birth_registry_auto	RESOLVED — oid = 39232

fn_birth_registry_auto_resolved = true trigger_validation_by_function_oid = true (validation is pg_trigger.tgfoid = 39232, not by trigger name)

Side observation: a sibling function fn_birth_registry_auto_id (oid 66750) exists and is bound to 3 collections (see §Policy mismatch). It is not the contract function — collections wired to it count as missing valid trigger.

---

Phase 1 — Required set

SELECT collection_name FROM public.collection_registry
WHERE coverage_status='BIRTH_REQUIRED' AND coverage_scope_status='IN_SCOPE';

• birth_required_total = 73
• birth_required_in_scope = 73 (all BIRTH_REQUIRED rows also IN_SCOPE — no BIRTH_REQUIRED outside scope)
• policy_unknown_count = 0 (no NULL coverage_status remains)

---

Phase 2 — Valid trigger detection (function-OID-based)

SELECT DISTINCT c.relname
FROM pg_trigger t
JOIN pg_class c ON c.oid=t.tgrelid
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public'
  AND NOT t.tgisinternal
  AND t.tgfoid = 39232           -- fn_birth_registry_auto
  AND (t.tgtype & 4) = 4         -- INSERT
  AND (t.tgtype & 1) = 1;        -- ROW-level

• already_wired_count = 60 distinct BIRTH_REQUIRED+IN_SCOPE collections.
• 13 of these collections carry two valid triggers (both trg_birth_<name> and birth_trigger_<name> bound to the same function) — duplicate-trigger cleanup is out of B3-A scope but flagged below.
• missing_trigger_count = 73 − 60 = 13

---

Phase 3 — Cross-checks (species mapping, physical table)

-- species_collection_map cover among required
SELECT count(*) FROM required r LEFT JOIN spm USING(collection_name) WHERE spm IS NULL;
-- physical base table among required
SELECT count(*) FROM required r LEFT JOIN phys USING(collection_name) WHERE phys IS NULL;

• missing_species_mapping_count = 8
• missing_physical_table_count = 0

---

Phase 4 — Trigger candidate set

trigger_candidate_set =
  BIRTH_REQUIRED + IN_SCOPE
  ∩ has physical table
  ∩ has species_collection_map row
  ∩ NOT in valid_trigger set

trigger_candidate_count = 5

collection_name	governance note
birth_registry	The registry itself; has 2 non-contract triggers (trg_birth_auto_certify → fn_birth_auto_certify, trg_birth_change_flag_matrix → fn_birth_change_flag_matrix); none on the contract function. B3-A wiring will coexist with auto-certify / change-flag-matrix.
governance_relations	Wired to variant fn_birth_registry_auto_id (oid 66750), not the contract function — counted as missing. Pre-B3-A action: GPT/governance decision on whether to migrate to contract function or keep _id variant.
law_dot_enforcement	Same as above.
law_jurisdiction	Same as above.
normative_relations	No birth trigger at all (no trg_birth_, no birth_trigger_). Clean install.

---

Phase 5 — Missing species mapping (blockers excluded from candidate set)

The following 8 BIRTH_REQUIRED+IN_SCOPE collections are blocked from B3-A wiring until species_collection_map is added:

apr_action_types
apr_approvals
apr_request_types
binding_registry
dot_domain_rules
field_type_equivalences
nrm_approval_rules
nrm_doc_type_config

Recommendation: a separate species-mapping seed step must precede or accompany B3-A for these 8.

---

Phase 6 — Missing physical table

None. Every BIRTH_REQUIRED+IN_SCOPE row has a backing BASE TABLE. (Orphan registry rows were classified DEFERRED with coverage_scope_status=ORPHAN_REGISTRY in B3-P Population and are excluded from BIRTH_REQUIRED.)

---

Phase 7 — Policy mismatch / unknowns

1. Variant-function bindings (3) — governance_relations, law_dot_enforcement, law_jurisdiction use fn_birth_registry_auto_id (oid 66750). Per the strict contract (function-OID match against fn_birth_registry_auto), these are NOT valid wirings and were placed in the candidate set. Governance decision needed: migrate to contract function OR widen the contract to accept the _id variant.
2. Duplicate wirings (13 collections) — both naming conventions (trg_birth_<name> and birth_trigger_<name>) coexist and both call fn_birth_registry_auto. Functionally idempotent but represents tech debt; B3-A should not add a third copy. The B3-A implementation prompt should detect existing valid wiring and skip — already-wired-skip semantics confirmed.
3. birth_registry itself targeted (1) — birth_registry is in BIRTH_REQUIRED+IN_SCOPE but has no contract trigger. Adding fn_birth_registry_auto to birth_registry risks recursive INSERT loops if the function inserts into birth_registry itself. GPT must explicitly approve/exempt before B3-A wiring.
4. No UNCLASSIFIED_NEW rows — every collection has coverage policy.

---

Phase 8 — Ready for B3-A implementation prompt?

b3a_implementation_prompt_can_be_prepared = true (with explicit notes)

Conditions:

• 5 candidate collections identified with deterministic evidence.
• Function OID locked (39232).
• 8 species-mapping gaps documented as pre-B3-A blockers.
• 3 variant-function rebindings flagged for governance decision.
• 1 recursive-risk candidate (birth_registry) flagged for explicit exemption decision.

B3-A prompt must encode:

• function-OID-based valid-trigger detection (NOT name-based),
• skip-if-already-wired,
• recursive-insert safety check for birth_registry,
• pre-flight: species mapping must exist (refuse to wire collections without species_collection_map row),
• compiled SQL preview + advisory lock + single transaction.

---

Final response

b3a_readiness_status=PARTIAL
birth_required_total=73
birth_required_in_scope=73
already_wired_count=60
missing_trigger_count=13
missing_species_mapping_count=8
missing_physical_table_count=0
trigger_candidate_count=5
policy_unknown_count=0
fn_birth_registry_auto_resolved=true
trigger_validation_by_function_oid=true
report_path=knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3a-trigger-readiness-report.md
no_mutation_performed=true
next_recommended_action=GPT_REVIEW_READINESS_THEN_B3A_IMPLEMENTATION_PROMPT

Status is PARTIAL (not PASS) because:

• 8 collections in BIRTH_REQUIRED+IN_SCOPE lack species_collection_map (blocker for B3-A),
• 3 collections are bound to the variant function (governance decision required),
• 1 collection (birth_registry) needs explicit recursive-insert exemption.

The 5 clean candidates can proceed once items 2 + 3 are decided; the 8 mapping gaps require a separate pre-B3-A step.

---

B3-A Readiness | read-only | 2026-05-12