@ekubo/indexer

Service for indexing Ekubo events into a Postgres database.

Overview

The indexer focuses on producing an always-consistent realtime view of Ekubo events. Every network — Starknet included — is read by polling its own RPC over block ranges; see Block streams.

Events are not transformed by the indexer, simply cataloged for later use such as in materialized views or complex analytical queries.

Syncing a new node

It can take days to sync a fresh database with all the networks, so it's recommended to start from a backup of our production database.

Nightly dumps are published by .github/workflows/pg-dump.yaml—grab the most recent db-backup-<timestamp>.dump artifact from the Actions tab, then import it into your Postgres instance:

pg_restore --clean --if-exists --no-owner \
  --dbname postgres://user:pass@host:5432/dbname \
  db-backup-20240101T000000Z.dump

During restore you may see warnings or errors about the DigitalOcean doadmin role or the pg_cron extension; those are expected and can be ignored if your target database lacks the same privileges/extensions.

Join the Discord and ask in the #devs channel if you need support.

Restoring a dump from the command line

The sequence below goes from an empty (or stale) local database to a warm one without opening a browser, and is safe to run unattended. It assumes the gh CLI is authenticated against this repository.

1. Find the newest successful dump. Artifacts are kept for 7 days, so the most recent run is usually the only one still downloadable:

gh run list --repo EkuboProtocol/indexer --workflow pg-dump.yaml \
  --status success --limit 1 --json databaseId,createdAt

2. Download it. The artifact is named db-backup-<run_id> and contains a single db-backup-<timestamp>.dump:

gh run download <run_id> --repo EkuboProtocol/indexer --dir ./dump

The dump is on the order of 8 GB. gh buffers the whole artifact into $TMPDIR before extracting it, so budget roughly twice its size in free space, plus room for the restored data. Expect the download to take several minutes.

3. Check the archive before touching the database. This only reads the table of contents, so it is fast and catches a truncated download:

pg_restore --list ./dump/db-backup-*/db-backup-*.dump | head

4. Restore. CI dumps with PostgreSQL 18, so the local client must be 18 or newer — verify with pg_restore --version. --clean --if-exists drops each object the dump recreates, so a database with an older copy of the schema does not need to be emptied by hand:

pg_restore --clean --if-exists --no-owner --no-privileges -j 8 \
  --dbname postgres://postgres:postgres@localhost:5432/postgres \
  ./dump/db-backup-*/db-backup-*.dump

pg_restore exits non-zero on a restore that worked. The dump carries the production doadmin role and the pg_cron extension along with its scheduled jobs; locally those statements fail, which only means scheduled jobs will not run. Do not gate on the exit code — confirm the data instead:

psql -d postgres -c "select chain_id, order_key from indexer_cursor order by chain_id"
psql -d postgres -c "select count(*) from pool_keys"

Every chain in the snapshot should have an indexer_cursor row, and that block number is where an indexer started against this database will resume from.

5. Apply any migrations newer than the snapshot, since the dump reflects production at the time it was taken:

bun run migrate

Automated database dumps

Nightly backups run through .github/workflows/pg-dump.yaml, which connects to the production database using repository secrets, runs pg_dump -Fc, and uploads the resulting db-backup-<timestamp>.dump as a GitHub Actions artifact (retained for 7 days, named db-backup-<run_id>). These artifacts let you bootstrap a new node quickly without waiting for a multi-day sync—grab the latest run from the Actions tab when you need a fresh snapshot.

Docker image

Build the runtime image once. Bun executes the TypeScript sources directly, so no separate build step is required:

docker build -t ekubo-indexer .

CI publishes the same image to GitHub Container Registry under ghcr.io/ekuboprotocol/indexer:<git-sha> so other environments can pull the exact build:

docker pull ghcr.io/ekuboprotocol/indexer:<git-sha>

The resulting image can execute any of the TypeScript entrypoints. Run the network-specific indexer entrypoint directly:

docker run --rm \
  -e NETWORK=mainnet \
  ekubo-indexer bun src/starknet.ts

Running scripts from the Docker image

Override the command to reuse the same image for auxiliary scripts such as migrations. The default entrypoint is already bun, so point it to the desired TypeScript file:

docker run --rm ekubo-indexer scripts/migrate.ts

Match the examples in .do/app.yaml to run other helpers, e.g.:

docker run --rm ekubo-indexer src/price-sync/index.ts

Token metadata generation and database synchronization are owned by the EkuboProtocol/default-tokens repository. The indexer image does not fetch or write token metadata.

The price-sync process runs every configured job as an independent recurring loop. A job declares the chains it may write prices for plus a three-character source identifier; startup fails if two jobs claim the same chain and source. Most jobs price one chain, but a job may price several when one upstream request covers them all. TOKEN_PRICE_SYNC_INTERVAL_MS controls the default cadence in milliseconds (default: 60000). CoinGecko jobs use COINGECKO_TOKEN_PRICE_SYNC_INTERVAL_SECONDS; set it to a positive number and provide COINGECKO_API_KEY to enable them. Zero or an unset value disables those jobs.

Two properties keep CoinGecko request volume flat rather than growing with erc20_tokens:

• Native currency prices for every chain come from a single cgn job. Chains sharing a CoinGecko coin ID cost one request between them, not one apiece.
• The per-chain cg1 jobs request only the tokens CoinGecko has actually priced. Everything else is re-probed on a slow rotation (a full pass per day), so a chain with thousands of unlisted tokens does not pay for them every cycle. This state is held in the worker process, so a restart replays one full sweep before settling back down.

Price source prioritization

Per-source confidence lives in erc20_token_price_sources, seeded once by the migration — the worker never writes that table, so policy adjustments (made in a later migration) survive deploys. Freshness is per observation instead: each price row carries a valid_until, stamped by the job as three of its own sync intervals (one-minute floor), except Chainlink, which uses the feed's heartbeat window anchored at the round's updatedAt. Rows predating the column fall back to five minutes past their timestamp. The latest-price cache prefers the quoter (the interface derives price-impact loss from it), then Chainlink, then CoinGecko, then SushiSwap; it averages sources tied at the highest confidence, records the result under the synthetic AVG source, and reconciles expiration once per second so a lower-confidence source is promoted when the leader goes stale.

Chainlink feeds

Chainlink token/USD feeds supplement those sources over EVM RPC. Set CHAINLINK_TOKEN_PRICE_SYNC_INTERVAL_SECONDS to a positive number and provide CHAINLINK_TOKEN_PRICE_CONFIG as a JSON object keyed by chain ID. Each chain declares fallback RPC URLs and a Chainlink Reference Data Directory catalogUrl. The catalog supplies token/USD proxy addresses and heartbeats; feeds are matched only when an eligible indexed token and catalog base asset have a unique symbol. Discovery prefers standard reference-price proxies, falls back to the underlying proxy associated with a shared SVR feed on networks that only publish that variant, and also supports primary tokenized-price feeds. Secondary SVR proxies, ambiguous symbols, and hidden, deprecating, or non-USD feeds are skipped. An optional feeds array can override or supplement discovery for exceptional mappings.

Catalogs are refreshed hourly by default, controlled by CHAINLINK_FEED_CATALOG_REFRESH_INTERVAL_SECONDS, and the last successful response remains usable during a catalog outage. All discovered feed reads for a chain are aggregated into one on-chain Multicall3 call, which the RPC provider accounts for as a single eth_call; multicallAddress can override the standard 0xcA11...CA11 deployment for a chain. Stale, incomplete, non-positive, and superseded rounds are skipped. The RPC-reported chain ID is also checked before reading feeds.

{
  "1": {
    "rpcUrls": ["https://eth-mainnet.example/v1/API_KEY"],
    "catalogUrl": "https://reference-data-directory.vercel.app/feeds-mainnet.json"
  }
}

Chainlink jobs are disabled when the interval is zero/unset or the config is empty. Valid observations are stored under the cl1 source using the feed round's updatedAt timestamp, and unchanged rounds are not inserted repeatedly. One failing feed does not prevent fresh observations from other configured feeds on that chain.

Block streams

Every network is indexed by one polling, range-reading stream (src/_shared/blockStream.ts). Polling, reorg detection, the poll backoff, the retention window and the cursor live there and are identical on every chain; the per-chain adapters supply only what a chain family actually does differently -- src/evm/logStream.ts and src/starknet/eventStream.ts.

EVM

The EVM adapter is log-driven rather than fetching a header per block. A poll is two requests whatever the chain's block time:

1. eth_getBlockByNumber("latest"), which gives the head and a real block hash for the cursor.
2. eth_getLogs over everything since the last poll, which returns blockHash and blockTimestamp on each log.

Those two log fields are the only header data the runtime persists, so no per-block header read is needed.

event_index is the log's block-wide logIndex, unchanged. The apibara RPC stream never populated logIndexInTransaction either, so keeping it identical is what makes this a stream swap rather than a change to a primary key other tables order on.

That scheme has an inherited ceiling: compute_event_id packs the index into 16 bits, and logIndex counts every contract's logs in the block, so a block with more than 65,535 logs in total cannot be indexed at all. The stream now refuses such a log with a message naming the cause, rather than letting it surface as a failure deep inside a Postgres function. Raising the ceiling means re-basing event_id onto a per-transaction index, which is a migration, not a stream change. base_fee_per_gas is still written but is read by nothing, and rows for blocks with no events are removed within a day by delete_old_empty_blocks.

Measured against the previous stream on Monad (0.3 s blocks), at the same two second cadence: 9.7 requests per poll became 2.1.

Starknet

Starknet reads starknet_getEvents over the same window the EVM chains read eth_getLogs over, and shares everything below that. Three things differ, and all three are in the adapter:

• One address per request. starknet_getEvents takes a single address, not a list, so asking per contract would be twelve requests a poll. The range read is filtered by event selector instead -- which the RPC accepts as an OR-list -- and narrowed to our contracts locally. On mainnet that prefilter takes a range read from 15.9 to 3.3 events per block. A processor filtering on address alone disables the prefilter rather than silently narrowing the read.
• The URL pins JSON-RPC v0.10. That is the first version where EMITTED_EVENT carries transaction_index and event_index; on v0.9 they are absent and the continuation_token counts matched results, so it cannot supply them either. The stream refuses to index an event without them rather than defaulting to zero, so pointing this at an older spec stops the worker instead of silently writing wrong primary keys. One block read remains, for the timestamp, and only for blocks above the cursor -- the reorg window is re-read every poll and paying a per-block cost for it would be ~390k requests a day instead of ~6k.
• event_index is per transaction, counting every event the transaction emitted rather than only ours. That is what the apibara DNA stream stored and what compute_event_id has packed into every Starknet event_id ever written, so it is not ours to renumber. It counts the events in between, so it is emphatically not a position within the filtered results: block 14555766 holds 3 and 19 for one transaction, where numbering only the matched events would give 0 and 1.

Those two numbers were checked three ways before this relied on them: v0.10's values, a reconstruction from starknet_getBlockWithReceipts, and the rows the DNA stream wrote years ago all agree, over 5,000 mainnet blocks and 44 event tables with no row missed.

Note that this differs from EVM, where event_index is the block-wide logIndex. Both are inherited from the streams they replace.

Finality is l1_accepted -- settlement on Ethereum, hours behind the head -- rather than EVM's finalized. latest is ACCEPTED_ON_L2 and can still be reorged; pre_confirmed has no hash at all, so nothing that cannot be rolled back to is indexed.

scripts/verifyStarknetStream.ts replays a settled range and asserts the positions it reconstructs are exactly the ones already in the database.

Correctness

Missing an event is the failure that matters, because nothing errors and the gap surfaces later as a wrong number downstream. Three choices follow from that.

• Range queries, not subscriptions. An eth_getLogs call answers for the blocks it was asked about or it errors. A dropped WebSocket frame is indistinguishable from silence, and no provider guarantees delivery.

• Silent truncation is refused. A provider that caps results and returns exactly the cap looks identical to one that found exactly that many. SUSPECT_LOG_COUNT (default 10,000, Alchemy's documented limit) is the count treated as suspect: the range is split and re-read rather than believed, and a single block still landing on the cap throws.

• One endpoint per chain. A comma-separated EVM_RPC_URL still parses, but viem routes per request, so a fallback list lets two calls in one poll be answered by backends with different views of the chain. One endpoint fails by stopping, which is safe, because the cursor is durable.

• A refused range fails fast. An earlier version recognised "the range was too wide" from the error text and recovered by splitting. Every provider words that differently — eth_getLogs is limited to a 10,000 range (Base), block range greater than 10000 max (Ink), Block range is too large (Optimism), Log response size exceeded (Alchemy) — and any of them can reword it in a release, at which point recovery silently becomes a crash loop. The classification was the liability, so it is gone. Nothing is lost: viem's transport already retries what is worth retrying, with backoff (HTTP 403/408/413/429/500/502/503 and JSON-RPC -1, -32005, -32603 and 429), so anything reaching the stream has survived that and is a real error. The range is ours to choose, so the error names GET_LOGS_RANGE_SIZE as the knob and carries the provider's own words as the cause.

  Keep GET_LOGS_RANGE_SIZE at or under 5,000 on Alchemy. Below that boundary it applies no result cap, so a range refusal is not reachable; above it, a 10K log cap applies. Measured density suggests 10,000 would be fine too — Robinhood averages 0.04 matched events per block and Arbitrum 0.0002 — but since the stream fails fast on a refusal rather than splitting out of one, a 2x wider backfill is not worth an occasional stall.

Reorgs are found by re-reading a window below the cursor each poll and comparing it against what was emitted. A block that changed hash, lost its logs, or gained logs it did not have produces an invalidate. The window must be deeper than any reorg the chain can produce; a reorg touching no log of ours changes nothing we store and is not looked for.

The window is configured in seconds — REORG_WINDOW_SECONDS, default 120 — and converted to a block count per chain from a block rate measured off the head read each poll already makes. A block is not a unit of time, and these chains run from 12s to 0.09s apart, so the block count this used to take meant 768s of protection on Ethereum and 6s on Robinhood. Ethereum is pinned at 1152s in .env.evm.mainnet, its worst-case head-to-finalized span, because above finality the window is its only protection.

The derived count is capped at half GET_LOGS_RANGE_SIZE, which guarantees the other half of every read is forward progress no matter what the chain's block rate turns out to be. When the cap binds, the stream warns once with the window it actually got; raising GET_LOGS_RANGE_SIZE is the remedy.

Backing off on a quiet chain

A poll costs the same two requests whether it finds an event or none, so a chain that has produced nothing in months costs exactly what the busiest one does. The interval therefore holds at POLL_INTERVAL_MS (default 2,000) for QUIET_POLLS_BEFORE_BACKOFF (default 30) consecutive polls that index nothing, then doubles per empty poll up to MAX_POLL_INTERVAL_MS (default 30,000). Any matched log, or any reorg, puts it straight back to the floor.

That first stretch is the latency guarantee: a chain that indexed anything in the last minute keeps polling at full rate, so a busy chain never leaves the floor. Backing off is a delay and never a miss — a range query asked less often reads a wider range, not a narrower one. The cost is that an event on a dormant chain can take up to MAX_POLL_INTERVAL_MS to be indexed, and indexer_cursor.head_base_fee_per_gas is that stale meanwhile. Set MAX_POLL_INTERVAL_MS equal to POLL_INTERVAL_MS to switch it off for a chain where that is not acceptable, as .env.evm.mainnet does.

On restart

A log diff cannot tell a restart apart from a reorg: the window is seeded from whatever the chain says now, so there is nothing to disagree with. So the stored cursor is checked directly, once, with a single eth_getBlockByNumber. If its hash no longer matches, the stream invalidates back a full reorg window before reading anything. A block hash commits to its entire ancestry, so that one comparison settles every block beneath it — this is strictly stronger than the diff it replaces, and it is what preserves the guarantee the previous stream's initializeStartingCursor provided.

Two related rules keep a restart from doing damage of its own:

• The first window read is adopted as the baseline rather than diffed against, so a deploy does not roll the chain back on every start.
• A rollback's cursor carries the landing block's hash when that block is one the stream recorded, so a restart in the window right after a reorg can still check canonicality. Only log-bearing blocks are recorded, so this is best-effort.
• A finalized block ahead of the cursor is held back rather than announced, and the check runs after the cursor advances rather than at the top of the tick. On a chain that finalises within a block or two of the head, comparing against a cursor that still holds last tick's value would suppress the message forever and freeze finalized_order_key. The runtime's recovery path resets the cursor to the last finalized one, so announcing a finalized block past ours would let a later error move the cursor forward and skip everything in between. Holding it back only ever costs a re-index.

For the same reason the re-read never begins above the cursor. On a chain that finalises in well under a second, finality can overtake a cursor that has fallen a few blocks behind, and clamping the read to the finalized block would drop the blocks in between without an error.

The rollback never rewinds past the finalized block, since a finalized block cannot be the reorg point and the rows beneath it are settled.

Standing still

Once caught up, a head that has not moved emits nothing, and the window is not re-read at all. A block hash commits to its entire ancestry, so a head that is byte-for-byte last poll's proves nothing below it has changed and the re-read could not find anything. eth_getLogs is 60 of the roughly 80 Alchemy compute units a poll costs, so on a 12 s chain polled every 2 s this is where the cost stops scaling with block time: five polls in six become a single head read.

The comparison is on hash, not height, because a one-block reorg leaves the height alone.

The trade is that a stale answer lingers slightly longer. If latest reports one hash while the eth_getLogs in the same poll is answered by a backend still on the previous one, the mismatch is not noticed until the next block arrives rather than on the next poll. That is a consequence of the single-endpoint rule above being violated, and it self-heals by rollback either way.

scripts/verifyLogStream.ts settles the question directly for a given chain and range, replaying it through the stream and diffing against a direct query:

bun scripts/verifyLogStream.ts https://mainnet.base.org base 600 100 \
  0x4200000000000000000000000000000000000006

It exits non-zero on any mismatch.

Database migrations

• Local: bun run migrate or bun scripts/migrate.ts (both invoke scripts/migrate.ts).
• Docker: docker run --rm --env-file .env ekubo-indexer scripts/migrate.ts.
• DigitalOcean: the .do/app.yaml run-migrations pre-deploy job automatically applies migrations before rolling out new workers, ensuring the Postgres schema is up-to-date.

Migration files live under migrations/ and execute in order via scripts/migrate.ts.

DigitalOcean App Spec

The DigitalOcean Apps spec in .do/app.yaml documents the full production stack:

• Workers for each network (e.g.: starknet-mainnet, eth-mainnet, base-mainnet) that run the corresponding network entrypoint (bun src/starknet.ts or bun src/evm.ts) with the appropriate NETWORK value, pulling the published Docker image (ghcr.io/ekuboprotocol/indexer:${IMAGE_TAG}).
• Managed Postgres (indexer-db-nyc1) wired in via the PG_CONNECTION_STRING env var, alongside the per-worker RPC secrets (EVM_RPC_URL, STARKNET_RPC_URL).
• A run-migrations pre-deploy job and the long-running src/price-sync/index.ts process. Each price source/chain job has an independent timer, with separately configured CoinGecko and Chainlink cadences. The app spec discovers Chainlink feeds for eligible tokens on Ethereum, Base, Arbitrum, and Robinhood through Chainlink's multi-network catalogs and the existing Alchemy API key secret.

Use this file as a base to recreate the stack in a new DigitalOcean App Platform project or as a reference for configuring similar infrastructure elsewhere.

Breaking changelog (tracking as of 2025-11-17)

This log records indexer deployments that:

• require manual intervention beyond running scripts/migrate.ts (e.g., backfilling data, reseeding state, or pausing workers), or
• introduce schema changes, even when the standard migration workflow can apply them automatically. Schema-only updates may not mandate manual steps but can still break downstream consumers that rely on the previous structure, so they belong here as well.

2026-09-08: Starknet moves off apibara DNA onto its own RPC

No schema change. Requires configuration before deploying.

Starknet is now indexed by the same stream as every EVM chain (src/_shared/blockStream.ts) instead of the apibara DNA gRPC stream. Two manual steps:

1. starknet-mainnet must be enabled on the Alchemy app whose key ALCHEMY_API_KEY holds. It was an EVM-only app, so the worker will fail to read the chain until it is. (Already done.)
2. STARKNET_RPC_URL is a new secret in the app spec.

Apibara is gone entirely: the four @apibara/* packages, APIBARA_URL, DNA_TOKEN, and the APIBARA_DNA_TOKEN, STARKNET_MAINNET_APIBARA_URL and ETH_MAINNET_APIBARA_URL secrets the deploy workflow passed to envsubst. The repository secrets themselves can be deleted in GitHub once this has shipped; nothing reads them.

Downstream is unaffected, checked rather than assumed. api and quoter-service are the only things that read this database directly (mcp, interface and wallet go through the api), and both read the same five indexer_cursor columns plus event_id. Against a live cursor row written by the DNA stream, the new adapter returns an identical head_block_hash, head_block_time and head_base_fee_per_gas; event_id is unchanged, which is what quoter-service's incremental sync keys on (last_event_id > $n); and fork_counter, which it compares to decide a full refetch, only moves on an invalidate, of which a live boot from the real cursor produced none.

The one behavioural difference is latency: DNA pushed a block as it was produced, and this polls, so Starknet events land up to POLL_INTERVAL_MS (2 s) later than they used to.

event_id is unchanged: transaction_index and event_index come from starknet_getEvents under JSON-RPC v0.10 and match what DNA wrote, verified over 5,000 mainnet blocks against 44 event tables with no row missed. The URL must name v0.10 or later; the stream refuses to index without those fields rather than defaulting them.

Adds 1.9M Alchemy compute units a day ($26/month at a 2 s poll floor), where DNA was billed separately.

2026-09-06: Market depth refresh, 25 s → 10 s, and a 10-minute schedule

00126_faster_pool_market_depth. Redefines pool_market_depth_view and reschedules its cron job. Same columns, same values, no consumer changes.

refresh_pool_market_depth was the most expensive job left on the instance: 25.1 s average over 24 hours (35.5 s over 7 days, max 169.7 s), every 15 minutes. Two independent causes, neither of them a missing index — the index this needed already existed from 00118.

• The median-tick step read the whole swaps table. Its LATERAL subquery had no LIMIT/OFFSET, so the planner pulled it up into a plain join, lost the per-pool correlation, and estimated ~513k rows per pool for a one-hour window. Against that estimate a hash join over a sequential scan of all 46M swaps beats 5,000 index probes, and that is what it picked: ~20 s and ~11 GB read from disk per refresh, 96 times a day, roughly a terabyte. Forcing the index with enable_seqscan = off does not help either: measured, it ran past 170 s and the statement timeout cut it off before the plan could be captured. The estimate is what is wrong, so OFFSET 0 is the fix — the standard optimisation fence, no semantic change. That step now runs in 0.29 s entirely from cache.

• The tick math was quadratic in disguise. Every (pool, depth) pair intersected the depth band against every one of the pool's tick segments (the largest pool has 1,763), so a pool cost ticks × 41 range intersections. It is now one ordered pass per pool: prefix sums of each segment's token amounts, and a band's amount is the difference of the cumulative value at its two edges. The largest pool goes from 1,689 ms to 93 ms.

The rewrite is exact, not approximate. NUMERIC + - * are arbitrary-precision and lossless; the only rounding is inside POWER() and the 1/p divisions, and both definitions evaluate those at the same tick values, so the reassociated sums agree digit for digit. Verified two ways: on production, both definitions in a single snapshot returned the same 69,150 rows with zero differing values (24.5 s → 10.0 s); and in tests/migrations/faster-pool-market-depth.test.ts, which snapshots the old view's output, applies the migration, and asserts strict equality over a fixture covering bands inside the tick range, bands running off both ends, a pool whose liquidity returns to zero mid-range, a pool priced from pool_states because it never swapped, a pool with no ticks, and a pool whose fee is wider than the tightest bands.

Watch for one trap if this is ever touched again: deriving the band edge prices as p(last) × p(offset) via exponent laws is one extra rounding step and drifts ~1e-12 relative. Each edge price is computed directly from its own tick.

Schedule. The REFRESH … CONCURRENTLY diff costs ~0.19 s on top of computing the view, so a run goes 25.1 s → ~10.5 s. The job moves from every 15 minutes to every 10: 1,512 s/day against today's 2,410, a 37% cut, while the worst-case lag between a liquidity change and the pools table drops from 25 minutes to 20 (the API's own 600 s cache is the other half of that). Every 5 minutes was considered and not taken — it would cost ~3,000 s/day, more than today, for a further 5 minutes. It is a one-token change in the migration's DO block if that trade is wanted later.

Deploy: CREATE OR REPLACE VIEW takes ACCESS EXCLUSIVE on the view alone and only ACCESS SHARE on the tables it reads, so unlike 00120 and 00123 it holds nothing the workers can wait on and needs no LOCK TABLE blocks. The one lock it can wait behind is a cron refresh already reading the view, hence the 15-minute lock_timeout. The migration does not refresh the matview; the next cron run picks up the new definition.

2026-09-06: Price-source bloat and stale statistics

00125_price_source_bloat_and_stale_stats. Reloptions and ANALYZE only — no lock that conflicts with the workers — plus one manual operator step.

Two findings from the I/O sweep that followed 00124:

• erc20_tokens_latest_price_by_source is the largest live source of disk reads on the instance (~1 GB per five minutes): 17,223 live rows in 978 MB (594 MB heap + 384 MB index), ~6 MB of live data. Autovacuum is not behind on it — it fires on essentially every naptime. The heap is the residue of the 2026-09-01 incident described under 00119: a worker blocked 22 hours inside pg_advisory_lock pinned the vacuum horizon, and at ~90 updates/s that left ~7M dead tuples vacuum could not remove until the session ended. Vacuum reclaims that space in place; only a rewrite shrinks the heap.

  The migration sets fillfactor=70 (HOT headroom after the repack; the table's only index is its primary key) and autovacuum_vacuum_scale_factor=0.01 (matching 00119's setting on the sibling table erc20_tokens_latest_price, which got fillfactor=80). Neither prevents a repeat of the incident — that needs lock_timeout / statement_timeout on the indexer role and an alert on age(backend_xmin), a separate change.

  Manual step, after the migration is applied: rewrite the table once. Preferred, because it rewrites without an exclusive lock:

  pg_repack -d defaultdb -t erc20_tokens_latest_price_by_source

  (pg_repack 1.5.2 is available on the managed instance; it needs CREATE EXTENSION pg_repack in defaultdb and a matching local client.) The fallback is VACUUM (FULL, ANALYZE) erc20_tokens_latest_price_by_source, which takes seconds at 17k live rows but holds ACCESS EXCLUSIVE: it blocks the price sync's reads as well as its writes, and nothing in src/price-sync retries a failed cycle, so prices go stale for the duration. If using it, set lock_timeout = '10s' first and run outside :00–:02, when the hourly prune_erc20_tokens_usd_prices DELETE is holding the table's trigger path. Expect ~978 MB → a few MB and the table's heap_blks_read in pg_statio_user_tables to go flat.

• Planner statistics on four large event tables were months stale (statistics reset at the 08-25 restart; the default 10% analyze threshold would not re-fire for months): nonfungible_token_transfers reported 13,645 live rows against 3.1M real, nonfungible_token_owners 10,751 against 2.35M, protocol_fees_paid 426 against 1.86M, position_fees_collected 2,251 against 1.81M. This is what had the positions-history query (WITH transfers …, 28k calls at 909 ms — the slowest API statement remaining) estimating one row for a whole chain and scanning millions of transfers per request. The migration runs ANALYZE on those four and sets autovacuum_analyze_scale_factor=0.01 on them so it stays current.

  ANALYZE alone repairs the plan: with current statistics the existing (chain_id, emitter, token_id, …) index serves chain_id + token_id by skip scan. An extra (chain_id, token_id) index was tried in review and dropped — it saved ~14 buffers a call against the skip scan and nothing at all under stale statistics, the actual failure mode. The hourly tables are deliberately not on the list; they autoanalyze every few days at the default threshold.

Deploy: ANALYZE and reloption changes take SHARE UPDATE EXCLUSIVE, which does not conflict with the workers' writes, so this migration holds nothing a worker can wait on and needs no LOCK TABLE blocks. It runs in seconds.

2026-09-05: Index generated_drop_proof for the claims endpoint

00124_index_generated_drop_proof_address. Schema change; no consumer changes.

incentives.generated_drop_proof (3.1M rows, 2.6 GB, proofs inline) had only its primary key (drop_id, id), while GET /claims/:address filters it by address. Every claims request was a sequential scan of the whole table: 17,378 calls at 1,180 ms and 269k disk blocks each since 2026-08-25 — 35 TB of physical reads from a 2.6 GB table at a 20% cache-hit ratio, the largest source of I/O on the instance and the reason other hot tables were being evicted from the buffer cache. Adds generated_drop_proof_address_idx (address) and generated_drop_proof_drop_id_amount_idx (drop_id, amount) (the per-drop totals aggregate becomes an index-only scan), and runs ANALYZE on the table, whose column statistics had never been collected.

Deploy: CREATE INDEX takes SHARE on a table only the out-of-band drop generator writes, so it does not interact with the indexer workers and does not use the LOCK TABLE blocks pattern. Expect tens of seconds for the build. After: the WITH funded_roots … statement in pg_stat_statements should drop from ~1.2 s to milliseconds, and pg_statio_user_tables should show the table's heap_blks_read flat.

2026-09-05: Indexable last_event_id for the pool-state poll

00123_pool_last_event_id. Schema change; no consumer changes.

quoter-service polls all_pool_states_view every 5 s per chain with last_event_id > $3. That column was GREATEST() over five state tables, so the predicate could only be a post-join filter and every poll walked every pool on the chain through the view's ~10 side-table joins — 96.8 ms and ~112k buffers per call, the largest single CPU consumer on the instance (5.9% of the box on its own in a 10-minute sample).

New table pool_last_event_id (pool_key_id, chain_id, core_address, last_event_id) stores that value. Each of pool_states, twamm_pool_states, boosted_fees_pool_states, limit_order_pool_states and ve33_pool_states gets two triggers: <table>_maintain_pool_last_event_id (AFTER INSERT OR DELETE) and <table>_maintain_pool_last_event_id_upd (AFTER UPDATE OF last_event_id, guarded by WHEN (OLD.last_event_id IS DISTINCT FROM NEW.last_event_id)). An upward move — every swap and position update — is a single primary-key update; inserts, deletes and downward moves recompute the value exactly from the five sources. It tracks those sources; whether they go down on a reorg is their own recompute functions' behaviour, unchanged from the live GREATEST, and the quoter relies on fork_counter for reorgs.

The view's last_event_id column now reads from the table through an inner join placed inside the first eight relations of the join list (the planner's join_collapse_limit; further down, the index could never drive the plan). Column list, order and types are unchanged — CREATE OR REPLACE VIEW — so neither quoter-service nor the API needs a change.

Operator notes. Repair one pool with SELECT recompute_pool_last_event_id(<pool_key_id>); resync everything with SELECT recompute_pool_last_event_id(pool_key_id) FROM pool_states — required after any bulk rewrite done with triggers disabled, since a missing row silently drops the pool from the view and a stale-low value stops the quoter refetching it. Post-deploy parity check: SELECT (SELECT count(*) FROM pool_states), (SELECT count(*) FROM pool_last_event_id), (SELECT count(*) FROM all_pool_states_view) — all three must match. Then watch the apsv poll in pg_stat_statements: mean should fall from ~97 ms to low single digits.

Deploy: CREATE TRIGGER takes SHARE ROW EXCLUSIVE on five tables the workers write mid-transaction; the migration locks blocks first (the #173 pattern) so it cannot deadlock. The swap is a single short transaction.

2026-09-04: Stop writing empty blocks; chain head moves to indexer_cursor

00122_indexer_cursor_head_block. Coordinated deploy across three repos — read this before rolling out.

The indexer no longer writes a blocks row for a block with no events, and the migration deletes the 202,040 empty rows already stored. 85% of blocks written were empty (8 of the 13 chains are 100% empty), and each one was inserted, kept a day, then deleted by delete_old_empty_blocks() — paying all 43 of the ON DELETE CASCADE lookups hanging off blocks despite never having had a child row. That was ~99M of the 184.5M cascade executions in a 10-day window. delete_old_empty_blocks(), its cron job, and blocks_num_events_block_time_idx are all dropped.

indexer_cursor gains four nullable columns — head_block_number (bigint), head_block_hash (numeric), head_block_time (timestamptz), head_base_fee_per_gas (numeric) — seeded from the current tip. The indexer maintains them in writeCursor, which already upserts that row once per block, so the head costs no extra write. New accessor public.get_chain_head_time(); incentives.compute_pending_reward_periods and public.get_oracle_twap_tick now use it instead of ORDER BY block_number DESC / MAX(block_time) over blocks.

Deploy order is load-bearing and there is no compatibility bridge. Nothing keeps the head columns current except the indexer build shipping with this migration, and nothing keeps a blocks-based tip query correct once that build stops writing empty blocks. The indexer, the API and quoter-service must go out together. A reader left on SELECT … FROM blocks ORDER BY block_number DESC LIMIT 1 does not error — it silently returns the last block that happened to carry an event, which on an all-empty chain is arbitrarily stale.

Consumers updated alongside:

repo	what changed
EkuboProtocol/api	getLatestBlock reads indexer_cursor; the timestamp and block-number lookups stay on blocks
EkuboProtocol/quoter-service	LATEST_BLOCK_QUERY reads indexer_cursor

base_fee_per_gas has no consumer inside the database — no view, matview or function references it — so quoter-service's tip query was its only reader.

Lookups that genuinely want history keep reading blocks: incentives.compute_rewards_for_period_v1 and the API's by-timestamp and by-number queries. They already tolerated empty blocks being absent, since the sweep had been removing day-old ones for as long as it existed; this makes that behaviour uniform rather than time-dependent.

The migration's DELETE FROM blocks WHERE num_events = 0 runs after 00121, which is what makes it affordable — the cascade lookups it fires are index scans rather than sequential scans of two ~100 MB tables.

2026-09-04: Incremental rewards by position; index the block cascade

Two schema changes, both aimed at the hourly cron spike on ekubo-db-nyc1. Measured from cron.job_run_details and pg_stat_statements on 2026-09-04.

00120_incremental_rewards_by_position. Schema change with a downstream contract. incentives.computed_rewards_by_position_materialized is no longer a materialized view. It is now a plain view over a new table, incentives.computed_rewards_by_position, which is maintained incrementally by triggers. The view exposes exactly the columns the matview did, so readers (including the API's position-rewards endpoint) need no change — but anything issuing REFRESH MATERIALIZED VIEW against that name will now fail, and the cron job refresh_computed_rewards_by_position is unscheduled.

REFRESH ... CONCURRENTLY has no change detection, so it re-aggregated all 40M rows of computed_rewards every hour — 3,488 s/day — to absorb the ~205 rows that actually land per day. Both aggregates are SUM, so they are now maintained forward instead: ~205 single-row upserts a day, and the numbers are exact at every instant rather than up to an hour stale.

New objects: table computed_rewards_by_position (adds source_row_count, not exposed through the view); rewards_by_position_apply(); triggers on computed_rewards (insert/update/delete), generated_drop_reward_periods (insert/delete) and campaigns (update of core_address); rebuild_rewards_by_position(); and verify_rewards_by_position().

Operator notes. The migration seeds the table with one full aggregate, which is the same ~145 s the hourly job used to take, inside the migration transaction — expect the deploy to sit there for that long.

Because the totals are now maintained rather than recomputed, drift is possible in a way it was not before. SELECT * FROM incentives.verify_rewards_by_position() returns the disagreeing groups and empty means correct; it costs about what the old refresh did, so run it out of band (daily or weekly), not on a read path. SELECT incentives.rebuild_rewards_by_position() repairs it from the base tables. A rebuild is required after anything the triggers deliberately do not cover — in particular moving a campaign_reward_period to a different campaign, which would re-key rows. Nothing does that today.

Bulk maintenance caution: the computed_rewards trigger is row-level, which is right for ~205 rows/day but wrong for a mass recompute. Recomputing every period would fire 40M triggers; disable the trigger and call rebuild_rewards_by_position() instead.

00121_index_block_cascade_children. Adds 13 (chain_id, block_number) indexes on the children of blocks that had none, three of which carry real data today: ve33_pool_fees_accounted, ve33_pool_emissions_accrued and ve33_rewards_claimed.

blocks has 43 ON DELETE CASCADE children, so each deleted block runs 43 cascade lookups; the unindexed ones sequentially scanned 100 MB+ tables. delete_old_empty_blocks() grew from 12.9 s to 250 s per hourly run between 2026-07-31 and 2026-09-04 as the new chain indexers raised the block-deletion rate and the ve33 tables grew. Those three tables accounted for 54,754 s of the 56,140 s of cascade CPU in a 10-day window.

Manual consideration: the indexes are built with plain CREATE INDEX, not CONCURRENTLY, because scripts/migrate.ts runs the migration set inside a single transaction and CREATE INDEX CONCURRENTLY cannot run in one. Building takes a SHARE lock that blocks writes to those tables — a few seconds each at current sizes (109 MB and 105 MB), but it will stall the ve33 indexer workers for the duration.

Either apply during a quiet period, or build the three large ones by hand first, which avoids the write stall entirely:

CREATE INDEX CONCURRENTLY ve33_pool_fees_accounted_chain_id_block_number_idx
    ON ve33_pool_fees_accounted (chain_id, block_number);
CREATE INDEX CONCURRENTLY ve33_pool_emissions_accrued_chain_id_block_number_idx
    ON ve33_pool_emissions_accrued (chain_id, block_number);
CREATE INDEX CONCURRENTLY ve33_rewards_claimed_chain_id_block_number_idx
    ON ve33_rewards_claimed (chain_id, block_number);

The migration's statements are IF NOT EXISTS and use exactly these names, so a hand-built index is adopted rather than rebuilt. Keep the names identical, and check for an INVALID index (\d+ on the table) if a concurrent build is interrupted — that one must be dropped and rebuilt, since IF NOT EXISTS will otherwise skip past a broken index.

2026-09-02: Mainnet-only networks; nine new EVM mainnets

The indexer no longer runs any testnet. Removed workers, .env.evm.* / .env.starknet.* files and package.json scripts for starknet-sepolia, eth-sepolia, base-sepolia, arb-sepolia and rhc-sepolia, and dropped the matching price-sync fetchers for chains 11155111, 421614 and 46630. Downstream consumers still reading rows for those chain IDs will see the data stop advancing; no rows are deleted by this change, so purging them is a separate manual step if wanted.

Added mainnet workers for Optimism, Gnosis, Unichain, World Chain, Ink, BNB Smart Chain and Polygon alongside the existing Ethereum, Base, Arbitrum, Robinhood, Monad and MegaETH. Each new worker takes its production RPC from https://<network>.g.alchemy.com/v2/${ALCHEMY_API_KEY} in .do/app.yaml, falling back to the chain's public endpoint where one exists; the committed .env.evm.* files keep key-free public URLs for local runs.

.do/app.yaml's &indexer-image anchor moved from starknet-sepolia to starknet-mainnet, since the service that defined it is gone.

Price sync now covers every EVM mainnet. Previously only Ethereum, Base, Monad, Robinhood, Arbitrum and Starknet had any price source, so the nine mainnets this release adds would have indexed pools with no USD prices at all. Each of the 13 EVM mainnets now has at least three:

chain	CoinGecko native	CoinGecko tokens	Sushi	Ekubo quoter
Ethereum (1)	ethereum	—	yes	USDC
Optimism (10)	ethereum	optimistic-ethereum	yes	USDC
BNB Smart Chain (56)	binancecoin	binance-smart-chain	yes	USDC (18dp)
Gnosis (100)	xdai	xdai	yes	USDC.e
Unichain (130)	ethereum	unichain	no	USDC
Polygon (137)	polygon-ecosystem-token	polygon-pos	yes	USDC
Monad (143)	monad	monad	yes	USDC
World Chain (480)	ethereum	world-chain	no	USDC
MegaETH (4326)	ethereum	megaeth	yes	—
Robinhood (4663)	ethereum	robinhood	yes	USDC
Base (8453)	ethereum	base	yes	USDC
Arbitrum (42161)	ethereum	arbitrum-one	yes	—
Ink (57073)	ethereum	ink	no	USDC

CoinGecko asset-platform slugs and native coin IDs are taken from CoinGecko's own /asset_platforms response rather than assumed. Sushi is wired only where api.sushi.com/price/v1/<chainId> actually answers: Unichain, World Chain and Ink return 404 and are left off. MegaETH has no listed USD stablecoin to quote against, so it gets no quoter job.

Quoter jobs query only tokens that already have a pool with non-zero TVL, so on a chain with no pools yet they issue no requests and cost nothing; they begin reporting on their own once liquidity arrives. They resolve through prod-api-quoter.ekubo.org/<chainId>, which is served by the matching quoter services in EkuboProtocol/quoter-service#41 — that PR should land first or these jobs will log failed lookups once pools exist.

Note BNB Smart Chain's bridged USDC is 18 decimals, not the usual 6. Every proxy token's symbol(), decimals() and totalSupply() were read on-chain. Gnosis uses USDC.e (0x2a22…76F0) rather than the older USDC (0xDDAf…7A83) because it carries about 11x the DEX liquidity.

2026-09-02: Reduce write amplification on hot tables

Three CPU fixes measured from pg_stat_statements on ekubo-db-nyc1 over 2026-08-25 to 2026-09-01.

Schema changes. Drops erc20_tokens_latest_price_valid_until_idx (added in 00116). Its only consumer is refresh_expired_erc20_token_latest_prices, which reads ~4,316 of the table's ~7,360 rows per scan and takes FOR UPDATE, so it was barely filtering while blocking HOT on all 47.9M updates the table takes per week. Nothing in the API filters on valid_until. Sets autovacuum_vacuum_scale_factor = 0.01 and a lowered fillfactor on pool_states, pool_tvl and erc20_tokens_latest_price.

Cron change. refresh_computed_rewards_by_position moves from * */6 * * * to 5 * * * *. The old expression put */6 in the hour field and left the minute field wide, so the job fired every minute during hours 0, 6, 12 and 18 — 240 runs/day totalling 4h52m of database time. Reward periods land on hourly boundaries, so the new cadence is hourly rather than the six-hourly one the broken expression was reaching for; minute 5 leaves four minutes after compute_incentive_rewards (minute 1) for the boundary blocks to be indexed. 24 runs/day of ~100 s is ~40m/day.

Manual intervention required, BEFORE this migration is deployed. The migration drops erc20_tokens_latest_price_valid_until_idx, and refresh_expired_erc20_token_latest_prices (~1 call/sec) then has to find expired rows by sequential scan. That is only cheap once the heap matches its live rows. Measured on 2026-09-02 against the bloated 46,147-page heap:

	buffers	time
index scan (today)	50	0.14 ms
seq scan on bloated heap	46,147 (45,155 read from disk)	103.6 ms
seq scan after repack (~250 pages)	~250	sub-ms

Repack first, then deploy. Deploying the migration against an unrepacked table makes that once-a-second query 750x more expensive.

# ~7,700 live rows in a 361 MB heap + 280 MB of indexes.
pg_repack -d defaultdb -t erc20_tokens_latest_price -t pool_states -t pool_tvl

pg_repack 1.5.2 is available on the managed instance and rewrites without an exclusive lock. VACUUM (FULL, ANALYZE) on the same three tables is equivalent and takes seconds, but holds ACCESS EXCLUSIVE and will stall the indexer and price-sync for the duration.

Note also that fillfactor only applies to pages written after a rewrite, and that lowering autovacuum_vacuum_scale_factor lets autovacuum reclaim space in place but never shrinks an already-bloated heap. scripts/migrate.ts runs each migration inside a transaction, so the rewrite cannot live in the migration — hence the manual step.

Downstream consumers are unaffected: no column, view or function signature changes, so the order in which the indexer components roll is irrelevant — the only ordering that matters is repack before migrate.

2026-08-09: Freshness-aware prioritized token prices

Per-source confidence now lives in erc20_token_price_sources, seeded by the migration and never written by the worker, with the quoter ranked highest. erc20_tokens_usd_prices gains a nullable valid_until that each observation carries (legacy rows are treated as valid for five minutes past their timestamp). The compact erc20_tokens_latest_price_by_source cache tracks those expirations, while the physical, primary-keyed erc20_tokens_latest_price table stores the fresh maximum-confidence aggregate for fast quoter reads. The price worker reconciles expirations once per second, promoting a lower-confidence source when needed. The all_pool_states_view definition remains unchanged. Apply migrations before deploying the updated price-sync worker. Consumers selecting every column from erc20_tokens_latest_price must account for its new confidence and valid_until columns and the synthetic AVG source on tied values; consumers of erc20_tokens_usd_prices gain a nullable column. No manual backfill is required.

This release also adds the cl1 Chainlink price source, which reads token/USD reference feeds over EVM RPC and is seeded above the aggregator APIs (the quoter ranks highest). It is inert until CHAINLINK_TOKEN_PRICE_SYNC_INTERVAL_SECONDS and CHAINLINK_TOKEN_PRICE_CONFIG are set, so no manual intervention is required to deploy without it.

2026-08-05: Pool-key discovery indexes

Adds two indexes on pool_keys — (chain_id, token1) and (chain_id, pool_extension) — so the API's new /poolKeys discovery route can filter by a single token (either side) or by extension without scanning a chain's whole pool set. Schema-only change: run the migration; no manual backfill is required and no existing structure changes.

2026-07-31: Token circulating supply

erc20_tokens now has a nullable, non-negative integer circulating_supply column. Like total_supply, the value is stored in the token's indivisible units so consumers must divide by 10 ^ token_decimals before multiplying by a per-token USD price. Apply this migration before deploying the default-tokens database sync that writes the new field. Existing rows remain NULL until that sync finds a supply source; no manual backfill is required.

2026-07-31: Ve33 fee component added to fee stats

hourly_volume_by_token now tracks the Ve33 portion of its total fees in a dedicated ve33_fees column. The 24-hour pool stats views expose ve33_fees0_24h and ve33_fees1_24h as components of the existing inclusive fees0_24h and fees1_24h totals. The migration backfills the breakdown from indexed PoolFeesAccounted events and keeps both totals reorg-safe. Apply the indexer migration before deploying API code that selects the new columns; no manual backfill is required.

2026-07-31: Token price history covering index

erc20_tokens_usd_prices now has a covering index on (chain_id, token_address, timestamp DESC) that includes the price value and source. Apply migrations before deploying the token price-history API to keep its bounded chart queries index-only. No backfill or manual intervention is required beyond running the migration.

2026-07-31: Reorg-safe per-tick liquidity aggregation

The per_pool_per_tick_liquidity triggers now retain transient rows until both the net liquidity delta and total liquidity are zero. This prevents order-dependent corruption when position updates are cascade-deleted during a reorg. The migration atomically rebuilds every tick aggregate from canonical position_updates; no manual backfill is required beyond running the migration.

2026-07-30: Pool token-pair lookup index

pool_keys now has an index on (chain_id, token0, token1) so API queries can find every configuration for a token pair without scanning all pool keys. Apply migrations before deploying the optimized pair-events API query. No backfill or manual intervention is required beyond running the migration.

2026-07-30: Ve33 fees included in hourly pool stats

EVM V3 PoolFeesAccounted events now contribute to hourly_volume_by_token.fees, which feeds the API's fee totals and APRs. The migration backfills existing Ve33 fee events and keeps the hourly aggregates correct when events are inserted or removed during a reorg. These events now require a pool_key_id, so an unresolved pool fails indexing instead of being silently omitted from fee stats. Apply migrations before deploying the updated EVM indexer; no manual backfill is expected.

2026-07-28: Token metadata automation moved to default-tokens

The DigitalOcean sync-tokens scheduled job and scripts/sync-tokens.ts were removed. Before deploying this indexer version, configure the EkuboProtocol/default-tokens update and database-sync workflow secrets, run the token-list update once, and run the separate database sync once. Future token sources, generated metadata, provenance, bridge mappings, and hosted logos are audited in that repository.

2026-07-16: Ve33 voted swap fee indexing

EVM V3 VoteWeightApplied events now store the stake's selected fee in ve33_vote_weight_applied.voted_swap_fee. The migration backfills existing rows to 0, then removes the column default so new rows must provide the value. Apply migrations before deploying the updated EVM indexer; no manual backfill is required.

2026-06-29: Ve33 event indexing

EVM V3 Ve33 events now write to ve33_stake_changed, ve33_vote_weight_applied, ve33_pool_fees_accounted, ve33_pool_fees_claimed, ve33_emissions_scheduled, ve33_pool_emissions_accrued, and ve33_rewards_claimed. Robinhood Chain also indexes VeToken and FreeVe33Positions ERC721 transfers when VE_TOKEN_V3_ADDRESS and VE33_POSITIONS_V3_ADDRESS are configured. Apply migrations before deploying consumers that read these tables.

2026-06-29: Ve33 pool state view support

Ve33 pool quote state is now maintained in ve33_pool_states and exposed through all_pool_states_view via ve33_* columns plus is_ve33_pool. Apply migrations before deploying consumers that select from the view; no manual backfill is required beyond the migration.

2026-02-23: all_pool_states_view now includes unsupported extensions

all_pool_states_view no longer filters rows by supported pool extension state markers, so any pool with a pool_states row now appears in the view. Downstream consumers that assumed the view contained only quoter-supported pools should add their own filtering before deploy; no backfill or manual intervention is required beyond running migrations.

2026-03-23: all_pool_states_view adds pool_tvl_usd

all_pool_states_view now includes pool_tvl_usd, computed from pool_tvl plus erc20_tokens_latest_price for both pool tokens. The column is NULL when either side lacks a latest USD price. Apply migrations before deploying any consumer that selects from this view.

2026-02-10: Auctions contract event indexing

EVM V3 auction events now write to auction_completed, auction_funds_added, auction_boost_started, and auction_creator_proceeds_collected.

2026-02-01: Boosted fees indexing and pool flags

Boosted fees now write to boosted_fees_events, boosted_fees_donate_rate_deltas, and boosted_fees_donated, while all_pool_states_view now exposes the boosted fee donate rates plus the last donated time and future deltas. Run migrations before deploying any consumers that read the view or expect boosted-fee schedules.

2026-01-28: Reorg detection fork counter on indexer_cursor

The indexer_cursor table now includes a fork_counter column that increments whenever the indexer deletes blocks during reorg handling. Downstream services can use it to detect reorgs even when the cursor position is unchanged. Run the migrations before deploying consumers that query indexer_cursor.

2026-01-05: Incentives campaigns scoped to core/licensee

Incentives campaigns now require a single core_address and support optional locker/licensee filters (allowed_lockers). Run the migrations before computing rewards, and refresh incentives.campaign_rewards_overview_materialized after deploy so the updated filtering is reflected in dashboards.

2025-12-14: Remove tvl_usd from all_pool_states_view

The tvl_usd column has been removed from all_pool_states_view to keep the view lightweight. Update any consumers that read this column before deploying, then run the standard migrations; no backfill or manual work is required.

2025-11-29: Pool config metadata for the EVM indexer

Pools now persist the raw PoolConfig word plus its decoded attributes. The pool_keys table gains pool_config, pool_config_type, stableswap_center_tick, and stableswap_amplification, and tick_spacing can be null for stableswap pools. all_pool_states_view also surfaces these new columns so downstream quoters can tell which pool type they are handling. Starknet pools continue to expose pool_config = NULL because their fee encoding is incompatible with the EVM packer. No manual work is required besides running the migrations, but any consumer that relied on tick_spacing always being non-null should be updated before ingesting stableswap data.

2025-11-27: Limit-order pools in all_pool_states_view

all_pool_states_view now joins limit_order_pool_states, exposes is_limit_order_pool, and allows pools with the limit-order extension to appear in the view. Apply migrations before deploying any component that reads this view; no manual backfills are required.

2025-11-18: TWAMM proceeds withdrawal bug

We had to reindex from the beginning due to a bug in inserting TWAMM proceeds withdrawal events. We also added some columns to the TWAMM order updates and TWAMM collect proceeds tables to improve correctness.