P3D — B3-F1a Soft Gate Shape Probe + SQL Artifact — Agent Prompt (DRAFT)

Date: 2026-05-12 Self-contained. Mode: READ-ONLY + COMPILE-ONLY. NO DDL. NO CREATE FUNCTION. NO CREATE TRIGGER. NO mutation.

---

Mission

Probe live PG schema for system_issues + gate dependencies. Compile soft gate SQL artifacts (function, trigger, rollback, full-scan). Store artifacts in KB. Do NOT execute any DDL or data mutation.

---

Scope constants

gate_function_name = fn_collection_onboarding_soft_gate
gate_trigger_name = trg_collection_onboarding_soft_gate
gate_trigger_target_family = collection_registry
sibling_policy_key = policy.birth_trigger.accepted_sibling_scope
contract_function_name = fn_birth_registry_auto
exempt_collection = birth_registry
issue_category = BIRTH_ONBOARDING
db_schema = DISCOVER

---

Hard boundaries

ZERO DDL. ZERO CREATE FUNCTION. ZERO CREATE TRIGGER.
ZERO INSERT/UPDATE/DELETE.
Read-only SELECTs are allowed against pg_catalog, information_schema, and the approved PG-native policy/data tables needed for compilation: collection_registry, dot_config, species_collection_map, system_issues, and system_health_checks if present. No PG writes of any kind. Write KB artifacts only.

---

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

---

Phase 1 — system_issues shape probe

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

Also probe:

• Primary key column(s)
• Unique constraints / indexes
• Any composite unique that could serve as dedup key
• Existing status values and any obvious terminal/open status convention. Do not hardcode terminal statuses such as resolved or closed unless supported by live data or an existing policy artifact.

Report full schema. Resolve concepts:

concept_id	semantic_hint	on_unresolved
si_pk	Primary key	BLOCKED
si_category	Issue category	BLOCKED
si_severity	Severity level	BLOCKED
si_entity_ref	Entity reference	BLOCKED
si_description	Description text	BLOCKED
si_status	Issue status	BLOCKED
si_created_at	Creation timestamp	Report FIELD_ABSENT

If ANY BLOCKED concept → report SCHEMA_INSUFFICIENT, do not compile function.

---

Phase 1B — system_health_checks shape probe

Probe whether system_health_checks exists and report its columns if present. This is informational for B3-F1a. Do not require it for soft-gate issue logging unless the design explicitly chooses it later. If absent, report system_health_checks_present=false and continue.

Phase 2 — collection_registry policy column probe

Resolve: cr_collection_key, cr_governance_role, cr_coverage_status, cr_coverage_scope_status, cr_coverage_exemption_reason, cr_coverage_review_owner.

Verify all 6 exist (from B3-P DDL). If any absent → BLOCKED.

---

Phase 3 — Dependency resolution

3A. Contract + sibling function OIDs

-- PATTERN ONLY
SELECT proname, oid FROM pg_proc
WHERE proname IN ('fn_birth_registry_auto', 'fn_birth_registry_auto_id')
  AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = <resolved_schema>);

3B. Sibling policy from dot_config

-- PATTERN ONLY
SELECT <config_value> FROM dot_config WHERE <config_key> = 'policy.birth_trigger.accepted_sibling_scope';

Parse JSON. Verify version, source_review, entries. Extract sibling function names + approved collections.

3C. species_collection_map concept

Resolve species_map_collection_key.

---

Phase 4 — Dedup strategy design (from live shape)

Based on Phase 1 probe:

• If system_issues has unique constraint on (entity_ref + category) → use as dedup key
• If no unique constraint but entity_ref + category + status columns exist → dedup by SELECT before INSERT using live-supported open/terminal status semantics. Prefer a live-detected open status if present. If only terminal statuses are detected, use them only with evidence in the report. Do not hardcode resolved/closed without live support or policy evidence.
• If entity_ref absent → BLOCKED (cannot safely dedup)

Report chosen dedup strategy with evidence.

---

Phase 5 — Compile SQL artifacts

5A. Compile gate function body

Using ALL resolved concepts from Phases 1-4, compile complete PL/pgSQL function body for fn_collection_onboarding_soft_gate.

Behavioral contract (what function does):

1. Read NEW row's governance_role + coverage_status + coverage_scope_status
2. Determine governed-like set from live data (not hardcoded)
3. If governed-like OR BIRTH_REQUIRED:
   • Check physical table exists
   • Check species mapping exists
   • Check birth trigger with accepted OID (contract OR sibling from policy)
   • Check birth_registry recursive risk
   • Check sibling out-of-scope
4. Check coverage_status non-NULL
5. If EXEMPT: check exemption_reason non-NULL
6. For each gap: dedup check → INSERT system_issues if no open issue
7. RETURN NEW always

Store compiled function in:

knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-function.sql.md

5B. Compile trigger SQL

CREATE TRIGGER trg_collection_onboarding_soft_gate
  AFTER INSERT OR UPDATE OF <cr_governance_role>, <cr_coverage_status>, <cr_coverage_scope_status>, <cr_coverage_exemption_reason>, <cr_coverage_review_owner>
  ON <resolved_schema>.<collection_registry>
  FOR EACH ROW EXECUTE FUNCTION fn_collection_onboarding_soft_gate();

Store in:

knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-trigger.sql.md

5C. Compile rollback SQL

DROP TRIGGER IF EXISTS trg_collection_onboarding_soft_gate ON <collection_registry>;
DROP FUNCTION IF EXISTS fn_collection_onboarding_soft_gate();

Store in:

knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-rollback.sql.md

5D. Compile companion full-scan query

Complete SQL query that performs all 10 checks across ALL governed collections (same logic as B3-F0 dry-run, but adapted to resolved concepts).

Store in:

knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-full-scan-query.sql.md

---

Phase 6 — Conflict check (read-only)

Check if gate function/trigger already exist:

fn_collection_onboarding_soft_gate: absent → OK for future create
trg_collection_onboarding_soft_gate: absent → OK
If exists → report, include in artifacts as conflict note

---

Report path

knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3f1a-soft-gate-shape-probe-and-sql-artifact-report.md

Final response

b3f1a_status=PASS|PARTIAL|BLOCKED|SCHEMA_INSUFFICIENT
system_issues_shape_sufficient=true|false
dedup_strategy=<description>
concepts_resolved=true|false
function_compiled=true|false
trigger_compiled=true|false
rollback_compiled=true|false
full_scan_compiled=true|false
artifacts_stored_in_kb=true|false
system_health_checks_present=true|false
dedup_status_semantics_supported_by_live_data=true|false
no_ddl_executed=true
no_pg_mutation_performed=true
gate_function_conflict=true|false
gate_trigger_conflict=true|false
report_path=<above>
next_recommended_action=GPT_REVIEW_COMPILED_SQL_THEN_B3F1B_EXECUTION

---

B3-F1a Shape Probe + SQL Artifact | DRAFT | Read-only compile-only | 2026-05-12