KB-31C2

22-P1 — IU Native Create Helper Functions Report

5 min read Revision 1

pack-22p1helper-functionsreportcomplete

22-P1 — IU Native Create Helper Functions Report

Date: 2026-05-06 | Status: COMMITTED — all tests pass Controlling prompt: 22-p1-iu-native-create-helper-functions-prompt.md (rev8) DB: VPS PostgreSQL (postgres container, db=directus)

§1. Summary

5 helper functions installed inside a single transaction. Preflight passed all column / trigger / FK / extension checks. All §6 tests pass. REVOKE PUBLIC verified. Row counts unchanged. COMMIT.

Function	nargs	volatile	secdef
`fn_content_hash(text)`	1	i	f
`fn_iu_resolve_default(text,text,text)`	3	s	f
`fn_iu_classify_existing(text)`	1	s	f
`fn_iu_create_preflight()`	0	s	f
`fn_iu_verify_invariants(text)`	1	s	f

§2. Baseline + Post Counts

tbl	baseline	post
information_unit	1	1
unit_version	1	1
birth_iu	1	1
birth_total	80258	80258

No drift.

§3. Preflight + Samples

Name conflict: 0 rows (no pre-existing helpers).
Extensions: digest(text,text) → t, gen_random_uuid() → t.
Deferrable FK IU→UV: fk_iu_version_anchor (deferrable=t, deferred=t).
Sample unit_kind: design_doc_section (sole vocab entry → auto_single for null-explicit case).
Sample IU canonical_address: pilot.iu0.test-001.

§4. Hash Inspect

Existing fn_sbx_compute_content_hash (4 args, IMMUTABLE) — not reused per prompt.

§5. Install Transaction

BEGIN
CREATE FUNCTION × 5
REVOKE × 5
... tests ...
COMMIT

§6. Test Results

Test	Result	Status
hash_test	`63efb315ed71cc7e5a1fc202434bb3aec2091e7838707e148a017faebb7464fe` (64-char hex)	✅
resolve_null	`{status:auto_single, value:design_doc_section}`	✅ (sole vocab → auto_single, acceptable)
resolve_valid	`{status:explicit, value:design_doc_section}`	✅
resolve_invalid	`{status:invalid, message:"Not in vocab. Available: design_doc_section"}`	✅
resolve_null_config	`{status:invalid_input, message:"p_config_key NULL/empty"}`	✅
resolve_null_prefix	`{status:invalid_input, message:"p_vocab_prefix NULL/empty"}`	✅
classify_existing	`{status:exists_complete, iu_id:3ffbbaa5-…, issues:[]}`	✅
classify_absent	`{status:not_found, canonical_address:"__nonexistent__"}`	✅
classify_null	`{status:invalid_input}`	✅
classify_empty	`{status:invalid_input}`	✅
preflight	`{status:pass, fk_name:fk_iu_version_anchor, fk_initially_deferred:true}`	✅
verify_existing	`{all_pass:true, i1..i5 all true}`	✅
verify_absent	`{status:not_found}`	✅
verify_null	`{status:invalid_input}`	✅

No SKIPPED, no FAIL, no exceptions.

§7. REVOKE Verification

information_schema.routine_privileges after install (only owner directus retains EXECUTE; no PUBLIC grantee row):

routine_name              | grantee  | privilege
fn_content_hash           | directus | EXECUTE
fn_iu_classify_existing   | directus | EXECUTE
fn_iu_create_preflight    | directus | EXECUTE
fn_iu_resolve_default     | directus | EXECUTE
fn_iu_verify_invariants   | directus | EXECUTE

PUBLIC has no privileges on any of the 5 functions.

§8. P2 readiness

Helper install: all 5 installed, COMMIT
Preflight pass: yes (fk_initially_deferred=true)
Existing sample invariant: all_pass=true for pilot.iu0.test-001
Runtime blockers before fn_iu_create: none

P2 (fn_iu_create main writer + tests) is unblocked.

§9. Hard Boundaries — Compliance

❌ No row DML / No CREATE OR REPLACE / No table DDL — confirmed (only CREATE FUNCTION + REVOKE)
❌ No DOT/adapter/seed — confirmed
❌ No hardcode in tests — CTEs only
❌ No reuse of fn_sbx_compute_content_hash — confirmed (separate fn_content_hash body)
❌ No lifecycle_status in any output — confirmed

22-P1 Report | 2026-05-06 | COMMITTED. 5/5 helpers installed, 14/14 tests pass, counts stable, REVOKE clean. P2 ready.