P9 E1 — fn_log_issue / system_issues Read-only Verify

Date: 2026-04-27
Mode: READ-ONLY. No DDL/DML. No wrapper. No schema change. No system_issues write.
DB path: ssh contabo → docker exec postgres psql -U "$SANDBOX_USER" -d directus
Sandbox user: ${PG_USER_DIRECTUS:-workflow_admin} from /opt/incomex/.env on VPS.

KB Inputs Read

• knowledge/dev/laws/dieu38-trien-khai/P9-tier2-remediation-design.md — R3 requires E1 read-only verification before any adapter design.
• knowledge/dev/laws/dieu38-trien-khai/index.md — index points P8 OFFICIAL to P8-implementation-design-plan-v0-1.md.
• knowledge/dev/laws/dieu38-trien-khai/P8-implementation-design-plan-v0-1.md — OFFICIAL v0.3.
• knowledge/dev/laws/dieu38-trien-khai/P6-checker-dot-design-v0-2.md — §9-10 checker I/O contract.
• reports/p9-tier1-verification-log-2026-04-27.md — A3 system_issues readiness failure and required adapter fields.

1. Function Existence / Signature

Result: FOUND. One overload only.

schema	function	args	returns
public	fn_log_issue	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	integer

Key implication: callers must supply at least source, severity, and title; other fields are nullable/defaulted.

2. Body Logic Summary

The function does three things:

1. Validates required params: p_source, p_severity, p_title.
2. Normalizes severity into only critical, warning, info.
3. Inserts one new row into system_issues and returns id.

Key snippets, shortened:

IF p_source IS NULL OR p_source = '' THEN
  RAISE EXCEPTION 'fn_log_issue: p_source required';
END IF;

v_severity := CASE lower(btrim(p_severity))
  WHEN 'warn' THEN 'warning'
  WHEN 'normal' THEN 'info'
  ELSE lower(btrim(p_severity))
END;

IF v_severity NOT IN ('critical', 'warning', 'info') THEN
  RAISE EXCEPTION 'fn_log_issue: invalid severity %, allowed critical|warning|info', p_severity;
END IF;

INSERT INTO system_issues (
  source, severity, title, description,
  entity_type, entity_code, issue_type,
  evidence_snapshot, run_id, status,
  detected_at, first_seen_at, last_seen_at
)

No secrets appeared in the function body.

3. system_issues Columns

public.system_issues exists with 28 columns:

id, code, title, description, entity_type, entity_code, issue_type, severity, source, detected_at, status, resolved_at, resolved_by, resolution, violation_hash, source_system, issue_class, business_logic_hash, run_id, first_seen_at, last_seen_at, occurrence_count, verification_contract_id, evidence_snapshot, sub_class, parent_issue_id, coalesce_key, reopen_count.

4. Mapping: fn_log_issue params → system_issues columns

fn_log_issue param	system_issues column	Notes
p_source	source	Direct.
p_severity	severity	Normalized to critical, warning, info. warn accepted as warning; normal accepted as info; error is rejected.
p_title	title	Direct. This is the nearest target for P6/P8 summary.
p_description	description	Direct optional detail.
p_entity_type	entity_type	Direct optional type.
p_entity_code	entity_code	Direct.
p_issue_type	issue_type	Direct. This is the nearest function param for P6/P8 category, but it does not populate issue_class or sub_class.
p_evidence	evidence_snapshot	Cast from jsonb to json.
p_run_id	run_id	Direct.
p_status	status	Defaults to open if null/default.
function generated	detected_at, first_seen_at, last_seen_at	Set to current timestamp/NOW.

5. Dedup / Conflict / Escalation

Question	Finding
Dedup column used?	None in function body. coalesce_key, violation_hash, and business_logic_hash exist on table but are not written by fn_log_issue.
ON CONFLICT target + action?	None. Function performs plain insert and returns the new id.
Severity escalation logic?	No escalation. Only normalization/validation. No existing-row lookup, no occurrence_count increment, no reopen_count, no critical escalation rule.

6. P6/P8 Contract Mapping

P6/P8 contract	fn_log_issue param	system_issues column	Match?
source	p_source	source	PASS direct.
severity	p_severity	severity	PARTIAL. Direct but vocabulary mismatch: P6 uses BLOCK/ERROR/WARN/INFO; function accepts critical/warning/info plus aliases warn/normal. Adapter must map ERROR/BLOCK to critical, WARN to warning, INFO to info.
category	p_issue_type	issue_type	PARTIAL. Function can carry category as issue_type; it does not populate issue_class/sub_class.
entity_code	p_entity_code	entity_code	PASS direct.
summary	p_title	title	PASS with rename: summary becomes title.
details JSONB / evidence	p_evidence	evidence_snapshot	PASS with cast to json.
checker_id	likely p_source or p_issue_type	source or issue_type	NEEDS DESIGN. P6 §10 outputs checker_id; function has no dedicated checker_id column.
run_at	none	detected_at	PARTIAL. Function sets detected_at internally; caller cannot pass run_at.
affected_entities	p_entity_type + p_entity_code or p_evidence	entity_type, entity_code, evidence_snapshot	PARTIAL. Single entity direct; multi-entity list must go in evidence.
result	none	none direct	NEEDS DESIGN. Could encode in issue_type, title, or evidence.
escalation_list	none	none direct	NEEDS DESIGN. Could encode in evidence or leave to external escalation layer.
issue_signature	none	none written	FAIL direct. Table has coalesce_key, but function does not accept or write it.

7. Feasibility Verdict

PASS: adapter feasible without ALTER, with limitations.

Feasible minimal adapter behavior:

• Map P6/P8 source → p_source.
• Map severity vocabulary before calling fn_log_issue: BLOCK/ERROR → critical, WARN → warning, INFO → info.
• Map summary → p_title.
• Map category → p_issue_type.
• Map entity_code → p_entity_code.
• Put checker-specific fields such as checker_id, result, affected_entities, details, issue_signature, and escalation hints into p_evidence.

Limits that remain after E1:

• No dedup via coalesce_key is available through current fn_log_issue.
• No ON CONFLICT behavior exists.
• No severity escalation/occurrence increment exists.
• No direct write path for issue_class, sub_class, coalesce_key, violation_hash, or occurrence_count through this function.

Therefore, a no-ALTER adapter can satisfy logging of checker findings, but cannot satisfy dedup/escalation semantics unless those semantics live outside fn_log_issue or a later approved wrapper/design step adds them.

8. E1 Close

Status: PASS with adapter constraints documented.
Next allowed step after review: design adapter only if User/GPT approve.
Not performed: wrapper creation, schema mutation, issue insertion, E2-E7.