Expose structured workflow failures and explicit monitoring errors

Summary

Expose structured workflow failure diagnostics, including PostgreSQL SQLSTATE when available, and stop representing monitoring-provider failures as empty/not-found results.

This is a general pg_durable reliability improvement surfaced while evaluating Oracle autonomous-transaction migration scenarios; it should not be Oracle-specific.

Problems

SQL failures lose machine-readable diagnostics

src/activities/execute_sql.rs currently converts a sqlx database error directly to free text:

let err_msg = format!("SQL execution failed: {e}");

After that conversion, callers and monitoring tools cannot reliably distinguish constraint violations, permission errors, cancellations, timeouts, connection failures, and other categories. PostgreSQL SQLSTATE, detail, hint, and related fields are no longer available as structured data.

Monitoring failures look like missing data

df.instance_info() returns an empty row set for several materially different cases:

• the instance does not exist or is hidden by RLS;
• the Tokio runtime cannot be created;
• the duroxide provider cannot be opened;
• get_instance_info() fails.

Other monitoring helpers use similar best-effort/empty behavior. This can make an outage look like “no instance” or “no executions,” precisely when operators need reliable diagnostics.

Proposed behavior

Define a backward-compatible failure envelope for node/workflow failures. At minimum, capture before stringification:

• phase/category (for example SQL execution, connection, orchestration, cancellation, timeout);
• human-readable message;
• PostgreSQL SQLSTATE when the source is sqlx::Error::Database;
• instance, execution, and failed-node identity already known to pg_durable;
• failure timestamp;
• safe optional database fields such as detail/hint only after applying the existing security policy.

Monitoring APIs must explicitly distinguish:

1. not found or not visible under RLS;
2. runtime/provider/query failure;
3. a valid instance with no result yet.

Provider failures should raise an actionable PostgreSQL error or return an explicit diagnostic contract; they must not silently collapse to the same empty result as “not found.” Preserve RLS behavior and avoid leaking whether another role owns an instance.

Acceptance criteria

• A failed SQL node exposes its PostgreSQL SQLSTATE as structured data when available.
• Non-database failures use a documented fallback category without inventing a SQLSTATE.
• Existing human-readable error output remains available for compatibility.
• df.instance_info() distinguishes provider/runtime failure from not-found/not-visible behavior without weakening RLS.
• Audit the other monitoring functions for the same empty-on-error pattern and either fix them or document why their best-effort behavior is intentional.
• Tests cover a constraint violation, permission error, connection/provider failure, missing instance, and RLS-hidden instance.
• Persisted and returned diagnostics do not expose SQL text, bound values, secrets, or row data by default.
• Documentation explains the structured fields and the monitoring error contract.

Upgrade and compatibility

Any persisted schema or monitoring return-signature change must include upgrade DDL, fresh-install/upgrade-chain equivalence, B1 behavior against supported older schemas, and treatment of existing/in-flight instances. Prefer additive fields or a versioned JSON envelope over breaking existing function signatures.

Start with src/activities/execute_sql.rs and trace how SQL and monitoring failures reach persisted or returned diagnostics, including df.instance_info() and the other monitoring helpers. Review the existing security policy, RLS behavior, schema upgrade requirements, and tests before choosing an additive or versioned contract. Done means structured SQLSTATE and fallback categories, explicit provider/runtime errors, preserved compatibility, and coverage for the listed failure and visibility cases.