# Compare Uniswap V3 and V4 routes Compare Uniswap V3, V4, and split ETH → USDC routes at one pinned block. Simulate family leaders, rank after gas, and inspect every check and rejection reason. Canonical HTML: ## Goal Compare Uniswap V3, V4, and split ETH → USDC routes at one pinned Ethereum block. The notebook keeps routes as rows from quote through decision. It quotes the full set, simulates the leading V3, V4, and split routes as exact Universal Router calls, then ranks only calls that execute successfully and pass every check. Failed routes stay in the record with their rejection reasons. Simulation only. Nothing is signed or sent. ## Explore or rerun the route decision The guide opens with dated results for the default inputs. Change the trade size or sender, then select **Run all** to replace them with a fresh run against a finalized Ethereum mainnet block. ## Parameters - **client** (`client`) - **block_selector** (`block_selector`, type `text`) Default: `finalized`. - **Trade size** (`amount_in_eth`, type `decimal`) — ETH sent through every route. Default: `20`. - **max_slippage_bps** (`max_slippage_bps`, type `uint`) Default: `100`. - **V3 factory ABI** (`v3_factory_abi`, type `abi`) — The notebook calls getPool to discover V3 pools. Default: `[{"type":"function","name":"getPool","stateMutability":"view","inputs":[{"name":"tokenA","type":"address"},{"name":"tokenB","type":"address"},{"name":"fee","type":"uint24"}],"outputs":[{"name":"pool","type":"address"}]}]`. - **Chainlink feed ABI** (`chainlink_feed_abi`, type `abi`) — The notebook calls latestRoundData and decimals to price execution gas in USDC. Default: `[{"type":"function","name":"latestRoundData","stateMutability":"view","inputs":[],"outputs":[{"name":"roundId","type":"uint80"},{"name":"answer","type":"int256"},{"name":"startedAt","type":"uint256"},{"name":"updatedAt","type":"uint256"},{"name":"answeredInRound","type":"uint80"}]},{"type":"function","name":"decimals","stateMutability":"view","inputs":[],"outputs":[{"name":"decimals","type":"uint8"}]}]`. - **ERC-20 balance ABI** (`erc20_balance_abi`, type `abi`) — The notebook calls balanceOf before and after each simulation. Default: `[{"type":"function","name":"balanceOf","stateMutability":"view","inputs":[{"name":"account","type":"address"}],"outputs":[{"name":"balance","type":"uint256"}]}]`. - **V3 quoter ABI** (`v3_quoter_abi`, type `abi`) — The notebook calls quoteExactInputSingle for V3 route legs. Default: `[{"type":"function","name":"quoteExactInputSingle","stateMutability":"nonpayable","inputs":[{"name":"params","type":"tuple","components":[{"name":"tokenIn","type":"address"},{"name":"tokenOut","type":"address"},{"name":"amountIn","type":"uint256"},{"name":"fee","type":"uint24"},{"name":"sqrtPriceLimitX96","type":"uint160"}]}],"outputs":[{"name":"amountOut","type":"uint256"},{"name":"sqrtPriceX96After","type":"uint160"},{"name":"initializedTicksCrossed","type":"uint32"},{"name":"gasEstimate","type":"uint256"}]}]`. - **V4 quoter ABI** (`v4_quoter_abi`, type `abi`) — The notebook calls quoteExactInputSingle for V4 route legs. Default: `[{"type":"function","name":"quoteExactInputSingle","stateMutability":"nonpayable","inputs":[{"name":"params","type":"tuple","components":[{"name":"poolKey","type":"tuple","components":[{"name":"currency0","type":"address"},{"name":"currency1","type":"address"},{"name":"fee","type":"uint24"},{"name":"tickSpacing","type":"int24"},{"name":"hooks","type":"address"}]},{"name":"zeroForOne","type":"bool"},{"name":"exactAmount","type":"uint128"},{"name":"hookData","type":"bytes"}]}],"outputs":[{"name":"amountOut","type":"uint256"},{"name":"gasEstimate","type":"uint256"}]}]`. - **Universal Router codec ABI** (`universal_router_codec_abi`, type `abi`) — The notebook uses these functions to encode Universal Router calldata. Default: `[{"type":"function","name":"execute","stateMutability":"payable","inputs":[{"name":"commands","type":"bytes"},{"name":"inputs","type":"bytes[]"},{"name":"deadline","type":"uint256"}],"outputs":[]},{"type":"function","name":"wrapEthParam","inputs":[{"name":"recipient","type":"address"},{"name":"amount","type":"uint256"}],"outputs":[]},{"type":"function","name":"v3ExactInputParam","inputs":[{"name":"recipient","type":"address"},{"name":"amountIn","type":"uint256"},{"name":"amountOutMinimum","type":"uint256"},{"name":"path","type":"bytes"},{"name":"payerIsUser","type":"bool"}],"outputs":[]},{"type":"function","name":"v4Payload","inputs":[{"name":"actions","type":"bytes"},{"name":"params","type":"bytes[]"}],"outputs":[]},{"type":"function","name":"exactInputSingleParam","inputs":[{"name":"params","type":"tuple","components":[{"name":"poolKey","type":"tuple","components":[{"name":"currency0","type":"address"},{"name":"currency1","type":"address"},{"name":"fee","type":"uint24"},{"name":"tickSpacing","type":"int24"},{"name":"hooks","type":"address"}]},{"name":"zeroForOne","type":"bool"},{"name":"amountIn","type":"uint128"},{"name":"amountOutMinimum","type":"uint128"},{"name":"hookData","type":"bytes"}]}],"outputs":[]},{"type":"function","name":"settleAllParam","inputs":[{"name":"currency","type":"address"},{"name":"maxAmount","type":"uint256"}],"outputs":[]},{"type":"function","name":"takeAllParam","inputs":[{"name":"currency","type":"address"},{"name":"amountMinimum","type":"uint256"}],"outputs":[]}]`. - **V3 path types** (`v3_path_types_json`, type `json`) — Token-fee-token types for packed V3 swap paths. Default: `["address","uint24","address"]`. - **Sender** (`research_sender_address`, type `address`) — An EOA at the pinned block, funded with the selected trade amount. Default: `0x200000000000000000000000000000000000c0de`. ### 1. Create the market checkpoint Start by pinning the client to a finalized Ethereum mainnet block. Every contract read, quote, and simulation uses that state. The checkpoint also records the candidate pools, trade inputs, sender, router deadline, and the values used to price gas. `get_gas_price` samples the RPC gas price when the notebook runs; it is not part of the pinned state. The ETH/USD value comes from Chainlink at the pinned block. The notebook accepts it only if the oracle answer is valid and no more than two hours old, then treats `1 USDC = 1 USD` when converting gas cost to USDC. #### Market checkpoint Inspect the pinned block, pool counts, inputs, gas price, and oracle status. ```sql CREATE OR REPLACE TABLE ticket AS WITH base AS ( SELECT b.number AS block_number, b.hash AS block_hash, b.timestamp AS block_timestamp, parse_ether(CAST($amount_in_eth AS VARCHAR)) AS amount_in_raw, CAST($max_slippage_bps AS UBIGINT) AS slippage_bps, get_gas_price($client) AS gas_price_wei, CAST($research_sender_address AS ADDRESS) AS sender, (client_context(p.client)).chain_id::UBIGINT AS chain_id, -- Set the Universal Router deadline 15 minutes after the pinned block. (epoch(b.timestamp)::UBIGINT + 900)::UINT256 AS deadline, p.client, '0x66a9893cC07D91D95644AEDD05D03f95e1dBA8Af'::ADDRESS AS router, '0x000000000004444c5dc75cB358380D2e3dE08A90'::ADDRESS AS pool_manager, '0x52f0E24D1c21C8A0cB1e5a5dD6198556BD9E1203'::ADDRESS AS v4_quoter, '0x61fFE014bA17989E743c5F6cB21bF9697530B21e'::ADDRESS AS v3_quoter, '0x1F98431c8aD98523631AE4a59f267346ea31F984'::ADDRESS AS v3_factory, '0xcA11bde05977b3631167028862bE2a173976CA11'::ADDRESS AS multicall3, '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'::ADDRESS AS usdc, '0x43506849D7C04F9138D1A2050bbF3A0c054402dd'::ADDRESS AS usdc_implementation, '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'::ADDRESS AS weth, '0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS AS eth_usd_feed, '0x0000000000000000000000000000000000000000'::ADDRESS AS native_eth, '0x0000000000000000000000000000000000000000'::ADDRESS AS zero_hook FROM get_block($client, $block_selector) b CROSS JOIN LATERAL ( SELECT pin($client, b.number::UBIGINT, b.hash, true) AS client ) p ) SELECT base.*, -- Require 2 wei so each 50/50 split leg receives input. base.amount_in_raw >= 2::UINT256 -- Keep V4 quote and swap amounts within uint128. AND base.amount_in_raw <= CAST( '340282366920938463463374607431768211455' AS UINT256 ) -- Limit retained-output slippage to 100%. AND base.slippage_bps <= 10000 AS route_parameters_ok, try_sub(10000::UINT256, base.slippage_bps::UINT256) AS retained_slippage_bps, CASE WHEN code_at(client, sender) = '0x'::BYTES THEN 'SUPPORTED_EOA' ELSE 'UNSUPPORTED_CONTRACT_OR_DELEGATED_SENDER' END AS sender_status FROM base; CREATE OR REPLACE TABLE pools AS SELECT p.*, CASE WHEN protocol = 'v4' THEN struct_pack( currency0 := t.native_eth, currency1 := t.usdc, fee := fee::UINT24, tickSpacing := tick_spacing::INT24, hooks := t.zero_hook ) END AS v4_pool_key FROM ( VALUES ( 'v3_100'::VARCHAR, 'v3'::VARCHAR, 100::UINTEGER, NULL::INTEGER ), ( 'v3_500', 'v3', 500, NULL ), ( 'v3_3000', 'v3', 3000, NULL ), ( 'v4_500', 'v4', 500, 10 ), ( 'v4_3000', 'v4', 3000, 60 ) ) p(pool_key, protocol, fee, tick_spacing) CROSS JOIN ticket t; CREATE OR REPLACE TABLE v3_pools AS WITH calls AS ( SELECT pool_key AS source_id, t.client, t.v3_factory AS to_address, -- Include the getPool selector in the contract call. encode_function_data( $v3_factory_abi, 'getPool', t.weth, t.usdc, fee::UINT24 ) AS call_data, t.multicall3 AS multicall3_address FROM pools CROSS JOIN ticket t WHERE protocol = 'v3' ) SELECT p.*, d.pool::ADDRESS AS pool_address FROM read_contract_multicall( -- Sort sources to keep Multicall3 requests and results deterministic. (SELECT * FROM calls ORDER BY source_id), max_calls_per_batch := 16 ) r JOIN pools p ON p.pool_key = r.source_id CROSS JOIN LATERAL call_decode( $v3_factory_abi, 'getPool', r.return_data ) d WHERE r.ok AND r.call_success; CREATE OR REPLACE TABLE valuation AS -- Run the two Chainlink reads once for this valuation. WITH oracle_read AS MATERIALIZED ( SELECT (o.round_data).roundId AS round_id, (o.round_data).answer AS answer_raw, (o.round_data).updatedAt AS updated_at_raw, (o.round_data).answeredInRound AS answered_in_round, o.decimals, o.block_timestamp, o.gas_price_wei, to_timestamp((o.round_data).updatedAt) AS oracle_updated_at FROM ( SELECT read_contract( t.client, t.eth_usd_feed, $chainlink_feed_abi, 'latestRoundData' ) AS round_data, read_contract( t.client, t.eth_usd_feed, $chainlink_feed_abi, 'decimals' )::UTINYINT AS decimals, t.block_timestamp, t.gas_price_wei FROM ticket t ) o ) SELECT date_diff( 'second', oracle_updated_at, block_timestamp )::BIGINT AS oracle_age_seconds, CASE WHEN answer_raw <= 0::INT256 OR updated_at_raw = 0::UINT256 THEN 'ORACLE_INVALID' WHEN oracle_updated_at IS NULL THEN 'ORACLE_TIMESTAMP_INVALID' WHEN answered_in_round < round_id THEN 'ORACLE_ROUND_INCOMPLETE' WHEN oracle_updated_at > block_timestamp THEN 'ORACLE_FUTURE' -- Reject oracle answers older than two hours. WHEN oracle_age_seconds > 7200 THEN 'ORACLE_STALE' ELSE 'VALUATION_OK' END AS status, CASE WHEN status = 'VALUATION_OK' THEN answer_raw::UINT256 END AS eth_usd_raw, decimals AS oracle_decimals, gas_price_wei FROM oracle_read; SELECT t.chain_id, t.block_number, t.block_hash, t.block_timestamp, format_ether(t.amount_in_raw) AS amount_in_eth, t.amount_in_raw, t.gas_price_wei, t.deadline, t.sender, t.slippage_bps, t.sender_status, (SELECT count(*) FROM v3_pools) AS discovered_v3_pools, (SELECT count(*) FROM pools WHERE protocol = 'v4') AS derived_v4_pools, format_units(v.eth_usd_raw, v.oracle_decimals) AS eth_usd, format_units(v.gas_price_wei, 9) AS gas_price_gwei, v.oracle_age_seconds, v.status AS valuation_status FROM ticket t CROSS JOIN valuation v; ``` ### 2. Verify runtime-code identity Before quoting any route, the notebook checks every declared dependency and discovered V3 pool against its expected runtime-code hash. A missing pool or hash mismatch blocks quoting, so the notebook cannot return `PASS`. These checks confirm runtime-code identity at the pinned block. They do not establish that the contracts are secure or correct. #### Verify runtime code Check discovered pool coverage and approved runtime-code hashes before quoting. ```sql CREATE OR REPLACE TABLE approved_code_dependencies AS WITH manifest AS ( SELECT m.* FROM ticket t CROSS JOIN LATERAL ( VALUES ( 'router'::VARCHAR, t.router, '0x6a5f46971b50c6e1b7eef97902311444e479d734e4f80ad88367783cf373fe7f'::BYTES32 ), ( 'pool_manager', t.pool_manager, '0x785f1014552b7ce7d5fb7d0c970ca60edee94fd00425d7ca21609acac7ce1293'::BYTES32 ), ( 'weth', t.weth, '0xd0a06b12ac47863b5c7be4185c2deaad1c61557033f56c7d4ea74429cbb25e23'::BYTES32 ), ( 'usdc_proxy', t.usdc, '0xd80d4b7c890cb9d6a4893e6b52bc34b56b25335cb13716e0d1d31383e6b41505'::BYTES32 ), ( 'usdc_implementation', t.usdc_implementation, '0xcdfb7d322961af3acae7a8f7ee8b69c205b36f576cc5b077f170c7eb8ecbe3ea'::BYTES32 ), ( 'v3_quoter', t.v3_quoter, '0x06148f47d0f41a68d3bc970030a7150e5d608cfbc28d372440a2e41ce543d92b'::BYTES32 ), ( 'v4_quoter', t.v4_quoter, '0x06de58fa119c5deaa7a667fb92d3894e25d9160e62fb82c8d86d43b47eefe441'::BYTES32 ), ( 'v3_factory', t.v3_factory, '0x4d7b8525cd5d14343fa67a732fba5b24cddba11620ca88392f4ec6c52f91fd69'::BYTES32 ), ( 'multicall3', t.multicall3, '0xd5c15df687b16f2ff992fc8d767b4216323184a2bbc6ee2f9c398c318e770891'::BYTES32 ), ( 'chainlink_feed', t.eth_usd_feed, '0x4b79b5c8aee6da0f7b393e8b53e6265ef7320a1d16184c65bd3841b5aa3d700d'::BYTES32 ) ) m(name, address, expected_hash) UNION ALL SELECT p.pool_key, p.pool_address, CASE p.fee WHEN 100 THEN '0x65154c5c5a7a171a3d89df22d2a11319ddcb60bdf00ff9a4e3bd7312cb6badce'::BYTES32 WHEN 500 THEN '0xa981b66c747a3d9fa29d7e200d5faaa2826960523d0e5a0df8148e8868c480b4'::BYTES32 WHEN 3000 THEN '0xf2b8b58f95b1471751302e520a0e7c410ce9846ed46020be253dbd25fbb6da11'::BYTES32 END FROM v3_pools p ), observed AS ( SELECT m.*, keccak256(code_at(t.client, m.address)) AS observed_hash FROM manifest m CROSS JOIN ticket t ) SELECT *, observed_hash = expected_hash AS passed FROM observed; CREATE OR REPLACE TABLE code_manifest_status AS SELECT (SELECT count(*) FROM pools WHERE protocol = 'v3')::UINTEGER AS declared_v3_pool_count, (SELECT count(*) FROM v3_pools)::UINTEGER AS discovered_v3_pool_count, count(*) FILTER (WHERE passed IS NOT TRUE)::UINTEGER AS hash_mismatch_count, ( (SELECT count(*) FROM v3_pools) = (SELECT count(*) FROM pools WHERE protocol = 'v3') AND count(*) FILTER (WHERE passed IS NOT TRUE) = 0 ) AS code_manifest_ok FROM approved_code_dependencies; SELECT s.declared_v3_pool_count, s.discovered_v3_pool_count, s.hash_mismatch_count, s.code_manifest_ok, d.name, d.address, d.expected_hash, d.observed_hash, d.passed FROM approved_code_dependencies d CROSS JOIN code_manifest_status s ORDER BY d.name; ``` ### 3. Compare route quotes At the pinned block, quote every direct V3 and V4 route and every 50/50 split between the declared V3 and V4 pools. Rank them by quoted USDC output before gas. The top quote in each family advances to exact simulation, but a quote leader is not yet a winner. Every quote stays in the final comparison. An unsimulated route can still block `PASS`, so a simulated route does not win simply because it was one of the three routes executed. #### Rank route quotes Show every route, quoted USDC output, output per ETH, quote status, and distance from the top quote. ```sql SELECT CASE WHEN discovered_v3_pool_count <> declared_v3_pool_count THEN error('V3_POOL_DISCOVERY_INCOMPLETE') WHEN code_manifest_ok IS NOT TRUE THEN error('APPROVED_CODE_MISMATCH') ELSE true END AS trust_checkpoint_ok FROM code_manifest_status; CREATE OR REPLACE TABLE quote_surface AS WITH routes AS ( SELECT c.*, a.v3_amount_in_raw, t.amount_in_raw - a.v3_amount_in_raw AS v4_amount_in_raw, ((c.v3_share_pct > 0)::UINTEGER + (c.v4_share_pct > 0)::UINTEGER) AS expected_legs FROM ( SELECT pool_key AS route, protocol, CASE WHEN protocol = 'v3' THEN pool_key END AS v3_pool_key, CASE WHEN protocol = 'v4' THEN pool_key END AS v4_pool_key, CASE WHEN protocol = 'v3' THEN 100 ELSE 0 END::UINTEGER AS v3_share_pct, CASE WHEN protocol = 'v4' THEN 100 ELSE 0 END::UINTEGER AS v4_share_pct FROM pools UNION ALL SELECT 'split_' || v3.fee || '_' || v4.fee, 'split', v3.pool_key, v4.pool_key, 50::UINTEGER, 50::UINTEGER FROM pools v3 CROSS JOIN pools v4 WHERE v3.protocol = 'v3' AND v4.protocol = 'v4' ) c CROSS JOIN ticket t CROSS JOIN LATERAL ( SELECT muldiv( t.amount_in_raw, c.v3_share_pct::UINT256, 100::UINT256 ) AS v3_amount_in_raw ) a ), quote_legs AS ( SELECT c.route || ':v3' AS source_id, c.route, 'v3'::VARCHAR AS leg, c.v3_amount_in_raw AS amount_in_raw, t.client, t.v3_quoter AS to_address, -- Include the selector in the V3 Quoter call. encode_function_data( $v3_quoter_abi, 'quoteExactInputSingle', struct_pack( tokenIn := t.weth, tokenOut := t.usdc, amountIn := c.v3_amount_in_raw, fee := p.fee::UINT24, sqrtPriceLimitX96 := 0::UHUGEINT ) ) AS call_data, t.multicall3 AS multicall3_address FROM routes c JOIN pools p ON p.pool_key = c.v3_pool_key CROSS JOIN ticket t WHERE c.v3_amount_in_raw > 0::UINT256 UNION ALL SELECT c.route || ':v4', c.route, 'v4', c.v4_amount_in_raw, t.client, t.v4_quoter, -- Include the selector in the V4 Quoter call. encode_function_data( $v4_quoter_abi, 'quoteExactInputSingle', struct_pack( poolKey := p.v4_pool_key, zeroForOne := true, exactAmount := CAST(c.v4_amount_in_raw::VARCHAR AS UHUGEINT), hookData := '0x'::BYTES ) ), t.multicall3 FROM routes c JOIN pools p ON p.pool_key = c.v4_pool_key CROSS JOIN ticket t WHERE c.v4_amount_in_raw > 0::UINT256 ), raw AS MATERIALIZED ( -- Run the ordered quote multicall once before both decoders read it. SELECT * FROM read_contract_multicall( ( -- Sort legs to keep Multicall3 requests and results deterministic. SELECT * FROM quote_legs ORDER BY source_id ), max_calls_per_batch := 32 ) ), decoded AS ( SELECT l.route, l.leg, d.amountOut::UINT256 AS amount_out_raw FROM raw r JOIN quote_legs l USING (source_id) CROSS JOIN LATERAL call_decode( $v3_quoter_abi, 'quoteExactInputSingle', r.return_data ) d WHERE l.leg = 'v3' AND r.ok AND r.call_success UNION ALL SELECT l.route, l.leg, d.amountOut::UINT256 FROM raw r JOIN quote_legs l USING (source_id) CROSS JOIN LATERAL call_decode( $v4_quoter_abi, 'quoteExactInputSingle', r.return_data ) d WHERE l.leg = 'v4' AND r.ok AND r.call_success ) SELECT c.*, q.quoted_leg_count, CASE WHEN c.expected_legs = q.quoted_leg_count THEN q.v3_amount_out_raw + q.v4_amount_out_raw END AS quoted_output_raw, q.v3_amount_out_raw, q.v4_amount_out_raw, CASE WHEN c.expected_legs = q.quoted_leg_count THEN 'QUOTE_OK' ELSE 'QUOTE_INCOMPLETE' END AS quote_status FROM routes c LEFT JOIN ( SELECT route, count(*) FILTER (WHERE amount_out_raw IS NOT NULL)::UINTEGER AS quoted_leg_count, COALESCE(max(amount_out_raw) FILTER (WHERE leg = 'v3'), 0::UINT256) AS v3_amount_out_raw, COALESCE(max(amount_out_raw) FILTER (WHERE leg = 'v4'), 0::UINT256) AS v4_amount_out_raw FROM decoded GROUP BY route ) q USING (route); WITH displayed AS ( SELECT *, max(quoted_output_raw) FILTER (WHERE quote_status = 'QUOTE_OK') OVER () AS best_quoted_output_raw FROM quote_surface ) SELECT row_number() OVER ( ORDER BY quoted_output_raw DESC NULLS LAST, route )::UINTEGER AS quote_rank, route, protocol, v3_share_pct, v4_share_pct, format_ether(v3_amount_in_raw) AS v3_amount_in_eth, format_ether(v4_amount_in_raw) AS v4_amount_in_eth, expected_legs, format_units(quoted_output_raw, 6) AS quoted_output_usdc, -- Scale raw USDC output to one ETH and apply six decimals. format_units( muldiv( quoted_output_raw, parse_ether('1'), v3_amount_in_raw + v4_amount_in_raw ), 6 ) AS usdc_per_eth, -- Show signed hundredth-bps with half-up integer rounding. CASE WHEN quoted_output_raw IS NULL OR best_quoted_output_raw IS NULL OR best_quoted_output_raw = 0::UINT256 THEN NULL WHEN quoted_output_raw >= best_quoted_output_raw THEN ( muldiv( quoted_output_raw - best_quoted_output_raw, 1000000::UINT256, best_quoted_output_raw, 4::TINYINT ) // 100::UINT256 )::VARCHAR || '.' || lpad( ( muldiv( quoted_output_raw - best_quoted_output_raw, 1000000::UINT256, best_quoted_output_raw, 4::TINYINT ) % 100::UINT256 )::VARCHAR, 2, '0' ) ELSE '-' || ( muldiv( best_quoted_output_raw - quoted_output_raw, 1000000::UINT256, best_quoted_output_raw, 4::TINYINT ) // 100::UINT256 )::VARCHAR || '.' || lpad( ( muldiv( best_quoted_output_raw - quoted_output_raw, 1000000::UINT256, best_quoted_output_raw, 4::TINYINT ) % 100::UINT256 )::VARCHAR, 2, '0' ) END AS delta_to_best_bps, quote_status FROM displayed ORDER BY quote_rank; ``` ### 4. Simulate exact router calls Quotes and swaps use different calls, so a leading quote is not enough. The notebook encodes the top V3, V4, and split routes as exact Universal Router calls, then simulates all three against the same pinned state. It keeps the calldata, pinned block, gas estimate, and sender and router balances before and after execution for later checks. Each call allows output up to 100 bps below its quote. Because the quote and simulation use the same state, a later check requires the simulated output to match the quote exactly. #### Simulate exact calls Show each simulated route, payload hash, execution ID, pinned block, status, and result root. ```sql CREATE OR REPLACE TABLE route_programs AS WITH inputs AS ( SELECT q.*, muldiv( q.v3_amount_out_raw, t.retained_slippage_bps, 10000::UINT256 ) AS v3_min_raw, muldiv( q.v4_amount_out_raw, t.retained_slippage_bps, 10000::UINT256 ) AS v4_min_raw FROM quote_surface q CROSS JOIN ticket t WHERE q.quote_status = 'QUOTE_OK' AND t.sender_status = 'SUPPORTED_EOA' AND t.route_parameters_ok QUALIFY row_number() OVER ( PARTITION BY protocol ORDER BY quoted_output_raw DESC NULLS LAST, route ) = 1 ), payloads AS ( SELECT i.*, v3_pool.pool_address AS v3_pool_address, v3_pool.fee AS v3_pool_fee, i.v3_min_raw + i.v4_min_raw AS min_output_raw, -- Add the execute(...) selector to the router calldata. encode_function_data( $universal_router_codec_abi, 'execute', CASE i.protocol -- Use 0x0b WRAP_ETH, 0x00 V3_SWAP_EXACT_IN, and 0x10 V4_SWAP. WHEN 'v3' THEN '0x0b00'::BYTES WHEN 'v4' THEN '0x10'::BYTES ELSE '0x0b0010'::BYTES END, CASE i.protocol WHEN 'v3' THEN ARRAY[v3.wrap_input, v3.swap_input] WHEN 'v4' THEN ARRAY[v4.v4_input] ELSE ARRAY[v3.wrap_input, v3.swap_input, v4.v4_input] END, t.deadline )::BYTES AS calldata FROM inputs i LEFT JOIN v3_pools v3_pool ON v3_pool.pool_key = i.v3_pool_key LEFT JOIN pools v4_pool ON v4_pool.pool_key = i.v4_pool_key CROSS JOIN ticket t CROSS JOIN LATERAL ( SELECT -- Encode command inputs without selectors. CASE WHEN i.v3_amount_in_raw > 0::UINT256 THEN encode_function_args( $universal_router_codec_abi, 'wrapEthParam', -- Use ADDRESS_THIS to retain WETH in the router. '0x0000000000000000000000000000000000000002'::ADDRESS, i.v3_amount_in_raw ) END AS wrap_input, CASE WHEN i.v3_amount_in_raw > 0::UINT256 THEN encode_function_args( $universal_router_codec_abi, 'v3ExactInputParam', t.sender, i.v3_amount_in_raw, i.v3_min_raw, abi_encode_packed( $v3_path_types_json, t.weth, v3_pool.fee, t.usdc ), false ) END AS swap_input ) v3 CROSS JOIN LATERAL ( SELECT CASE WHEN i.v4_amount_in_raw > 0::UINT256 THEN encode_function_args( $universal_router_codec_abi, 'v4Payload', -- Use 0x06 SWAP_EXACT_IN_SINGLE, 0x0c SETTLE_ALL, and 0x0f TAKE_ALL. '0x060c0f'::BYTES, ARRAY[ encode_function_args( $universal_router_codec_abi, 'exactInputSingleParam', struct_pack( poolKey := v4_pool.v4_pool_key, zeroForOne := true, amountIn := CAST(i.v4_amount_in_raw::VARCHAR AS UHUGEINT), amountOutMinimum := CAST(i.v4_min_raw::VARCHAR AS UHUGEINT), hookData := '0x'::BYTES ) ), encode_function_args( $universal_router_codec_abi, 'settleAllParam', t.native_eth, i.v4_amount_in_raw ), encode_function_args( $universal_router_codec_abi, 'takeAllParam', t.usdc, i.v4_min_raw ) ] ) END AS v4_input ) v4 ) SELECT route AS candidate_key, protocol, v3_pool_key, quoted_output_raw, min_output_raw, calldata, keccak256(calldata) AS payload_hash, program( [ assume_no_code(t.sender, 'sender is modeled as an EOA'), assume_native_balance( t.sender, t.amount_in_raw, 'fund exactly the modeled msg.value' ) ], [ execute_call( -- Cap the simulation at 12,000,000 gas. t.sender, t.router, calldata, t.amount_in_raw, 12000000 ) ] ) AS program, list_concat( list_concat( [ observation_fact(t.sender, 'Simulated sender'), observation_fact(t.router, 'Universal Router'), observation_fact(t.weth, 'WETH'), observation_fact(t.usdc, 'USDC'), observation_fact(t.usdc_implementation, 'USDC implementation') ], CASE WHEN protocol IN ('v3', 'split') THEN [ observation_fact( v3_pool_address, CASE v3_pool_fee WHEN 100 THEN 'Uniswap V3 ETH/USDC 0.01%' WHEN 500 THEN 'Uniswap V3 ETH/USDC 0.05%' WHEN 3000 THEN 'Uniswap V3 ETH/USDC 0.3%' END ) ] ELSE []::OBSERVATION[] END ), list_concat( CASE WHEN protocol IN ('v4', 'split') THEN [ observation_fact(t.pool_manager, 'Uniswap V4 PoolManager') ] ELSE []::OBSERVATION[] END, [ observe('sender.native', native_balance(t.sender)), observe( 'sender.weth', read_contract(t.weth, $erc20_balance_abi, 'balanceOf', t.sender) ), observe( 'sender.usdc', read_contract(t.usdc, $erc20_balance_abi, 'balanceOf', t.sender) ), observe('router.native', native_balance(t.router)), observe( 'router.weth', read_contract(t.weth, $erc20_balance_abi, 'balanceOf', t.router) ), observe( 'router.usdc', read_contract(t.usdc, $erc20_balance_abi, 'balanceOf', t.router) ) ] ) ) AS observations FROM payloads CROSS JOIN ticket t; CREATE OR REPLACE TABLE executions AS -- Run the relation once before joining receipt metadata. WITH run_receipts AS MATERIALIZED ( SELECT r.execution_id, r.candidate_key FROM run(( SELECT t.client, p.candidate_key, p.program, p.observations FROM route_programs p CROSS JOIN ticket t )) r ) SELECT e.execution_id, r.candidate_key, e.chain_id, e.chain_profile, e.anchor_block_number, e.anchor_block_hash, e.status, e.error_code, e.fact_completeness, e.observation_status, e.total_transaction_gas_estimate, e.transaction_gas_status, e.execution_result_root FROM run_receipts r JOIN evm.executions e USING (execution_id); SELECT e.candidate_key, p.payload_hash, e.execution_id, e.status, e.error_code, e.anchor_block_number, e.anchor_block_hash, e.execution_result_root FROM executions e JOIN route_programs p USING (candidate_key) ORDER BY e.candidate_key; ``` ### 5. Rank routes after gas Only simulations that complete successfully and pass every route check enter the after-gas ranking. The simulated output must match the quote exactly. Balance observations must be complete, the call tree may reach only expected targets and contexts, and the router's ETH, WETH, and USDC balances must not change. For each eligible route, the notebook converts the gas estimate to USDC and subtracts it from the simulated output. The notebook reprices the same gas estimate at 0.5×, 1×, and 2× the sampled gas price to show whether the route order changes. Only the 1× ranking controls the final decision. Rejected routes stay visible with their first failed check. #### Rank routes after gas Show quoted output, simulated output, gas cost, output after gas, check status, and first failure reason. ```sql CREATE OR REPLACE TABLE expected_execution_targets AS SELECT candidate_key, name, address, expected_kind, expected_context FROM route_programs p LEFT JOIN v3_pools vp ON vp.pool_key = p.v3_pool_key CROSS JOIN ticket t CROSS JOIN LATERAL ( VALUES ( 'router'::VARCHAR, t.router, true, 'call'::VARCHAR, t.router ), ( 'weth', t.weth, p.protocol IN ('v3', 'split'), 'call', t.weth ), ( 'v3_pool', vp.pool_address, p.protocol IN ('v3', 'split'), 'call', vp.pool_address ), ( 'pool_manager', t.pool_manager, p.protocol IN ('v4', 'split'), 'call', t.pool_manager ), ( 'usdc_proxy', t.usdc, true, 'call', t.usdc ), ( 'usdc_implementation', t.usdc_implementation, true, 'delegatecall', t.usdc ) ) v(name, address, required, expected_kind, expected_context) WHERE required; CREATE OR REPLACE TABLE route_evidence AS WITH observation_evidence AS ( SELECT e.execution_id, e.candidate_key, count(*) FILTER (WHERE o.pair_status = 'complete')::UINTEGER AS successful_observation_keys, first( COALESCE( o.pre_error_code, o.terminal_error_code, 'OBSERVATION_PAIR_INCOMPLETE' ) ORDER BY o.observation_key ) FILTER ( WHERE o.pair_status <> 'complete' ) AS observation_error, first(o.delta_direction) FILTER (WHERE o.observation_key = 'sender.native') AS sender_native_direction, first(o.delta_magnitude) FILTER (WHERE o.observation_key = 'sender.native') AS sender_native_magnitude, first(o.delta_direction) FILTER (WHERE o.observation_key = 'sender.usdc') AS sender_usdc_direction, first(o.delta_magnitude) FILTER (WHERE o.observation_key = 'sender.usdc') AS sender_usdc_magnitude, first(o.changed) FILTER (WHERE o.observation_key = 'sender.weth') AS sender_weth_changed, first(o.changed) FILTER (WHERE o.observation_key = 'router.native') AS router_native_changed, first(o.changed) FILTER (WHERE o.observation_key = 'router.weth') AS router_weth_changed, first(o.changed) FILTER (WHERE o.observation_key = 'router.usdc') AS router_usdc_changed FROM executions e LEFT JOIN evm.execution_observation_pairs o ON o.execution_id = e.execution_id AND o.observation_key IN ( 'sender.native', 'sender.weth', 'sender.usdc', 'router.native', 'router.weth', 'router.usdc' ) GROUP BY e.execution_id, e.candidate_key ), step_evidence AS ( SELECT e.execution_id, count(s.step_index)::UINTEGER AS program_step_count, count(s.step_index) FILTER ( WHERE s.step_index = 0 AND s.sender = t.sender AND s.target = t.router AND s.value = t.amount_in_raw AND s.calldata_hash = p.payload_hash )::UINTEGER AS matching_program_step_count, first(s.status ORDER BY s.step_index) FILTER ( WHERE s.step_index = 0 ) AS step_status FROM executions e JOIN route_programs p ON p.candidate_key = e.candidate_key LEFT JOIN evm.execution_steps s USING (execution_id) CROSS JOIN ticket t GROUP BY e.execution_id ), frames AS ( SELECT e.candidate_key, c.code_address, lower(c.call_kind) AS call_kind, c.context_address FROM executions e JOIN evm.execution_frames c ON c.execution_id = e.execution_id AND c.attempt_kind = 'program_step' ), policy_evidence AS ( SELECT COALESCE(f.candidate_key, t.candidate_key) AS candidate_key, count(*) FILTER ( WHERE f.code_address IS NOT NULL AND t.address IS NULL )::UINTEGER AS unexpected_reached_targets, count(*) FILTER ( WHERE t.address IS NOT NULL AND f.code_address IS NULL )::UINTEGER AS missing_required_targets, count(*) FILTER ( WHERE f.code_address IS NOT NULL AND t.address IS NOT NULL AND ( f.call_kind <> t.expected_kind OR f.context_address <> t.expected_context ) )::UINTEGER AS wrong_call_contexts, count(*) FILTER ( WHERE f.code_address IS NOT NULL )::UINTEGER AS call_rows FROM frames f FULL JOIN expected_execution_targets t ON t.candidate_key = f.candidate_key AND t.address = f.code_address GROUP BY COALESCE(f.candidate_key, t.candidate_key) ), evidence AS ( SELECT p.candidate_key AS route, p.protocol, p.v3_pool_key, p.quoted_output_raw, p.min_output_raw, p.payload_hash, e.execution_id, e.chain_id AS receipt_chain_id, e.chain_profile AS receipt_chain_profile, e.status AS execution_status, e.error_code AS execution_error, e.fact_completeness, e.observation_status, e.total_transaction_gas_estimate AS transaction_gas_estimate, e.transaction_gas_status, s.step_status, s.program_step_count, s.matching_program_step_count, e.execution_result_root, e.anchor_block_number, e.anchor_block_hash, b.* EXCLUDE (candidate_key, execution_id), c.unexpected_reached_targets, c.missing_required_targets, c.wrong_call_contexts, c.call_rows, fx.summary_status AS effect_summary_status, fx.fact_completeness AS effect_fact_completeness, fx.reverted_frame_count, fx.discarded_frame_count, fx.poisoned_frame_count, fx.attempted_create_count, fx.attempted_selfdestruct_count, CASE WHEN b.sender_usdc_direction = 'increase' THEN b.sender_usdc_magnitude END AS executed_output_raw FROM route_programs p LEFT JOIN executions e ON e.candidate_key = p.candidate_key LEFT JOIN step_evidence s USING (execution_id) LEFT JOIN observation_evidence b ON b.candidate_key = p.candidate_key LEFT JOIN policy_evidence c ON c.candidate_key = p.candidate_key LEFT JOIN evm.execution_effect_summary fx USING (execution_id) ) SELECT e.*, g.checks, g.evidence_eligible, g.evidence_rejection_reason FROM evidence e CROSS JOIN ticket t CROSS JOIN LATERAL ( SELECT map( list(check_name ORDER BY precedence), list(passed ORDER BY precedence) ) AS checks, bool_and(passed) AS evidence_eligible, arg_min(rejection_reason, precedence) FILTER (WHERE passed IS NOT TRUE) AS evidence_rejection_reason FROM ( VALUES ( 1, 'execution_ok', (e.execution_status = 'success') IS TRUE, COALESCE(e.execution_error, 'EXECUTION_REVERTED') ), ( 2, 'execution_facts_ok', ( e.fact_completeness = 'complete' AND e.effect_summary_status = 'complete' AND e.effect_fact_completeness = 'complete' ) IS TRUE, 'EXECUTION_FACTS_INCOMPLETE' ), ( 3, 'execution_context_ok', ( e.receipt_chain_id = 1::UBIGINT AND e.receipt_chain_profile = 'ethereum-mainnet' AND e.anchor_block_number = t.block_number AND e.anchor_block_hash = t.block_hash AND e.execution_result_root IS NOT NULL ) IS TRUE, 'EXECUTION_CONTEXT_MISMATCH' ), ( 4, 'observations_ok', ( e.observation_error IS NULL AND e.observation_status = 'complete' AND e.successful_observation_keys = 6 ) IS TRUE, 'OBSERVATION_EVIDENCE_INCOMPLETE' ), ( 5, 'gas_ok', ( e.step_status = 'success' AND e.transaction_gas_status = 'complete_program' AND e.transaction_gas_estimate IS NOT NULL ) IS TRUE, 'GAS_EVIDENCE_INCOMPLETE' ), ( 6, 'transaction_binding_ok', ( e.program_step_count = 1 AND e.matching_program_step_count = 1 ) IS TRUE, 'TRANSACTION_BINDING_FAILED' ), ( 7, 'call_policy_ok', ( e.unexpected_reached_targets = 0 AND e.missing_required_targets = 0 AND e.wrong_call_contexts = 0 ) IS TRUE, 'CALL_POLICY_FAILED' ), ( 8, 'forbidden_effects_ok', ( e.reverted_frame_count = 0 AND e.discarded_frame_count = 0 AND e.poisoned_frame_count = 0 AND e.attempted_create_count = 0 AND e.attempted_selfdestruct_count = 0 ) IS TRUE, 'REVERTED_OR_FORBIDDEN_EFFECT' ), ( 9, 'quote_match_ok', (e.executed_output_raw = e.quoted_output_raw) IS TRUE, 'SAME_STATE_QUOTE_MISMATCH' ), ( 10, 'funding_ok', ( e.sender_native_direction = 'decrease' AND e.sender_native_magnitude = t.amount_in_raw ) IS TRUE, 'SENDER_BALANCE_DELTA_MISMATCH' ), ( 11, 'sender_weth_ok', (e.sender_weth_changed IS FALSE) IS TRUE, 'SENDER_BALANCE_DELTA_MISMATCH' ), ( 12, 'router_residual_ok', ( e.router_native_changed IS FALSE AND e.router_weth_changed IS FALSE AND e.router_usdc_changed IS FALSE ) IS TRUE, 'ROUTER_RESIDUAL_CHANGED' ) ) v(precedence, check_name, passed, rejection_reason) ) g; CREATE OR REPLACE TABLE route_rankings AS WITH unexecuted_bound AS ( -- Find the highest gross quote outside the executed set. SELECT max(q.quoted_output_raw) AS max_unexecuted_gross_quote_raw FROM quote_surface q LEFT JOIN route_programs p ON p.candidate_key = q.route WHERE p.candidate_key IS NULL ), costed AS ( SELECT s.scenario, s.gas_price_wei, e.*, g.gas_scenario_status, g.gas_cost_raw, n.net_output_raw, e.evidence_eligible IS TRUE AND g.gas_scenario_status = 'VALID' AND g.gas_cost_raw IS NOT NULL AND n.net_output_raw IS NOT NULL AS eligible, CASE WHEN e.evidence_eligible IS NOT TRUE THEN COALESCE(e.evidence_rejection_reason, 'INELIGIBLE_UNCLASSIFIED') WHEN g.gas_scenario_status <> 'VALID' THEN 'GAS_SCENARIO_INVALID' WHEN g.gas_cost_raw IS NULL OR n.net_output_raw IS NULL THEN 'GAS_VALUATION_INVALID' END AS rejection_reason FROM route_evidence e CROSS JOIN valuation v CROSS JOIN LATERAL ( VALUES -- Round half-price gas up. Use checked multiplication for double. ( '0.5x'::VARCHAR, evm_ceil_div(v.gas_price_wei, 2::UINT256) ), ( '1x', v.gas_price_wei ), ( '2x', try_mul(v.gas_price_wei, 2::UINT256) ) ) s(scenario, gas_price_wei) CROSS JOIN LATERAL ( SELECT CASE WHEN s.gas_price_wei IS NULL THEN 'ARITHMETIC_OVERFLOW' ELSE 'VALID' END AS gas_scenario_status, CASE WHEN e.transaction_gas_estimate IS NOT NULL AND s.gas_price_wei IS NOT NULL AND v.eth_usd_raw IS NOT NULL THEN muldiv( try_mul(e.transaction_gas_estimate::UINT256, s.gas_price_wei), v.eth_usd_raw, -- Convert wei and oracle-scaled USD to six-decimal USDC. Round up. parse_units('1', v.oracle_decimals::INTEGER + 12), 1::TINYINT ) END AS gas_cost_raw ) g CROSS JOIN LATERAL ( SELECT try_sub( e.executed_output_raw, g.gas_cost_raw ) AS net_output_raw ) n ) SELECT *, CASE WHEN net_output_raw IS NULL OR max_unexecuted_gross_quote_raw IS NULL OR max_unexecuted_gross_quote_raw = 0::UINT256 THEN NULL WHEN net_output_raw >= max_unexecuted_gross_quote_raw THEN ( muldiv( net_output_raw - max_unexecuted_gross_quote_raw, 1000000::UINT256, max_unexecuted_gross_quote_raw, 4::TINYINT ) // 100::UINT256 )::VARCHAR || '.' || lpad( ( muldiv( net_output_raw - max_unexecuted_gross_quote_raw, 1000000::UINT256, max_unexecuted_gross_quote_raw, 4::TINYINT ) % 100::UINT256 )::VARCHAR, 2, '0' ) ELSE '-' || ( muldiv( max_unexecuted_gross_quote_raw - net_output_raw, 1000000::UINT256, max_unexecuted_gross_quote_raw, 4::TINYINT ) // 100::UINT256 )::VARCHAR || '.' || lpad( ( muldiv( max_unexecuted_gross_quote_raw - net_output_raw, 1000000::UINT256, max_unexecuted_gross_quote_raw, 4::TINYINT ) % 100::UINT256 )::VARCHAR, 2, '0' ) END AS net_vs_best_unexecuted_bps, CASE -- Partition ineligible rows separately. Hide their ranks. WHEN eligible THEN row_number() OVER ( PARTITION BY scenario, eligible ORDER BY net_output_raw DESC NULLS LAST, route )::UINTEGER END AS scenario_rank FROM costed CROSS JOIN unexecuted_bound; SELECT scenario, format_units(gas_price_wei, 9) AS gas_price_gwei, scenario_rank, route, protocol, execution_id, execution_result_root, format_units(quoted_output_raw, 6) AS quoted_output_usdc, format_units(executed_output_raw, 6) AS executed_output_usdc, transaction_gas_estimate::UBIGINT AS transaction_gas_units, format_units(gas_cost_raw, 6) AS gas_cost_usdc, format_units(net_output_raw, 6) AS net_output_usdc, net_vs_best_unexecuted_bps, eligible, CASE WHEN eligible IS TRUE THEN 'ELIGIBLE' ELSE COALESCE(rejection_reason, 'INELIGIBLE_UNCLASSIFIED') END AS route_status FROM route_rankings ORDER BY CASE scenario WHEN '0.5x' THEN 1 WHEN '1x' THEN 2 ELSE 3 END, eligible DESC, scenario_rank NULLS LAST, quoted_output_raw DESC, route; ``` ### 6. Make the route decision `preflight_decision` is the final result. It can return `PASS` only if the market and runtime-code checks pass, every generated route has a complete quote, simulation evidence is complete for the leading V3, V4, and split routes, and at least one simulated route remains eligible after the execution checks. The final comparison gives unsimulated routes the benefit of the doubt. It uses the highest pre-gas quote among them as the bar. The leading eligible route must match or beat that quote after its own gas cost. Otherwise, the result is `FAIL`. A `PASS` applies only to the routes in this guide, these inputs, this pinned block, and the sampled gas price. It does not claim the best route outside this list or at a later block. #### Return the decision Show PASS or FAIL, the selected route on PASS, its difference from the highest unsimulated quote, the pinned block, and the execution ID. ```sql CREATE OR REPLACE TABLE leading_eligible_route AS SELECT r.*, r.net_output_raw >= r.max_unexecuted_gross_quote_raw AS winner_bound_passed, format_units(r.net_output_raw, 6) AS net_output_usdc, format_units(r.max_unexecuted_gross_quote_raw, 6) AS max_unexecuted_gross_quote_usdc, format_units( r.net_output_raw::INT256 - r.max_unexecuted_gross_quote_raw::INT256, 6 ) AS winner_bound_margin_usdc FROM route_rankings r WHERE r.scenario = '1x' AND r.scenario_rank = 1; CREATE OR REPLACE TABLE decision AS WITH slate AS ( SELECT ( (SELECT count(*) FROM pools) + (SELECT count(*) FROM pools WHERE protocol = 'v3') * (SELECT count(*) FROM pools WHERE protocol = 'v4') )::UINTEGER AS expected_candidate_count, (SELECT count(*) FROM quote_surface)::UINTEGER AS quote_candidate_count, (SELECT count(*) FROM quote_surface WHERE quote_status = 'QUOTE_OK')::UINTEGER AS quoted_candidate_count, (SELECT count(DISTINCT protocol) FROM quote_surface)::UINTEGER AS route_family_count, ( EXISTS (SELECT 1 FROM quote_surface WHERE protocol = 'v3') AND EXISTS (SELECT 1 FROM quote_surface WHERE protocol = 'v4') AND EXISTS (SELECT 1 FROM quote_surface WHERE protocol = 'split') AND NOT EXISTS ( SELECT 1 FROM quote_surface WHERE protocol NOT IN ('v3', 'v4', 'split') ) ) AS required_route_families_present, (SELECT count(*) FROM route_programs)::UINTEGER AS program_count, (SELECT count(DISTINCT protocol) FROM route_programs)::UINTEGER AS program_family_count, (SELECT count(*) FROM executions)::UINTEGER AS execution_count, (SELECT count(DISTINCT candidate_key) FROM executions)::UINTEGER AS distinct_execution_count, (SELECT count(*) FROM route_evidence)::UINTEGER AS evidence_count, ( SELECT count(*) FROM route_evidence WHERE checks['observations_ok'] IS TRUE AND checks['gas_ok'] IS TRUE AND checks['execution_facts_ok'] IS TRUE AND checks['execution_context_ok'] IS TRUE AND checks['transaction_binding_ok'] IS TRUE AND call_rows IS NOT NULL )::UINTEGER AS complete_evidence_count, (SELECT count(*) FROM route_rankings)::UINTEGER AS ranking_count, (SELECT count(*) FROM route_rankings WHERE scenario = '1x')::UINTEGER AS one_x_ranking_count, ( SELECT count(*) FROM expected_execution_targets x LEFT JOIN approved_code_dependencies a ON a.address = x.address WHERE a.address IS NULL )::UINTEGER AS missing_code_coverage_count ) SELECT CASE WHEN f.failure_reason IS NULL THEN 'PASS' ELSE 'FAIL' END AS outcome, f.failure_reason, s.* FROM ticket t CROSS JOIN valuation v CROSS JOIN code_manifest_status m CROSS JOIN slate s CROSS JOIN LATERAL ( SELECT CASE WHEN t.route_parameters_ok IS NOT TRUE THEN 'INVALID_ROUTE_PARAMETERS' WHEN t.chain_id IS DISTINCT FROM 1 THEN 'WRONG_CHAIN' WHEN t.sender_status IS DISTINCT FROM 'SUPPORTED_EOA' THEN t.sender_status WHEN v.status IS DISTINCT FROM 'VALUATION_OK' THEN v.status WHEN m.code_manifest_ok IS NOT TRUE OR s.missing_code_coverage_count <> 0 THEN 'APPROVED_CODE_MISMATCH' WHEN s.expected_candidate_count = 0 OR s.quote_candidate_count <> s.expected_candidate_count OR s.quoted_candidate_count <> s.expected_candidate_count OR s.required_route_families_present IS NOT TRUE OR s.program_count <> s.route_family_count OR s.program_family_count <> s.route_family_count THEN 'CANDIDATE_OR_QUOTE_INCOMPLETE' WHEN s.execution_count <> s.program_count OR s.distinct_execution_count <> s.program_count OR s.evidence_count <> s.program_count OR s.complete_evidence_count <> s.program_count OR s.ranking_count <> s.program_count * 3 OR s.one_x_ranking_count <> s.program_count THEN 'EXECUTION_OR_EVIDENCE_INCOMPLETE' WHEN NOT EXISTS ( SELECT 1 FROM route_rankings WHERE scenario = '1x' AND scenario_rank = 1 ) THEN 'NO_ELIGIBLE_ROUTE' WHEN NOT EXISTS ( SELECT 1 FROM leading_eligible_route WHERE winner_bound_passed IS TRUE ) THEN 'WINNER_BOUND_NOT_MET' ELSE NULL::VARCHAR END AS failure_reason ) f; SELECT d.outcome, d.failure_reason, d.quoted_candidate_count, d.execution_count, CASE WHEN d.outcome = 'PASS' THEN s.route ELSE NULL::VARCHAR END AS selected_route, CASE WHEN d.outcome = 'PASS' THEN s.net_output_usdc ELSE NULL::VARCHAR END AS net_output_usdc, CASE WHEN d.outcome = 'PASS' THEN s.max_unexecuted_gross_quote_usdc ELSE NULL::VARCHAR END AS max_unexecuted_gross_quote_usdc, CASE WHEN d.outcome = 'PASS' THEN s.winner_bound_margin_usdc ELSE NULL::VARCHAR END AS winner_bound_margin_usdc, CASE WHEN d.outcome = 'PASS' THEN s.winner_bound_passed ELSE NULL::BOOLEAN END AS winner_bound_passed, s.anchor_block_number::UBIGINT AS anchor_block_number, CASE WHEN d.outcome = 'PASS' THEN s.execution_id ELSE NULL::UUID END AS execution_id, CASE WHEN d.outcome = 'PASS' THEN s.execution_result_root ELSE NULL::BYTES32 END AS execution_result_root FROM decision d LEFT JOIN leading_eligible_route s ON true; ``` ### 7. Inspect decision checks `decision_check_summary` is the audit view for `preflight_decision`. It keeps rejected routes visible and shows each route or global check with its observed value, expected value, and rejection reason. Treat `preflight_decision` as the final result. This table explains that result, but it does not expose every completeness count used to calculate it. #### Review decision checks Show route and global checks with observed and expected values. Failed routes retain their first rejection reason. ```sql WITH route_checks AS ( SELECT 1::UINTEGER AS scope_order, 'route'::VARCHAR AS scope, r.route, u.check_ordinality::UBIGINT AS check_ordinality, u.entry.key::VARCHAR AS check_name, u.entry.value::BOOLEAN AS passed, COALESCE(u.entry.value::VARCHAR, 'NULL') AS observed, 'true'::VARCHAR AS expected, CASE WHEN u.entry.value IS NOT TRUE THEN COALESCE( r.evidence_rejection_reason, r.rejection_reason, 'INELIGIBLE_UNCLASSIFIED' ) END AS rejection_reason FROM route_rankings r CROSS JOIN UNNEST(map_entries(r.checks)) WITH ORDINALITY AS u(entry, check_ordinality) WHERE r.scenario = '1x' ), global_checks AS ( SELECT 2::UINTEGER AS scope_order, 'global'::VARCHAR AS scope, NULL::VARCHAR AS route, g.check_ordinality::UBIGINT AS check_ordinality, g.check_name, g.passed, g.observed, g.expected, CASE WHEN g.passed IS NOT TRUE THEN g.rejection_reason END AS rejection_reason FROM ticket t CROSS JOIN valuation v CROSS JOIN code_manifest_status m CROSS JOIN decision d LEFT JOIN leading_eligible_route s ON true CROSS JOIN LATERAL ( VALUES ( 1, 'market_context'::VARCHAR, ( t.chain_id IS NOT DISTINCT FROM 1 AND t.sender_status IS NOT DISTINCT FROM 'SUPPORTED_EOA' AND v.status IS NOT DISTINCT FROM 'VALUATION_OK' AND t.route_parameters_ok IS TRUE ) IS TRUE, concat_ws( '; ', 'chain_id=' || t.chain_id::VARCHAR, 'sender=' || t.sender_status, 'valuation=' || v.status, CASE WHEN t.route_parameters_ok THEN 'inputs=valid' ELSE 'inputs=invalid' END ), 'chain_id=1; sender=SUPPORTED_EOA; valuation=VALUATION_OK; inputs=valid'::VARCHAR, COALESCE(d.failure_reason, 'MARKET_CONTEXT_INVALID') ), ( 2, 'trusted_code_coverage', ( m.code_manifest_ok IS TRUE AND d.missing_code_coverage_count = 0 ) IS TRUE, concat_ws( '; ', 'declared_v3_pools=' || m.declared_v3_pool_count::VARCHAR, 'discovered_v3_pools=' || m.discovered_v3_pool_count::VARCHAR, 'hash_mismatches=' || m.hash_mismatch_count::VARCHAR, 'missing_coverage=' || d.missing_code_coverage_count::VARCHAR ), 'discovered_v3_pools=declared_v3_pools; hash_mismatches=0; missing_coverage=0', COALESCE(d.failure_reason, 'APPROVED_CODE_MISMATCH') ), ( 3, 'candidate_slate', ( d.expected_candidate_count > 0 AND d.quote_candidate_count = d.expected_candidate_count AND d.quoted_candidate_count = d.expected_candidate_count AND d.required_route_families_present IS TRUE AND d.program_count = d.route_family_count AND d.program_family_count = d.route_family_count AND d.execution_count = d.program_count ) IS TRUE, concat_ws( '; ', 'generated_routes=' || d.quote_candidate_count::VARCHAR, 'quoted_routes=' || d.quoted_candidate_count::VARCHAR, 'families=' || d.route_family_count::VARCHAR, 'programs=' || d.program_count::VARCHAR, 'executions=' || d.execution_count::VARCHAR ), 'all generated routes quoted; one program and execution per V3/V4/split family', COALESCE(d.failure_reason, 'CANDIDATE_OR_QUOTE_INCOMPLETE') ), ( 4, 'eligible_route', s.route IS NOT NULL, 'route=' || COALESCE(s.route, 'none'), 'at least one eligible route', COALESCE(d.failure_reason, 'NO_ELIGIBLE_ROUTE') ), ( 5, 'winner_bound', s.winner_bound_passed IS TRUE, COALESCE( concat_ws( '; ', 'leading_net=' || s.net_output_usdc, 'highest_unsimulated_gross=' || s.max_unexecuted_gross_quote_usdc, 'margin=' || s.winner_bound_margin_usdc ), d.failure_reason, 'no leading eligible route' ), 'leading_net >= highest_unsimulated_gross', COALESCE(d.failure_reason, 'WINNER_BOUND_NOT_MET') ) ) g( check_ordinality, check_name, passed, observed, expected, rejection_reason ) ) SELECT scope, route, check_name, passed, observed, expected, rejection_reason FROM ( SELECT * FROM route_checks UNION ALL SELECT * FROM global_checks ) summary ORDER BY scope_order, route NULLS LAST, check_ordinality; ``` ### 8. Inspect the selected call path After `PASS`, inspect the selected route's call tree instead of stopping at its final output. Each row identifies the code address reached, its call type and context, and its position in the tree. `UNEXPECTED_TARGET` marks code outside the allowed route targets. The first row also includes the retained execution record for further queries. #### Trace selected calls Show each reached code address, call type, context, frame status, and allowlist label. ```sql SELECT CASE WHEN c.frame_index = 0 THEN execution_evidence(s.execution_id) END AS execution_evidence, COALESCE(t.name, 'UNEXPECTED_TARGET') AS target, c.call_kind AS kind, c.value, c.frame_status, c.context_address, c.code_address, c.frame_index, c.parent_frame_index FROM decision d JOIN leading_eligible_route s ON d.outcome = 'PASS' JOIN evm.execution_frames c ON c.execution_id = s.execution_id AND c.attempt_kind = 'program_step' LEFT JOIN expected_execution_targets t ON t.candidate_key = s.route AND t.address = c.code_address ORDER BY c.frame_index; ``` ## Query the decision record The winning route is only one row in the result. The notebook keeps the pinned block, full route set, encoded calls, simulation results, checks, and rejection reasons together as queryable tables. Change the route set or ranking rule and run the notebook again. You can inspect which route ranks first after gas and whether its exact router call matched expectations. Call frames and balance changes are already available as tables, so you can query them without first building a custom trace parser. The decision applies only to the routes, inputs, pinned block, and sampled gas price used here. `execution_result_root` covers the simulated execution only, not the quotes or ranking SQL. Nothing is signed or sent. ## Functions used in this guide - [get_block](/docs/functions/get_block.md) - [get_gas_price](/docs/functions/get_gas_price.md) - [client_context](/docs/functions/client_context.md) - [pin](/docs/functions/pin.md) - [read_contract](/docs/functions/read_contract.md) - [read_contract_multicall](/docs/functions/read_contract_multicall.md) - [native_balance](/docs/functions/native_balance.md) - [code_at](/docs/functions/code_at.md) - [parse_units](/docs/functions/parse_units.md) - [parse_ether](/docs/functions/parse_ether.md) - [format_units](/docs/functions/format_units.md) - [format_ether](/docs/functions/format_ether.md) - [muldiv](/docs/functions/muldiv.md) - [evm_ceil_div](/docs/functions/evm_ceil_div.md) - [try_mul](/docs/functions/try_mul.md) - [try_sub](/docs/functions/try_sub.md) - [keccak256](/docs/functions/keccak256.md) - [abi_encode_packed](/docs/functions/abi_encode_packed.md) - [encode_function_data](/docs/functions/encode_function_data.md) - [encode_function_args](/docs/functions/encode_function_args.md) - [call_decode](/docs/functions/call_decode.md) - [to_timestamp](/docs/functions/to_timestamp.md) - [client](/docs/functions/client.md) - [program](/docs/functions/program.md) - [execute_call](/docs/functions/execute_call.md) - [assume_no_code](/docs/functions/assume_no_code.md) - [assume_native_balance](/docs/functions/assume_native_balance.md) - [observation_fact](/docs/functions/observation_fact.md) - [observe](/docs/functions/observe.md) - [run](/docs/functions/run.md)