P3D — B3-F1c-a Full-Scan Shape Probe + Compile Report

Date: 2026-05-13 Mode: READ-ONLY + COMPILE-ONLY (no DDL, no DML, no scheduler binding) Status: PARTIAL — all probes resolved cleanly; function + rollback compiled; scheduler compiled as a decision artifact (no executable scheduler SQL produced because pg_cron is unavailable and the alternative paths require non-SQL companions). Prompt: knowledge/dev/laws/dieu44-trien-khai/prompts/p3d-birth-system-b3f1c-full-scan-automation-shape-probe-prompt-DRAFT.md rev7 GPT approval: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b3f1c-full-scan-automation-design-and-prompt-approved-2026-05-13.md Context (live): B3-F1b soft gate PASS_ACCEPTED.

Executive summary

• pg_cron is neither installed nor available in the current PG image.
• directus_flows is the only proven-automatic scheduler vehicle in the live infra; recommended path is DIRECTUS_FLOW with a Nuxt companion endpoint.
• system_health_checks resolves cleanly as a check-definition registry (no per-run result/timestamp columns). Therefore per-run summary INSERTs into system_health_checks are not supportable against live shape; the full-scan function returns the summary via JSONB return value only.
• Existing helper and gate from B3-F1b are present and unambiguous.
• fn_birth_onboarding_full_scan does not exist — no naming conflict.
• All three artifacts (function, scheduler decision, rollback) are written to KB.
• No PG DDL/DML executed in this run.

Phase-by-phase findings

Phase 0 — Environment / schema discovery

db        : directus
schema    : public
pg version: PostgreSQL 16.13 (Debian 16.13-1.pgdg13+1) on x86_64-pc-linux-gnu

Phase 1 — Scheduler availability

Probe	Query	Result
pg_cron installed	SELECT extname,extversion FROM pg_extension WHERE extname='pg_cron'	0 rows
pg_cron available	SELECT name,default_version FROM pg_available_extensions WHERE name='pg_cron'	0 rows
DOT scheduler tables	dot_tools exists with cron_schedule, trigger_type, script_path columns	present
directus_flows exists	information_schema.tables WHERE table_name='directus_flows'	1 row
directus_flows schedule rows	WHERE trigger='schedule'	≥5 active flows already running cron (e.g. [DOT-REG] Count Refresh (6h), OPS Write Health Check, Permission Regression Check, Checklist Watchdog Timeout, AI Task Watchdog)
Live invocation pattern	scheduled flows chain to a request operation (HTTP POST to Nuxt)	confirmed via directus_operations for [DOT-REG] Count Refresh (6h)

Decision basis: DIRECTUS_FLOW is the only path with live evidence of automatic recurring execution today. dot_tools/SYSTEM_HEALTH_SCHEDULER are infrastructure-present but cron-runner-absent (per S178 A+6 memory). pg_cron is closed. Manual SELECT fn_birth_onboarding_full_scan() is documented as emergency-only and does NOT count as automatic.

Phase 2 — system_health_checks shape

Column	Type	Null	Default
code	text	NO	—
name	text	NO	—
jurisdiction	text	NO	—
check_kind	text	NO	'detect_only'
executor_type	text	NO	—
executor_ref	text	NO	—
threshold_config	jsonb	NO	'{}'
severity_on_fail	text	NO	—
auto_fix_action	text	YES	—
is_active	boolean	NO	true
order_index	integer	NO	—
description	text	YES	—
_dot_origin	text	NO	'unknown'

Primary key: code (single column).

Interpretation: this is a check-definition registry, not a run-results table. There is no status / result / details_json / checked_at column. Per-run summary writes are not compileable against this shape.

Mapped concepts requested by the prompt:

• shc_pk → code
• shc_check_type → semantically split across code + name (no dedicated check_type column)
• shc_target → semantically encoded in executor_ref + jurisdiction, no dedicated target column
• shc_status → not present (only is_active flag for definition state)
• shc_details → only definition-time threshold_config and description; no per-run details
• shc_checked_at → not present
• shc_dedup → none beyond PK on code

Sufficiency for the originally envisioned per-run summary write: NO.

→ system_health_checks_shape_sufficient = false.

A separate one-row INSERT registering the check definition itself (code = HC-BIRTH-ONBOARDING-FULL-SCAN) is possible but is out of scope for B3-F1c-a (compile-only). It belongs to whichever scheduler path is chosen and would be reviewed alongside that scheduler decision.

Phase 3 — Helper + gate verification

public.fn_b3f1_log_collection_onboarding_gap(p_entity_ref text, p_gap_type text, p_severity text, p_description text) RETURNS void    -- PRESENT
public.fn_collection_onboarding_soft_gate() RETURNS trigger                                                                          -- PRESENT

Both have exactly one matching signature; no ambiguity.

→ existing_helper_present = true, existing_gate_present = true.

Phase 4 — Conflict check

fn_birth_onboarding_full_scan exists = false

→ full_scan_function_conflict = false.

Phase 4b — Sibling policy live

SELECT key FROM public.dot_config WHERE key='policy.birth_trigger.accepted_sibling_scope';
-- 1 row, value JSONB length 280, contains entries[].function = 'fn_birth_registry_auto_id' …

→ sibling policy still loads. No hardcode required.

Phase 4c — B3-F1a reviewed artifact present

KB doc id: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-full-scan-query.sql.md
Revision: 3 (rev3 fixes #1 & #3 applied)

The compiled function reuses rev3 logic verbatim, modulo plpgsql wrapping.

Phase 5 — Full-scan function compile

Artifact: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1c-full-scan-function.sql.md (rev 1).

• CREATE FUNCTION (not OR REPLACE) — fails closed on conflict.
• Returns jsonb with run_id, scanned_at, total_checked, critical_count, warn_count, compliant_count, gap_collections.
• Reuses rev3 11-check logic verbatim (same severity matrix, same SIBLING_OUT_OF_SCOPE GLOBAL scope, same strict zero-arg trigger function resolution).
• Calls fn_b3f1_log_collection_onboarding_gap(text,text,text,text) per gap (helper performs dedup against system_issues).
• No INSERT into system_health_checks (shape insufficient — see Phase 2).
• No CREATE EXTENSION, no DDL beyond the function definition itself.
• compliant_count derived from total_checked - count(distinct collection_name with at least one gap); falls back to total_checked when no gaps.

→ full_scan_function_compiled = true.

Phase 6 — Scheduler compile

Artifact: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1c-full-scan-scheduler.sql.md (rev 1).

• pg_cron unavailable → no executable scheduler SQL compiled.
• Document includes a candidate Directus Flow + Operation seed with cron expression as configuration (not hardcoded truth) and <NUXT_HOST>/api/birth/onboarding/full-scan companion endpoint flagged as a separate, out-of-SQL deliverable.
• Alternative paths (DOT cron, system-health scheduler, pg_cron, manual fallback) documented with their blockers.

→ scheduler_compiled = NOT_APPLICABLE (no executable scheduler SQL produced; decision artifact is stored). → recommended_scheduling = DIRECTUS_FLOW.

Phase 7 — Rollback compile

Artifact: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1c-full-scan-rollback.sql.md (rev 1).

• Drops only fn_birth_onboarding_full_scan and (idempotently) any Directus Flow + Operation rows named for the full-scan if Path A is ever bound.
• Includes a DO $$ … $$ negative-assertion guard that aborts if the rollback would have collateral-dropped the helper, gate, or sibling policy.
• Explicitly does NOT touch system_issues history, system_health_checks registry, dot_tools, B3-A triggers, sibling-policy-registered trigger functions, or any registry data.

→ rollback_compiled = true.

Compliance with hard boundaries

Boundary	Verified
No DDL executed	✔ — only read-only SELECT and information_schema/pg_catalog queries hit live PG
No CREATE FUNCTION executed	✔ — function SQL only written to KB
No CREATE EXTENSION executed	✔ — pg_cron not installed; install path explicitly closed
No scheduler created	✔ — Directus Flow seed only documented; no INSERT
No INSERT/UPDATE/DELETE on PG	✔ — all probes were SELECT-only
No system_issues writes	✔ — helper not called by this run
No system_health_checks writes	✔ — registry not mutated
No external tool installation	✔ — no host commands beyond docker exec postgres psql ... -c '<SELECT>'
No hard gate	✔ — out of scope
No Phase 5C2	✔ — out of scope
No UI cutover	✔ — out of scope
KB artifacts uploaded	✔ — three artifacts + this report

Recommended next action

GPT_REVIEW_COMPILED_SQL_THEN_DECIDE_B3F1C_B_EXECUTION_OR_SCHEDULER_DESIGN

Reviewer should:

1. Verify fn_birth_onboarding_full_scan SQL line-for-line matches B3-F1a rev3 logic before approving execution.
2. Accept or override recommended_scheduling = DIRECTUS_FLOW. If accepted, a separate session must (a) define and review the Nuxt /api/birth/onboarding/full-scan companion endpoint, (b) approve a single cron cadence as policy, and (c) compile a Directus-Flow-binding execution artifact.
3. If overridden toward SYSTEM_HEALTH_SCHEDULER or AGENT_SCHEDULED_JOB, both require the S178 A+6 prerequisite (DOT-runner cron on host) to land first.
4. Treat system_health_checks_shape_sufficient = false as a design fact, not a defect — the registry stores definitions, not runs; run-summary is naturally captured via the function's JSONB return + gap entries in system_issues.

Required final response fields

b3f1c_probe_status=PARTIAL
pg_cron_installed=false
pg_cron_available=false
directus_flows_scheduler_exists=true
system_health_checks_shape_sufficient=false
existing_helper_present=true
existing_gate_present=true
full_scan_function_conflict=false
full_scan_function_compiled=true
scheduler_compiled=NOT_APPLICABLE
rollback_compiled=true
artifacts_stored_in_kb=true
no_ddl_executed=true
no_pg_mutation_performed=true
recommended_scheduling=DIRECTUS_FLOW
pg_cron_extension_install_allowed=false
external_tool_install_allowed=false
manual_fallback_counts_as_automatic=false
report_path=knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3f1c-full-scan-shape-probe-and-compile-report.md
next_recommended_action=GPT_REVIEW_COMPILED_SQL_THEN_DECIDE_B3F1C_B_EXECUTION_OR_SCHEDULER_DESIGN

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