Interroger les données UTXO ainsi que les journaux et les traces EVM

La plupart des informations dont un enquêteur a besoin sur une blockchain ne sont pas stockées sous forme de réponses prêtes à l'emploi. Sur Bitcoin et les autres chaînes UTXO, il n'existe pas de soldes directement consultables : chaque solde, chaque montant de frais et chaque flux doivent être reconstitués en joignant les sorties aux entrées qui les dépensent ultérieurement. Sur les chaînes EVM, les mouvements de jetons, les autorisations et les versements des contrats se trouvent dans les journaux d'événements et les appels internes plutôt que dans la transaction elle-même. Les tables décodées couvrent les contrats populaires, mais tôt ou tard, une affaire implique un nouveau jeton, un contrat non vérifié ou un schéma que personne n'a décodé. Cette leçon montre comment écrire correctement ces jointures et ces décodages en SQL, afin que chaque chiffre d'un rapport repose sur une requête qu'un autre examinateur peut réexécuter pour obtenir le même résultat. Elle utilise le schéma générique de la leçon 2.7 et s'appuie sur les modèles de jointures, de fonctions de fenêtrage et de CTE de la leçon 2.8.

Tables UTXO et jointure sur l'outpoint

Les jeux de données UTXO décomposent chaque transaction en lignes selon deux niveaux de granularité (valeurs en satoshis) :

Une sortie est identifiée par son outpoint : le hachage de la transaction associé à l'indice de la sortie. Une entrée désigne l'outpoint qu'elle dépense au moyen de spent_transaction_hash et de spent_output_index. Dans ce schéma, les transactions coinbase n'ont aucune ligne dans inputs ; certains jeux de données conservent une entrée coinbase avec un outpoint vide, vérifiez donc ce point avant tout comptage.

Une entrée brute ne contient que l'outpoint, un script et un numéro de séquence, sans adresse ni montant (leçon 2.2). Les colonnes address, value et script_type de inputs existent parce que le pipeline a recherché chaque sortie dépensée et en a recopié les champs, une étape que les outils blockchain-etl appellent enrichissement. C'est à la fois une commodité et une dépendance : si l'enrichissement a échoué pour certains blocs, ces entrées ont des valeurs NULL et tous les frais et soldes calculés à partir de celles-ci sont erronés. Comptez les entrées dont la colonne value est NULL dans votre plage avant de vous y fier.

Deux autres pièges liés à la structure :

• Tableaux d'adresses. Certains jeux de données, dont le jeu de données Bitcoin de BigQuery, stockent addresses sous forme de tableau, car les scripts multisignatures nus contiennent plusieurs clés et les scripts non standard n'en contiennent aucune. Pour obtenir une ligne par adresse, dépliez le tableau : CROSS JOIN UNNEST(addresses) AS address dans BigQuery, CROSS JOIN UNNEST(addresses) AS t(address) dans Trino et DuckDB, unnest() dans PostgreSQL, LATERAL FLATTEN dans Snowflake, explode() dans Spark SQL. Les sorties OP_RETURN conservent un tableau NULL ou vide.
• Txid en double. Deux transactions coinbase de 2010 partagent le txid de transactions coinbase antérieures, un problème que BIP 30 a interdit par la suite. Lorsque l'exactitude est essentielle, utilisez le hachage et le numéro de bloc comme clé.

L'arête du graphe de dépense UTXO correspond à la jointure sur les deux parties de l'outpoint :

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

Une jointure sur le seul hachage associe chaque entrée à toutes les sorties de la transaction antérieure.

Sorties non dépensées et soldes à un instant donné

La recherche d'UTXO est une anti-jointure : effectuez une jointure externe gauche des sorties vers les entrées et conservez les lignes pour lesquelles aucune entrée ne correspond.

-- Sorties non dépensées d'une adresse au bloc 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     -- dépenses jusqu'à l'instantané uniquement
WHERE o.address = 'bc1q…7k3f'
  AND o.block_number <= 870000
  AND i.transaction_hash IS NULL;           -- aucune entrée dépensant la sortie trouvée

La somme de value donne le solde de l'adresse à cette hauteur. Dans l'exemple, l'adresse a reçu 0,05 et 0,03 BTC ; la première sortie a été dépensée au bloc 870 000 et la seconde au bloc 870 005, de sorte que le solde au bloc 870 000 est de 0,03 BTC.

La condition d'instantané sur i.block_number doit se trouver dans la clause ON. Si elle est déplacée dans WHERE, elle est évaluée après la jointure, lorsque i.block_number est NULL pour chaque sortie sans correspondance ; la requête ne renvoie donc aucune ligne au lieu de 0,03 BTC. NOT EXISTS (SELECT 1 FROM inputs …) exprime la même anti-jointure sans ce piège. Pour un historique du solde, empilez les sorties vers l'adresse sous forme de montants positifs et les entrées enrichies provenant de cette adresse sous forme de montants négatifs avec UNION ALL, puis calculez une somme par fenêtrage dans l'ordre des blocs (leçon 2.8).

Frais sans multiplication des lignes par la jointure

Les frais correspondent à la somme des entrées moins la somme des sorties. Joindre les entrées aux sorties sur le hachage de transaction et sommer les deux côtés est incorrect : une transaction comportant 3 entrées et 4 sorties produit 12 lignes jointes et multiplie les deux sommes. Agrégez d'abord chaque côté, puis joignez les totaux.

-- Frais par transaction : agréger chaque côté, puis joindre
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          -- la coinbase n'a pas d'entrées : exclue par la jointure
FROM vout
JOIN vin ON vin.transaction_hash = vout.transaction_hash
ORDER BY fee_sat DESC;

Dans le bloc de l'exemple, une transaction dépense 5 000 000 et 2 000 000 sat et crée des sorties de 6 000 000 et 991 000, de sorte que ses frais s'élèvent à 9 000 sat. La coinbase disparaît dans la jointure interne, ce qui est correct : elle ne paie pas de frais. Divisez par virtual_size pour obtenir un taux de frais. De nombreux jeux de données précalculent fee ; le recalculer sur un échantillon constitue un bon test de leur enrichissement.

Heuristiques sous forme d'indicateurs stockés

Les heuristiques du module 4 deviennent des indicateurs : des colonnes qui signalent des candidats, et non des conclusions. Un signal plus fort de monnaie rendue (leçon 4.2) est la correspondance de type de script : dans une transaction à deux sorties dont les entrées partagent un même type de script, l'unique sortie de ce type est une candidate à la monnaie rendue.

-- Candidates à la monnaie rendue : l'unique sortie dont le type de script correspond aux entrées
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      -- toutes les entrées partagent un même type
),
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;

La requête externe ajoute également un signal plus faible : un montant qui n'est pas un multiple rond de 100 000 sat. Dans l'exemple, des entrées SegWit natives versent 6 000 000 sat à une adresse P2SH et 991 000 à une nouvelle adresse SegWit native ; la sortie de 991 000 est signalée selon les deux critères. Stockez les indicateurs avec le nom et la version de l'heuristique et combinez-les en un niveau de confiance lors de l'analyse, jamais dans une jointure codée en dur. La correspondance de type de script échoue lorsque le destinataire utilise le même type de portefeuille, ce qui devient de plus en plus courant avec la diffusion de Taproot, et lorsque les portefeuilles font délibérément varier les types de monnaie rendue.

L'heuristique de propriété commune des entrées (leçon 4.1) devient une liste d'arêtes reliant les adresses ayant dépensé ensemble :

-- Arêtes d'entrées communes, en excluant les transactions de type CoinJoin
WITH equal_outs AS (                    -- plus grand groupe de sorties de même valeur
  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                 -- exclure les transactions de type CoinJoin
)
SELECT DISTINCT anchor AS addr_a, address AS addr_b, transaction_hash
FROM ins
WHERE address <> anchor;                -- une arête par adresse d'entrée supplémentaire

Trois sorties égales ou plus constituent un signal sommaire de CoinJoin ; ces transactions sont donc exclues. Relier chaque adresse d'entrée à une ancre unique (la plus petite adresse de la transaction) produit n − 1 arêtes pour n adresses plutôt que toutes les paires, et conserver le hachage de transaction sur chaque arête préserve l'élément de preuve. Transformer les arêtes en groupes implique de trouver les composantes connexes, ce qui est itératif : les analystes exportent généralement les arêtes vers une bibliothèque de graphes dotée d'un algorithme union-find. En SQL, la propagation d'étiquettes (remplacer de façon répétée l'identifiant de chaque adresse par le plus petit parmi ceux de ses voisins jusqu'à ce que plus rien ne change) peut s'écrire avec WITH RECURSIVE, mais elle est lente sur les données de l'ensemble de la chaîne. Consignez les règles d'exclusion et leur version, et surveillez l'apparition de supergroupes (leçon 4.4).

Journaux EVM : signatures, topics et données

Une entrée de journal comporte l'adresse du contrat émetteur, jusqu'à quatre topics et un champ data. Pour un événement Solidity normal, topic0 est le hachage Keccak-256 de la signature canonique (nom et types des paramètres, sans espaces ni noms de paramètres). Les paramètres déclarés indexed sont placés dans topic1 à topic3 ; les autres sont encodés selon l'ABI dans data sous forme de mots de 32 octets.

Filtrer sur topic0 sélectionne un type d'événement dans l'ensemble des contrats. Ajoutez contract_address dès lors que vous ciblez un jeton ou un protocole précis : n'importe qui peut déployer un contrat qui émet un Transfer correctement formé, et les jetons frauduleux le font précisément pour insérer de faux transferts dans l'historique des victimes. Un événement n'est digne de confiance que dans la mesure où le contrat qui l'a émis l'est lui-même. ERC-20 et ERC-721 partagent la signature Transfer, mais ERC-721 indexe l'identifiant du jeton, ce qui donne quatre topics ; le test topic3 IS NULL conserve les transferts ERC-20. Lorsque les topics sont stockés dans un tableau, BigQuery commence à zéro (topics[OFFSET(0)] ou SAFE_OFFSET(0)), tandis que PostgreSQL, Trino et DuckDB commencent à un (topics[1]) ; une erreur sur ce point décale silencieusement tous les topics.

Les topics et les mots de données comptent 64 caractères hexadécimaux après 0x. Une adresse occupe les 40 derniers, complétés à gauche par des zéros ; elle commence donc au caractère 27 ; le mot de données k (en partant de zéro) commence au caractère 3 + 64k.

-- Décoder les événements ERC-20 Transfer d'un jeton à partir des journaux bruts
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    -- premier mot de 32 octets
FROM logs
WHERE contract_address = '0x7a11…0001'
  AND topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL                   -- ERC-721 ajoute un 4e topic (tokenId)
  AND block_number BETWEEN 21000000 AND 21000999;

Dans une ligne d'exemple, data se termine par 77359400, soit 2 000 000 000 ; avec les 6 décimales du jeton, cela représente 2 000 jetons. La conversion de l'hexadécimal en nombre est le point où les moteurs diffèrent : DuckDB effectue une conversion vers un HUGEINT de 128 bits ; from_base(h, 16) de Trino et la conversion INT64 de BigQuery sont sur 64 bits ; PostgreSQL nécessite une fonction définie par l'utilisateur. La limite de 64 bits, d'environ 9,22 × 10^18 unités brutes, ne représente que 9,22 jetons à 18 décimales ; des conversions qui réussissent sur des données de test provoquent donc un dépassement de capacité sur des transferts réels. Décodez les montants à l'étape ETL ou utilisez une table décodée, stockez-les sous forme de nombres décimaux et confiez les types dynamiques (chaînes, tableaux) à une bibliothèque ABI.

Certaines valeurs ne nécessitent aucune conversion. Une autorisation « illimitée » fixe le montant autorisé à 2^256 − 1, soit 64 caractères f :

-- Autorisations illimitées de jetons : montant autorisé = 2^256 - 1 (64 'f' hexadécimaux)
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;

Établir la liste des propriétaires qui ont accordé des autorisations illimitées à une même adresse dépensière est une étape standard pour mesurer l'ampleur d'une campagne de vidage de portefeuilles ou de la compromission d'un protocole (leçon 5.5).

Transferts internes à partir des traces

L'ETH envoyé par un contrat, par exemple lors d'un retrait d'un pool ou d'un versement multisignature, n'apparaît pas dans transactions.value, mais dans les traces, à raison d'une ligne par cadre d'appel. trace_address est la position du cadre dans l'arbre des appels (ici sous forme de texte : '' pour l'appel de premier niveau, '0' pour son premier sous-appel, '0,1' pour le deuxième appel à l'intérieur de celui-ci ; certains jeux de données utilisent des tableaux). trace_type vaut call, create, suicide (le nom historique de selfdestruct) ou reward ; call_type vaut call, delegatecall ou staticcall ; status et tx_status indiquent si le cadre et la transaction entière ont réussi.

-- Transferts internes d'ETH (envoyés par des contrats) vers une adresse
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 et staticcall ne déplacent pas d'ETH
  AND trace_address <> ''         -- '' est l'appel de premier niveau lui-même
  AND status = 1                  -- ce cadre d'appel a réussi
  AND tx_status = 1               -- et la transaction également
  AND block_number BETWEEN 21000000 AND 21050000;

Chaque filtre élimine un faux positif connu. Certains formats de traces affichent sur un delegatecall une valeur qui n'a jamais été transférée. Exclure le cadre de premier niveau évite le double comptage si vous interrogez également transactions ; pour mesurer tout l'ETH reçu, utilisez uniquement les traces, en incluant le cadre de premier niveau, et n'oubliez pas que les cadres create avec une valeur et les cadres selfdestruct déplacent également de l'ETH. Le statut importe davantage pour les traces que pour les journaux : une transaction annulée n'émet aucun journal, mais ses traces sont tout de même enregistrées, et un cadre peut être annulé à l'intérieur d'une transaction réussie. Exiger les deux statuts couvre les cas habituels, mais une annulation interceptée au milieu de l'arbre peut passer à travers ; vérifiez donc individuellement les transactions inhabituelles.

Analyse du gaz

Depuis EIP-1559 (août 2021) : frais payés = gaz utilisé × prix effectif du gaz ; montant détruit = gaz utilisé × frais de base par unité de gaz ; frais de priorité = gaz utilisé × (prix effectif du gaz − frais de base). Les transactions transportant des blobs (EIP-4844) paient également des frais de blob détruits, égaux au gaz de blob utilisé × prix du gaz de blob, enregistrés dans des champs distincts du reçu.

-- Frais quotidiens, montant détruit et frais de priorité, en 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                -- les transactions échouées paient aussi des frais
JOIN blocks AS b ON b.hash = t.block_hash    -- hachage, pas hauteur : exclut les blocs remplacés
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;

Contrairement aux requêtes sur les valeurs transférées, celle-ci conserve délibérément les transactions échouées, car elles consomment du gaz et le paient. Le gaz multiplié par un prix en wei dépasse facilement 64 bits (10 millions d'unités de gaz à 1 000 gwei représentent 10^19 wei), une raison supplémentaire pour laquelle ces colonnes sont de type décimal. Sur un petit échantillon, une journée affiche 0,0239 ETH de frais, dont seulement 0,0030 ETH ont été détruits : une seule transaction payant 1 000 gwei par unité de gaz (980 gwei au-dessus des frais de base) domine, le type de valeur aberrante qui mérite un examen plus approfondi. Les données de gaz montrent qui a financé le gaz d'un compte (un lien fort, leçon 4.3), quels expéditeurs paient des frais de priorité anormalement élevés (souvent des robots ou des attaquants pour lesquels le temps est critique) et combien une attaque a coûté. Sur les rollups, les reçus comportent des champs supplémentaires de frais de données L1 dont les noms diffèrent selon la chaîne.

Points clés pour l'examen : attendez-vous à devoir repérer l'erreur dans une requête : jointure sur les deux parties de l'outpoint ; enrichissement et valeurs NULL des entrées ; recherche d'UTXO par anti-jointure, avec la condition d'instantané dans ON ; agrégation séparée des entrées et des sorties pour éviter la multiplication des lignes ; coinbase et doublons BIP 30 ; heuristiques sous forme d'indicateurs versionnés et listes d'arêtes d'entrées communes avec exclusions CoinJoin ; topic0 comme hachage Keccak-256 de la signature canonique ; filtrage par adresse de contrat pour se prémunir contre les événements falsifiés ; topic3 IS NULL pour ERC-20 ; SUBSTR(topic, 27, 40) et mots de données à 3 + 64k ; tableaux de topics indexés à partir de zéro ou de un ; dépassement de capacité sur 64 bits ; autorisations illimitées ; filtres de traces sur le type d'appel, l'adresse de trace et les deux statuts ; formules EIP-1559 des frais, du montant détruit et des frais de priorité, en conservant les transactions échouées dans les totaux de frais.