70 lines
2.6 KiB
SQL
70 lines
2.6 KiB
SQL
-- AwoooP T2 MCP Gateway bridge backfill (24h)
|
|
-- 2026-05-12 Codex + ogt
|
|
--
|
|
-- Purpose:
|
|
-- Mirror real legacy mcp_audit_log rows into awooop_mcp_gateway_audit so
|
|
-- truth-chain can show MCP usage for recent incidents while first-class
|
|
-- Gateway migration continues. These rows are explicitly marked as bridge
|
|
-- records and policy_enforced=false; they are not proof of five-gate
|
|
-- Gateway enforcement.
|
|
--
|
|
-- Idempotency:
|
|
-- gate_result.legacy_audit_id stores the mcp_audit_log.id source key.
|
|
-- Re-running this SQL will only insert missing rows.
|
|
|
|
WITH inserted AS (
|
|
INSERT INTO awooop_mcp_gateway_audit (
|
|
project_id,
|
|
run_id,
|
|
trace_id,
|
|
agent_id,
|
|
tool_name,
|
|
input_hash,
|
|
output_hash,
|
|
gate_result,
|
|
result_status,
|
|
block_gate,
|
|
block_reason,
|
|
latency_ms,
|
|
created_at
|
|
)
|
|
SELECT
|
|
'awoooi' AS project_id,
|
|
NULL::uuid AS run_id,
|
|
LEFT(COALESCE(src.incident_id, src.session_id), 128) AS trace_id,
|
|
LEFT(COALESCE(src.agent_role, 'legacy-mcp-provider'), 128) AS agent_id,
|
|
LEFT('legacy:' || src.mcp_server || ':' || src.tool_name, 128) AS tool_name,
|
|
encode(digest(COALESCE(src.input_params::text, 'null'), 'sha256'), 'hex') AS input_hash,
|
|
CASE
|
|
WHEN src.output_result IS NULL THEN NULL
|
|
ELSE encode(digest(src.output_result::text, 'sha256'), 'hex')
|
|
END AS output_hash,
|
|
jsonb_build_object(
|
|
'schema_version', 'legacy_mcp_bridge_v1',
|
|
'gateway_path', 'legacy_backfill',
|
|
'policy_enforced', false,
|
|
'not_used_reason', 'legacy direct provider path; bridge audit only',
|
|
'legacy_audit_id', src.id::text,
|
|
'legacy_mcp_server', src.mcp_server,
|
|
'legacy_tool_name', src.tool_name,
|
|
'flywheel_node', src.flywheel_node
|
|
) AS gate_result,
|
|
CASE WHEN src.success IS TRUE THEN 'success' ELSE 'failed' END AS result_status,
|
|
NULL::smallint AS block_gate,
|
|
CASE WHEN src.success IS TRUE THEN NULL ELSE LEFT(src.error_message, 256) END AS block_reason,
|
|
src.duration_ms AS latency_ms,
|
|
src.created_at
|
|
FROM mcp_audit_log src
|
|
WHERE src.created_at > NOW() - INTERVAL '24 hours'
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM awooop_mcp_gateway_audit dst
|
|
WHERE dst.project_id = 'awoooi'
|
|
AND dst.gate_result->>'schema_version' = 'legacy_mcp_bridge_v1'
|
|
AND dst.gate_result->>'legacy_audit_id' = src.id::text
|
|
)
|
|
RETURNING call_id
|
|
)
|
|
SELECT COUNT(*) AS inserted_bridge_rows
|
|
FROM inserted;
|