P3D — B3-F1c Full-Scan Automation Note

Date: 2026-05-13 Mode: DESIGN NOTE — no execution

---

Why B3-F1b alone is NOT full automatic drift coverage

B3-F1b installs a trigger on collection_registry. This catches:

✅ New collection registered → gate fires → checks 11 conditions
✅ Existing collection changes governance_role/coverage_status → gate fires

This does NOT catch:

❌ Rogue trigger CREATE directly on another table (no collection_registry mutation)
❌ Physical table DROP/CREATE without collection_registry UPDATE
❌ Species mapping DELETE/INSERT without collection_registry change
❌ Birth trigger DISABLE/DROP without collection_registry change
❌ Function ALTER/REPLACE without collection_registry change
❌ Sibling policy dot_config UPDATE without collection_registry change

What's needed: PG-native scheduled full-scan

The full-scan query (compiled in B3-F1a rev3 artifact) checks ALL 166 collections against ALL 11 conditions. Running it periodically catches ALL drift types above.

Implementation options (design only — not executed)

Option	Mechanism	Pro	Con
A. pg_cron job	SELECT cron.schedule('birth_health', '0 */6 * * *', $$...$$)	PG-native, automatic, no external tool	Requires pg_cron extension
B. Dedicated PG function + manual/Agent call	SELECT fn_birth_onboarding_full_scan()	No extension needed	Requires human/Agent to remember to run
C. system_health_checks trigger	On schedule via existing health check framework	Integrates with existing DOT infra	Needs health check scheduler design

Recommendation: SHAPE_PROBE_FIRST, then choose implementation. Option A (pg_cron) is preferred only if a live read-only probe confirms the extension is installed/available and operational. If pg_cron is absent, do not install external tooling in this step; evaluate existing system_health_checks scheduler or a PG-native/manual Agent fallback through a separate design. Either way, results should be designed to write to system_health_checks + system_issues only after GPT review.

What B3-F1c must deliver

1. Wrap full-scan query into a PG function (fn_birth_onboarding_full_scan)
2. Function INSERTs gaps into system_issues (same dedup as soft gate)
3. Function INSERTs health check record into system_health_checks
4. Schedule via pg_cron OR document manual run procedure
5. Frequency: every 6 hours or daily

After B3-F1b + B3-F1c: hạ tầng tự mở rộng = COMPLETE

B3-F1b (trigger)     = real-time coverage for registry mutations
B3-F1c (full-scan)   = periodic coverage for ALL drift types
Together              = tự động 100%, không phụ thuộc thao tác thủ công

---

B3-F1c Automation Note | 2026-05-13