Consultas a dados UTXO e a logs e rastros da EVM

A maior parte do que um investigador precisa obter de uma blockchain não está armazenada como uma resposta pronta. No Bitcoin e em outras blockchains UTXO, não há saldos para consultar: cada saldo, taxa e fluxo precisa ser reconstruído unindo saídas às entradas que posteriormente as gastam. Nas blockchains EVM, movimentações de tokens, aprovações e pagamentos de contratos ficam em logs de eventos e chamadas internas, e não na própria transação. Tabelas decodificadas abrangem contratos populares, mas, mais cedo ou mais tarde, um caso envolve um token novo, um contrato não verificado ou um padrão que ninguém decodificou. Esta aula mostra como escrever essas junções e decodificações corretamente em SQL, para que cada valor em um relatório se apoie em uma consulta que outro perito possa executar novamente e obter o mesmo resultado. Ela usa o esquema genérico da Aula 2.7 e se baseia nos padrões de junção, janela e CTE da Aula 2.8.

Tabelas UTXO e a junção por outpoint

Os conjuntos de dados UTXO dividem cada transação em linhas com dois níveis de granularidade (valores em satoshis):

Uma saída é identificada por seu outpoint: hash da transação mais índice da saída. Uma entrada indica o outpoint que gasta por meio de spent_transaction_hash e spent_output_index. Neste esquema, as transações coinbase não têm linhas em inputs; alguns conjuntos de dados mantêm uma entrada coinbase com um outpoint vazio, portanto, verifique antes de contar.

Uma entrada bruta contém apenas o outpoint, um script e um número de sequência, sem endereço nem valor (Aula 2.2). As colunas address, value e script_type em inputs existem porque o pipeline consultou cada saída gasta e copiou seus campos, uma etapa que as ferramentas blockchain-etl chamam de enriquecimento. É uma conveniência e uma dependência: se o enriquecimento falhou em alguns blocos, essas entradas têm valores NULL e todas as taxas e saldos calculados a partir delas estão errados. Conte as entradas com value NULL no seu intervalo antes de confiar nesses dados.

Mais duas armadilhas de estrutura:

• Arrays de endereços. Alguns conjuntos de dados, incluindo o conjunto de dados Bitcoin do BigQuery, armazenam addresses como um array, porque scripts multisig puros contêm várias chaves e scripts não padronizados não contêm nenhuma. Para obter uma linha por endereço, expanda o array: CROSS JOIN UNNEST(addresses) AS address no BigQuery, CROSS JOIN UNNEST(addresses) AS t(address) no Trino e no DuckDB, unnest() no PostgreSQL, LATERAL FLATTEN no Snowflake, explode() no Spark SQL. Saídas OP_RETURN mantêm um array NULL ou vazio.
• txids duplicados. Duas transações coinbase de 2010 compartilham o txid de transações coinbase anteriores, um problema que a BIP 30 proibiu posteriormente. Quando a exatidão for importante, use o hash e o número do bloco como chave.

A aresta do grafo de gastos UTXO é a junção pelas duas partes do outpoint:

inputs.spent_transaction_hash = outputs.transaction_hash AND inputs.spent_output_index = outputs.output_index

Unir apenas pelo hash associa cada entrada a todas as saídas da transação anterior.

Saídas não gastas e saldos em um ponto no tempo

Encontrar UTXOs é uma antijunção: faça uma junção à esquerda de saídas com entradas e mantenha as linhas em que nenhuma entrada correspondeu.

-- Saídas não gastas de um endereço até o bloco 870.000
SELECT o.transaction_hash, o.output_index, o.value / 1e8 AS btc
FROM outputs AS o
LEFT JOIN inputs AS i
  ON  i.spent_transaction_hash = o.transaction_hash
  AND i.spent_output_index     = o.output_index
  AND i.block_number          <= 870000     -- apenas gastos até o ponto de corte
WHERE o.address = 'bc1q…7k3f'
  AND o.block_number <= 870000
  AND i.transaction_hash IS NULL;           -- nenhuma entrada de gasto encontrada

Somar value fornece o saldo do endereço nessa altura. No exemplo, o endereço recebeu 0,05 e 0,03 BTC; a primeira saída foi gasta no bloco 870.000 e a segunda no bloco 870.005, portanto, o saldo no bloco 870.000 é 0,03 BTC.

A condição do ponto de corte em i.block_number deve ficar na cláusula ON. Se movida para WHERE, ela é avaliada após a junção, quando i.block_number é NULL para todas as saídas sem correspondência, de modo que a consulta retorna zero linhas em vez de 0,03 BTC. NOT EXISTS (SELECT 1 FROM inputs …) expressa a mesma antijunção sem essa armadilha. Para um histórico de saldo, empilhe as saídas destinadas ao endereço como valores positivos e as entradas enriquecidas provenientes dele como valores negativos usando UNION ALL, e então execute uma soma de janela na ordem dos blocos (Aula 2.8).

Taxas sem multiplicação de linhas na junção

Uma taxa é a soma das entradas menos a soma das saídas. Unir entradas a saídas pelo hash da transação e somar ambos os lados é errado: uma transação com 3 entradas e 4 saídas produz 12 linhas na junção e multiplica ambas as somas. Agregue cada lado primeiro e depois una os totais.

-- Taxa por transação: agregue cada lado e depois faça a junção
WITH vin AS (
  SELECT transaction_hash, COUNT(*) AS n_in, SUM(value) AS in_sat
  FROM inputs WHERE block_number = 870000
  GROUP BY transaction_hash
),
vout AS (
  SELECT transaction_hash, COUNT(*) AS n_out, SUM(value) AS out_sat
  FROM outputs WHERE block_number = 870000
  GROUP BY transaction_hash
)
SELECT vout.transaction_hash, n_in, n_out,
       in_sat - out_sat AS fee_sat          -- coinbase não tem entradas: excluída pela junção
FROM vout
JOIN vin ON vin.transaction_hash = vout.transaction_hash
ORDER BY fee_sat DESC;

No bloco de exemplo, uma transação gasta 5.000.000 e 2.000.000 sat e cria saídas de 6.000.000 e 991.000, portanto, sua taxa é de 9.000 sat. A coinbase desaparece na junção interna, o que é correto: ela não paga taxa. Divida por virtual_size para obter uma taxa por unidade de tamanho. Muitos conjuntos de dados pré-calculam fee; recalculá-la em uma amostra é um bom teste do enriquecimento desses dados.

Heurísticas como indicadores armazenados

As heurísticas do Módulo 4 tornam-se indicadores: colunas que marcam candidatos, não conclusões. Um sinal mais forte de troco (Aula 4.2) é a correspondência do tipo de script: em uma transação com duas saídas cujas entradas compartilham um único tipo de script, a única saída desse tipo é uma candidata a troco.

-- Candidatas a troco: a única saída cujo tipo de script corresponde ao das entradas
WITH tx_in AS (
  SELECT transaction_hash, MIN(script_type) AS in_type
  FROM inputs WHERE block_number BETWEEN 870000 AND 870099
  GROUP BY transaction_hash
  HAVING COUNT(DISTINCT script_type) = 1      -- todas as entradas compartilham um tipo
),
outs AS (
  SELECT o.transaction_hash, o.output_index, o.address, o.value,
         CASE WHEN o.script_type = t.in_type THEN 1 ELSE 0 END AS type_match,
         COUNT(*) OVER (PARTITION BY o.transaction_hash) AS n_out
  FROM outputs AS o JOIN tx_in AS t ON t.transaction_hash = o.transaction_hash
  WHERE o.block_number BETWEEN 870000 AND 870099
)
SELECT transaction_hash, output_index, address, value,
       CASE WHEN MOD(value, 100000) <> 0 THEN 1 ELSE 0 END AS not_round
FROM (SELECT outs.*, SUM(type_match) OVER (PARTITION BY transaction_hash) AS n_match
      FROM outs) AS x
WHERE n_out = 2 AND n_match = 1 AND type_match = 1;

A consulta externa também adiciona um sinal mais fraco: um valor que não é um múltiplo redondo de 100.000 sat. No exemplo, entradas SegWit nativas pagam 6.000.000 sat a um endereço P2SH e 991.000 a um novo endereço SegWit nativo; a saída de 991.000 é sinalizada pelos dois critérios. Armazene os indicadores com o nome e a versão da heurística e combine-os em um nível de confiança na análise, nunca em uma junção codificada de forma fixa. A correspondência do tipo de script falha quando o destinatário usa o mesmo tipo de carteira, algo cada vez mais comum com a disseminação do Taproot, e quando as carteiras variam deliberadamente os tipos de troco.

A heurística de propriedade comum das entradas (Aula 4.1) torna-se uma lista de arestas de endereços que gastaram em conjunto:

-- Arestas de entradas comuns, ignorando transações semelhantes a CoinJoin
WITH equal_outs AS (                    -- maior grupo de saídas de mesmo valor
  SELECT transaction_hash, MAX(n) AS max_equal
  FROM (SELECT transaction_hash, value, COUNT(*) AS n
        FROM outputs WHERE block_number BETWEEN 870000 AND 870099
        GROUP BY transaction_hash, value) AS g
  GROUP BY transaction_hash
),
ins AS (
  SELECT i.transaction_hash, i.address,
         MIN(i.address) OVER (PARTITION BY i.transaction_hash) AS anchor
  FROM inputs AS i JOIN equal_outs AS e ON e.transaction_hash = i.transaction_hash
  WHERE i.block_number BETWEEN 870000 AND 870099
    AND e.max_equal < 3                 -- ignore transações semelhantes a CoinJoin
)
SELECT DISTINCT anchor AS addr_a, address AS addr_b, transaction_hash
FROM ins
WHERE address <> anchor;                -- uma aresta por endereço de entrada adicional

Três ou mais saídas iguais são um sinal rudimentar de CoinJoin, por isso essas transações são excluídas. Vincular cada endereço de entrada a uma âncora (o menor endereço na transação) gera n − 1 arestas para n endereços em vez de todos os pares, e manter o hash da transação em cada aresta preserva a evidência. Transformar arestas em agrupamentos significa encontrar componentes conexos, o que é iterativo: os analistas geralmente exportam as arestas para uma biblioteca de grafos com um algoritmo union-find. Em SQL, a propagação de rótulos (substituir repetidamente o ID de cada endereço pelo menor entre seus vizinhos até que nada mude) pode ser escrita com WITH RECURSIVE, mas é lenta em dados de toda a blockchain. Registre as regras de exclusão e sua versão, e fique atento a superagrupamentos (Aula 4.4).

Logs da EVM: assinaturas, tópicos e dados

Um log tem o endereço do contrato emissor, até quatro tópicos e um campo data. Para um evento Solidity normal, topic0 é o hash Keccak-256 da assinatura canônica (nome e tipos dos parâmetros, sem espaços nem nomes de parâmetros). Os parâmetros declarados como indexed vão para topic1 a topic3; os demais são codificados conforme a ABI em data como palavras de 32 bytes.

Filtrar por topic0 seleciona um tipo de evento em todos os contratos. Adicione contract_address sempre que estiver se referindo a um token ou protocolo específico: qualquer pessoa pode implantar um contrato que emita um Transfer bem-formado, e tokens fraudulentos fazem exatamente isso para inserir transferências falsas nos históricos das vítimas. Um evento é tão confiável quanto o contrato que o emitiu. ERC-20 e ERC-721 compartilham a assinatura Transfer, mas ERC-721 indexa o ID do token, resultando em quatro tópicos; testar topic3 IS NULL mantém as transferências ERC-20. Quando os tópicos são armazenados como um array, o BigQuery conta a partir de zero (topics[OFFSET(0)] ou SAFE_OFFSET(0)), enquanto PostgreSQL, Trino e DuckDB contam a partir de um (topics[1]); errar isso desloca todos os tópicos silenciosamente.

Tópicos e palavras de dados têm 64 caracteres hexadecimais após 0x. Um endereço ocupa os últimos 40, preenchido com zeros à esquerda, portanto começa no caractere 27; a palavra de dados k (a partir de zero) começa no caractere 3 + 64k.

-- Decodifique eventos Transfer ERC-20 de um token a partir de logs brutos
SELECT block_number, log_index, transaction_hash,
       '0x' || SUBSTR(topic1, 27, 40)  AS from_address,
       '0x' || SUBSTR(topic2, 27, 40)  AS to_address,
       SUBSTR(data, 3, 64)             AS amount_hex    -- primeira palavra de 32 bytes
FROM logs
WHERE contract_address = '0x7a11…0001'
  AND topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL                   -- ERC-721 adiciona um 4º tópico (tokenId)
  AND block_number BETWEEN 21000000 AND 21000999;

Em uma linha de exemplo, data termina em 77359400, que equivale a 2.000.000.000; com as 6 casas decimais do token, isso corresponde a 2.000 tokens. A conversão de hexadecimal para número é onde os mecanismos diferem: o DuckDB converte para um HUGEINT de 128 bits; from_base(h, 16) do Trino e a conversão para INT64 do BigQuery são de 64 bits; o PostgreSQL precisa de uma função definida pelo usuário. O limite de 64 bits de cerca de 9,22 × 10^18 unidades brutas corresponde a apenas 9,22 tokens com 18 casas decimais, portanto conversões que funcionam nos dados de teste sofrem estouro em transferências reais. Decodifique os valores na etapa de ETL ou use uma tabela decodificada, armazene-os como decimais e deixe os tipos dinâmicos (strings, arrays) para uma biblioteca ABI.

Alguns valores não precisam de conversão. Uma aprovação "ilimitada" define o limite autorizado como 2^256 − 1, que corresponde a 64 caracteres f:

-- Aprovações ilimitadas de tokens: limite autorizado = 2^256 - 1 (64 caracteres hexadecimais 'f')
SELECT block_number, transaction_hash,
       contract_address                AS token,
       '0x' || SUBSTR(topic1, 27, 40)  AS owner,
       '0x' || SUBSTR(topic2, 27, 40)  AS spender
FROM logs
WHERE topic0 = '0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925'
  AND LOWER(data) = '0xffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
  AND block_number BETWEEN 21000000 AND 21000999;

Listar os proprietários que concederam aprovações ilimitadas a um mesmo agente autorizado a gastar é uma etapa padrão para dimensionar uma campanha de drenagem de carteiras ou o comprometimento de um protocolo (Aula 5.5).

Transferências internas a partir de rastros

ETH enviado por um contrato, como em um saque de pool ou um pagamento multisig, não aparece em transactions.value, mas em rastros, com uma linha por quadro de chamada. trace_address é a posição do quadro na árvore de chamadas (aqui em texto: '' para a chamada de nível superior, '0' para sua primeira subchamada, '0,1' para a segunda chamada dentro dela; alguns conjuntos de dados usam arrays). trace_type é call, create, suicide (o nome histórico de selfdestruct) ou reward; call_type é call, delegatecall ou staticcall; status e tx_status registram se o quadro e a transação inteira foram bem-sucedidos.

-- Transferências internas de ETH (enviadas por contratos) para um endereço
SELECT block_number, transaction_hash, trace_address,
       from_address, value / 1e18 AS value_eth
FROM traces
WHERE to_address = '0x5e1f…c0de'
  AND value > 0
  AND trace_type = 'call'
  AND call_type = 'call'          -- delegatecall e staticcall não movimentam ETH
  AND trace_address <> ''         -- '' é a própria chamada de nível superior
  AND status = 1                  -- este quadro de chamada foi bem-sucedido
  AND tx_status = 1               -- e a transação também
  AND block_number BETWEEN 21000000 AND 21050000;

Cada filtro remove um falso positivo conhecido. Alguns formatos de rastros mostram um valor em um delegatecall que nunca foi transferido. Excluir o quadro de nível superior evita a contagem dupla se você também consultar transactions; para medir todo o ETH recebido, use apenas rastros, incluindo o quadro de nível superior, e lembre-se de que quadros create com valor e quadros selfdestruct também movimentam ETH. O status importa mais para rastros do que para logs: uma transação revertida não emite logs, mas seus rastros ainda são registrados, e um quadro pode ser revertido dentro de uma transação bem-sucedida. Exigir ambos os status abrange os casos usuais, mas uma reversão capturada no meio da árvore pode passar despercebida, portanto verifique transações incomuns individualmente.

Análise de gas

Desde a EIP-1559 (agosto de 2021): taxa paga = gas usado × preço efetivo do gas; queima = gas usado × taxa base por gas; taxa de prioridade = gas usado × (preço efetivo do gas − taxa base). Transações que carregam blobs (EIP-4844) também pagam uma taxa de blob que é queimada, gas de blob usado × preço do gas de blob, registrada em campos separados do recibo.

-- Taxas, queima e taxas de prioridade diárias, em ETH
SELECT DATE_TRUNC('day', t.block_timestamp)                         AS day_utc,
       COUNT(*)                                                      AS txs,
       SUM(t.gas_used * t.effective_gas_price) / 1e18                AS fees_eth,
       SUM(t.gas_used * b.base_fee_per_gas) / 1e18                   AS burned_eth,
       SUM(t.gas_used * (t.effective_gas_price - b.base_fee_per_gas)) / 1e18
                                                                     AS tips_eth
FROM transactions AS t                -- transações com falha também pagam taxas
JOIN blocks AS b ON b.hash = t.block_hash    -- hash, não altura: ignora blocos substituídos
WHERE t.block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND t.block_timestamp <  TIMESTAMP '2024-11-08 00:00:00'
GROUP BY DATE_TRUNC('day', t.block_timestamp)
ORDER BY day_utc;

Ao contrário das consultas de valor, esta mantém deliberadamente as transações com falha, porque elas consomem gas e pagam por ele. O gas multiplicado por um preço em wei ultrapassa facilmente 64 bits (10 milhões de gas a 1.000 gwei são 10^19 wei), outro motivo para essas colunas serem decimais. Em uma pequena amostra, um dia mostra 0,0239 ETH em taxas, dos quais apenas 0,0030 ETH foram queimados: uma única transação pagando 1.000 gwei por gas (980 gwei acima da taxa base) domina o total, o tipo de valor atípico que merece uma análise mais detalhada. Os dados de gas mostram quem financiou o gas de uma conta (um vínculo forte, Aula 4.3), quais remetentes pagam taxas de prioridade excepcionalmente altas (frequentemente bots ou atacantes em operações sensíveis ao tempo) e quanto custou um ataque. Em rollups, os recibos contêm campos extras de taxas de dados L1 cujos nomes diferem conforme a blockchain.

Foco no exame: espere identificar o erro em uma consulta: junção pelas duas partes do outpoint; enriquecimento e valores NULL nas entradas; localização de UTXOs com uma antijunção, com a condição do ponto de corte em ON; agregação de entradas e saídas separadamente para evitar a multiplicação de linhas; coinbase e duplicatas da BIP 30; heurísticas como indicadores versionados e listas de arestas de entradas comuns com exclusões de CoinJoin; topic0 como o Keccak-256 da assinatura canônica; filtragem por endereço de contrato contra eventos falsificados; topic3 IS NULL para ERC-20; SUBSTR(topic, 27, 40) e palavras de dados em 3 + 64k; arrays de tópicos com índice inicial zero versus um; estouro de 64 bits; aprovações ilimitadas; filtros de rastros por tipo de chamada, endereço do rastro e ambos os status; fórmulas de taxa, queima e prioridade da EIP-1559, mantendo transações com falha nos totais de taxas.