Filter Stablecoins Transfers

Stream filtered ERC-20 stablecoin token transfers with SQL transforms for compliance monitoring or whale watching.

Use Case

You want to stream only USDC transfers on Base Mainnet into Postgres, with amounts converted from raw units to human-readable values. This is useful for compliance monitoring, whale watching, or building token-specific analytics.

Pipeline Configuration

1. Create a new pipeline

   In the GoldRush Platform, navigate to Manage Pipelines and click Create Pipeline. Name it usdc-whale-watch.

2. Configure the Postgres destination

   Select Postgres as the destination type:

   destination:
     type: "postgres"
     url: "postgresql://your-host:5432/compliance"
     user: "${PG_USER}"
     password: "${PG_PASSWORD}"
     batch_size: 500
   
3. Select your source

   Choose Base Mainnet as the chain and Transfers as the data type. This streams every token and native transfer on Base.

4. Add a SQL transform

   Filter to USDC transfers only and convert the raw amount to a human-readable decimal value. USDC on Base has 6 decimals:

   transforms:
     transfers: >
       SELECT block_height, block_signed_at, tx_hash,
              from_address, to_address,
              CAST(amount AS DOUBLE) / 1000000 AS amount_usdc
       FROM transfers
       WHERE contract_address = '0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913'
   
5. Deploy

   Review and deploy. Only USDC transfers flow to your database - all other tokens are filtered out before reaching the destination.

How the Transform Works

The SQL transform runs inside the pipeline, before data reaches your database:

1. WHERE clause filters to only transfers where the contract_address matches USDC on Base
2. CAST and arithmetic converts the raw amount (stored as a string of the integer value in smallest units) to a DOUBLE divided by 10^6
3. SELECT projects only the columns you need, reducing storage

This means your Postgres database only stores the data you care about - no wasted writes for irrelevant tokens.

Verify Data

SELECT block_height, tx_hash, from_address, to_address, amount_usdc
FROM usdc_whale_watch.transfers
ORDER BY amount_usdc DESC
LIMIT 20;

Extending to Multiple Tokens

To track multiple tokens, expand the WHERE clause with an IN filter:

transforms:
  transfers: >
    SELECT block_height, block_signed_at, tx_hash, contract_address,
           from_address, to_address, amount
    FROM transfers
    WHERE contract_address IN (
      '0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913',
      '0x4200000000000000000000000000000000000006',
      '0x50c5725949A6F0c72E6C4a641F24049A917DB0Cb'
    )

When tracking multiple tokens with different decimal places, keep the raw amount as a string and handle decimal conversion in your application layer instead.

Production Tips

• Batch size: A smaller batch size (500) is appropriate here because the SQL transform filters out most records, resulting in lower write throughput to the destination.
• Indexing: Add indexes on from_address, to_address, and amount_usdc for common query patterns.
• Alerting: Combine this pipeline with a simple cron job or application query to trigger alerts when amount_usdc exceeds a threshold.