P3D Birth — B3-F1c-H On-demand Trigger B2 Execution Plan (Compile-Only)

Doc rev: 2 (Mode A / Mode B split patch) Date: 2026-05-14 Mode: COMPILE-ONLY. NOT executed. No SQL run. Author: Claude (Opus 4.7, 1M context) Patch motivation (this rev): knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b3f1c-h-sql-artifacts-patch-required-before-execution-2026-05-14.md — default Trigger B2 envelope must invoke the scanner exactly once. Direct scanner rollup moved to a separate Mode B requiring its own explicit authorization. GPT approval (parent): knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b2-rev3-b3f1c-h-dual-trigger-patch-approved-2026-05-14.md Design reference: knowledge/dev/laws/dieu44-trien-khai/design/p3d-birth-b2-contract-rev3-and-b3f1c-h-wrapper-integration-design.md §7

---

0. What changed in rev 2

Item	rev 1	rev 2
Default envelope scanner invocations	One via wrapper + optional second commented direct call	Exactly one via wrapper — direct call removed from default
Direct scanner rollup capability	Inlined as commented "optional second invocation"	Moved to separate Mode B with explicit Mode B authorization
Mode taxonomy	Implicit / mixed	Two named modes: A (default, wrapper boolean) and B (alternative, direct rollup)
Combination of modes	Implicit ("you could also run")	Explicitly forbidden in same session

1. Status

plan_status=DRAFTED
plan_executed=false
execution_authorized=false
manual_trigger_mutation_mode=INTENTIONAL_WRITE
required_authorization=GPT_REVIEW + USER_AUTHORIZATION (per-invocation, per-mode)

This document plans Trigger B2 (post-wrapper on-demand birth full-scan). It does NOT run the trigger.

2. Scope and prerequisites

Trigger B2 may only be invoked when all of the following hold (re-checked at execute time):

Prerequisite	Mode A	Mode B
Scanner public.fn_birth_onboarding_full_scan() installed	YES	YES
Wrapper public.fn_birth_onboarding_full_scan_hc(jsonb) RETURNS boolean installed	YES	optional (Mode B bypasses wrapper)
Wrapper COMMENT starts with B3-F1c-H wrapper	YES	optional (Mode B bypasses wrapper)
system_health_checks row code='DOT-BIRTH-ONBOARD-FULLSCAN-HC' present, is_active=true	YES	optional (Mode B is a diagnostic, not a dispatch test)
birth_registry.canonical_address / owner / jsonb_profile ELD columns present	YES	YES
Fresh GPT review of THIS plan	YES	YES
Explicit user authorization	YES (Mode A)	YES (Mode B — must explicitly name "Mode B")
KB report path drafted	YES	YES

If any prerequisite fails for the chosen mode, Trigger B2 is BLOCKED in that mode.

3. Mutation classification (NORMATIVE)

Aspect	Value
Class (both modes)	INTENTIONAL_WRITE
Reason	fn_birth_onboarding_full_scan() writes to system_issues by contract; both modes invoke the scanner exactly once.
Implication	NEITHER mode is read-only. NEITHER is dry-run. Neither idempotent in system_issues.
What is written	New system_issues rows for any new gaps detected. Existing rows unaffected.
Rollback	NONE. Scanner outputs are append-only audit trail. Do not delete system_issues rows produced by B2 invocations.

4. Approved execution — two modes, one scanner invocation each

4.1 Mode A — wrapper boolean validation (DEFAULT)

Use when: the Council wants to confirm the birth/onboarding gate passes under the current threshold without needing the full JSONB rollup.

Path: PG SELECT through wrapper.

Scanner invocations: exactly one (inside the wrapper).

Authorization: standard Trigger B2 authorization. No special "Mode B" naming required.

4.1.1 Mode A primary statement

-- Trigger B2 Mode A — INTENTIONAL_WRITE. One scanner invocation via wrapper.
SELECT public.fn_birth_onboarding_full_scan_hc('{}'::jsonb) AS hc_result;

Tolerant variant (requires authorization that names the chosen threshold):

-- Mode A tolerant — INTENTIONAL_WRITE. One scanner invocation via wrapper.
SELECT public.fn_birth_onboarding_full_scan_hc('{"critical_threshold": 5}'::jsonb) AS hc_result;

4.1.2 Mode A envelope (default approved batch)

-- =============================================================================
-- Trigger B2 Mode A envelope — wrapper boolean validation.
-- INTENTIONAL_WRITE. ONE scanner invocation (inside the wrapper).
-- AUTHORIZATION REQUIRED.
-- =============================================================================
SELECT now() AS ts_before, count(*) AS issues_before
FROM public.system_issues;

SELECT public.fn_birth_onboarding_full_scan_hc('{}'::jsonb) AS hc_result;

SELECT now() AS ts_after,
       count(*) AS issues_after,
       (SELECT max(id) FROM public.system_issues) AS max_issue_id_after
FROM public.system_issues;

-- Read-only spot-check for the KB report.
SELECT id, severity, category, message, created_at
FROM public.system_issues
WHERE created_at >= now() - interval '5 minutes'
ORDER BY id DESC
LIMIT 50;

The envelope contains exactly one statement that invokes the scanner (the wrapper SELECT). The bracketing count(*) SELECTs are read-only against system_issues.

4.2 Mode B — direct scanner rollup diagnostic (separate authorization)

Use when: the Council needs the full scanner JSONB rollup (not just the boolean). This is a diagnostic mode, not a dispatch test.

Path: PG SELECT directly to fn_birth_onboarding_full_scan(). Bypasses wrapper threshold semantics.

Scanner invocations: exactly one (direct).

Authorization: explicit, separate, must name "Mode B". Mode A authorization does NOT cover Mode B.

4.2.1 Mode B primary statement

-- Trigger B2 Mode B — INTENTIONAL_WRITE. One direct scanner invocation. Bypasses wrapper threshold.
SELECT public.fn_birth_onboarding_full_scan() AS scanner_rollup;

4.2.2 Mode B envelope

-- =============================================================================
-- Trigger B2 Mode B envelope — direct scanner rollup diagnostic.
-- INTENTIONAL_WRITE. ONE scanner invocation (direct).
-- BYPASSES wrapper threshold semantics — captures full JSONB rollup.
-- SEPARATE AUTHORIZATION (must explicitly name "Mode B").
-- =============================================================================
SELECT now() AS ts_before, count(*) AS issues_before
FROM public.system_issues;

SELECT public.fn_birth_onboarding_full_scan() AS scanner_rollup;

SELECT now() AS ts_after,
       count(*) AS issues_after,
       (SELECT max(id) FROM public.system_issues) AS max_issue_id_after
FROM public.system_issues;

-- Read-only spot-check for the KB report.
SELECT id, severity, category, message, created_at
FROM public.system_issues
WHERE created_at >= now() - interval '5 minutes'
ORDER BY id DESC
LIMIT 50;

The envelope contains exactly one statement that invokes the scanner (the direct SELECT). No wrapper involved.

4.3 Mode constraints (NORMATIVE)

1. Modes A and B MUST NOT be combined in a single authorized session. A combined session would invoke the scanner twice, which is forbidden by this plan.
2. If both wrapper boolean AND scanner rollup are needed: run two sequential authorized sessions — Mode A first, then Mode B with fresh authorization (or vice versa, but each with its own scanner invocation accepted and reported).
3. Each invocation produces its own KB execution report (§6).
4. Mode B is not a substitute for Mode A. Mode A is the canonical health-gate test; Mode B is a diagnostic for examining the rollup.
5. No "Mode A + Mode B in one batch" shortcut is provided. Any future variant must be reviewed and named explicitly.

5. Transport

Run via the approved PG path on the VPS — same transport used by B3-ELD-EXEC:

ssh root@38.242.240.89 'docker exec -i postgres psql -U directus -d directus -v ON_ERROR_STOP=1 -e' <<'SQL'
-- contents of §4.1.2 (Mode A) OR §4.2.2 (Mode B), NOT both
SQL

• MUST NOT wrap inside an unrelated script.
• MUST NOT batch with non-B2 SQL.
• MUST NOT use \set aliases that obscure the function name.
• MUST capture stdout/stderr to the execution report.

6. Required KB execution report fields

After invocation, the operator MUST upload a report to:

knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-b3f1c-h-on-demand-trigger-b2-execution-report-<UTC-timestamp>-mode<A|B>.md

Required fields:

Field	Source	Mode A	Mode B
mode	constant	A	B
timestamp_utc	now() captured before invocation	YES	YES
invoker	user/agent name	YES	YES
jurisdiction	NRM-LAW-35-V5P2	YES	YES
authorization_ref	path to GPT review + user authorization message (must name Mode B explicitly for Mode B)	YES	YES
exact_sql_executed	verbatim envelope copied from §4.1.2 (A) or §4.2.2 (B)	YES	YES
scanner_invocations	constant 1	YES	YES
wrapper_boolean_result	from hc_result SELECT	YES	N/A
scanner_rollup_jsonb	from scanner_rollup SELECT	N/A	YES
critical_count	extracted from rollup (wrapper folds it in Mode A; Mode B reads directly)	YES (only if also captured via separate Mode B session)	YES
system_issues_count_before	from first SELECT	YES	YES
system_issues_count_after	from third SELECT	YES	YES
system_issues_count_delta	after - before	YES	YES
system_issues_new_ids	from spot-check SELECT (id list)	YES	YES
system_issues_new_severities	extracted	YES	YES
cfg_passed	{} or the explicit object	YES	N/A
threshold_used	extracted from cfg (default 0)	YES	N/A
psql_exit_code	0 if success	YES	YES
stderr_capture	full stderr	YES	YES
wall_clock_duration_ms	from ssh wrapper timing	YES	YES
wrapper_function_present_at_start	preflight check result	YES	(optional)
row_present_at_start	preflight check result	YES	(optional)
dispatch_path_used	PG_SELECT (wrapper) (A) or PG_SELECT (direct scanner) (B)	YES	YES
mutation_mode	INTENTIONAL_WRITE	YES	YES
rollback_attempted	false (append-only)	YES	YES
next_recommended_action	per outcome	YES	YES

7. PASS / FAIL semantics (operational, not contract)

7.1 Mode A

hc_result	critical_count (post-hoc from system_issues if needed)	Operational reading
true	0 (or ≤ threshold)	Birth/onboarding healthy under current threshold
false	> threshold	Gaps present; triage system_issues rows added in this run
(error)	(n/a)	Scanner or wrapper error; check stderr; do NOT auto-retry; raise to Council

7.2 Mode B

scanner_rollup shape	Operational reading
JSONB with critical_count = 0	Birth/onboarding has no critical gaps (regardless of any threshold semantics; Mode B does not apply a threshold)
JSONB with critical_count > 0	Gaps present in the rollup; triage system_issues rows added in this run
NULL	Scanner returned NULL — investigate as scanner contract violation
(error)	Scanner error; check stderr; raise to Council

A false from Mode A or a non-zero critical_count from Mode B is operational signal, not a system failure — the gate is working when it reports gaps. The execution report MUST distinguish "scanner errored" from "scanner returned with findings".

8. Authorization workflow (normative)

1. Operator requests Trigger B2 and declares the mode (A or B).
2. Operator references this plan + the design §7.
3. GPT reviews:
   • For Mode A: confirms wrapper + row are installed, comment signature present, no concurrent governance lock, approves cfg/threshold.
   • For Mode B: separately approves the diagnostic intent and confirms the wrapper-bypass is acceptable for this invocation.
4. User issues scoped authorization that names the mode (for Mode B, the authorization must say "Mode B" explicitly).
5. Operator runs §4.1.2 (A) or §4.2.2 (B) — never both in the same session.
6. Operator uploads the mode-specific report (§6).
7. Operator returns final fields to Council.

9. What this trigger does NOT do

Not done	Why
Run dot-dot-health CLI	Current path is PG SELECT; CLI --only-check is future enhancement pending static-proof artifact.
Run any --dry-run flag	Out of scope for this plan.
Modify system_health_checks	Plan is read+invoke; no row mutation.
Modify birth_registry	Out of scope.
Combine Mode A and Mode B in one session	Forbidden by §4.3 — would double scanner invocation.
Treat Mode B as a substitute for Mode A	Mode B bypasses threshold semantics; it is diagnostic only.
Delete or update system_issues	Append-only audit.
Auto-retry on error	Operator must investigate stderr first.

10. Hard-boundary attestation (this plan, compile-time)

Boundary	Held
No SQL executed	YES
No wrapper invocation	YES
No scanner invocation	YES
No dot-dot-health run	YES
Plan executed	NO
Secrets disclosed	NONE

11. Plan fingerprint

Field	Mode A	Mode B
Path	PG_SELECT (wrapper)	PG_SELECT (direct scanner)
Primary statement	SELECT fn_birth_onboarding_full_scan_hc('{}'::jsonb)	SELECT fn_birth_onboarding_full_scan()
Class	INTENTIONAL_WRITE	INTENTIONAL_WRITE
Scanner invocations per session	1	1
Wrapper involved	YES	NO
Threshold semantics	applied (cfg.critical_threshold)	NOT applied
Default cfg / threshold	{} / 0	N/A
Envelope statements	4 (pre-count, wrapper, post-count, spot-check)	4 (pre-count, scanner, post-count, spot-check)
Authorization granularity	One invocation per authorization	One invocation per authorization, must name "Mode B"
KB report mandatory	YES	YES
Combinable with the other mode in same session	NO	NO

---

P3D Birth — B3-F1c-H On-demand Trigger B2 Execution Plan (Rev2 — Mode A/B Split Patch) | 2026-05-14 | COMPILE-ONLY