{"record":{"id":"0ed0b120fcbbeb97","repo":"nautechsystems/nautilus_trader","slug":"missing-block-timestamp-for-pool-snapshot-at-bl","errorCode":null,"errorMessage":"Missing block timestamp for pool snapshot {} at block {}","messagePattern":"Missing block timestamp for pool snapshot (.+?) at block (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":2444,"sourceCode":"            let transaction_hash: String = row.get(\"transaction_hash\");\n            let observed_block_hash = row.try_get::<Option<String>, _>(\"block_hash\")?;\n            let block =\n                u64::try_from(block).with_context(|| \"Pool snapshot block number is negative\")?;\n            let transaction_index = u32::try_from(transaction_index)\n                .with_context(|| \"Pool snapshot transaction index is negative\")?;\n            let log_index =\n                u32::try_from(log_index).with_context(|| \"Pool snapshot log index is negative\")?;\n            let block_hash = if transaction_index == BLOCK_SCOPED_SNAPSHOT_INDEX\n                && log_index == BLOCK_SCOPED_SNAPSHOT_INDEX\n            {\n                Some(transaction_hash.clone())\n            } else {\n                observed_block_hash\n            };\n            let block_timestamp = row\n                .try_get::<Option<String>, _>(\"block_timestamp\")?\n                .ok_or_else(|| {\n                    anyhow::anyhow!(\n                        \"Missing block timestamp for pool snapshot {} at block {}\",\n                        pool_identifier,\n                        block\n                    )\n                })?;\n            let timestamp = parse_cached_block_timestamp(&block_timestamp)\n                .map_err(|e| anyhow::anyhow!(\"Invalid block timestamp '{block_timestamp}': {e}\"))?;\n\n            let block_position =\n                BlockPosition::new(block, transaction_hash, transaction_index, log_index)\n                    .with_block_hash(block_hash);\n\n            let fee_protocol_value = row.get::<i16, _>(\"fee_protocol\");\n            let fee_protocol = u8::try_from(fee_protocol_value).with_context(|| {\n                format!(\"Invalid pool snapshot fee protocol {fee_protocol_value}\")\n            })?;\n            let fee_protocol0_basis_points = row\n                .get::<Option<i32>, _>(\"fee_protocol0_basis_points\")","sourceCodeStart":2426,"sourceCodeEnd":2462,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L2426-L2462","documentation":"The snapshot row was found and read, but its `block_timestamp` column contained SQL NULL when a non-null timestamp string is required. The loader needs the timestamp to reconstruct the snapshot's block time via `parse_cached_block_timestamp`. This indicates a data-integrity problem in the snapshot table rather than a query failure.","triggerScenarios":"Loading a pool snapshot row whose `block_timestamp` column is NULL — typically a row written by an older writer version before the column existed, a manually inserted/edited row, or a snapshot persisted by a code path that did not populate timestamps.","commonSituations":"Schema migration added `block_timestamp` after rows were already written (backfill not run); legacy snapshots from a previous adapter version; manual DB repairs or restores that dropped the column value.","solutions":["Backfill NULL `block_timestamp` values for existing snapshot rows from block data (or from the chain RPC by block number).","Invalidate or delete the affected snapshot rows so the loader can fall back to a newer valid snapshot.","Upgrade the writer so all snapshots persist a timestamp, then re-snapshot the pools.","Add a NOT NULL constraint plus default/backfill in migration to catch this at write time going forward."],"exampleFix":"// SQL backfill\n// before: block_timestamp IS NULL\nUPDATE pool_snapshots SET block_timestamp = to_char(to_timestamp(blocks.timestamp), 'YYYY-MM-DD\"T\"HH24:MI:SSZ')\nFROM blocks WHERE pool_snapshots.block = blocks.number AND pool_snapshots.block_timestamp IS NULL;\n// after: enforce at schema level\nALTER TABLE pool_snapshots ALTER COLUMN block_timestamp SET NOT NULL;","handlingStrategy":"validation","validationCode":"let ts: Option<String> = sqlx::query_scalar(\n    \"SELECT block_timestamp FROM pool_snapshots WHERE pool_identifier = $1 AND block = $2\")\n    .bind(pool_id).bind(block).fetch_one(&db).await?;\nif ts.as_deref().unwrap_or_default().is_empty() {\n    return Err(\"snapshot row has missing block_timestamp; re-snapshot required\");\n}","typeGuard":null,"tryCatchPattern":"match load_snapshot(...).await {\n    Err(e) if e.to_string().contains(\"Missing block timestamp\") => {\n        // treat row as corrupt: invalidate and fall back to a newer snapshot\n        store.set_validation_state(..., Invalid).await?;\n        load_newer_snapshot().await\n    }\n    other => other,\n}","preventionTips":["Backfill block_timestamp immediately after any migration that adds the column.","Add NOT NULL constraints so NULL timestamps cannot be persisted.","Add a startup integrity check that rejects snapshots with NULL critical columns."],"tags":["database","data-integrity","null-value","snapshot-loading"],"backgroundTag":"null-argument","analyzedSha":"18893faf8b356be3320add8de2f861b0b647cf06","analyzedAt":"2026-09-08T20:49:34.690Z","contentChangedAt":"2026-09-08T20:49:34.690Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}