P3D Birth — B3-F1c-H system_health_checks Row INSERT SQL (Compile-Only)

Date: 2026-05-14 Mode: COMPILE-ONLY. NOT executed. No DML run. Author: Claude (Opus 4.7, 1M context) GPT approval: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b2-rev3-b3f1c-h-dual-trigger-patch-approved-2026-05-14.md Companion wrapper artifact: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-wrapper-function.sql.md Companion rollback: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-wrapper-row-rollback.sql.md

---

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

Aspect	Value	Source
system_health_checks schema (13 columns)	Verified — matches design	information_schema.columns
Row with code='DOT-BIRTH-ONBOARD-FULLSCAN-HC' exists?	NO (row_count=0)	SELECT count(*)
Live check_kind values	detect_and_fix, detect_only	DISTINCT
Live severity_on_fail values	critical, warn	DISTINCT
Live _dot_origin values	dieu22_fix25, dieu35_fix25, dieu36_fix25, dieu43_v1_2_seed, s178_fix28_h11_split	DISTINCT
Live executor_type values	builtin (16), sql (14); no existing function row	GROUP BY
Jurisdiction NRM-LAW-35-V5P2 in normative_registry	YES — status=enacted, version=5.2, not retired	SELECT
max(order_index) within jurisdiction NRM-LAW-35-V5P2	114 (rows=15)	aggregate
Computed order_index for this row	115 (max+1)	strategy: MAX_PLUS_ONE_WITHIN_JURISDICTION

2. Taxonomy selections (existing values only — no invention)

Column	Selected value	Rationale
check_kind	detect_only	Wrapper has no auto-fix; matches detect_only semantics. Live value confirmed.
severity_on_fail	critical	A non-zero critical_count from the canonical scanner is by definition critical. Live value confirmed.
_dot_origin	dieu35_fix25	Live convention for jurisdiction NRM-LAW-35-V5P2: ALL 15 sibling rows use this origin. Design rev2 had assumed dot-dot-health; live data shows that token is not in use. Adopting the in-use sibling convention rather than inventing a new value. DEBT NOTE: Future cleanup may want a dieu44_b3f1c_h token; recorded as B3-F1c-H-dot-origin-naming-debt.
executor_type	function	First function row — design-locked.
executor_ref	fn_birth_onboarding_full_scan_hc	Wrapper (public schema implied by dispatcher's pg_proc lookup).
threshold_config	{"critical_threshold": 0}	Strict — design-locked.
auto_fix_action	NULL	No safe auto-fix; design-locked.
is_active	true	Active for next dispatch — design-locked.
jurisdiction	NRM-LAW-35-V5P2	Required by dot-dot-health hardcode. Debt B3-F1c-jurisdiction-decoupling-debt accepted in design. Live in normative_registry.
order_index	115 (computed: max(order_index)+1 within jurisdiction at INSERT time)	Strategy: MAX_PLUS_ONE_WITHIN_JURISDICTION. Recomputed at execute time via SELECT, NOT hardcoded; preflight value 115 recorded for reviewer.
code	DOT-BIRTH-ONBOARD-FULLSCAN-HC	Design-locked. UNIQUE check via gate.
name	Birth/Onboarding Full Scan — Critical Gaps	Human-readable; design-locked.
description	(see SQL body)	Self-contained with INTENTIONAL_WRITE notice.

3. Authorization

Compile-only. Execution requires:

1. Wrapper artifact installed successfully first (function public.fn_birth_onboarding_full_scan_hc(jsonb) RETURNS boolean must exist).
2. GPT review of this SQL.
3. Explicit user authorization scoped to one execution.
4. Pre-execution preflight re-run.

4. Hard-boundary attestation

Boundary	Held
No DML executed	YES
No INSERT run	YES
Wrapper not invoked	YES
Scanner not invoked	YES
Taxonomy values invented	NONE — all selected from live DISTINCT sets
order_index hardcoded	NO — computed at execute time
Secrets disclosed	NONE

5. The SQL — single transaction with preflight gates

-- =============================================================================
-- B3-F1c-H system_health_checks row INSERT — compile artifact (NOT executed).
-- Authorization required before running this block.
-- =============================================================================
BEGIN;

-- -----------------------------------------------------------------------------
-- Gate 1: row with this code must NOT already exist (UNIQUE protection).
-- -----------------------------------------------------------------------------
DO $gate1$
DECLARE
  v_count integer;
BEGIN
  SELECT count(*) INTO v_count
  FROM public.system_health_checks
  WHERE code = 'DOT-BIRTH-ONBOARD-FULLSCAN-HC';

  IF v_count > 0 THEN
    RAISE EXCEPTION
      'B3-F1c-H row INSERT aborted: row with code=DOT-BIRTH-ONBOARD-FULLSCAN-HC '
      'already present (count=%). Re-review with explicit replacement decision.', v_count;
  END IF;
END$gate1$;

-- -----------------------------------------------------------------------------
-- Gate 2: wrapper function must exist with (jsonb) RETURNS boolean.
-- -----------------------------------------------------------------------------
DO $gate2$
DECLARE
  v_func_args text;
  v_func_ret  text;
BEGIN
  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 IS NULL THEN
    RAISE EXCEPTION
      'B3-F1c-H row INSERT aborted: wrapper public.fn_birth_onboarding_full_scan_hc(*) not found. '
      'Install the wrapper artifact first.';
  END IF;

  -- Accept any signature whose args contain jsonb and return is boolean.
  IF v_func_args NOT LIKE '%jsonb%' OR v_func_ret <> 'boolean' THEN
    RAISE EXCEPTION
      'B3-F1c-H row INSERT aborted: wrapper signature unexpected. '
      'Got args=[%], ret=[%]. Expected jsonb arg and boolean return.',
      v_func_args, v_func_ret;
  END IF;
END$gate2$;

-- -----------------------------------------------------------------------------
-- Gate 3: jurisdiction must be live in normative_registry.
-- -----------------------------------------------------------------------------
DO $gate3$
DECLARE
  v_exists boolean;
BEGIN
  SELECT EXISTS (
    SELECT 1
    FROM public.normative_registry
    WHERE code = 'NRM-LAW-35-V5P2'
      AND retired_at IS NULL
  ) INTO v_exists;

  IF NOT v_exists THEN
    RAISE EXCEPTION
      'B3-F1c-H row INSERT aborted: jurisdiction NRM-LAW-35-V5P2 not live in '
      'normative_registry (missing or retired).';
  END IF;
END$gate3$;

-- -----------------------------------------------------------------------------
-- INSERT — order_index computed at execute time as max+1 within jurisdiction.
-- -----------------------------------------------------------------------------
INSERT INTO public.system_health_checks (
  code,
  name,
  jurisdiction,
  check_kind,
  executor_type,
  executor_ref,
  threshold_config,
  severity_on_fail,
  auto_fix_action,
  is_active,
  order_index,
  description,
  _dot_origin
)
SELECT
  'DOT-BIRTH-ONBOARD-FULLSCAN-HC',
  'Birth/Onboarding Full Scan — Critical Gaps',
  'NRM-LAW-35-V5P2',
  'detect_only',
  'function',
  'fn_birth_onboarding_full_scan_hc',
  '{"critical_threshold": 0}'::jsonb,
  'critical',
  NULL,
  true,
  COALESCE(max(s.order_index), 0) + 1,
  'Wraps public.fn_birth_onboarding_full_scan(); fails when critical_count exceeds '
  || 'threshold_config.critical_threshold (default 0). Birth/onboarding coverage gate; '
  || 'dispatched by dot-dot-health under NRM-LAW-35-V5P2. INTENTIONAL_WRITE on invocation '
  || '(scanner writes system_issues).',
  'dieu35_fix25'
FROM public.system_health_checks s
WHERE s.jurisdiction = 'NRM-LAW-35-V5P2';

-- -----------------------------------------------------------------------------
-- Gate 4: post-INSERT integrity — exactly one row inserted, fields match.
-- -----------------------------------------------------------------------------
DO $gate4$
DECLARE
  v_row record;
  v_inserted_count int;
BEGIN
  SELECT count(*) INTO v_inserted_count
  FROM public.system_health_checks
  WHERE code = 'DOT-BIRTH-ONBOARD-FULLSCAN-HC';

  IF v_inserted_count <> 1 THEN
    RAISE EXCEPTION
      'B3-F1c-H row INSERT inconsistency: expected 1 row with code=DOT-BIRTH-ONBOARD-FULLSCAN-HC, found %.',
      v_inserted_count;
  END IF;

  SELECT * INTO v_row
  FROM public.system_health_checks
  WHERE code = 'DOT-BIRTH-ONBOARD-FULLSCAN-HC';

  IF v_row.executor_type <> 'function'
     OR v_row.executor_ref  <> 'fn_birth_onboarding_full_scan_hc'
     OR v_row.jurisdiction  <> 'NRM-LAW-35-V5P2'
     OR v_row.check_kind    <> 'detect_only'
     OR v_row.severity_on_fail <> 'critical'
     OR v_row.is_active     <> true
     OR v_row._dot_origin   <> 'dieu35_fix25'
     OR v_row.threshold_config <> '{"critical_threshold": 0}'::jsonb
  THEN
    RAISE EXCEPTION
      'B3-F1c-H row INSERT integrity check failed. Row state: executor_type=%, '
      'executor_ref=%, jurisdiction=%, check_kind=%, severity_on_fail=%, '
      'is_active=%, _dot_origin=%, threshold_config=%.',
      v_row.executor_type, v_row.executor_ref, v_row.jurisdiction,
      v_row.check_kind, v_row.severity_on_fail, v_row.is_active,
      v_row._dot_origin, v_row.threshold_config;
  END IF;

  -- order_index must be > existing siblings (i.e., we won the max+1).
  IF v_row.order_index <= 114 THEN
    RAISE EXCEPTION
      'B3-F1c-H row INSERT integrity check failed: order_index=% is not greater than '
      'preflight max=114. Concurrent insert may have collided. Investigate.',
      v_row.order_index;
  END IF;
END$gate4$;

COMMIT;

6. Expected echo (when executed)

BEGIN
DO        -- gate 1
DO        -- gate 2
DO        -- gate 3
INSERT 0 1
DO        -- gate 4
COMMIT

Any deviation must be captured in the execution report.

7. Anti-patterns avoided

Anti-pattern	Why avoided
Hardcoded order_index = 115	Would fail under concurrent INSERTs; live recompute via aggregate handles it. Preflight value 115 is documented above for reviewer, not in SQL.
Inventing a new _dot_origin like dot-dot-health	Live taxonomy does not include that token; the design's assumption did not match live data. Adopting dieu35_fix25 (in-use for all sibling rows in this jurisdiction) is conservative.
INSERT ... ON CONFLICT DO ...	Would silently overwrite or hide errors. Gate 1 makes the absence explicit.
Hardcoding the check_kind outside the live DISTINCT set	Both detect_only and detect_and_fix exist; detect_only is chosen because no auto_fix_action is set.
Skipping post-INSERT integrity check	Gate 4 verifies the row materialized with the expected fields before committing.
Inserting before wrapper exists	Gate 2 enforces wrapper presence.

8. Compile-time fingerprint

Field	Value
Target table	public.system_health_checks
Row code	DOT-BIRTH-ONBOARD-FULLSCAN-HC
Row jurisdiction	NRM-LAW-35-V5P2
Row executor_type	function (first such row in table)
Row executor_ref	fn_birth_onboarding_full_scan_hc
Row check_kind	detect_only
Row severity_on_fail	critical
Row _dot_origin	dieu35_fix25 (live convention; design assumption corrected)
Row threshold_config	{"critical_threshold": 0}
Row is_active	true
Row order_index strategy	MAX_PLUS_ONE_WITHIN_JURISDICTION (preflight: 115)
Gates	4 (pre: row-absent, wrapper-exists, jurisdiction-live; post: integrity)

---

P3D Birth — B3-F1c-H system_health_checks Row INSERT SQL Artifact | 2026-05-14 | COMPILE-ONLY