The Problem with "New Pair" Lists

Every block, dozens of new Uniswap V2 pairs get created. Most are noise — test tokens, abandoned forks, honeypots nobody ever funds. A raw feed of PairCreated events is useless on its own; you need to know which pairs actually received real liquidity, and from there, which ones had that liquidity yanked back out.

Building the Watcher

I parsed PairCreated and Transfer events together, layering CTEs so the query first identifies new pairs, then checks how much WETH actually flowed into each one within the first 24 hours. Pairs below a minimum liquidity threshold get filtered out before they ever reach the table — no point flagging a pool nobody funded.

WITH recent_pairs AS (
    SELECT evt_block_time, token0, token1, pair
    FROM uniswap_v2_ethereum.factory_evt_paircreated
    ORDER BY 1 DESC
    LIMIT 50
),

liquidity_adds AS (
    SELECT "to",
           SUM(CAST(value AS DOUBLE) / 1e18) AS value_eth
    FROM erc20_ethereum.evt_Transfer
    WHERE evt_block_time >= now() - interval '1' day
      AND contract_address = {{Target_Token}}
    GROUP BY 1
)

SELECT rp.evt_block_time, la.value_eth, rp.pair
FROM recent_pairs rp
LEFT JOIN liquidity_adds la ON rp.pair = la."to"
WHERE la.value_eth > {{Min_Liquidity_Added}}
ORDER BY 1 DESC;

Defining "Rug" Mathematically

The harder part wasn't finding burns — it's deciding which burns matter.

A founder removing 5% of liquidity to rebalance isn't a rug. Someone pulling 95%+ of the pool's WETH reserve in one transaction is. I cross-referenced Burn events against the pool's Sync state right before the burn, so the percentage is calculated against the actual reserve at that moment — not against some stale snapshot.

(weth_amount * 100.0) / (weth_amount + weth_reserve) AS rug_percentage

Anything above 95% gets flagged — in practice, most confirmed drains land at 99.9%+, since partial liquidity removal rarely triggers a second look.

What It Actually Catches

Running this over a single day's window already surfaces multiple pairs crossing the 95% threshold within hours of being created — the classic shape of a liquidity pull executed by the same address that deployed the pool. The terminal doesn't tell you a token is a scam before it happens; it tells you, with a direct Etherscan link, the exact transaction where someone walked away with the liquidity, so you don't have to dig for it manually.

The Illusion of the Transaction Table

When investigating the exploit, the standard ethereum.transactions table proved useless. The looters weren't interacting with the bridge directly; they were routing transactions through private proxy contracts to mask their tracks. Looking at raw transaction inputs only showed noise. To find the ground truth, I had to drop down to the deepest layer: smart contract logs.

Parsing the Ground Truth

By querying ethereum.logs, I isolated the exact Transfer events emitted by the Nomad vault itself. Using the event signature (topic0 = 0xddf2...) and parsing the raw byte array data with BYTEARRAY_SUBSTRING, I bypassed the proxy layer entirely and extracted the true recipient addresses of the stolen funds.

Here is the core SQL architecture used to extract the exact transaction signatures:

WITH nomad_logs AS (
    SELECT 
        block_time,
        tx_hash,
        contract_address AS token_address,
        -- Extracting the true recipient address by skipping the first 12 bytes of zeros
        BYTEARRAY_SUBSTRING(topic2, 13) AS recipient_address, 
        -- Converting raw hex data to numeric values to prevent Overflow
        varbinary_to_uint256(data) AS raw_amount
    FROM ethereum.logs
    WHERE contract_address = {{Nomad_Vault_Address}}
      AND topic0 = 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef -- ERC20 Transfer Signature
)
-- Further logic applied for decimal conversion and deduplication

Financial Synthesis & Data Protection

Counting tokens is only half the battle; assigning fiat value is where queries fail. Joining high-frequency log data with prices.usd minute-by-minute led to a Cartesian explosion and query timeouts (Full Table Scan). Furthermore, a standard INNER JOIN wiped out millions of dollars from the total because some obscure stolen tokens lacked exact minute-to-minute price feeds.

The architectural fix: I built an isolated CTE with aggregated daily_prices (AVG(price)) and used a strict LEFT JOIN combined with COALESCE to ensure zero financial data was dropped during the valuation process. This stabilized the query and revealed the true scale of the exploit.

The Damage

What did they steal? Mostly hard liquidity.

By parsing raw ethereum.logs and matching with historical prices.usd, the asset distribution is clear. USDC took the biggest hit, followed by WBTC & WETH. The total calculated damage sits exactly at $174.7M.

Unique Hackers Number & Total Funds Stolen

Asset Distribution Graph & Leaderboard Table

Conclusion

The Nomad exploit proves your biggest DeFi threat isn't a hooded hacker. Sometimes, it's a single line of bad code leaving the vault door open for the entire internet.