Create `stg_chain__proxies` model

[ccerv1]

What is it?

We need a staging model for each chain that captures different proxies:

• block_timestamp
• transaction_hash
• deployer_address
• proxy_address
• proxy_type

Here's the deployer for Safe: https://github.com/safe-global/safe-singleton-factory?tab=readme-ov-file#expected-addresses

And the same for 4337: https://docs.safe.global/home/4337-supported-networks

Arachnid: https://github.com/Arachnid/deterministic-deployment-proxy

[ccerv1]

Excellent flow chart of 4337 UserOps: https://github.com/safe-global/safe-modules/tree/main/modules/4337

sequenceDiagram
    actor B as Bundler
    participant E as Entry Point
    participant P as Safe Proxy
    participant S as Safe Singleton
    participant M as Safe 4337 Module
    actor T as Target

    B->>+E: Submit User Operations
    E->>+P: Validate User Operation
    P-->>S: Load Safe logic
    Note over P, M: Gas overhead for calls and storage access
    P->>+M: Forward validation
    Note over P, M: Load fallback handler ~2100 gas<br>Intital module access ~2600 gas
    M->>P: Check signatures
    P-->>S: Load Safe logic
    Note over P, M: Call to Safe Proxy ~100 gas<br>Load logic ~100 gas
    opt Pay required fee
        M->>P: Trigger fee payment
        P-->>S: Load Safe logic
        Note over P, M: Module check ~2100 gas<br>Call to Safe Proxy ~100 gas<br>Load logic ~100 gas
        P->>E: Perform fee payment
    end
    M-->>-P: Validation response
    P-->>-E: Validation response
    Note over P, M: Total gas overhead<br>Without fee payment ~4.900 gas<br>With fee payment ~7.200 gas

    E->>+P: Execute User Operation
    P-->>S: Load Safe logic
    P->>+M: Forward execution
    Note over P, M: Call to Safe Proxy ~100 gas<br>Call to fallback handler ~100 gas
    M->>P: Execute From Module
    P-->>S: Load Safe logic
    Note over P, M: Call to Safe Proxy ~100 gas<br>Module check ~100 gas
    P->>+T: Perform transaction
    opt Bubble up return data
        T-->>-P: Call Return Data
        P-->>M: Call Return Data
        M-->>-P: Call return data
        P-->>-E: Call return data
    end

[ccerv1]

I am analyzing the patterns in this sheet here

Also using this query to facilitate:

with proxy_contracts as (
  select * 
    from UNNEST([ STRUCT
    (
      'SAFE' as proxy_type,
      '1.4.1' as `version`,
      LOWER('0x4e1DCf7AD4e460CfD30791CCC4F9c8a4f820ec67') as factory_address
    ),
    ( 
      'SAFE',
      '1.3.0',
      LOWER('0xC22834581EbC8527d974F8a1c97E1bEA4EF910BC')
    ), 
    (
      'SAFE',
      '1.1.1',
      LOWER('0x76E2cFc1F5Fa8F6a5b3fC4c8F4788F0116861F9B')
    ),
    (
      'SAFE',
      '1.0.0',
      LOWER('0x12302fE9c02ff50939BaAaaf415fc226C078613C')
    ),  
    (
      'ARACHNID',
      '1.0.0',
      --LOWER('0x4e59b44847b379578588920cA78FbF26c0B4956C')
      LOWER('0x3fAB184622Dc19b6109349B94811493BF2a45362')
    ),
    (
      'ENTRYPOINT',
      '0.0.7',
      LOWER('0x0000000071727De22E5E9d8BAf0edAc6f37da032')
    ),
    (
      'ENTRYPOINT',
      '0.0.6',
      LOWER('0x5ff137d4b0fdcd49dca30c7cf57e578a026d2789')
    )
  ])

),
proxy_txns as (
  select
    proxies.proxy_type as proxy_type,
    traces.block_timestamp, 
    traces.transaction_hash,
    traces.from_address as proxy_address,
    traces.to_address
  from `superchain.optimism_traces` as traces
  inner join proxy_contracts as proxies
    on lower(traces.from_address) = lower(proxies.factory_address)
  where
    traces.block_timestamp >= '2024-06-01'
    and traces.trace_type = 'call'
    and traces.from_address != traces.to_address
)

select
  proxy_type,
  proxy_address,
  to_address,
  transaction_hash
from proxy_txns
where proxy_address != '0x0000000071727de22e5e9d8baf0edac6f37da032'
order by transaction_hash desc

[ccerv1]

Example Daimo: https://basescan.org/tx/0x5bf8b7a05b79b686917f9539dcd08a4140e42ecc95e075c307d9098f2c39e8d2

select distinct to_address
from `oso_playground.stg_base__proxies`
where transaction_hash = '0x5bf8b7a05b79b686917f9539dcd08a4140e42ecc95e075c307d9098f2c39e8d2'

result:

[{
  "to_address": "0x27785ad361898b526f37d87c4facfd757ff0622f"
}, {
  "to_address": "0x2a6d311394184eeb6df8fbbf58626b085374ffe7"
}, {
  "to_address": "0xa9e1ccb08053e4f5dabb506718352389c1547462"
}]

interpretation:

• ERC1967 Proxy: 0x277
• Daimo deployer: 0x2a6
• Daimo Paymaster: 0xa93

[ccerv1]

Simulation logic on the new model:

with proxy_contracts as (
  select * 
    from UNNEST([ STRUCT
    (
      'SAFE' as proxy_type,
      '1.4.1' as `version`,
      LOWER('0x4e1DCf7AD4e460CfD30791CCC4F9c8a4f820ec67') as factory_address
    ),
    ( 
      'SAFE',
      '1.3.0',
      LOWER('0xC22834581EbC8527d974F8a1c97E1bEA4EF910BC')
    ), 
    (
      'SAFE',
      '1.1.1',
      LOWER('0x76E2cFc1F5Fa8F6a5b3fC4c8F4788F0116861F9B')
    ),
    (
      'SAFE',
      '1.0.0',
      LOWER('0x12302fE9c02ff50939BaAaaf415fc226C078613C')
    ),  
    (
      'ENTRYPOINT',
      '0.0.7',
      LOWER('0x0000000071727De22E5E9d8BAf0edAc6f37da032')
    ),
    (
      'ENTRYPOINT',
      '0.0.6',
      LOWER('0x5ff137d4b0fdcd49dca30c7cf57e578a026d2789')
    )
  ])

),
proxy_txns as (
  select
    traces.transaction_hash, 
    proxies.proxy_type,
    traces.trace_type,
    traces.call_type,
    case
      when lower(traces.from_address) = lower(proxies.factory_address) then traces.from_address
      when lower(traces.to_address) = lower(proxies.factory_address) then traces.to_address
      else null
    end as proxy_address,
    traces.from_address,
    traces.to_address
  from `superchain.base_traces` as traces
  inner join proxy_contracts as proxies
    on lower(traces.from_address) = lower(proxies.factory_address)
    or lower(traces.to_address) = lower(proxies.factory_address)
  where
    traces.block_timestamp >= '2024-06-01'
    and traces.status = 1
),

result as (
  select
    transaction_hash,
    trace_type,
    call_type,
    proxy_type,
    proxy_address,
    from_address,
    to_address
  from proxy_txns
  where
    proxy_address is not null
    and from_address != to_address
)

select
  proxy_address,
  proxy_type,
  trace_type,
  call_type,
  count(distinct from_address) as from_address,
  count(distinct to_address) as to_address,
  count(*) as total_txns,
  sum(case when from_address = proxy_address then 1 else 0 end) as from_txns,
  sum(case when to_address = proxy_address then 1 else 0 end) as to_txns
from result
group by 1,2,3,4
order by 7 desc

[ccerv1]

Test to see if it's working for the Daimo account factory:

with hashes as (
  select transaction_hash
  from `oso_playground.stg_base__proxies`
  where from_address = LOWER('0x2A6d311394184EeB6Df8FBBF58626B085374Ffe7')
)

select
  to_address,
  count(*) as txns
from `oso_playground.stg_base__proxies` 
where
  transaction_hash in (select transaction_hash from hashes)
  and from_address != LOWER('0x2A6d311394184EeB6Df8FBBF58626B085374Ffe7')
group by to_address
order by txns desc

~ returns about 1800 results

[ccerv1]

Additional resources:

Good overview from Dune:

• https://dune.com/ilemi/erc-and-eip-starter-kit#accounts

And specific this query includes the canonical 4337 contracts by chain:

• https://dune.com/queries/2381969/4737936

Finally, here is the reference EIP for completeness: https://eips.ethereum.org/EIPS/eip-4337

[ccerv1]

See example from Superfluid: https://dune.com/queries/3832736/6446295