P3D — Birth System B3-F Onboarding Gate — Agent Prompt (DRAFT)

Date: 2026-05-12 Self-contained. Scope: install Collection Onboarding Gate infrastructure. Prerequisite: B3-A trigger install PASS + coverage policy columns exist on collection_registry.

Effort: medium.

---

Mission

Install PG-native Collection Onboarding Gate: (1) gate trigger function, (2) trigger on collection_registry, (3) periodic health check query. Stage 1+2 (audit + issue creation). No blocking.

---

Scope constants

gate_function_name = fn_collection_onboarding_gate (new — verify absent before create)
gate_trigger_name = trg_collection_onboarding_gate (new — verify absent)
health_check_type_value = BIRTH_COVERAGE_GAP
issue_category_value = BIRTH_ONBOARDING
db_schema = DISCOVER

---

Hard boundaries

DDL allowed: CREATE FUNCTION (gate only), CREATE TRIGGER (gate only).
No CREATE OR REPLACE — absent→create, exists-same→OK, exists-different→CONFLICT.
No ALTER TABLE (coverage columns assumed already added by prior step).
No business data mutation except system_health_checks + system_issues INSERT.
No birth trigger installation (that's B3-A).

---

Prerequisite check

Before gate creation, verify coverage policy columns exist on collection_registry. If coverage_status column absent → STOP, report POLICY_STORAGE_NOT_READY. Gate cannot function without policy storage.

---

Phase 0–1 — Discovery + concept resolution

Concept registry for gate

Agent resolves ALL of these from live schema (semantic hints, not assertions):

concept_id	table_family	semantic_hint	on_unresolved
cr_pk	collection_registry	Primary key	ABORT
cr_collection_key	collection_registry	Collection name	ABORT
cr_governance_role	collection_registry	Governance role	ABORT
cr_coverage_status	collection_registry	Coverage classification status	ABORT (POLICY_STORAGE_NOT_READY)
cr_coverage_scope	collection_registry	Coverage scope status	Report FIELD_ABSENT
cr_exemption_reason	collection_registry	Exemption reason text	Report FIELD_ABSENT
scm_collection_key	species_collection_map	Collection in mapping	ABORT
shi_pk	system_health_checks	Primary key	ABORT
shi_type	system_health_checks	Check type identifier	ABORT
shi_target	system_health_checks	Target entity ref	ABORT
shi_status	system_health_checks	Check result status	ABORT
shi_details	system_health_checks	Details JSONB/text	Report FIELD_ABSENT
shi_checked_at	system_health_checks	Timestamp	ABORT
si_pk	system_issues	Primary key	ABORT
si_category	system_issues	Issue category	ABORT
si_severity	system_issues	Severity level	ABORT
si_entity_ref	system_issues	Entity reference	ABORT
si_description	system_issues	Description text	ABORT
si_status	system_issues	Issue status	ABORT

---

Phase 2 — Gate function behavioral contract

Agent creates a PL/pgSQL function that:

Inputs: NEW row from collection_registry (trigger context)

Behavior:

1. Read resolved governance_role concept from NEW
2. IF governance_role IN (set of governed-like values — Agent reads distinct values from live data to determine, NOT hardcoded list) THEN:
3. Check 5 conditions using resolved concepts:
   • Physical table exists (information_schema.tables)
   • coverage_status non-NULL (resolved cr_coverage_status)
   • IF coverage_status = value-meaning-BIRTH_REQUIRED: species mapping exists (species_collection_map)
   • IF coverage_status = value-meaning-BIRTH_REQUIRED: birth trigger exists with fn_birth_registry_auto binding (pg_trigger + pg_proc)
   • IF coverage_status = value-meaning-EXEMPT: exemption reason non-NULL (cr_exemption_reason)
4. For each failed condition: INSERT system_health_checks row + system_issues row (if no open issue)
5. RETURN NEW (no blocking)

Agent derives actual PL/pgSQL from resolved concepts. Agent includes compiled function body in report for review.

---

Phase 3 — DDL conflict handling

Gate function:
  IF fn_collection_onboarding_gate absent → CREATE FUNCTION
  IF exists AND definition matches compiled → ALREADY_EXISTS_OK
  IF exists AND definition differs → BLOCKED_EXISTING_CONFLICT → STOP

Gate trigger:
  IF trg_collection_onboarding_gate absent on collection_registry → CREATE TRIGGER
  IF exists AND function binding matches → ALREADY_EXISTS_OK
  IF exists AND function binding differs → BLOCKED_EXISTING_CONFLICT → STOP

---

Phase 4 — Periodic health check query

Agent also produces a standalone SQL query (not a function/trigger — just a query) that:

Scans ALL governed collections
Checks same 5 conditions as gate
Outputs: collection_name, governance_role, 5 condition results, gap_detected boolean
Can be run ad-hoc or scheduled via pg_cron

Agent saves this query in the report as birth_coverage_gap_check_query.

---

Phase 5 — Installation transaction

BEGIN;
-- Advisory lock
-- Create gate function (if absent)
-- Create gate trigger on collection_registry (if absent)
-- Verify gate trigger exists with correct function binding
-- Test: UPDATE one collection_registry row's governance_role to same value (no-op change)
--   → verify system_health_checks row created (if gap exists) or no error (if no gap)
-- Capture: function OID, trigger name, any system_health_checks/system_issues PKs from test
COMMIT;

---

Phase 6 — Rollback

DROP TRIGGER IF EXISTS <exact_gate_trigger_name> ON <collection_registry>;
DROP FUNCTION IF EXISTS <exact_gate_function_name>();
DELETE FROM system_health_checks WHERE <shi_pk> IN (<captured_test_pks>);
DELETE FROM system_issues WHERE <si_pk> IN (<captured_test_pks>);

Exact captured artifacts only.

---

Final response

b3f_status=PASS|PARTIAL|BLOCKED|POLICY_STORAGE_NOT_READY
gate_function_created=true|false
gate_trigger_created=true|false
health_check_query_produced=true|false
policy_storage_ready=true|false
concept_resolution_complete=true|false
compiled_function_logged=true|false
no_business_data_mutation=true (except system_health_checks/system_issues)

---

B3-F Onboarding Gate | DRAFT | Behavioral contract, no PL/pgSQL body in prompt | 2026-05-12