P3D — B3-F1c Full-Scan Automation Design

Date: 2026-05-13 Mode: DESIGN ONLY

---

1. Goal

Wrap reviewed full-scan query into a PG function that writes findings to system_issues + system_health_checks. Then design an automatic scheduler path after probing live scheduler availability. After B3-F1c execution and GPT review, B3-F may become eligible for COMPLETE status (trigger real-time + full-scan periodic coverage). Do not declare B3-F complete from this design note alone.

---

2. Phase split (two-pass)

Step	Mode	Scope
B3-F1c-a	READ-ONLY + COMPILE	Probe pg_cron/scheduler availability, probe system_health_checks shape, compile function + scheduler SQL, store in KB
GPT review	Review compiled SQL	—
B3-F1c-b	EXECUTE reviewed SQL	CREATE FUNCTION + schedule setup

---

3. Scheduling strategy: SHAPE_PROBE_FIRST

Do not assume pg_cron. Probe live:

1. pg_cron extension installed? → SELECT * FROM pg_extension WHERE extname='pg_cron'
2. pg_cron available but not installed? → SELECT * FROM pg_available_extensions WHERE name='pg_cron'
3. Existing DOT scheduler? → Check dot_tools for scheduled job entries
4. Directus Flows scheduler? → Check directus_flows for scheduled triggers

Probe result	Recommended path
pg_cron installed	Option A: cron.schedule() — best, fully PG-native
pg_cron available not installed	BLOCKED_FOR_EXTENSION_DECISION. Do not install extensions in B3-F1c-a; external/extension enablement requires separate GPT/user approval and explicit ops decision.
No pg_cron, Directus Flows exists	Option C: Directus Flow calling the function via webhook/operation
No pg_cron, no approved scheduler	BLOCKED_FOR_SCHEDULER_DECISION. A manual Agent/human call may be documented as an emergency fallback, but it does not satisfy automatic coverage and cannot make B3-F complete.

Regardless of scheduling path, the PG FUNCTION itself is the core deliverable. Scheduling is configuration.

---

4. Function design: fn_birth_onboarding_full_scan()

Behavioral contract

1. Run same 11 checks as B3-F1b gate, but across ALL collection_registry rows (not just trigger NEW row)
2. For each gap: call fn_b3f1_log_collection_onboarding_gap (REUSE existing helper — same dedup)
3. After gap scan: INSERT 1 summary row into system_health_checks with:
   - check type = 'BIRTH_ONBOARDING_FULL_SCAN'
   - result = critical_count + warn_count + compliant_count
   - timestamp
4. RETURN summary as JSON or text

Key: REUSE existing helper

fn_b3f1_log_collection_onboarding_gap already handles dedup (check open issue before INSERT). Full-scan function calls the SAME helper → no duplicate spam even if full-scan runs repeatedly.

No hardcode

Same rules as B3-F1b: sibling policy from dot_config, OIDs by proname (strict), no embedded lists, no snapshot counts.

---

5. system_health_checks shape (verify in B3-F1c-a)

Need to confirm columns for summary row INSERT. Probe:

• pk, check_type/name, target, status/result, details, timestamp
• Dedup: check if previous scan row exists → UPDATE or INSERT

---

6. Rollback

DROP FUNCTION IF EXISTS fn_birth_onboarding_full_scan();
-- If pg_cron was used: SELECT cron.unschedule('birth_onboarding_full_scan');
-- Do NOT remove B3-F1b gate, helper, trigger
-- Do NOT remove B3-A triggers or dot_config policy

---

7. After B3-F1c: B3-F = COMPLETE

B3-F1b (trigger on registry)     = real-time detection ✅ LIVE
B3-F1c (scheduled full-scan)     = periodic detection ⏳
Together = 100% automatic birth system governance

---

B3-F1c Full-Scan Automation Design | 2026-05-13