Reading the ClickHouse Sink Announcement — A Same-Day Architectural Memo

From: Kaustubh Agrawal — Growth Engineer candidate Source: docs.envio.dev/blog/clickhouse-sink-hyperindex-v3 (opens in a new tab) Companion docs: ENVIO_REVENUE_MODEL.md · ENVIO_REVENUE_MATH.md · ENVIO_GROWTH_PLAN.md · ENVIO_DECK_OUTLINE.md · ENVIO_FIRST_24_HOURS.md · ENVIO_VIDEO_SCRIPT.md · ENVIO_INDEXER_TEARDOWN.md · ENVIO_ONBOARDING_FORENSIC.md

This is the freshest of the memos in the package — the announcement dropped recently and I had hours, not weeks, to think about it. The other artefacts in this package come from sustained reading; this one comes from a same-day pass at the architecture, the customer fit, and the Growth implications. Caveat amplified accordingly: where the indexer teardown was code-grounded, this one is reasoning about announced design. I'd want to revise it after another week of thinking — but I also wanted to send something the same week the feature shipped, because that's exactly the cadence a Growth Engineer should be operating at.

TL;DR — Six Observations

The shape of the observation: this is a strategically positioned release, not a tactical one. The Sink is engineered to unlock a customer segment Envio couldn't fully serve before — protocols whose product is analytics on top of indexed data. That's a different conversation than "we index your contracts faster." It's a different sale, a different ACV, and a different growth playbook.

§1 What The Announcement Actually Is, In One Pass

The blog post describes a feature with a clean shape:

When an event hits a handler in HyperIndex v3, the entity changes that handler produces are flushed to both Postgres and ClickHouse in the same batch. Postgres remains the primary indexed state — it's what GraphQL queries against, it's where the live entities live, it's the existing developer experience. ClickHouse becomes a purpose-built analytics mirror (the engineering team's exact phrasing) — every entity change written as an INSERT into a history table, tagged with a checkpoint ID that links it to a specific block.

Two table types per indexer:

• Entity history tables — append-only, every change recorded as SET or DELETE actions
• Checkpoints table — one row per block, recording block number, block hash, chain ID, and event count

Current state is reconstructed via views that select the latest SET row per entity ID. That single design choice — current state as a view over append-only history — is what unlocks the rest of the architecture's elegance.

§2 The Three Properties of Onchain Data

The engineering team, quoting from the post, identifies three properties of onchain data that make ClickHouse a near-perfect match: "Append-heavy, Highly structured, Queried in aggregate."

Reading them in order:

• Append-heavy. Blocks are immutable once finalized. New events arrive continuously. ClickHouse's storage engine (column-oriented, sorted by primary key, with MergeTree-family tables) is engineered around exactly this access pattern — append-fast, scan-fast, mutate-rarely.
• Highly structured. Every event has a fixed schema derived from its ABI. There's no schema-on-read overhead. ClickHouse's typed columns (Decimal, DateTime64, Enum8/Enum16) align cleanly with what BigInt, Date, and enum types in schema.graphql already declare.
• Queried in aggregate. Analytical workloads on indexer data look like "sum the volume per pool per hour, top 100 addresses by 7-day fees, leaderboard refreshed every minute." Those are scans across millions of rows with GROUP BY clauses — the canonical strength of column-oriented storage.

The reason I'm spelling these out at length: the framing in the post is precise enough that it's clearly the engineering team's actual decision-making logic, not post-hoc marketing. That's a strong signal of design discipline. A team that can name the three load characteristics the design optimizes for is a team that built it deliberately, not one that bolted it on. The same memo could have led with "we added ClickHouse support." It chose to lead with why the substrate fit. That voice difference is what turns a feature announcement into an architecture announcement.

§3 Reorg-As-DELETE — The Most Elegant Piece

The reorg story is the part of the design I admired most on first read. The post puts it as: "a single DELETE per table that removes all rows above the reorg checkpoint."

Why this is elegant: in any system that mirrors blockchain state, reorg handling is usually the hardest correctness problem. The naive approach is to track entity-level mutation history in a separate table, replay forward from the reorg block, and reconstruct the diff. Most systems get this wrong on the first three implementations.

The Sink's design sidesteps the entire complexity surface. Because the history tables are append-only and every row carries the checkpoint ID of the block that produced it, a reorg becomes:

1. DELETE FROM <table> WHERE checkpoint_id > <reorg_checkpoint>
2. Replay from <reorg_checkpoint> forward

There's no partial state to unwind. No mutation log to reconcile. No reconciliation pass. The append-only model means the state at any historical block is always reconstructible from the rows whose checkpoint IDs are ≤ that block — which is also why time-travel queries fall out for free, with no separate snapshot infrastructure.

This is the kind of design where the engineering team chose a constraint up front (entities are append-only history) that paid back as a simplification across three downstream problems (reorgs, time-travel queries, audit trail). That pattern — single constraint, multiple downstream wins — is the signature of a well-thought-through architecture. It's the same kind of design discipline I read in Velodrome's separation of EventHandlers/ from Aggregators/ from Snapshots/ in the indexer teardown.

§4 The Dedicated-Plan Tier Wedge

The blog post is explicit: "Currently supported on the Dedicated Plan only." And: "You are bringing your own ClickHouse."

That's not a limitation — it's a tier-design choice, and it's correctly priced.

A team that needs analytics-grade query performance on top of indexed onchain data is, by selection, a team that:

• Runs production workloads at non-trivial event volume
• Has dashboards / BI tools / leaderboards as a product, not a side feature
• Has the engineering bandwidth to operate or contract a ClickHouse instance (ClickHouse Cloud, Altinity, self-hosted)
• Has the revenue to justify both the Dedicated tier and ClickHouse hosting costs

Those are the customers who should be on Dedicated. The Sink isn't a feature you bolt onto a hobbyist plan — it's the feature that defines the Dedicated tier.

This connects directly to the math memo's framing of expansion as a revenue lever. The Sink creates a structurally new tier-up trigger. Customers on Production Medium or Production Large who hit Postgres analytical query latency now have a clear path forward: upgrade to Dedicated, plug in your ClickHouse, keep your existing GraphQL endpoint working. The Sink doesn't ask them to migrate or re-architect. It asks them to add a second store. That's a much easier upgrade conversation than "rebuild your indexer on a new tier."

For Growth, this is meaningful: every analytics-product Envio customer is now a structurally pre-qualified upsell target. The conversation is concrete ("your dashboard query is slowing down — here's the path"), the upgrade is non-disruptive (Postgres still works), and the value-prop is observable in the same week (analytical query latency drops by orders of magnitude).

§5 The Customers I'd Outreach To First

This is where the design meets the existing customer wall. I'm naming customers that are already publicly listed on Envio's homepage and case-study pages — and mapping each to the analytical workload that pushes them toward the Sink.

If I were doing first-week Growth outreach on this feature specifically, the priority order would be: Velodrome (highest architectural alignment) → Sablier (literally already runs an analytics indexer) → Polymarket (largest event volume on the wall). Each gets a one-page custom outreach note grounded in their actual public indexer code or product surface. The other two — LI.FI and Limitless — are second-wave once the first three have produced reference case studies.

§6 The Velodrome Connection — A Callback to the Indexer Teardown

This deserves its own short section because the alignment is striking.

In the indexer teardown, I documented Velodrome's separation of concerns into EventHandlers/ (event ingestion), Aggregators/ (derived state computation), and Snapshots/ (hourly snapshots of aggregated entities). The LiquidityPoolAggregator.ts file alone is about 24KB and computes pool-level TVL, volume, fees, votes, and emissions across 12 chains.

Read forward to ClickHouse Sink: everything in the Aggregators/ + Snapshots/ layers is exactly the data that needs to live in ClickHouse, not Postgres. Aggregators produce derived state that gets queried in aggregate. Snapshots are time-bucketed by epoch. Both layers exist precisely because Velodrome's product surface (dashboards, charts, multi-chain reporting) has analytical query needs that the raw event entities don't satisfy.

The Sink doesn't change anything about Velodrome's existing handler code. It changes where the analytical reads land. Their dashboards stop scanning Postgres tables and start scanning ClickHouse tables, while their transactional reads (entity-by-ID lookups, fresh state queries) keep hitting Postgres unchanged.

For a Growth Engineer, this maps cleanly to a specific Day-1 outreach: a co-authored case study with the Velodrome team titled "From Aggregators to Analytics: how Velodrome moved its dashboard layer to ClickHouse Sink." The customer gets distribution and credit for being the first published case study. Envio gets the canonical reference customer for the feature. The story writes itself because the architectural fit is already there.

§7 The Experimental Caveats Are A Good Sign

The post includes two explicit warnings:

1. "During historical backfill, the sink does not store every intermediate entity change" — only the final batch state is written during the sync phase. Once at chain head, every change is captured.
2. "Do not run multiple sinks against the same ClickHouse database at the same time."

I read both as signs of engineering honesty. The first is a deliberate design tradeoff — full-fidelity backfill writes would balloon ClickHouse storage with intermediate state that's never queried. The second is the kind of operational footgun that engineering teams catch only by running the system in production. Calling them out in the launch post means the team has actually run it and is not going to leave customers to discover these on their own.

The Sink is flagged experimental. That's also honest — it's brand new, the API surface is small but evolving, and customers should know what they're opting into. Honest experimentalism beats premature stable-stamping every time.

§8 What This Signals About Envio's Product Direction

Most indexer features incrementally improve the existing customer journey: faster syncs, more chains, better DX. ClickHouse Sink is a different shape — it's a persona expansion, not a feature improvement.

Today's Envio default customer is a transactional dApp backend: load entities, render UI, refresh the dashboard. The Sink unlocks a second persona: the analytics product. Dashboards, leaderboards, reporting layers, BI integrations, time-series analytics. Same customer base in many cases — but a different budget envelope, because analytics products tend to be sold separately, often with different pricing, often to a different buyer (data teams, BI leads) than the dApp engineering team.

The Growth implication: existing accounts that started as transactional indexer customers now have a structurally valid path into a second product line. That's a clean expansion lever — one that doesn't compete with the original use case, doesn't require migration, and pays off in higher per-account revenue.

This is, structurally, the third major plot point of the math memo's expansion lever (Play 4): in addition to more chains and more indexers, there's now more data destinations. Three orthogonal expansion vectors, not two.

§9 The Growth Plays This Feature Unlocks

This is the section I'd want to walk through together at the planning meeting. Five specific plays, each with what gets shipped:

Play A — "From Postgres to ClickHouse: when to make the move" decision-tree post

A short canonical blog post that lets a customer self-diagnose whether they need the Sink. Three or four signals (query latency on aggregations, dashboard refresh times, time-bucketed reporting) plus a flowchart, plus links to the env-var setup. Ships in week one. Half-life: years. Becomes the doc every sales call links to when a prospect asks "is the Sink right for me?"

Play B — A Dedicated-Plan content track

Three or four assets focused specifically on the Dedicated tier: case studies with Sink customers, talks about the architecture, BI-tool integration tutorials. The content track positions Dedicated as the analytics tier, not just the expensive tier. That positioning shift makes the upgrade conversation easier on every existing mid-market customer.

Play C — Partner-integration kit for the BI ecosystem

The blog post names Metabase, Superset, Grafana, Tableau, Hex, and Redash as integration targets. Each one gets a 30-line example showing how to point it at an Envio-managed ClickHouse instance. Ship one per fortnight. Six BI tools, twelve weeks. Each example becomes a co-marketing opportunity with the BI vendor in question.

Play D — Waitlist conversion for the future managed offering

The post mentions a waitlist for an eventual managed ClickHouse offering. That waitlist is, on its own, a high-intent qualified pipeline of customers who want the Sink but don't want to operate ClickHouse themselves. A weekly Growth-side check-in with that waitlist (status updates, beta invites, integration help) keeps the pipeline warm until the managed offering ships. Cost: about an hour per week. Revenue: meaningful, when the managed product launches.

Play E — A "ClickHouse Sink in 30 minutes" pillar artefact

Direct parallel to Play 1 in the strategy memo (the "First Indexer in 30 Minutes" artefact). Loom video, repo, next-step doc. Walks a Dedicated customer from pnpx envio@v3.0.0-alpha.21 init through the four env vars to a ClickHouse instance with their first auto-generated tables. Single most reusable activation asset for the Sink. Re-shot every quarter as the alpha hardens to v3 stable.

All five plays share three properties: each is shippable inside the first 90 days of the role, each has a clear measurement signal, and each reinforces the others (the BI integration kit feeds the case-study content track which feeds the waitlist conversion which feeds the pillar artefact).

§10 Why I Wrote This The Same Week It Shipped

The honest answer: I wanted to demonstrate the cadence I'd want to operate at. A Growth Engineer at a small product-led company should be turning major releases into customer-facing assets within days, not months — because the recency window is when prospects are most actively googling the feature, when the team's launch energy is freshest, and when the news cycle is still amplifying.

Writing a thoughtful architectural memo within hours of the announcement is the kind of work that, internally, would be the seed of a blog post, an X thread, a conference talk, a customer-outreach campaign. Externally, it's the kind of artefact that signals to a prospect "this team treats their releases as starting points for sustained conversation, not single posts."

Same caveats as the indexer teardown apply, amplified by recency: I read the public surface of a feature announced very recently. Some of what's above will be obvious to your team, some will already be in flight, and some I'll have read in a way that misses internal context. The point isn't that I've gotten everything right — it's that I'm operating at the cadence the role demands, before the role is even formalised.

If anything in here is useful as input into our planning conversation, great. If it's mostly already on your radar, that's also useful — it tells me the team is operating at the level I want to be operating at. Either way, this is the work I'd be doing in week one anyway. I figured I'd start a few days early.

— Kaustubh