Here's a thing nobody tells you when you set out to build a real dapp: a chain on its own can't power a consumer app. It's a database, but it's a database that's optimized for transactions and durability, not for queries. It can answer "what's the state at block N?" in milliseconds, and it can answer "what was the state ten blocks ago?" with some work, but it can't answer "tell me when something changes" without polling.
For a token, that's fine — users care about their balance, they refresh, they see it. For a social network, that's death. Nobody wants to mash F5 to see new posts. Nobody wants to open a fresh page just to discover that someone replied to them three minutes ago. The whole "social" part of social media is the real-time-ness. People read other people while the people are still online.
The way you bridge that gap on every chain — Ethereum, Solana, ours — is with an indexer. A small server that watches the chain, materializes the events you care about into a regular database, and pushes updates to your clients in real time. The Graph is the Ethereum-world version of this. We didn't need The Graph. We needed about 200 lines of Node.js.
This chapter is the design walkthrough.
what an indexer actually does
The job, broken into three pieces:
- Tail the chain. Poll the RPC for new blocks, fetch their receipts, look at the events emitted by the contracts you care about.
- Materialize. Translate those raw events into rows of your own format and persist them in a database optimized for queries (SQLite, in our case).
- Broadcast. Push new rows to any client that's connected via WebSocket.
The full source for ours is in packages/social-indexer — three files, 200ish lines, no dependencies beyond better-sqlite3 and ws. Let me walk through each piece.
the tailer
async start(): Promise<void> {
let lastIndexed = this.opts.store.getLastIndexedBlock();
while (this.running) {
const head = await this.fetchHead();
while (lastIndexed < head && this.running) {
const next = lastIndexed + 1;
await this.indexBlock(next);
lastIndexed = next;
this.opts.store.setLastIndexedBlock(next);
}
await sleep(this.opts.pollIntervalMs ?? 1500);
}
}Every 1.5 seconds, ask the chain /chain for its current head, then walk forward block by block until we've caught up. Every block we index, we persist lastIndexedBlock to SQLite so a restart doesn't re-process anything.
The indexBlock function is the meat:
private async indexBlock(blockNumber: number): Promise<void> {
const receipts = await this.fetchReceipts(blockNumber);
const watch = new Set([
this.opts.contracts.profile.toLowerCase(),
this.opts.contracts.posts.toLowerCase(),
this.opts.contracts.follow.toLowerCase(),
this.opts.contracts.gallery.toLowerCase(),
this.opts.contracts.votes.toLowerCase(),
]);
for (const r of receipts) {
if (!r.events?.length) continue;
if (!watch.has(r.recipient.toLowerCase())) continue;
const acts = receiptToActivities(r, this.opts.contracts, blockTsMs);
for (const a of acts) {
const stored = this.opts.store.insert(a);
if (stored) this.opts.onActivity?.(stored);
}
}
}Pull receipts for the block, filter to ones where the recipient is one of our five contract addresses, ignore the rest. For each matching receipt, run its events through a pure mapping function that turns { name: 'Post', data: {...} } into our Activity row shape. Insert into SQLite (with a unique constraint on (tx_hash, log_index) so duplicate-indexing is a no-op), and if anything actually got inserted, fire the onActivity callback.
The key thing here is that the mapping function is pure. Same input, same output, no side effects, easy to unit-test. It's defined in mapper.ts and tested with about a dozen fixture cases before we ever talk to the chain.
the SQLite materialization
I went with SQLite over Postgres because at this scale a single file is nothing but upside: zero infra, instant startup, atomic writes via WAL, queries that are fast enough to not need an ORM. The schema is tiny:
CREATE TABLE activities (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT NOT NULL,
block_number INTEGER NOT NULL,
tx_hash TEXT NOT NULL,
log_index INTEGER NOT NULL,
actor_address TEXT NOT NULL,
target_address TEXT,
data TEXT NOT NULL, -- JSON blob
ts INTEGER NOT NULL,
UNIQUE (tx_hash, log_index)
);
CREATE INDEX idx_activities_block ON activities (block_number DESC);
CREATE INDEX idx_activities_actor ON activities (actor_address, ts DESC);That's it. Three indexes cover every read pattern the frontend has:
- The activity feed page sorts by
id DESC(which mirrorsblock_number DESCsince ids are monotonic). - A user's activity history queries by
actor_address+ ordered byts DESC. - Event-by-id detail is a primary key lookup.
If we ever needed full-text search across post content we'd add an FTS5 virtual table; we haven't needed it yet because that's a frontend search problem on a small dataset, not an indexer problem.
The UNIQUE (tx_hash, log_index) constraint is doing more work than it looks. It makes the whole pipeline idempotent. If the indexer crashes and restarts and re-processes the last block, the duplicate inserts are no-ops. If it accidentally re-fetches receipts for a block it already saw, same. If you restart with a different START_AT_HEAD setting and the histories overlap, same. Unique constraints are the cheapest possible idempotency primitive and I lean on them everywhere.
the WebSocket layer
This is where the real-time-ness lives:
this.wss = new WebSocketServer({ server: this.httpServer });
this.wss.on('connection', (sock) => this.onClientConnect(sock));
broadcast(activity: Activity): void {
const frame = JSON.stringify({ type: 'activity', activity });
for (const sock of this.clients) {
if (sock.readyState === sock.OPEN) sock.send(frame);
}
}The HTTP server and the WebSocket server share the same port (3001). When a client connects we add it to a set; when they disconnect we drop them. Every new activity that comes out of the tailer gets broadcast to every connected client as a single JSON frame.
There's one nicety: on connect, before we go live, we send a catch-up frame with the last 10 activities:
private onClientConnect(sock: WebSocket): void {
this.clients.add(sock);
const recent = this.opts.store.list({ limit: 10 });
sock.send(JSON.stringify({ type: 'hello', recent: recent.reverse() }));
}This is what makes the frontend feel alive even on first page load. The toast lane interprets the hello frame as "play these as a small animated stack of historical events" and the user lands on the page already seeing things have happened. Without it, you'd land on a static page with no activity unless someone happened to be posting at that exact moment.
the toast routing rule
I want to flag one design decision that gets a disproportionate amount of joy out of users when they see it.
Every WebSocket frame the frontend receives goes through this rule:
if (event.actorAddress === connectedWalletAddress) {
// top-right lane — your own action confirmation, longer dismiss
pushTop(event);
} else {
// bottom-left lane — global activity, 2-second dismiss
pushBottom(event);
}Two parallel toast streams from the same WebSocket. Your own actions land top-right with explorer link + "view post" link, displayed for 8 seconds. Other users' actions land bottom-left with linked addresses, displayed for 2 seconds.
It sounds tiny. But the moment you connect a wallet and post something, you immediately understand the difference: the confirmation acknowledging YOUR action is in your sightline (top-right is where every modern app puts confirmations), while ambient activity by strangers stays out of the way (bottom-left, low-attention). Both lanes pause-on-hover so if you actually want to click into a toast it doesn't disappear out from under you.
It's the kind of detail you can only really design for once you've split your sources of update into "things I just caused" vs "things the world just did." Which the indexer pattern naturally lets you do.
why not put this on the chain?
The recurring question with anything indexer-shaped is "couldn't you do that on-chain?"
Technically, sometimes, yes. You could maintain a running activity log in a contract storage slot and have every action push to it. We don't, and you shouldn't, for a really fundamental reason: the chain is global state shared by every node, and most of what an indexer materializes is per-client UX state.
A user's home feed isn't part of the chain's truth. It's a view of the chain. Computing it on the chain would mean every node reaches consensus on your home feed — which doesn't make sense, because your home feed depends on who you follow, which is global, but also on your reading position, your filter preferences, your "show NSFW yes/no" toggle, etc. None of those things should be slowing down block production.
The right framing: the chain is the source of truth, the indexer is the materialized view. The chain stores facts ("alice posted, bob followed alice, carl voted on alice's post"). The indexer stores derived data ("here is alice's home feed sorted by hot," "here is the activity stream"). Different jobs, different tools.
what the indexer cost us
The indexer is 200 lines of Node.js, a 12 KB compiled output, runs in ~30 MB of RAM, and uses a SQLite file that's currently smaller than a single iPhone photo. It's deployed alongside the Next.js frontend on a single Hetzner box that costs about €4 a month. There's no Postgres, no Redis, no Kafka, no The Graph subgraph, no message queue.
The whole real-time feed for a non-trivial consumer dapp on a smart-contract chain runs on that one cheap VPS. I think this is going to surprise people, because the established pattern in the EVM world is "decentralize everything, including the indexer" via The Graph or similar — and the result is usually a thing that's slower, more expensive, and harder to debug than just running your own. If your indexer is non-trivial, run your own indexer. It's fine.
In Chapter 5 we'll go up the stack one more level — into the frontend, where the most interesting problem is "how do you do wallet connect on a chain that doesn't use ECDSA?"