KB-4415 rev 2
P3D Birth B3-F1c-H Wrapper + Row SQL Artifact Compile Report (Rev2 — Patched)
13 min read Revision 2
p3dbirth-systemb3f1c-hcompile-reportsql-artifactcomment-mandatorymode-a-bpatched2026-05-14
P3D Birth — B3-F1c-H Wrapper + Row SQL Artifact Compile Report
Doc rev: 2 (patches integrated) Date: 2026-05-14 Mode: COMPILE-ONLY — no SQL run; read-only preflight only Author: Claude (Opus 4.7, 1M context) Patch motivation:
knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b3f1c-h-sql-artifacts-patch-required-before-execution-2026-05-14.mdParent GPT approval:knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b2-rev3-b3f1c-h-dual-trigger-patch-approved-2026-05-14.mdDesign source:knowledge/dev/laws/dieu44-trien-khai/design/p3d-birth-b2-contract-rev3-and-b3f1c-h-wrapper-integration-design.md(rev 2)
0. What changed in rev 2 (patches)
| Patch | Affected artifacts | Effect |
|---|---|---|
| Patch 1 — COMMENT mandatory for wrapper install | wrapper SQL (rev 2), rollback SQL (note added) | COMMENT ON FUNCTION now lives in the same transaction as CREATE FUNCTION. A post-create verification gate RAISEs if the comment signature is missing or wrong, rolling back the install. Closes the rollback contract (obj_description() LIKE 'B3-F1c-H wrapper%'). |
| Patch 2 — Trigger B2 single scanner invocation per session | Trigger B2 plan (rev 2) | Default envelope now invokes the scanner exactly once via the wrapper (Mode A). The direct scanner rollup is moved to a separate Mode B that requires explicit authorization naming "Mode B". Mode A + Mode B in one session is forbidden. |
| Other artifacts | system_health_checks row INSERT (unchanged) |
INSERT verifies wrapper signature only, not COMMENT. The wrapper-install responsibility for COMMENT is enforced inside the wrapper artifact. No change required. |
1. Executive summary
| Stream | Status |
|---|---|
| Wrapper SQL artifact compiled (rev 2 — COMMENT mandatory + verify gate) | PASS |
system_health_checks INSERT SQL compiled (unchanged) |
PASS |
| Rollback SQL compiled (note about guaranteed COMMENT added) | PASS |
| On-demand Trigger B2 execution plan compiled (rev 2 — Mode A / Mode B split) | PASS |
| Preflight (read-only) | PASS |
CREATE OR REPLACE used |
NO |
| Scanner invoked | NO |
| Wrapper invoked | NO |
| Trigger B2 default invokes scanner once | YES (Mode A) |
| Trigger B2 direct scanner rollup separated | YES (Mode B, requires separate authorization) |
| COMMENT mandatory for wrapper install | YES |
| Rollback comment dependency satisfied | YES (atomic install + verify gate) |
| Execution authorized | NO |
b3f1c_h_artifact_patch_status = PASS
2. Live preflight evidence (recorded 2026-05-14, read-only on VPS 38.242.240.89)
Transport: ssh root@38.242.240.89 "docker exec -i -e PGOPTIONS='-c default_transaction_read_only=on ...' postgres psql -U directus -d directus -tAXq -v ON_ERROR_STOP=1". Session transaction_read_only = on.
2.1 Function inventory
| Function | args | ret | Existence | def length |
|---|---|---|---|---|
public.fn_birth_onboarding_full_scan |
(empty) |
jsonb |
EXISTS | 17,438 bytes |
public.fn_birth_onboarding_full_scan_hc |
— | — | ABSENT | — |
wrapper_function_absent = true
wrapper_existing_conflict = false
scanner_invoked = false (only pg_get_function_arguments / pg_get_function_result / length(pg_get_functiondef(...)) queried)
2.2 system_health_checks schema (13 columns)
| column | type | nullable | default |
|---|---|---|---|
| code | text | NO | (none) |
| name | text | NO | (none) |
| jurisdiction | text | NO | (none) |
| check_kind | text | NO | 'detect_only'::text |
| executor_type | text | NO | (none) |
| executor_ref | text | NO | (none) |
| threshold_config | jsonb | NO | '{}'::jsonb |
| severity_on_fail | text | NO | (none) |
| auto_fix_action | text | YES | (none) |
| is_active | boolean | NO | true |
| order_index | integer | NO | (none) |
| description | text | YES | (none) |
| _dot_origin | text | NO | 'unknown'::text |
system_health_checks_schema_verified = true.
2.3 Row absence check
SELECT count(*) FROM public.system_health_checks WHERE code='DOT-BIRTH-ONBOARD-FULLSCAN-HC';
-- → row_count=0
health_check_row_absent = true
health_check_row_conflict = false
2.4 Live taxonomy values (existing-only)
| Column | DISTINCT values | Selected |
|---|---|---|
check_kind |
detect_and_fix, detect_only |
detect_only |
severity_on_fail |
critical, warn |
critical |
_dot_origin |
dieu22_fix25, dieu35_fix25, dieu36_fix25, dieu43_v1_2_seed, s178_fix28_h11_split |
dieu35_fix25 |
executor_type (current rows) |
builtin (16), sql (14) — no function row yet |
function (first such row) |
Selection rationale unchanged from rev 1. Debt B3-F1c-H-dot-origin-naming-debt recorded.
2.5 Jurisdiction verification
NRM-LAW-35-V5P2 exists in normative_registry, status=enacted, version=5.2, not retired.
jurisdiction_debt_recorded = true
2.6 order_index strategy
max(order_index) WHERE jurisdiction='NRM-LAW-35-V5P2' = 114 (rows=15). Computed next = 115, applied via aggregate at INSERT time (not hardcoded).
order_index_strategy = MAX_PLUS_ONE_WITHIN_JURISDICTION
2.7 Birth-related rows already present
matches = 0 — no overlap.
3. Artifacts compiled (paths and revs)
| # | Type | Path | Rev |
|---|---|---|---|
| 1 | Wrapper SQL | …/artifacts/p3d-birth-b3f1c-h-wrapper-function.sql.md |
2 (COMMENT mandatory + verify gate) |
| 2 | system_health_checks INSERT SQL |
…/artifacts/p3d-birth-b3f1c-h-system-health-check-row.sql.md |
1 (unchanged) |
| 3 | Rollback SQL | …/artifacts/p3d-birth-b3f1c-h-wrapper-row-rollback.sql.md |
2 (note added re: guaranteed COMMENT) |
| 4 | On-demand Trigger B2 plan | …/artifacts/p3d-birth-b3f1c-h-on-demand-trigger-b2-plan.md |
2 (Mode A / Mode B split) |
| 5 | This compile report | …/reports/p3d-birth-b3f1c-h-wrapper-row-sql-artifact-report.md |
2 |
wrapper_sql_compiled = true
insert_sql_compiled = true
rollback_sql_compiled = true
on_demand_trigger_b2_plan_compiled = true
4. Design + patch fidelity checks
| Requirement | Source | Honoured in rev 2 |
|---|---|---|
Wrapper signature (cfg jsonb DEFAULT '{}') RETURNS boolean |
design rev 2 §3.2 | YES |
CREATE FUNCTION only, no CREATE OR REPLACE |
GPT zero-trust | YES — wrapper artifact gate 1 |
Wrapper calls fn_birth_onboarding_full_scan() |
design rev 2 §3.3 | YES |
Wrapper reads critical_count and compares to critical_threshold (default 0) |
design rev 2 §3.4 | YES |
Wrapper returns false if scanner NULL |
design rev 2 §3.3 | YES |
Wrapper does NOT catch EXCEPTION WHEN OTHERS |
design rev 2 §3.6 | YES |
| Wrapper does NOT hardcode collection list | design rev 2 §3.6 | YES |
Wrapper does NOT write system_issues directly |
design rev 2 §3.6 | YES |
COMMENT ON FUNCTION mandatory in install |
GPT patch 1 | YES — wrapper artifact §4 (Gate 3) + §5 (contract) |
| Install is one atomic transaction (CREATE + COMMENT + verify) | GPT patch 1 | YES |
Rollback obj_description() LIKE 'B3-F1c-H wrapper%' contract reliably satisfied |
GPT patch 1 | YES — guaranteed by atomic install |
system_health_checks row code = DOT-BIRTH-ONBOARD-FULLSCAN-HC |
design rev 2 §4.1 | YES |
executor_type = function, executor_ref = fn_birth_onboarding_full_scan_hc |
design rev 2 §4.1 | YES |
threshold_config = {"critical_threshold": 0} |
design rev 2 §4.1 | YES |
is_active = true |
design rev 2 §4.1 | YES |
jurisdiction = NRM-LAW-35-V5P2 with debt note |
design rev 2 §4.3 | YES |
| Taxonomy values existing-only | spec rule | YES |
order_index MAX_PLUS_ONE_WITHIN_JURISDICTION |
spec rule | YES |
| Rollback drops wrapper only with signature gates | design rev 2 §rollback | YES |
Trigger B2 classifies INTENTIONAL_WRITE, requires KB report |
design rev 2 §7 | YES |
| Trigger B2 default invokes scanner exactly once | GPT patch 2 | YES — Mode A, wrapper artifact §4.1.2 |
| Direct scanner rollup is a separate Mode B with explicit authorization | GPT patch 2 | YES — Trigger B2 plan §4.2 |
| Mode A + Mode B forbidden in same session | GPT patch 2 | YES — Trigger B2 plan §4.3 |
5. Departures from design (recorded)
| Departure | Status | Reason |
|---|---|---|
_dot_origin = dieu35_fix25 instead of design's assumed dot-dot-health |
recorded as B3-F1c-H-dot-origin-naming-debt |
Live taxonomy did not include dot-dot-health. Adopted in-use sibling convention. |
| COMMENT now MANDATORY (was "optional, separate transaction" in rev 1) | resolved | GPT patch 1. Closes rollback contract gap. |
| Direct scanner rollup moved out of default Trigger B2 envelope (was inline-but-commented in rev 1) | resolved | GPT patch 2. Avoids any path to double scanner invocation. |
No other departures.
6. Anti-patterns avoided
| Anti-pattern | Avoided in |
|---|---|
Silent overwrite (CREATE OR REPLACE) |
Wrapper artifact gate 1 |
Hardcoded order_index |
INSERT artifact uses aggregate at execute time |
Invented taxonomy value (dot-dot-health as _dot_origin) |
Replaced with live dieu35_fix25 |
INSERT ... ON CONFLICT masking |
Explicit gate 1 in INSERT artifact |
| Partial rollback | Rollback artifact gates 1–7 + single transaction |
| Cascade drop | Rollback explicitly excludes CASCADE |
| Read-only assumption on Trigger B2 | Plan explicitly classifies INTENTIONAL_WRITE |
| COMMENT as cosmetic | Wrapper artifact rev 2: COMMENT is a rollback-contract artifact, enforced by Gate 3. |
| Splitting CREATE and COMMENT across uncoupled transactions | Wrapper artifact rev 2: same transaction, atomic. |
| Double scanner invocation in one B2 session | Trigger B2 plan rev 2: Mode A and Mode B forbidden in same session. |
| Direct scanner call masquerading as "companion capture" in default envelope | Trigger B2 plan rev 2: removed; only available under Mode B with explicit authorization. |
7. Boundary attestation (this patch session)
| Boundary | Held |
|---|---|
| No SQL execution | YES |
| No DDL execution | YES |
| No DML execution | YES |
| No wrapper creation | YES |
No system_health_checks INSERT |
YES |
No fn_birth_onboarding_full_scan invocation |
YES |
| No wrapper invocation | YES |
| No dot-dot-health run | YES |
| No cron change | YES |
| No file change (other than these 4 KB artifacts) | YES |
| No Phase 5C2 work | YES |
| No UI cutover | YES |
| Secrets disclosed | NONE |
8. Sequence to execute (informational; each step needs its own authorization)
- GPT review of patched artifacts (rev 2).
- Wrapper install — execute
…/p3d-birth-b3f1c-h-wrapper-function.sql.md(rev 2). Single atomic block: CREATE FUNCTION + COMMENT + verify gate. Install is incomplete (and rolled back) if COMMENT signature is not set. - Row install — execute
…/p3d-birth-b3f1c-h-system-health-check-row.sql.md(INSERT block). - Trigger B2 invocation #1 — per
…/p3d-birth-b3f1c-h-on-demand-trigger-b2-plan.md(rev 2). Choose Mode A (default) or Mode B (separate authorization). Each session invokes the scanner exactly once. - Cron observation #1 — diagnostic confirmation of Trigger A.
- B3-F1c-H complete.
Steps 4 and 5 may run in either order under the dual-trigger model.
9. Final fields
b3f1c_h_artifact_patch_status=PASS
comment_mandatory_for_wrapper_install=true
rollback_comment_dependency_satisfied=true
trigger_b2_double_scanner_invocation_removed=true
trigger_b2_default_invokes_scanner_once=true
trigger_b2_direct_scanner_rollup_separate_mode=true
create_or_replace_used=false
scanner_invoked=false
b3f1c_h_compile_status=PASS
wrapper_function_absent=true
wrapper_existing_conflict=false
wrapper_sql_compiled=true
system_health_checks_schema_verified=true
health_check_row_absent=true
health_check_row_conflict=false
check_kind_selected=detect_only
severity_on_fail_selected=critical
dot_origin_selected=dieu35_fix25
order_index_strategy=MAX_PLUS_ONE_WITHIN_JURISDICTION
preflight_max_order_index=114
computed_next_order_index=115
jurisdiction_debt_recorded=true
insert_sql_compiled=true
rollback_sql_compiled=true
on_demand_trigger_b2_plan_compiled=true
manual_trigger_mutation_mode=INTENTIONAL_WRITE
execution_allowed=false
phase5c2_resume_allowed=false
next_recommended_action=GPT_REVIEW_PATCHED_B3F1C_H_ARTIFACTS
P3D Birth — B3-F1c-H Wrapper + Row SQL Artifact Compile Report (Rev2 — Patches Integrated) | 2026-05-14 | COMPILE-ONLY