nautechsystems/nautilus_trader · error · anyhow::Error

Missing block timestamp for pool snapshot {} at block {}

Error message

Missing block timestamp for pool snapshot {} at block {}

What it means

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.

Source

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

            let transaction_hash: String = row.get("transaction_hash");
            let observed_block_hash = row.try_get::<Option<String>, _>("block_hash")?;
            let block =
                u64::try_from(block).with_context(|| "Pool snapshot block number is negative")?;
            let transaction_index = u32::try_from(transaction_index)
                .with_context(|| "Pool snapshot transaction index is negative")?;
            let log_index =
                u32::try_from(log_index).with_context(|| "Pool snapshot log index is negative")?;
            let block_hash = if transaction_index == BLOCK_SCOPED_SNAPSHOT_INDEX
                && log_index == BLOCK_SCOPED_SNAPSHOT_INDEX
            {
                Some(transaction_hash.clone())
            } else {
                observed_block_hash
            };
            let block_timestamp = row
                .try_get::<Option<String>, _>("block_timestamp")?
                .ok_or_else(|| {
                    anyhow::anyhow!(
                        "Missing block timestamp for pool snapshot {} at block {}",
                        pool_identifier,
                        block
                    )
                })?;
            let timestamp = parse_cached_block_timestamp(&block_timestamp)
                .map_err(|e| anyhow::anyhow!("Invalid block timestamp '{block_timestamp}': {e}"))?;

            let block_position =
                BlockPosition::new(block, transaction_hash, transaction_index, log_index)
                    .with_block_hash(block_hash);

            let fee_protocol_value = row.get::<i16, _>("fee_protocol");
            let fee_protocol = u8::try_from(fee_protocol_value).with_context(|| {
                format!("Invalid pool snapshot fee protocol {fee_protocol_value}")
            })?;
            let fee_protocol0_basis_points = row
                .get::<Option<i32>, _>("fee_protocol0_basis_points")

View on GitHub (pinned to 18893faf8b)

Solutions

  1. Backfill NULL `block_timestamp` values for existing snapshot rows from block data (or from the chain RPC by block number).
  2. Invalidate or delete the affected snapshot rows so the loader can fall back to a newer valid snapshot.
  3. Upgrade the writer so all snapshots persist a timestamp, then re-snapshot the pools.
  4. Add a NOT NULL constraint plus default/backfill in migration to catch this at write time going forward.

Example fix

// SQL backfill
// before: block_timestamp IS NULL
UPDATE pool_snapshots SET block_timestamp = to_char(to_timestamp(blocks.timestamp), 'YYYY-MM-DD"T"HH24:MI:SSZ')
FROM blocks WHERE pool_snapshots.block = blocks.number AND pool_snapshots.block_timestamp IS NULL;
// after: enforce at schema level
ALTER TABLE pool_snapshots ALTER COLUMN block_timestamp SET NOT NULL;
Defensive patterns

Strategy: validation

Validate before calling

let ts: Option<String> = sqlx::query_scalar(
    "SELECT block_timestamp FROM pool_snapshots WHERE pool_identifier = $1 AND block = $2")
    .bind(pool_id).bind(block).fetch_one(&db).await?;
if ts.as_deref().unwrap_or_default().is_empty() {
    return Err("snapshot row has missing block_timestamp; re-snapshot required");
}

Try / catch

match load_snapshot(...).await {
    Err(e) if e.to_string().contains("Missing block timestamp") => {
        // treat row as corrupt: invalidate and fall back to a newer snapshot
        store.set_validation_state(..., Invalid).await?;
        load_newer_snapshot().await
    }
    other => other,
}

Prevention

When it happens

Trigger: 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.

Common situations: 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.

Related errors

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