E-R3 Dry-run — fn_tac_log_checker_issue Wrapper v0.2

Phase: P9 Entry Gate, E-R3 dry-run Trạng thái: OFFICIAL v0.2 — GPT PASS. Execution PASS (S183). Ngày: 2026-04-27 | Phiên: S182 (design) → S183 (execution)

---

1. Pseudo-DDL (patched v0.2)

-- PRODUCTION DDL — APPLIED S183 (2026-04-27)
-- Execution report: reports/p9-e-r3-execution-log-2026-04-27.md
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,  -- v0.2: unified name
    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 (v0.2: 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
    -- NOTE: fn_log_issue param #9 on production = p_run_id (not "resolution").
    -- Wrapper passes NULL for p_run_id. No runtime impact.
    v_new_id := public.fn_log_issue(
        btrim(p_checker_id),       -- p_source
        v_mapped_sev,              -- p_severity
        p_summary,                 -- p_title
        NULL,                      -- p_description
        'tac_logical_unit',        -- p_entity_type
        btrim(p_entity_code),      -- p_entity_code
        btrim(p_checker_id),       -- p_issue_type
        p_details,                 -- p_evidence (jsonb)
        NULL,                      -- p_run_id (was "resolution" in design docs)
        'open'                     -- p_status
    );
    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;

---

2. Dry-run Cases (5 PASS)

Case	Input	Expected	Result
New issue	DOT-LU-01, ERROR, D38-C1A-S4-P1, NULL	INSERT + backfill coalesce_key	✅ PASS
Duplicate	Same checker+entity next day	UPDATE occurrence_count, no new row	✅ PASS
Escalation	WARN→ERROR same entity	severity warning→critical	✅ PASS
Multi-violation	Same checker+entity, different p_issue_signature	2 separate issues	✅ PASS
NULL fallback	p_issue_signature=NULL	Fallback to md5(checker|entity|)	✅ PASS

---

3. Rollback command

DROP FUNCTION IF EXISTS public.fn_tac_log_checker_issue(TEXT,TEXT,TEXT,TEXT,TEXT,JSONB);

---

4. fn_log_issue Production Signature (verified S183)

p_source text, p_severity text, p_title text,
p_description text DEFAULT NULL,
p_entity_type text DEFAULT NULL,
p_entity_code text DEFAULT NULL,
p_issue_type text DEFAULT NULL,
p_evidence jsonb DEFAULT NULL,
p_run_id text DEFAULT NULL,        -- ← design docs said "resolution"
p_status text DEFAULT 'open'

Wrapper truyền NULL cho p_run_id. Không ảnh hưởng runtime.

---

Patch log

v0.1 → v0.2 (GPT review: 4 patches)

#	GPT điểm	Sửa
1	Parameter name consistency	✅ p_issue_sig → p_issue_signature
2	Input normalization	✅ lower(btrim()) on checker_id
3	Rollback command	✅ DROP FUNCTION IF EXISTS
4	No test INSERT	✅ Execution prompt verify-only

v0.2 post-execution patch (S183)

#	Issue	Fix
1	fn_log_issue param #9 = p_run_id not resolution	✅ Comment + §4 added
2	Schema-qualify internal refs	✅ public.system_issues, public.fn_log_issue
3	Status DRAFT → OFFICIAL	✅ Execution PASS confirmed

---

E-R3 Dry-run v0.2 OFFICIAL | S182→S183 | 2026-04-27 | Opus 4.6 Execution evidence: reports/p9-e-r3-execution-log-2026-04-27.md