P9 Trigger Guard DROP Bug Investigation — 2026-04-28

Executor: Claude Code via SSH contabo Scope: read-only investigation only; no DDL/DML; no mutation. DBs checked: directus, incomex_metadata Status: PASS — root cause identified; fix options documented; no function modified.

0. Boundary / Sources

3 cau Tuyen ngon

Vinh vien: fix must remove reliance on nullable pg_event_trigger_dropped_objects().object_name for DROP events.
Nham duoc khong: post-fix guard must use non-null fallback fields and schema-qualified insert, so rollback/DROP TRIGGER cannot crash the guard table constraint.
100% tu dong: fix gate must include pre-check, ALTER FUNCTION, smoke DROP TRIGGER in controlled temp object, rollback, and post-verify for both DBs.

Read sources

.claude/skills/incomex-rules.md
search_knowledge("operating rules SSOT")
search_knowledge("hiến pháp v4.0 constitution")
reports/p9-g6-trigger-guard-incident-investigation-2026-04-28.md
reports/p9-g6-trigger-guard-repair-option-a-log-2026-04-28.md
Gate A incident log: reports/p9-gate-a-production-ddl-execution-log-2026-04-28.md
PostgreSQL official docs, Event Trigger Functions: pg_event_trigger_dropped_objects() returns object_name only when schema/name uniquely identify the object; otherwise NULL. It also exposes object_identity, address_names, and address_args. Source: https://www.postgresql.org/docs/15/functions-event-triggers.html

1. Live Evidence — directus

Connectivity

current_user=directus
current_database=directus
PostgreSQL 16.13

DROP guard metadata

nspname=public
proname=fn_evt_trigger_guard_drop
owner=workflow_admin
security_definer=f
proconfig=NULL
language=plpgsql

DROP guard source, annotated

DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF r.object_type = 'trigger' THEN
      INSERT INTO trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)
      VALUES (r.object_name, r.address_names[1], TRUE, FALSE);
      RAISE WARNING '[TRIGGER-GUARD] DROPPED: trigger % on %', r.object_name, r.address_names[1];
    END IF;
  END LOOP;
END;

Annotations:

INSERT INTO trigger_guard_alerts is not schema-qualified.
Function has proconfig=NULL, so search path is not locked.
r.object_name is nullable by PostgreSQL contract for dropped objects when schema/name is not a unique identifier.
Gate A rollback stack trace proves the bad value: r.object_name = NULL, r.address_names[1] = public.
trigger_guard_alerts.trigger_name is NOT NULL; therefore INSERT crashes before rollback can continue.

DDL guard metadata/source comparison

proname=fn_evt_trigger_guard
owner=workflow_admin
security_definer=f
proconfig={"search_path=pg_catalog, public"}

DDL guard source already uses the repaired pattern:

v_cmd := r.command_tag;
v_obj_type := r.object_type;
v_schema := r.schema_name;
v_obj_name := r.object_identity;

IF v_obj_type = 'trigger' THEN
  INSERT INTO public.trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)
  VALUES (
    v_obj_name,
    COALESCE(v_schema, 'unknown'),
    TRUE,
    CASE WHEN v_cmd = 'DROP TRIGGER' THEN FALSE ELSE TRUE END
  );
END IF;

Key differences vs DROP guard:

DDL guard uses object_identity; DROP guard uses nullable object_name.
DDL guard uses COALESCE; DROP guard has no null fallback.
DDL guard schema-qualifies public.trigger_guard_alerts; DROP guard does not.
DDL guard has locked search_path; DROP guard does not.

Alert table schema

id integer NOT NULL default nextval(...)
trigger_name text NOT NULL
table_name text NOT NULL
expected_enabled boolean NOT NULL
actual_enabled boolean NOT NULL
detected_at timestamptz default now()
resolved_at timestamptz NULL
resolved_by text NULL

Prompt note: the dispatch query used created_at, but live table uses detected_at.

Recent rows from 2026-04-28

id 142 trg_tac_pm_enacted_lock on public.tac_publication_member       table_name=unknown actual=t
id 141 trg_tac_pm_consistency on public.tac_publication_member        table_name=unknown actual=t
id 140 trg_tac_enacted_immut on public.tac_unit_version               table_name=unknown actual=t
id 139 trg_tac_birth_gate_uv on public.tac_unit_version               table_name=unknown actual=t
id 138 trg_tac_birth_gate_lu on public.tac_logical_unit                table_name=unknown actual=t
id 137 trg_tac_uv_compute_derived on public.tac_unit_version          table_name=unknown actual=t

No persisted NULL trigger_name row exists because the failing INSERT rolled back with the statement.

Event triggers

evt_trigger_guard_ddl  enabled O -> fn_evt_trigger_guard()
evt_trigger_guard_drop enabled O -> fn_evt_trigger_guard_drop()

2. Live Evidence — incomex_metadata

Connectivity

current_user=directus
current_database=incomex_metadata
PostgreSQL 16.13

DROP guard metadata/source

nspname=public
proname=fn_evt_trigger_guard_drop
owner=workflow_admin
security_definer=f
proconfig=NULL
language=plpgsql

Source is identical to directus:

FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
  IF r.object_type = 'trigger' THEN
    INSERT INTO trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)
    VALUES (r.object_name, r.address_names[1], TRUE, FALSE);
    RAISE WARNING '[TRIGGER-GUARD] DROPPED: trigger % on %', r.object_name, r.address_names[1];
  END IF;
END LOOP;

DDL guard metadata/source comparison

proname=fn_evt_trigger_guard
owner=workflow_admin
security_definer=t
proconfig={"search_path=pg_catalog, public"}

Source matches the repaired DDL guard pattern using object_identity, COALESCE, and public.trigger_guard_alerts.

Difference from directus: incomex_metadata.fn_evt_trigger_guard is SECURITY DEFINER, while directus.fn_evt_trigger_guard is SECURITY INVOKER. Both DROP guards remain SECURITY INVOKER and unpatched.

Alert table schema

Same as directus: trigger_name, table_name, expected_enabled, actual_enabled are NOT NULL; timestamp column is detected_at, not created_at.

Recent rows

id 6 trg_tg_repair_smoke_incomex_metadata_20260428032550 on tg_repair_smoke_incomex_metadata_20260428032550.t table_name=unknown actual=t

Event triggers

evt_trigger_guard_ddl  enabled O -> fn_evt_trigger_guard()
evt_trigger_guard_drop enabled O -> fn_evt_trigger_guard_drop()

3. Root Cause

fn_evt_trigger_guard_drop() assumes pg_event_trigger_dropped_objects().object_name is always non-null for trigger drops. That assumption is false.

PostgreSQL documents object_name as nullable when schema/name cannot uniquely identify the dropped object. A trigger is not uniquely identified by schema + trigger name alone; it is identified by trigger name plus relation. Therefore, during Gate A rollback, r.object_name was NULL for DROP TRIGGER, while r.address_names[1] resolved to public rather than the target table. The function then tried:

INSERT INTO trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)
VALUES (NULL, 'public', TRUE, FALSE);

That violates trigger_guard_alerts.trigger_name NOT NULL, causing the event trigger itself to abort the DROP operation. Because event triggers fire inside the DDL statement, this blocks rollback/DROP TRIGGER globally for affected trigger drops.

Secondary defects:

DROP guard insert is not schema-qualified.
DROP guard lacks locked search_path.
DROP guard was not updated when DDL guard was repaired.
Both DBs have the same DROP guard defect.

4. Fix Option A — Minimal Safe Parity With DDL Guard (Recommended)

Patch DROP guard to mirror the repaired DDL guard style:

use r.object_identity as primary trigger_name;
use COALESCE(r.schema_name, 'unknown') for table_name, matching existing DDL guard row shape;
schema-qualify public.trigger_guard_alerts;
lock search_path to pg_catalog, public;
keep current security mode per DB unless the repair gate explicitly decides otherwise.

Pre-check

Run on both DBs:

SELECT current_user, current_database();
SELECT n.nspname, p.proname, pg_get_userbyid(p.proowner) AS owner,
       p.prosecdef AS security_definer, p.proconfig
FROM pg_proc p
JOIN pg_namespace n ON n.oid=p.pronamespace
WHERE n.nspname='public'
  AND p.proname IN ('fn_evt_trigger_guard', 'fn_evt_trigger_guard_drop');

SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema='public' AND table_name='trigger_guard_alerts'
ORDER BY ordinal_position;

SELECT evtname, evtenabled, evtfoid::regprocedure
FROM pg_event_trigger
WHERE evtname IN ('evt_trigger_guard_ddl', 'evt_trigger_guard_drop');

Expected:

both event triggers enabled;
trigger_guard_alerts exists with NOT NULL audit columns;
current DROP guard source is the known defective version.

Mutation SQL

For directus (preserve SECURITY INVOKER):

BEGIN;

CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard_drop()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = pg_catalog, public
AS $function$
DECLARE
  r RECORD;
  v_trigger_name TEXT;
  v_table_name TEXT;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF r.object_type = 'trigger' THEN
      v_trigger_name := COALESCE(
        r.object_identity,
        array_to_string(r.address_names, '.'),
        '[unknown dropped trigger]'
      );
      v_table_name := COALESCE(r.schema_name, 'unknown');

      INSERT INTO public.trigger_guard_alerts
        (trigger_name, table_name, expected_enabled, actual_enabled)
      VALUES
        (v_trigger_name, v_table_name, TRUE, FALSE);

      RAISE WARNING '[TRIGGER-GUARD] DROPPED: trigger % on %',
        v_trigger_name, v_table_name;
    END IF;
  END LOOP;
END;
$function$;

ALTER FUNCTION public.fn_evt_trigger_guard_drop() OWNER TO workflow_admin;

COMMIT;

For incomex_metadata, use the same SQL. This preserves current DROP guard SECURITY INVOKER. If reviewer wants security mode parity with its DDL guard, see Option B.

Rollback SQL

Rollback to the previous source should only be used if Option A breaks, because the old version is known defective:

BEGIN;

CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard_drop()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY INVOKER
AS $function$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF r.object_type = 'trigger' THEN
      INSERT INTO trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)
      VALUES (r.object_name, r.address_names[1], TRUE, FALSE);
      RAISE WARNING '[TRIGGER-GUARD] DROPPED: trigger % on %', r.object_name, r.address_names[1];
    END IF;
  END LOOP;
END;
$function$;

ALTER FUNCTION public.fn_evt_trigger_guard_drop() OWNER TO workflow_admin;

COMMIT;

Post-verify

Read-only catalog checks:

SELECT p.proname, pg_get_userbyid(p.proowner) AS owner, p.prosecdef, p.proconfig
FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
WHERE n.nspname='public' AND p.proname='fn_evt_trigger_guard_drop';

SELECT prosrc FROM pg_proc WHERE proname='fn_evt_trigger_guard_drop';

Smoke test in repair gate (mutation by design, not this investigation):

BEGIN;
CREATE TABLE public.tg_drop_smoke_YYYYMMDDHHMMSS (id int);
CREATE FUNCTION public.fn_tg_drop_smoke_YYYYMMDDHHMMSS()
RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;
CREATE TRIGGER trg_tg_drop_smoke_YYYYMMDDHHMMSS
BEFORE INSERT ON public.tg_drop_smoke_YYYYMMDDHHMMSS
FOR EACH ROW EXECUTE FUNCTION public.fn_tg_drop_smoke_YYYYMMDDHHMMSS();
DROP TRIGGER trg_tg_drop_smoke_YYYYMMDDHHMMSS ON public.tg_drop_smoke_YYYYMMDDHHMMSS;
DROP FUNCTION public.fn_tg_drop_smoke_YYYYMMDDHHMMSS();
DROP TABLE public.tg_drop_smoke_YYYYMMDDHHMMSS;
COMMIT;

Then verify an audit row exists and no NULL was inserted:

SELECT id, trigger_name, table_name, expected_enabled, actual_enabled, detected_at
FROM public.trigger_guard_alerts
WHERE trigger_name LIKE 'trg_tg_drop_smoke_%'
ORDER BY id DESC LIMIT 5;

Expected: one DROP row with non-null trigger_name, actual_enabled=false; no statement abort.

5. Fix Option B — Stronger Semantic Logging + Security Mode Parity

Patch DROP guard to parse object_identity into trigger/table fields and set security mode to match each DB's repaired DDL guard.

For directus: keep SECURITY INVOKER to match current DDL guard. For incomex_metadata: use SECURITY DEFINER to match current DDL guard.

Pre-check

Same as Option A, plus record DDL guard security mode:

SELECT proname, prosecdef, proconfig
FROM pg_proc
WHERE proname IN ('fn_evt_trigger_guard', 'fn_evt_trigger_guard_drop');

Mutation SQL template

Set SECURITY INVOKER in directus; set SECURITY DEFINER in incomex_metadata if reviewer approves parity.

BEGIN;

CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard_drop()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY INVOKER -- directus; use SECURITY DEFINER for incomex_metadata only if approved
SET search_path = pg_catalog, public
AS $function$
DECLARE
  r RECORD;
  v_identity TEXT;
  v_trigger_name TEXT;
  v_table_name TEXT;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF r.object_type = 'trigger' THEN
      v_identity := COALESCE(
        r.object_identity,
        array_to_string(r.address_names, '.'),
        '[unknown dropped trigger]'
      );
      v_trigger_name := COALESCE(NULLIF(regexp_replace(v_identity, '\s+on\s+.*$', ''), ''), v_identity);
      v_table_name := COALESCE(NULLIF(regexp_replace(v_identity, '^.*\s+on\s+', ''), ''), r.schema_name, 'unknown');

      INSERT INTO public.trigger_guard_alerts
        (trigger_name, table_name, expected_enabled, actual_enabled)
      VALUES
        (v_trigger_name, v_table_name, TRUE, FALSE);

      RAISE WARNING '[TRIGGER-GUARD] DROPPED: trigger % on %',
        v_trigger_name, v_table_name;
    END IF;
  END LOOP;
END;
$function$;

ALTER FUNCTION public.fn_evt_trigger_guard_drop() OWNER TO workflow_admin;

COMMIT;

Rollback SQL

Same rollback as Option A, adjusted to previous security mode if needed. This rollback restores known-defective behavior, so prefer forward fix over rollback unless the new function itself fails.

Post-verify

Same as Option A, with additional semantic check:

SELECT id, trigger_name, table_name, expected_enabled, actual_enabled, detected_at
FROM public.trigger_guard_alerts
WHERE trigger_name LIKE 'trg_tg_drop_smoke_%'
ORDER BY id DESC LIMIT 5;

Expected: trigger_name is just the trigger name or at minimum a non-null identity; table_name is the target table identity, not public and not unknown when object_identity contains on <table>.

6. Fix Option C — Table Schema Relaxation (Not Recommended)

Allow trigger_guard_alerts.trigger_name to be nullable or give it a default.

Why not recommended

This treats the symptom, not the root cause. It would allow broken audit rows and would still leave unqualified insert + unlocked search_path in the DROP guard.

Mutation shape if ever needed

ALTER TABLE public.trigger_guard_alerts
  ALTER COLUMN trigger_name DROP NOT NULL;

This violates the mission principle for this defect and should not be used as primary fix.

7. Recommendation

Recommended: Option A.

Reasoning:

It is the smallest root-cause fix.
It mirrors the already-repaired DDL guard pattern.
It avoids parsing assumptions about object_identity format.
It preserves current security mode and owner, reducing blast radius.
It fixes all three operational defects: nullable object_name, unqualified insert, and unlocked search_path.

Option B is acceptable if reviewer explicitly wants better table-name semantics and security parity per DB, but it has more parsing/security-mode surface.

8. Evidence Gaps / Notes

The prompt's audit-row query referenced created_at; live schema uses detected_at in both DBs.
No direct call to pg_event_trigger_dropped_objects() was executed; it is only valid in event trigger context.
No DROP TRIGGER smoke test was run in this investigation because scope is read-only.
No mutation was executed.

9. STOP

Investigation complete. Do not repair function in this gate.