View this page as Markdown

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.

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.

Inputs

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.

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;
Loading cached snapshot · 1 rows

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.

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;
Loading cached snapshot · 13 rows

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.

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;
Loading cached snapshot · 11 rows

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.

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;
Loading cached snapshot · 3 rows

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.

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;
Loading cached snapshot · 9 rows

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.

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;
Loading cached snapshot · 1 rows

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.

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;
Loading cached snapshot · 41 rows

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.

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;
Loading cached snapshot · 9 rows

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.