nautechsystems/nautilus_trader · error · anyhow::Error

Failed to install verification schema: {e}

Error message

Failed to install verification schema: {e}

What it means

Inside the migration transaction, each `CREATE TABLE IF NOT EXISTS` statement for the verification schema (`execution_verification_nonce`, `execution_verified_finalized_header`, `execution_verification_decision`, `execution_replacement_scan`) is executed in turn; any sqlx failure is wrapped as this error with the driver message appended. Common causes are PostgreSQL rejecting the DDL — permissions, type/reference conflicts with pre-existing objects, or syntax incompatibilities — while the transaction is still open (it will be rolled back by the caller's error path).

Source

Thrown at crates/adapters/blockchain/src/cache/database.rs:3978

            ",
            "
            CREATE TABLE IF NOT EXISTS execution_replacement_scan (
                intent_id BIGINT PRIMARY KEY REFERENCES execution_intent(id) ON DELETE RESTRICT,
                chain_id INTEGER NOT NULL,
                wallet_address TEXT NOT NULL,
                nonce BIGINT NOT NULL CHECK (nonce >= 0),
                finalized_cursor_number BIGINT NOT NULL CHECK (finalized_cursor_number >= 0),
                finalized_cursor_hash TEXT NOT NULL CHECK (finalized_cursor_hash <> ''),
                manifest_digest TEXT NOT NULL CHECK (manifest_digest <> ''),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                UNIQUE (chain_id, wallet_address, nonce)
            )
            ",
        ] {
            sqlx::query(statement)
                .execute(&mut *transaction)
                .await
                .map_err(|e| anyhow::anyhow!("Failed to install verification schema: {e}"))?;
        }

        sqlx::query(
            "LOCK TABLE execution_intent, execution_transaction_hash, \
             execution_verification_nonce, execution_verified_finalized_header, \
             execution_verification_decision, execution_replacement_scan \
             IN ACCESS EXCLUSIVE MODE",
        )
        .execute(&mut *transaction)
        .await
        .map_err(|e| anyhow::anyhow!("Failed to lock execution verification state: {e}"))?;

        let installed_version = sqlx::query_scalar::<_, i16>(
            "SELECT version FROM execution_schema_version \
             WHERE component = 'evm_execution_verification'",
        )
        .fetch_optional(&mut *transaction)
        .await

View on GitHub (pinned to 18893faf8b)

Solutions

  1. Read the wrapped PostgreSQL error in the message: for 'permission denied', grant CREATE on the schema to the adapter's role or run as a migration-capable user.
  2. Verify prerequisite tables exist first (`chain`, `execution_intent`, `execution_schema_version`); the foreign keys in these CREATE statements require them.
  3. If a same-name object of a different type exists (or an old incompatible table), drop or rename it manually, then rerun the migration.
  4. Check the PostgreSQL server version supports the used syntax (BIGSERIAL, TEXT[], JSONB, cardinality checks — PostgreSQL 9.4+); upgrade if running an old server.

Example fix

// before
// adapter role lacking DDL rights
-- GRANT: none
CREATE TABLE IF NOT EXISTS execution_verification_nonce (...); -- permission denied

// after
-- run as superuser/migration role before starting the adapter
GRANT CREATE, USAGE ON SCHEMA public TO nautilus_adapter;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO nautilus_adapter;
Defensive patterns

Strategy: try-catch

Validate before calling

async fn assert_ddl_prerequisites(pool: &sqlx::PgPool) -> anyhow::Result<()> {
    let exists: Option<i16> = sqlx::query_scalar(
        "SELECT 1 FROM information_schema.tables WHERE table_name IN ('chain','execution_intent') HAVING COUNT(*) = 2",
    )
    .fetch_optional(pool)
    .await?;
    anyhow::ensure!(exists.is_some(), "prerequisite tables chain/execution_intent missing");
    Ok(())
}

Try / catch

if let Err(e) = database.ensure_execution_verification_schema(&bootstrap).await {
    let msg = e.to_string();
    if msg.contains("Failed to install verification schema") {
        if msg.contains("permission denied") {
            // grant CREATE on schema to the adapter role, or run migration as a privileged user
        } else if msg.contains("already exists") || msg.contains("relation") {
            // inspect conflicting pre-existing objects / old schema version, remediate, rerun
        } else {
            return Err(e);
        }
    } else {
        return Err(e);
    }
}

Prevention

When it happens

Trigger: Executing the schema-install loop when PostgreSQL returns an error for one of the `CREATE TABLE IF NOT EXISTS` statements — e.g. the role lacks CREATE privilege on the schema, an incompatible table with the same name already exists, the referenced `chain` or `execution_intent` tables are missing, or the statement text conflicts with an existing object type.

Common situations: Migrating a database created by an older schema version with conflicting table definitions; running the adapter with a read-only or restricted DB role; pointing at a database where `chain`/`execution_intent` tables were never created; partial manual schema edits leaving objects in an incompatible state.

Related errors

AI-assisted analysis of nautechsystems/nautilus_trader@18893faf8b (2026-09-08). Data as JSON: /api/errors/77b1112bb8361db2. Report an issue: GitHub.