Post 07
Indexing Your Contract's Events with viem and Postgres
Before you reach for a hosted indexer or a subgraph: a reorg-safe event indexer in a few hundred lines. Backfill, live tail, a cursor that survives restarts, and a schema the front end can query directly.
The front end should almost never read history from an RPC node — 'all trades', 'this user's positions', 'volume this week' are queries an eth_call cannot answer and getLogs will rate-limit. The usual answer is a subgraph or a hosted indexer. But for a single contract with a handful of events, a self-hosted indexer is a few hundred lines, and it means your data lives in the same Postgres your app already uses.
The schema
1-- one row per event, idempotent on (tx_hash, log_index)2create table trades (3 id bigserial primary key,4 block_number bigint not null,5 block_hash text not null,6 tx_hash text not null,7 log_index int not null,8 trader text not null,9 token_in text not null,10 token_out text not null,11 amount_in numeric not null,12 amount_out numeric not null,13 ts timestamptz not null,14 unique (tx_hash, log_index)15);16create index on trades (trader);17create index on trades (block_number);18 19-- the cursor: how far we've indexed, and the hashes of recent20-- blocks so we can detect a reorg21create table index_state (22 id int primary key default 1,23 last_block bigint not null,24 recent_blocks jsonb not null default '[]' -- [{number, hash}]25);Backfill: scan history in ranges
getLogs over a huge range times out. Chunk it, and respect the provider's max-range limit (often 2,000–10,000 blocks).
1import { parseAbiItem } from "viem";2import { publicClient } from "./client";3import { db } from "./db";4 5const TRADE = parseAbiItem(6 "event Trade(address indexed trader, address tokenIn, address tokenOut, uint256 amountIn, uint256 amountOut)",7);8const RANGE = 5_000n;9 10export async function backfill(from: bigint, to: bigint) {11 for (let start = from; start <= to; start += RANGE) {12 const end = start + RANGE - 1n > to ? to : start + RANGE - 1n;13 14 const logs = await publicClient.getLogs({15 address: CONTRACT,16 event: TRADE,17 fromBlock: start,18 toBlock: end,19 });20 21 if (logs.length) await insertTrades(logs);22 await db.query(23 "update index_state set last_block = $1 where id = 1", [end],24 );25 console.log(`indexed ${start}–${end} (+${logs.length})`);26 }27}1async function insertTrades(logs: Log[]) {2 const blocks = new Map<bigint, bigint>(); // blockNumber -> timestamp3 for (const l of logs) {4 if (!blocks.has(l.blockNumber!)) {5 const b = await publicClient.getBlock({ blockNumber: l.blockNumber! });6 blocks.set(l.blockNumber!, b.timestamp);7 }8 }9 10 await db.transaction(async (tx) => {11 for (const l of logs) {12 await tx.query(13 `insert into trades14 (block_number, block_hash, tx_hash, log_index, trader,15 token_in, token_out, amount_in, amount_out, ts)16 values ($1,$2,$3,$4,$5,$6,$7,$8,$9,to_timestamp($10))17 on conflict (tx_hash, log_index) do nothing`,18 [l.blockNumber, l.blockHash, l.transactionHash, l.logIndex,19 l.args.trader, l.args.tokenIn, l.args.tokenOut,20 l.args.amountIn.toString(), l.args.amountOut.toString(),21 Number(blocks.get(l.blockNumber!))],22 );23 }24 });25}on conflict (tx_hash, log_index) do nothing makes the whole indexer safe to restart, re-run, and overlap. Idempotency is the property that lets you stop worrying about crashes.
Live tail with reorg detection
Once caught up, poll for new blocks. Before indexing a new block, check that its parent hash matches the last block hash you stored — if it does not, a reorg happened and you need to roll back.
1const CONFIRMATIONS = 5n; // index up to head - 5 to reduce reorg churn2 3export async function tick() {4 const head = await publicClient.getBlockNumber();5 const safeHead = head - CONFIRMATIONS;6 const { last_block, recent_blocks } = await loadState();7 8 if (safeHead <= last_block) return;9 10 // reorg check: does the block after last_block still descend from us?11 const next = await publicClient.getBlock({ blockNumber: last_block + 1n });12 const known = recent_blocks.find((b) => BigInt(b.number) === last_block);13 if (known && next.parentHash !== known.hash) {14 await handleReorg(last_block, recent_blocks);15 return; // next tick re-indexes from the rolled-back point16 }17 18 await backfill(last_block + 1n, safeHead);19 await recordRecentBlocks(last_block + 1n, safeHead);20}21 22async function handleReorg(from: bigint, recent: RecentBlock[]) {23 // walk back until a stored hash still matches the chain24 let good = from;25 for (const b of [...recent].reverse()) {26 const onChain = await publicClient.getBlock({ blockNumber: BigInt(b.number) });27 if (onChain.hash === b.hash) { good = BigInt(b.number); break; }28 }29 await db.query("delete from trades where block_number > $1", [good]);30 await db.query("update index_state set last_block = $1 where id = 1", [good]);31 console.warn(`reorg — rolled back to block ${good}`);32}The front end just reads Postgres
1export async function GET(req: Request) {2 const trader = new URL(req.url).searchParams.get("trader");3 const rows = await db.query(4 `select tx_hash, token_in, token_out, amount_in, amount_out, ts5 from trades6 where ($1::text is null or trader = $1)7 order by block_number desc, log_index desc8 limit 50`,9 [trader],10 );11 return Response.json(rows);12}13// fast, paginable, filterable — none of which getLogs gives youRun the indexer as a separate long-lived process (a worker, a small container, a cron for the tail), not inside your Next.js app. It needs to run continuously and independently of request traffic.
When to use something else
- Many contracts, or contracts you do not control → a framework (Ponder, or a subgraph) that handles ABIs, factories, and multi-source indexing.
- You need it indexed across ten chains → hosted (Goldsky, Alchemy, Envio) — running ten reorg-safe tails yourself is a job.
- Complex derived state / aggregations that change per block → Ponder or a subgraph's mapping model fits better than raw SQL triggers.
A single contract, a few events, one chain: you do not need a platform. You need getLogs in a loop, an idempotent insert, and a parent-hash check. The front end thanks you with instant, filterable queries.Found this useful? Pass it on.
Want a custom write-up for your team? Get in touch.
Building something like this?
If a post here maps to a problem on your roadmap, that's usually a good sign we should talk.