P3D — Birth System Completion Read-Only Inventory Report

Date: 2026-05-12 Mode: READ-ONLY (no DB write, no DDL, no migration, no patch, no UI/Nuxt, no deploy) Prompt: knowledge/dev/laws/dieu44-trien-khai/prompts/p3d-birth-system-completion-readonly-inventory-prompt-DRAFT.md rev2 (approved) Reviewer approval: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-final-review-p3d-birth-system-inventory-prompt-rev2-approved-2026-05-12.md

Phase 0 — Environment + DB discovery

Item	Resolved	Notes
db_container	`postgres` (image `postgres:16`, status `Up 3 weeks (healthy)`)	from `docker ps` on VPS 38.242.240.89
compose/env files	`/opt/incomex/docker/.env`, `/opt/incomex/.env` discovered
target_db	`directus` (DB_DATABASE=directus, DB_USER=directus)	birth-system tables live in `directus`, not `workflow`
connecting role	`workflow_admin` (superuser of postgres cluster, has access to `directus`)	DB owner role is `directus`; admin used for read-only
schemas in `directus`	`public`, `sandbox_tac` (+ session temp schemas)	resolved schema = `public`

db_connection_status = RESOLVED schema_status = RESOLVED (directus.public)

Phase 1 — Table-family existence

Query pattern compiled:

SELECT table_schema, table_name FROM information_schema.tables
WHERE table_name IN ('birth_registry','entity_species','species_collection_map',
  'collection_registry','information_unit','unit_version','entity_dependencies',
  'entity_labels','universal_edges','event_outbox','pivot_definitions')
  AND table_schema='public';

family_id	resolved table	status
birth_registry	`public.birth_registry`	EXISTS
species_collection_map	`public.species_collection_map`	EXISTS
entity_species	`public.entity_species`	EXISTS
collection_registry	`public.collection_registry`	EXISTS
information_unit	`public.information_unit`	EXISTS
unit_version	`public.unit_version` (also `sandbox_tac.unit_version`)	EXISTS
entity_dependencies	`public.entity_dependencies`	EXISTS
entity_labels	`public.entity_labels`	EXISTS
universal_edges	`public.universal_edges`	EXISTS
event_outbox	`public.event_outbox`	EXISTS
pivot_definitions	`public.pivot_definitions`	EXISTS

table_family_resolution_complete = true (11/11)

Phase 2 — Concept resolution

2A. birth_registry concepts (introspected from `information_schema.columns`)

concept_id	resolved column	status
birth_pk	`id` (integer)	RESOLVED
birth_collection_key	`collection_name` (varchar, NOT NULL)	RESOLVED
birth_entity_ref	`entity_code` (varchar, NOT NULL)	RESOLVED
birth_species_identifier	`species_code` (varchar)	RESOLVED
birth_composition_identifier	`composition_level` (varchar)	RESOLVED
birth_created_at	`born_at` (timestamptz; also `date_created`)	RESOLVED
birth_actor	`user_created` (uuid)	RESOLVED
birth_governance_state	`governance_role` (varchar)	RESOLVED
birth_lifecycle_state	`status` (varchar) + `certified` (bool) + `certified_at`	RESOLVED
birth_owner_ref	(none)	FIELD_ABSENT
birth_provenance_ref	`dot_origin` (varchar)	RESOLVED

Additional fields present (not in concept registry): inspect_pen, inspect_stamp, inspect_gate (lifecycle gates), sort.

2B. species/mapping concepts

concept_id	resolved column	status
species_pk	`entity_species.id`	RESOLVED
species_code	`entity_species.species_code`	RESOLVED
species_composition	`entity_species.composition_level`	RESOLVED
mapping_collection_key	`species_collection_map.collection_name`	RESOLVED
mapping_species_ref	`species_collection_map.species_code`	RESOLVED
mapping_is_primary	`species_collection_map.is_primary`	RESOLVED

2C. collection_registry concepts

concept_id	resolved column	status
collection_key	`collection_registry.collection_name` (also `code`)	RESOLVED (collection_name is the join key to species_collection_map / birth_registry; `code` is the registry-internal code)
collection_governance_role	`governance_role` (varchar)	RESOLVED
collection_migration_state	`migration_state` (varchar)	RESOLVED

concept_resolution_complete = true (all concepts resolved; one FIELD_ABSENT — birth_owner_ref)

Phase 3 — Birth-system component inventory

3A. birth_registry state

Compiled SQL:

SELECT count(*) AS total,
       count(*) FILTER (WHERE species_code IS NOT NULL) AS with_species,
       count(*) FILTER (WHERE species_code IS NULL) AS null_species
FROM public.birth_registry;

total	with_species	null_species
208,537	208,537	0

Top 20 by collection_name (compiled SELECT collection_name, count(*) FROM public.birth_registry GROUP BY 1 ORDER BY 2 DESC):

collection_name	count
entity_labels	118,933
registry_changelog	48,774
system_issues	28,518
universal_edges	2,617
knowledge_documents	2,465
universal_rule_violations	1,741
directus_fields	1,532
lifecycle_log	851
dot_tools	590
task_comments	278
law_dot_enforcement	272
meta_catalog	191
collection_registry	169
entity_dependencies	160
species_collection_map	160
universal_rule_run_results	130
checkpoint_instances	121
trigger_registry	110
workflow_step_relations	80
workflow_steps	70

3B. species_collection_map state

SELECT count(*) FROM public.species_collection_map; → 154 rows.

3C. entity_species state

SELECT count(*) FROM public.entity_species; → 41 species.

3D. fn_birth_registry_auto

SELECT proname, pronargs FROM pg_proc WHERE proname='fn_birth_registry_auto';

proname	pronargs
fn_birth_registry_auto	0

Function exists (0-arg trigger function, used by trg_birth_* AFTER INSERT triggers). Bounded fact set: signature confirmed; full body not dumped (per rev2 bounded-output rule). Write target inferred from trigger coverage = public.birth_registry.

fn_birth_registry_auto_exists = true

3E. Birth-trigger inventory

Compiled SQL:

SELECT trigger_name, event_object_table, action_timing, event_manipulation
FROM information_schema.triggers
WHERE trigger_schema='public' AND trigger_name LIKE 'trg_birth_%'
ORDER BY event_object_table;

Total rows returned: 33 (birth_trigger_count = 33).

Distinct governed collections with trg_birth_* AFTER INSERT trigger (excluding birth_registry self-triggers):

agents, approval_requests, checkpoint_sets, checkpoint_types, collection_registry,
dot_tools, entity_dependencies, entity_species, governance_registry,
governance_relations, information_unit, law_catalog, law_dot_enforcement,
law_jurisdiction, measurement_registry, meta_catalog, modules, normative_registry,
pivot_definitions, pivot_results, system_issues, table_registry, tasks, taxonomy,
taxonomy_facets, ui_pages, workflow_change_requests, workflow_steps, workflows

→ 29 distinct collections with INSERT birth trigger.

birth_registry self-triggers (do not count toward governed coverage):

trg_birth_auto_certify BEFORE UPDATE
trg_birth_change_flag_matrix AFTER INSERT/UPDATE/DELETE (×3)

3F. Collection registry vs birth-trigger coverage

SELECT count(*) FROM public.collection_registry;                                     -- 166
SELECT count(*) FROM public.collection_registry WHERE governance_role IS NOT NULL;   -- 166

metric	value
collections_governed_count	166
collections_with_birth_trigger	29
collections_without_birth_trigger	137

Coverage gap: 17.5% of governed collections currently have an INSERT birth trigger — major gap to close in B-phase implementation.

Phase 4 — IU-specific birth state

4A. fn_iu_create

args	returns
`p_canonical_address text, p_title text, p_body text, p_actor text, p_unit_kind text DEFAULT NULL, p_section_type text DEFAULT NULL, p_owner_ref text DEFAULT NULL, p_publication_type text DEFAULT NULL, p_parent_ref uuid DEFAULT NULL`	`jsonb`

fn_iu_create_exists = true (pronargs=9).

4B. fn_iu_create_plan

Exists (pronargs=9).

4C. fn_iu_birth_gate_layer1

Exists (pronargs=0, trigger function).

4D. IU/UV counts

metric	value
`information_unit` rows	12
`unit_version` rows	19
birth_registry rows where collection_name='information_unit'	12

IU birth registration is 100% covered (12 IU rows ↔ 12 birth_registry rows).

Phase 5 — Enrichment-hook inventory

family	exists	row_count
entity_dependencies	EXISTS	142
entity_labels	EXISTS	118,832
universal_edges	EXISTS	2,199
event_outbox	EXISTS	19,451
pivot_definitions	EXISTS	37

All 5 enrichment hook tables are present and populated. Schema columns introspected in Phase 2 (see source query).

Phase 6 — Metadata column coverage (deterministic sample)

Governed collections = 166 (>20) → sample = first 10 by collection_name ASC + IU + UV = 12 collections.

Compiled SQL:

WITH base AS (
  SELECT collection_name AS c FROM public.collection_registry ORDER BY collection_name ASC LIMIT 10
), sample AS (SELECT c FROM base UNION SELECT 'information_unit' UNION SELECT 'unit_version')
SELECT s.c,
  EXISTS(SELECT 1 FROM information_schema.columns
         WHERE table_schema='public' AND table_name=s.c AND column_name='canonical_address') AS has_canonical_address,
  EXISTS(SELECT 1 FROM information_schema.columns
         WHERE table_schema='public' AND table_name=s.c AND data_type IN ('jsonb','json')) AS has_jsonb,
  EXISTS(SELECT 1 FROM information_schema.triggers
         WHERE trigger_schema='public' AND event_object_table=s.c
           AND trigger_name LIKE 'trg_birth_%' AND event_manipulation='INSERT') AS has_birth_trigger,
  EXISTS(SELECT 1 FROM public.species_collection_map WHERE collection_name=s.c) AS has_species_map
FROM sample s ORDER BY 1;

collection	canonical_address	jsonb	birth_trigger	species_map
admin_fallback_log	f	t	f	f
agents	f	f	t	t
agent_views	f	t	f	t
ai_discussion_comments	f	f	f	t
ai_discussions	f	t	f	t
ai_tasks	f	f	f	t
approval_requests	f	t	t	t
apr_action_types	f	f	f	f
apr_approvals	f	f	f	f
apr_request_types	f	f	f	f
information_unit	t	t	t	t
unit_version	f	t	f	f

metric	value
canonical_address_coverage	1/12 (information_unit only)
jsonb_profile_coverage	6/12
birth_trigger_coverage_in_sample	3/12
species_map_coverage_in_sample	7/12

Phase 7 — Birth metadata completeness vs design v3 §E

Design item	birth_registry column	Status
Identity (entity ref)	`entity_code`	RESOLVED
Species	`species_code`	RESOLVED
Composition	`composition_level`	RESOLVED
Timestamp	`born_at`	RESOLVED
Governance state	`governance_role`	RESOLVED
Lifecycle	`status` (+`certified`,`certified_at`)	RESOLVED
Owner	(none)	FIELD_ABSENT
Provenance	`dot_origin`	RESOLVED
Actor	`user_created` (+`user_updated`)	RESOLVED
Collection key	`collection_name`	RESOLVED

required_at_birth supported: 9/10. Unknown/Absent: 1/10 (birth_owner_ref).

Hook tables (required_hook 7):

entity_dependencies ✓
entity_labels ✓
universal_edges ✓
event_outbox ✓
pivot_definitions ✓
information_unit ✓
unit_version ✓

required_hook supported: 7/7. Unknown: 0/7.

Critical blocker notes

Coverage gap is the biggest finding. 137/166 governed collections (82.5%) have NO trg_birth_* AFTER INSERT trigger. Birth contract must close this gap or explicitly mark scope-out collections.
birth_owner_ref is absent from birth_registry. If the design v3 §E "Owner" requirement is load-bearing, schema patch will be needed (out of scope for read-only inventory).
canonical_address is sparse. Only information_unit in the sample has it; if the birth contract requires universal canonical addressing, a large migration is implied.
birth_registry has entity_code only (varchar), not an FK-typed entity_ref. Provenance back to source entity relies on (collection_name, entity_code) tuple — not enforced by FK. Document in contract.
null_species_count = 0 — every existing birth row has a species_code. Good baseline; species-coverage gap is fully on the trigger side (collections lacking births), not orphan births.
information_unit is 100% birth-covered (12 IU = 12 birth rows). Pilot for DIEU-35 is on a coherent footing.

No mutation performed. All SQL above was executed read-only against directus.public on VPS 38.242.240.89 inside the postgres:16 container.

Final response status

birth_inventory_status=PARTIAL
db_connection_status=RESOLVED
schema_status=RESOLVED
table_family_resolution_complete=true
concept_resolution_complete=true
birth_registry_row_count=208537
birth_null_species_count=0
birth_trigger_count=33
collections_governed_count=166
collections_with_birth_trigger=29
collections_without_birth_trigger=137
species_count_live=41
species_collection_map_count=154
entity_dependencies_status=EXISTS
entity_labels_status=EXISTS
universal_edges_status=EXISTS
event_outbox_status=EXISTS
pivot_definitions_status=EXISTS
fn_iu_create_exists=true
fn_birth_registry_auto_exists=true
required_at_birth_supported_count=9/10
required_at_birth_unknown_count=1/10
required_hook_supported_count=7/7
required_hook_unknown_count=0/7
compiled_sql_logged=true
canonical_address_coverage=1/12
jsonb_profile_coverage=6/12
report_path=knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-completion-readonly-inventory-report.md
no_mutation_performed=true
next_recommended_action=GPT_REVIEW_INVENTORY_THEN_BIRTH_CONTRACT

P3D Birth System Completion Read-Only Inventory | rev2 executed | 2026-05-12

P3D — Birth System Completion Read-Only Inventory Report

Phase 0 — Environment + DB discovery

Phase 1 — Table-family existence

Phase 2 — Concept resolution

2A. birth_registry concepts (introspected from information_schema.columns)

2B. species/mapping concepts

2C. collection_registry concepts

Phase 3 — Birth-system component inventory

3A. birth_registry state

3B. species_collection_map state

3C. entity_species state

3D. fn_birth_registry_auto

3E. Birth-trigger inventory

3F. Collection registry vs birth-trigger coverage

Phase 4 — IU-specific birth state

4A. fn_iu_create

4B. fn_iu_create_plan

4C. fn_iu_birth_gate_layer1

4D. IU/UV counts

Phase 5 — Enrichment-hook inventory

Phase 6 — Metadata column coverage (deterministic sample)

Phase 7 — Birth metadata completeness vs design v3 §E

Critical blocker notes

Final response status

2A. birth_registry concepts (introspected from `information_schema.columns`)