P3D — Birth Coverage Classification Read-Only — Agent Prompt (DRAFT rev2)

Date: 2026-05-12 Rev: 2 (concept registry, table-family registry, weighted evidence, PG-native) Mode: DRAFT — not approved for dispatch Self-contained: Agent reads ONLY this document.

Effort: medium-high.

---

Mission

Classify every governed collection lacking trg_birth_* INSERT trigger into 1 of 5 statuses. Read-only.

---

Scope constants

target = governed collections WITHOUT birth INSERT trigger
db_schema = DISCOVER (historical expectation: public — not execution truth)
statuses = BIRTH_REQUIRED | BIRTH_EXEMPT_STRUCTURAL_JUNCTION | BIRTH_EXEMPT_SYSTEM_LOG_OR_AUDIT | BIRTH_EXEMPT_DERIVED_CACHE | BIRTH_DEFERRED_NEEDS_REVIEW

---

Hard boundaries

No DB write. No DDL. No trigger creation. No function patch. No migration. No nested dispatch.

---

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

0A. DB container

# Primary: docker compose config
docker compose -f /opt/incomex/docker/docker-compose.yml config --services 2>/dev/null || \
  docker-compose -f /opt/incomex/docker/docker-compose.yml config --services 2>/dev/null
# Fallback with timeout:
timeout 5 docker ps --format '{{.Names}} {{.Image}}' 2>/dev/null | grep -i postgres

If both fail → DB_CONTAINER_UNKNOWN → BLOCKED.

0B. DB credentials

# From compose/env
grep -h 'DB_DATABASE\|DB_USER\|DB_PORT' /opt/incomex/docker/.env /opt/incomex/.env 2>/dev/null

If not found → fallback:

find / -maxdepth 4 \( -name '.env' -o -name 'docker-compose*' \) -type f 2>/dev/null | head -5

0C. Schema discovery

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

Use resolved schema in ALL subsequent queries.

---

Phase 1 — Table-family registry + existence

Verify each table family before use:

-- PATTERN ONLY
SELECT table_name FROM information_schema.tables
WHERE table_schema = <resolved_schema> AND table_name = '<family>';

family_id	semantic_hint	required_for	on_absent
collection_registry	Governed collection catalog	Core: uncovered list	CRITICAL
birth_registry	Birth records	Birth row presence signal	Report ABSENT
species_collection_map	Species mapping	Species signal	Report ABSENT
information_schema.triggers	Trigger metadata	Trigger coverage	CRITICAL (system catalog)
information_schema.columns	Column metadata	Schema shape	CRITICAL (system catalog)
information_schema.table_constraints	FK metadata	FK profile	CRITICAL (system catalog)

---

Phase 2 — Concept registry + resolution

Introspect resolved tables:

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

Resolve concepts (semantic hints = guidance, NOT assertions):

concept_id	table_family	semantic_hint	required_for	on_unresolved
collection_key	collection_registry	Collection name/identifier	Join + filter	ABORT
collection_governance_role	collection_registry	Governance role/level	Classification signal	Report FIELD_ABSENT
collection_migration_state	collection_registry	Migration/pilot state	Classification signal	Report FIELD_ABSENT
birth_collection_key	birth_registry	Collection name in birth	Birth presence check	Report FIELD_ABSENT
species_map_collection_key	species_collection_map	Collection name in mapping	Species signal	Report FIELD_ABSENT

Additional concepts resolved per-collection in Phase 3 (not pre-declared — discovered dynamically):

For each uncovered collection:
  column_name, column_data_type, column_nullable → from information_schema.columns
  fk_source_column, fk_target_table → from information_schema.table_constraints + key_column_usage
  trigger_name, trigger_event → from information_schema.triggers
  row_count → from count(*)

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

---

Phase 3 — Identify uncovered collections

-- PATTERN ONLY — compile after Phase 1+2
WITH governed AS (
  SELECT <collection_key> AS cname FROM <resolved_schema>.<collection_registry>
),
has_trigger AS (
  SELECT DISTINCT event_object_table AS cname
  FROM information_schema.triggers
  WHERE trigger_schema = <resolved_schema>
    AND trigger_name LIKE 'trg_birth_%'
    AND event_manipulation = 'INSERT'
)
SELECT g.cname
FROM governed g
LEFT JOIN has_trigger t ON t.cname = g.cname
WHERE t.cname IS NULL
ORDER BY g.cname;

Store as uncovered_list.

---

Phase 4 — Gather weighted evidence per collection

For EACH collection in uncovered_list, gather 10 signals:

4.1 Schema shape

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

Derive: total_columns, has_pk (column with 'id' or serial/uuid), has_text_columns, has_jsonb_columns.

4.2 FK profile

-- PATTERN ONLY
SELECT kcu.column_name, ccu.table_name AS fk_target
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name AND tc.table_schema = ccu.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = <resolved_schema>
  AND tc.table_name = '<cname>';

Derive: fk_count, fk_targets list.

4.3 PK profile

-- PATTERN ONLY
SELECT kcu.column_name, c.data_type
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.columns c ON c.table_name = kcu.table_name AND c.column_name = kcu.column_name AND c.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = <resolved_schema>
  AND tc.table_name = '<cname>';

Derive: pk_type (uuid / integer / composite / none).

4.4 Row count

-- PATTERN ONLY
SELECT count(*) FROM <resolved_schema>.<cname>;

4.5 Governance role + migration state

-- PATTERN ONLY
SELECT <collection_governance_role>, <collection_migration_state>
FROM <resolved_schema>.<collection_registry>
WHERE <collection_key> = '<cname>';

4.6 Species mapping presence

-- PATTERN ONLY
SELECT count(*) FROM <resolved_schema>.<species_collection_map>
WHERE <species_map_collection_key> = '<cname>';

4.7 Birth row presence

-- PATTERN ONLY
SELECT count(*) FROM <resolved_schema>.<birth_registry>
WHERE <birth_collection_key> = '<cname>';

4.8 Any existing triggers

-- PATTERN ONLY
SELECT trigger_name, event_manipulation FROM information_schema.triggers
WHERE trigger_schema = <resolved_schema> AND event_object_table = '<cname>';

4.9 Write-path indicators

Has INSERT/UPDATE triggers? (from 4.8). Has Directus M2O/O2M fields? (FK profile).

4.10 Naming pattern

Analyze collection name for signals: _log, _audit, _changelog, _history, _junction, _rel, _map, _link, _cache, _result, _temp, _derived, directus_*.

Naming = WEAK signal. Weight ≤ 0.2 in classification.

---

Phase 5 — Weighted classification

For each collection, score evidence:

Signal	Weight	BIRTH_REQUIRED indicators	EXEMPT indicators
governance_role = 'governed'	0.20	Governed = required signal	Non-governed = exempt signal
Has own PK (uuid or serial)	0.15	Own identity	Composite PK = junction signal
Has text/jsonb business columns	0.15	Business entity	Few columns = junction
species_mapping_present	0.10	Mapped = primary entity	Not mapped = might be secondary
birth_rows > 0	0.10	Already birthing (has trigger elsewhere?)	No births = new or exempt
fk_count ≤ 1	0.05	Self-contained	fk_count ≥ 2 + few own cols = junction
row_count > 0	0.05	Active table	0 rows = may be unused
Has business triggers	0.05	Active write path	No triggers
migration_state	0.05	pilot/active = relevant	deprecated = possibly exempt
Naming pattern	0.10	No exempt pattern	Log/junction/cache pattern

Classification rules:

IF table does not exist → BIRTH_DEFERRED_NEEDS_REVIEW
ELSE IF directus_* system table → BIRTH_EXEMPT_SYSTEM_LOG_OR_AUDIT
ELSE IF composite PK + fk_count ≥ 2 + total_columns ≤ 5 + no text/jsonb → BIRTH_EXEMPT_STRUCTURAL_JUNCTION
ELSE IF naming matches log/audit/changelog + no species mapping + append-only indicators → BIRTH_EXEMPT_SYSTEM_LOG_OR_AUDIT
ELSE IF naming matches cache/result/derived + no species mapping → BIRTH_EXEMPT_DERIVED_CACHE
ELSE IF governance_role = 'governed' + own uuid PK + has business columns → BIRTH_REQUIRED
ELSE → BIRTH_DEFERRED_NEEDS_REVIEW

Agent classifies. GPT/User reviews. Agent does NOT create triggers.

---

Phase 6 — Summary

total_uncovered = <N>
birth_required_count = <N>
exempt_junction_count = <N>
exempt_system_log_count = <N>
exempt_derived_cache_count = <N>
deferred_needs_review_count = <N>

---

Report path

knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-coverage-classification-report.md

Report: full table (collection × status × top 3 signals × score).

Final response format

classification_status=PASS|PARTIAL|BLOCKED
total_uncovered=<N>
birth_required_count=<N>
exempt_junction_count=<N>
exempt_system_log_count=<N>
exempt_derived_cache_count=<N>
deferred_needs_review_count=<N>
table_family_resolution_complete=true|false
concept_resolution_complete=true|false
compiled_sql_logged=true|false
report_path=<above>
no_mutation_performed=true
next_recommended_action=GPT_REVIEW_CLASSIFICATION_THEN_B3_TRIGGER_DESIGN

---

Birth Coverage Classification | DRAFT rev2 | Weighted evidence, PG-native | 2026-05-12