Skip to main content

nautilus_blockchain/cache/
mod.rs

1// -------------------------------------------------------------------------------------------------
2//  Copyright (C) 2015-2026 Nautech Systems Pty Ltd. All rights reserved.
3//  https://nautechsystems.io
4//
5//  Licensed under the GNU Lesser General Public License Version 3.0 (the "License");
6//  You may not use this file except in compliance with the License.
7//  You may obtain a copy of the License at https://www.gnu.org/licenses/lgpl-3.0.en.html
8//
9//  Unless required by applicable law or agreed to in writing, software
10//  distributed under the License is distributed on an "AS IS" BASIS,
11//  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12//  See the License for the specific language governing permissions and
13//  limitations under the License.
14// -------------------------------------------------------------------------------------------------
15
16//! Caching layer for blockchain entities and domain objects.
17//!
18//! This module provides an in-memory cache with optional PostgreSQL persistence for storing
19//! and retrieving blockchain-related data such as blocks, tokens, pools, swaps, and other
20//! DeFi protocol events.
21
22use std::{
23    collections::{BTreeMap, HashMap, HashSet},
24    str::FromStr,
25    sync::Arc,
26};
27
28use alloy::primitives::{Address, B256};
29use nautilus_core::UnixNanos;
30use nautilus_model::defi::{
31    Block, DexType, Pool, PoolIdentifier, PoolLiquidityUpdate, PoolSwap, SharedChain, SharedDex,
32    SharedPool, Token,
33    data::{PoolFeeCollect, PoolFeeProtocolCollect, PoolFeeProtocolUpdate, PoolFlash},
34    pool_analysis::{position::PoolPosition, snapshot::PoolSnapshot},
35    tick_map::tick::PoolTick,
36};
37use sqlx::postgres::PgConnectOptions;
38
39use crate::{
40    cache::{
41        consistency::CachedBlocksConsistencyStatus,
42        database::BlockchainCacheDatabase,
43        rows::{ExecutionTransactionRow, PoolRow},
44    },
45    events::initialize::InitializeEvent,
46};
47
48pub mod consistency;
49pub mod copy;
50pub mod database;
51pub mod rows;
52pub mod types;
53
54#[derive(Debug, Clone, Default, PartialEq, Eq)]
55pub(crate) struct PoolEventSyncState {
56    pub version: u32,
57    pub last_full_sync_block: Option<u64>,
58    pub family_blocks: Vec<(String, u64)>,
59}
60
61/// Provides caching functionality for various blockchain domain objects.
62#[derive(Debug)]
63pub struct BlockchainCache {
64    /// The blockchain chain this cache is associated with.
65    chain: SharedChain,
66    /// Map of block numbers to their corresponding timestamp
67    block_timestamps: BTreeMap<u64, UnixNanos>,
68    /// Map of block numbers to the hashes observed with those timestamps.
69    block_hashes: BTreeMap<u64, B256>,
70    /// Map of DEX identifiers to their corresponding DEX objects.
71    dexes: HashMap<DexType, SharedDex>,
72    /// Map of token addresses to their corresponding `Token` objects.
73    tokens: HashMap<Address, Token>,
74    /// Cached set of invalid token addresses that failed validation or processing.
75    invalid_tokens: HashSet<Address>,
76    /// Map of pool identifiers to their corresponding `Pool` objects.
77    pools: HashMap<PoolIdentifier, SharedPool>,
78    /// Optional database connection for persistent storage.
79    pub database: Option<BlockchainCacheDatabase>,
80}
81
82impl BlockchainCache {
83    /// Creates a new in-memory blockchain cache for the specified chain.
84    #[must_use]
85    pub fn new(chain: SharedChain) -> Self {
86        Self {
87            chain,
88            dexes: HashMap::new(),
89            tokens: HashMap::new(),
90            invalid_tokens: HashSet::new(),
91            pools: HashMap::new(),
92            block_timestamps: BTreeMap::new(),
93            block_hashes: BTreeMap::new(),
94            database: None,
95        }
96    }
97
98    /// Returns the highest continuous block number currently cached, if any.
99    pub async fn get_cache_block_consistency_status(
100        &self,
101    ) -> Option<CachedBlocksConsistencyStatus> {
102        let database = self.database.as_ref()?;
103        database
104            .get_block_consistency_status(&self.chain)
105            .await
106            .map_err(|e| log::error!("Error getting block consistency status: {e}"))
107            .ok()
108    }
109
110    /// Returns the earliest block number where any DEX in the cache was created on the blockchain.
111    #[must_use]
112    pub fn min_dex_creation_block(&self) -> Option<u64> {
113        self.dexes
114            .values()
115            .map(|dex| dex.factory_creation_block)
116            .min()
117    }
118
119    /// Returns the timestamp for the specified block number if it exists in the cache.
120    #[must_use]
121    pub fn get_block_timestamp(&self, block_number: u64) -> Option<&UnixNanos> {
122        self.block_timestamps.get(&block_number)
123    }
124
125    /// Returns the observed hash for the specified block number, if cached.
126    #[must_use]
127    pub fn get_block_hash(&self, block_number: u64) -> Option<B256> {
128        self.block_hashes.get(&block_number).copied()
129    }
130
131    fn parse_block_hash(hash: &str) -> anyhow::Result<B256> {
132        B256::from_str(hash).map_err(|e| anyhow::anyhow!("Invalid block hash {hash}: {e}"))
133    }
134
135    /// Records a block timestamp in the in-memory cache without persisting it.
136    ///
137    /// Used while streaming pool events so event conversion can resolve `ts_event` for blocks
138    /// that have not been persisted via [`Self::add_block`].
139    pub fn cache_block_timestamp(&mut self, number: u64, timestamp: UnixNanos) {
140        self.block_timestamps.insert(number, timestamp);
141    }
142
143    /// Records the hash and timestamp observed for one block without persisting it.
144    ///
145    /// # Errors
146    ///
147    /// Returns an error if the block hash is not valid 32-byte hex.
148    pub fn cache_block_metadata(&mut self, block: &Block) -> anyhow::Result<()> {
149        let hash = Self::parse_block_hash(&block.hash)?;
150        self.block_timestamps.insert(block.number, block.timestamp);
151        self.block_hashes.insert(block.number, hash);
152        Ok(())
153    }
154
155    /// Initializes the database connection for persistent storage.
156    pub async fn initialize_database(&mut self, pg_connect_options: PgConnectOptions) {
157        let database = BlockchainCacheDatabase::init(pg_connect_options).await;
158        self.database = Some(database);
159    }
160
161    /// Returns whether a persistent database is attached to the cache.
162    #[must_use]
163    pub const fn has_database(&self) -> bool {
164        self.database.is_some()
165    }
166
167    /// Persists an execution transaction record, failing closed when no database is attached.
168    ///
169    /// # Errors
170    ///
171    /// Returns an error if no database is configured or the database operation fails.
172    #[expect(
173        clippy::too_many_arguments,
174        reason = "the parameters mirror the persisted execution transaction fields"
175    )]
176    pub async fn add_execution_transaction(
177        &self,
178        chain_id: u32,
179        wallet_address: &str,
180        nonce: u64,
181        transaction_hash: &str,
182        purpose: &str,
183        status: &str,
184        client_order_id: Option<&str>,
185    ) -> anyhow::Result<()> {
186        let database = self.database.as_ref().ok_or_else(|| {
187            anyhow::anyhow!(
188                "No durable store configured; refusing to persist execution transaction"
189            )
190        })?;
191
192        database
193            .add_execution_transaction(
194                chain_id,
195                wallet_address,
196                nonce,
197                transaction_hash,
198                purpose,
199                status,
200                client_order_id,
201            )
202            .await
203    }
204
205    /// Migrates the execution transaction table and installs its signer and order uniqueness
206    /// constraints, failing closed when no database is attached.
207    ///
208    /// # Errors
209    ///
210    /// Returns an error if no database is configured or the database operation fails.
211    pub async fn ensure_execution_transaction_schema(&self) -> anyhow::Result<()> {
212        let database = self.database.as_ref().ok_or_else(|| {
213            anyhow::anyhow!(
214                "No durable store configured; refusing to migrate execution transaction"
215            )
216        })?;
217
218        database.ensure_execution_transaction_schema().await
219    }
220
221    /// Updates the status of a persisted execution transaction record, failing closed when no
222    /// database is attached.
223    ///
224    /// # Errors
225    ///
226    /// Returns an error if no database is configured or the database operation fails.
227    pub async fn update_execution_transaction_status(
228        &self,
229        chain_id: u32,
230        transaction_hash: &str,
231        status: &str,
232    ) -> anyhow::Result<()> {
233        let database = self.database.as_ref().ok_or_else(|| {
234            anyhow::anyhow!("No durable store configured; refusing to update execution transaction")
235        })?;
236
237        database
238            .update_execution_transaction_status(chain_id, transaction_hash, status)
239            .await
240    }
241
242    /// Loads an execution transaction record by chain ID and transaction hash, failing closed
243    /// when no database is attached.
244    ///
245    /// # Errors
246    ///
247    /// Returns an error if no database is configured or the database operation fails.
248    pub async fn get_execution_transaction(
249        &self,
250        chain_id: u32,
251        transaction_hash: &str,
252    ) -> anyhow::Result<Option<ExecutionTransactionRow>> {
253        let database = self.database.as_ref().ok_or_else(|| {
254            anyhow::anyhow!("No durable store configured; refusing to load execution transaction")
255        })?;
256
257        database
258            .get_execution_transaction(chain_id, transaction_hash)
259            .await
260    }
261
262    /// Toggles performance optimization settings in the database.
263    ///
264    /// # Errors
265    ///
266    /// Returns an error if the database is not initialized or the operation fails.
267    pub async fn toggle_performance_settings(&self, enable: bool) -> anyhow::Result<()> {
268        if let Some(database) = &self.database {
269            database.toggle_perf_sync_settings(enable).await
270        } else {
271            log::debug!("Database not initialized, skipping performance settings toggle");
272            Ok(())
273        }
274    }
275
276    /// Initializes the chain by seeding it in the database and creating necessary partitions.
277    ///
278    /// This method sets up the blockchain chain in the database, creates block and token
279    /// partitions for optimal performance, and loads existing tokens into the cache.
280    pub async fn initialize_chain(&mut self) {
281        // Seed target adapter chain in database
282        if let Some(database) = &self.database {
283            if let Err(e) = database.seed_chain(&self.chain).await {
284                log::error!(
285                    "Error seeding chain in database: {e}. Continuing without database cache functionality"
286                );
287                return;
288            }
289            log::debug!("Chain seeded in the database");
290
291            if let Err(e) = database.ensure_pool_event_block_hash_schema().await {
292                log::error!("Error adding pool event block hash storage: {e}");
293                return;
294            }
295
296            match database.create_block_partition(&self.chain).await {
297                Ok(message) => log::debug!("Executing block partition creation: {message}"),
298                Err(e) => log::error!(
299                    "Error creating block partition for chain {}: {e}. Continuing without partition creation...",
300                    self.chain.chain_id
301                ),
302            }
303
304            match database.create_token_partition(&self.chain).await {
305                Ok(message) => log::debug!("Executing token partition creation: {message}"),
306                Err(e) => log::error!(
307                    "Error creating token partition for chain {}: {e}. Continuing without partition creation...",
308                    self.chain.chain_id
309                ),
310            }
311        }
312
313        if let Err(e) = self.load_tokens().await {
314            log::error!("Error loading tokens from the database: {e}");
315        }
316    }
317
318    /// Connects to the database and loads initial data.
319    ///
320    /// # Errors
321    ///
322    /// Returns an error if database seeding, token loading, or block loading fails.
323    pub async fn connect(&mut self, from_block: u64) -> anyhow::Result<()> {
324        log::debug!("Connecting and loading from_block {from_block}");
325
326        if let Err(e) = self.load_tokens().await {
327            log::error!("Error loading tokens from the database: {e}");
328        }
329
330        // TODO disable block syncing for now as we don't have timestamps yet configured
331        // if let Err(e) = self.load_blocks(from_block).await {
332        //     log::error!("Error loading blocks from database: {e}");
333        // }
334
335        Ok(())
336    }
337
338    /// Loads tokens from the database into the in-memory cache.
339    async fn load_tokens(&mut self) -> anyhow::Result<()> {
340        if let Some(database) = &self.database {
341            let (tokens, invalid_tokens) = tokio::try_join!(
342                database.load_tokens(Arc::clone(&self.chain)),
343                database.load_invalid_token_addresses(self.chain.chain_id)
344            )?;
345
346            log::debug!(
347                "Loading {} valid tokens and {} invalid tokens from cache database",
348                tokens.len(),
349                invalid_tokens.len()
350            );
351
352            self.tokens
353                .extend(tokens.into_iter().map(|token| (token.address, token)));
354            self.invalid_tokens.extend(invalid_tokens);
355        }
356        Ok(())
357    }
358
359    /// Loads DEX exchange pools from the database into the in-memory cache.
360    ///
361    /// Returns the loaded pools.
362    ///
363    /// # Errors
364    ///
365    /// Returns an error if the DEX has not been registered or if database operations fail.
366    pub async fn load_pools(&mut self, dex_id: &DexType) -> anyhow::Result<Vec<Pool>> {
367        let mut loaded_pools = Vec::new();
368
369        if let Some(database) = &self.database {
370            let dex = self
371                .get_dex(dex_id)
372                .ok_or_else(|| anyhow::anyhow!("DEX {dex_id:?} has not been registered"))?;
373            let pool_rows = database
374                .load_pools(Arc::clone(&self.chain), &dex_id.to_string())
375                .await?;
376            log::debug!(
377                "Loading {} pools for DEX {} from cache database",
378                pool_rows.len(),
379                dex_id,
380            );
381
382            for pool_row in pool_rows {
383                if let Some(pool) = self.build_pool_from_row(&pool_row, &dex) {
384                    loaded_pools.push(pool.clone());
385                    self.pools.insert(pool.pool_identifier, Arc::new(pool));
386                }
387            }
388        }
389        Ok(loaded_pools)
390    }
391
392    /// Loads a single DEX pool from the database into the in-memory cache.
393    ///
394    /// Returns the loaded pool, or `None` when it is absent from the database. Unlike
395    /// [`load_pools`](Self::load_pools), this loads only the requested pool, so per-pool tools do
396    /// not pay the cost of loading the whole DEX pool set.
397    ///
398    /// # Errors
399    ///
400    /// Returns an error if the DEX has not been registered or if database operations fail.
401    pub async fn load_pool(
402        &mut self,
403        dex_id: &DexType,
404        pool_identifier: &PoolIdentifier,
405    ) -> anyhow::Result<Option<Pool>> {
406        let dex = self
407            .get_dex(dex_id)
408            .ok_or_else(|| anyhow::anyhow!("DEX {dex_id:?} has not been registered"))?;
409
410        let pool_row = {
411            let Some(database) = &self.database else {
412                return Ok(None);
413            };
414            database
415                .load_pool(
416                    Arc::clone(&self.chain),
417                    &dex_id.to_string(),
418                    pool_identifier,
419                )
420                .await?
421        };
422
423        let Some(pool_row) = pool_row else {
424            return Ok(None);
425        };
426        let Some(pool) = self.build_pool_from_row(&pool_row, &dex) else {
427            return Ok(None);
428        };
429        self.pools
430            .insert(pool.pool_identifier, Arc::new(pool.clone()));
431        Ok(Some(pool))
432    }
433
434    /// Builds a [`Pool`] from a database row using cached tokens.
435    ///
436    /// Returns `None` (after logging the reason) when a referenced token is missing from the cache
437    /// or the stored pool identifier cannot be parsed.
438    fn build_pool_from_row(&self, pool_row: &PoolRow, dex: &SharedDex) -> Option<Pool> {
439        let Some(token0) = self.tokens.get(&pool_row.token0_address) else {
440            log::error!(
441                "Failed to load pool {} for DEX {}: Token0 with address {} not found in cache. \
442                     This may indicate the token was not properly loaded from the database or the pool references an unknown token",
443                pool_row.address,
444                dex.name,
445                pool_row.token0_address
446            );
447            return None;
448        };
449
450        let Some(token1) = self.tokens.get(&pool_row.token1_address) else {
451            log::error!(
452                "Failed to load pool {} for DEX {}: Token1 with address {} not found in cache. \
453                     This may indicate the token was not properly loaded from the database or the pool references an unknown token",
454                pool_row.address,
455                dex.name,
456                pool_row.token1_address
457            );
458            return None;
459        };
460
461        let Some(pool_identifier) = pool_row.pool_identifier.parse().ok() else {
462            log::error!(
463                "Invalid pool identifier '{}' in database for pool {}, skipping",
464                pool_row.pool_identifier,
465                pool_row.address
466            );
467            return None;
468        };
469
470        let ts_init = pool_row.creation_block_timestamp.unwrap_or_default();
471
472        let mut pool = Pool::new(
473            Arc::clone(&self.chain),
474            Arc::clone(dex),
475            pool_row.address,
476            pool_identifier,
477            pool_row.creation_block,
478            token0.clone(),
479            token1.clone(),
480            pool_row.fee,
481            pool_row.tick_spacing,
482            ts_init,
483        );
484
485        if let Some(ref hook_address_str) = pool_row.hook_address
486            && let Ok(hooks) = hook_address_str.parse()
487        {
488            pool.set_hooks(hooks);
489        }
490
491        if let Some(initial_sqrt_price_x96_str) = &pool_row.initial_sqrt_price_x96
492            && let Ok(initial_sqrt_price_x96) = initial_sqrt_price_x96_str.parse()
493            && let Some(initial_tick) = pool_row.initial_tick
494        {
495            pool.initialize(initial_sqrt_price_x96, initial_tick);
496        }
497
498        Some(pool)
499    }
500
501    /// Loads block timestamps from the database starting `from_block` number
502    /// into the in-memory cache.
503    #[allow(dead_code)]
504    async fn load_blocks(&mut self, from_block: u64) -> anyhow::Result<()> {
505        if let Some(database) = &self.database {
506            let block_timestamps = database
507                .load_block_timestamps(Arc::clone(&self.chain), from_block)
508                .await?;
509
510            // Verify block number sequence consistency
511            if !block_timestamps.is_empty() {
512                let first = block_timestamps.first().unwrap().number;
513                let last = block_timestamps.last().unwrap().number;
514                let expected_len = (last - first + 1) as usize;
515                if block_timestamps.len() != expected_len {
516                    anyhow::bail!(
517                        "Block timestamps are not consistent and sequential. Expected {expected_len} blocks but got {}",
518                        block_timestamps.len()
519                    );
520                }
521            }
522
523            if block_timestamps.is_empty() {
524                log::debug!("No blocks found in database");
525                return Ok(());
526            }
527
528            log::debug!(
529                "Loading {} blocks timestamps from the cache database with last block number {}",
530                block_timestamps.len(),
531                block_timestamps.last().unwrap().number,
532            );
533
534            for block in block_timestamps {
535                self.block_timestamps.insert(block.number, block.timestamp);
536            }
537        }
538        Ok(())
539    }
540
541    /// Adds a block to the cache and persists it to the database if available.
542    ///
543    /// # Errors
544    ///
545    /// Returns an error if adding the block to the database fails, or if the
546    /// block hash is not valid 32-byte hex.
547    pub async fn add_block(&mut self, block: Block) -> anyhow::Result<()> {
548        // Populate in-memory first so the timestamp resolves even if persistence fails
549        self.cache_block_metadata(&block)?;
550        if let Some(database) = &self.database {
551            database.add_block(self.chain.chain_id, &block).await?;
552        }
553        Ok(())
554    }
555
556    /// Adds multiple blocks to the cache and persists them to the database in batch if available.
557    ///
558    /// # Errors
559    ///
560    /// Returns an error if adding the blocks to the database fails, or if a
561    /// block hash is not valid 32-byte hex.
562    pub async fn add_blocks_batch(
563        &mut self,
564        blocks: Vec<Block>,
565        use_copy_command: bool,
566    ) -> anyhow::Result<()> {
567        if blocks.is_empty() {
568            return Ok(());
569        }
570
571        // Validate every hash before persisting so a bad hash leaves no partial state.
572        let hashes = blocks
573            .iter()
574            .map(|block| Self::parse_block_hash(&block.hash))
575            .collect::<anyhow::Result<Vec<_>>>()?;
576
577        if let Some(database) = &self.database {
578            if use_copy_command {
579                database
580                    .add_blocks_copy(self.chain.chain_id, &blocks)
581                    .await?;
582            } else {
583                database
584                    .add_blocks_batch(self.chain.chain_id, &blocks)
585                    .await?;
586            }
587        }
588
589        // Update in-memory cache
590        for (block, hash) in blocks.into_iter().zip(hashes) {
591            self.block_timestamps.insert(block.number, block.timestamp);
592            self.block_hashes.insert(block.number, hash);
593        }
594
595        Ok(())
596    }
597
598    /// Adds block timestamps observed while streaming pool events.
599    ///
600    /// # Errors
601    ///
602    /// Returns an error if adding the block timestamps to the database fails,
603    /// or if a block hash is not valid 32-byte hex.
604    pub async fn add_pool_event_blocks_batch(&mut self, blocks: Vec<Block>) -> anyhow::Result<()> {
605        if blocks.is_empty() {
606            return Ok(());
607        }
608
609        // Validate every hash before persisting so a bad hash leaves no partial state.
610        let hashes = blocks
611            .iter()
612            .map(|block| Self::parse_block_hash(&block.hash))
613            .collect::<anyhow::Result<Vec<_>>>()?;
614
615        if let Some(database) = &self.database {
616            database
617                .add_pool_event_blocks_batch(self.chain.chain_id, &blocks)
618                .await?;
619        }
620
621        for (block, hash) in blocks.into_iter().zip(hashes) {
622            self.block_timestamps.insert(block.number, block.timestamp);
623            self.block_hashes.insert(block.number, hash);
624        }
625
626        Ok(())
627    }
628
629    /// Adds a DEX to the cache with the specified identifier.
630    ///
631    /// # Errors
632    ///
633    /// Returns an error if adding the DEX to the database fails.
634    pub async fn add_dex(&mut self, dex: SharedDex) -> anyhow::Result<()> {
635        log::debug!("Adding dex {} to the cache", dex.name);
636
637        if let Some(database) = &self.database {
638            database.add_dex(Arc::clone(&dex)).await?;
639        }
640
641        self.dexes.insert(dex.name, dex);
642        Ok(())
643    }
644
645    /// Adds a liquidity pool/pair to the cache.
646    ///
647    /// # Errors
648    ///
649    /// Returns an error if adding the pool to the database fails.
650    pub async fn add_pool(&mut self, pool: Pool) -> anyhow::Result<()> {
651        if let Some(database) = &self.database {
652            database.add_pool(&pool).await?;
653        }
654
655        self.pools.insert(pool.pool_identifier, Arc::new(pool));
656        Ok(())
657    }
658
659    /// Adds multiple pools to the cache and persists them to the database in batch if available.
660    ///
661    /// # Errors
662    ///
663    /// Returns an error if adding the pools to the database fails.
664    pub async fn add_pools_batch(&mut self, pools: Vec<Pool>) -> anyhow::Result<()> {
665        if pools.is_empty() {
666            return Ok(());
667        }
668
669        if let Some(database) = &self.database {
670            database.add_pools_copy(self.chain.chain_id, &pools).await?;
671        }
672        self.pools.extend(
673            pools
674                .into_iter()
675                .map(|pool| (pool.pool_identifier, Arc::new(pool))),
676        );
677
678        Ok(())
679    }
680
681    /// Adds a [`Token`] to the cache.
682    ///
683    /// # Errors
684    ///
685    /// Returns an error if adding the token to the database fails.
686    pub async fn add_token(&mut self, token: Token) -> anyhow::Result<()> {
687        if let Some(database) = &self.database {
688            database.add_token(&token).await?;
689        }
690        self.tokens.insert(token.address, token);
691        Ok(())
692    }
693
694    /// Adds multiple tokens to the cache and persists them to the database in batch if available.
695    ///
696    /// # Errors
697    ///
698    /// Returns an error if adding the tokens to the database fails.
699    pub async fn add_tokens_batch(&mut self, tokens: Vec<Token>) -> anyhow::Result<()> {
700        if tokens.is_empty() {
701            return Ok(());
702        }
703
704        if let Some(database) = &self.database {
705            database
706                .add_tokens_copy(self.chain.chain_id, &tokens)
707                .await?;
708        }
709
710        self.tokens
711            .extend(tokens.into_iter().map(|token| (token.address, token)));
712
713        Ok(())
714    }
715
716    /// Updates the in-memory token cache without persisting to the database.
717    pub fn insert_token_in_memory(&mut self, token: Token) {
718        self.tokens.insert(token.address, token);
719    }
720
721    /// Marks a token address as invalid in the in-memory cache without persisting to the database.
722    pub fn insert_invalid_token_in_memory(&mut self, address: Address) {
723        self.invalid_tokens.insert(address);
724    }
725
726    /// Adds an invalid token address with associated error information to the cache.
727    ///
728    /// # Errors
729    ///
730    /// Returns an error if adding the invalid token to the database fails.
731    pub async fn add_invalid_token(
732        &mut self,
733        address: Address,
734        error_string: &str,
735    ) -> anyhow::Result<()> {
736        if let Some(database) = &self.database {
737            database
738                .add_invalid_token(self.chain.chain_id, &address, error_string)
739                .await?;
740        }
741        self.invalid_tokens.insert(address);
742        Ok(())
743    }
744
745    /// Adds a [`PoolSwap`] to the cache database if available.
746    ///
747    /// # Errors
748    ///
749    /// Returns an error if adding the swap to the database fails.
750    pub async fn add_pool_swap(&self, swap: &PoolSwap) -> anyhow::Result<()> {
751        if let Some(database) = &self.database {
752            database.add_swap(self.chain.chain_id, swap).await?;
753        }
754
755        Ok(())
756    }
757
758    /// Adds a [`PoolLiquidityUpdate`] to the cache database if available.
759    ///
760    /// # Errors
761    ///
762    /// Returns an error if adding the liquidity update to the database fails.
763    pub async fn add_liquidity_update(
764        &self,
765        liquidity_update: &PoolLiquidityUpdate,
766    ) -> anyhow::Result<()> {
767        if let Some(database) = &self.database {
768            database
769                .add_pool_liquidity_update(self.chain.chain_id, liquidity_update)
770                .await?;
771        }
772
773        Ok(())
774    }
775
776    /// Adds multiple [`PoolSwap`]s to the cache database in a single batch operation if available.
777    ///
778    /// # Errors
779    ///
780    /// Returns an error if adding the swaps to the database fails.
781    pub async fn add_pool_swaps_batch(
782        &self,
783        swaps: &[PoolSwap],
784        use_copy_command: bool,
785    ) -> anyhow::Result<()> {
786        if let Some(database) = &self.database {
787            if use_copy_command {
788                database
789                    .add_pool_swaps_copy(self.chain.chain_id, swaps)
790                    .await?;
791            } else {
792                database
793                    .add_pool_swaps_batch(self.chain.chain_id, swaps)
794                    .await?;
795            }
796        }
797
798        Ok(())
799    }
800
801    /// Adds multiple [`PoolLiquidityUpdate`]s to the cache database in a single batch operation if available.
802    ///
803    /// # Errors
804    ///
805    /// Returns an error if adding the liquidity updates to the database fails.
806    pub async fn add_pool_liquidity_updates_batch(
807        &self,
808        updates: &[PoolLiquidityUpdate],
809        use_copy_command: bool,
810    ) -> anyhow::Result<()> {
811        if let Some(database) = &self.database {
812            if use_copy_command {
813                database
814                    .add_pool_liquidity_updates_copy(self.chain.chain_id, updates)
815                    .await?;
816            } else {
817                database
818                    .add_pool_liquidity_updates_batch(self.chain.chain_id, updates)
819                    .await?;
820            }
821        }
822
823        Ok(())
824    }
825
826    /// Adds a batch of pool fee collect events to the cache.
827    ///
828    /// # Errors
829    ///
830    /// Returns an error if adding the fee collects to the database fails.
831    pub async fn add_pool_fee_collects_batch(
832        &self,
833        collects: &[PoolFeeCollect],
834        use_copy_command: bool,
835    ) -> anyhow::Result<()> {
836        if let Some(database) = &self.database {
837            if use_copy_command {
838                database
839                    .copy_pool_fee_collects_batch(self.chain.chain_id, collects)
840                    .await?;
841            } else {
842                database
843                    .add_pool_collects_batch(self.chain.chain_id, collects)
844                    .await?;
845            }
846        }
847
848        Ok(())
849    }
850
851    /// Adds a batch of pool flash events to the cache.
852    ///
853    /// # Errors
854    ///
855    /// Returns an error if adding the flash events to the database fails.
856    pub async fn add_pool_flash_batch(&self, flash_events: &[PoolFlash]) -> anyhow::Result<()> {
857        if let Some(database) = &self.database {
858            database
859                .add_pool_flash_batch(self.chain.chain_id, flash_events)
860                .await?;
861        }
862
863        Ok(())
864    }
865
866    /// Adds a batch of pool fee-protocol update events to the cache database.
867    ///
868    /// # Errors
869    ///
870    /// Returns an error if adding the fee-protocol update events to the database fails.
871    pub async fn add_pool_fee_protocol_updates_batch(
872        &self,
873        updates: &[PoolFeeProtocolUpdate],
874    ) -> anyhow::Result<()> {
875        if let Some(database) = &self.database {
876            database
877                .add_pool_fee_protocol_updates_batch(self.chain.chain_id, updates)
878                .await?;
879        }
880
881        Ok(())
882    }
883
884    /// Adds a batch of pool protocol-fee withdrawal events to the cache database.
885    ///
886    /// # Errors
887    ///
888    /// Returns an error if adding the protocol-fee withdrawal events to the database fails.
889    pub async fn add_pool_fee_protocol_collect_batch(
890        &self,
891        collects: &[PoolFeeProtocolCollect],
892    ) -> anyhow::Result<()> {
893        if let Some(database) = &self.database {
894            database
895                .add_pool_fee_protocol_collect_batch(self.chain.chain_id, collects)
896                .await?;
897        }
898
899        Ok(())
900    }
901
902    /// Adds a pool snapshot to the cache database.
903    ///
904    /// This method saves the complete snapshot including:
905    /// - Pool state and analytics (pool_snapshot table)
906    /// - All positions at this snapshot (pool_position table)
907    /// - All ticks at this snapshot (pool_tick table)
908    ///
909    /// # Errors
910    ///
911    /// Returns an error if adding the snapshot to the database fails.
912    pub async fn add_pool_snapshot(
913        &self,
914        dex: &DexType,
915        pool_identifier: &PoolIdentifier,
916        snapshot: &PoolSnapshot,
917    ) -> anyhow::Result<()> {
918        // Reject stub snapshots at the pool's creation block: empty positions, empty ticks,
919        // and the snapshot block matching pool creation indicates a bootstrap that bailed
920        // before any liquidity events landed. A legitimately empty pool (e.g., fully burned)
921        // would have its last_processed_event at the burn block, not at creation, so the
922        // creation-block check preserves those valid checkpoints.
923        if snapshot.positions.is_empty()
924            && snapshot.ticks.is_empty()
925            && let Some(pool) = self.pools.get(pool_identifier)
926            && snapshot.block_position.number == pool.creation_block
927        {
928            log::warn!(
929                "Refusing to persist empty stub snapshot for {} at pool creation block {}",
930                snapshot.instrument_id,
931                snapshot.block_position.number,
932            );
933            return Ok(());
934        }
935
936        if let Some(database) = &self.database {
937            // Save snapshot first (required for foreign key constraints)
938            database
939                .add_pool_snapshot(self.chain.chain_id, dex, pool_identifier, snapshot)
940                .await?;
941
942            let positions: Vec<(PoolIdentifier, PoolPosition)> = snapshot
943                .positions
944                .iter()
945                .map(|pos| (*pool_identifier, pos.clone()))
946                .collect();
947
948            if !positions.is_empty() {
949                database
950                    .add_pool_positions_batch(
951                        self.chain.chain_id,
952                        snapshot.block_position.number,
953                        snapshot.block_position.transaction_index,
954                        snapshot.block_position.log_index,
955                        &positions,
956                    )
957                    .await?;
958            }
959
960            let ticks: Vec<(PoolIdentifier, &PoolTick)> = snapshot
961                .ticks
962                .iter()
963                .map(|tick| (*pool_identifier, tick))
964                .collect();
965
966            if !ticks.is_empty() {
967                database
968                    .add_pool_ticks_batch(
969                        self.chain.chain_id,
970                        snapshot.block_position.number,
971                        snapshot.block_position.transaction_index,
972                        snapshot.block_position.log_index,
973                        &ticks,
974                    )
975                    .await?;
976            }
977        }
978
979        Ok(())
980    }
981
982    /// Updates the initial price and tick for a pool.
983    ///
984    /// # Errors
985    ///
986    /// Returns an error if the database update fails.
987    pub async fn update_pool_initialize_price_tick(
988        &mut self,
989        initialize_event: &InitializeEvent,
990    ) -> anyhow::Result<()> {
991        if let Some(database) = &self.database {
992            database
993                .update_pool_initial_price_tick(self.chain.chain_id, initialize_event)
994                .await?;
995        }
996
997        // Update the cached pool if it exists
998        let pool_identifier = initialize_event.pool_identifier;
999        if let Some(cached_pool) = self.pools.get(&pool_identifier) {
1000            let mut updated_pool = (**cached_pool).clone();
1001            updated_pool.initialize(initialize_event.sqrt_price_x96, initialize_event.tick);
1002
1003            self.pools.insert(pool_identifier, Arc::new(updated_pool));
1004        }
1005
1006        Ok(())
1007    }
1008
1009    /// Returns a reference to the `DexExtended` associated with the given name.
1010    #[must_use]
1011    pub fn get_dex(&self, dex_id: &DexType) -> Option<SharedDex> {
1012        self.dexes.get(dex_id).cloned()
1013    }
1014
1015    /// Returns a list of registered `DexType` in the cache.
1016    #[must_use]
1017    pub fn get_registered_dexes(&self) -> HashSet<DexType> {
1018        self.dexes.keys().copied().collect()
1019    }
1020
1021    /// Returns a reference to the pool associated with the given address.
1022    #[must_use]
1023    pub fn get_pool(&self, pool_identifier: &PoolIdentifier) -> Option<&SharedPool> {
1024        self.pools.get(pool_identifier)
1025    }
1026
1027    /// Returns a reference to the `Token` associated with the given address.
1028    #[must_use]
1029    pub fn get_token(&self, address: &Address) -> Option<&Token> {
1030        self.tokens.get(address)
1031    }
1032
1033    /// Checks if a token address is marked as invalid in the cache.
1034    ///
1035    /// Returns `true` if the address was previously recorded as invalid due to
1036    /// validation or processing failures.
1037    #[must_use]
1038    pub fn is_invalid_token(&self, address: &Address) -> bool {
1039        self.invalid_tokens.contains(address)
1040    }
1041
1042    /// Saves the checkpoint block number indicating the last completed pool synchronization for a specific DEX.
1043    ///
1044    /// # Errors
1045    ///
1046    /// Returns an error if the database operation fails.
1047    pub async fn update_dex_last_synced_block(
1048        &self,
1049        dex: &DexType,
1050        block_number: u64,
1051    ) -> anyhow::Result<()> {
1052        if let Some(database) = &self.database {
1053            database
1054                .update_dex_last_synced_block(self.chain.chain_id, dex, block_number)
1055                .await
1056        } else {
1057            Ok(())
1058        }
1059    }
1060
1061    /// Updates the last synced block number for a pool.
1062    ///
1063    /// # Errors
1064    ///
1065    /// Returns an error if the database update fails.
1066    pub async fn update_pool_last_synced_block(
1067        &self,
1068        dex: &DexType,
1069        pool_identifier: &PoolIdentifier,
1070        block_number: u64,
1071    ) -> anyhow::Result<()> {
1072        if let Some(database) = &self.database {
1073            database
1074                .update_pool_last_synced_block(
1075                    self.chain.chain_id,
1076                    dex,
1077                    pool_identifier,
1078                    block_number,
1079                )
1080                .await
1081        } else {
1082            Ok(())
1083        }
1084    }
1085
1086    /// Retrieves the saved checkpoint block number from the last completed pool synchronization for a specific DEX.
1087    ///
1088    /// # Errors
1089    ///
1090    /// Returns an error if the database query fails.
1091    pub async fn get_dex_last_synced_block(&self, dex: &DexType) -> anyhow::Result<Option<u64>> {
1092        if let Some(database) = &self.database {
1093            database
1094                .get_dex_last_synced_block(self.chain.chain_id, dex)
1095                .await
1096        } else {
1097            Ok(None)
1098        }
1099    }
1100
1101    /// Retrieves the last synced block number for a pool.
1102    ///
1103    /// # Errors
1104    ///
1105    /// Returns an error if the database query fails.
1106    pub async fn get_pool_last_synced_block(
1107        &self,
1108        dex: &DexType,
1109        pool_identifier: &PoolIdentifier,
1110    ) -> anyhow::Result<Option<u64>> {
1111        if let Some(database) = &self.database {
1112            database
1113                .get_pool_last_synced_block(self.chain.chain_id, dex, pool_identifier)
1114                .await
1115        } else {
1116            Ok(None)
1117        }
1118    }
1119
1120    pub(crate) async fn get_pool_event_sync_state(
1121        &self,
1122        dex: &DexType,
1123        pool_identifier: &PoolIdentifier,
1124    ) -> anyhow::Result<PoolEventSyncState> {
1125        if let Some(database) = &self.database {
1126            database
1127                .get_pool_event_sync_state(self.chain.chain_id, dex, pool_identifier)
1128                .await
1129        } else {
1130            Ok(PoolEventSyncState::default())
1131        }
1132    }
1133
1134    pub(crate) async fn update_pool_event_sync(
1135        &self,
1136        dex: &DexType,
1137        pool_identifier: &PoolIdentifier,
1138        event_families: &[&str],
1139        block_number: u64,
1140        version: Option<u32>,
1141    ) -> anyhow::Result<()> {
1142        if let Some(database) = &self.database {
1143            database
1144                .update_pool_event_sync(
1145                    self.chain.chain_id,
1146                    dex,
1147                    pool_identifier,
1148                    event_families,
1149                    block_number,
1150                    version,
1151                )
1152                .await
1153        } else {
1154            Ok(())
1155        }
1156    }
1157
1158    /// Retrieves the maximum block number across all pool event tables for a given pool.
1159    ///
1160    /// # Errors
1161    ///
1162    /// Returns an error if any of the database queries fail.
1163    pub async fn get_pool_event_tables_last_block(
1164        &self,
1165        pool_identifier: &PoolIdentifier,
1166    ) -> anyhow::Result<Option<u64>> {
1167        if let Some(database) = &self.database {
1168            let (swaps_last_block, liquidity_last_block, collect_last_block, flash_last_block) = tokio::try_join!(
1169                database.get_table_last_block(
1170                    self.chain.chain_id,
1171                    "pool_swap_event",
1172                    pool_identifier
1173                ),
1174                database.get_table_last_block(
1175                    self.chain.chain_id,
1176                    "pool_liquidity_event",
1177                    pool_identifier
1178                ),
1179                database.get_table_last_block(
1180                    self.chain.chain_id,
1181                    "pool_collect_event",
1182                    pool_identifier
1183                ),
1184                database.get_table_last_block(
1185                    self.chain.chain_id,
1186                    "pool_flash_event",
1187                    pool_identifier
1188                ),
1189            )?;
1190
1191            let max_block = [
1192                swaps_last_block,
1193                liquidity_last_block,
1194                collect_last_block,
1195                flash_last_block,
1196            ]
1197            .into_iter()
1198            .flatten()
1199            .max();
1200            Ok(max_block)
1201        } else {
1202            Ok(None)
1203        }
1204    }
1205}
1206
1207#[cfg(test)]
1208mod tests {
1209    use std::{
1210        str::FromStr,
1211        sync::Arc,
1212        time::{SystemTime, UNIX_EPOCH},
1213    };
1214
1215    use alloy::primitives::{B256, I256, U160, U256, address};
1216    use futures_util::TryStreamExt;
1217    use nautilus_core::UnixNanos;
1218    use nautilus_infrastructure::sql::pg::{PostgresConnectOptions, get_postgres_connect_options};
1219    use nautilus_model::defi::{
1220        AmmType, Block, Blockchain, Chain, Dex, PoolProfiler, SharedChain, SharedDex, Token,
1221        data::{DexPoolData, block::BlockPosition},
1222        pool_analysis::snapshot::{PoolAnalytics, PoolSnapshot, PoolState},
1223    };
1224    use rstest::rstest;
1225    use sqlx::{
1226        AssertSqlSafe, Error as SqlxError, PgPool,
1227        postgres::{PgConnectOptions, PgPoolOptions},
1228    };
1229    use tokio_util::sync::CancellationToken;
1230    use ustr::Ustr;
1231
1232    use super::*;
1233    use crate::{
1234        config::BlockchainDataClientConfig,
1235        data::core::{BlockchainDataClientCore, SnapshotValidation},
1236    };
1237
1238    fn test_cache() -> BlockchainCache {
1239        BlockchainCache::new(Arc::new(Chain::new(Blockchain::Ethereum, 1)))
1240    }
1241
1242    #[rstest]
1243    fn cache_block_timestamp_records_in_memory() {
1244        let mut cache = test_cache();
1245        assert_eq!(cache.get_block_timestamp(100), None);
1246
1247        cache.cache_block_timestamp(100, UnixNanos::from(1_700_000_000_000_000_000));
1248
1249        assert_eq!(
1250            cache.get_block_timestamp(100),
1251            Some(&UnixNanos::from(1_700_000_000_000_000_000))
1252        );
1253    }
1254
1255    #[tokio::test]
1256    async fn add_block_populates_metadata_without_database() {
1257        let mut cache = test_cache();
1258        let block = Block::new(
1259            "0x1111111111111111111111111111111111111111111111111111111111111111".to_string(),
1260            "0x0".to_string(),
1261            42,
1262            Ustr::from("miner"),
1263            30_000_000,
1264            21_000,
1265            UnixNanos::from(1_700_000_000_000_000_000),
1266            Some(Blockchain::Ethereum),
1267        );
1268
1269        cache.add_block(block).await.unwrap();
1270
1271        assert_eq!(
1272            cache.get_block_timestamp(42),
1273            Some(&UnixNanos::from(1_700_000_000_000_000_000))
1274        );
1275        let expected =
1276            B256::from_str("0x1111111111111111111111111111111111111111111111111111111111111111")
1277                .unwrap();
1278        assert_eq!(cache.get_block_hash(42), Some(expected));
1279    }
1280
1281    #[tokio::test]
1282    async fn add_blocks_batch_rejects_invalid_hash_without_partial_cache() {
1283        let mut cache = test_cache();
1284        let ts = UnixNanos::from(1_700_000_000_000_000_000);
1285        let mut bad = test_block(13, ts);
1286        bad.hash = "0xnotahash".to_string();
1287
1288        let result = cache
1289            .add_blocks_batch(vec![test_block(12, ts), bad, test_block(14, ts)], false)
1290            .await;
1291
1292        assert!(result.is_err());
1293        assert_eq!(cache.get_block_timestamp(12), None);
1294        assert_eq!(cache.get_block_timestamp(13), None);
1295        assert_eq!(cache.get_block_timestamp(14), None);
1296        assert_eq!(cache.get_block_hash(12), None);
1297    }
1298
1299    #[tokio::test]
1300    async fn add_pool_event_blocks_batch_rejects_invalid_hash_without_partial_cache() {
1301        let mut cache = test_cache();
1302        let ts = UnixNanos::from(1_700_000_000_000_000_000);
1303        let mut bad = test_block(13, ts);
1304        bad.hash = "0xnotahash".to_string();
1305
1306        let result = cache
1307            .add_pool_event_blocks_batch(vec![test_block(12, ts), bad, test_block(14, ts)])
1308            .await;
1309
1310        assert!(result.is_err());
1311        assert_eq!(cache.get_block_timestamp(12), None);
1312        assert_eq!(cache.get_block_timestamp(13), None);
1313        assert_eq!(cache.get_block_timestamp(14), None);
1314        assert_eq!(cache.get_block_hash(12), None);
1315    }
1316
1317    #[tokio::test]
1318    async fn pool_event_block_hash_schema_upgrades_legacy_table() -> anyhow::Result<()> {
1319        let Some((database, schema)) = connect_cache_test_database().await? else {
1320            return Ok(());
1321        };
1322        execute_schema_statement(
1323            &schema.admin_pool,
1324            format!(
1325                "ALTER TABLE {}.pool_event_block DROP COLUMN hash",
1326                schema.name
1327            ),
1328        )
1329        .await?;
1330
1331        database.ensure_pool_event_block_hash_schema().await?;
1332        database
1333            .add_pool_event_blocks_batch(
1334                1,
1335                &[test_block(42, UnixNanos::from(1_700_000_000_000_000_000))],
1336            )
1337            .await?;
1338
1339        let hash: String = sqlx::query_scalar(AssertSqlSafe(format!(
1340            "SELECT hash FROM {}.pool_event_block WHERE chain_id = 1 AND number = 42",
1341            schema.name
1342        )))
1343        .fetch_one(&schema.admin_pool)
1344        .await?;
1345        let expected_hash = format!("0x{:064x}", 42);
1346        anyhow::ensure!(
1347            hash == expected_hash,
1348            "Unexpected persisted pool event block hash: {hash}"
1349        );
1350
1351        schema.cleanup().await?;
1352        Ok(())
1353    }
1354
1355    #[tokio::test]
1356    async fn stream_pool_events_uses_pool_event_block_metadata_without_full_block()
1357    -> anyhow::Result<()> {
1358        let Some((database, schema)) = connect_cache_test_database().await? else {
1359            return Ok(());
1360        };
1361        let chain = arbitrum();
1362        let dex = uniswap_v3(&chain);
1363        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
1364        let pool_identifier = PoolIdentifier::from_address(pool_address);
1365        let instrument_id = Pool::create_instrument_id(chain.name, &dex, pool_identifier.as_str());
1366        let expected_ts = UnixNanos::from(1_700_000_000_123_456_789);
1367
1368        database
1369            .add_pool_event_blocks_batch(chain.chain_id, &[test_block(12, expected_ts)])
1370            .await?;
1371        insert_pool_swap_event(
1372            &schema.admin_pool,
1373            &schema.name,
1374            chain.chain_id,
1375            &pool_identifier,
1376            12,
1377        )
1378        .await?;
1379        let events_result = database
1380            .stream_pool_events(chain, dex, instrument_id, pool_identifier, None, Some(12))
1381            .try_collect::<Vec<_>>()
1382            .await;
1383
1384        drop(database);
1385        schema.cleanup().await?;
1386
1387        let events = events_result?;
1388        let observed_metadata = match events.as_slice() {
1389            [DexPoolData::Swap(swap)] => {
1390                Some((swap.ts_event, swap.ts_init, swap.block_hash.clone()))
1391            }
1392            _ => None,
1393        };
1394
1395        let expected_metadata = Some((expected_ts, expected_ts, Some(format!("0x{:064x}", 12))));
1396        if observed_metadata != expected_metadata {
1397            anyhow::bail!(
1398                "unexpected stream metadata: expected {expected_metadata:?}, observed {observed_metadata:?}"
1399            );
1400        }
1401        Ok(())
1402    }
1403
1404    #[tokio::test]
1405    async fn stream_pool_events_round_trips_fee_protocol_update_in_order() -> anyhow::Result<()> {
1406        let Some((database, schema)) = connect_cache_test_database().await? else {
1407            return Ok(());
1408        };
1409        let chain = arbitrum();
1410        let dex = uniswap_v3(&chain);
1411        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
1412        let pool_identifier = PoolIdentifier::from_address(pool_address);
1413        let instrument_id = Pool::create_instrument_id(chain.name, &dex, pool_identifier.as_str());
1414        let ts = UnixNanos::from(1_700_000_000_000_000_000);
1415
1416        database
1417            .add_pool_event_blocks_batch(chain.chain_id, &[test_block(12, ts), test_block(13, ts)])
1418            .await?;
1419        // Swap at block 12, SetFeeProtocol at block 13: stream must order swap before update.
1420        insert_pool_swap_event(
1421            &schema.admin_pool,
1422            &schema.name,
1423            chain.chain_id,
1424            &pool_identifier,
1425            12,
1426        )
1427        .await?;
1428        // Asymmetric values (4, 6) catch a token0/token1 column swap.
1429        let update = PoolFeeProtocolUpdate::new(
1430            Arc::clone(&chain),
1431            Arc::clone(&dex),
1432            instrument_id,
1433            pool_identifier,
1434            13,
1435            "0x00000000000000000000000000000000000000000000000000000000000000ab".to_string(),
1436            0,
1437            0,
1438            4,
1439            6,
1440            ts,
1441            ts,
1442        );
1443        database
1444            .add_pool_fee_protocol_updates_batch(chain.chain_id, std::slice::from_ref(&update))
1445            .await?;
1446
1447        let events_result = database
1448            .stream_pool_events(chain, dex, instrument_id, pool_identifier, None, Some(13))
1449            .try_collect::<Vec<_>>()
1450            .await;
1451
1452        drop(database);
1453        schema.cleanup().await?;
1454
1455        let events = events_result?;
1456        match events.as_slice() {
1457            [DexPoolData::Swap(swap), DexPoolData::FeeProtocolUpdate(fp)] => {
1458                // Swap (block 12) must order before SetFeeProtocol (block 13); the asymmetric
1459                // (4, 6) values catch a token0/token1 column swap, and ts confirms the timestamp.
1460                let observed = (
1461                    swap.block,
1462                    fp.block,
1463                    fp.fee_protocol0_new,
1464                    fp.fee_protocol1_new,
1465                    fp.ts_event,
1466                );
1467
1468                if observed != (12, 13, 4, 6, ts) {
1469                    anyhow::bail!("unexpected fee protocol round-trip: {observed:?}");
1470                }
1471            }
1472            other => anyhow::bail!("unexpected stream events: {other:?}"),
1473        }
1474        Ok(())
1475    }
1476
1477    #[tokio::test]
1478    async fn stream_pool_events_round_trips_fee_protocol_update_integer_width() -> anyhow::Result<()>
1479    {
1480        let Some((database, schema)) = connect_cache_test_database().await? else {
1481            return Ok(());
1482        };
1483        let chain = arbitrum();
1484        let dex = pancakeswap_v3(&chain);
1485        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
1486        let pool_identifier = PoolIdentifier::from_address(pool_address);
1487        let instrument_id = Pool::create_instrument_id(chain.name, &dex, pool_identifier.as_str());
1488        let ts = UnixNanos::from(1_700_000_000_000_000_000);
1489
1490        database
1491            .add_pool_event_blocks_batch(chain.chain_id, &[test_block(13, ts)])
1492            .await?;
1493        let update = PoolFeeProtocolUpdate::new(
1494            Arc::clone(&chain),
1495            Arc::clone(&dex),
1496            instrument_id,
1497            pool_identifier,
1498            13,
1499            "0x00000000000000000000000000000000000000000000000000000000000000ac".to_string(),
1500            0,
1501            0,
1502            40_000,
1503            65_535,
1504            ts,
1505            ts,
1506        );
1507        database
1508            .add_pool_fee_protocol_updates_batch(chain.chain_id, std::slice::from_ref(&update))
1509            .await?;
1510
1511        let events_result = database
1512            .stream_pool_events(chain, dex, instrument_id, pool_identifier, None, Some(13))
1513            .try_collect::<Vec<_>>()
1514            .await;
1515
1516        drop(database);
1517        schema.cleanup().await?;
1518
1519        let events = events_result?;
1520        match events.as_slice() {
1521            [DexPoolData::FeeProtocolUpdate(fp)] => {
1522                let observed = (fp.fee_protocol0_new, fp.fee_protocol1_new, fp.ts_event);
1523
1524                if observed != (40_000, 65_535, ts) {
1525                    anyhow::bail!("unexpected integer-width fee protocol round-trip: {observed:?}");
1526                }
1527            }
1528            other => anyhow::bail!("unexpected stream events: {other:?}"),
1529        }
1530        Ok(())
1531    }
1532
1533    #[tokio::test]
1534    async fn stream_pool_events_round_trips_fee_protocol_collect_in_order() -> anyhow::Result<()> {
1535        let Some((database, schema)) = connect_cache_test_database().await? else {
1536            return Ok(());
1537        };
1538        let chain = arbitrum();
1539        let dex = uniswap_v3(&chain);
1540        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
1541        let pool_identifier = PoolIdentifier::from_address(pool_address);
1542        let instrument_id = Pool::create_instrument_id(chain.name, &dex, pool_identifier.as_str());
1543        let ts = UnixNanos::from(1_700_000_000_000_000_000);
1544
1545        database
1546            .add_pool_event_blocks_batch(chain.chain_id, &[test_block(12, ts), test_block(13, ts)])
1547            .await?;
1548        // Swap at block 12, CollectProtocol at block 13: stream must order swap before withdrawal.
1549        insert_pool_swap_event(
1550            &schema.admin_pool,
1551            &schema.name,
1552            chain.chain_id,
1553            &pool_identifier,
1554            12,
1555        )
1556        .await?;
1557        // Asymmetric amounts (111, 222) catch a token0/token1 column swap.
1558        let collect = PoolFeeProtocolCollect::new(
1559            Arc::clone(&chain),
1560            Arc::clone(&dex),
1561            instrument_id,
1562            pool_identifier,
1563            13,
1564            "0x00000000000000000000000000000000000000000000000000000000000000cd".to_string(),
1565            0,
1566            0,
1567            address!("0xc36442b4a4522e871399cd717abdd847ab11fe88"),
1568            address!("0xa61da382c18d9d5beb905ea192bae25e4c15d512"),
1569            111,
1570            222,
1571            ts,
1572            ts,
1573        );
1574        database
1575            .add_pool_fee_protocol_collect_batch(chain.chain_id, std::slice::from_ref(&collect))
1576            .await?;
1577
1578        let events_result = database
1579            .stream_pool_events(chain, dex, instrument_id, pool_identifier, None, Some(13))
1580            .try_collect::<Vec<_>>()
1581            .await;
1582
1583        drop(database);
1584        schema.cleanup().await?;
1585
1586        let events = events_result?;
1587        match events.as_slice() {
1588            [DexPoolData::Swap(swap), DexPoolData::FeeProtocolCollect(cp)] => {
1589                // Swap (block 12) must order before CollectProtocol (block 13); the asymmetric
1590                // (111, 222) amounts catch a token0/token1 column swap, and ts confirms the timestamp.
1591                let observed = (swap.block, cp.block, cp.amount0, cp.amount1, cp.ts_event);
1592                if observed != (12, 13, 111, 222, ts) {
1593                    anyhow::bail!("unexpected fee protocol collect round-trip: {observed:?}");
1594                }
1595
1596                if cp.sender != address!("0xc36442b4a4522e871399cd717abdd847ab11fe88")
1597                    || cp.recipient != address!("0xa61da382c18d9d5beb905ea192bae25e4c15d512")
1598                {
1599                    anyhow::bail!(
1600                        "unexpected fee protocol collect addresses: sender={}, recipient={}",
1601                        cp.sender,
1602                        cp.recipient
1603                    );
1604                }
1605            }
1606            other => anyhow::bail!("unexpected stream events: {other:?}"),
1607        }
1608        Ok(())
1609    }
1610
1611    #[tokio::test]
1612    #[expect(clippy::panic_in_result_fn)]
1613    async fn pre_upgrade_pool_event_cache_backfills_protocol_families_idempotently()
1614    -> anyhow::Result<()> {
1615        let Some(mut clean) = pool_event_sync_fixture().await? else {
1616            return Ok(());
1617        };
1618        let Some(mut migrated) = pool_event_sync_fixture().await? else {
1619            clean.cache.database = None;
1620            clean.schema.cleanup().await?;
1621            return Ok(());
1622        };
1623        let target_block = 13;
1624        let event_families = [
1625            "swap",
1626            "mint",
1627            "burn",
1628            "collect",
1629            "initialize",
1630            "flash",
1631            "fee_protocol_update",
1632            "fee_protocol_collect",
1633        ];
1634        let blocks = [
1635            test_block(11, UnixNanos::from(1_700_000_011_000_000_000)),
1636            test_block(12, UnixNanos::from(1_700_000_012_000_000_000)),
1637            test_block(13, UnixNanos::from(1_700_000_013_000_000_000)),
1638        ];
1639
1640        for fixture in [&mut clean, &mut migrated] {
1641            fixture
1642                .database()
1643                .add_pool_event_blocks_batch(fixture.chain.chain_id, &blocks)
1644                .await?;
1645            insert_pool_swap_event(
1646                &fixture.schema.admin_pool,
1647                &fixture.schema.name,
1648                fixture.chain.chain_id,
1649                &fixture.pool.pool_identifier,
1650                11,
1651            )
1652            .await?;
1653        }
1654
1655        let (mut update, mut collect) = protocol_fee_events(&clean);
1656        clean
1657            .database()
1658            .add_pool_fee_protocol_updates_batch(
1659                clean.chain.chain_id,
1660                std::slice::from_ref(&update),
1661            )
1662            .await?;
1663        clean
1664            .database()
1665            .add_pool_fee_protocol_collect_batch(
1666                clean.chain.chain_id,
1667                std::slice::from_ref(&collect),
1668            )
1669            .await?;
1670        clean
1671            .cache
1672            .update_pool_event_sync(
1673                &DexType::UniswapV3,
1674                &clean.pool.pool_identifier,
1675                &event_families,
1676                target_block,
1677                Some(1),
1678            )
1679            .await?;
1680
1681        migrated
1682            .cache
1683            .update_pool_last_synced_block(
1684                &DexType::UniswapV3,
1685                &migrated.pool.pool_identifier,
1686                target_block,
1687            )
1688            .await?;
1689        let pre_upgrade_state = migrated
1690            .cache
1691            .get_pool_event_sync_state(&DexType::UniswapV3, &migrated.pool.pool_identifier)
1692            .await?;
1693
1694        migrated
1695            .database()
1696            .add_pool_fee_protocol_updates_batch(
1697                migrated.chain.chain_id,
1698                std::slice::from_ref(&update),
1699            )
1700            .await?;
1701        migrated
1702            .cache
1703            .update_pool_event_sync(
1704                &DexType::UniswapV3,
1705                &migrated.pool.pool_identifier,
1706                &["fee_protocol_update"],
1707                12,
1708                None,
1709            )
1710            .await?;
1711        let interrupted_state = migrated
1712            .cache
1713            .get_pool_event_sync_state(&DexType::UniswapV3, &migrated.pool.pool_identifier)
1714            .await?;
1715
1716        migrated
1717            .database()
1718            .add_pool_fee_protocol_updates_batch(
1719                migrated.chain.chain_id,
1720                std::slice::from_ref(&update),
1721            )
1722            .await?;
1723        migrated
1724            .database()
1725            .add_pool_fee_protocol_collect_batch(
1726                migrated.chain.chain_id,
1727                std::slice::from_ref(&collect),
1728            )
1729            .await?;
1730        migrated
1731            .cache
1732            .update_pool_event_sync(
1733                &DexType::UniswapV3,
1734                &migrated.pool.pool_identifier,
1735                &["fee_protocol_update", "fee_protocol_collect"],
1736                target_block,
1737                None,
1738            )
1739            .await?;
1740        migrated
1741            .cache
1742            .update_pool_event_sync(
1743                &DexType::UniswapV3,
1744                &migrated.pool.pool_identifier,
1745                &event_families,
1746                target_block,
1747                Some(1),
1748            )
1749            .await?;
1750
1751        let final_state = migrated
1752            .cache
1753            .get_pool_event_sync_state(&DexType::UniswapV3, &migrated.pool.pool_identifier)
1754            .await?;
1755        let clean_events = clean.events(target_block).await?;
1756        let migrated_events = migrated.events(target_block).await?;
1757        let mut swap = expected_swap(&clean);
1758        swap.block_hash = Some(blocks[0].hash.clone());
1759        update.block_hash = Some(blocks[1].hash.clone());
1760        collect.block_hash = Some(blocks[2].hash.clone());
1761        let expected_events = vec![
1762            DexPoolData::Swap(swap),
1763            DexPoolData::FeeProtocolUpdate(update),
1764            DexPoolData::FeeProtocolCollect(collect),
1765        ];
1766
1767        clean.cache.database = None;
1768        migrated.cache.database = None;
1769        clean.schema.cleanup().await?;
1770        migrated.schema.cleanup().await?;
1771
1772        assert_eq!(
1773            pre_upgrade_state,
1774            PoolEventSyncState {
1775                version: 0,
1776                last_full_sync_block: Some(target_block),
1777                family_blocks: Vec::new(),
1778            }
1779        );
1780        assert_eq!(
1781            interrupted_state,
1782            PoolEventSyncState {
1783                version: 0,
1784                last_full_sync_block: Some(target_block),
1785                family_blocks: vec![("fee_protocol_update".to_string(), 12)],
1786            }
1787        );
1788        assert_eq!(
1789            final_state,
1790            PoolEventSyncState {
1791                version: 1,
1792                last_full_sync_block: Some(target_block),
1793                family_blocks: vec![
1794                    ("burn".to_string(), target_block),
1795                    ("collect".to_string(), target_block),
1796                    ("fee_protocol_collect".to_string(), target_block),
1797                    ("fee_protocol_update".to_string(), target_block),
1798                    ("flash".to_string(), target_block),
1799                    ("initialize".to_string(), target_block),
1800                    ("mint".to_string(), target_block),
1801                    ("swap".to_string(), target_block),
1802                ],
1803            }
1804        );
1805        assert_eq!(clean_events, expected_events);
1806        assert_eq!(migrated_events, clean_events);
1807        Ok(())
1808    }
1809
1810    #[tokio::test]
1811    async fn load_block_timestamps_prefers_full_block_over_pool_event_block() -> anyhow::Result<()>
1812    {
1813        let Some((database, schema)) = connect_cache_test_database().await? else {
1814            return Ok(());
1815        };
1816        let chain = arbitrum();
1817        let fallback_ts = UnixNanos::from(1_700_000_000_000_000_000);
1818        let pool_event_ts = UnixNanos::from(1_700_000_002_000_000_000);
1819        let full_block_ts = UnixNanos::from(1_700_000_001_000_000_000);
1820
1821        database
1822            .add_pool_event_blocks_batch(
1823                chain.chain_id,
1824                &[test_block(20, fallback_ts), test_block(21, pool_event_ts)],
1825            )
1826            .await?;
1827        database
1828            .add_block(chain.chain_id, &test_block(21, full_block_ts))
1829            .await?;
1830
1831        let rows_result = database.load_block_timestamps(chain, 20).await;
1832
1833        drop(database);
1834        schema.cleanup().await?;
1835
1836        let rows = rows_result?;
1837        let observed = rows
1838            .into_iter()
1839            .map(|row| (row.number, row.timestamp))
1840            .collect::<Vec<_>>();
1841
1842        let expected = vec![(20, fallback_ts), (21, full_block_ts)];
1843        if observed != expected {
1844            anyhow::bail!(
1845                "unexpected block timestamps: expected {expected:?}, observed {observed:?}"
1846            );
1847        }
1848        Ok(())
1849    }
1850
1851    #[tokio::test]
1852    async fn load_block_timestamps_uses_pool_event_block_when_full_block_timestamp_is_null()
1853    -> anyhow::Result<()> {
1854        let Some((database, schema)) = connect_cache_test_database().await? else {
1855            return Ok(());
1856        };
1857        let chain = arbitrum();
1858        let fallback_ts = UnixNanos::from(1_700_000_004_000_000_000);
1859
1860        database
1861            .add_pool_event_blocks_batch(chain.chain_id, &[test_block(22, fallback_ts)])
1862            .await?;
1863        insert_block_without_timestamp(&schema.admin_pool, &schema.name, chain.chain_id, 22)
1864            .await?;
1865
1866        let rows_result = database.load_block_timestamps(chain, 22).await;
1867
1868        drop(database);
1869        schema.cleanup().await?;
1870
1871        let rows = rows_result?;
1872        let observed = rows
1873            .into_iter()
1874            .map(|row| (row.number, row.timestamp))
1875            .collect::<Vec<_>>();
1876
1877        let expected = vec![(22, fallback_ts)];
1878        if observed != expected {
1879            anyhow::bail!(
1880                "unexpected block timestamps: expected {expected:?}, observed {observed:?}"
1881            );
1882        }
1883        Ok(())
1884    }
1885
1886    #[tokio::test]
1887    async fn load_pools_sets_pool_timestamps_from_pool_event_block() -> anyhow::Result<()> {
1888        let Some((database, schema)) = connect_cache_test_database().await? else {
1889            return Ok(());
1890        };
1891        let chain = arbitrum();
1892        let dex = uniswap_v3(&chain);
1893        let token0 = weth(&chain);
1894        let token1 = usdc(&chain);
1895        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
1896        let pool_identifier = PoolIdentifier::from_address(pool_address);
1897        let creation_block = 30;
1898        let creation_ts = UnixNanos::from(1_700_000_003_000_000_000);
1899
1900        let pool = Pool::new(
1901            Arc::clone(&chain),
1902            Arc::clone(&dex),
1903            pool_address,
1904            pool_identifier,
1905            creation_block,
1906            token0.clone(),
1907            token1.clone(),
1908            Some(500),
1909            Some(10),
1910            UnixNanos::default(),
1911        );
1912
1913        let mut cache = BlockchainCache::new(Arc::clone(&chain));
1914        cache.database = Some(database);
1915
1916        cache.add_dex(dex).await?;
1917        cache.add_token(token0).await?;
1918        cache.add_token(token1).await?;
1919        cache.add_pool(pool).await?;
1920        let Some(database) = cache.database.as_ref() else {
1921            anyhow::bail!("cache database must be set");
1922        };
1923        database
1924            .add_pool_event_blocks_batch(chain.chain_id, &[test_block(creation_block, creation_ts)])
1925            .await?;
1926
1927        let pools_result = cache.load_pools(&DexType::UniswapV3).await;
1928
1929        cache.database = None;
1930        schema.cleanup().await?;
1931
1932        let pools = pools_result?;
1933        let observed_timestamps = pools
1934            .first()
1935            .map(|pool| (pool.ts_event, pool.ts_init, pools.len()));
1936
1937        let expected_timestamps = Some((creation_ts, creation_ts, 1));
1938        if observed_timestamps != expected_timestamps {
1939            anyhow::bail!(
1940                "unexpected pool timestamps: expected {expected_timestamps:?}, observed {observed_timestamps:?}"
1941            );
1942        }
1943        Ok(())
1944    }
1945
1946    #[tokio::test]
1947    async fn load_latest_pool_snapshot_filters_by_validation_state() -> anyhow::Result<()> {
1948        let Some((database, schema)) = connect_cache_test_database().await? else {
1949            return Ok(());
1950        };
1951        let chain = arbitrum();
1952        let dex = uniswap_v3(&chain);
1953        let token0 = weth(&chain);
1954        let token1 = usdc(&chain);
1955        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
1956        let pool_identifier = PoolIdentifier::from_address(pool_address);
1957
1958        let pool = Pool::new(
1959            Arc::clone(&chain),
1960            Arc::clone(&dex),
1961            pool_address,
1962            pool_identifier,
1963            10, // creation block, distinct from the snapshot blocks below
1964            token0.clone(),
1965            token1.clone(),
1966            Some(500),
1967            Some(10),
1968            UnixNanos::default(),
1969        );
1970        let instrument_id = pool.instrument_id;
1971        let mut cache = BlockchainCache::new(Arc::clone(&chain));
1972        cache.database = Some(database);
1973        cache.add_dex(dex).await?;
1974        cache.add_token(token0).await?;
1975        cache.add_token(token1).await?;
1976        cache.add_pool(pool).await?;
1977
1978        let ts = UnixNanos::from(1_700_000_000_000_000_000);
1979        let database = cache.database.as_ref().expect("cache database must be set");
1980        database
1981            .add_pool_event_blocks_batch(
1982                chain.chain_id,
1983                &[
1984                    test_block(100, ts),
1985                    test_block(150, ts),
1986                    test_block(200, ts),
1987                ],
1988            )
1989            .await?;
1990
1991        // replay@100, on_chain@150, invalid@200: one snapshot per block with a distinct verdict.
1992        for (block, state) in [
1993            (100u64, "replay"),
1994            (150u64, "on_chain"),
1995            (200u64, "invalid"),
1996        ] {
1997            let snapshot = PoolSnapshot::new(
1998                instrument_id,
1999                PoolState::default(),
2000                Vec::new(),
2001                Vec::new(),
2002                PoolAnalytics::default(),
2003                BlockPosition::new(block, "0xabc".to_string(), 0, 0),
2004                ts,
2005                ts,
2006            );
2007            cache
2008                .add_pool_snapshot(&DexType::UniswapV3, &pool_identifier, &snapshot)
2009                .await?;
2010
2011            if state != "replay" {
2012                database
2013                    .set_pool_snapshot_validation_state(
2014                        chain.chain_id,
2015                        &pool_identifier,
2016                        block,
2017                        0,
2018                        0,
2019                        state,
2020                    )
2021                    .await?;
2022            }
2023        }
2024
2025        let latest_valid = database
2026            .load_latest_pool_snapshot(chain.chain_id, &pool_identifier, None, true)
2027            .await;
2028        let latest_any = database
2029            .load_latest_pool_snapshot(chain.chain_id, &pool_identifier, None, false)
2030            .await;
2031        let stored_invalid = database
2032            .get_pool_snapshot_validation_state(chain.chain_id, &pool_identifier, 200, 0, 0)
2033            .await;
2034        let stored_on_chain = database
2035            .get_pool_snapshot_validation_state(chain.chain_id, &pool_identifier, 150, 0, 0)
2036            .await;
2037
2038        cache.database = None;
2039        schema.cleanup().await?;
2040
2041        let latest_valid_metadata = latest_valid?.map(|snapshot| {
2042            (
2043                snapshot.block_position.number,
2044                snapshot.block_position.block_hash,
2045            )
2046        });
2047        let latest_any_block = latest_any?.map(|s| s.block_position.number);
2048        let stored_invalid = stored_invalid?;
2049        let stored_on_chain = stored_on_chain?;
2050
2051        // require_valid excludes 'invalid', so the latest usable snapshot is on_chain@150; without
2052        // the filter the newest row (invalid@200) wins. The stored verdict stays readable by primary
2053        // key and untouched by the load filter.
2054        if latest_valid_metadata != Some((150, Some(format!("0x{:064x}", 150))))
2055            || latest_any_block != Some(200)
2056            || stored_invalid.as_deref() != Some("invalid")
2057            || stored_on_chain.as_deref() != Some("on_chain")
2058        {
2059            anyhow::bail!(
2060                "unexpected load filter result: latest_valid_metadata={latest_valid_metadata:?}, latest_any_block={latest_any_block:?}, stored_invalid={stored_invalid:?}, stored_on_chain={stored_on_chain:?}"
2061            );
2062        }
2063
2064        Ok(())
2065    }
2066
2067    #[tokio::test]
2068    async fn add_pool_snapshot_upserts_existing_snapshot() -> anyhow::Result<()> {
2069        let Some((database, schema)) = connect_cache_test_database().await? else {
2070            return Ok(());
2071        };
2072        let chain = arbitrum();
2073        let dex = pancakeswap_v3(&chain);
2074        let token0 = weth(&chain);
2075        let token1 = usdc(&chain);
2076        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
2077        let pool_identifier = PoolIdentifier::from_address(pool_address);
2078
2079        let pool = Pool::new(
2080            Arc::clone(&chain),
2081            Arc::clone(&dex),
2082            pool_address,
2083            pool_identifier,
2084            10,
2085            token0.clone(),
2086            token1.clone(),
2087            Some(500),
2088            Some(10),
2089            UnixNanos::default(),
2090        );
2091        let instrument_id = pool.instrument_id;
2092        let mut cache = BlockchainCache::new(Arc::clone(&chain));
2093        cache.database = Some(database);
2094        cache.add_dex(dex).await?;
2095        cache.add_token(token0).await?;
2096        cache.add_token(token1).await?;
2097        cache.add_pool(pool).await?;
2098
2099        let ts = UnixNanos::from(1_700_000_000_000_000_000);
2100        let database = cache.database.as_ref().expect("cache database must be set");
2101        database
2102            .add_pool_event_blocks_batch(chain.chain_id, &[test_block(100, ts)])
2103            .await?;
2104
2105        let mut state = PoolState::default();
2106        state.set_protocol_fee_basis_points(3_200, 4_000);
2107        let owner = address!("0000000000000000000000000000000000000001");
2108        let mut snapshot = PoolSnapshot::new(
2109            instrument_id,
2110            state,
2111            vec![PoolPosition::new(owner, -10, 10, 100)],
2112            vec![PoolTick::new(
2113                -10,
2114                100,
2115                100,
2116                U256::ZERO,
2117                U256::ZERO,
2118                true,
2119                100,
2120            )],
2121            PoolAnalytics::default(),
2122            BlockPosition::new(100, "0xabc".to_string(), 0, 0),
2123            ts,
2124            ts,
2125        );
2126        cache
2127            .add_pool_snapshot(&DexType::PancakeSwapV3, &pool_identifier, &snapshot)
2128            .await?;
2129        database
2130            .set_pool_snapshot_validation_state(
2131                chain.chain_id,
2132                &pool_identifier,
2133                100,
2134                0,
2135                0,
2136                "on_chain",
2137            )
2138            .await?;
2139
2140        snapshot.state.set_protocol_fee_basis_points(3_300, 4_100);
2141        snapshot.positions[0].liquidity = 200;
2142        snapshot.ticks[0].liquidity_gross = 200;
2143        snapshot.ticks[0].liquidity_net = 200;
2144        cache
2145            .add_pool_snapshot(&DexType::PancakeSwapV3, &pool_identifier, &snapshot)
2146            .await?;
2147
2148        let loaded = database
2149            .load_latest_pool_snapshot(chain.chain_id, &pool_identifier, None, false)
2150            .await?;
2151        let validation = database
2152            .get_pool_snapshot_validation_state(chain.chain_id, &pool_identifier, 100, 0, 0)
2153            .await?;
2154
2155        cache.database = None;
2156        schema.cleanup().await?;
2157
2158        let Some(loaded) = loaded else {
2159            anyhow::bail!("expected snapshot to load");
2160        };
2161        let observed = (
2162            loaded.state.fee_protocol,
2163            loaded.state.fee_protocol0_basis_points,
2164            loaded.state.fee_protocol1_basis_points,
2165            loaded.positions[0].liquidity,
2166            loaded.ticks[0].liquidity_gross,
2167            loaded.ticks[0].liquidity_net,
2168            validation,
2169        );
2170
2171        if observed
2172            != (
2173                0,
2174                Some(3_300),
2175                Some(4_100),
2176                200,
2177                200,
2178                200,
2179                Some("on_chain".to_string()),
2180            )
2181        {
2182            anyhow::bail!("unexpected upserted snapshot state: {observed:?}");
2183        }
2184        Ok(())
2185    }
2186
2187    #[tokio::test]
2188    async fn load_pool_loads_only_the_requested_pool() -> anyhow::Result<()> {
2189        let Some((database, schema)) = connect_cache_test_database().await? else {
2190            return Ok(());
2191        };
2192        let chain = arbitrum();
2193        let dex = uniswap_v3(&chain);
2194        let token0 = weth(&chain);
2195        let token1 = usdc(&chain);
2196        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
2197        let pool_identifier = PoolIdentifier::from_address(pool_address);
2198        let absent_identifier =
2199            PoolIdentifier::from_address(address!("0x1111111111111111111111111111111111111111"));
2200
2201        let pool = Pool::new(
2202            Arc::clone(&chain),
2203            Arc::clone(&dex),
2204            pool_address,
2205            pool_identifier,
2206            30,
2207            token0.clone(),
2208            token1.clone(),
2209            Some(500),
2210            Some(10),
2211            UnixNanos::default(),
2212        );
2213        let mut cache = BlockchainCache::new(Arc::clone(&chain));
2214        cache.database = Some(database);
2215        cache.add_dex(dex).await?;
2216        cache.add_token(token0).await?;
2217        cache.add_token(token1).await?;
2218        cache.add_pool(pool).await?;
2219
2220        // Drop the in-memory pool so load_pool must read it back from the database, then prove it
2221        // repopulates the cache for exactly the requested pool and reports None for an absent one.
2222        cache.pools.clear();
2223        let loaded = cache.load_pool(&DexType::UniswapV3, &pool_identifier).await;
2224        let cached_after_load = cache.get_pool(&pool_identifier).is_some();
2225        let absent = cache
2226            .load_pool(&DexType::UniswapV3, &absent_identifier)
2227            .await;
2228
2229        cache.database = None;
2230        schema.cleanup().await?;
2231
2232        let loaded_id = loaded?.map(|pool| pool.pool_identifier);
2233        let absent_is_some = absent?.is_some();
2234
2235        if loaded_id != Some(pool_identifier) || !cached_after_load || absent_is_some {
2236            anyhow::bail!(
2237                "unexpected load_pool result: loaded_id={loaded_id:?}, cached_after_load={cached_after_load}, absent_is_some={absent_is_some}"
2238            );
2239        }
2240
2241        Ok(())
2242    }
2243
2244    // check_snapshot_validity lives on the data client but exercises the cache DB read path, so its
2245    // RPC-unreachable test reuses this module's isolated-schema scaffolding. It runs fully only when
2246    // both Postgres and an ENVIO_API_TOKEN are present (the live-smoke setup) and skips otherwise.
2247    #[tokio::test]
2248    async fn check_snapshot_validity_reports_stored_verdict_when_rpc_unreachable()
2249    -> anyhow::Result<()> {
2250        // BlockchainDataClientCore::new builds a HyperSyncClient, which requires a UUID token; the
2251        // crate denies unsafe_code, so the test cannot inject one. Skip when it is absent (checked
2252        // before opening a schema to avoid leaking it).
2253        if std::env::var("ENVIO_API_TOKEN").is_err() {
2254            return Ok(());
2255        }
2256        let Some((database, schema)) = connect_cache_test_database().await? else {
2257            return Ok(());
2258        };
2259        let chain = arbitrum();
2260        let dex = uniswap_v3(&chain);
2261        let token0 = weth(&chain);
2262        let token1 = usdc(&chain);
2263        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
2264        let pool_identifier = PoolIdentifier::from_address(pool_address);
2265        let pool = Pool::new(
2266            Arc::clone(&chain),
2267            Arc::clone(&dex),
2268            pool_address,
2269            pool_identifier,
2270            10,
2271            token0.clone(),
2272            token1.clone(),
2273            Some(500),
2274            Some(10),
2275            UnixNanos::default(),
2276        );
2277        let instrument_id = pool.instrument_id;
2278
2279        // Unreachable RPC so the on-chain compare cannot fetch the block and must fall back to the
2280        // stored verdict.
2281        let config = BlockchainDataClientConfig::builder()
2282            .chain(Arc::clone(&chain))
2283            .dex_ids(vec![DexType::UniswapV3])
2284            .http_rpc_url("http://127.0.0.1:9".into())
2285            .use_hypersync_for_live_data(true)
2286            .build();
2287        let mut core =
2288            BlockchainDataClientCore::new(config, None, None, CancellationToken::new()).unwrap();
2289        core.cache.database = Some(database);
2290        core.cache.add_dex(dex).await?;
2291        core.cache.add_token(token0).await?;
2292        core.cache.add_token(token1).await?;
2293        core.cache.add_pool(pool.clone()).await?;
2294
2295        // Persist an `invalid` verdict at the watermark the profiler will report.
2296        let ts = UnixNanos::from(1_700_000_000_000_000_000);
2297        let block_position = BlockPosition::new(200, "0xabc".to_string(), 0, 0);
2298        let snapshot = PoolSnapshot::new(
2299            instrument_id,
2300            PoolState::default(),
2301            Vec::new(),
2302            Vec::new(),
2303            PoolAnalytics::default(),
2304            block_position.clone(),
2305            ts,
2306            ts,
2307        );
2308        core.cache
2309            .add_pool_snapshot(&DexType::UniswapV3, &pool_identifier, &snapshot)
2310            .await?;
2311        core.cache
2312            .database
2313            .as_ref()
2314            .expect("cache database must be set")
2315            .set_pool_snapshot_validation_state(
2316                chain.chain_id,
2317                &pool_identifier,
2318                200,
2319                0,
2320                0,
2321                "invalid",
2322            )
2323            .await?;
2324        core.cache
2325            .add_pool_snapshot(&DexType::UniswapV3, &pool_identifier, &snapshot)
2326            .await?;
2327
2328        let mut profiler = PoolProfiler::new(Arc::new(pool));
2329        profiler
2330            .initialize(U160::from_str_radix("3cb0adde486484998be0b", 16).unwrap())
2331            .expect("profiler should initialize from a known sqrt price");
2332        profiler.last_processed_event = Some(block_position);
2333        profiler.last_processed_ts = Some(ts);
2334
2335        let reported = core.check_snapshot_validity(&profiler, false).await;
2336        let stored_after = core
2337            .cache
2338            .database
2339            .as_ref()
2340            .expect("cache database must be set")
2341            .get_pool_snapshot_validation_state(chain.chain_id, &pool_identifier, 200, 0, 0)
2342            .await;
2343
2344        core.cache.database = None;
2345        schema.cleanup().await?;
2346
2347        // The RPC could not reach the block, so the reported verdict comes from the stored row, and
2348        // the stored row is left untouched (a transient RPC failure must not clobber a definitive
2349        // verdict).
2350        let reported = reported?;
2351        let stored_after = stored_after?;
2352        if reported != SnapshotValidation::Invalid || stored_after.as_deref() != Some("invalid") {
2353            anyhow::bail!(
2354                "unexpected validity result: reported={reported:?}, stored_after={stored_after:?}"
2355            );
2356        }
2357
2358        Ok(())
2359    }
2360
2361    struct PoolEventSyncFixture {
2362        cache: BlockchainCache,
2363        schema: TestSchema,
2364        chain: SharedChain,
2365        dex: SharedDex,
2366        pool: Pool,
2367    }
2368
2369    impl PoolEventSyncFixture {
2370        fn database(&self) -> &BlockchainCacheDatabase {
2371            self.cache
2372                .database
2373                .as_ref()
2374                .expect("cache database must be set")
2375        }
2376
2377        async fn events(&self, to_block: u64) -> anyhow::Result<Vec<DexPoolData>> {
2378            self.database()
2379                .stream_pool_events(
2380                    Arc::clone(&self.chain),
2381                    Arc::clone(&self.dex),
2382                    self.pool.instrument_id,
2383                    self.pool.pool_identifier,
2384                    None,
2385                    Some(to_block),
2386                )
2387                .try_collect()
2388                .await
2389        }
2390    }
2391
2392    async fn pool_event_sync_fixture() -> anyhow::Result<Option<PoolEventSyncFixture>> {
2393        let Some((database, schema)) = connect_cache_test_database().await? else {
2394            return Ok(None);
2395        };
2396        let chain = arbitrum();
2397        let dex = uniswap_v3(&chain);
2398        let token0 = weth(&chain);
2399        let token1 = usdc(&chain);
2400        let pool_address = address!("0xd13040d4fe917EE704158CfCB3338dCd2838B245");
2401        let pool = Pool::new(
2402            Arc::clone(&chain),
2403            Arc::clone(&dex),
2404            pool_address,
2405            PoolIdentifier::from_address(pool_address),
2406            10,
2407            token0.clone(),
2408            token1.clone(),
2409            Some(500),
2410            Some(10),
2411            UnixNanos::default(),
2412        );
2413        let mut cache = BlockchainCache::new(Arc::clone(&chain));
2414        cache.database = Some(database);
2415        cache.add_dex(Arc::clone(&dex)).await?;
2416        cache.add_token(token0).await?;
2417        cache.add_token(token1).await?;
2418        cache.add_pool(pool.clone()).await?;
2419
2420        Ok(Some(PoolEventSyncFixture {
2421            cache,
2422            schema,
2423            chain,
2424            dex,
2425            pool,
2426        }))
2427    }
2428
2429    fn protocol_fee_events(
2430        fixture: &PoolEventSyncFixture,
2431    ) -> (PoolFeeProtocolUpdate, PoolFeeProtocolCollect) {
2432        let update = PoolFeeProtocolUpdate::new(
2433            Arc::clone(&fixture.chain),
2434            Arc::clone(&fixture.dex),
2435            fixture.pool.instrument_id,
2436            fixture.pool.pool_identifier,
2437            12,
2438            "0x00000000000000000000000000000000000000000000000000000000000000ab".to_string(),
2439            1,
2440            1,
2441            4,
2442            6,
2443            UnixNanos::from(1_700_000_012_000_000_000),
2444            UnixNanos::from(1_700_000_012_000_000_000),
2445        );
2446        let collect = PoolFeeProtocolCollect::new(
2447            Arc::clone(&fixture.chain),
2448            Arc::clone(&fixture.dex),
2449            fixture.pool.instrument_id,
2450            fixture.pool.pool_identifier,
2451            13,
2452            "0x00000000000000000000000000000000000000000000000000000000000000cd".to_string(),
2453            2,
2454            2,
2455            address!("0xc36442b4a4522e871399cd717abdd847ab11fe88"),
2456            address!("0xa61da382c18d9d5beb905ea192bae25e4c15d512"),
2457            111,
2458            222,
2459            UnixNanos::from(1_700_000_013_000_000_000),
2460            UnixNanos::from(1_700_000_013_000_000_000),
2461        );
2462        (update, collect)
2463    }
2464
2465    fn expected_swap(fixture: &PoolEventSyncFixture) -> PoolSwap {
2466        PoolSwap::new(
2467            Arc::clone(&fixture.chain),
2468            Arc::clone(&fixture.dex),
2469            fixture.pool.instrument_id,
2470            fixture.pool.pool_identifier,
2471            11,
2472            "0x000000000000000000000000000000000000000000000000000000000000000c".to_string(),
2473            0,
2474            0,
2475            UnixNanos::from(1_700_000_011_000_000_000),
2476            UnixNanos::from(1_700_000_011_000_000_000),
2477            address!("0x1111111111111111111111111111111111111111"),
2478            address!("0x2222222222222222222222222222222222222222"),
2479            I256::try_from(-1_000_000_000_000_000_000_i128).unwrap(),
2480            I256::try_from(2_000_000_i128).unwrap(),
2481            U160::from(79_228_162_514_264_337_593_543_950_336_u128),
2482            1_000_000,
2483            0,
2484        )
2485    }
2486
2487    async fn connect_cache_test_database()
2488    -> anyhow::Result<Option<(BlockchainCacheDatabase, TestSchema)>> {
2489        let config = get_postgres_connect_options(None, None, None, None, None);
2490        let mut connect_options: PgConnectOptions = config.clone().into();
2491        let Some(mut admin_pool) =
2492            connect_cache_test_pool(connect_options.clone(), &config.username).await
2493        else {
2494            return Ok(None);
2495        };
2496        let schema_name = cache_test_schema_name();
2497
2498        if let Err(e) = create_cache_test_schema(&admin_pool, &schema_name).await {
2499            if !is_database_create_permission_denied(&e) || config.username == "postgres" {
2500                return Err(e);
2501            }
2502
2503            eprintln!(
2504                "Postgres role {} cannot create isolated blockchain cache test schema; retrying with postgres role: {e}",
2505                config.username
2506            );
2507            admin_pool.close().await;
2508            connect_options = postgres_test_connect_options(&config);
2509            let Some(postgres_pool) =
2510                connect_cache_test_pool(connect_options.clone(), "postgres").await
2511            else {
2512                return Err(e);
2513            };
2514            admin_pool = postgres_pool;
2515            create_cache_test_schema(&admin_pool, &schema_name).await?;
2516        }
2517
2518        let database = crate::cache::database::tests::connect_test_database(
2519            connect_options.options([("search_path", format!("{schema_name},public"))]),
2520        )
2521        .await?;
2522
2523        Ok(Some((
2524            database,
2525            TestSchema {
2526                admin_pool,
2527                name: schema_name,
2528            },
2529        )))
2530    }
2531
2532    async fn connect_cache_test_pool(
2533        connect_options: PgConnectOptions,
2534        username: &str,
2535    ) -> Option<PgPool> {
2536        match PgPoolOptions::new()
2537            .max_connections(1)
2538            .connect_with(connect_options)
2539            .await
2540        {
2541            Ok(pool) => Some(pool),
2542            Err(e) => {
2543                eprintln!(
2544                    "Postgres connection as {username} failed; skipping blockchain cache DB test: {e}"
2545                );
2546                None
2547            }
2548        }
2549    }
2550
2551    fn postgres_test_connect_options(config: &PostgresConnectOptions) -> PgConnectOptions {
2552        PostgresConnectOptions::new(
2553            config.host.clone(),
2554            config.port,
2555            String::from("postgres"),
2556            config.password.clone(),
2557            config.database.clone(),
2558        )
2559        .into()
2560    }
2561
2562    fn is_database_create_permission_denied(error: &anyhow::Error) -> bool {
2563        match error.downcast_ref::<SqlxError>() {
2564            Some(SqlxError::Database(database_error)) => {
2565                database_error
2566                    .code()
2567                    .is_some_and(|code| code.as_ref() == "42501")
2568                    && database_error
2569                        .message()
2570                        .contains("permission denied for database")
2571            }
2572            _ => false,
2573        }
2574    }
2575
2576    struct TestSchema {
2577        admin_pool: PgPool,
2578        name: String,
2579    }
2580
2581    impl TestSchema {
2582        async fn cleanup(self) -> anyhow::Result<()> {
2583            drop_cache_test_schema(&self.admin_pool, &self.name).await?;
2584            self.admin_pool.close().await;
2585            Ok(())
2586        }
2587    }
2588
2589    #[expect(
2590        clippy::too_many_lines,
2591        reason = "test schema declares the narrow table set used by cache SQL"
2592    )]
2593    async fn create_cache_test_schema(pool: &PgPool, schema: &str) -> anyhow::Result<()> {
2594        execute_schema_statement(pool, format!("CREATE SCHEMA {schema}")).await?;
2595
2596        let statements = [
2597            format!("CREATE DOMAIN {schema}.U256 AS NUMERIC(78, 0)"),
2598            format!("CREATE DOMAIN {schema}.U160 AS NUMERIC(49, 0)"),
2599            format!("CREATE DOMAIN {schema}.U128 AS NUMERIC(39, 0)"),
2600            format!(
2601                r#"
2602                CREATE TABLE {schema}."chain" (
2603                    chain_id INTEGER PRIMARY KEY,
2604                    name TEXT NOT NULL
2605                )
2606                "#
2607            ),
2608            format!(
2609                r#"
2610                CREATE TABLE {schema}."block" (
2611                    chain_id INTEGER NOT NULL,
2612                    number BIGINT NOT NULL,
2613                    hash TEXT,
2614                    parent_hash TEXT,
2615                    miner TEXT,
2616                    gas_limit BIGINT,
2617                    gas_used BIGINT,
2618                    timestamp TEXT,
2619                    base_fee_per_gas TEXT,
2620                    blob_gas_used TEXT,
2621                    excess_blob_gas TEXT,
2622                    l1_gas_price TEXT,
2623                    l1_gas_used BIGINT,
2624                    l1_fee_scalar BIGINT,
2625                    PRIMARY KEY (chain_id, number)
2626                )
2627                "#
2628            ),
2629            format!(
2630                r#"
2631                CREATE TABLE {schema}."pool_event_block" (
2632                    chain_id INTEGER NOT NULL,
2633                    number BIGINT NOT NULL,
2634                    hash TEXT,
2635                    timestamp TEXT NOT NULL,
2636                    PRIMARY KEY (chain_id, number)
2637                )
2638                "#
2639            ),
2640            format!(
2641                r#"
2642                CREATE TABLE {schema}."token" (
2643                    chain_id INTEGER NOT NULL,
2644                    address TEXT NOT NULL,
2645                    symbol TEXT,
2646                    name TEXT,
2647                    decimals INTEGER,
2648                    error TEXT,
2649                    PRIMARY KEY (chain_id, address)
2650                )
2651                "#
2652            ),
2653            format!(
2654                r#"
2655                CREATE TABLE {schema}."dex" (
2656                    chain_id INTEGER NOT NULL,
2657                    name TEXT NOT NULL,
2658                    factory_address TEXT NOT NULL,
2659                    creation_block BIGINT NOT NULL,
2660                    last_full_sync_pools_block_number BIGINT,
2661                    PRIMARY KEY (chain_id, name),
2662                    UNIQUE (chain_id, factory_address)
2663                )
2664                "#
2665            ),
2666            format!(
2667                r#"
2668                CREATE TABLE {schema}."pool" (
2669                    chain_id INTEGER NOT NULL,
2670                    dex_name TEXT NOT NULL,
2671                    address TEXT NOT NULL,
2672                    pool_identifier TEXT NOT NULL,
2673                    creation_block BIGINT NOT NULL,
2674                    token0_chain INTEGER NOT NULL,
2675                    token0_address TEXT NOT NULL,
2676                    token1_chain INTEGER NOT NULL,
2677                    token1_address TEXT NOT NULL,
2678                    fee INTEGER,
2679                    tick_spacing INTEGER,
2680                    initial_tick INTEGER,
2681                    initial_sqrt_price_x96 TEXT,
2682                    hook_address TEXT,
2683                    last_full_sync_block_number BIGINT,
2684                    event_sync_version INTEGER NOT NULL DEFAULT 0,
2685                    PRIMARY KEY (chain_id, dex_name, pool_identifier)
2686                )
2687                "#
2688            ),
2689            format!(
2690                r#"
2691                CREATE TABLE {schema}."pool_event_sync" (
2692                    chain_id INTEGER NOT NULL,
2693                    dex_name TEXT NOT NULL,
2694                    pool_identifier TEXT NOT NULL,
2695                    event_family TEXT NOT NULL,
2696                    last_full_sync_block_number BIGINT NOT NULL,
2697                    PRIMARY KEY (chain_id, dex_name, pool_identifier, event_family)
2698                )
2699                "#
2700            ),
2701            format!(
2702                r#"
2703                CREATE TABLE {schema}."pool_swap_event" (
2704                    chain_id INTEGER NOT NULL,
2705                    pool_identifier TEXT NOT NULL,
2706                    dex_name TEXT NOT NULL,
2707                    block BIGINT NOT NULL,
2708                    transaction_hash TEXT NOT NULL,
2709                    transaction_index INTEGER NOT NULL,
2710                    log_index INTEGER NOT NULL,
2711                    sender TEXT NOT NULL,
2712                    recipient TEXT NOT NULL,
2713                    sqrt_price_x96 TEXT NOT NULL,
2714                    liquidity TEXT NOT NULL,
2715                    tick INTEGER NOT NULL,
2716                    amount0 TEXT NOT NULL,
2717                    amount1 TEXT NOT NULL,
2718                    order_side TEXT,
2719                    base_quantity NUMERIC,
2720                    quote_quantity NUMERIC,
2721                    spot_price NUMERIC,
2722                    execution_price NUMERIC,
2723                    UNIQUE(chain_id, transaction_hash, log_index)
2724                )
2725                "#
2726            ),
2727            format!(
2728                r#"
2729                CREATE TABLE {schema}."pool_liquidity_event" (
2730                    chain_id INTEGER NOT NULL,
2731                    pool_identifier TEXT NOT NULL,
2732                    dex_name TEXT NOT NULL,
2733                    block BIGINT NOT NULL,
2734                    transaction_hash TEXT NOT NULL,
2735                    transaction_index INTEGER NOT NULL,
2736                    log_index INTEGER NOT NULL,
2737                    event_type TEXT NOT NULL,
2738                    sender TEXT,
2739                    owner TEXT NOT NULL,
2740                    position_liquidity TEXT NOT NULL,
2741                    amount0 TEXT NOT NULL,
2742                    amount1 TEXT NOT NULL,
2743                    tick_lower INTEGER NOT NULL,
2744                    tick_upper INTEGER NOT NULL,
2745                    UNIQUE(chain_id, transaction_hash, log_index)
2746                )
2747                "#
2748            ),
2749            format!(
2750                r#"
2751                CREATE TABLE {schema}."pool_collect_event" (
2752                    chain_id INTEGER NOT NULL,
2753                    pool_identifier TEXT NOT NULL,
2754                    dex_name TEXT NOT NULL,
2755                    block BIGINT NOT NULL,
2756                    transaction_hash TEXT NOT NULL,
2757                    transaction_index INTEGER NOT NULL,
2758                    log_index INTEGER NOT NULL,
2759                    owner TEXT NOT NULL,
2760                    amount0 TEXT NOT NULL,
2761                    amount1 TEXT NOT NULL,
2762                    tick_lower INTEGER NOT NULL,
2763                    tick_upper INTEGER NOT NULL,
2764                    UNIQUE(chain_id, transaction_hash, log_index)
2765                )
2766                "#
2767            ),
2768            format!(
2769                r#"
2770                CREATE TABLE {schema}."pool_flash_event" (
2771                    chain_id INTEGER NOT NULL,
2772                    pool_identifier TEXT NOT NULL,
2773                    dex_name TEXT NOT NULL,
2774                    block BIGINT NOT NULL,
2775                    transaction_hash TEXT NOT NULL,
2776                    transaction_index INTEGER NOT NULL,
2777                    log_index INTEGER NOT NULL,
2778                    sender TEXT NOT NULL,
2779                    recipient TEXT NOT NULL,
2780                    amount0 TEXT NOT NULL,
2781                    amount1 TEXT NOT NULL,
2782                    paid0 TEXT NOT NULL,
2783                    paid1 TEXT NOT NULL,
2784                    UNIQUE(chain_id, transaction_hash, log_index)
2785                )
2786                "#
2787            ),
2788            format!(
2789                r#"
2790                CREATE TABLE {schema}."pool_fee_protocol_update_event" (
2791                    chain_id INTEGER NOT NULL,
2792                    pool_identifier TEXT NOT NULL,
2793                    dex_name TEXT NOT NULL,
2794                    block BIGINT NOT NULL,
2795                    transaction_hash TEXT NOT NULL,
2796                    transaction_index INTEGER NOT NULL,
2797                    log_index INTEGER NOT NULL,
2798                    fee_protocol0_new INTEGER NOT NULL,
2799                    fee_protocol1_new INTEGER NOT NULL,
2800                    UNIQUE(chain_id, transaction_hash, log_index)
2801                )
2802                "#
2803            ),
2804            format!(
2805                r#"
2806                CREATE TABLE {schema}."pool_fee_protocol_collect_event" (
2807                    chain_id INTEGER NOT NULL,
2808                    pool_identifier TEXT NOT NULL,
2809                    dex_name TEXT NOT NULL,
2810                    block BIGINT NOT NULL,
2811                    transaction_hash TEXT NOT NULL,
2812                    transaction_index INTEGER NOT NULL,
2813                    log_index INTEGER NOT NULL,
2814                    sender TEXT NOT NULL,
2815                    recipient TEXT NOT NULL,
2816                    amount0 TEXT NOT NULL,
2817                    amount1 TEXT NOT NULL,
2818                    UNIQUE(chain_id, transaction_hash, log_index)
2819                )
2820                "#
2821            ),
2822            format!(
2823                r#"
2824                CREATE TABLE {schema}."pool_snapshot" (
2825                    chain_id INTEGER NOT NULL,
2826                    pool_identifier TEXT NOT NULL,
2827                    dex_name TEXT NOT NULL,
2828                    block BIGINT NOT NULL,
2829                    transaction_index INTEGER NOT NULL,
2830                    log_index INTEGER NOT NULL,
2831                    transaction_hash TEXT NOT NULL,
2832                    current_tick INTEGER NOT NULL,
2833                    price_sqrt_ratio_x96 NUMERIC NOT NULL,
2834                    liquidity NUMERIC NOT NULL,
2835                    protocol_fees_token0 NUMERIC NOT NULL,
2836                    protocol_fees_token1 NUMERIC NOT NULL,
2837                    fee_protocol SMALLINT NOT NULL,
2838                    fee_protocol0_basis_points INTEGER,
2839                    fee_protocol1_basis_points INTEGER,
2840                    fee_growth_global_0 NUMERIC NOT NULL,
2841                    fee_growth_global_1 NUMERIC NOT NULL,
2842                    total_amount0_deposited NUMERIC NOT NULL,
2843                    total_amount1_deposited NUMERIC NOT NULL,
2844                    total_amount0_collected NUMERIC NOT NULL,
2845                    total_amount1_collected NUMERIC NOT NULL,
2846                    total_swaps INTEGER NOT NULL,
2847                    total_mints INTEGER NOT NULL,
2848                    total_burns INTEGER NOT NULL,
2849                    total_fee_collects INTEGER NOT NULL,
2850                    total_flashes INTEGER NOT NULL,
2851                    liquidity_utilization_rate DOUBLE PRECISION,
2852                    validation_state TEXT NOT NULL DEFAULT 'replay' CHECK (validation_state IN ('on_chain', 'replay', 'invalid')),
2853                    PRIMARY KEY (chain_id, pool_identifier, block, transaction_index, log_index)
2854                )
2855                "#
2856            ),
2857            format!(
2858                r#"
2859                CREATE TABLE {schema}."pool_position" (
2860                    chain_id INTEGER NOT NULL,
2861                    pool_identifier TEXT NOT NULL,
2862                    snapshot_block BIGINT NOT NULL,
2863                    snapshot_transaction_index INTEGER NOT NULL,
2864                    snapshot_log_index INTEGER NOT NULL,
2865                    owner TEXT NOT NULL,
2866                    tick_lower INTEGER NOT NULL,
2867                    tick_upper INTEGER NOT NULL,
2868                    liquidity NUMERIC NOT NULL,
2869                    fee_growth_inside_0_last NUMERIC NOT NULL,
2870                    fee_growth_inside_1_last NUMERIC NOT NULL,
2871                    tokens_owed_0 NUMERIC NOT NULL,
2872                    tokens_owed_1 NUMERIC NOT NULL,
2873                    total_amount0_deposited NUMERIC,
2874                    total_amount1_deposited NUMERIC,
2875                    total_amount0_collected NUMERIC,
2876                    total_amount1_collected NUMERIC,
2877                    PRIMARY KEY (chain_id, pool_identifier, snapshot_block, snapshot_transaction_index, snapshot_log_index, owner, tick_lower, tick_upper)
2878                )
2879                "#
2880            ),
2881            format!(
2882                r#"
2883                CREATE TABLE {schema}."pool_tick" (
2884                    chain_id INTEGER NOT NULL,
2885                    pool_identifier TEXT NOT NULL,
2886                    snapshot_block BIGINT NOT NULL,
2887                    snapshot_transaction_index INTEGER NOT NULL,
2888                    snapshot_log_index INTEGER NOT NULL,
2889                    tick_value INTEGER NOT NULL,
2890                    liquidity_gross NUMERIC NOT NULL,
2891                    liquidity_net NUMERIC NOT NULL,
2892                    fee_growth_outside_0 NUMERIC NOT NULL,
2893                    fee_growth_outside_1 NUMERIC NOT NULL,
2894                    initialized BOOLEAN NOT NULL,
2895                    last_updated_block BIGINT NOT NULL,
2896                    PRIMARY KEY (chain_id, pool_identifier, snapshot_block, snapshot_transaction_index, snapshot_log_index, tick_value)
2897                )
2898                "#
2899            ),
2900        ];
2901
2902        for statement in statements {
2903            execute_schema_statement(pool, statement).await?;
2904        }
2905
2906        Ok(())
2907    }
2908
2909    async fn insert_pool_swap_event(
2910        pool: &PgPool,
2911        schema: &str,
2912        chain_id: u32,
2913        pool_identifier: &PoolIdentifier,
2914        block: u64,
2915    ) -> anyhow::Result<()> {
2916        sqlx::query(AssertSqlSafe(format!(
2917            r#"
2918            INSERT INTO {schema}."pool_swap_event" (
2919                chain_id, pool_identifier, dex_name, block, transaction_hash, transaction_index,
2920                log_index, sender, recipient, sqrt_price_x96, liquidity, tick, amount0, amount1
2921            )
2922            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
2923            "#
2924        )))
2925        .bind(chain_id as i32)
2926        .bind(pool_identifier.to_string())
2927        .bind(DexType::UniswapV3.to_string())
2928        .bind(block as i64)
2929        .bind("0x000000000000000000000000000000000000000000000000000000000000000c")
2930        .bind(0_i32)
2931        .bind(0_i32)
2932        .bind("0x1111111111111111111111111111111111111111")
2933        .bind("0x2222222222222222222222222222222222222222")
2934        .bind("79228162514264337593543950336")
2935        .bind("1000000")
2936        .bind(0_i32)
2937        .bind("-1000000000000000000")
2938        .bind("2000000")
2939        .execute(pool)
2940        .await?;
2941        Ok(())
2942    }
2943
2944    async fn insert_block_without_timestamp(
2945        pool: &PgPool,
2946        schema: &str,
2947        chain_id: u32,
2948        number: u64,
2949    ) -> anyhow::Result<()> {
2950        sqlx::query(AssertSqlSafe(format!(
2951            r#"
2952            INSERT INTO {schema}."block" (
2953                chain_id, number, hash, parent_hash, miner, gas_limit, gas_used, timestamp
2954            )
2955            VALUES ($1, $2, $3, $4, $5, $6, $7, NULL)
2956            "#
2957        )))
2958        .bind(chain_id as i32)
2959        .bind(number as i64)
2960        .bind(format!("0x{number:064x}"))
2961        .bind("0x0")
2962        .bind("0x0000000000000000000000000000000000000000")
2963        .bind(30_000_000_i64)
2964        .bind(21_000_i64)
2965        .execute(pool)
2966        .await?;
2967        Ok(())
2968    }
2969
2970    async fn drop_cache_test_schema(pool: &PgPool, schema: &str) -> anyhow::Result<()> {
2971        execute_schema_statement(pool, format!("DROP SCHEMA IF EXISTS {schema} CASCADE")).await
2972    }
2973
2974    async fn execute_schema_statement(pool: &PgPool, statement: String) -> anyhow::Result<()> {
2975        sqlx::query(AssertSqlSafe(statement)).execute(pool).await?;
2976        Ok(())
2977    }
2978
2979    fn cache_test_schema_name() -> String {
2980        let nanos = SystemTime::now()
2981            .duration_since(UNIX_EPOCH)
2982            .expect("system clock must be after UNIX epoch")
2983            .as_nanos();
2984
2985        format!("nt_blockchain_cache_test_{}_{}", std::process::id(), nanos)
2986    }
2987
2988    fn arbitrum() -> SharedChain {
2989        let Some(chain) = Chain::from_chain_id(42161) else {
2990            panic!("Arbitrum chain must exist in model definitions");
2991        };
2992
2993        Arc::new(chain.clone())
2994    }
2995
2996    fn uniswap_v3(chain: &SharedChain) -> SharedDex {
2997        Arc::new(Dex::new(
2998            (**chain).clone(),
2999            DexType::UniswapV3,
3000            "0x1F98431c8aD98523631AE4a59f267346ea31F984",
3001            0,
3002            AmmType::CLAMM,
3003            "PoolCreated",
3004            "Swap",
3005            "Mint",
3006            "Burn",
3007            "Collect",
3008        ))
3009    }
3010
3011    fn pancakeswap_v3(chain: &SharedChain) -> SharedDex {
3012        Arc::new(Dex::new(
3013            (**chain).clone(),
3014            DexType::PancakeSwapV3,
3015            "0x0BFbCF9fa4f9C56B0F40a671Ad40E0805A091865",
3016            0,
3017            AmmType::CLAMM,
3018            "PoolCreated",
3019            "Swap",
3020            "Mint",
3021            "Burn",
3022            "Collect",
3023        ))
3024    }
3025
3026    fn weth(chain: &SharedChain) -> Token {
3027        Token::new(
3028            Arc::clone(chain),
3029            address!("0x82aF49447D8a07e3bd95BD0d56f35241523fBab1"),
3030            "Wrapped Ether".to_string(),
3031            "WETH".to_string(),
3032            18,
3033        )
3034    }
3035
3036    fn usdc(chain: &SharedChain) -> Token {
3037        Token::new(
3038            Arc::clone(chain),
3039            address!("0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8"),
3040            "USD Coin".to_string(),
3041            "USDC".to_string(),
3042            6,
3043        )
3044    }
3045
3046    fn test_block(number: u64, timestamp: UnixNanos) -> Block {
3047        Block::new(
3048            format!("0x{number:064x}"),
3049            String::from("0x0"),
3050            number,
3051            Ustr::from("0x0000000000000000000000000000000000000000"),
3052            30_000_000,
3053            21_000,
3054            timestamp,
3055            Some(Blockchain::Arbitrum),
3056        )
3057    }
3058}