P9 G6 Trigger Guard Incident Investigation

Date: 2026-04-28
Executor: Codex CLI via SSH contabo
Scope: read-only investigation + fix proposal only
Verdict: incident understood; no mutation executed

0. Boundary

Hard exclusions observed:

• No DDL/DML/CREATE TABLE executed.
• No ALTER/DROP event trigger.
• No ALTER/DROP function.
• No event trigger disable.
• No trigger_guard_alerts creation.
• No G6 retry / G8 / G11 / P9 production mutation.
• No public.tac_*, Directus, labels, issues, dot log writes.
• No git commit/push.

Hostname check:

vmi3080463

1. Documents read

• .claude/skills/incomex-rules.md: 36 mục / 8 bước; direct search_knowledge, no background agent.
• knowledge/current-state/reports/trigger-guard-v2-d26-complete: docs say event trigger guard on both directus and incomex_metadata; event trigger real-time + DOT-316 daily enforcement.
• knowledge/current-state/reports/trigger-guard-d26-p3-report: docs say trigger_guard_config exists in both DBs and trigger_guard_alerts exists in both DBs; DOT-316 dot-trigger-guard active, cron 15 3 * * *.
• DOT registry KB search for DOT-316 / dot-trigger-guard.
• Đ26 / Đ31 searches: trigger/counting guard and system integrity verification are protective infrastructure; do not bypass blindly.

2. 3 câu Tuyên ngôn

Câu	Answer
Vĩnh viễn?	Fix gốc phải make guard self-contained under any caller search_path, not patch one G6 run.
Nhầm được không?	Guard functions should schema-qualify internal tables and use owned execution context, so authorized DDL cannot accidentally break logging.
100% tự động?	DOT-316/Đ31 post-verify should continuously assert event triggers, function definitions, table existence, privileges, and a controlled smoke path.

3. Findings

3.1 Event triggers active on both DBs

directus:

evt_trigger_guard_ddl  | ddl_command_end | O | public | fn_evt_trigger_guard      | workflow_admin
evt_trigger_guard_drop | sql_drop        | O | public | fn_evt_trigger_guard_drop | workflow_admin

incomex_metadata:

evt_trigger_guard_ddl  | ddl_command_end | O | public | fn_evt_trigger_guard      | workflow_admin
evt_trigger_guard_drop | sql_drop        | O | public | fn_evt_trigger_guard_drop | workflow_admin

3.2 Function definition is unsafe under restricted search_path

Both DBs have identical function metadata:

public.fn_evt_trigger_guard      owner=workflow_admin security_definer=f proconfig=NULL alerts_ref_pos=480 config_ref_pos=0 exception_ref_pos=0
public.fn_evt_trigger_guard_drop owner=workflow_admin security_definer=f proconfig=NULL alerts_ref_pos=265 config_ref_pos=0 exception_ref_pos=0

The body uses unqualified trigger_guard_alerts:

INSERT INTO trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)

The body does not reference trigger_guard_config or trigger_guard_exceptions.

Read-only resolution proof with G6-style path:

directus:

session_search_path=p9_g6_dryrun,pg_catalog
unqualified_lookup=NULL
qualified_lookup=public.trigger_guard_alerts

incomex_metadata:

session_search_path=p9_g6_dryrun,pg_catalog
unqualified_lookup=NULL
qualified_lookup=public.trigger_guard_alerts

Root cause of G6 run #3 DDL failure:

G6 bundle sets search_path = p9_g6_dryrun, pg_catalog. The event trigger function is SECURITY INVOKER, has no function-level search_path, and inserts into unqualified trigger_guard_alerts. During trigger DDL, PostgreSQL resolves trigger_guard_alerts against the caller search path, cannot find it, and raises:

ERROR: relation "trigger_guard_alerts" does not exist
CONTEXT: PL/pgSQL function public.fn_evt_trigger_guard() line 17 at SQL statement

The table exists in public; the failure is name resolution, not physical absence.

3.3 Guard tables and privileges

directus objects:

public.trigger_guard_alerts            table owner=workflow_admin
public.trigger_guard_alerts_id_seq     sequence owner=workflow_admin
public.trigger_guard_exceptions        table owner=workflow_admin
public.trigger_guard_exceptions_id_seq sequence owner=workflow_admin

directus rows:

trigger_guard_alerts_rows=73
trigger_guard_exceptions_rows=27
latest_alert=2026-04-26 14:58:48.453288+00
unresolved=73

directus current role privilege:

directus rolsuper=f rolcreaterole=f rolcreatedb=f
can_insert_alerts=t can_select_alerts=t can_select_exceptions=t

incomex_metadata objects:

public.trigger_guard_alerts            table owner=workflow_admin
public.trigger_guard_alerts_id_seq     sequence owner=workflow_admin
public.trigger_guard_exceptions        table owner=workflow_admin
public.trigger_guard_exceptions_id_seq sequence owner=workflow_admin

incomex_metadata rows:

trigger_guard_alerts_rows=2
trigger_guard_exceptions_rows=0

incomex_metadata current role privilege:

directus rolsuper=f rolcreaterole=f rolcreatedb=f
can_insert_alerts=f can_select_alerts=t can_select_exceptions=t

trigger_guard_config was not found in either DB by catalog search, despite docs saying it exists.

3.4 Docs comparison / drift

Docs expected:

• Event trigger guard on both DBs: matches.
• trigger_guard_alerts on both DBs: matches.
• trigger_guard_config on both DBs: drift, absent on both DBs.
• DOT-316 active cron enforcement: registry says active, but last_executed is blank in dot_tools.
• Exceptions mechanism: table exists, but event trigger functions do not consult it.

Conclusion: incomex_metadata is not a healthy control DB for this specific incident. It has the same function-level search_path defect, plus weaker INSERT privilege for role directus.

3.5 DOT-316 status

directus.public.dot_tools:

id=946
code=DOT-316
name=dot-trigger-guard
status=active
tier=A
trigger_type=cron
cron_schedule=15 3 * * *
paired_dot=DOT-KB-PROTECT, DOT-PIVOT-HEALTH, DOT-GOV-VERIFY
last_executed=NULL
extra_metadata={}

Registry status is active. Evidence gap: no runtime last_executed evidence in dot_tools.

3.6 Whitelist / exception path

There is a public.trigger_guard_exceptions table:

id, table_name, trigger_name, reason, temporary_until, created_at, created_by

directus has 27 permanent exception rows for known count triggers. incomex_metadata has 0 rows.

However, both event trigger functions have exception_ref_pos=0; they do not read the exception table. Therefore the current event trigger layer has no effective whitelist/exception mechanism for authorized DDL on p9_g6_dryrun.

4. Answers to dispatch questions

#	Question	Answer
1	Active event triggers per DB	Both DBs have evt_trigger_guard_ddl and evt_trigger_guard_drop, enabled O, targeting public guard functions.
2	fn_evt_trigger_guard definition per DB	Same on both DBs: owner workflow_admin, SECURITY INVOKER, no proconfig, unqualified trigger_guard_alerts.
3	Guard tables per DB	trigger_guard_alerts and trigger_guard_exceptions exist in public on both DBs. trigger_guard_config absent on both.
4	Docs match/drift	Partial match; event triggers and alerts exist, config table missing, function ignores exceptions, DOT last evidence blank.
5	DOT-316 status	Registered as id=946, code=DOT-316, active tier A cron 15 3 * * *; last_executed=NULL.
6	Whitelist/exception for schema test	Table exists but not enforced by current function; effective answer is no.
7	Fix options	See below.

5. Fix options, not executed

Option A — Preferred root fix: make event trigger functions schema-safe and owned

Purpose: keep the guard active, but make it independent from caller search_path and role grants.

Pre-check:

SELECT current_database(), current_user;
SELECT e.evtname, e.evtenabled, p.proname
FROM pg_event_trigger e
JOIN pg_proc p ON p.oid=e.evtfoid
ORDER BY e.evtname;
SELECT to_regclass('public.trigger_guard_alerts') AS alerts_table,
       to_regclass('public.trigger_guard_exceptions') AS exceptions_table;
SELECT n.nspname, p.proname, pg_get_userbyid(p.proowner), p.prosecdef, 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');

Mutation SQL, per DB (directus and incomex_metadata), to be executed only after GPT/User authorization:

BEGIN;

CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  r RECORD;
  v_cmd TEXT;
  v_obj_type TEXT;
  v_schema TEXT;
  v_obj_name TEXT;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    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);
      RAISE WARNING '[TRIGGER-GUARD] DDL detected: % on %', v_cmd, v_obj_name;
    END IF;
  END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard_drop()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF r.object_type = 'trigger' THEN
      INSERT INTO public.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;
$$;

ALTER FUNCTION public.fn_evt_trigger_guard() OWNER TO workflow_admin;
ALTER FUNCTION public.fn_evt_trigger_guard_drop() OWNER TO workflow_admin;

COMMIT;

Rollback/compensation:

BEGIN;
CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$ ... previous definition captured from pg_get_functiondef ... $$;
CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$ ... previous definition captured from pg_get_functiondef ... $$;
ALTER FUNCTION public.fn_evt_trigger_guard() OWNER TO workflow_admin;
ALTER FUNCTION public.fn_evt_trigger_guard_drop() OWNER TO workflow_admin;
COMMIT;

Post-verify:

SELECT p.proname, p.prosecdef, p.proconfig,
       position('public.trigger_guard_alerts' in pg_get_functiondef(p.oid)) AS qualified_ref
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 set_config('search_path','p9_g6_dryrun,pg_catalog', true),
       to_regclass('trigger_guard_alerts') AS unqualified_lookup,
       to_regclass('public.trigger_guard_alerts') AS qualified_lookup;

Controlled smoke, if authorized:

BEGIN;
SET LOCAL search_path = p9_g6_dryrun, pg_catalog;
CREATE SCHEMA IF NOT EXISTS tg_guard_smoke;
CREATE TABLE tg_guard_smoke.t(id int);
CREATE FUNCTION tg_guard_smoke.f() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;
CREATE TRIGGER trg_tg_guard_smoke BEFORE INSERT ON tg_guard_smoke.t FOR EACH ROW EXECUTE FUNCTION tg_guard_smoke.f();
ROLLBACK;

Related laws: Đ26 (guard must stay active), Đ31 (system integrity verification), Đ33 (isolated schema/rollback), Đ35 (DOT enforcement), Đ32/Đ39 + Constitution (authorized change only).

Option B — Tactical G6 compatibility: include public in G6 dry-run search_path

Purpose: avoid current guard failure without mutating guard infrastructure. This is not a full root fix because other callers can still set a restricted search path.

Pre-check:

SELECT set_config('search_path','p9_g6_dryrun,public,pg_catalog', true),
       to_regclass('trigger_guard_alerts') AS unqualified_lookup,
       to_regclass('public.trigger_guard_alerts') AS qualified_lookup;

Mutation command, runner/package only, not DB:

perl -0pi -e \"s/SET search_path = p9_g6_dryrun, pg_catalog;/SET search_path = p9_g6_dryrun, public, pg_catalog;/g\" g6_artifacts/ddl/g6_execution_bundle.sql
bash -n g6_artifacts/bin/run_g6.sh

Rollback:

perl -0pi -e \"s/SET search_path = p9_g6_dryrun, public, pg_catalog;/SET search_path = p9_g6_dryrun, pg_catalog;/g\" g6_artifacts/ddl/g6_execution_bundle.sql

Post-verify:

SELECT current_setting('search_path');
SELECT to_regclass('trigger_guard_alerts'), to_regclass('public.trigger_guard_alerts');

Related laws: Đ33 (G6 schema isolation), Đ32/Đ39 (explicit retry authorization), Đ26/Đ31 caveat: this option does not repair guard drift.

Option C — Full Đ26/DOT-316 reconciliation

Purpose: align implementation with docs: config/exception-aware guard, DOT-316 evidence, and multi-DB parity.

Pre-check:

SELECT to_regclass('public.trigger_guard_config'),
       to_regclass('public.trigger_guard_alerts'),
       to_regclass('public.trigger_guard_exceptions');
SELECT id, code, status, tier, trigger_type, cron_schedule, last_executed
FROM public.dot_tools
WHERE code='DOT-316' OR name='dot-trigger-guard';

Mutation outline, after separate design review:

-- DB: directus and incomex_metadata
-- 1. Create or restore public.trigger_guard_config only if the law-approved schema is confirmed.
-- 2. Replace both guard functions with schema-qualified, SECURITY DEFINER, exception-aware versions.
-- 3. Update DOT-316 executor evidence path so last_executed/health evidence is populated.

Rollback:

-- Restore captured pg_get_functiondef outputs.
-- Drop only newly-created config table if created by the same authorized change and unused.
-- Revert DOT-316 metadata via approved DOT path.

Post-verify:

SELECT count(*) FROM public.trigger_guard_alerts;
SELECT count(*) FROM public.trigger_guard_exceptions;
SELECT to_regclass('public.trigger_guard_config');
SELECT id, code, status, last_executed FROM public.dot_tools WHERE code='DOT-316';

Related laws: Đ26 and Đ31 primary; Đ35 for DOT metadata path; Đ33 for controlled smoke schemas; Đ32/Đ39/Constitution for authorization chain.

6. Recommendation

Recommended path: Option A before any G6 retry. It fixes the root cause while preserving the guard. Option B can unblock G6 but leaves the event trigger fragile for any future DDL session with restricted search_path. Option C should follow as governance cleanup because docs and runtime are already divergent (trigger_guard_config missing, exceptions table ignored, DOT-316 last evidence blank).

7. Evidence gaps

• trigger_guard_config schema could not be recovered from live DB because the table is absent on both DBs.
• DOT-316 runtime execution evidence is not visible in dot_tools.last_executed.
• No write-path smoke test was executed because this investigation is read-only.

8. Secret hygiene scan

LEAK_SCAN_PASS targets=1 patterns=9 disallowed_matches=0