{"record":{"id":"77b1112bb8361db2","repo":"nautechsystems/nautilus_trader","slug":"failed-to-install-verification-schema-e","errorCode":null,"errorMessage":"Failed to install verification schema: {e}","messagePattern":"Failed to install verification schema: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":3978,"sourceCode":"            \",\n            \"\n            CREATE TABLE IF NOT EXISTS execution_replacement_scan (\n                intent_id BIGINT PRIMARY KEY REFERENCES execution_intent(id) ON DELETE RESTRICT,\n                chain_id INTEGER NOT NULL,\n                wallet_address TEXT NOT NULL,\n                nonce BIGINT NOT NULL CHECK (nonce >= 0),\n                finalized_cursor_number BIGINT NOT NULL CHECK (finalized_cursor_number >= 0),\n                finalized_cursor_hash TEXT NOT NULL CHECK (finalized_cursor_hash <> ''),\n                manifest_digest TEXT NOT NULL CHECK (manifest_digest <> ''),\n                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n                UNIQUE (chain_id, wallet_address, nonce)\n            )\n            \",\n        ] {\n            sqlx::query(statement)\n                .execute(&mut *transaction)\n                .await\n                .map_err(|e| anyhow::anyhow!(\"Failed to install verification schema: {e}\"))?;\n        }\n\n        sqlx::query(\n            \"LOCK TABLE execution_intent, execution_transaction_hash, \\\n             execution_verification_nonce, execution_verified_finalized_header, \\\n             execution_verification_decision, execution_replacement_scan \\\n             IN ACCESS EXCLUSIVE MODE\",\n        )\n        .execute(&mut *transaction)\n        .await\n        .map_err(|e| anyhow::anyhow!(\"Failed to lock execution verification state: {e}\"))?;\n\n        let installed_version = sqlx::query_scalar::<_, i16>(\n            \"SELECT version FROM execution_schema_version \\\n             WHERE component = 'evm_execution_verification'\",\n        )\n        .fetch_optional(&mut *transaction)\n        .await","sourceCodeStart":3960,"sourceCodeEnd":3996,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L3960-L3996","documentation":"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).","triggerScenarios":"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.","commonSituations":"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.","solutions":["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.","Verify prerequisite tables exist first (`chain`, `execution_intent`, `execution_schema_version`); the foreign keys in these CREATE statements require them.","If a same-name object of a different type exists (or an old incompatible table), drop or rename it manually, then rerun the migration.","Check the PostgreSQL server version supports the used syntax (BIGSERIAL, TEXT[], JSONB, cardinality checks — PostgreSQL 9.4+); upgrade if running an old server."],"exampleFix":"// before\n// adapter role lacking DDL rights\n-- GRANT: none\nCREATE TABLE IF NOT EXISTS execution_verification_nonce (...); -- permission denied\n\n// after\n-- run as superuser/migration role before starting the adapter\nGRANT CREATE, USAGE ON SCHEMA public TO nautilus_adapter;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO nautilus_adapter;","handlingStrategy":"try-catch","validationCode":"async fn assert_ddl_prerequisites(pool: &sqlx::PgPool) -> anyhow::Result<()> {\n    let exists: Option<i16> = sqlx::query_scalar(\n        \"SELECT 1 FROM information_schema.tables WHERE table_name IN ('chain','execution_intent') HAVING COUNT(*) = 2\",\n    )\n    .fetch_optional(pool)\n    .await?;\n    anyhow::ensure!(exists.is_some(), \"prerequisite tables chain/execution_intent missing\");\n    Ok(())\n}","typeGuard":null,"tryCatchPattern":"if let Err(e) = database.ensure_execution_verification_schema(&bootstrap).await {\n    let msg = e.to_string();\n    if msg.contains(\"Failed to install verification schema\") {\n        if msg.contains(\"permission denied\") {\n            // grant CREATE on schema to the adapter role, or run migration as a privileged user\n        } else if msg.contains(\"already exists\") || msg.contains(\"relation\") {\n            // inspect conflicting pre-existing objects / old schema version, remediate, rerun\n        } else {\n            return Err(e);\n        }\n    } else {\n        return Err(e);\n    }\n}","preventionTips":["Run migrations with a role that has CREATE privileges on the target schema.","Ensure base tables (chain, execution_intent, execution_schema_version) are installed before the verification migration.","Never hand-edit schema objects; apply all DDL through the versioned migration path.","Pin a supported PostgreSQL version (14+) in staging and production to avoid syntax/feature gaps."],"tags":["database","postgresql","schema","migration","sql"],"backgroundTag":"database-write-failed","analyzedSha":"18893faf8b356be3320add8de2f861b0b647cf06","analyzedAt":"2026-09-08T20:49:34.690Z","contentChangedAt":"2026-09-08T20:49:34.690Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}