What causes "Failed to migrate execution_transaction table: {e}" in nautechsystems/nautilus_trader?

Inspect {e} and match the SQLSTATE: 23505 unique violation, 23514 check violation, 42P01 undefined table, 42501 insufficient privilege

nautechsystems/nautilus_trader · error · anyhow::Error

Failed to migrate execution_transaction table: {e}

Error message

Failed to migrate execution_transaction table: {e}

What it means

Thrown when one of the DDL statements building the v2 execution schema fails inside ensure_execution_transaction_schema: ALTER TABLE on execution_transaction, CREATE TABLE for execution_intent / execution_transaction_hash / execution_transaction_transition, the unique partial indexes, or the re-added execution_intent_active_check. The wrapped error names the exact problem, such as duplicate key values defeating a unique index, rows violating the CHECK, a missing chain FK target, or missing DDL privileges.

Source

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

                from_status TEXT,
                to_status TEXT NOT NULL,
                block_number BIGINT,
                block_hash TEXT,
                observed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                CHECK (block_number IS NULL OR block_number >= 0),
                CHECK (to_status IN (
                    'prepared', 'signed', 'broadcast', 'included', 'finalized',
                    'reverted', 'replaced', 'dropped', 'reorged', 'recoverable'
                )),
                UNIQUE (intent_id, transition_key)
            )
            ",
        ] {
            sqlx::query(statement)
                .execute(&mut *transaction)
                .await
                .map_err(|e| {
                    anyhow::anyhow!("Failed to migrate execution_transaction table: {e}")
                })?;
        }

        let installed_version = sqlx::query_scalar::<_, i16>(
            "SELECT version FROM execution_schema_version WHERE component = 'evm_execution'",
        )
        .fetch_optional(&mut *transaction)
        .await
        .map_err(|e| anyhow::anyhow!("Failed to read execution schema version: {e}"))?;
        if let Some(installed_version) = installed_version
            && installed_version > EXECUTION_SCHEMA_VERSION
        {
            anyhow::bail!(
                "Execution schema version {} is newer than supported version {EXECUTION_SCHEMA_VERSION}",
                installed_version
            );
        }

View on GitHub (pinned to 2114cf6f76)

Solutions

Inspect {e} and match the SQLSTATE: 23505 unique violation, 23514 check violation, 42P01 undefined table, 42501 insufficient privilege
Deduplicate offending rows (e.g. deactivate all but one active intent per signer) so the unique indexes can build
Ensure the chain table exists and contains every chain_id used by intents (FK ON DELETE RESTRICT)
Run the migration as the schema owner or grant the required CREATE/ALTER privileges

Defensive patterns

Strategy: try-catch

Validate before calling

-- Pre-flight the conditions the DDL depends on
SELECT chain_id FROM chain; -- FK target must exist
SELECT chain_id, wallet_address, COUNT(*) FROM execution_intent
WHERE active GROUP BY 1, 2 HAVING COUNT(*) > 1; -- would break the unique index

Try / catch

if let Err(e) = db.ensure_execution_transaction_schema().await {
    let msg = e.to_string();
    if msg.contains("duplicate key") {
        // deduplicate active intents, then re-run
    } else if msg.contains("violates check constraint") {
        // repair offending rows, then re-run
    } else if msg.contains("permission denied") {
        // fix role privileges, then re-run
    } else {
        return Err(e);
    }
}

Prevention

Never hand-edit the execution schema; let the migration be the only writer of DDL
Validate data against the new CHECK and unique constraints before upgrading (dry-run on a restore)
Grant the migrating role CREATE/ALTER/TRIGGER on the schema upfront
Test the upgrade on a copy of production data so constraint violations surface before the live run

When it happens

Trigger: CREATE UNIQUE INDEX execution_intent_active_signer_key on a database already holding two active intents for the same (chain_id, wallet_address); ADD CONSTRAINT execution_intent_active_check on rows violating it; execution_intent referencing chain(chain_id) when the chain table does not exist; a role without CREATE privilege.

Common situations: Re-running the migration against a partially-migrated or hand-edited schema; databases whose v1 data came from a modified build; least-privilege deployment roles.

Related errors

AI-assisted analysis of nautechsystems/nautilus_trader@2114cf6f76 (2026-08-21). Data as JSON: /api/errors/0df7b7191efe9a4f. Report an issue: GitHub.