index-pump-events

name	index-pump-events
description	Use this skill when designing or running a pump.fun event indexer — choosing between RPC websockets, paid RPC, Geyser, or webhook indexers; designing the schema; deduplication; backfill; cost/latency tradeoffs. Triggers on "index pump events", "indexer schema", "Geyser stream", "backfill pump trades", "Helius webhooks pump".

Index pump.fun events at scale

When to use

• Building a dashboard, analytics platform, or trading bot that needs more than one-shot event handling.
• Existing public-RPC logsSubscribe is dropping events under load.
• The user wants historical replay (e.g., "all trades for mint X in the last 30 days").
• The user wants a denormalised table they can SQL against.

Ingestion choice (decision table)

Volume	Recommendation	Why
1 coin, hobby	Public RPC logsSubscribe (existing LaunchMonitor)	Free; reliability is fine for one watcher
5–10 coins	Paid RPC	More connections, better buffering
All launches, real-time	Geyser (Triton Yellowstone, Helius)	Lowest latency; raw account writes
Analytics + history	Helius enhanced webhooks → Postgres	Easiest ops; supports replay
Self-hosted production	Geyser → Kafka → Postgres + S3 archive	Full sovereignty

See tutorials/54-indexing-v2-events.md for client code per approach.

Schema starter

CREATE TABLE pump_trades (
  signature TEXT ,
  ix_index  ,
  slot  ,
  block_time TIMESTAMPTZ ,
  mint TEXT ,
  quote_mint TEXT ,
  side TEXT  (side  (, )),
  user_pubkey TEXT ,
  quote_amount (, ) ,
  token_amount (, ) ,
  fee_quote (, )  ,
  is_v2  ,
   (signature, ix_index)
);

 INDEX idx_pump_trades_mint_time  pump_trades (mint, block_time );
 INDEX idx_pump_trades_user  pump_trades (user_pubkey, block_time );
 INDEX idx_pump_trades_quote  pump_trades (quote_mint, block_time );

 pump_launches (
  signature TEXT ,
  slot  ,
  block_time TIMESTAMPTZ ,
  mint TEXT  ,
  creator TEXT ,
  quote_mint TEXT ,
  name TEXT ,
  symbol TEXT ,
  uri TEXT ,
  is_v2  ,
  creator_fee_shares JSONB
);

 INDEX idx_pump_launches_creator  pump_launches (creator, block_time );
 INDEX idx_pump_launches_quote  pump_launches (quote_mint, block_time );