22-P3-P0 — IU Creation Gateway Read-only Inspection Prompt (rev3)

Date: 2026-05-06 | Status: PROMPT rev3 — chờ GPT/User approve trước dispatch Controlling: GPT rev2 review (8 fixes: systematic error capture, ON_ERROR_STOP, partial data, inspection_status) HARD BOUNDARY: READ-ONLY ONLY. Không DDL, không DML, không GRANT/REVOKE, không trigger, không function change.

---

#!/usr/bin/env bash
# 22-P3-P0 rev3 — IU Creation Gateway Read-only Inspection
# STRICTLY READ-ONLY. No writes, no DDL, no DML, no permission changes.

set -uo pipefail  # no -e: always reach final status

PG_CONTAINER="${PG_CONTAINER:-postgres}"
PG_USER="${PG_USER:-directus}"
PG_DB="${PG_DB:-directus}"
PSQL_RO=(docker exec -i "$PG_CONTAINER" psql -v ON_ERROR_STOP=1 -U "$PG_USER" -d "$PG_DB")

LOG_PATH="/tmp/22-p3-p0.$(date -u +%Y%m%d-%H%M%S).log"
QUERY_ERRORS=""
QUERY_COUNT=0
QUERY_FAIL_COUNT=0

echo "=== 22-P3-P0 rev3 IU Creation Gateway Inspection ===" | tee "$LOG_PATH"
echo "timestamp=$(date -u +%Y-%m-%dT%H:%M:%SZ)" | tee -a "$LOG_PATH"

run_section() {
  echo "" | tee -a "$LOG_PATH"
  echo "========================================" | tee -a "$LOG_PATH"
  echo "§$1" | tee -a "$LOG_PATH"
  echo "========================================" | tee -a "$LOG_PATH"
}

# Helper: run SQL, capture exit code, track errors
run_sql() {
  local id="$1" label="$2"
  QUERY_COUNT=$((QUERY_COUNT + 1))
  echo "--- $id $label ---" | tee -a "$LOG_PATH"
  "${PSQL_RO[@]}" 2>&1 | tee -a "$LOG_PATH"
  local psql_exit=${PIPESTATUS[0]}
  if [ "$psql_exit" -ne 0 ]; then
    QUERY_FAIL_COUNT=$((QUERY_FAIL_COUNT + 1))
    QUERY_ERRORS="${QUERY_ERRORS}${id}: ${label} (exit=${psql_exit})\n"
    echo "*** QUERY ERROR ($id) — exit=$psql_exit — logged ***" | tee -a "$LOG_PATH"
  fi
  return "$psql_exit"
}

# ============================================================
# §1 PG OWNERSHIP AND PRIVILEGES
# ============================================================
run_section "1 PG OWNERSHIP AND PRIVILEGES"

run_sql "1.1" "Table owners" <<'SQL'
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE tablename IN ('information_unit', 'unit_version', 'birth_registry', 'dot_config')
ORDER BY tablename;
SQL

run_sql "1.2" "Granted table privileges on information_unit" <<'SQL'
SELECT grantee, privilege_type, is_grantable
FROM information_schema.table_privileges
WHERE table_schema = 'public' AND table_name = 'information_unit'
ORDER BY grantee, privilege_type;
SQL

run_sql "1.3" "Granted table privileges on unit_version" <<'SQL'
SELECT grantee, privilege_type, is_grantable
FROM information_schema.table_privileges
WHERE table_schema = 'public' AND table_name = 'unit_version'
ORDER BY grantee, privilege_type;
SQL

run_sql "1.4" "EFFECTIVE table privileges (has_table_privilege)" <<'SQL'
SELECT r.rolname,
       has_table_privilege(r.rolname, 'public.information_unit', 'INSERT') AS iu_insert,
       has_table_privilege(r.rolname, 'public.information_unit', 'UPDATE') AS iu_update,
       has_table_privilege(r.rolname, 'public.information_unit', 'DELETE') AS iu_delete,
       has_table_privilege(r.rolname, 'public.unit_version', 'INSERT') AS uv_insert,
       has_table_privilege(r.rolname, 'public.unit_version', 'UPDATE') AS uv_update,
       has_table_privilege(r.rolname, 'public.unit_version', 'DELETE') AS uv_delete
FROM pg_roles r
WHERE r.rolname NOT LIKE 'pg_%'
ORDER BY r.rolname;
SQL

run_sql "1.5" "EFFECTIVE function EXECUTE privileges" <<'SQL'
SELECT r.rolname,
       has_function_privilege(r.rolname, 'public.fn_iu_create(text,text,text,text,text,text,text,text,uuid)', 'EXECUTE') AS can_create,
       has_function_privilege(r.rolname, 'public.fn_iu_create_plan(text,text,text,text,text,text,text,text,uuid)', 'EXECUTE') AS can_plan
FROM pg_roles r
WHERE r.rolname NOT LIKE 'pg_%'
ORDER BY r.rolname;
SQL

run_sql "1.6" "Function metadata" <<'SQL'
SELECT p.proname, n.nspname, p.proowner::regrole AS owner, p.prosecdef, p.provolatile,
  COALESCE(array_to_string(p.proconfig, ', '), '') AS config
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
  AND p.proname IN (
    'fn_iu_create', 'fn_iu_create_plan',
    'fn_content_hash', 'fn_iu_resolve_default', 'fn_iu_classify_existing',
    'fn_iu_create_preflight', 'fn_iu_verify_invariants',
    'fn_birth_registry_auto'
  )
ORDER BY p.proname;
SQL

run_sql "1.7" "Granted EXECUTE on IU functions" <<'SQL'
SELECT routine_name, grantee, privilege_type
FROM information_schema.routine_privileges
WHERE routine_schema = 'public'
  AND routine_name IN (
    'fn_iu_create', 'fn_iu_create_plan',
    'fn_content_hash', 'fn_iu_resolve_default', 'fn_iu_classify_existing',
    'fn_iu_create_preflight', 'fn_iu_verify_invariants'
  )
ORDER BY routine_name, grantee;
SQL

run_sql "1.8" "PUBLIC grants on IU functions" <<'SQL'
SELECT routine_name, grantee, privilege_type
FROM information_schema.routine_privileges
WHERE routine_schema = 'public'
  AND routine_name IN ('fn_iu_create', 'fn_iu_create_plan',
    'fn_content_hash', 'fn_iu_resolve_default', 'fn_iu_classify_existing',
    'fn_iu_create_preflight', 'fn_iu_verify_invariants')
  AND grantee = 'PUBLIC';
SQL

run_sql "1.9" "All PG roles" <<'SQL'
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;
SQL

# ============================================================
# §2 DIRECTUS DEPENDENCY
# ============================================================
run_section "2 DIRECTUS DEPENDENCY"

# Note: Directus tables are OPTIONAL — if missing, report as partial data, NOT as "no dependency"
run_sql "2.1" "Directus collection metadata [optional table]" <<'SQL'
SELECT collection, singleton, sort_field, accountability, versioning
FROM public.directus_collections
WHERE collection IN ('information_unit', 'unit_version', 'birth_registry')
ORDER BY collection;
SQL

run_sql "2.2" "Directus permissions for IU/UV [optional table]" <<'SQL'
SELECT p.id, p.collection, p.action, p.fields, p.policy,
  pol.name AS policy_name, r.name AS role_name
FROM public.directus_permissions p
LEFT JOIN public.directus_policies pol ON pol.id = p.policy
LEFT JOIN public.directus_access da ON da.policy = p.policy
LEFT JOIN public.directus_roles r ON r.id = da.role
WHERE p.collection IN ('information_unit', 'unit_version')
ORDER BY p.collection, p.action;
SQL

run_sql "2.3" "Directus roles admin_access/app_access [optional table]" <<'SQL'
SELECT id, name, admin_access, app_access
FROM public.directus_roles
ORDER BY name;
SQL

run_sql "2.4" "Directus fields for IU [optional table]" <<'SQL'
SELECT collection, field, hidden, readonly, sort
FROM public.directus_fields
WHERE collection = 'information_unit'
ORDER BY sort NULLS LAST, field;
SQL

run_sql "2.5" "PG role Directus is using" <<'SQL'
SELECT current_user AS cu, session_user AS su, current_database() AS db;
SQL

# ============================================================
# §3 EXISTING TRIGGERS / GATES
# ============================================================
run_section "3 EXISTING TRIGGERS / GATES"

run_sql "3.1" "Triggers on information_unit" <<'SQL'
SELECT t.tgname, t.tgenabled,
  CASE t.tgtype & 2 WHEN 2 THEN 'BEFORE' ELSE 'AFTER' END AS timing,
  CASE t.tgtype & 28
    WHEN 4 THEN 'INSERT' WHEN 8 THEN 'DELETE' WHEN 16 THEN 'UPDATE'
    WHEN 20 THEN 'INSERT|UPDATE' WHEN 12 THEN 'INSERT|DELETE'
    WHEN 24 THEN 'UPDATE|DELETE' WHEN 28 THEN 'INSERT|UPDATE|DELETE'
    ELSE (t.tgtype & 28)::text
  END AS events,
  p.proname AS function_name,
  CASE t.tgtype & 1 WHEN 1 THEN 'FOR EACH ROW' ELSE 'FOR EACH STATEMENT' END AS level
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE t.tgrelid = 'public.information_unit'::regclass AND NOT t.tgisinternal
ORDER BY t.tgname;
SQL

run_sql "3.2" "Triggers on unit_version" <<'SQL'
SELECT t.tgname, t.tgenabled,
  CASE t.tgtype & 2 WHEN 2 THEN 'BEFORE' ELSE 'AFTER' END AS timing,
  CASE t.tgtype & 28
    WHEN 4 THEN 'INSERT' WHEN 8 THEN 'DELETE' WHEN 16 THEN 'UPDATE'
    WHEN 20 THEN 'INSERT|UPDATE' WHEN 12 THEN 'INSERT|DELETE'
    WHEN 24 THEN 'UPDATE|DELETE' WHEN 28 THEN 'INSERT|UPDATE|DELETE'
    ELSE (t.tgtype & 28)::text
  END AS events,
  p.proname AS function_name,
  CASE t.tgtype & 1 WHEN 1 THEN 'FOR EACH ROW' ELSE 'FOR EACH STATEMENT' END AS level
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE t.tgrelid = 'public.unit_version'::regclass AND NOT t.tgisinternal
ORDER BY t.tgname;
SQL

run_sql "3.3" "Birth trigger function source (first 4000 chars)" <<'SQL'
SELECT left(prosrc, 4000) FROM pg_proc WHERE proname = 'fn_birth_registry_auto';
SQL

run_sql "3.4" "Trigger order context (ordinary triggers: alphabetical by name for same table/event/timing)" <<'SQL'
SELECT t.tgname,
  CASE t.tgtype & 2 WHEN 2 THEN 'BEFORE' ELSE 'AFTER' END AS timing,
  CASE t.tgtype & 28
    WHEN 4 THEN 'INSERT' WHEN 8 THEN 'DELETE' WHEN 16 THEN 'UPDATE'
    WHEN 20 THEN 'INSERT|UPDATE' ELSE (t.tgtype & 28)::text
  END AS events,
  p.proname,
  t.tgconstraint != 0 AS is_constraint_trigger
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE t.tgrelid = 'public.information_unit'::regclass AND NOT t.tgisinternal
ORDER BY t.tgname;
SQL

# ============================================================
# §4 EXISTING IU ROWS AND PROVENANCE CLUES
# ============================================================
run_section "4 EXISTING IU ROWS AND PROVENANCE CLUES"

run_sql "4.1" "IU/UV/birth counts" <<'SQL'
SELECT 'information_unit' AS tbl, count(*) FROM public.information_unit
UNION ALL SELECT 'unit_version', count(*) FROM public.unit_version
UNION ALL SELECT 'birth_total', count(*) FROM public.birth_registry;
SQL

run_sql "4.2" "Birth coverage via entity_code join (accurate)" <<'SQL'
SELECT
  count(*) AS total_iu,
  count(br.id) AS iu_with_birth,
  count(*) FILTER (WHERE br.id IS NULL) AS iu_missing_birth
FROM public.information_unit iu
LEFT JOIN public.birth_registry br
  ON br.collection_name = 'information_unit'
 AND br.entity_code = 'information_unit::' || iu.id::text;
SQL

run_sql "4.3" "Duplicate birth detection" <<'SQL'
SELECT entity_code, count(*) AS birth_count
FROM public.birth_registry
WHERE collection_name = 'information_unit'
GROUP BY entity_code
HAVING count(*) > 1
ORDER BY birth_count DESC;
SQL

run_sql "4.4" "created_by distribution" <<'SQL'
SELECT created_by, count(*) AS cnt
FROM public.information_unit
GROUP BY created_by ORDER BY cnt DESC;
SQL

run_sql "4.5" "identity_profile top-level keys distribution" <<'SQL'
SELECT key, count(*) AS cnt
FROM public.information_unit, jsonb_object_keys(COALESCE(identity_profile, '{}'::jsonb)) AS key
GROUP BY key ORDER BY cnt DESC;
SQL

run_sql "4.6" "Provenance markers search" <<'SQL'
SELECT
  count(*) FILTER (WHERE identity_profile ? 'created_via') AS has_created_via,
  count(*) FILTER (WHERE identity_profile ? 'source') AS has_source,
  count(*) FILTER (WHERE identity_profile ? 'origin') AS has_origin,
  count(*) FILTER (WHERE identity_profile ? 'pipeline') AS has_pipeline,
  count(*) AS total
FROM public.information_unit;
SQL

run_sql "4.7" "P2 pilot rows" <<'SQL'
SELECT canonical_address, created_by, unit_kind, identity_profile->>'title' AS title
FROM public.information_unit WHERE canonical_address LIKE 'pilot.p2.%' ORDER BY canonical_address;
SQL

run_sql "4.8" "All IU canonical_address sample (first 30)" <<'SQL'
SELECT canonical_address, created_by, unit_kind
FROM public.information_unit ORDER BY canonical_address LIMIT 30;
SQL

echo "--- 4.9 Scale-safe invariant check ---" | tee -a "$LOG_PATH"
QUERY_COUNT=$((QUERY_COUNT + 1))
IU_COUNT=$("${PSQL_RO[@]}" -tA -c "SELECT count(*) FROM public.information_unit;" 2>/dev/null | tr -d '[:space:]')
echo "IU_COUNT=$IU_COUNT" | tee -a "$LOG_PATH"

if [ -z "$IU_COUNT" ] || ! [[ "$IU_COUNT" =~ ^[0-9]+$ ]]; then
  echo "*** Could not determine IU count — skipping invariant check ***" | tee -a "$LOG_PATH"
  QUERY_FAIL_COUNT=$((QUERY_FAIL_COUNT + 1))
  QUERY_ERRORS="${QUERY_ERRORS}4.9: IU count query failed or non-numeric\n"
elif [ "$IU_COUNT" -le 1000 ]; then
  echo "IU count=$IU_COUNT (<=1000) — checking ALL rows" | tee -a "$LOG_PATH"
  run_sql "4.9a" "Invariant check ALL IUs ($IU_COUNT rows)" <<'SQL'
SELECT iu.canonical_address,
  v.vj->>'all_pass' AS all_pass,
  v.vj->>'i1_iu_exists' AS i1, v.vj->>'i2_uv_linked' AS i2,
  v.vj->>'i3_anchors_exact' AS i3, v.vj->>'i4_birth_exists' AS i4,
  v.vj->>'i5_uv_birth_ok' AS i5
FROM public.information_unit iu
CROSS JOIN LATERAL (SELECT public.fn_iu_verify_invariants(iu.canonical_address) AS vj) v
ORDER BY iu.canonical_address;
SQL
else
  # >1000: SAMPLE ONLY — report must NOT claim global proof
  echo "IU count=$IU_COUNT (>1000) — SAMPLE ONLY (pilot + first 100)" | tee -a "$LOG_PATH"
  echo "*** WARNING: sample results are NOT proof of global invariant health ***" | tee -a "$LOG_PATH"
  run_sql "4.9b" "Invariant check PILOT rows (sample)" <<'SQL'
SELECT iu.canonical_address, (public.fn_iu_verify_invariants(iu.canonical_address))->>'all_pass' AS all_pass
FROM public.information_unit iu WHERE iu.canonical_address LIKE 'pilot.%' ORDER BY iu.canonical_address;
SQL
  run_sql "4.9c" "Invariant check FIRST 100 (sample — not global proof)" <<'SQL'
SELECT all_pass, count(*) FROM (
  SELECT (public.fn_iu_verify_invariants(iu.canonical_address))->>'all_pass' AS all_pass
  FROM public.information_unit iu ORDER BY iu.canonical_address LIMIT 100
) x GROUP BY all_pass;
SQL
fi

# ============================================================
# §5 POLICY REGISTRY LOCATION
# ============================================================
run_section "5 POLICY REGISTRY LOCATION"

run_sql "5.1" "dot_config columns" <<'SQL'
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'dot_config'
ORDER BY ordinal_position;
SQL

run_sql "5.2" "dot_config keys related to iu_create/creation/gateway" <<'SQL'
SELECT key, value
FROM public.dot_config
WHERE key LIKE 'iu_create.%' OR key LIKE 'creation.%'
   OR key LIKE 'gateway.%' OR key LIKE 'canonical.%'
ORDER BY key;
SQL

run_sql "5.3" "dot_config total count + sample" <<'SQL'
SELECT count(*) AS total_keys FROM public.dot_config;
SQL
run_sql "5.3b" "dot_config key sample (first 30)" <<'SQL'
SELECT key FROM public.dot_config ORDER BY key LIMIT 30;
SQL

run_sql "5.4" "collection_registry existence [optional table]" <<'SQL'
SELECT EXISTS(
  SELECT 1 FROM information_schema.tables
  WHERE table_schema = 'public' AND table_name = 'collection_registry'
) AS collection_registry_exists;
SQL
# Optional — may fail if table doesn't exist
run_sql "5.4b" "collection_registry IU entry [optional table]" <<'SQL'
SELECT * FROM public.collection_registry
WHERE collection_name IN ('information_unit', 'unit_version')
ORDER BY collection_name;
SQL

run_sql "5.5" "dot_tools registry IU-related [optional table]" <<'SQL'
SELECT tool_id, tool_name, category, status
FROM public.dot_tools
WHERE tool_name ILIKE '%iu%' OR tool_name ILIKE '%information_unit%' OR tool_name ILIKE '%create%'
ORDER BY tool_id;
SQL

# ============================================================
# §6 TAC / P10 DIRECT INSERT INVENTORY
# ============================================================
run_section "6 TAC / P10 DIRECT INSERT INVENTORY"

run_sql "6.1" "IUs by address pattern" <<'SQL'
SELECT
  CASE
    WHEN canonical_address LIKE 'pilot.%' THEN 'pilot'
    WHEN canonical_address LIKE 'test.%' THEN 'test'
    WHEN canonical_address ~ '^\d+\.' THEN 'TAC-numeric'
    ELSE 'other'
  END AS address_pattern,
  count(*) AS cnt,
  array_agg(DISTINCT created_by) AS creators
FROM public.information_unit GROUP BY 1 ORDER BY cnt DESC;
SQL

run_sql "6.2" "Non-pilot/test IU samples" <<'SQL'
SELECT canonical_address, created_by, unit_kind, identity_profile->>'title' AS title
FROM public.information_unit
WHERE canonical_address NOT LIKE 'pilot.%' AND canonical_address NOT LIKE 'test.%'
ORDER BY canonical_address LIMIT 30;
SQL

echo "--- §6 KB/FILE SEARCH (Agent must do separately) ---" | tee -a "$LOG_PATH"
echo "1. search_knowledge('TAC IU creation INSERT information_unit')" | tee -a "$LOG_PATH"
echo "2. search_knowledge('P10A P10B direct INSERT')" | tee -a "$LOG_PATH"
echo "3. Identify exact TAC prompt files with INSERT INTO information_unit" | tee -a "$LOG_PATH"
echo "4. Note: TAC validation? batch size? fn_iu_create compatible?" | tee -a "$LOG_PATH"

# ============================================================
# §7 GLOBAL DIRECT-WRITE INVENTORY
# ============================================================
run_section "7 GLOBAL DIRECT-WRITE INVENTORY"

echo "--- 7.1 Filesystem search: direct INSERT/UPDATE patterns ---" | tee -a "$LOG_PATH"

GREP_CMD="grep"
GREP_ARGS=()
if command -v rg >/dev/null 2>&1; then
  GREP_CMD="rg"
  GREP_ARGS=(-n --glob '*.{sh,sql,md,py,js,ts}' \
    -g '!**/.git/**' -g '!**/node_modules/**' -g '!**/backups/**' \
    -g '!**/context-pack/**' -g '!**/*.log')
  echo "Using ripgrep (rg)" | tee -a "$LOG_PATH"
else
  GREP_ARGS=(-rn \
    --include="*.sh" --include="*.sql" --include="*.md" --include="*.py" --include="*.js" --include="*.ts" \
    --exclude-dir=.git --exclude-dir=node_modules --exclude-dir=backups \
    --exclude-dir=context-pack)
  echo "Using grep" | tee -a "$LOG_PATH"
fi

for pattern in \
  "INSERT INTO public.information_unit" \
  "INSERT INTO information_unit" \
  "UPDATE public.information_unit" \
  "INSERT INTO public.unit_version" \
  "INSERT INTO unit_version" \
  "UPDATE public.unit_version"; do
  echo "--- Pattern: $pattern ---" | tee -a "$LOG_PATH"
  QUERY_COUNT=$((QUERY_COUNT + 1))
  timeout 30 "$GREP_CMD" "${GREP_ARGS[@]}" "$pattern" /home/ /opt/ /root/ 2>/dev/null | head -50 | tee -a "$LOG_PATH"
  GREP_EXIT=${PIPESTATUS[0]}
  if [ "$GREP_EXIT" -eq 124 ]; then
    echo "*** TIMEOUT — search truncated ***" | tee -a "$LOG_PATH"
    QUERY_FAIL_COUNT=$((QUERY_FAIL_COUNT + 1))
    QUERY_ERRORS="${QUERY_ERRORS}7.1/$pattern: grep timeout (30s)\n"
  fi
  echo "(end pattern)" | tee -a "$LOG_PATH"
done

echo "--- §7 KB SEARCH (Agent must do separately) ---" | tee -a "$LOG_PATH"
echo "1. search_knowledge('INSERT INTO information_unit')" | tee -a "$LOG_PATH"
echo "2. search_knowledge('INSERT INTO unit_version')" | tee -a "$LOG_PATH"
echo "3. Classify each: migration / test-pilot / TAC-import / production / stale / unknown" | tee -a "$LOG_PATH"

# ============================================================
# §8 DETECTOR / AUXILIARY COVERAGE
# ============================================================
run_section "8 DETECTOR / AUXILIARY COVERAGE"

run_sql "8.1" "system_health_checks existence [optional table]" <<'SQL'
SELECT EXISTS(
  SELECT 1 FROM information_schema.tables
  WHERE table_schema = 'public' AND table_name = 'system_health_checks'
) AS health_checks_table_exists;
SQL

run_sql "8.2" "Health checks IU/birth/orphan related [optional table]" <<'SQL'
SELECT check_name, check_type, status, last_run
FROM public.system_health_checks
WHERE check_name ILIKE '%iu%' OR check_name ILIKE '%birth%' OR check_name ILIKE '%orphan%' OR check_name ILIKE '%ghost%'
ORDER BY check_name;
SQL

run_sql "8.3" "Registry health / detector functions" <<'SQL'
SELECT proname FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
  AND (proname ILIKE '%registry_health%' OR proname ILIKE '%orphan%' OR proname ILIKE '%ghost%' OR proname ILIKE '%detector%')
ORDER BY proname;
SQL

run_sql "8.4" "pg_cron existence" <<'SQL'
SELECT EXISTS(SELECT 1 FROM pg_extension WHERE extname = 'pg_cron') AS pg_cron_installed;
SQL

# ============================================================
# §9 TRIGGER-GUARD FEASIBILITY
# ============================================================
run_section "9 TRIGGER-GUARD FEASIBILITY"

run_sql "9.1" "current_setting canonical_writer (should be empty/not set)" <<'SQL'
SELECT COALESCE(current_setting('app.canonical_writer', true), '(not set)') AS canonical_writer_current;
SQL

run_sql "9.2" "Existing BEFORE INSERT triggers on IU" <<'SQL'
SELECT t.tgname, p.proname, t.tgenabled
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE t.tgrelid = 'public.information_unit'::regclass
  AND NOT t.tgisinternal AND (t.tgtype & 2) = 2 AND (t.tgtype & 4) = 4
ORDER BY t.tgname;
SQL

run_sql "9.3" "fn_iu_create — does it set canonical_writer? (CRITICAL)" <<'SQL'
SELECT CASE
  WHEN prosrc LIKE '%canonical_writer%' THEN 'YES — sets canonical_writer'
  ELSE 'NO — does NOT set canonical_writer (MUST patch fn_iu_create BEFORE adding trigger guard)'
END AS canonical_writer_in_fn
FROM pg_proc WHERE proname = 'fn_iu_create';
SQL
# ⚠️ If 9.3 = NO → trigger guard would BLOCK fn_iu_create itself.
# Sequencing: patch fn_iu_create → then trigger guard. Two prompts.

run_sql "9.4" "Trigger order context" <<'SQL'
SELECT t.tgname,
  CASE t.tgtype & 2 WHEN 2 THEN 'BEFORE' ELSE 'AFTER' END AS timing,
  CASE t.tgtype & 28
    WHEN 4 THEN 'INSERT' WHEN 8 THEN 'DELETE' WHEN 16 THEN 'UPDATE'
    WHEN 20 THEN 'INSERT|UPDATE' ELSE (t.tgtype & 28)::text
  END AS events,
  p.proname,
  t.tgconstraint != 0 AS is_constraint_trigger
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE t.tgrelid = 'public.information_unit'::regclass AND NOT t.tgisinternal
ORDER BY t.tgname;
SQL

# ============================================================
# FINAL STATUS
# ============================================================
echo "" | tee -a "$LOG_PATH"
echo "=== INSPECTION SUMMARY ===" | tee -a "$LOG_PATH"
echo "queries_run=$QUERY_COUNT" | tee -a "$LOG_PATH"
echo "queries_failed=$QUERY_FAIL_COUNT" | tee -a "$LOG_PATH"
echo "LOG=$LOG_PATH" | tee -a "$LOG_PATH"
echo "All queries READ-ONLY. No mutations performed." | tee -a "$LOG_PATH"

if [ -n "$QUERY_ERRORS" ]; then
  echo "" | tee -a "$LOG_PATH"
  echo "=== QUERY ERRORS / PARTIAL DATA ===" | tee -a "$LOG_PATH"
  echo -e "$QUERY_ERRORS" | tee -a "$LOG_PATH"
  echo "inspection_status=PARTIAL" | tee -a "$LOG_PATH"
  echo "*** Agent: report must note partial data. Reduce recommendation confidence. ***" | tee -a "$LOG_PATH"
else
  echo "inspection_status=COMPLETE" | tee -a "$LOG_PATH"
fi

echo "" | tee -a "$LOG_PATH"
echo "=== UPLOAD REPORT NOW ===" | tee -a "$LOG_PATH"

---

Agent Instructions

KB searches (do after bash script):

1. search_knowledge("TAC IU creation INSERT information_unit")
2. search_knowledge("P10A P10B direct INSERT")
3. search_knowledge("INSERT INTO information_unit")
4. search_knowledge("INSERT INTO unit_version")

Add findings to §6 and §7 of report.

§10 Analysis (ADVISORY ONLY — GPT/User decide)

Evaluate 5 options based on evidence:

Option	Breakage risk	Bypass reduction	Complexity	Hardcode risk	Rollback	Readiness
A Observe-only	?	?	?	?	?	?
B L0 policy only	?	?	?	?	?	?
C L0 + L3 detector	?	?	?	?	?	?
D L0 + L2 guard	?	?	?	?	?	?
E Full role sep	?	?	?	?	?	?

If inspection_status=PARTIAL, Agent MUST note which evidence is missing and reduce confidence accordingly.

Agent recommend 1 option with rationale. GPT/User will review and decide.

---

Report Template

# 22-P3-P0 — IU Creation Gateway Inspection Report

> Date: [timestamp]
> Executor: [agent]
> Prompt: rev3
> inspection_status: [COMPLETE/PARTIAL]
> queries_run: [N]
> queries_failed: [N]
> log_path: [path]

## §1 PG Ownership and Privileges
[results — include effective privileges]

## §2 Directus Dependency
[results — note if any Directus query failed = partial data, not "no dependency"]

## §3 Existing Triggers / Gates
[results]

## §4 Existing IU Rows and Provenance
[results — birth join, duplicate detection]
[If IU >1000: note invariant results are SAMPLE ONLY, not global proof]

## §5 Policy Registry Location
[results — dot_config columns, keys]

## §6 TAC / P10 Direct Insert Inventory
[SQL results + KB search results]

## §7 Global Direct-write Inventory
[grep results + KB search results — note timeouts/truncations]
[Classify each: migration / test-pilot / TAC-import / production / stale / unknown]

## §8 Detector / Auxiliary Coverage
[results — note if optional tables missing = partial]

## §9 Trigger-guard Feasibility
Key finding: fn_iu_create [does/does not] set canonical_writer.
Sequencing: [patch fn_iu_create BEFORE trigger guard / not needed]

## Query Errors / Partial Data
[list failed queries with id, error excerpt, impact on recommendation]

## §10 Risk / Recommendation Matrix (ADVISORY ONLY)
[table + recommendation]
[If PARTIAL: note reduced confidence and which evidence is missing]

Report path: knowledge/dev/laws/dieu44-trien-khai/reports/22-p3-p0-iu-creation-gateway-inspection-report.md

---

Hard Boundaries

• ❌ READ-ONLY ONLY — no DDL, no DML, no GRANT/REVOKE
• ❌ No trigger creation, no function change
• ❌ No DOT registration, no adapter, no cleanup, no IU rows, no Pack 2C

---

22-P3-P0 rev3 | 2026-05-06 | READ-ONLY | Systematic error capture | Chờ GPT/User approve trước dispatch.