MCPJVM-364: External verification state persistence on SQLite

[nimbly-dev]

Description

External verification introduces HTTP- and SQL-backed verification outcomes that should be persisted as part of regression execution state rather than remaining only in transient execution memory or scattered evidence files. A structured persisted model will support later deterministic reporting and query behavior while preserving canonical Artifacts.

This story adds SQLite-backed persistence for external verification execution state and outcome summaries.

Scope

In scope:

• Persist external verification execution state into the SQLite-backed store
• Persist verification identity, provider type, final status, and summary result metadata
• Persist deterministic linkage back to canonical run evidence or verification-related Artifacts
• Support both HTTP-based and SQL-based verification result persistence under a generic model
• Keep runtime inserts system-owned and triggered internally by verification execution flow

Out of scope:

• Provider runtime implementation details for HTTP or SQL verification
• Public MCP query/read contract design
• Replacing canonical execution evidence files
• Performance Suite integration

Expected Tables / Core Structures

Table / Structure	Purpose
external_verifications	persisted verification identity, provider type, status, summary result fields
artifacts	link verification rows back to canonical evidence or verification-related Artifacts

Expected external_verifications direction includes fields such as run_id, project_name, plan_name, verification_name, provider_type, status, reason_code, connection_ref, actual_path, expected_value_text, and bounded request/response summary fields.

Acceptance Criteria

• Regression Suite persists external verification execution state into the SQLite-backed store
• Persisted verification rows capture provider type and final outcome deterministically
• Persisted verification state links back to canonical execution evidence where applicable
• External verification persistence behaves fail-closed when required storage writes cannot complete
• The persisted model is sufficient to support later MCP query/read stories

Implementation Notes

• Persist provider-neutral summary fields first; keep vendor/provider-specific raw detail in canonical evidence where possible.
• Keep SQL/HTTP verification persistence generic so later providers do not force schema churn immediately.

Testing

• UT for provider-neutral verification row mapping
• UT for fail-closed persistence behavior on invalid or incomplete verification outcomes
• IT for HTTP verification persistence
• IT for SQL verification persistence