P3D — Birth B3-P Policy Population — Agent Prompt (DRAFT rev2)

Date: 2026-05-12 Rev: 2 (no hardcoded counts, approved artifact with revision, old value capture, restore rollback) Self-contained. Prerequisite: B3-P DDL PASS.

Effort: medium.

---

Mission

Populate coverage policy columns for ALL governed collections based on approved classification artifact. Single transaction. Capture old values for rollback.

---

Scope constants

approved_classification_artifact = knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-coverage-classification-report.md
approved_classification_revision = 1 (verify before use)
approved_population_actor = 'b3p_policy_population'
db_schema = DISCOVER

---

Hard boundaries

Data mutation: UPDATE collection_registry coverage_* columns ONLY. No DDL. No function/trigger. No other table mutation.

---

Phase 0 — Discovery + prerequisites

0A. DB/schema discovery (self-contained inline)

0B. Verify coverage columns exist on collection_registry

Resolve concepts: coverage_status, coverage_scope_status, coverage_exemption_reason, coverage_review_owner, coverage_decided_at, coverage_decided_by.

If ANY absent → STOP, POLICY_STORAGE_NOT_READY.

---

Phase 1 — Load approved classification

Read classification artifact from KB. Verify revision matches.

Extract per-collection mappings as structured data:

For each collection in classification report:
  collection_name → classification_status (BIRTH_REQUIRED / EXEMPT_JUNCTION / EXEMPT_LOG / EXEMPT_CACHE / DEFERRED)
  collection_name → sub_classification (if DEFERRED: excluded_CMS / excluded_AgencyOS / missing_table / special_case)

No hardcoded counts. Agent counts dynamically from artifact content. Report derived counts for verification.

Also identify collections that ALREADY HAVE birth triggers (live query, not hardcoded "29"):

-- PATTERN ONLY
SELECT DISTINCT event_object_table FROM information_schema.triggers
WHERE trigger_schema = <resolved_schema> AND trigger_name LIKE 'trg_birth_%' AND event_manipulation = 'INSERT';

---

Phase 2 — Build population map

For EACH collection in collection_registry, determine coverage values:

Policy mapping rules (GPT-locked, from design §5):

IF collection has existing birth trigger → BIRTH_REQUIRED + IN_SCOPE
IF classification = BIRTH_REQUIRED → BIRTH_REQUIRED + IN_SCOPE
IF classification = EXEMPT_JUNCTION → BIRTH_EXEMPT_STRUCTURAL_JUNCTION + IN_SCOPE + exemption reason
IF classification = EXEMPT_LOG → BIRTH_EXEMPT_SYSTEM_LOG_OR_AUDIT + IN_SCOPE + exemption reason
IF classification = EXEMPT_CACHE → BIRTH_EXEMPT_DERIVED_CACHE + IN_SCOPE + exemption reason
IF classification = DEFERRED + sub=excluded_CMS → BIRTH_DEFERRED_NEEDS_REVIEW + USER_EXCLUDED
IF classification = DEFERRED + sub=excluded_AgencyOS → BIRTH_DEFERRED_NEEDS_REVIEW + FUTURE_SCOPE
IF classification = DEFERRED + sub=missing_table → BIRTH_DEFERRED_NEEDS_REVIEW + ORPHAN_REGISTRY
IF classification = DEFERRED + sub=special_case → BIRTH_DEFERRED_NEEDS_REVIEW + (per case)
IF collection NOT in classification AND NOT in trigger list → UNCLASSIFIED_NEW → STOP if count > 0

---

Phase 3 — Old value capture

Before ANY update, capture current state:

-- PATTERN ONLY
SELECT <collection_key>,
       <coverage_status>, <coverage_scope_status>,
       <coverage_exemption_reason>, <coverage_review_owner>,
       <coverage_decided_at>, <coverage_decided_by>
FROM <collection_registry>;

Store as old_values_snapshot. This is the rollback source.

---

Phase 4 — Execution log + compiled preview

Log: population map, compiled UPDATE statements, old_values_snapshot. compiled_sql_logged=true.

---

Phase 5 — Population transaction

BEGIN;

-- Advisory lock
SELECT pg_try_advisory_xact_lock(hashtext('p3d_birth_b3p_policy_population'));

-- For each collection in population_map:
--   Only UPDATE where current coverage_status IS NULL (don't overwrite existing decisions)
--   SET coverage_status, coverage_scope_status, exemption_reason, review_owner
--   SET coverage_decided_at = now(), coverage_decided_by = approved_population_actor

-- Capture: updated_collection_list, skipped_already_set_list, unclassified_list

-- Verify: count where coverage_status IS NULL should = unclassified_count
-- If unclassified_count > 0 → STOP, ROLLBACK, report UNCLASSIFIED_NEW_COLLECTIONS

COMMIT;

---

Phase 6 — Post-commit verification

-- PATTERN ONLY
SELECT <coverage_status>, <coverage_scope_status>, count(*)
FROM <collection_registry>
GROUP BY 1, 2 ORDER BY 1, 2;

Report distribution. Compare with population map — should match.

---

Phase 7 — KB report + VPS log

Rollback (restore old values):

-- For each collection in updated_collection_list:
UPDATE <collection_registry>
SET <coverage_status> = <old_value>,
    <coverage_scope_status> = <old_scope_value>,
    <coverage_exemption_reason> = <old_exemption>,
    <coverage_review_owner> = <old_owner>,
    <coverage_decided_at> = <old_decided_at>,
    <coverage_decided_by> = <old_decided_by>
WHERE <collection_key> = '<collection_name>';

Per-row restore from old_values_snapshot. Not blanket NULL.

Post-rollback verification: re-query coverage distribution, compare with old_values_snapshot.

---

Final response

b3p_population_status=PASS|PARTIAL|BLOCKED|UNCLASSIFIED_STOP
total_governed=<N>
populated_count=<N>
already_set_count=<N>
unclassified_new_count=<N>
birth_required_total=<N>
exempt_total=<N>
deferred_total=<N>
null_coverage_remaining=<N>
old_values_captured=true|false
compiled_sql_logged=true|false
execution_log_path=<path>
approved_artifact_revision_verified=true|false
no_ddl=true

---

B3-P Population | rev2 | No hardcoded counts, old value restore | 2026-05-12