E-R3 Execution Log — 2026-04-27

Agent: Codex (GPT-5)
Gate: GPT SUPERVISION PASS
Prompt: E-R3 Execution v0.4

Environment

• DB_USER: workflow_admin
• DB_USER == workflow_admin: yes
• Database: directus
• Schema: public
• Runtime path: ssh contabo → source /opt/incomex/.env → docker exec postgres psql -U "$DB_USER" -d directus

Pre-checks

#	Check	Output	Status
0	DB_USER == workflow_admin	DB_USER=workflow_admin	PASS
1	Target NOT in public	empty output / 0 rows	PASS
1b	Target in other schema?	empty output / no other schema function found	NOTE
2	fn_log_issue signature (10 params)	p_source text, p_severity text, p_title text, p_description text DEFAULT NULL::text, p_entity_type text DEFAULT NULL::text, p_entity_code text DEFAULT NULL::text, p_issue_type text DEFAULT NULL::text, p_evidence jsonb DEFAULT NULL::jsonb, p_run_id text DEFAULT NULL::text, p_status text DEFAULT 'open'::text	PASS
3	system_issues 8 columns + types	coalesce_key|text|text; evidence_snapshot|json|json; id|integer|int4; last_seen_at|timestamp with time zone|timestamptz; occurrence_count|integer|int4; severity|character varying|varchar; status|character varying|varchar; title|character varying|varchar	PASS
3b	evidence_snapshot type	json	ghi nhận
3c	occurrence_count type	integer/int4	ghi nhận
4	workflow_admin role exists	1	PASS

SQL Materialization

• evidence_snapshot type detected: json
• Assignment line chosen: evidence_snapshot = COALESCE(p_details::json, evidence_snapshot)
• Final SQL logged before execute: yes

Execution

#	Step	Output	Status
6	CREATE FUNCTION	CREATE FUNCTION	PASS
6b	ALTER OWNER	ALTER FUNCTION	PASS
7a	Schema = public	fn_tac_log_checker_issue|public|...	PASS
7b	Verify 6 params	p_checker_id text, p_severity text, p_entity_code text, p_summary text, p_issue_signature text DEFAULT NULL::text, p_details jsonb DEFAULT NULL::jsonb	PASS
7c	Verify owner = workflow_admin	workflow_admin	PASS
7d	Verify SECURITY DEFINER = true	t	PASS
7e	Verify search_path = public, pg_catalog	{"search_path=public, pg_catalog"}	PASS

Full verify output:

fn_tac_log_checker_issue|public|p_checker_id text, p_severity text, p_entity_code text, p_summary text, p_issue_signature text DEFAULT NULL::text, p_details jsonb DEFAULT NULL::jsonb|workflow_admin|t|{"search_path=public, pg_catalog"}

DDL Applied

CREATE OR REPLACE FUNCTION public.fn_tac_log_checker_issue(
    p_checker_id       TEXT,
    p_severity         TEXT,
    p_entity_code      TEXT,
    p_summary          TEXT,
    p_issue_signature  TEXT DEFAULT NULL,
    p_details          JSONB DEFAULT NULL
) RETURNS INTEGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog
AS $$
DECLARE
    v_mapped_sev    TEXT;
    v_sev_rank      INTEGER;
    v_existing_id   INTEGER;
    v_existing_rank INTEGER;
    v_dedup_key     TEXT;
    v_new_id        INTEGER;
BEGIN
    -- 1. Map P6 severity
    v_mapped_sev := CASE upper(btrim(p_severity))
        WHEN 'BLOCK' THEN 'critical'
        WHEN 'ERROR' THEN 'critical'
        WHEN 'WARN'  THEN 'warning'
        WHEN 'INFO'  THEN 'info'
        ELSE NULL
    END;
    IF v_mapped_sev IS NULL THEN
        RAISE EXCEPTION 'fn_tac_log_checker_issue: invalid severity %',
            p_severity;
    END IF;
    v_sev_rank := CASE v_mapped_sev
        WHEN 'critical' THEN 3 WHEN 'warning' THEN 2 ELSE 1 END;

    -- 2. Compute dedup key (normalize inputs)
    v_dedup_key := md5(
        lower(btrim(p_checker_id)) || '|' ||
        btrim(COALESCE(p_entity_code, '')) || '|' ||
        btrim(COALESCE(p_issue_signature, ''))
    );

    -- 3. Check existing open issue
    SELECT id,
           CASE severity
               WHEN 'critical' THEN 3 WHEN 'warning' THEN 2 ELSE 1
           END
    INTO v_existing_id, v_existing_rank
    FROM public.system_issues
    WHERE coalesce_key = v_dedup_key AND status = 'open'
    LIMIT 1;

    -- 4a. Existing → UPDATE (escalate + refresh)
    IF v_existing_id IS NOT NULL THEN
        UPDATE public.system_issues SET
            last_seen_at = NOW(),
            occurrence_count = COALESCE(occurrence_count, 1) + 1,
            severity = CASE
                WHEN v_sev_rank > v_existing_rank THEN v_mapped_sev
                ELSE severity
            END,
            title = p_summary,
            evidence_snapshot = COALESCE(p_details::json, evidence_snapshot)
        WHERE id = v_existing_id;
        RETURN v_existing_id;
    END IF;

    -- 4b. New → INSERT via fn_log_issue + backfill coalesce_key
    v_new_id := public.fn_log_issue(
        btrim(p_checker_id),
        v_mapped_sev,
        p_summary,
        NULL,
        'tac_logical_unit',
        btrim(p_entity_code),
        btrim(p_checker_id),
        p_details,
        NULL,
        'open'
    );
    UPDATE public.system_issues SET coalesce_key = v_dedup_key WHERE id = v_new_id;
    RETURN v_new_id;
END;
$$;

ALTER FUNCTION public.fn_tac_log_checker_issue(TEXT,TEXT,TEXT,TEXT,TEXT,JSONB)
    OWNER TO workflow_admin;

Kết luận

• Verdict: PASS
• Rollback: Không cần
• DDL adjustment: evidence_snapshot là json, nên dùng COALESCE(p_details::json, evidence_snapshot)
• Scope guard: Không test INSERT/UPDATE/DELETE runtime, không ghi system_issues, không sửa fn_log_issue, không chạy E4/E5/E7/P9.