P3D — B3-F1c-a Full-Scan Shape Probe + SQL Compile — Agent Prompt (DRAFT)

Date: 2026-05-13 Self-contained. Mode: READ-ONLY + COMPILE-ONLY. NO DDL. NO mutation.

---

Mission

Probe scheduler availability + system_health_checks shape. Compile fn_birth_onboarding_full_scan() function SQL + scheduling SQL. Store artifacts in KB. Do NOT execute.

---

Scope constants

full_scan_function_name = fn_birth_onboarding_full_scan
existing_helper = fn_b3f1_log_collection_onboarding_gap(text,text,text,text) (REUSE, do NOT recreate)
existing_gate = fn_collection_onboarding_soft_gate (reference for logic alignment)
sibling_policy_key = policy.birth_trigger.accepted_sibling_scope
contract_function_name = fn_birth_registry_auto
health_check_type = BIRTH_ONBOARDING_FULL_SCAN
db_schema = DISCOVER

---

Hard boundaries

ZERO DDL. ZERO mutation. Read-only SELECTs only against pg_catalog, information_schema, and live PG-native tables needed for shape probe. If a candidate table such as dot_tools or directus_flows may not exist, check existence before querying it. KB artifact write only.

---

Phase 0 — Environment + DB discovery

---

Phase 1 — Scheduler availability probe

-- PATTERN ONLY
-- 1a. pg_cron installed?
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_cron';
-- 1b. pg_cron available?
SELECT name, default_version FROM pg_available_extensions WHERE name = 'pg_cron';
-- 1c. Existing DOT scheduler?
-- First check whether a candidate scheduler table such as dot_tools exists; only query it if present.
-- 1d. Directus Flows?
-- First check whether directus_flows exists and resolve its trigger/schedule columns; only query schedule rows if present.
-- Do not assume Directus Flows table or column names exist.

Report: which scheduling options are available live.

---

Phase 2 — system_health_checks 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_health_checks'
ORDER BY ordinal_position;

Resolve concepts for summary row:

• shc_pk, shc_check_type, shc_target, shc_status, shc_details, shc_checked_at

---

Phase 3 — Verify existing helper + gate

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

Both must exist (created by B3-F1b). If absent → BLOCKED.

---

Phase 4 — Conflict check

fn_birth_onboarding_full_scan: absent → OK
If exists → report, include conflict status

---

Phase 5 — Compile full-scan function

Behavioral contract

Function fn_birth_onboarding_full_scan() RETURNS jsonb:

1. Loop ALL collection_registry rows
2. For each: run same 11 checks as gate function (same logic, same scope rules)
3. For each gap: call existing fn_b3f1_log_collection_onboarding_gap (dedup handled by helper)
4. Count: total_checked, critical_count, warn_count, compliant_count
5. Compile summary-write logic only after system_health_checks shape is proven sufficient. Candidate behavior: INSERT one summary row per scan run, or use an idempotent/update strategy if live shape supports it. Required semantic fields: check type/name, status/result, details JSON, checked_at/timestamp. Exact column mapping must be derived from live shape; do not assume column names.
6. RETURN jsonb summary

Same anti-hardcode rules: sibling policy from dot_config, strict function resolution, no embedded lists.

CREATE FUNCTION only (not CREATE OR REPLACE). If function exists at execute time → BLOCKED.

Store compiled function SQL in:

artifacts/p3d-birth-system-b3f1c-full-scan-function.sql.md

---

Phase 6 — Compile scheduler SQL (if applicable)

If pg_cron available:

artifacts/p3d-birth-system-b3f1c-full-scan-scheduler.sql.md

Content must be compiled only if pg_cron is installed and live-compatible. The schedule expression must be treated as configuration, not hardcoded truth. Use a proposed default only as a reviewed candidate, and include a note that final cadence must be approved/materialized as scheduler config.

If pg_cron not available: document manual run procedure + recommend Directus Flow or Agent cron.

---

Phase 7 — Compile rollback SQL

artifacts/p3d-birth-system-b3f1c-full-scan-rollback.sql.md

DROP only B3-F1c-created artifacts, such as fn_birth_onboarding_full_scan and the exact scheduler job if a scheduler artifact is later approved/executed. Do NOT touch B3-F1b artifacts, B3-A triggers, dot_config sibling policy, system_issues data, or system_health_checks history.

---

Phase 8 — Store artifacts + report

Report: reports/p3d-birth-system-b3f1c-full-scan-shape-probe-and-compile-report.md

---

Final response

b3f1c_probe_status=PASS|PARTIAL|BLOCKED
pg_cron_installed=true|false
pg_cron_available=true|false
directus_flows_scheduler_exists=true|false
system_health_checks_shape_sufficient=true|false
existing_helper_present=true|false
existing_gate_present=true|false
full_scan_function_conflict=true|false
full_scan_function_compiled=true|false
scheduler_compiled=true|false|NOT_APPLICABLE
rollback_compiled=true|false
artifacts_stored_in_kb=true|false
no_ddl_executed=true
no_pg_mutation_performed=true
recommended_scheduling=PG_CRON|SYSTEM_HEALTH_SCHEDULER|DIRECTUS_FLOW|AGENT_SCHEDULED_JOB|BLOCKED_FOR_SCHEDULER_DECISION
pg_cron_extension_install_allowed=false
external_tool_install_allowed=false
manual_fallback_counts_as_automatic=false
next_recommended_action=GPT_REVIEW_COMPILED_SQL_THEN_DECIDE_B3F1C_B_EXECUTION_OR_SCHEDULER_DESIGN

---

B3-F1c-a Shape Probe + Compile | DRAFT | 2026-05-13