P3D — Birth System Completion Read-Only Inventory — Agent Prompt (DRAFT rev2)

Date: 2026-05-12 Rev: 2 (discovery-first, concept registry, table-family registry, PATTERN ONLY SQL) Mode: DRAFT — not approved for dispatch Self-contained: Agent reads ONLY this document.

Effort: medium-high.

---

Mission

Discover the complete current state of the birth system across all governed collections. Read-only inventory to identify gaps before any implementation.

---

Scope constants (GPT/User-declared)

pilot_publication_code = 'DIEU-35'
target_scope = birth system across all governed collections
db_schema = DISCOVER (historical expectation: public — not execution truth until resolved)

ALL other values — DB connection, paths, URLs, columns, joins, counts — MUST be discovered.

---

Hard boundaries

No DB write. No DDL. No migration. No function patch. No UI/Nuxt change. No deploy. No nested dispatch. If unknown → UNKNOWN. Do not guess.

---

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

0A. DB container discovery

docker ps --format '{{.Names}} {{.Image}} {{.Status}}' | grep -i postgres

Report: container name, image, status. If not found → DB_CONTAINER_UNKNOWN.

0B. DB credentials discovery

# Discover compose/env files
find / -maxdepth 5 \( -name 'docker-compose*' -o -name '.env' \) -type f 2>/dev/null | head -10
# Extract DB credentials
grep -h 'DB_DATABASE\|DB_USER\|DB_HOST\|DB_PORT\|POSTGRES_DB\|POSTGRES_USER' <discovered_files> 2>/dev/null

Report: db_container, db_user, db_name as RESOLVED or DB_CONNECTION_UNKNOWN. If UNKNOWN → SQL phases BLOCKED.

0C. Schema discovery

-- PATTERN ONLY — compile after DB connection resolved
SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog','information_schema','pg_toast') ORDER BY schema_name;

Report resolved schema(s). Use resolved schema in all subsequent queries (not hardcoded public).

---

Phase 1 — Table-family registry + existence verification

Declare table families as scope. Verify existence of each:

-- PATTERN ONLY — compile with resolved schema
SELECT table_name FROM information_schema.tables
WHERE table_schema = <resolved_schema> AND table_name = '<family_table>';

family_id	semantic_hint	required_for	on_absent
birth_registry	Birth records storage	Core inventory	CRITICAL — report, continue other phases
species_collection_map	Species ↔ collection mapping	Species coverage	CRITICAL
entity_species	Species definitions + composition	Species/lớp	CRITICAL
collection_registry	Governed collection list	Coverage analysis	CRITICAL
information_unit	IU target table	IU-specific checks	Report if absent
unit_version	UV target table	IU-specific checks	Report if absent
entity_dependencies	Dependency records	Hook inventory	Report ABSENT if missing
entity_labels	Label records	Hook inventory	Report ABSENT if missing
universal_edges	Edge/relation records	Hook inventory	Report ABSENT if missing
event_outbox	Event outbox records	Event hook	Report ABSENT if missing
pivot_definitions	Pivot configuration	Pivot hook	Report ABSENT if missing

Report: resolved table name + schema + existence status for each family.

---

Phase 2 — Concept registry + resolution

Introspect all existing tables from Phase 1:

-- PATTERN ONLY
SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = <resolved_schema>
  AND table_name IN (<resolved_table_names_from_phase1>)
ORDER BY table_name, ordinal_position;

Resolve each concept. Semantic hints = search guidance, NOT assertions:

2A. Birth registry concepts

concept_id	table_family	semantic_hint	required_for	on_unresolved
birth_pk	birth_registry	Primary key	Reference	Report FIELD_ABSENT
birth_collection_key	birth_registry	Collection name/identifier	Filter by collection	Report FIELD_ABSENT
birth_entity_ref	birth_registry	Entity ID reference	Match to source entity	Report FIELD_ABSENT
birth_species_identifier	birth_registry	Species code/name	Species coverage	Report FIELD_ABSENT
birth_composition_identifier	birth_registry	Composition level	Lớp coverage	Report FIELD_ABSENT
birth_created_at	birth_registry	Creation/birth timestamp	Temporal analysis	Report FIELD_ABSENT
birth_actor	birth_registry	Who/what created	Provenance	Report FIELD_ABSENT
birth_governance_state	birth_registry	Governance role/state	Governance check	Report FIELD_ABSENT
birth_lifecycle_state	birth_registry	Lifecycle/conformance status	Status check	Report FIELD_ABSENT
birth_owner_ref	birth_registry	Owner reference	Ownership	Report FIELD_ABSENT
birth_provenance_ref	birth_registry	Source/provenance reference	Trace	Report FIELD_ABSENT

2B. Species/mapping concepts

concept_id	table_family	semantic_hint	required_for	on_unresolved
species_pk	entity_species	Primary key	Reference	Report FIELD_ABSENT
species_code	entity_species	Species code identifier	Lookup	Report FIELD_ABSENT
species_composition	entity_species	Composition level value	Lớp assignment	Report FIELD_ABSENT
mapping_collection_key	species_collection_map	Collection name	Coverage	Report FIELD_ABSENT
mapping_species_ref	species_collection_map	FK to species	Lookup chain	Report FIELD_ABSENT
mapping_is_primary	species_collection_map	Primary mapping flag	Primary species	Report FIELD_ABSENT

2C. Collection registry concepts

concept_id	table_family	semantic_hint	required_for	on_unresolved
collection_key	collection_registry	Collection identifier/name	Cross-reference	Report FIELD_ABSENT
collection_governance_role	collection_registry	Governance role/level	Governed filter	Report FIELD_ABSENT
collection_migration_state	collection_registry	Migration/pilot state	State analysis	Report FIELD_ABSENT

Resolution rule: 0 candidates = FIELD_ABSENT. 1 = RESOLVED. >1 = AMBIGUOUS_FIELD.

---

Phase 3 — Birth system component inventory

ALL SQL below is PATTERN ONLY — Agent compiles executable SQL after Phase 1+2 resolution. Agent must include compiled SQL in report.

3A. birth_registry state

-- PATTERN: total + species coverage
SELECT count(*) AS total,
       count(*) FILTER (WHERE <birth_species_identifier> IS NOT NULL) AS with_species,
       count(*) FILTER (WHERE <birth_species_identifier> IS NULL) AS null_species
FROM <birth_registry>;
-- PATTERN: by collection
SELECT <birth_collection_key>, count(*) FROM <birth_registry> GROUP BY 1 ORDER BY 2 DESC;

3B. species_collection_map state

-- PATTERN: all mappings
SELECT <mapping_collection_key>, <mapping_species_ref>, <mapping_is_primary>
FROM <species_collection_map> ORDER BY <mapping_collection_key>;

3C. entity_species state

-- PATTERN: all species
SELECT <species_code>, <species_composition> FROM <entity_species> ORDER BY <species_code>;

3D. fn_birth_registry_auto

SELECT proname, pronargs FROM pg_proc WHERE proname = 'fn_birth_registry_auto';
SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'fn_birth_registry_auto';

Report bounded facts: signature, return type, write targets (which columns it INSERTs), checked fields, warning/error mode.

3E. Birth trigger inventory

-- PATTERN
SELECT trigger_name, event_object_table, action_timing, event_manipulation
FROM information_schema.triggers
WHERE trigger_schema = <resolved_schema> AND trigger_name LIKE 'trg_birth_%'
ORDER BY event_object_table;

3F. Collection registry vs birth trigger coverage

-- PATTERN: governed collections from registry
SELECT <collection_key>, <collection_governance_role> FROM <collection_registry>;
-- Cross-reference with 3E: which governed collections LACK birth trigger

---

Phase 4 — IU-specific birth state

4A. fn_iu_create

SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'fn_iu_create';

Report: parameters, return type, identity_profile keys it sets, publication_authority handling.

4B. fn_iu_create_plan

SELECT proname FROM pg_proc WHERE proname = 'fn_iu_create_plan';

4C. fn_iu_birth_gate_layer1

SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'fn_iu_birth_gate_layer1';

Report: checked keys, mode (WARNING/ERROR), vocab validation scope.

4D. Current IU/UV counts

-- PATTERN
SELECT count(*) FROM <information_unit>;
SELECT count(*) FROM <unit_version>;
SELECT count(*) FROM <birth_registry> WHERE <birth_collection_key> = 'information_unit';

---

Phase 5 — Enrichment hook inventory

For each enrichment table family (entity_dependencies, entity_labels, universal_edges, event_outbox, pivot_definitions): if table EXISTS from Phase 1:

-- PATTERN: schema + count + trigger coverage
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = <resolved_schema> AND table_name = '<resolved_table>';
SELECT count(*) FROM <resolved_table>;

For event_outbox: also check trigger coverage:

-- PATTERN
SELECT trigger_name, event_object_table FROM information_schema.triggers
WHERE trigger_schema = <resolved_schema>
  AND (trigger_name LIKE '%event%' OR trigger_name LIKE '%outbox%')
ORDER BY event_object_table;

---

Phase 6 — Metadata column coverage (deterministic sample)

From collection_registry: if total governed collections ≤ 20 → inspect ALL. If > 20 → inspect all IU/TAC families + first 10 by stable resolved <collection_key> ASC.

For each sampled collection, check:

• Has birth trigger? (from Phase 3E)
• Has species mapping? (from Phase 3B)
• Has canonical_address or equivalent? (introspect columns, look for address/canonical/slug)
• Has JSONB profile column? (introspect, look for jsonb type)
• Has FK to parent/owner? (FK introspection)

Report: coverage matrix (collection × 5 features).

---

Phase 7 — Birth metadata completeness analysis

Compare birth_registry resolved columns (Phase 2A) against design v3 §E contract:

Design item	birth_registry column	Status
Identity (ID)	<birth_entity_ref>	RESOLVED or FIELD_ABSENT
Species	<birth_species_identifier>	RESOLVED or FIELD_ABSENT
Composition	<birth_composition_identifier>	RESOLVED or FIELD_ABSENT
Timestamp	<birth_created_at>	RESOLVED or FIELD_ABSENT
Governance state	<birth_governance_state>	RESOLVED or FIELD_ABSENT
Lifecycle	<birth_lifecycle_state>	RESOLVED or FIELD_ABSENT
Owner	<birth_owner_ref>	RESOLVED or FIELD_ABSENT
Provenance	<birth_provenance_ref>	RESOLVED or FIELD_ABSENT
Actor	<birth_actor>	RESOLVED or FIELD_ABSENT

This is the core deliverable: which REQUIRED_AT_BIRTH items does the current schema support vs which are MISSING.

---

Report path

knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-completion-readonly-inventory-report.md

Final response format

birth_inventory_status=PASS|PARTIAL|BLOCKED
db_connection_status=RESOLVED|UNKNOWN
schema_status=RESOLVED|UNKNOWN
table_family_resolution_complete=true|false
concept_resolution_complete=true|false
birth_registry_row_count=<N>
birth_null_species_count=<N>
birth_trigger_count=<N>
collections_governed_count=<N>
collections_with_birth_trigger=<N>
collections_without_birth_trigger=<N>
species_count_live=<N>
species_collection_map_count=<N>
entity_dependencies_status=EXISTS|ABSENT
entity_labels_status=EXISTS|ABSENT
universal_edges_status=EXISTS|ABSENT
event_outbox_status=EXISTS|ABSENT
pivot_definitions_status=EXISTS|ABSENT
fn_iu_create_exists=true|false
fn_birth_registry_auto_exists=true|false
required_at_birth_supported_count=<N>/10
required_at_birth_unknown_count=<N>/10
required_hook_supported_count=<N>/7
required_hook_unknown_count=<N>/7
compiled_sql_logged=true|false
canonical_address_coverage=<N>/<sample_size>
jsonb_profile_coverage=<N>/<sample_size>
report_path=<above>
no_mutation_performed=true
next_recommended_action=GPT_REVIEW_INVENTORY_THEN_BIRTH_CONTRACT

---

Birth System Completion Read-Only Inventory | DRAFT rev2 | Self-contained, discovery-first, zero hardcode | 2026-05-12