{"record":{"id":"0df7b7191efe9a4f","repo":"nautechsystems/nautilus_trader","slug":"failed-to-migrate-execution-transaction-table-e","errorCode":null,"errorMessage":"Failed to migrate execution_transaction table: {e}","messagePattern":"Failed to migrate execution_transaction table: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":3219,"sourceCode":"                from_status TEXT,\n                to_status TEXT NOT NULL,\n                block_number BIGINT,\n                block_hash TEXT,\n                observed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n                CHECK (block_number IS NULL OR block_number >= 0),\n                CHECK (to_status IN (\n                    'prepared', 'signed', 'broadcast', 'included', 'finalized',\n                    'reverted', 'replaced', 'dropped', 'reorged', 'recoverable'\n                )),\n                UNIQUE (intent_id, transition_key)\n            )\n            \",\n        ] {\n            sqlx::query(statement)\n                .execute(&mut *transaction)\n                .await\n                .map_err(|e| {\n                    anyhow::anyhow!(\"Failed to migrate execution_transaction table: {e}\")\n                })?;\n        }\n\n        let installed_version = sqlx::query_scalar::<_, i16>(\n            \"SELECT version FROM execution_schema_version WHERE component = 'evm_execution'\",\n        )\n        .fetch_optional(&mut *transaction)\n        .await\n        .map_err(|e| anyhow::anyhow!(\"Failed to read execution schema version: {e}\"))?;\n        if let Some(installed_version) = installed_version\n            && installed_version > EXECUTION_SCHEMA_VERSION\n        {\n            anyhow::bail!(\n                \"Execution schema version {} is newer than supported version {EXECUTION_SCHEMA_VERSION}\",\n                installed_version\n            );\n        }\n","sourceCodeStart":3201,"sourceCodeEnd":3237,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/2114cf6f761429e0adb5ca9596fcd7b895b16011/crates/adapters/blockchain/src/cache/database.rs#L3201-L3237","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":null,"handlingStrategy":"try-catch","validationCode":"-- Pre-flight the conditions the DDL depends on\nSELECT chain_id FROM chain; -- FK target must exist\nSELECT chain_id, wallet_address, COUNT(*) FROM execution_intent\nWHERE active GROUP BY 1, 2 HAVING COUNT(*) > 1; -- would break the unique index","typeGuard":null,"tryCatchPattern":"if let Err(e) = db.ensure_execution_transaction_schema().await {\n    let msg = e.to_string();\n    if msg.contains(\"duplicate key\") {\n        // deduplicate active intents, then re-run\n    } else if msg.contains(\"violates check constraint\") {\n        // repair offending rows, then re-run\n    } else if msg.contains(\"permission denied\") {\n        // fix role privileges, then re-run\n    } else {\n        return Err(e);\n    }\n}","preventionTips":["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"],"tags":["rust","sqlx","postgresql","ddl","unique-index","check-constraint","migration"],"backgroundTag":"schema-migration-failed","analyzedSha":"2114cf6f761429e0adb5ca9596fcd7b895b16011","analyzedAt":"2026-08-21T11:28:30.864Z","schemaVersion":2},"datasetVersion":"2026-08-22T04:17:13.399Z"}