DOT-119 v2.0.0 Script (Rev 5)
#!/usr/bin/env bash
=============================================================================
dot-birth-trigger-setup v2.0.0 — Metadata-Driven Birth Trigger Management
=============================================================================
Per: 18a rev4 FINAL. No-clobber. JSON. Explicit modes.
MUST NOT contain CREATE OR REPLACE FUNCTION. Self-check at startup.
Exit codes: 0=success/skip 1=usage 10=unclassified 11=invalid-metadata
12=no-clobber/fn-hash-drift 13=fallback-missing 14=trigger-mismatch
20=exec-failed 30=permission/tooling
=============================================================================
set -euo pipefail
VERSION="2.0.0" SCRIPT_NAME="dot-birth-trigger-setup" SYNTHETIC_SENTINEL="birth_synthetic_id"
═══ NO-CLOBBER SELF-CHECK (split to avoid self-match) ═══
_NC="CREATE OR REPLACE FUNCTION" _NC="${_NC} fn_birth_registry_auto" if grep -qF "$_NC" "$0" 2>/dev/null; then echo '{"status":"error","exit_code":12,"message":"FATAL: No-clobber violation."}' >&2 exit 12 fi unset _NC
═══ CONFIG ═══
PG_CONTAINER="${PG_CONTAINER:-postgres}" PG_USER="${PG_USER:-directus}" PG_DB="${PG_DB:-directus}"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" [ -f "${SCRIPT_DIR}/../config/environment.sh" ] && source "${SCRIPT_DIR}/../config/environment.sh"
═══ HELPERS ═══
run_pg() { docker exec -i "$PG_CONTAINER" psql -U "$PG_USER" -d "$PG_DB" -t -A -v ON_ERROR_STOP=1 "$@"; }
JSON_OUTPUT=false emit() { if [ "$JSON_OUTPUT" = true ]; then echo "$1" elif [ -n "${2:-}" ]; then echo "$2"; fi }
check_synthetic_fallback_capability() { local fn_source fn_source=$(run_pg <<SQL || true SELECT pg_get_functiondef('fn_birth_registry_auto'::regproc) SQL ) [ -z "$fn_source" ] && { echo "fn_birth_registry_auto not found"; return 1; } local missing="" echo "$fn_source" | grep -q 'TG_TABLE_NAME' || missing="${missing} TG_TABLE_NAME" echo "$fn_source" | grep -q '::' || missing="${missing} ::-separator" if ! echo "$fn_source" | grep -qE "NEW.id|.id\b|to_jsonb(NEW).*id|->>'id'"; then missing="${missing} id-fallback" fi [ -n "$missing" ] && { echo "Missing:${missing}"; return 1; } echo "pass" return 0 }
usage() { cat <<EOF $SCRIPT_NAME v$VERSION — Metadata-Driven Birth Trigger Setup
Usage: $SCRIPT_NAME --collection=<name> --dry-run [--json] $SCRIPT_NAME --collection=<name> --execute [--json] $SCRIPT_NAME --collection=<name> --verify [--json] $SCRIPT_NAME --collection=<name> --drop --confirm-drop [--json] $SCRIPT_NAME --help
Exit: 0=ok/skip 1=usage 10=unclassified 11=invalid 12=no-clobber 13=fallback-missing 14=mismatch 20=exec-fail 30=perm EOF }
═══ CLI PARSING (exactly 1 mode) ═══
COLLECTION="" MODE="" MODE_COUNT=0 CONFIRM_DROP=false SYNTHETIC_CAP_STATUS=""
[ $# -eq 0 ] && { usage; exit 1; }
for arg in "$@"; do case "$arg" in --collection=) COLLECTION="${arg#=}" ;; --dry-run) MODE="dry-run"; MODE_COUNT=$((MODE_COUNT+1)) ;; --execute) MODE="execute"; MODE_COUNT=$((MODE_COUNT+1)) ;; --verify) MODE="verify"; MODE_COUNT=$((MODE_COUNT+1)) ;; --drop) MODE="drop"; MODE_COUNT=$((MODE_COUNT+1)) ;; --confirm-drop) CONFIRM_DROP=true ;; --json) JSON_OUTPUT=true ;; --help|-h) usage; exit 0 ;; *) echo "Unknown: $arg" >&2; usage; exit 1 ;; esac done
[ -z "$COLLECTION" ] || [ -z "$MODE" ] && { usage; exit 1; }
if [ "$MODE_COUNT" -ne 1 ]; then
emit "{"status":"error","exit_code":1,"message":"Exactly one mode required."}"
"ERROR: Exactly one of --dry-run/--execute/--verify/--drop."
exit 1
fi
if [ "$MODE" = "drop" ]; then
if [ "${DOT119_ALLOW_DROP:-}" != "1" ] || [ "$CONFIRM_DROP" != true ]; then
emit "{"status":"error","exit_code":30,"message":"--drop requires DOT119_ALLOW_DROP=1 and --confirm-drop."}"
"ERROR: --drop needs DOT119_ALLOW_DROP=1 + --confirm-drop."
exit 30
fi
fi
if ! echo "$COLLECTION" | grep -qE '^[a-z_][a-z0-9_]*$'; then
emit "{"status":"error","exit_code":30,"message":"Invalid collection: $COLLECTION"}"
"ERROR: Invalid collection '$COLLECTION'."
exit 30
fi
═══ READ METADATA ═══
METADATA=$(run_pg <<SQL || true SELECT birth_code_strategy || '|' || COALESCE(birth_code_column, '') || '|' || COALESCE(birth_identity_source, '') FROM collection_registry WHERE collection_name = '${COLLECTION}' SQL )
[ -z "$METADATA" ] && {
emit "{"status":"error","exit_code":30,"collection":"$COLLECTION","message":"Not in collection_registry."}"
"ERROR: '$COLLECTION' not in collection_registry."
exit 30
}
STRATEGY=$(echo "$METADATA" | cut -d'|' -f1) CODE_COLUMN=$(echo "$METADATA" | cut -d'|' -f2)
═══ STRATEGY GATE ═══
if [ "$STRATEGY" = "unclassified" ]; then
emit "{"status":"stop","exit_code":10,"collection":"$COLLECTION","strategy":"unclassified","finding":{"finding_code":"BIRTH-IDENTITY-UNCLASSIFIED","severity":"blocker","remediation":["Classify via registry metadata path.","Rerun: $SCRIPT_NAME --collection=$COLLECTION --dry-run --json"]}}"
"STOP: '$COLLECTION' unclassified."
exit 10
fi
if [ "$STRATEGY" = "disabled" ] || [ "$STRATEGY" = "subordinate" ]; then
if [ "$MODE" = "verify" ]; then
TRIG_COUNT=$(run_pg <<SQL
SELECT count(*) FROM pg_trigger WHERE tgname='trg_birth_${COLLECTION}' AND NOT tgisinternal
SQL
)
if [ "${TRIG_COUNT:-0}" -gt 0 ]; then
emit "{"status":"mismatch","exit_code":14,"collection":"$COLLECTION","strategy":"$STRATEGY","message":"Trigger exists but strategy=$STRATEGY."}"
"MISMATCH: '$COLLECTION' strategy=$STRATEGY but trigger exists."
exit 14
fi
emit "{"status":"verified_no_trigger","exit_code":0,"collection":"$COLLECTION","strategy":"$STRATEGY"}"
"VERIFY OK: '$COLLECTION' strategy=$STRATEGY, no trigger."
exit 0
fi
emit "{"status":"skipped","exit_code":0,"collection":"$COLLECTION","strategy":"$STRATEGY","message":"No trigger needed."}"
"SKIP: '$COLLECTION' strategy=$STRATEGY."
exit 0
fi
═══ VALIDATE TABLE ═══
TABLE_EXISTS=$(run_pg <<SQL
SELECT count(*) FROM information_schema.tables WHERE table_schema='public' AND table_name='${COLLECTION}'
SQL
)
[ "${TABLE_EXISTS:-0}" -eq 0 ] && {
emit "{"status":"error","exit_code":11,"collection":"$COLLECTION","message":"Table not found."}"
"ERROR: Table '$COLLECTION' not found."
exit 11
}
═══ STRATEGY → FUNCTION + ARGS ═══
TRIGGER_FUNCTION="" TRIGGER_ARGS=""
case "$STRATEGY" in
column)
[ -z "$CODE_COLUMN" ] && {
emit "{"status":"error","exit_code":11,"message":"strategy=column but no birth_code_column."}"
"ERROR: strategy=column but column NULL."
exit 11
}
if ! echo "$CODE_COLUMN" | grep -qE '^[a-z_][a-z0-9_]$'; then
emit "{"status":"error","exit_code":11,"message":"Invalid column: $CODE_COLUMN"}"
"ERROR: Invalid column '$CODE_COLUMN'."
exit 11
fi
COL_EXISTS=$(run_pg <<SQL
SELECT count() FROM information_schema.columns
WHERE table_schema='public' AND table_name='${COLLECTION}' AND column_name='${CODE_COLUMN}'
SQL
)
[ "${COL_EXISTS:-0}" -eq 0 ] && {
emit "{"status":"error","exit_code":11,"message":"Column '${CODE_COLUMN}' not on table."}"
"ERROR: Column '$CODE_COLUMN' not on '$COLLECTION'."
exit 11
}
TRIGGER_FUNCTION="fn_birth_registry_auto"
TRIGGER_ARGS="'${CODE_COLUMN}'"
;;
synthetic_id)
# ★ Rev 5: capture capability status for JSON output
CAP_RESULT=$(check_synthetic_fallback_capability 2>&1)
CAP_EXIT=$?
if [ $CAP_EXIT -ne 0 ]; then
SYNTHETIC_CAP_STATUS="fail"
emit "{"status":"error","exit_code":13,"collection":"$COLLECTION","synthetic_capability":"fail","message":"$CAP_RESULT"}"
"ERROR: Synthetic fallback not detected. $CAP_RESULT"
exit 13
fi
SYNTHETIC_CAP_STATUS="pass"
SENTINEL_COL=$(run_pg <<SQL
SELECT count(*) FROM information_schema.columns
WHERE table_schema='public' AND table_name='${COLLECTION}' AND column_name='${SYNTHETIC_SENTINEL}'
SQL
)
[ "${SENTINEL_COL:-0}" -gt 0 ] && {
emit "{"status":"error","exit_code":11,"message":"Sentinel collision: ${SYNTHETIC_SENTINEL}"}"
"ERROR: Sentinel collision."
exit 11
}
TRIGGER_FUNCTION="fn_birth_registry_auto"
TRIGGER_ARGS="'${SYNTHETIC_SENTINEL}'"
;;
legacy_id_single_colon)
FN_ID_EXISTS=$(run_pg <<SQL
SELECT to_regprocedure('fn_birth_registry_auto_id()') IS NOT NULL
SQL
)
if [ "$FN_ID_EXISTS" != "t" ]; then
emit "{"status":"error","exit_code":13,"collection":"$COLLECTION","message":"fn_birth_registry_auto_id() not found."}"
"ERROR: fn_birth_registry_auto_id() not found."
exit 13
fi
TRIGGER_FUNCTION="fn_birth_registry_auto_id"
TRIGGER_ARGS=""
;;
*)
emit "{"status":"error","exit_code":11,"message":"Unknown strategy: $STRATEGY"}"
"ERROR: Unknown strategy '$STRATEGY'."
exit 11
;;
esac
═══ BUILD TRIGGER ═══
TRIGGER_NAME="trg_birth_${COLLECTION}" if [ -n "$TRIGGER_ARGS" ]; then CREATE_SQL="CREATE TRIGGER ${TRIGGER_NAME} AFTER INSERT ON public.${COLLECTION} FOR EACH ROW EXECUTE FUNCTION ${TRIGGER_FUNCTION}(${TRIGGER_ARGS});" else CREATE_SQL="CREATE TRIGGER ${TRIGGER_NAME} AFTER INSERT ON public.${COLLECTION} FOR EACH ROW EXECUTE FUNCTION ${TRIGGER_FUNCTION}();" fi
═══ FN HASH (audit only) ═══
FN_HASH_BEFORE=$(run_pg <<SQL SELECT md5(pg_get_functiondef('fn_birth_registry_auto'::regproc)) SQL )
═══ MODE DISPATCH ═══
case "$MODE" in
dry-run)
EXISTING_COUNT=$(run_pg <<SQL
SELECT count() FROM pg_trigger WHERE tgname='${TRIGGER_NAME}' AND NOT tgisinternal
SQL
)
TRIG_EXISTS=$([ "${EXISTING_COUNT:-0}" -gt 0 ] && echo true || echo false)
# ★ Rev 5: include synthetic_capability in JSON when applicable
local_cap=""
[ -n "$SYNTHETIC_CAP_STATUS" ] && local_cap=","synthetic_capability":"$SYNTHETIC_CAP_STATUS""
emit "{"status":"dry-run","exit_code":0,"collection":"$COLLECTION","strategy":"$STRATEGY","code_column":"${CODE_COLUMN}","trigger_name":"$TRIGGER_NAME","function":"$TRIGGER_FUNCTION","args":"$TRIGGER_ARGS","trigger_exists":$TRIG_EXISTS,"proposed_sql":"$CREATE_SQL","fn_hash":"$FN_HASH_BEFORE"${local_cap}}"
"DRY-RUN: $COLLECTION (strategy=$STRATEGY)
Trigger: $TRIGGER_NAME → $TRIGGER_FUNCTION($TRIGGER_ARGS)
Exists: $TRIG_EXISTS
SQL: $CREATE_SQL
fn hash: $FN_HASH_BEFORE$([ -n "$SYNTHETIC_CAP_STATUS" ] && echo "
synthetic_capability: $SYNTHETIC_CAP_STATUS")"
exit 0
;;
execute)
EXISTING_COUNT=$(run_pg <<SQL
SELECT count() FROM pg_trigger WHERE tgname='${TRIGGER_NAME}' AND NOT tgisinternal
SQL
)
[ "${EXISTING_COUNT:-0}" -gt 0 ] && {
emit "{"status":"skipped","exit_code":0,"collection":"$COLLECTION","message":"Already exists (idempotent)."}"
"SKIP: '$TRIGGER_NAME' already exists."
exit 0
}
run_pg <<SQL
BEGIN; ${CREATE_SQL} COMMIT;
SQL
POST_COUNT=$(run_pg <<SQL
SELECT count(*) FROM pg_trigger WHERE tgname='${TRIGGER_NAME}' AND NOT tgisinternal
SQL
)
[ "${POST_COUNT:-0}" -eq 0 ] && {
emit "{"status":"error","exit_code":20,"collection":"$COLLECTION","message":"Trigger not found after CREATE."}"
"ERROR: Trigger creation failed."
exit 20
}
FN_HASH_AFTER=$(run_pg <<SQL
SELECT md5(pg_get_functiondef('fn_birth_registry_auto'::regproc))
SQL
)
[ "$FN_HASH_BEFORE" != "$FN_HASH_AFTER" ] && {
emit "{"status":"critical","exit_code":12,"message":"fn hash drifted!"}" "CRITICAL: fn hash drifted!"
exit 12
}
emit "{"status":"success","exit_code":0,"collection":"$COLLECTION","trigger_name":"$TRIGGER_NAME","strategy":"$STRATEGY","fn_hash":"$FN_HASH_AFTER"}"
"SUCCESS: Created '$TRIGGER_NAME' (strategy=$STRATEGY)."
exit 0
;;
verify)
EXISTING_FUNC=$(run_pg <<SQL || true
SELECT tgfoid::regproc FROM pg_trigger WHERE tgname='${TRIGGER_NAME}' AND NOT tgisinternal
SQL
)
[ -z "$EXISTING_FUNC" ] && {
emit "{"status":"absent","exit_code":14,"collection":"$COLLECTION","trigger_name":"$TRIGGER_NAME"}"
"VERIFY: '$TRIGGER_NAME' absent."
exit 14
}
EXISTING_FUNC=$(echo "$EXISTING_FUNC" | tr -d '[:space:]')
[ "$EXISTING_FUNC" != "$TRIGGER_FUNCTION" ] && {
emit "{"status":"mismatch","exit_code":14,"expected":"$TRIGGER_FUNCTION","actual":"$EXISTING_FUNC"}"
"MISMATCH: Expected $TRIGGER_FUNCTION, got $EXISTING_FUNC."
exit 14
}
if [ -n "$TRIGGER_ARGS" ]; then
TRIG_DEF=$(run_pg <<SQL || true
SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname='${TRIGGER_NAME}' AND NOT tgisinternal
SQL
)
if ! echo "$TRIG_DEF" | grep -qF "$TRIGGER_ARGS"; then
echo "MISMATCH: Args $TRIGGER_ARGS not in trigger def." >&2
emit "{"status":"mismatch","exit_code":14,"collection":"$COLLECTION","expected_args":"$TRIGGER_ARGS"}" ""
exit 14
fi
fi
emit "{"status":"verified","exit_code":0,"collection":"$COLLECTION","trigger_name":"$TRIGGER_NAME","function":"$EXISTING_FUNC","fn_hash":"$FN_HASH_BEFORE"}"
"VERIFY OK: '$TRIGGER_NAME' → $EXISTING_FUNC. fn=$FN_HASH_BEFORE."
exit 0
;;
drop)
run_pg <<SQL
DROP TRIGGER IF EXISTS ${TRIGGER_NAME} ON public.${COLLECTION};
SQL
FN_HASH_AFTER=$(run_pg <<SQL
SELECT md5(pg_get_functiondef('fn_birth_registry_auto'::regproc))
SQL
)
[ "$FN_HASH_BEFORE" != "$FN_HASH_AFTER" ] && {
emit "{"status":"critical","exit_code":12,"message":"fn hash drifted during drop!"}" "CRITICAL: fn hash drifted!"
exit 12
}
emit "{"status":"dropped","exit_code":0,"collection":"$COLLECTION","trigger_name":"$TRIGGER_NAME"}"
"DROPPED: '$TRIGGER_NAME' on '$COLLECTION'."
exit 0
;;
esac