Pipes: Troubleshooting Diagnostic

Specialized agent for diagnosing and fixing runtime errors in blockchain indexers built with Subsquid Pipes SDK.

When to Use This Skill

Activate when:

User reports an error message
Indexer crashes or stops unexpectedly
Data is missing or incorrect in database
TypeScript compilation fails
Database connection issues
Portal API errors or timeouts
User mentions "error", "not working", "broken", "failed", or "bug"
User complains indexer is too slow or wants performance optimization
User asks how to speed up sync or reduce sync time
User mentions "slow", "performance", "optimize", or "faster"

Important Note

Before diagnosing errors, check if the user followed the mandatory workflow in pipes-new-indexer skill. Many errors are caused by skipping documentation and not using proper setup procedures.

Diagnostic Checklist

1. Identify Error Type

Compilation Errors:

TypeScript type mismatches
Missing imports or dependencies
ABI version conflicts (@subsquid/evm-abi 0.3.1 vs 1.x.x)

Runtime Errors:

Portal API connection failures
Database connection issues
Event decoding errors
Memory issues or OOM
Cursor corruption

Data Quality Issues:

Missing events
Incorrect event parameters
Duplicate records
Wrong block ranges

2. Check Running Processes

If indexer is currently running:

# Check if process is running
ps aux | grep "npm run dev\|tsx src/index.ts\|node"

# Check output if running in background
# Use Bash tool with bash_id

3. Read Error Context

Always read the relevant files:

src/index.ts - Main pipeline code
package.json - Dependency versions
.env - Connection strings
Error stack traces from Bash

Common Error Patterns

Error Pattern 1: ABI Version Mismatch

Symptoms:

Type 'LogParams' is not assignable to type 'EvmLogParams'
Property 'topics' is missing in type 'LogParams'

Diagnosis: Wrong @subsquid/evm-abi version Root Cause: Using 1.x.x instead of 0.3.1

Fix:

// package.json
{
  "dependencies": {
    "@subsquid/evm-abi": "^0.3.1"  // NOT ^1.0.0
  }
}

Steps:

Read package.json
Edit to correct version
Run npm install or bun install
Verify types resolve

Error Pattern 2: Portal API Connection Failed

Symptoms:

Error: connect ECONNREFUSED
Error: Portal request failed with status 429
Error: Portal timeout after 30s

Diagnosis: Network or rate limit issue

Fix Options:

Rate Limiting (429): Add delay between requests or reduce block range
Connection Refused: Check internet connection, verify Portal URL
Timeout: Increase timeout or reduce batch size

Code Changes:

// Reduce block range to avoid rate limits
range: {
  from: 21_000_000,
  to: 21_100_000  // Smaller range
}

// Or adjust from block to be more recent
range: { from: 21_000_000 }  // Last few million blocks only

Error Pattern 3: Database Connection Failed

Symptoms:

Error: connect ECONNREFUSED localhost:5432
Error: ClickHouse authentication failed
Error: Database 'pipes' does not exist

Diagnosis: Database not running or misconfigured

Fix Steps:

Check if database is running:

# PostgreSQL
docker ps | grep postgres

# ClickHouse
docker ps | grep clickhouse

Check connection string in .env:
```
cat .env
```

Start database if needed:

# ClickHouse
docker start clickhouse

# Or start with docker-compose
docker-compose up -d

Create database if missing:

# ClickHouse
docker exec -it clickhouse clickhouse-client --query "CREATE DATABASE IF NOT EXISTS pipes"

Note: Sync Table Error on First Run (Harmless)

On the very first start of a new indexer, you will see:

[ERROR][@clickhouse/client][Connection] Query: HTTP request error.
Caused by: ClickHouseError: Unknown table expression identifier 'pipes.sync'

This is expected and harmless. The SDK tries to read the sync table to check for resume state, fails because it doesn't exist yet, then creates it. The indexer will continue normally. Ignore this error on first run.

Error Pattern 4: Event Decoding Failed

Symptoms:

Error: Cannot decode event with signature '0x...'
TypeError: Cannot read property 'from' of undefined

Diagnosis: Wrong ABI or contract address

Fix Steps:

Read src/index.ts to check ABI import
Verify contract address is correct

Check if using correct event ABI:

// Wrong: Using wrong common ABI
events: {
  swap: commonAbis.erc20.events.Transfer  // Wrong event
}

// Correct: Use proper ABI
events: {
  swap: uniswapV3.events.Swap  // Correct
}

If custom contract, regenerate ABI:

npx @subsquid/evm-typegen@latest src/contracts \
  0xYourContractAddress \
  --chain-id 1

Error Pattern 4b: Proxy Contract ABI — Crash on Startup

Symptoms:

TypeError: Cannot read properties of undefined (reading 'topic')
    at evmDecoder (evm-decoder.ts:322:70)

Diagnosis: The contract is a proxy. The CLI/typegen fetched the proxy ABI (only Upgraded event), but index.ts references events like Supply, Borrow, etc. that don't exist in the generated file.

How to confirm: Check the generated contract file:

grep "export const events" src/contracts/*.ts
# If only "Upgraded" → proxy contract

Fix:

Find the implementation address on Etherscan → "Read as Proxy" tab

Generate types from the implementation:

npx @subsquid/evm-typegen@latest src/contracts \
  <IMPLEMENTATION_ADDRESS> --chain-id <CHAIN_ID>

Update the import in src/index.ts:

// Change from proxy file to implementation file
import { events } from './contracts/<IMPLEMENTATION_ADDRESS>.js'

Keep the proxy address in contracts: array — events emit from the proxy address

Prevention: After CLI generation, always check the generated contract file for proxy-only ABI before running. See pipes-new-indexer/references/ABI_GUIDE.md for full proxy guide.

Error Pattern 5: Missing Data

Symptoms:

Indexer runs successfully but database is empty
Only partial data is indexed
Specific events are missing

Diagnosis: Filtering issue or wrong start block

Fix Steps:

Check start block is before events occurred:

// Verify on Etherscan when contract was deployed
range: { from: 'deployment_block' }

Check if contract is a proxy:
- Proxy contracts emit events from implementation address
- Need to track implementation, not proxy

Verify event names match ABI exactly:

// Case-sensitive, must match exactly
events: {
  transfer: erc20Abi.Transfer  // Correct case
}

Check for overly restrictive filters:

// May be filtering out too many events
.filter((e) => /* check filter logic */)

Check for sync table conflict (shared database): All indexers write to {database}.sync with id = 'stream'. If another indexer used this database, your indexer resumes from the wrong block.
```
docker exec <container> clickhouse-client --password <pw> \
  --query "SELECT * FROM <database>.sync FORMAT Vertical"
```
Fix: Use a separate database per indexer, or drop the sync table before starting.

Error Pattern 5b: Factory Indexer Shows Zero Data

Symptoms: Factory-pattern indexer runs, syncs blocks, but database has 0 rows for 30-60+ seconds.

Diagnosis: The factory pattern only discovers child contracts from range.from forward. If no new child contracts were created in the synced blocks, there's no data yet.

This is expected, not a bug.

Fix:

Wait 60-90 seconds — if the factory is active, data will appear
To track ALL child contracts, set range.from to the factory's deployment block
Check the SQLite file size to confirm contracts are being discovered:
```
ls -la <project>/*.sqlite  # size > 0 = contracts found
```

Error Pattern 5c: Timestamps Show 1970 Dates

Symptoms: All dates in ClickHouse show as 1970-01-28 or similar

Diagnosis: Passing milliseconds instead of seconds to a DateTime column

Fix: Divide getTime() by 1000 in your .pipe() transform:

// WRONG
timestamp: d.timestamp.getTime(),

// CORRECT
timestamp: Math.floor(d.timestamp.getTime() / 1000),

Note: The auto-generated enrichEvents helper handles this correctly. This only happens with manual .pipe() transforms.

Recovery: Drop tables + sync, delete SQLite (if factory), restart.

Error Pattern 6: Memory Issues

Symptoms:

Error: JavaScript heap out of memory
Process killed (signal 9)

Diagnosis: Indexer processing too much data at once

Fix Options:

Reduce block range
Reduce number of contracts tracked
Process data in smaller batches

Increase Node.js memory limit:

NODE_OPTIONS="--max-old-space-size=4096" npm run dev

Error Pattern 7: ClickHouse Schema Issues

Symptoms:

Error: Table already exists
Error: Column type mismatch
Error: Cannot insert NULL into NOT NULL column

Fix Steps:

Drop and recreate table:

docker exec clickhouse clickhouse-client --password=default \
  --query "DROP TABLE IF EXISTS pipes.table_name"

Verify schema matches data types:
- Addresses: String
- Amounts: Float64 (after dividing by decimals)
- Block numbers: UInt64
- Timestamps: DateTime(3)

Ensure sync table is cleared for fresh starts:

docker exec clickhouse clickhouse-client --password=default \
  --query "DROP TABLE IF EXISTS pipes.sync"

Error Pattern 8: Process Crashed / Indexer Died Mid-Sync

Symptoms:

Terminal shows process exited
npm run dev was killed (OOM, Ctrl+C, machine restart)
Partial data in database

Diagnosis: Normal crash recovery scenario. The sync table tracks progress.

Fix Steps:

Simply restart — it will resume automatically:
```
cd <project-folder>
npm run dev
```
Verify the "Resuming from X" log line shows a block near where it crashed. This is the one scenario where "Resuming" is expected and correct.

If data looks corrupted, drop sync + data tables and start fresh:

docker exec <container> clickhouse-client --password <pw> \
  --query "DROP TABLE IF EXISTS pipes.sync; DROP TABLE IF EXISTS pipes.<your_table>"

Error Pattern 9: Node.js Version Compatibility Issues

Symptoms:

ZSTD_error_prefix_unknown
TypeError: terminated (ZstdDecompress)

Or random crashes during large syncs.

Diagnosis: Using Node.js v25+ which has known zstd decompression bugs

Fix:

# Check version
node --version

# If v25.x, switch to LTS:
# Option 1: nvm
nvm install 22 && nvm use 22

# Option 2: Homebrew (macOS)
brew install node@22
export PATH="/opt/homebrew/opt/node@22/bin:$PATH"

# Option 3: Download from https://nodejs.org/

# Restart indexer
npm run dev

If you can't switch versions: The zstd bug tends to crash on large syncs (millions of blocks). For quick tests with recent blocks (~100K), v25 often works fine.

Hyperliquid-specific note: Node.js v25 crashes are especially common during large Hyperliquid fills syncs (50M+ blocks). However, checkpoint/resume works reliably — the indexer will pick up from where it crashed. If switching Node versions is not feasible, let it crash and restart repeatedly until sync completes. Each restart resumes from the last checkpoint.

Prevention: Always use Node.js LTS (v20 or v22) for Pipes SDK projects.

Error Pattern 10: Hyperliquid Validation — SDK vs Portal Block Batching

Symptoms: validate.ts Portal cross-reference shows wildly different fill counts compared to ClickHouse, even though spot-checks pass and data looks correct.

Diagnosis: The Pipes SDK batches Hyperliquid blocks differently from raw Portal queries. The SDK may merge, split, or reorder blocks internally for efficiency. A Portal query for fromBlock: X, toBlock: Y may return a different number of fills than what the SDK indexed for the same nominal range, because the SDK's actual block boundaries don't align 1:1 with Portal's.

This is NOT a bug. The data is correct — the counting methodology differs.

Fix — use spot-checks as primary truth verification:

// DON'T rely on block-range count comparison for Hyperliquid:
// ClickHouse count: 15,234  vs  Portal count: 14,891  → misleading 2.3% diff

// DO use transaction-level spot-checks as primary verification:
// 1. Pick 3-5 specific fills from ClickHouse (by hash or tid)
// 2. Query Portal for the same block
// 3. Verify field-level match: coin, px, sz, side, dir, user

validate.ts pattern for Hyperliquid:

// Phase 2 (Portal cross-ref): Use as a SANITY CHECK only, not exact match
// Accept wider tolerance (20-30%) or skip count comparison entirely
// Phase 3 (Spot-checks): This is the PRIMARY truth verification
// Pick fills from ClickHouse, query Portal for same block, verify fields match exactly

Prevention: When writing validate.ts for Hyperliquid indexers, make spot-checks the authoritative verification and treat count comparisons as approximate sanity checks only.

Error Pattern 10b: Hyperliquid addFill Missing Range

Symptoms:

TypeError: Cannot read properties of undefined (reading 'from')
    at parsePortalRange
    at HyperliquidFillsQueryBuilder.addRequest
    at HyperliquidFillsQueryBuilder.addFill

Diagnosis: The addFill() method requires a range parameter. Unlike EVM decoders where range is set once, each Hyperliquid fill filter needs its own range.

Note: In SDK 1.0+, use hyperliquidFillsQuery() instead of new HyperliquidFillsQueryBuilder(). The error behavior is the same.

Fix:

// WRONG
.addFill({ request: { coin: ['BTC'] } })

// CORRECT
.addFill({ range: { from: 920000000 }, request: { coin: ['BTC'] } })

Prevention: Always pass range in addFill(). The dataset starts at block 750,000,000.

Error Pattern 11: CLI Crashes on `init` — ora ESM/CJS Error

Symptoms:

[PIPES SDK] Error: (0 , import_ora.default) is not a function

Diagnosis: The CLI is bundled as CJS but imports ora v6+ which is ESM-only. Only init is affected; --schema and --version work fine.

Fix: Patch the CLI bundle to replace ora with a no-op spinner:

CLI_PATH=$(find ~/.npm/_npx -name "index.cjs" -path "*pipes-cli*" 2>/dev/null | head -1)
sed -i.bak 's/var import_ora = __toESM(require("ora"), 1);/var import_ora = { default: function(opts) { var t = typeof opts === "string" ? opts : (opts \&\& opts.text) || ""; return { start: function(m) { console.log(m || t); return this; }, succeed: function(m) { console.log(m || t); return this; }, fail: function(m) { console.log(m || t); return this; }, stop: function() { return this; }, text: t }; } };/' "$CLI_PATH"

Then re-run the init command.

WARNING: npx may silently re-download and overwrite the patch. Always verify before running init:

CLI_PATH=$(find ~/.npm/_npx -name "index.cjs" -path "*pipes-cli*" 2>/dev/null | head -1)
grep -q 'import_ora = { default: function' "$CLI_PATH" && echo "Patched" || echo "Needs patching"

Data Validation & Quality Checks

After an indexer completes successfully, validate the data quality to ensure production readiness.

Validation Levels

Level 1: Schema Validation (CRITICAL)

Verify table structure is correct:

-- Check table exists
SELECT count() FROM system.tables
WHERE database = '<database>' AND name = '<table_name>'

-- Check column types
DESCRIBE <database>.<table_name>

Checks:

Table exists
All expected columns present
Column data types match design
Indexes created
Table engine correct

Level 2: Data Quality (HIGH PRIORITY)

Validate individual data values:

-- Address format validation
SELECT
  countIf(length(pool_address) != 42) as invalid_length,
  countIf(pool_address NOT LIKE '0x%') as missing_prefix,
  countIf(NOT match(pool_address, '^0x[0-9a-fA-F]{40}$')) as invalid_format
FROM <table_name>

-- Transaction hash format
SELECT
  countIf(length(transaction_hash) != 66) as invalid_length,
  countIf(transaction_hash NOT LIKE '0x%') as missing_prefix
FROM <table_name>

-- BigInt values validation
SELECT
  countIf(amount = '') as empty_amounts,
  countIf(NOT match(amount, '^-?[0-9]+$')) as invalid_numbers
FROM <table_name>

-- NULL checks
SELECT
  countIf(from_address IS NULL) as null_from,
  countIf(to_address IS NULL) as null_to,
  countIf(value IS NULL) as null_value
FROM <table_name>

Checks:

Addresses are 42 characters (0x + 40 hex)
Transaction hashes are 66 characters (0x + 64 hex)
BigInt values are valid numbers
No unexpected NULL values
Block numbers in expected range

Level 3: Completeness (MEDIUM PRIORITY)

Ensure no missing data:

-- Block range coverage
SELECT
  MIN(block_number) as min_block,
  MAX(block_number) as max_block,
  COUNT(DISTINCT block_number) as unique_blocks
FROM <table_name>

-- Check for block gaps
SELECT
  block_number,
  block_number - lag(block_number) OVER (ORDER BY block_number) as gap
FROM (
  SELECT DISTINCT block_number
  FROM <table_name>
  ORDER BY block_number
)
WHERE gap > 1

-- Event count per block
SELECT
  block_number,
  COUNT(*) as event_count
FROM <table_name>
GROUP BY block_number
HAVING event_count > 1000
ORDER BY event_count DESC
LIMIT 10

Checks:

Block range matches expected
No gaps in block sequence
Event counts are reasonable
No duplicate events (same tx_hash + log_index)

Level 4: Consistency (MEDIUM PRIORITY)

Verify logical relationships:

-- Block timestamps are monotonic
SELECT
  block_number,
  block_timestamp,
  lag(block_timestamp) OVER (ORDER BY block_number) as prev_timestamp
FROM (
  SELECT DISTINCT block_number, block_timestamp
  FROM <table_name>
  ORDER BY block_number
)
WHERE block_timestamp < prev_timestamp

Checks:

Block timestamps increase with block numbers
Log indexes sequential within transactions

Common Data Quality Issues

Issue 1: NULL Values in Required Fields

Symptom: Critical fields contain NULL

Cause: Missing .toString() on BigInt values

Fix:

// Wrong
amount: transfer.event.value,

// Correct
amount: transfer.event.value.toString(),

Issue 2: Invalid Address Formats

Symptom: Addresses not 42 characters or missing 0x

Cause: Incorrect data extraction or transformation

Fix: Validate address format in transformation pipeline

Issue 3: Block Gaps

Symptom: Missing blocks in sequence

Cause: Indexer crashed and didn't resume properly

Fix: Clear sync table and restart from affected block

Validation Checklist

Before declaring success:

Table structure matches design
No NULL values in required fields
All addresses are valid (42 chars, 0x prefix, hex)
All transaction hashes valid (66 chars)
Block range complete (no gaps)
Data count increasing over time
Sample transactions match block explorer

Diagnostic Workflow

Read error message - Get exact error text
Identify pattern - Match to known patterns above
Read relevant files - Check src/index.ts, package.json, .env
Verify environment - Check database, network, dependencies
Apply fix - Edit files or run commands
Test fix - Restart indexer and verify
Validate data - Run quality checks above
Monitor - Watch logs to confirm resolution

Prevention Tips

Always use Pipes CLI - Never manually create files
Follow workflow - See pipes-new-indexer for the standard workflow
Start with recent blocks - Test faster, iterate quicker
Verify setup - See ENVIRONMENT_SETUP.md before starting
Check patterns - See PATTERNS.md for common solutions

Related Skills

pipes-new-indexer - Create new indexers
PERFORMANCE.md - Optimize slow indexers
pipes-new-indexer ABI_GUIDE - Fetch contract ABIs and handle proxies
pipes-new-indexer SCHEMA_GUIDE - Design ClickHouse schemas

pipes-troubleshooting

Pipes: Troubleshooting Diagnostic

When to Use This Skill

Important Note

Diagnostic Checklist

1. Identify Error Type

2. Check Running Processes

3. Read Error Context

Common Error Patterns

Error Pattern 1: ABI Version Mismatch

Error Pattern 2: Portal API Connection Failed

Error Pattern 3: Database Connection Failed

Note: Sync Table Error on First Run (Harmless)

Error Pattern 4: Event Decoding Failed

Error Pattern 4b: Proxy Contract ABI — Crash on Startup

Error Pattern 5: Missing Data

Error Pattern 5b: Factory Indexer Shows Zero Data

Error Pattern 5c: Timestamps Show 1970 Dates

Error Pattern 6: Memory Issues

Error Pattern 7: ClickHouse Schema Issues

Error Pattern 8: Process Crashed / Indexer Died Mid-Sync

Error Pattern 9: Node.js Version Compatibility Issues

Error Pattern 10: Hyperliquid Validation — SDK vs Portal Block Batching

Error Pattern 10b: Hyperliquid addFill Missing Range

Error Pattern 11: CLI Crashes on init — ora ESM/CJS Error

Data Validation & Quality Checks

Validation Levels

Level 1: Schema Validation (CRITICAL)

Level 2: Data Quality (HIGH PRIORITY)

Level 3: Completeness (MEDIUM PRIORITY)

Level 4: Consistency (MEDIUM PRIORITY)

Common Data Quality Issues

Issue 1: NULL Values in Required Fields

Issue 2: Invalid Address Formats

Issue 3: Block Gaps

Validation Checklist

Diagnostic Workflow

Prevention Tips

Related Skills

Related Documentation

How to Access

Additional Resources

Official Subsquid Documentation

More from subsquid-labs/agent-skills

pipes-new-indexer

portal-query-evm-transactions

pipes-performance

portal-query-evm-traces

portal-query-solana-instructions

pipes-abi

Error Pattern 11: CLI Crashes on `init` — ora ESM/CJS Error