P3D — B3-F1c-b Full-Scan Function-Only Execution Prompt (DRAFT)

Date: 2026-05-13 Author: Opus (draft). Requires GPT review before Agent dispatch. Mode: EXECUTE reviewed CREATE FUNCTION only. No scheduler. No smoke test. Self-contained. Agent reads this prompt + 2 KB artifacts. Nothing else.

Mission

Install public.fn_birth_onboarding_full_scan() into PostgreSQL. Function-only. No scheduler binding, no Directus Flow, no Nuxt endpoint, no smoke test.

Hard boundaries

ALLOWED:
  CREATE FUNCTION public.fn_birth_onboarding_full_scan() — from reviewed artifact only

FORBIDDEN:
  CREATE OR REPLACE
  CREATE EXTENSION
  COMMENT ON
  Directus Flow creation (INSERT INTO directus_flows)
  Directus Operation creation (INSERT INTO directus_operations)
  Nuxt endpoint creation
  Scheduler binding / cadence config
  DML smoke test (SELECT fn_birth_onboarding_full_scan())
  INSERT INTO system_issues
  INSERT INTO system_health_checks
  DROP of any existing function/trigger/policy
  ALTER of any existing object
  Any DDL/DML beyond the single CREATE FUNCTION

Phase 0 — Environment

cd /opt/incomex
source .env

Resolve database connection. All SQL via:

docker exec postgres psql -U directus -d directus

Phase 1 — Read reviewed artifacts from KB

Read these 2 artifacts. They are the ONLY source of SQL:

1. knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1c-full-scan-function.sql.md
2. knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1c-full-scan-rollback.sql.md

Extract the SQL code block from artifact 1 (the CREATE FUNCTION statement). Extract the rollback SQL from artifact 2 (the DROP FUNCTION IF EXISTS + guard block).

Gate 1: Verify function artifact content:

Contains CREATE FUNCTION public.fn_birth_onboarding_full_scan() — YES required
Contains CREATE OR REPLACE — MUST be NO
Contains COMMENT ON — MUST be NO
Contains CREATE EXTENSION — MUST be NO
Contains INSERT INTO directus_flows or directus_operations — MUST be NO
Contains cron.schedule — MUST be NO

If ANY gate fails → STOP. Report artifact_verification=FAIL. Do not execute.

Phase 2 — Preflight checks (read-only SQL)

All in one psql session. No mutation.

2a. Conflict check

SELECT count(*) AS conflict_count
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
  AND p.proname = 'fn_birth_onboarding_full_scan';

Gate 2: conflict_count MUST be 0. If >0 → STOP. Function already exists.

2b. Dependency checks

-- Helper function
SELECT count(*) AS helper_count
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
  AND p.proname = 'fn_b3f1_log_collection_onboarding_gap'
  AND p.pronargs = 4;

-- Gate function
SELECT count(*) AS gate_count
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
  AND p.proname = 'fn_collection_onboarding_soft_gate'
  AND p.pronargs = 0
  AND p.prorettype = 'pg_catalog.trigger'::regtype;

-- Policy key (count + parse test)
SELECT count(*) AS policy_key_count
FROM public.dot_config
WHERE key = 'policy.birth_trigger.accepted_sibling_scope';

-- Policy value parse test (only if count=1). Use a guarded parse; malformed JSON must produce dependency_check=FAIL and STOP, not a half-executed install.
DO $$
DECLARE
  v_raw text;
  v_json jsonb;
BEGIN
  SELECT value INTO v_raw
  FROM public.dot_config
  WHERE key = 'policy.birth_trigger.accepted_sibling_scope';

  IF v_raw IS NULL OR length(trim(v_raw)) = 0 THEN
    RAISE EXCEPTION 'POLICY_EMPTY';
  END IF;

  BEGIN
    v_json := v_raw::jsonb;
  EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 'POLICY_MALFORMED_JSON: %', SQLERRM;
  END;

  IF v_json -> 'entries' IS NULL OR jsonb_typeof(v_json -> 'entries') <> 'array' THEN
    RAISE EXCEPTION 'POLICY_ENTRIES_NOT_ARRAY';
  END IF;
END$$;

-- Contract function
SELECT count(*) AS contract_count
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
  AND p.proname = 'fn_birth_registry_auto'
  AND p.pronargs = 0
  AND p.prorettype = 'pg_catalog.trigger'::regtype;

Gate 3: ALL of the following MUST be true:

helper_count = 1
gate_count = 1
policy_key_count = 1
policy_not_empty = true
policy_has_entries = true
policy_entries_is_array = true
contract_count = 1

If ANY fails → STOP. Report which dependency is missing. Do not execute.

Note: These preflight checks are defense-in-depth. The function itself handles dependency failures gracefully at runtime (returns status='dependency_fail'). But we want the environment healthy at install time.

Phase 3 — Execute CREATE FUNCTION

Single transaction with advisory lock.

BEGIN;

-- Advisory lock to prevent concurrent DDL
SELECT pg_try_advisory_xact_lock(hashtext('p3d_birth_b3f1c_b_full_scan_function_execution')) AS lock_acquired;
-- If lock_acquired = false → ROLLBACK and STOP. Do not use numeric magic advisory-lock IDs.

-- Execute the CREATE FUNCTION SQL extracted from artifact 1.
-- Paste the exact SQL from the artifact. Do NOT modify it.
-- Do NOT add CREATE OR REPLACE.
-- Do NOT add COMMENT ON.

<< PASTE EXACT CREATE FUNCTION SQL FROM ARTIFACT 1 HERE >>

-- In-transaction verification
SELECT
  p.proname,
  p.pronargs,
  pg_catalog.format_type(p.prorettype, NULL) AS return_type,
  l.lanname AS language,
  p.prosecdef AS security_definer,
  array_to_string(p.proconfig, ', ') AS config
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE n.nspname = 'public'
  AND p.proname = 'fn_birth_onboarding_full_scan';

Gate 4 (in-transaction): Verify:

proname = fn_birth_onboarding_full_scan
pronargs = 0
return_type = jsonb
language = plpgsql
security_definer = true
config contains search_path=public, pg_catalog

If ANY check fails → ROLLBACK; and STOP.

If ALL pass → COMMIT;

Phase 4 — Post-commit verification

Repeat the same verification query outside the transaction:

SELECT
  p.proname,
  p.pronargs,
  pg_catalog.format_type(p.prorettype, NULL) AS return_type,
  l.lanname AS language,
  p.prosecdef AS security_definer,
  array_to_string(p.proconfig, ', ') AS config
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE n.nspname = 'public'
  AND p.proname = 'fn_birth_onboarding_full_scan';

Gate 5: Same checks as Gate 4. If fails → function may not have committed. Report as PARTIAL.

Phase 5 — Rollback documentation

Log the reviewed rollback SQL for human reference (do NOT execute it):

ROLLBACK SQL (from artifact 2, for manual use if needed):
BEGIN;
DROP FUNCTION IF EXISTS public.fn_birth_onboarding_full_scan();
<< negative assertion guards from artifact 2 >>
COMMIT;

Phase 6 — Report

Write execution report to KB:

Path: knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3f1c-b-full-scan-function-execution-report.md

Report must include:

All gate results (Gate 1–5)
Preflight dependency values
In-transaction verification values
Post-commit verification values
Rollback SQL reference
Final status

Phase 7 — No git commit

Do not run git add, git commit, or git push in this execution. B3-F1c-b is a PG DDL execution plus KB report only. Repository commits, if needed for non-PG artifacts, require a separate explicit instruction.

Final response fields

b3f1c_b_execution_status=PASS|PARTIAL|FAIL
artifact_verification=PASS|FAIL
conflict_check=PASS|FAIL
dependency_check=PASS|FAIL
advisory_lock_acquired=true|false
create_function_executed=true|false
in_transaction_verification=PASS|FAIL
post_commit_verification=PASS|FAIL
report_uploaded=true|false
git_commit_performed=false
rollback_sql_documented=true|false
dml_smoke_test_performed=false
directus_flow_created=false
scheduler_bound=false

What this prompt does NOT do

Does NOT run SELECT fn_birth_onboarding_full_scan() — that is a DML smoke test (writes to system_issues) and requires separate approval.
Does NOT create Directus Flow or Operation.
Does NOT create Nuxt endpoint.
Does NOT bind any scheduler or cadence.
Does NOT write to system_issues or system_health_checks.
Does NOT drop or alter any existing object.
Does NOT touch B3-F1b helper, gate, trigger, or policy.

B3-F1c-b Function-Only Execution Prompt | DRAFT | 2026-05-13