P3D Pack 1 — Read-Only Inventory Prompt

Date: 2026-05-10 Status: PROMPT — chờ GPT/User review trước dispatch Scope: Read-only PG queries — so sánh TAC schema vs IU native schema Hard boundary: NO MUTATION. Read-only queries only.

#!/usr/bin/env bash
set -uo pipefail
CONTAINER="${PG_CONTAINER:-postgres}"
DB="${PG_DB:-directus}"
DBUSER="${PG_USER:-directus}"
PSQL=(docker exec -i "$CONTAINER" psql -U "$DBUSER" -d "$DB" -v ON_ERROR_STOP=1)
TS=$(date +%Y%m%d-%H%M%S)
LOG="/tmp/p3d-pack1-inventory-${TS}.log"
exec > >(tee -a "$LOG") 2>&1
echo "=== P3D PACK 1 INVENTORY — READ-ONLY — $TS ==="

# ── S1: TAC schema ──
echo "--- S1: tac_logical_unit columns ---"
"${PSQL[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='tac_logical_unit' ORDER BY ordinal_position;"
echo ""
echo "--- S1b: tac_logical_unit count + sample ---"
"${PSQL[@]}" -c "SELECT count(*) AS total FROM tac_logical_unit;"
"${PSQL[@]}" -c "SELECT id, canonical_address, section_type, lifecycle_status, parent_id IS NOT NULL AS has_parent FROM tac_logical_unit ORDER BY canonical_address LIMIT 5;"
echo ""

# ── S2: tac_unit_version schema ──
echo "--- S2: tac_unit_version columns ---"
"${PSQL[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='tac_unit_version' ORDER BY ordinal_position;"
echo ""
echo "--- S2b: tac_unit_version count + lifecycle dist ---"
"${PSQL[@]}" -c "SELECT count(*) AS total FROM tac_unit_version;"
"${PSQL[@]}" -c "SELECT lifecycle_status, count(*) FROM tac_unit_version GROUP BY lifecycle_status;"
echo ""

# ── S3: tac_publication schema ──
echo "--- S3: tac_publication columns ---"
"${PSQL[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='tac_publication' ORDER BY ordinal_position;"
echo ""
echo "--- S3b: tac_publication count ---"
"${PSQL[@]}" -c "SELECT id, doc_code, version, lifecycle_status FROM tac_publication ORDER BY doc_code;"
echo ""

# ── S4: tac_publication_member schema ──
echo "--- S4: tac_publication_member columns ---"
"${PSQL[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='tac_publication_member' ORDER BY ordinal_position;"
echo ""
echo "--- S4b: render_order continuity ---"
"${PSQL[@]}" -c "SELECT publication_id, count(*) AS members, min(render_order) AS min_ro, max(render_order) AS max_ro FROM tac_publication_member GROUP BY publication_id;"
echo ""

# ── S5: IU native information_unit schema ──
echo "--- S5: information_unit columns ---"
"${PSQL[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='information_unit' ORDER BY ordinal_position;"
echo ""
echo "--- S5b: information_unit count + sample ---"
"${PSQL[@]}" -c "SELECT count(*) AS total FROM information_unit;"
"${PSQL[@]}" -c "SELECT id, canonical_address, lifecycle_status, parent_or_container_ref IS NOT NULL AS has_parent FROM information_unit LIMIT 5;"
echo ""

# ── S6: IU native unit_version schema ──
echo "--- S6: unit_version columns ---"
"${PSQL[@]}" -c "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='unit_version' ORDER BY ordinal_position;"
echo ""
echo "--- S6b: unit_version count + version_seq ---"
"${PSQL[@]}" -c "SELECT count(*) AS total FROM unit_version;"
"${PSQL[@]}" -c "SELECT lifecycle_status, count(*) FROM unit_version GROUP BY lifecycle_status;"
echo ""

# ── S7: Column comparison ──
echo "--- S7: Side-by-side column comparison ---"
"${PSQL[@]}" <<'SQL'
SELECT 'TAC_LU' AS source, column_name, data_type FROM information_schema.columns WHERE table_name='tac_logical_unit'
UNION ALL
SELECT 'IU_NATIVE' AS source, column_name, data_type FROM information_schema.columns WHERE table_name='information_unit'
ORDER BY column_name, source;
SQL
echo ""
"${PSQL[@]}" <<'SQL'
SELECT 'TAC_UV' AS source, column_name, data_type FROM information_schema.columns WHERE table_name='tac_unit_version'
UNION ALL
SELECT 'UV_NATIVE' AS source, column_name, data_type FROM information_schema.columns WHERE table_name='unit_version'
ORDER BY column_name, source;
SQL
echo ""

# ── S8: FK/constraints ──
echo "--- S8: FK constraints TAC + IU ---"
"${PSQL[@]}" <<'SQL'
SELECT tc.table_name, tc.constraint_name, kcu.column_name, ccu.table_name AS foreign_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name IN ('tac_logical_unit','tac_unit_version','tac_publication','tac_publication_member','information_unit','unit_version')
ORDER BY tc.table_name, tc.constraint_name;
SQL
echo ""

# ── S9: IU functions exist ──
echo "--- S9: IU functions ---"
"${PSQL[@]}" -c "SELECT proname, pronargs FROM pg_proc WHERE proname LIKE 'fn_iu_%' AND pronamespace='public'::regnamespace ORDER BY proname;"
echo ""

# ── S10: Gateway triggers ──
echo "--- S10: Gateway + notification triggers ---"
"${PSQL[@]}" -c "SELECT tgname, tgrelid::regclass AS table_name, tgenabled FROM pg_trigger WHERE tgname LIKE 'trg_aa_iu%' OR tgname LIKE 'trg_aa_uv%' ORDER BY tgname;"
echo ""

# ── S11: dot_config IU keys ──
echo "--- S11: IU dot_config ---"
"${PSQL[@]}" -c "SELECT key, value FROM dot_config WHERE key LIKE 'iu_%' ORDER BY key;"
echo ""

echo "=== END INVENTORY $TS ==="
echo ""
echo "=== AGENT: UPLOAD REPORT ==="
echo "Path: knowledge/dev/laws/dieu44-trien-khai/reports/p3d-pack1-readonly-inventory-report.md"
echo "Include: full log above"
echo "Upload even on error."