03 — System Issue Semantic Idempotency

Problem

fn_log_issue did a blind INSERT on every call. The executor header (lines 66-68) specified "Case A (no row same key): INSERT" — idempotency the function never implemented. The schema already had the columns for it: coalesce_key, violation_hash, run_id, first_seen_at, last_seen_at, occurrence_count, reopen_count — all unused (coalesce_key/violation_hash 100% NULL across 8,154 rows/24h, 1,015 distinct semantic keys).

Patch applied (same signature, fail-safe)

Semantic key = md5(lower(source|entity_type|entity_code|issue_type|title)).

• Case B (key already open/active): UPDATE last_seen_at=now(), occurrence_count=occurrence_count+1, run_id, evidence_snapshot, severity; RETURN existing id. No new row → no birth.
• Case A (no match): INSERT with coalesce_key populated, occurrence_count=1; RETURN new id. (Fail-safe: brand-new findings still recorded.)

Title is included in the key so genuinely different findings never merge; the anti-loop title embeds the entity code so it is stable per entity across runs → repeats coalesce.

Rehearsal (live BEGIN..ROLLBACK)

3 identical calls → id 227319 each; count=1, occurrence_count=3; births: first insert +1, repeats +0. Proves "second identical finding → no new row → no new birth."

Views

v_system_issue_semantic_duplicate_dashboard (rows vs distinct keys, legacy NULL-key count, est. duplicates), v_system_issue_idempotency_guard (fn does coalesce lookup + increments occurrence), v_system_issue_idempotency_patch_plan (APPLIED). Optional defense-in-depth partial unique index on coalesce_key WHERE status IN (open,active) is STAGED (historical rows are NULL-key so the partial index is conflict-free).