Incomex AI Portal
KB-6B20 rev 2

P3D Birth B3-F1c-H Wrapper Function SQL (Compile-Only, Rev2 — COMMENT-Mandatory Patch)

14 min read Revision 2
p3dbirth-systemb3f1c-hwrappersql-artifactcompile-onlycomment-mandatorypatched2026-05-14

P3D Birth — B3-F1c-H Wrapper Function SQL (Compile-Only)

Doc rev: 2 (COMMENT-mandatory patch) Date: 2026-05-14 Mode: COMPILE-ONLY. NOT executed. No DDL 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 — COMMENT ON FUNCTION is mandatory for wrapper install, because the rollback artifact gates on obj_description(...) LIKE 'B3-F1c-H wrapper%'. Install is incomplete without it. 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: knowledge/dev/laws/dieu44-trien-khai/design/p3d-birth-b2-contract-rev3-and-b3f1c-h-wrapper-integration-design.md (rev 2) Companion row INSERT: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-system-health-check-row.sql.md Companion rollback: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-wrapper-row-rollback.sql.md

0. What changed in rev 2

Item rev 1 rev 2
COMMENT ON FUNCTION Optional, in a separate transaction Mandatory, in the same transaction as CREATE FUNCTION
Post-create verification None NEW gate verifies function exists AND comment signature was applied
Atomicity guarantee Two transactions, possible partial state One transaction; if COMMENT fails the CREATE is rolled back
Rollback contract reliability Depended on operator running both blocks Now guaranteed by atomic install

The rollback artifact (…/p3d-birth-b3f1c-h-wrapper-row-rollback.sql.md) requires the comment signature 'B3-F1c-H wrapper%'. Making the comment mandatory closes that contract.

1. Preflight evidence (recorded 2026-05-14, read-only)

Aspect Value Source
public.fn_birth_onboarding_full_scan_hc(*) exists? NO (wrapper_function_absent=true) pg_proc lookup
public.fn_birth_onboarding_full_scan() exists? YES — args=[], ret=jsonb, def 17,438 bytes pg_proc lookup + pg_get_functiondef length
Scanner invoked during preflight? NO only metadata queried
Existing wrapper conflict? NO none exists
Action CREATE FUNCTION + COMMENT, same transaction (no CREATE OR REPLACE) per GPT zero-trust + COMMENT-mandatory directives

create_or_replace_used = false

2. Authorization

This artifact is compile-only. Execution is not authorized by this artifact alone. A separate execution session requires:

  1. GPT review of this SQL (rev 2).
  2. Explicit user authorization scoped to one execution of the verbatim block.
  3. Pre-execution preflight (re-run the same checks on the live database).

Install is one atomic block. Do not run CREATE FUNCTION without COMMENT ON FUNCTION — the install will RAISE EXCEPTION and roll back if the comment signature is missing or wrong.

3. Hard-boundary attestation

Boundary Held
No DDL executed YES
No CREATE FUNCTION run YES
No CREATE OR REPLACE anywhere in artifact YES
Scanner not invoked YES
File change outside Agent Data artifacts NO
Secrets disclosed NONE

4. The SQL — single atomic transaction (CREATE + COMMENT + verify)

-- =============================================================================
-- B3-F1c-H wrapper function — compile artifact (compile-only; NOT executed).
-- Authorization required before running this block.
-- SINGLE ATOMIC TRANSACTION: CREATE FUNCTION + COMMENT + verification gate.
-- Install is incomplete (and rolled back) if the COMMENT signature is not set.
-- =============================================================================
BEGIN;

-- -----------------------------------------------------------------------------
-- Gate 1: wrapper must NOT already exist (CREATE FUNCTION only, no OR REPLACE).
-- -----------------------------------------------------------------------------
DO $gate1$
DECLARE
  v_exists boolean;
BEGIN
  SELECT EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'public'
      AND p.proname = 'fn_birth_onboarding_full_scan_hc'
  ) INTO v_exists;
  IF v_exists THEN
    RAISE EXCEPTION
      'B3-F1c-H wrapper compile aborted: public.fn_birth_onboarding_full_scan_hc(*) already exists. '
      'CREATE FUNCTION only — CREATE OR REPLACE is NOT authorized by this artifact. '
      'Re-review with explicit replacement decision.';
  END IF;
END$gate1$;

-- -----------------------------------------------------------------------------
-- Gate 2: scanner must exist with signature `() RETURNS jsonb`.
-- -----------------------------------------------------------------------------
DO $gate2$
DECLARE
  v_args text;
  v_ret  text;
BEGIN
  SELECT pg_get_function_arguments(p.oid), pg_get_function_result(p.oid)
    INTO v_args, v_ret
  FROM pg_proc p
  JOIN pg_namespace n ON p.pronamespace = n.oid
  WHERE n.nspname = 'public'
    AND p.proname = 'fn_birth_onboarding_full_scan';

  IF v_args IS NULL THEN
    RAISE EXCEPTION
      'B3-F1c-H wrapper compile aborted: public.fn_birth_onboarding_full_scan() not found.';
  END IF;

  IF v_args <> '' OR v_ret <> 'jsonb' THEN
    RAISE EXCEPTION
      'B3-F1c-H wrapper compile aborted: scanner signature unexpected. '
      'Got args=[%], ret=[%]. Expected args=[], ret=jsonb.',
      v_args, v_ret;
  END IF;
END$gate2$;

-- -----------------------------------------------------------------------------
-- CREATE FUNCTION — thin adapter for dot-dot-health.dispatch_function.
-- -----------------------------------------------------------------------------
CREATE FUNCTION public.fn_birth_onboarding_full_scan_hc(cfg jsonb DEFAULT '{}'::jsonb)
RETURNS boolean
LANGUAGE plpgsql
SECURITY INVOKER
AS $wrapper$
DECLARE
  v_result          jsonb;
  v_critical_count  bigint;
  v_threshold       bigint;
BEGIN
  -- Delegate to canonical scanner. Do NOT duplicate any collection or
  -- detection logic here; all governance lives in fn_birth_onboarding_full_scan.
  v_result := public.fn_birth_onboarding_full_scan();

  -- Safe interpretation: NULL output means scanner contract violated -> fail.
  IF v_result IS NULL THEN
    RETURN false;
  END IF;

  v_critical_count := COALESCE((v_result ->> 'critical_count')::bigint, 0);
  v_threshold      := COALESCE((cfg     ->> 'critical_threshold')::bigint, 0);

  -- Pass iff critical_count <= threshold. No EXCEPTION WHEN OTHERS:
  -- scanner errors must propagate so dot-dot-health logs them as fail.
  RETURN v_critical_count <= v_threshold;
END;
$wrapper$;

-- -----------------------------------------------------------------------------
-- COMMENT ON FUNCTION — MANDATORY. Rollback artifact gates on this signature.
-- -----------------------------------------------------------------------------
COMMENT ON FUNCTION public.fn_birth_onboarding_full_scan_hc(jsonb) IS
'B3-F1c-H wrapper. Calls fn_birth_onboarding_full_scan() and folds critical_count <= cfg.critical_threshold (default 0) to a boolean for dot-dot-health.dispatch_function. INTENTIONAL_WRITE: inherits scanner''s system_issues writes. Install: 2026-05-14, jurisdiction NRM-LAW-35-V5P2 (debt: jurisdiction-decoupling). NO CREATE OR REPLACE.';

-- -----------------------------------------------------------------------------
-- Gate 3 (post-create verification): function exists AND comment signature set.
-- This gate makes the COMMENT mandatory: if it is missing or wrong, the entire
-- transaction (including CREATE FUNCTION) is rolled back.
-- -----------------------------------------------------------------------------
DO $gate3$
DECLARE
  v_func_exists boolean;
  v_func_args   text;
  v_func_ret    text;
  v_comment     text;
BEGIN
  -- Function must be present with expected signature.
  SELECT EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'public'
      AND p.proname = 'fn_birth_onboarding_full_scan_hc'
  ) INTO v_func_exists;

  IF NOT v_func_exists THEN
    RAISE EXCEPTION
      'B3-F1c-H wrapper install verification failed: function not present post-CREATE. '
      'Transaction will be rolled back.';
  END IF;

  SELECT pg_get_function_arguments(p.oid), pg_get_function_result(p.oid)
    INTO v_func_args, v_func_ret
  FROM pg_proc p
  JOIN pg_namespace n ON p.pronamespace = n.oid
  WHERE n.nspname = 'public'
    AND p.proname = 'fn_birth_onboarding_full_scan_hc';

  IF v_func_args NOT LIKE '%jsonb%' OR v_func_ret <> 'boolean' THEN
    RAISE EXCEPTION
      'B3-F1c-H wrapper install verification failed: signature mismatch. '
      'Got args=[%], ret=[%]. Expected jsonb arg and boolean return.',
      v_func_args, v_func_ret;
  END IF;

  -- Comment must be present AND start with the rollback-gate signature.
  SELECT obj_description(p.oid, 'pg_proc') INTO v_comment
  FROM pg_proc p
  JOIN pg_namespace n ON p.pronamespace = n.oid
  WHERE n.nspname = 'public'
    AND p.proname = 'fn_birth_onboarding_full_scan_hc'
  LIMIT 1;

  IF v_comment IS NULL THEN
    RAISE EXCEPTION
      'B3-F1c-H wrapper install verification failed: COMMENT ON FUNCTION is NULL. '
      'Rollback artifact requires obj_description() LIKE ''B3-F1c-H wrapper%%''. '
      'Transaction will be rolled back. Re-run with COMMENT statement included.';
  END IF;

  IF v_comment NOT LIKE 'B3-F1c-H wrapper%' THEN
    RAISE EXCEPTION
      'B3-F1c-H wrapper install verification failed: COMMENT signature does not start with '
      '''B3-F1c-H wrapper''. Got: [%]. Transaction will be rolled back.', v_comment;
  END IF;
END$gate3$;

COMMIT;

5. COMMENT signature contract (NORMATIVE)

The rollback artifact identifies wrapper rows installed by this artifact via:

obj_description(<oid>, 'pg_proc') LIKE 'B3-F1c-H wrapper%'

This means:

  1. Wrapper install is incomplete without a matching COMMENT. The transaction in §4 enforces this via Gate 3.
  2. The COMMENT MUST start with the exact literal B3-F1c-H wrapper. Operators MUST NOT change this prefix.
  3. The remainder of the COMMENT may evolve in future revisions, but the prefix is a contract with the rollback artifact.
  4. If a future revision changes the prefix, a new rollback artifact MUST be issued in lockstep.

6. Anti-patterns avoided

Anti-pattern Why avoided
CREATE OR REPLACE FUNCTION Would silently overwrite an existing function with no audit trail. GPT zero-trust directive forbids it.
EXCEPTION WHEN OTHERS in wrapper Would mask scanner errors; dot-dot-health relies on dispatch error propagation for visibility.
Hardcoding collection list Wrapper is collection-agnostic; collection enumeration lives in the scanner.
Reading multiple counters (warning_count, etc.) Severity policy belongs in the scanner. Wrapper reads critical_count only.
Writing system_issues directly Wrapper is a pure adapter; scanner owns writes.
Function in non-public schema dot-dot-health.dispatch_function looks up pg_proc.proname in nspname='public'.
Strict (jsonb) without default DEFAULT '{}' makes ad-hoc PG-side calls ergonomic without breaking the executor contract.
Splitting CREATE and COMMENT across uncoupled transactions Could leave the wrapper installed but unsignable by rollback. Rev 2 fixes this by atomic install + verify gate.
Treating COMMENT as cosmetic Comment is contractual — the rollback gates on it.

7. Expected echo (when executed)

BEGIN
DO        -- gate 1 (no exception)
DO        -- gate 2 (no exception)
CREATE FUNCTION
COMMENT
DO        -- gate 3 (post-create verification, no exception)
COMMIT

Any deviation (warning, notice, exception) must be captured in the execution report; do not assume idempotency.

8. Rollback availability

Rollback is documented separately at knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-wrapper-row-rollback.sql.md. Rollback drops this function only if the row installed by the companion INSERT was deleted successfully AND the function comment matches the install signature.

With rev 2 of this artifact, the comment signature is guaranteed by the install — a successful install leaves the wrapper with a comment starting B3-F1c-H wrapper, and a failed install leaves no wrapper at all (atomic rollback).

9. Compile-time fingerprint

Field Value
Wrapper schema public
Wrapper name fn_birth_onboarding_full_scan_hc
Wrapper args cfg jsonb DEFAULT '{}'::jsonb
Wrapper return boolean
Language plpgsql
Security INVOKER
Calls public.fn_birth_onboarding_full_scan()
JSONB key read critical_count (from result), critical_threshold (from cfg)
Strictness NULL result → false; missing keys → 0
Error policy propagate (no EXCEPTION WHEN OTHERS)
Side effects Inherits scanner writes (system_issues). Wrapper itself does not write.
COMMENT mandatory YES — enforced by Gate 3
COMMENT signature prefix B3-F1c-H wrapper
Install atomicity Single transaction, all-or-nothing
Gates 3 (pre: wrapper-absent, scanner-signature; post: function+comment verify)

P3D Birth — B3-F1c-H Wrapper Function SQL Artifact (Rev2 — COMMENT-Mandatory Patch) | 2026-05-14 | COMPILE-ONLY