Nests · Graph Protocol
Graph Allocations + Curation
graph-allocations · Arbitrum
Horizon allocation lifecycle and L2 curation signal in one nest, with a `port_queue` view over both: subgraph deployments carrying signal that no indexer is serving.
statusbuilding
categoryGraph Protocol
tier0 · beachhead
complexitylow
discoveryfixed addresses
What it indexes
Each event becomes a decoded SQL table.
AllocationCreated, AllocationClosed, AllocationResized, Signalled, Burned
Needs an archive RPC: curation signal is cumulative, so L2Curation is indexed from its 2022 deployment. Full backfill measured at 12 minutes and 54 MB. Filter honestly - net signal above zero returns 3,853 of 7,621 still-signalled deployments, which is a haystack; above 1,000 it is 63. Release gate: reading the top of that list by hand, and confirming that no open allocation on SubgraphService really does mean unserved, since LegacyAllocationMigrated has fired zero times.
Run it yourself
The nest is a git repository: ABIs, views and a manifest. One command scaffolds a local copy, and
nuthatch dev in it backfills and follows the chain against your own RPC.
nuthatch init --from https://github.com/nuthatch-org/graph-allocations-nest Source: nuthatch-org/graph-allocations-nest.
Published data
We run this nest and publish its sealed segments to the public mirror, each stored under the hash of its contents. The figures are what the mirror listed on 2026-11-11, when this page was built; Play reads the mirror as it is now.
tables75
segments6,829
rows sealed6,334,881
sealed through block523,328,607
datasetd6fd43c87dd9593872b5c0f414b7291b230d3da7b2fcdf3ba2ad49e5ec496e8b
Authored views
The nest’s own SQL over its event tables, as published. Open one to read it.
deployment_signalviews/10-signal.sql
-- Net curation signal per subgraph deployment.
--
-- Signal is cumulative: shares are minted by `Signalled` and destroyed by `Burned`, so the current
-- holding is the difference and needs the full history since the L2 deployment. `tokens` is GRT paid
-- in; `signal` is the bonding-curve share minted for it. The two are not interchangeable - GRT
-- returned on a burn is not the GRT that went in - so the conserved quantity to filter on is signal,
-- and GRT is carried alongside as a sense of scale only.
CREATE VIEW deployment_signal AS
WITH minted AS (
SELECT "subgraphDeploymentID" AS deployment_id,
SUM(CAST(signal AS HUGEINT)) AS signal_minted,
SUM(CAST(tokens AS HUGEINT)) AS grt_signalled,
COUNT(DISTINCT curator) AS curators
FROM curation__signalled GROUP BY 1
),
burned AS (
SELECT "subgraphDeploymentID" AS deployment_id,
SUM(CAST(signal AS HUGEINT)) AS signal_burned
FROM curation__burned GROUP BY 1
)
SELECT m.deployment_id,
m.signal_minted - COALESCE(b.signal_burned, 0) AS net_signal,
m.grt_signalled,
m.curators
FROM minted m LEFT JOIN burned b ON b.deployment_id = m.deployment_id;open_allocationsviews/20-allocations.sql
-- Allocations currently open on the SubgraphService data service.
--
-- An allocation is open when it was created and never closed. `AllocationResized` changes the stake
-- on an existing allocation and neither opens nor closes one, so it is deliberately not consulted.
--
-- Caveat worth stating in the view rather than a README nobody opens: this sees only allocations on
-- `SubgraphService`. `LegacyAllocationMigrated` has fired zero times on this contract, so the
-- pre-Horizon migration path is not visible here. "Not open here" equals "unserved" only if every
-- live allocation now lives on SubgraphService.
CREATE VIEW open_allocations AS
SELECT "subgraphDeploymentId" AS deployment_id,
COUNT(*) AS open_allocations,
COUNT(DISTINCT indexer) AS indexers
FROM subgraph_service__allocation_created
WHERE "allocationId" NOT IN (SELECT "allocationId" FROM subgraph_service__allocation_closed)
GROUP BY 1;port_queueviews/30-port-queue.sql
-- The port queue: deployments carrying live curation signal that no indexer is serving.
--
-- Someone has GRT staked on this data and nobody is producing it - a party with a demonstrated
-- financial interest in a dataset they are not receiving. That is a far better queue than
-- "deployments with no allocation", most of which are dead test deployments nobody has revisited.
--
-- No threshold is applied here; the view is the honest population and the caller filters. Measured
-- 2026-08-19 on a full backfill: 13,881 deployments ever signalled, 7,621 still carrying signal,
-- 6,781 with an open allocation, 3,853 signalled-and-unserved. That last number is a haystack. At
-- net_signal > 1,000 it becomes 63, which is a list a person can read. Filter accordingly.
CREATE VIEW port_queue AS
SELECT s.deployment_id,
CAST(s.net_signal / 1000000000000000000 AS BIGINT) AS net_signal,
CAST(s.grt_signalled / 1000000000000000000 AS BIGINT) AS grt_signalled,
s.curators
FROM deployment_signal s
LEFT JOIN open_allocations o ON o.deployment_id = s.deployment_id
WHERE COALESCE(o.open_allocations, 0) = 0
AND s.net_signal > 0
ORDER BY s.net_signal DESC;lodestar_allocationsviews/40-lodestar-allocations.sql
-- Lodestar's `SubgraphAllocation` shape, served from events instead of the gateway (RFC-0011).
--
-- Maps one-for-one onto the interface at `lodestar/src/lib/ingest/allocations.ts:7`, so the route can
-- read this and stop calling `subgraphQuery`. Twelve of its thirteen fields come from two contracts;
-- the thirteenth is noted below and is the only thing this view guesses at, which it refuses to do.
CREATE VIEW lodestar_allocations AS
WITH created AS (
SELECT "allocationId" AS id,
indexer,
"subgraphDeploymentId" AS subgraph_deployment,
CAST(tokens AS HUGEINT) AS allocated_tokens,
CAST("currentEpoch" AS HUGEINT) AS created_at_epoch,
block_timestamp AS created_at,
block_number AS created_at_block
FROM subgraph_service__allocation_created
),
-- `AllocationResized` changes the stake without opening or closing anything, so the current size is
-- the newest resize if there is one and the creation amount otherwise.
resized AS (
SELECT "allocationId" AS id,
CAST("newTokens" AS HUGEINT) AS allocated_tokens,
ROW_NUMBER() OVER (PARTITION BY "allocationId" ORDER BY block_number DESC, log_index DESC) AS rn
FROM subgraph_service__allocation_resized
),
closed AS (
SELECT "allocationId" AS id,
block_timestamp AS closed_at,
block_number AS closed_at_block,
-- decoded as the text 'true'/'false'; Boolean('false') is true in JavaScript, which is how every
-- closed allocation read forceClosed on the nest path. Cast so the JSON carries a real boolean.
CAST("forceClosed" AS BOOLEAN) AS force_closed
FROM subgraph_service__allocation_closed
),
-- The POI lives here, not on `AllocationClosed` - which is where the subgraph's shape implies it
-- should be. Rewards are collected on close, so this is also the closest thing to a closing epoch.
-- Both eras. Legacy allocations were paid by `RewardsAssigned` (no POI on the event, no delegator
-- split); Horizon ones by `IndexingRewardsCollected`. The network's all-time rewards read 196M against
-- the gateway's 837M with the legacy era missing; with it, 837,303,089 GRT exact.
-- Horizon collects rewards on every POI, not once at close: one closed allocation had two collections
-- and the newest alone read 0.48 GRT against the gateway's 639.5. Amounts sum over the collections; the
-- POI and the epoch are the newest's.
rewards AS (
SELECT id, poi, rewards_epoch,
SUM(indexing_rewards) OVER (PARTITION BY id) AS indexing_rewards,
SUM(indexing_delegator_rewards) OVER (PARTITION BY id) AS indexing_delegator_rewards,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY block_number DESC, log_index DESC) AS rn
FROM (
SELECT "allocationId" AS id, poi, CAST("tokensRewards" AS HUGEINT) AS indexing_rewards,
CAST("tokensDelegationRewards" AS HUGEINT) AS indexing_delegator_rewards, CAST("currentEpoch" AS HUGEINT) AS rewards_epoch,
block_number, log_index
FROM subgraph_service__indexing_rewards_collected
UNION ALL
SELECT "allocationID", NULL, CAST(amount AS HUGEINT), 0, CAST(epoch AS HUGEINT), block_number, log_index
FROM rewards__rewards_assigned
)
),
-- The subgraph's `queryFeesCollected` is the indexer's net: after the curators' share and the 1%
-- protocol cut, the cut truncated per event. Gross read 12.2% high on every deployment's 30-day fees.
-- Legacy: `RebateCollected.queryFees` (already net), and the pre-rebate `AllocationCollected` less its
-- curation fees.
fees AS (
SELECT id, SUM(t) AS query_fees_collected FROM (
SELECT "allocationId" AS id, CAST("tokensCollected" AS HUGEINT) - CAST("tokensCurators" AS HUGEINT) - (CAST("tokensCollected" AS HUGEINT) // 100) AS t FROM subgraph_service__query_fees_collected
UNION ALL SELECT "allocationID", CAST("queryFees" AS HUGEINT) FROM staking_legacy__rebate_collected
UNION ALL SELECT "allocationID", CAST(tokens AS HUGEINT) - CAST("curationFees" AS HUGEINT) FROM staking_legacy__allocation_collected
) GROUP BY 1
),
-- The subgraph's `subgraphDeployment.signalledTokens`, exactly: what a curator paid in **net of
-- the curation tax** (the tax is burned, it never reaches the pool), less what burns returned, plus
-- query fees `Collected` into the pool. The first version of this fold summed gross deposits and
-- knew nothing of `Collected`; measured against the subgraph on five deployments it was wrong on
-- four, and the residual was `curatorFeeRewards` to the wei on every one (nuthatch#1078).
signal AS (
SELECT dep, SUM(tok) AS signalled_tokens FROM (
SELECT "subgraphDeploymentID" AS dep, CAST(tokens AS HUGEINT) - CAST("curationTax" AS HUGEINT) AS tok FROM curation__signalled
UNION ALL
SELECT "subgraphDeploymentID" AS dep, -CAST(tokens AS HUGEINT) AS tok FROM curation__burned
UNION ALL
SELECT "subgraphDeploymentID" AS dep, CAST(tokens AS HUGEINT) AS tok FROM curation__collected
) GROUP BY 1
)
SELECT c.id,
c.indexer,
c.subgraph_deployment,
COALESCE(s.signalled_tokens, 0) AS signalled_tokens,
COALESCE(r.allocated_tokens, c.allocated_tokens) AS allocated_tokens,
c.created_at_epoch,
c.created_at,
cl.closed_at,
-- The one field nothing carries. `AllocationClosed` has no epoch, so this is the epoch the
-- rewards were collected in - which for a normal close is the same block. It is NULL rather
-- than a guess when an allocation closed without collecting rewards.
CASE WHEN cl.id IS NULL THEN NULL ELSE rw.rewards_epoch END AS closed_at_epoch,
rw.poi,
COALESCE(rw.indexing_rewards, 0) AS indexing_rewards,
-- the delegators' share of those rewards, the subgraph's `indexingDelegatorRewards` (nuthatch#1160)
COALESCE(rw.indexing_delegator_rewards, 0) AS indexing_delegator_rewards,
COALESCE(f.query_fees_collected, 0) AS query_fees_collected,
CASE WHEN cl.id IS NULL THEN 'Active' ELSE 'Closed' END AS status,
cl.force_closed,
c.created_at_block,
cl.closed_at_block
FROM created c
LEFT JOIN (SELECT * FROM resized WHERE rn = 1) r ON r.id = c.id
LEFT JOIN closed cl ON cl.id = c.id
LEFT JOIN (SELECT * FROM rewards WHERE rn = 1) rw ON rw.id = c.id
LEFT JOIN fees f ON f.id = c.id
LEFT JOIN signal s ON s.dep = c.subgraph_deployment;epoch_boundaries, lodestar_epochsviews/50-lodestar-epochs.sql
-- Lodestar's `SubgraphEpoch` shape (RFC-0011), served from events.
--
-- **The trap this view exists to avoid.** Inside an Arbitrum contract `block.number` is the **L1**
-- block, so EpochManager counts epochs in L1 blocks while a log's `block_number` is **L2**. Computing
-- epochs in L2 block space is wrong by a factor of ~48 (60,142 epochs where the network has 1,356).
--
-- So every block this nest holds is placed by its own `l1_block_number` (`[extract] l1_blocks`),
-- with EpochManager's arithmetic: within a length segment that began at L1 block `anchor` in epoch
-- `e0` with length `len`, `epoch(l1) = e0 + (l1 - anchor) // len`. The segments are the
-- `EpochLengthUpdate` events. The first (initialize) sets `anchor` to its own L1 block; each later one
-- re-anchors at `currentEpochBlock()`, the start of the epoch it fired in, which is the previous
-- anchor plus whole epochs of the previous length. On Arbitrum One that gives epoch 1 at L1
-- 16,083,151 with length 6,646, then epoch 92 at L1 16,687,937 with length 7,200, which are the
-- contract's `lastLengthUpdateEpoch()`, `lastLengthUpdateBlock()` and `epochLength()` at the tip.
--
-- An epoch's `start_block` is the first L2 block this nest holds in it, not the chain's first L2
-- block of the epoch: only log-bearing blocks carry an L1 number here. Bucketing by it is still
-- exact, because L1 numbers never decrease along L2, so every row of the nest falls in the epoch its
-- own block's L1 number says. `start_l1_block` and `end_l1_block` are the epoch's exact L1 range,
-- the numbers the network subgraph reports as `startBlock` and `endBlock`. See
-- nightswatchhq/nuthatch#1116 and #1882.
CREATE VIEW epoch_boundaries AS
WITH length_updates AS (
SELECT CAST(u.epoch AS HUGEINT) AS e0, CAST(u."epochLength" AS HUGEINT) AS len,
CAST(l.l1_block_number AS HUGEINT) AS l1, u.block_number * 100000 + u.log_index AS k
FROM epochs__epoch_length_update u
JOIN l1_blocks l ON l.block_number = u.block_number AND l.block_hash = u.block_hash
),
steps AS (
SELECT e0, len, l1, k, (LEAD(e0) OVER (ORDER BY k) - e0) * len AS step FROM length_updates
),
segments AS (
SELECT e0, len, k,
MIN(l1) OVER () + COALESCE(SUM(step) OVER (ORDER BY k ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS anchor
FROM steps
),
-- Two updates inside one epoch share an anchor, and the later one is in force: an empty range.
ranges AS (
SELECT e0, len, anchor, LEAD(anchor) OVER (ORDER BY k) AS next_anchor FROM segments
),
per_epoch AS (
SELECT r.e0 + (b.l1 - r.anchor) // r.len AS epoch,
r.anchor + (b.l1 - r.anchor) // r.len * r.len AS start_l1_block,
r.len,
MIN(b.block_number) AS first_seen, MAX(b.block_number) AS last_seen
FROM (SELECT block_number, CAST(l1_block_number AS HUGEINT) AS l1 FROM l1_blocks) b
JOIN ranges r ON b.l1 >= r.anchor AND (r.next_anchor IS NULL OR b.l1 < r.next_anchor)
GROUP BY 1, 2, 3
)
SELECT p.epoch,
CAST(p.first_seen AS BIGINT) AS start_block,
-- An epoch ends where the next one starts. The newest has no successor yet, so it runs to its
-- own last observation - open-ended rather than wrong.
CAST(COALESCE(LEAD(p.first_seen) OVER (ORDER BY p.epoch) - 1, p.last_seen) AS BIGINT) AS end_block,
-- for bucketing: the newest epoch takes everything after it too, or the current epoch's fees
-- and signal read 0 until the next `EpochRun` (nuthatch#1160)
CAST(COALESCE(LEAD(p.first_seen) OVER (ORDER BY p.epoch) - 1, 9223372036854775807) AS BIGINT) AS until_block,
CAST(p.last_seen AS BIGINT) AS last_seen,
-- The L2 blocks between the predecessor's last row and this epoch's first, where the chain's
-- own first block of the epoch lies. No row of this nest is in them, so nothing is misfiled.
CAST(COALESCE(p.first_seen - LAG(p.last_seen) OVER (ORDER BY p.epoch) - 1, 0) AS BIGINT) AS unobserved_gap_blocks,
CAST(p.start_l1_block AS BIGINT) AS start_l1_block,
-- A length change takes effect from the start of the epoch it fired in, so an epoch's own
-- segment length is its length, and this is the start of the next epoch less one.
CAST(p.start_l1_block + p.len - 1 AS BIGINT) AS end_l1_block,
'l1' AS boundary_source
FROM per_epoch p;
-- The per-epoch totals Lodestar wants. Rewards carry their own epoch; query fees do not, so they are
-- bucketed by block against the boundaries above.
CREATE VIEW lodestar_epochs AS
-- Legacy `RewardsAssigned` carries the epoch and the whole amount; the delegators' share is the pool's
-- cut in force at that block (contract rounding, `amount - amount * cut // 1e6`), and nothing when
-- the pool had no shares - the same model `lodestar_indexer_ledger` measured exact against the
-- contracts. Horizon's `IndexingRewardsCollected` states the split itself.
WITH legacy_cuts AS (
SELECT LOWER(indexer) AS sp, CAST("indexingRewardCut" AS HUGEINT) AS cut, block_number * 100000 + log_index AS k FROM staking_legacy__delegation_parameters_updated
),
legacy_shares AS (
SELECT sp, k, SUM(sh) OVER (PARTITION BY sp ORDER BY k ROWS UNBOUNDED PRECEDING) AS cum_shares FROM (
SELECT LOWER(indexer) AS sp, block_number * 100000 + log_index AS k, CAST(shares AS HUGEINT) AS sh FROM staking_legacy__stake_delegated
UNION ALL SELECT LOWER(indexer), block_number * 100000 + log_index, -CAST(shares AS HUGEINT) FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER("serviceProvider"), block_number * 100000 + log_index, CAST(shares AS HUGEINT) FROM staking__tokens_delegated
UNION ALL SELECT LOWER("serviceProvider"), block_number * 100000 + log_index, -CAST(shares AS HUGEINT) FROM staking__tokens_undelegated
)
),
legacy_rewards AS (
-- Each reward with the delegation parameters and pool shares in force at it: the newest
-- `legacy_cuts` and `legacy_shares` rows at or before its key. That is an `ASOF LEFT JOIN`, which
-- only DuckDB spells; here the newest key of each is carried forward over the three streams in key
-- order (a change at the same key as a reward sorts first and applies, as `>=` did), and the reward
-- then joins each on that exact key. `k` is unique per event, so the join adds no rows.
SELECT r.epoch, r.amount,
CASE WHEN c.cut IS NOT NULL AND COALESCE(ps.cum_shares, 0) > 0 THEN r.amount - r.amount * c.cut // 1000000 ELSE 0 END AS delegator_share
FROM (
SELECT sp, epoch, amount, ck, pk FROM (
SELECT sp, epoch, amount, src,
MAX(CASE WHEN src = 0 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS ck,
MAX(CASE WHEN src = 1 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS pk
FROM (
SELECT sp, k, 0 AS src, CAST(NULL AS HUGEINT) AS epoch, CAST(NULL AS HUGEINT) AS amount FROM legacy_cuts
UNION ALL SELECT sp, k, 1, NULL, NULL FROM legacy_shares
UNION ALL SELECT LOWER(indexer), block_number * 100000 + log_index, 2, CAST(epoch AS HUGEINT), CAST(amount AS HUGEINT) FROM rewards__rewards_assigned
)
) WHERE src = 2
) r
LEFT JOIN legacy_cuts c ON c.sp = r.sp AND c.k = r.ck
LEFT JOIN legacy_shares ps ON ps.sp = r.sp AND ps.k = r.pk
),
rewards AS (
SELECT epoch, SUM(total) AS total_rewards, SUM(indexer) AS total_indexer_rewards, SUM(delegator) AS total_delegator_rewards FROM (
SELECT CAST("currentEpoch" AS HUGEINT) AS epoch, CAST("tokensRewards" AS HUGEINT) AS total,
CAST("tokensIndexerRewards" AS HUGEINT) AS indexer, CAST("tokensDelegationRewards" AS HUGEINT) AS delegator
FROM subgraph_service__indexing_rewards_collected
UNION ALL SELECT epoch, amount, amount - delegator_share, delegator_share FROM legacy_rewards
) GROUP BY 1
),
-- `queryFeesCollected` in the network subgraph is **net**, not gross: the curator share and a 1%
-- protocol tax are both taken out, and the tax is truncated **per event** rather than on the epoch
-- total. Summing first and taxing the total is wrong by a few hundred wei per epoch, which is small
-- enough to look like rounding noise and is in fact a different quantity. Integer division is
-- required: DuckDB's `/` returns a DOUBLE and loses precision outright at 1e23.
-- Measured over the 175 closed epochs from 1195 up, this took exact agreement from 0 to 145.
-- Both eras: legacy `RebateCollected.queryFees` is already the indexer's net and `curationFees` the
-- curators'; the pre-rebate `AllocationCollected` likewise less its `curationFees`.
fees AS (
SELECT b.epoch, SUM(q.net) AS query_fees_collected, SUM(q.curators) AS curator_query_fees
FROM (
SELECT block_number, CAST("tokensCollected" AS HUGEINT) - CAST("tokensCurators" AS HUGEINT) - (CAST("tokensCollected" AS HUGEINT) // 100) AS net, CAST("tokensCurators" AS HUGEINT) AS curators FROM subgraph_service__query_fees_collected
UNION ALL SELECT block_number, CAST("queryFees" AS HUGEINT), CAST("curationFees" AS HUGEINT) FROM staking_legacy__rebate_collected
UNION ALL SELECT block_number, CAST(tokens AS HUGEINT) - CAST("curationFees" AS HUGEINT), CAST("curationFees" AS HUGEINT) FROM staking_legacy__allocation_collected
) q
JOIN epoch_boundaries b
ON q.block_number >= b.start_block AND q.block_number <= b.until_block
GROUP BY 1
),
-- `signalledTokens` is **gross signal net of the curation tax**, and burns are not subtracted from
-- it at all. This previously computed `signalled - burned` and ignored `curationTax`, wrong in two
-- directions at once. The tell was that 81 of 266 epochs came out **negative**: a net flow was being
-- compared against something that is not a flow, and a negative token quantity is impossible as the
-- stock the subgraph is reporting. Measured, exact agreement went from 6 of 175 to 165 of 175, and
-- the ten that remain are five adjacent pairs of equal and opposite magnitude - value filed one
-- epoch out by the observed boundaries this view had before `l1_blocks` (#1116), not value lost.
signal AS (
SELECT b.epoch,
SUM(CAST(s.tokens AS HUGEINT) - CAST(s."curationTax" AS HUGEINT)) AS signalled_tokens
FROM curation__signalled s
JOIN epoch_boundaries b ON s.block_number >= b.start_block AND s.block_number <= b.until_block
GROUP BY 1
),
-- The subgraph's `Epoch.stakeDeposited`: own stake deposited during the epoch, both eras' events
-- (nuthatch#1160, for `api/epochs`). And the protocol's cut of the epoch's query fees, the
-- `tokensCollected // 100` the `fees` CTE already subtracts, exposed for `api/token-metrics`'
-- `taxedQueryFees`.
deposits AS (
SELECT b.epoch, SUM(t) AS stake_deposited FROM (
SELECT block_number, CAST(tokens AS HUGEINT) AS t FROM staking_legacy__stake_deposited
UNION ALL SELECT block_number, CAST(tokens AS HUGEINT) FROM staking__horizon_stake_deposited
) d JOIN epoch_boundaries b ON d.block_number >= b.start_block AND d.block_number <= b.until_block
GROUP BY 1
),
protocol_tax AS (
SELECT b.epoch, SUM(q.tax) AS taxed_query_fees
FROM (
SELECT block_number, CAST("tokensCollected" AS HUGEINT) // 100 AS tax FROM subgraph_service__query_fees_collected
UNION ALL SELECT block_number, CAST("protocolTax" AS HUGEINT) FROM staking_legacy__rebate_collected
) q
JOIN epoch_boundaries b ON q.block_number >= b.start_block AND q.block_number <= b.until_block
GROUP BY 1
)
SELECT b.epoch AS id,
b.start_block,
b.end_block,
b.start_l1_block,
b.end_l1_block,
COALESCE(s.signalled_tokens, 0) AS signalled_tokens,
COALESCE(d.stake_deposited, 0) AS stake_deposited,
COALESCE(p.taxed_query_fees, 0) AS taxed_query_fees,
COALESCE(r.total_rewards, 0) AS total_rewards,
COALESCE(r.total_indexer_rewards, 0) AS total_indexer_rewards,
COALESCE(r.total_delegator_rewards, 0) AS total_delegator_rewards,
COALESCE(f.query_fees_collected, 0) AS query_fees_collected,
COALESCE(f.curator_query_fees, 0) AS curator_query_fees
FROM epoch_boundaries b
LEFT JOIN rewards r ON r.epoch = b.epoch
LEFT JOIN fees f ON f.epoch = b.epoch
LEFT JOIN signal s ON s.epoch = b.epoch
LEFT JOIN deposits d ON d.epoch = b.epoch
LEFT JOIN protocol_tax p ON p.epoch = b.epoch
ORDER BY b.epoch;lodestar_disputesviews/60-lodestar-disputes.sql
-- Lodestar's dispute surface (RFC-0011), served from events.
--
-- Three creation events for three dispute kinds, and four resolutions. A dispute with no resolution
-- row is undecided, which a LEFT JOIN says without needing a status column on the chain.
CREATE VIEW lodestar_disputes AS
WITH created AS (
SELECT "disputeId" AS id, indexer, fisherman, CAST(tokens AS HUGEINT) AS deposit,
'Indexing' AS kind, "allocationId" AS allocation_id, poi,
CAST("stakeSnapshot" AS HUGEINT) AS stake_snapshot,
block_number AS created_at_block, block_timestamp AS created_at
FROM disputes__indexing_dispute_created
UNION ALL
SELECT "disputeId", indexer, fisherman, CAST(tokens AS HUGEINT),
'Query', NULL, "subgraphDeploymentId",
CAST("stakeSnapshot" AS HUGEINT), block_number, block_timestamp
FROM disputes__query_dispute_created
),
resolved AS (
SELECT "disputeId" AS id, 'Accepted' AS status, CAST(tokens AS HUGEINT) AS resolution_tokens,
block_number AS resolved_at_block, block_timestamp AS resolved_at
FROM disputes__dispute_accepted
UNION ALL
SELECT "disputeId", 'Rejected', CAST(tokens AS HUGEINT), block_number, block_timestamp
FROM disputes__dispute_rejected
UNION ALL
SELECT "disputeId", 'Drawn', CAST(tokens AS HUGEINT), block_number, block_timestamp
FROM disputes__dispute_drawn
)
SELECT c.id, c.kind, c.indexer, c.fisherman, c.deposit, c.allocation_id, c.poi,
c.stake_snapshot, c.created_at_block, c.created_at,
COALESCE(r.status, 'Undecided') AS status,
r.resolution_tokens, r.resolved_at_block, r.resolved_at
FROM created c
LEFT JOIN resolved r ON r.id = c.id;lodestar_escrow_transactionsviews/70-lodestar-escrow.sql
-- Lodestar's `PaymentsTx` shape (RFC-0011), served from PaymentsEscrow events.
--
-- Five money movements in one stream, with `type` doing the work the subgraph's
-- `paymentsEscrowTransactions` entity does. `allocationId` is NULL throughout and that is correct
-- rather than missing: escrow moves are payer/receiver level, no escrow event carries an allocation,
-- and Lodestar's own interface already types it as nullable.
--
-- `id` is `tx_hash-log_index`, which is stable, unique, and reproducible by anyone re-indexing the
-- same range - unlike a synthetic counter, which would depend on insertion order.
CREATE VIEW lodestar_escrow_transactions AS
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR) AS id,
'Deposit' AS type,
payer, receiver, collector,
CAST(tokens AS HUGEINT) AS amount,
CAST(NULL AS VARCHAR) AS allocation_id,
block_timestamp AS timestamp,
block_number
FROM escrow__deposit
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR), 'Thaw',
payer, receiver, collector, CAST("tokens" AS HUGEINT), NULL, block_timestamp, block_number
FROM escrow__thaw
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR), 'CancelThaw',
payer, receiver, collector, CAST("tokensThawing" AS HUGEINT), NULL, block_timestamp, block_number
FROM escrow__cancel_thaw
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR), 'Withdraw',
payer, receiver, collector, CAST(tokens AS HUGEINT), NULL, block_timestamp, block_number
FROM escrow__withdraw
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR), 'EscrowCollected',
payer, receiver, collector, CAST(tokens AS HUGEINT), NULL, block_timestamp, block_number
FROM escrow__escrow_collected;lodestar_delegationsviews/80-lodestar-delegations.sql
-- Lodestar's delegation_events shape, from Horizon and pre-Horizon staking events.
--
-- Maps onto `lodestar/src/lib/ingest/delegations.ts` and the inline UNION already used by
-- `/api/delegation-events`. event_type uses the community subgraph's vocabulary
-- (delegation / undelegation / withdrawal) so a switch does not rewrite the consumer.
--
-- Horizon renamed every staking event. The four `staking__*` tables are the current names;
-- `staking_legacy__stake_delegated` and `stake_delegated_locked` are the same proxy before
-- the upgrade. Drop the legacy pair and 366M blocks vanish. StakeDelegatedLocked is an
-- undelegation (tokens locked, then withdrawn), not a withdrawal; withdrawal is the later
-- DelegatedTokensWithdrawn / StakeDelegatedWithdrawn pair.
--
-- `id` is tx_hash-log_index, stable and reproducible. `tokens` is wei as HUGEINT, matching
-- the escrow view; the cron currently divides by 1e18 in process.
CREATE VIEW lodestar_delegations AS
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR) AS id,
'delegation' AS event_type,
LOWER(delegator) AS delegator,
LOWER("serviceProvider") AS indexer,
CAST(tokens AS HUGEINT) AS tokens,
block_timestamp AS timestamp,
block_number
FROM staking__tokens_delegated
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR),
'undelegation',
LOWER(delegator),
LOWER("serviceProvider"),
CAST(tokens AS HUGEINT),
block_timestamp,
block_number
FROM staking__tokens_undelegated
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR),
'withdrawal',
LOWER(delegator),
LOWER("serviceProvider"),
CAST(tokens AS HUGEINT),
block_timestamp,
block_number
FROM staking__delegated_tokens_withdrawn
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR),
'withdrawal',
LOWER(delegator),
LOWER(indexer),
CAST(tokens AS HUGEINT),
block_timestamp,
block_number
FROM staking__stake_delegated_withdrawn
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR),
'delegation',
LOWER(delegator),
LOWER(indexer),
CAST(tokens AS HUGEINT),
block_timestamp,
block_number
FROM staking_legacy__stake_delegated
UNION ALL
SELECT tx_hash || '-' || CAST(log_index AS VARCHAR),
'undelegation',
LOWER(delegator),
LOWER(indexer),
CAST(tokens AS HUGEINT),
block_timestamp,
block_number
FROM staking_legacy__stake_delegated_locked;lodestar_network_paramsviews/85-lodestar-params.sql
-- The governance parameters and totals Lodestar's network page shows beside `lodestar_network`
-- (nuthatch#1160), from the four pinned samples in `nuthatch.toml` and the epoch manager's events.
--
-- One row. Each sample is the newest non-reverted read, decoded the way `lodestar_network` decodes
-- `total_supply`: the last 32 hex characters of the raw return word folded base-16 into a HUGEINT.
-- `epoch_length` is not sampled because `epochs__epoch_length_update` carries every change to the
-- block, so the newest event is the state. Three legacy parameters the subgraph still reports -
-- `thawingPeriod`, `maxAllocationEpochs`, `delegationTaxPercentage` - have no getter on the Horizon
-- proxy and no column here: a consumer that needs them is showing a number that stopped meaning
-- anything at the upgrade, and this view declines to invent one.
--
-- The two tax totals are for `api/grt-flow`, whose `minted`/`burned` on the gateway path are the
-- subgraph's all-mints and all-burns; `lodestar_network` carries the bridge halves and the issuance
-- half, and these are the burn halves that remain: the protocol's cut of query fees (1%, burned)
-- and the curation tax (1% of every signal, burned).
CREATE VIEW lodestar_network_params AS
WITH samples AS (
-- newest non-reverted sample per parameter: the last 32 hex characters of its return word
SELECT name, h, block_number FROM (
SELECT 'delegation_ratio' AS name, right(lower(CAST(result AS VARCHAR)), 32) AS h,
block_number, ROW_NUMBER() OVER (ORDER BY block_number DESC) AS rn
FROM delegation_ratio WHERE reverted = false
) WHERE rn = 1
UNION ALL
SELECT name, h, block_number FROM (
SELECT 'curation_tax_percentage' AS name, right(lower(CAST(result AS VARCHAR)), 32) AS h,
block_number, ROW_NUMBER() OVER (ORDER BY block_number DESC) AS rn
FROM curation_tax_percentage WHERE reverted = false
) WHERE rn = 1
UNION ALL
SELECT name, h, block_number FROM (
SELECT 'protocol_payment_cut' AS name, right(lower(CAST(result AS VARCHAR)), 32) AS h,
block_number, ROW_NUMBER() OVER (ORDER BY block_number DESC) AS rn
FROM protocol_payment_cut WHERE reverted = false
) WHERE rn = 1
UNION ALL
SELECT name, h, block_number FROM (
SELECT 'max_thawing_period' AS name, right(lower(CAST(result AS VARCHAR)), 32) AS h,
block_number, ROW_NUMBER() OVER (ORDER BY block_number DESC) AS rn
FROM max_thawing_period WHERE reverted = false
) WHERE rn = 1
),
dec AS (
-- The 32 hex characters as a HUGEINT, the number `list_reduce(..., acc * 16 + d)` folds them to,
-- read as two 64-bit halves (DuckDB casts `0x` text to UBIGINT, not HUGEINT), so an engine without
-- DuckDB's list comprehensions reads the same number. A top half from 2^63 overflows, as the fold did.
SELECT name,
CAST(CAST('0x' || substr(h, 1, 16) AS UBIGINT) AS HUGEINT) * CAST('18446744073709551616' AS HUGEINT)
+ CAST(CAST('0x' || substr(h, 17, 16) AS UBIGINT) AS HUGEINT) AS value,
block_number
FROM samples
),
epoch_len AS (
SELECT CAST("epochLength" AS HUGEINT) AS epoch_length, CAST(epoch AS HUGEINT) AS last_length_update_epoch,
block_number AS last_length_update_l2_block
FROM epochs__epoch_length_update ORDER BY block_number DESC, log_index DESC LIMIT 1
),
taxes AS (
SELECT (SELECT SUM(CAST("curationTax" AS HUGEINT)) FROM curation__signalled) AS total_curation_tax,
-- The protocol's cut of every query fee collected: `tokensCollected // 100` is how
-- `lodestar_epochs` already accounts for it, so the two agree by construction.
(SELECT SUM(CAST("tokensCollected" AS HUGEINT) // 100) FROM subgraph_service__query_fees_collected) AS total_protocol_tax
)
SELECT (SELECT value FROM dec WHERE name = 'delegation_ratio') AS delegation_ratio,
(SELECT block_number FROM dec WHERE name = 'delegation_ratio') AS delegation_ratio_at_block,
(SELECT value FROM dec WHERE name = 'curation_tax_percentage') AS curation_tax_percentage,
(SELECT value FROM dec WHERE name = 'protocol_payment_cut') AS protocol_payment_cut,
(SELECT value FROM dec WHERE name = 'max_thawing_period') AS max_thawing_period_seconds,
e.epoch_length,
e.last_length_update_epoch,
-- The contract's `lastLengthUpdateBlock()` is an L1 block: the start of the epoch the update fired in.
b.start_l1_block AS last_length_update_block,
e.last_length_update_l2_block,
t.total_curation_tax,
t.total_protocol_tax
FROM epoch_len e
CROSS JOIN taxes t
LEFT JOIN epoch_boundaries b ON b.epoch = e.last_length_update_epoch;lodestar_indexer_ledger, lodestar_indexers, lodestar_indexer_pool, lodestar_delegator_positions, lodestar_delegator_stakes, lodestar_delegators, lodestar_curator_signals, lodestar_curators, lodestar_provisionsviews/90-lodestar-indexers.sql
-- Lodestar's `Indexer`, `DelegatedStake`, `Curator` and `Provision` shapes, folded from events
-- (nuthatch#1160, the surfaces #1078 left: `api/indexers`, `api/indexer/[address]`,
-- `api/indexer-stake-history`, `api/rewards-history`, `api/curators`, `api/provisions`,
-- `api/portfolio`, `api/apr-provenance`, `lib/refresh.ts`, the indexer OpenGraph image).
--
-- Every modelling choice here is the one `80-lodestar-network.sql` already made and measured against
-- the gateway, applied per entity instead of summed: stake nets deposits, withdrawals and legacy
-- slashing; a delegation leaves the pool at the *exit* event (`StakeDelegatedLocked` before Horizon,
-- `TokensUndelegated` after), not at withdrawal; "active" is shares, not tokens. Where this view has
-- to say something the network view did not, the paragraph above the CTE says what and why, and the
-- parity run against the subgraph arbitrates rather than the reasoning.
--
-- Units: `*_tokens` and `*_shares` are wei as HUGEINT; cuts are parts per million as the contracts
-- emit them; addresses are lower-cased; `id` follows the subgraph (the address, or `delegator-indexer`).
-- ---------------------------------------------------------------------------------------------
-- The per-indexer ledger: one row per event that moved an indexer's own stake or its delegation
-- pool, with the block time, so a caller can state either figure *as of any moment* by summing rows
-- up to it (`api/indexer-stake-history` wants 27 weekly points; the subgraph answered those with
-- block-pinned queries). `lodestar_indexers` sums this same ledger, so the two cannot disagree.
--
-- `pool_delta` follows the model measured exact against `getDelegationPool` on every indexer: the
-- delegation events themselves, the explicit pool additions, the legacy-era delegator reward share
-- computed per `RewardsAssigned` from the cut in force (contract rounding, skipped when the pool was
-- empty), and the silent share on legacy allocations closed after the upgrade. Thawing is not in
-- `pool_delta`: summed over every indexer that is the subgraph's `totalDelegatedTokens` to the wei.
-- The subgraph's per-indexer `delegatedTokens` *does* include thawing (P2P: 158,046,448 GRT, equal to
-- `getDelegationPool().tokens`), so `thawing_delta` runs beside it - up at `TokensUndelegated`, down
-- at `DelegatedTokensWithdrawn` - and an as-of caller adds the two for that figure.
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_indexer_ledger AS
WITH horizon_start AS (SELECT MIN(block_number) AS bn FROM staking__horizon_stake_deposited),
pool_shares_series AS (
SELECT sp, k, SUM(sh) OVER (PARTITION BY sp ORDER BY k ROWS UNBOUNDED PRECEDING) AS cum_shares FROM (
SELECT LOWER(indexer) AS sp, block_number * 100000 + log_index AS k, CAST(shares AS HUGEINT) AS sh FROM staking_legacy__stake_delegated
UNION ALL SELECT LOWER(indexer), block_number * 100000 + log_index, -CAST(shares AS HUGEINT) FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER("serviceProvider"), block_number * 100000 + log_index, CAST(shares AS HUGEINT) FROM staking__tokens_delegated
UNION ALL SELECT LOWER("serviceProvider"), block_number * 100000 + log_index, -CAST(shares AS HUGEINT) FROM staking__tokens_undelegated
)
),
cuts AS (SELECT LOWER(indexer) AS sp, CAST("indexingRewardCut" AS HUGEINT) AS cut, block_number * 100000 + log_index AS k FROM staking_legacy__delegation_parameters_updated),
legacy_reward_share AS (
-- The cut and the pool shares in force at each reward: the newest `cuts` and `pool_shares_series`
-- rows at or before its key. That was `ASOF JOIN` (a reward before any cut drops, as the inner
-- join did) and `ASOF LEFT JOIN`, which only DuckDB spells; here each newest key is carried
-- forward over the three streams in key order, a change at the reward's own key sorting first as
-- `>=` had it, and the reward joins on that exact key. `k` is unique per event.
SELECT r.sp, r.ts, r.bn, r.k, r.amount - r.amount * c.cut // 1000000 AS t
FROM (
SELECT sp, k, amount, ts, bn, ck, pk FROM (
SELECT sp, k, amount, ts, bn, src,
MAX(CASE WHEN src = 0 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS ck,
MAX(CASE WHEN src = 1 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS pk
FROM (
SELECT sp, k, 0 AS src, CAST(NULL AS HUGEINT) AS amount, CAST(NULL AS BIGINT) AS ts, CAST(NULL AS UBIGINT) AS bn FROM cuts
UNION ALL SELECT sp, k, 1, CAST(NULL AS HUGEINT), CAST(NULL AS BIGINT), CAST(NULL AS UBIGINT) FROM pool_shares_series
UNION ALL SELECT LOWER(indexer), block_number * 100000 + log_index, 2, CAST(amount AS HUGEINT), CAST(block_timestamp AS BIGINT), block_number FROM rewards__rewards_assigned
)
) WHERE src = 2
) r
JOIN cuts c ON c.sp = r.sp AND c.k = r.ck
LEFT JOIN pool_shares_series ps ON ps.sp = r.sp AND ps.k = r.pk
WHERE r.bn < COALESCE((SELECT bn FROM horizon_start), 9223372036854775807) AND COALESCE(ps.cum_shares, 0) > 0
),
legacy_path_share AS (
-- As `legacy_reward_share`, for rewards on the Horizon path to legacy allocations.
SELECT h.sp, h.ts, h.bn, h.k, h.amount - h.amount * c.cut // 1000000 AS t
FROM (
SELECT sp, k, a, amount, ts, bn, ck, pk FROM (
SELECT sp, k, a, amount, ts, bn, src,
MAX(CASE WHEN src = 0 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS ck,
MAX(CASE WHEN src = 1 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS pk
FROM (
SELECT sp, k, 0 AS src, CAST(NULL AS VARCHAR) AS a, CAST(NULL AS HUGEINT) AS amount, CAST(NULL AS BIGINT) AS ts, CAST(NULL AS UBIGINT) AS bn FROM cuts
UNION ALL SELECT sp, k, 1, CAST(NULL AS VARCHAR), CAST(NULL AS HUGEINT), CAST(NULL AS BIGINT), CAST(NULL AS UBIGINT) FROM pool_shares_series
UNION ALL SELECT LOWER(indexer), block_number * 100000 + log_index, 2, LOWER("allocationID"), CAST(amount AS HUGEINT), CAST(block_timestamp AS BIGINT), block_number FROM rewards__horizon_rewards_assigned
)
) WHERE src = 2
) h
JOIN cuts c ON c.sp = h.sp AND c.k = h.ck
LEFT JOIN pool_shares_series ps ON ps.sp = h.sp AND ps.k = h.pk
WHERE h.a IN (SELECT LOWER("allocationID") FROM staking_legacy__allocation_created) AND COALESCE(ps.cum_shares, 0) > 0
)
SELECT LOWER(indexer) AS indexer, CAST(block_timestamp AS BIGINT) AS ts, block_number, block_number * 100000 + log_index AS k, 'stake_deposited' AS kind, CAST(tokens AS HUGEINT) AS stake_delta, CAST(0 AS HUGEINT) AS pool_delta, CAST(0 AS HUGEINT) AS shares_delta, CAST(0 AS HUGEINT) AS thawing_delta FROM staking_legacy__stake_deposited
UNION ALL SELECT LOWER(indexer), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'stake_withdrawn', -CAST(tokens AS HUGEINT), 0, 0, 0 FROM staking_legacy__stake_withdrawn
UNION ALL SELECT LOWER(indexer), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'stake_slashed', -CAST(tokens AS HUGEINT), 0, 0, 0 FROM staking_legacy__stake_slashed
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'stake_deposited', CAST(tokens AS HUGEINT), 0, 0, 0 FROM staking__horizon_stake_deposited
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'stake_withdrawn', -CAST(tokens AS HUGEINT), 0, 0, 0 FROM staking__horizon_stake_withdrawn
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'stake_slashed', -CAST(tokens AS HUGEINT), 0, 0, 0 FROM staking__provision_slashed
-- delegation pool
UNION ALL SELECT LOWER(indexer), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'delegated', 0, CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT), 0 FROM staking_legacy__stake_delegated
UNION ALL SELECT LOWER(indexer), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'undelegated', 0, -CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT), 0 FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'delegated', 0, CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT), 0 FROM staking__tokens_delegated
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'undelegated', 0, -CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT), CAST(tokens AS HUGEINT) FROM staking__tokens_undelegated
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'pool_added', 0, CAST(tokens AS HUGEINT), 0, 0 FROM staking__tokens_to_delegation_pool_added
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'pool_slashed', 0, -CAST(tokens AS HUGEINT), 0, 0 FROM staking__delegation_slashed
UNION ALL SELECT LOWER(indexer), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'rebate_delegation_rewards', 0, CAST("delegationRewards" AS HUGEINT), 0, 0 FROM staking_legacy__rebate_collected
UNION ALL SELECT LOWER(indexer), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'rebate_delegation_fees', 0, CAST("delegationFees" AS HUGEINT) , 0, 0 FROM staking_legacy__rebate_claimed
UNION ALL SELECT LOWER("serviceProvider"), CAST(block_timestamp AS BIGINT), block_number, block_number * 100000 + log_index, 'withdrawn', 0, 0, 0, -CAST(tokens AS HUGEINT) FROM staking__delegated_tokens_withdrawn
UNION ALL SELECT sp, ts, bn, k, 'legacy_reward_share', 0, t, 0, 0 FROM legacy_reward_share
UNION ALL SELECT sp, ts, bn, k, 'legacy_path_share', 0, t, 0, 0 FROM legacy_path_share;
-- ---------------------------------------------------------------------------------------------
-- Per-indexer stake, delegation pool, cuts, rewards, fees, registry and provisions.
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_indexers AS
WITH
-- Own stake and the delegation pool are sums over `lodestar_indexer_ledger`, so this view and any
-- as-of query over the ledger state the same figures. The ledger's header says what is in each.
-- **One pass over the ledger, not two.** These were two CTEs, `stake` and `pool_adjust`, each with
-- its own `GROUP BY` over `lodestar_indexer_ledger`. Nothing materialises a view, so that is the
-- whole ledger - sixteen unioned event streams and two ASOF joins over a windowed share series -
-- computed twice per request. Measured on the production nest, 2026-09-06: two folds 3.90 s, one
-- fold 2.13 s, for the identical answer. The `pool_adjust` half becomes a FILTER on the same
-- aggregate, which is what it always was.
stake AS (
SELECT indexer AS sp, SUM(stake_delta) AS staked_tokens,
MIN(ts) FILTER (WHERE stake_delta > 0) AS created_at,
MIN(block_number) FILTER (WHERE stake_delta > 0) AS created_at_block,
SUM(pool_delta) FILTER (WHERE kind NOT IN ('delegated', 'undelegated')) AS pool_rewards_added
FROM lodestar_indexer_ledger GROUP BY 1
),
-- Locked (thawing) own stake. Both `StakeLocked` and `HorizonStakeLocked` carry the *total* locked
-- amount and its release time, not a delta, so the newest event is the state - unless a withdrawal
-- came after it, which empties the lock. `thawing_period` on Horizon is per provision; this is the
-- legacy-shaped `lockedTokens` / `lockedUntil` the indexer pages show.
lock_events AS (
SELECT LOWER(indexer) AS sp, CAST(tokens AS HUGEINT) AS tokens, CAST("until" AS HUGEINT) AS until_block, block_number AS bn, log_index AS li FROM staking_legacy__stake_locked
UNION ALL SELECT LOWER("serviceProvider"), CAST(tokens AS HUGEINT), CAST("until" AS HUGEINT), block_number, log_index FROM staking__horizon_stake_locked
),
withdraw_events AS (
SELECT LOWER(indexer) AS sp, block_number AS bn, log_index AS li FROM staking_legacy__stake_withdrawn
UNION ALL SELECT LOWER("serviceProvider"), block_number, log_index FROM staking__horizon_stake_withdrawn
),
locked AS (
SELECT l.sp,
CASE WHEN w.bn IS NULL OR (l.bn, l.li) > (w.bn, w.li) THEN l.tokens ELSE 0 END AS locked_tokens,
CASE WHEN w.bn IS NULL OR (l.bn, l.li) > (w.bn, w.li) THEN l.until_block ELSE NULL END AS locked_until
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY sp ORDER BY bn DESC, li DESC) AS rn FROM lock_events) l
LEFT JOIN (SELECT sp, MAX(bn) AS bn, MAX(li) AS li FROM withdraw_events GROUP BY 1) w ON w.sp = l.sp
WHERE l.rn = 1
),
-- Delegation, per (delegator, indexer), exit at the exit event. Identical to the network view's
-- fold, kept per pair so `lodestar_delegator_stakes` below and the pool here cannot disagree.
delegation AS (
SELECT delegator, sp, SUM(tok) AS net_tokens, SUM(sh) AS net_shares FROM (
SELECT LOWER(delegator) AS delegator, LOWER(indexer) AS sp, CAST(tokens AS HUGEINT) AS tok, CAST(shares AS HUGEINT) AS sh FROM staking_legacy__stake_delegated
UNION ALL SELECT LOWER(delegator), LOWER(indexer), -CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT) FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER(delegator), LOWER("serviceProvider"), CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT) FROM staking__tokens_delegated
UNION ALL SELECT LOWER(delegator), LOWER("serviceProvider"), -CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT) FROM staking__tokens_undelegated
) GROUP BY 1, 2
),
pool AS (
SELECT sp,
SUM(net_tokens) AS delegated_net,
SUM(net_shares) AS delegator_shares,
COUNT(DISTINCT delegator) FILTER (WHERE net_shares > 0) AS delegator_count
FROM delegation GROUP BY 1
),
-- Undelegated and not yet withdrawn: the subgraph's `delegatedThawingTokens`, which is a Horizon
-- quantity (`pool.tokensThawing`) and is exit minus release over Horizon events only. Measured against
-- `getDelegationPool` on two indexers: 8,769 and 0 GRT, both exact. Legacy undelegations that were
-- never withdrawn are not thawing - they sit in the delegator's `tokensLocked` forever, 1.2M GRT on
-- one indexer - so they are carried in their own column and kept out of this one.
thawing AS (
SELECT sp, GREATEST(SUM(t), 0) AS delegated_thawing_tokens, GREATEST(SUM(l), 0) AS legacy_locked_unwithdrawn FROM (
SELECT LOWER("serviceProvider") AS sp, CAST(tokens AS HUGEINT) AS t, CAST(0 AS HUGEINT) AS l FROM staking__tokens_undelegated
UNION ALL SELECT LOWER("serviceProvider"), -CAST(tokens AS HUGEINT), 0 FROM staking__delegated_tokens_withdrawn
UNION ALL SELECT LOWER(indexer), 0, CAST(tokens AS HUGEINT) FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER(indexer), 0, -CAST(tokens AS HUGEINT) FROM staking__stake_delegated_withdrawn
) GROUP BY 1
),
-- Active allocations. The Horizon ones come from `lodestar_allocations` so the two surfaces share one
-- definition. A legacy allocation still open today is one that was created before the upgrade and
-- neither closed under either legacy `AllocationClosed` overload nor migrated into the subgraph
-- service (a migrated one is the service's to close, and is counted there).
legacy_open AS (
SELECT LOWER(indexer) AS sp, CAST(tokens AS HUGEINT) AS tokens
FROM staking_legacy__allocation_created c
WHERE c."allocationID" NOT IN (SELECT "allocationID" FROM staking_legacy__allocation_closed_7203)
AND c."allocationID" NOT IN (SELECT "allocationID" FROM staking_legacy__allocation_closed_f672)
AND c."allocationID" NOT IN (SELECT "allocationId" FROM subgraph_service__legacy_allocation_migrated)
),
allocated AS (
SELECT sp, SUM(tokens) AS allocated_tokens, COUNT(*) AS allocation_count FROM (
SELECT LOWER(indexer) AS sp, allocated_tokens AS tokens FROM lodestar_allocations WHERE status = 'Active'
UNION ALL SELECT sp, tokens FROM legacy_open
) GROUP BY 1
),
-- Delegation cuts, parts per million, newest of either era wins. Horizon sets them per payment type:
-- 0 = QueryFee, 1 = IndexingFee, 2 = IndexingRewards (`IGraphPayments.PaymentTypes`), and per
-- verifier; the subgraph reports the subgraph service's, and that is the only verifier that has set
-- any, so no filter is applied and the parity run will say if that stops being true.
cut_events AS (
SELECT LOWER(indexer) AS sp, 'indexing' AS kind, CAST("indexingRewardCut" AS HUGEINT) AS cut, block_number AS bn, log_index AS li, block_timestamp AS ts, 'legacy' AS era FROM staking_legacy__delegation_parameters_updated
UNION ALL SELECT LOWER(indexer), 'query', CAST("queryFeeCut" AS HUGEINT), block_number, log_index, block_timestamp, 'legacy' FROM staking_legacy__delegation_parameters_updated
-- Horizon's `feeCut` is the share that goes to DELEGATORS (HorizonStaking `_delegationFeeCut`); the
-- legacy cut, and the subgraph's field, is the share the indexer keeps. Measured on 8107: P2P's
-- queryFeeCut read 0 here against 1,000,000 on the gateway, Ellipfra's 570,000 against 430,000.
UNION ALL SELECT LOWER("serviceProvider"), CASE WHEN CAST("paymentType" AS INTEGER) = 2 THEN 'indexing' WHEN CAST("paymentType" AS INTEGER) = 0 THEN 'query' ELSE 'other' END,
1000000 - CAST("feeCut" AS HUGEINT), block_number, log_index, block_timestamp, 'horizon' FROM staking__delegation_fee_cut_set
),
-- An indexer with a provision is on Horizon, where the legacy parameters no longer apply: its cut is
-- the newest `DelegationFeeCutSet`, or "keeps everything" if none was set. 13 indexers carried a legacy
-- 800,000 or 750,000 here against the gateway's 1,000,000. Without a provision the legacy value stands.
cuts AS (
SELECT c.sp,
MAX(c.cut) FILTER (WHERE c.kind = 'indexing' AND c.rn = 1) AS indexing_reward_cut,
MAX(c.cut) FILTER (WHERE c.kind = 'query' AND c.rn = 1) AS query_fee_cut,
MAX(c.ts) AS last_delegation_parameter_update
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY sp, kind ORDER BY bn DESC, li DESC) AS rn FROM cut_events
WHERE era = 'horizon' OR sp NOT IN (SELECT LOWER("serviceProvider") FROM staking__provision_created)) c
GROUP BY 1
),
-- Indexing rewards assigned, both eras; the subgraph's `rewardsEarned` is the gross figure before the
-- delegators' share, which is what `RewardsAssigned.amount` is.
rewards AS (
SELECT sp, SUM(t) AS rewards_earned FROM (
SELECT LOWER(indexer) AS sp, CAST(amount AS HUGEINT) AS t FROM rewards__rewards_assigned
UNION ALL SELECT LOWER(indexer), CAST(amount AS HUGEINT) FROM rewards__horizon_rewards_assigned
) GROUP BY 1
),
-- Query fees. Three mechanisms across the protocol's life: `AllocationCollected` (oldest, gross of
-- curation), `RebateCollected.queryFees` (the rebate era), and the subgraph service's
-- `QueryFeesCollected.tokensCollected`. Summed; if the subgraph's `queryFeesCollected` turns out to
-- net curation fees off the first, the parity run will show a constant offset on old indexers.
fees AS (
SELECT sp, SUM(t) AS query_fees_collected FROM (
SELECT LOWER(indexer) AS sp, CAST(tokens AS HUGEINT) - CAST("curationFees" AS HUGEINT) AS t FROM staking_legacy__allocation_collected
UNION ALL SELECT LOWER(indexer), CAST("queryFees" AS HUGEINT) FROM staking_legacy__rebate_collected
-- net of the curators' share and the 1% protocol cut, the subgraph's figure (gross read 3.5% high)
UNION ALL SELECT LOWER("serviceProvider"), CAST("tokensCollected" AS HUGEINT) - CAST("tokensCurators" AS HUGEINT) - (CAST("tokensCollected" AS HUGEINT) // 100) FROM subgraph_service__query_fees_collected
) GROUP BY 1
),
-- Service registry: the newest of register / unregister per indexer is the state.
registry AS (
SELECT sp, CASE WHEN kind = 'reg' THEN url ELSE NULL END AS url,
CASE WHEN kind = 'reg' THEN geohash ELSE NULL END AS geohash
FROM (
SELECT LOWER(indexer) AS sp, 'reg' AS kind, url, geohash, block_number AS bn, log_index AS li,
ROW_NUMBER() OVER (PARTITION BY LOWER(indexer) ORDER BY block_number DESC, log_index DESC) AS rn
FROM (
SELECT indexer, url, geohash, block_number, log_index FROM service_registry__service_registered
UNION ALL SELECT indexer, NULL, NULL, block_number, log_index FROM service_registry__service_unregistered
-- Horizon indexers register with the SubgraphService, whose event carries abi.encode(string url,
-- string geohash, address rewardsDestination) as `data`: three head words, then each string as a
-- length word and padded bytes. 14 of 184 indexers had no legacy registration and read url null.
-- `geohash` is read from the same row one level up, where a LATERAL used to reach back for it.
UNION ALL SELECT sp, url, decode(from_hex(substr(h, 257 + ((ulen + 31) // 32) * 64 + 64, glen * 2))) AS geohash,
block_number, log_index FROM (
SELECT sp, block_number, log_index, ulen, h,
decode(from_hex(substr(h, 257, ulen * 2))) AS url,
CAST(('0x' || substr(h, 257 + ((ulen + 31) // 32) * 64, 64)) AS BIGINT) AS glen
FROM (SELECT "serviceProvider" AS sp, block_number, log_index, substr(data, 3) AS h,
CAST(('0x' || substr(substr(data, 3), 193, 64)) AS BIGINT) AS ulen
FROM subgraph_service__service_provider_registered)
) x
)
) WHERE rn = 1
),
-- Provisioned stake across every verifier; `lodestar_provisions` below has it per verifier.
provisioned AS (
SELECT sp, SUM(t) AS provisioned_tokens FROM (
SELECT LOWER("serviceProvider") AS sp, CAST(tokens AS HUGEINT) AS t FROM staking__provision_created
UNION ALL SELECT LOWER("serviceProvider"), CAST(tokens AS HUGEINT) FROM staking__provision_increased
UNION ALL SELECT LOWER("serviceProvider"), -CAST(tokens AS HUGEINT) FROM staking__tokens_deprovisioned
UNION ALL SELECT LOWER("serviceProvider"), -CAST(tokens AS HUGEINT) FROM staking__provision_slashed
) GROUP BY 1
)
SELECT s.sp AS id,
s.staked_tokens,
COALESCE(l.locked_tokens, 0) AS locked_tokens,
l.locked_until,
-- The subgraph's `delegatedTokens` is the whole pool, thawing included: it read 158,046,448 GRT for
-- P2P against `getDelegationPool().tokens` of exactly that, while the pool net of its 38,093,038 GRT
-- thawing was what this column held. `delegated_thawing_tokens` beside it is the thawing part.
COALESCE(p.delegated_net, 0) + COALESCE(s.pool_rewards_added, 0) + COALESCE(th.delegated_thawing_tokens, 0) AS delegated_tokens,
COALESCE(p.delegated_net, 0) AS delegated_net,
COALESCE(s.pool_rewards_added, 0) AS delegated_pool_rewards,
COALESCE(p.delegator_shares, 0) AS delegator_shares,
COALESCE(p.delegator_count, 0) AS delegator_count,
COALESCE(th.delegated_thawing_tokens, 0) AS delegated_thawing_tokens,
COALESCE(th.legacy_locked_unwithdrawn, 0) AS legacy_locked_unwithdrawn,
COALESCE(a.allocated_tokens, 0) AS allocated_tokens,
COALESCE(a.allocation_count, 0) AS allocation_count,
-- no cut ever set means the indexer keeps everything: Horizon's feeCut defaults to 0 for
-- delegators, which the subgraph reports as 1,000,000 (one indexer read 0 here against that)
COALESCE(c.indexing_reward_cut, 1000000) AS indexing_reward_cut,
COALESCE(c.query_fee_cut, 1000000) AS query_fee_cut,
c.last_delegation_parameter_update,
COALESCE(r.rewards_earned, 0) AS rewards_earned,
COALESCE(f.query_fees_collected, 0) AS query_fees_collected,
COALESCE(pr.provisioned_tokens, 0) AS provisioned_tokens,
g.url,
g.geohash,
s.created_at,
s.created_at_block
FROM stake s
LEFT JOIN locked l ON l.sp = s.sp
LEFT JOIN pool p ON p.sp = s.sp
LEFT JOIN thawing th ON th.sp = s.sp
LEFT JOIN allocated a ON a.sp = s.sp
LEFT JOIN cuts c ON c.sp = s.sp
LEFT JOIN rewards r ON r.sp = s.sp
LEFT JOIN fees f ON f.sp = s.sp
LEFT JOIN registry g ON g.sp = s.sp
LEFT JOIN provisioned pr ON pr.sp = s.sp;
-- ---------------------------------------------------------------------------------------------
-- Per (delegator, indexer): the subgraph's `DelegatedStake`, including its accounting.
--
-- Shares are exact and were measured against `getDelegation` (top 60, all equal). The token value
-- of a position is its share of the pool, `shares * pool_tokens / pool_shares`, as the contracts
-- value it. The rest follows `staking.ts` / `horizonStaking.ts` in the network subgraph:
-- * `personal_exchange_rate` is the delegator's weighted-average price per share, updated on every
-- delegation as (rate * shares + tokens_in) / (shares + shares_in), unchanged by undelegation;
-- * `realized_rewards` accrues on every undelegation as tokens_out - shares_out * rate, i.e. what
-- the position was worth at exit minus what it cost, and `TokensUndelegated.tokens` *is* the
-- exit value so no pool rate is needed.
-- That is a sequential fold per position, done with `list_reduce` over the position's events in
-- block order; the rate and realized rewards are DOUBLEs, which at 1e24 wei is a 1e-10 GRT error,
-- the subgraph's own BigDecimals are truncated to 18 places, and no on-chain oracle exists for either
-- (they are bookkeeping, not state). `locked_tokens` is undelegated and not yet withdrawn, both eras;
-- `locked_until` is the newest Horizon thaw request's `thawingUntil`, a Unix time, and 0 for a legacy
-- lock, whose 28-day thaw expired long ago and which is withdrawable now - the pages compare this
-- field to the clock, so a block number here would be wrong in a way that looks right.
-- ---------------------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------------------
-- The delegation pool per indexer: shares outstanding and tokens in it. Two columns, and they are
-- the only two `lodestar_delegator_stakes` wanted from `lodestar_indexers` - a view that folds the
-- whole ledger and returns forty. Measured on the production nest (2026-09-06): the full join costs
-- 4.20 s, these two columns cost 2.31 s, and `lodestar_delegator_stakes` was over the per-permit
-- DuckDB budget (244 MiB) computing the other thirty-eight to throw them away.
--
-- **The formula is the one `lodestar_indexers` uses, and it has to stay that way.** Both surfaces
-- report a pool; if they drift, an indexer page and a delegator page disagree about the same number.
-- `checks/pool-agrees.sql` fails if they ever differ - that check is the reason this duplication is
-- allowed to exist rather than a second source of truth.
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_indexer_pool AS
WITH delegation AS (
SELECT sp, SUM(tok) AS delegated_net, SUM(sh) AS delegator_shares FROM (
SELECT LOWER(indexer) AS sp, CAST(tokens AS HUGEINT) AS tok, CAST(shares AS HUGEINT) AS sh FROM staking_legacy__stake_delegated
UNION ALL SELECT LOWER(indexer), -CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT) FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER("serviceProvider"), CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT) FROM staking__tokens_delegated
UNION ALL SELECT LOWER("serviceProvider"), -CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT) FROM staking__tokens_undelegated
) GROUP BY 1
),
ledger AS (
SELECT indexer AS sp, SUM(pool_delta) FILTER (WHERE kind NOT IN ('delegated', 'undelegated')) AS pool_rewards_added
FROM lodestar_indexer_ledger GROUP BY 1
),
thawing AS (
SELECT sp, GREATEST(SUM(t), 0) AS delegated_thawing_tokens FROM (
SELECT LOWER("serviceProvider") AS sp, CAST(tokens AS HUGEINT) AS t FROM staking__tokens_undelegated
UNION ALL SELECT LOWER("serviceProvider"), -CAST(tokens AS HUGEINT) FROM staking__delegated_tokens_withdrawn
UNION ALL SELECT LOWER(indexer), 0 FROM staking_legacy__stake_delegated_locked
) GROUP BY 1
)
SELECT d.sp AS id,
d.delegator_shares,
COALESCE(d.delegated_net, 0) + COALESCE(l.pool_rewards_added, 0)
+ COALESCE(th.delegated_thawing_tokens, 0) AS delegated_tokens
FROM delegation d
LEFT JOIN ledger l ON l.sp = d.sp
LEFT JOIN thawing th ON th.sp = d.sp;
CREATE VIEW lodestar_delegator_positions AS
WITH events AS (
SELECT LOWER(delegator) AS delegator, LOWER(indexer) AS indexer, 'in' AS kind, CAST(tokens AS HUGEINT) AS tok, CAST(shares AS HUGEINT) AS sh, CAST(block_timestamp AS BIGINT) AS ts, block_number * 100000 + log_index AS k FROM staking_legacy__stake_delegated
UNION ALL SELECT LOWER(delegator), LOWER(indexer), 'out', CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT), CAST(block_timestamp AS BIGINT), block_number * 100000 + log_index FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER(delegator), LOWER("serviceProvider"), 'in', CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT), CAST(block_timestamp AS BIGINT), block_number * 100000 + log_index FROM staking__tokens_delegated
UNION ALL SELECT LOWER(delegator), LOWER("serviceProvider"), 'out', CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT), CAST(block_timestamp AS BIGINT), block_number * 100000 + log_index FROM staking__tokens_undelegated
),
exact AS (
SELECT delegator, indexer,
SUM(CASE WHEN kind = 'in' THEN sh ELSE -sh END) AS share_amount,
SUM(CASE WHEN kind = 'in' THEN tok ELSE 0 END) AS total_delegated_tokens,
SUM(CASE WHEN kind = 'out' THEN tok ELSE 0 END) AS total_undelegated_tokens,
MIN(ts) AS created_at,
MAX(CASE WHEN kind = 'out' THEN ts END) AS last_undelegated_at
FROM events GROUP BY 1, 2
),
folded AS (
SELECT delegator, indexer,
list_reduce(
list_prepend(
{'kind': 'init', 't': 0.0::DOUBLE, 'sh': 0.0::DOUBLE, 'shares': 0.0::DOUBLE, 'rate': 0.0::DOUBLE, 'realized': 0.0::DOUBLE},
list({'kind': kind, 't': CAST(tok AS DOUBLE), 'sh': CAST(sh AS DOUBLE), 'shares': 0.0::DOUBLE, 'rate': 0.0::DOUBLE, 'realized': 0.0::DOUBLE} ORDER BY k)
),
lambda acc, e:
CASE WHEN e.kind = 'in' THEN
{'kind': 's', 't': 0.0::DOUBLE, 'sh': 0.0::DOUBLE,
'shares': acc.shares + e.sh,
'rate': CASE WHEN acc.shares + e.sh > 0 THEN (acc.rate * acc.shares + e.t) / (acc.shares + e.sh) ELSE acc.rate END,
'realized': acc.realized}
ELSE
{'kind': 's', 't': 0.0::DOUBLE, 'sh': 0.0::DOUBLE,
'shares': acc.shares - e.sh,
'rate': acc.rate,
'realized': acc.realized + (e.t - e.sh * acc.rate)}
END
) AS st
FROM events GROUP BY 1, 2
),
withdrawn AS (
SELECT delegator, indexer, SUM(t) AS t FROM (
SELECT LOWER(delegator) AS delegator, LOWER(indexer) AS indexer, CAST(tokens AS HUGEINT) AS t FROM staking__stake_delegated_withdrawn
UNION ALL SELECT LOWER(delegator), LOWER("serviceProvider"), CAST(tokens AS HUGEINT) FROM staking__delegated_tokens_withdrawn
) GROUP BY 1, 2
),
-- Horizon thaw requests by a delegator on an indexer: request types 1 and 2 are delegation thaws
-- (`IHorizonStakingTypes.ThawRequestType`: Provision = 0, Delegation = 1, DelegationWithBeneficiary = 2).
thaw AS (
SELECT LOWER(owner) AS delegator, LOWER("serviceProvider") AS indexer, MAX(CAST("thawingUntil" AS BIGINT)) AS thawing_until
FROM staking__thaw_request_created WHERE CAST("requestType" AS INTEGER) IN (1, 2) GROUP BY 1, 2
)
SELECT x.delegator || '-' || x.indexer AS id,
x.delegator,
x.indexer,
x.share_amount,
x.total_delegated_tokens,
x.total_undelegated_tokens,
f.st.rate AS personal_exchange_rate,
CAST(f.st.realized AS HUGEINT) AS realized_rewards,
GREATEST(x.total_undelegated_tokens - COALESCE(w.t, 0), 0) AS locked_tokens,
CASE WHEN x.total_undelegated_tokens - COALESCE(w.t, 0) > 0 THEN COALESCE(t.thawing_until, 0) ELSE 0 END AS locked_until,
x.share_amount > 0 AS active,
x.created_at,
x.last_undelegated_at
FROM exact x
JOIN folded f ON f.delegator = x.delegator AND f.indexer = x.indexer
LEFT JOIN withdrawn w ON w.delegator = x.delegator AND w.indexer = x.indexer
LEFT JOIN thaw t ON t.delegator = x.delegator AND t.indexer = x.indexer;
-- ---------------------------------------------------------------------------------------------
-- The positions with the pool attached: `staked_tokens` is what the shares are worth today. Kept
-- apart from `lodestar_delegator_positions` because the join to `lodestar_indexer_pool` cannot be
-- filtered by delegator - DuckDB pushes nothing into the right side of a LEFT JOIN - so every reader
-- of this view pays the whole pool fold (4.8 s on 2026-09-26) even for one delegator. A reader that
-- does not need `staked_tokens`, or that already holds the pool figures, reads the positions view
-- (0.14 s for one delegator) and does the arithmetic below itself.
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_delegator_stakes AS
SELECT p.id,
p.delegator,
p.indexer,
p.share_amount,
-- shares * pool_tokens / pool_shares, the contract's uint256 formula. Two wei-scale HUGEINTs
-- multiplied overflow INT128 (a 15,572 GRT position in a 1.04M GRT pool did, on real data), so
-- the whole-share part stays exact and only the fractional remainder goes through DOUBLE,
-- which is within one part in 1e16 of the contract's answer.
CASE WHEN i.delegator_shares > 0
THEN (p.share_amount // i.delegator_shares) * i.delegated_tokens
+ CAST(CAST(p.share_amount % i.delegator_shares AS DOUBLE) * CAST(i.delegated_tokens AS DOUBLE) / CAST(i.delegator_shares AS DOUBLE) AS HUGEINT)
ELSE 0 END AS staked_tokens,
p.total_delegated_tokens,
p.total_undelegated_tokens,
p.personal_exchange_rate,
p.realized_rewards,
p.locked_tokens,
p.locked_until,
p.active,
p.created_at,
p.last_undelegated_at
FROM lodestar_delegator_positions p
LEFT JOIN lodestar_indexer_pool i ON i.id = p.indexer;
-- ---------------------------------------------------------------------------------------------
-- Per delegator: the subgraph's `Delegator` totals, folded from the positions above so the two
-- surfaces cannot disagree. `stakes_count` counts positions ever opened; `active_stakes_count`
-- those with shares left, the subgraph's rule. Over the positions, not the stakes: none of these
-- totals needs the pool, and the pool join would cost every delegator lookup the whole fold.
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_delegators AS
SELECT delegator AS id,
SUM(total_delegated_tokens) AS total_staked_tokens,
SUM(total_undelegated_tokens) AS total_unstaked_tokens,
SUM(realized_rewards) AS total_realized_rewards,
COUNT(*) AS stakes_count,
COUNT(*) FILTER (WHERE active) AS active_stakes_count
FROM lodestar_delegator_positions GROUP BY 1;
-- ---------------------------------------------------------------------------------------------
-- Per (curator, deployment): the subgraph's `Signal`, for the curator portfolio. Curation-level
-- positions only; name signal through GNS is the GNS contract's position here and the curator's
-- `NameSignal` in the subgraph, which no Lodestar page reads. Tokens in are net of the curation
-- tax as the subgraph's `signalledTokens` is; `realized_rewards` is the subgraph's unimplemented 0
-- (no curation handler writes it). The deployment figures beside it follow `lodestar_allocations`'
-- `signal` CTE and the active-allocation sum, so the three surfaces agree.
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_curator_signals AS
WITH pos AS (
SELECT curator, dep, SUM(sig) AS signal, SUM(tin) AS signalled_tokens, SUM(tout) AS unsignalled_tokens, MAX(ts) AS last_signal_change FROM (
SELECT LOWER(curator) AS curator, "subgraphDeploymentID" AS dep, CAST(signal AS HUGEINT) AS sig, CAST(tokens AS HUGEINT) - CAST("curationTax" AS HUGEINT) AS tin, CAST(0 AS HUGEINT) AS tout, CAST(block_timestamp AS BIGINT) AS ts FROM curation__signalled
UNION ALL SELECT LOWER(curator), "subgraphDeploymentID", -CAST(signal AS HUGEINT), 0, CAST(tokens AS HUGEINT), CAST(block_timestamp AS BIGINT) FROM curation__burned
) GROUP BY 1, 2
),
dep_signal AS (
SELECT dep, SUM(tok) AS signalled_tokens FROM (
SELECT "subgraphDeploymentID" AS dep, CAST(tokens AS HUGEINT) - CAST("curationTax" AS HUGEINT) AS tok FROM curation__signalled
UNION ALL SELECT "subgraphDeploymentID", -CAST(tokens AS HUGEINT) FROM curation__burned
UNION ALL SELECT "subgraphDeploymentID", CAST(tokens AS HUGEINT) FROM curation__collected
) GROUP BY 1
),
-- indexers' net query fees on the deployment, the subgraph's `queryFeesAmount` (see lodestar_deployments)
dep_fees AS (SELECT dep, SUM(t) AS query_fees_amount FROM (
SELECT "subgraphDeploymentId" AS dep, CAST("tokensCollected" AS HUGEINT) - CAST("tokensCurators" AS HUGEINT) - (CAST("tokensCollected" AS HUGEINT) // 100) AS t FROM subgraph_service__query_fees_collected
UNION ALL SELECT "subgraphDeploymentID", CAST("queryFees" AS HUGEINT) FROM staking_legacy__rebate_collected
UNION ALL SELECT "subgraphDeploymentID", CAST(tokens AS HUGEINT) - CAST("curationFees" AS HUGEINT) FROM staking_legacy__allocation_collected
) GROUP BY 1),
dep_stake AS (SELECT subgraph_deployment AS dep, SUM(allocated_tokens) AS staked_tokens FROM lodestar_allocations WHERE status = 'Active' GROUP BY 1)
SELECT p.curator || '-' || CAST(p.dep AS VARCHAR) AS id,
p.curator,
p.dep AS subgraph_deployment,
p.signalled_tokens,
p.unsignalled_tokens,
p.signal,
p.last_signal_change,
CAST(0 AS HUGEINT) AS realized_rewards,
COALESCE(ds.signalled_tokens, 0) AS deployment_signalled_tokens,
COALESCE(df.query_fees_amount, 0) AS deployment_query_fees_amount,
COALESCE(dst.staked_tokens, 0) AS deployment_staked_tokens
FROM pos p
LEFT JOIN dep_signal ds ON ds.dep = p.dep
LEFT JOIN dep_fees df ON df.dep = p.dep
LEFT JOIN dep_stake dst ON dst.dep = p.dep;
-- ---------------------------------------------------------------------------------------------
-- Curators, per the network subgraph's own rule (network view, #649): a curator is an address that
-- ever signalled through Curation *or* GNS, positions are netted per (curator, deployment) and per
-- (curator, subgraph) separately, and "active" is holding at least one position with signal left.
-- On L2 most signal is routed through GNS and the Curation-level curator is then the GNS contract
-- itself; that row is kept and flagged `is_gns`, so `api/curators` can drop it the way the subgraph's
-- list effectively does, without this view deciding for it.
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_curators AS
WITH positions AS (
SELECT curator, position, SUM(sig) AS net_signal, SUM(tin) AS tokens_in, SUM(tout) AS tokens_out FROM (
-- `totalSignalledTokens` is net of the curation tax in the subgraph (`tokens.minus(curationTax)`,
-- curation.ts handleSignalled); the GNS deposit is what was deposited.
SELECT LOWER(curator) AS curator, 'v:' || CAST("subgraphDeploymentID" AS VARCHAR) AS position, CAST(signal AS HUGEINT) AS sig, CAST(tokens AS HUGEINT) - CAST("curationTax" AS HUGEINT) AS tin, CAST(0 AS HUGEINT) AS tout FROM curation__signalled
UNION ALL SELECT LOWER(curator), 'v:' || CAST("subgraphDeploymentID" AS VARCHAR), -CAST(signal AS HUGEINT), 0, CAST(tokens AS HUGEINT) FROM curation__burned
UNION ALL SELECT LOWER(curator), 'n:' || CAST("subgraphID" AS VARCHAR), CAST("nSignalCreated" AS HUGEINT), CAST("tokensDeposited" AS HUGEINT), 0 FROM gns__signal_minted
UNION ALL SELECT LOWER(curator), 'n:' || CAST("subgraphID" AS VARCHAR), -CAST("nSignalBurnt" AS HUGEINT), 0, CAST("tokensReceived" AS HUGEINT) FROM gns__signal_burned
) GROUP BY 1, 2
)
SELECT curator AS id,
SUM(tokens_in) AS total_signalled_tokens,
SUM(tokens_out) AS total_unsignalled_tokens,
-- `signalCount` / `activeSignalCount` count Curation-level positions only; GNS (name signal)
-- positions are the subgraph's `nameSignalCount` and are reported separately. Counting both under
-- one name read 665 against 17 for one curator on the gateway.
COUNT(*) FILTER (WHERE position LIKE 'v:%') AS signal_count,
COUNT(*) FILTER (WHERE position LIKE 'v:%' AND net_signal > 0) AS active_signal_count,
COUNT(*) FILTER (WHERE position LIKE 'n:%') AS name_signal_count,
COUNT(*) FILTER (WHERE position LIKE 'n:%' AND net_signal > 0) AS active_name_signal_count,
-- The subgraph's `Curator.realizedRewards` is marked "NOT IMPLEMENTED" in its own schema and no
-- curation or GNS handler writes it, so it has been 0 on the gateway path since the field was
-- added. Zero here is the same fact, stated rather than reproduced by accident.
CAST(0 AS HUGEINT) AS realized_rewards,
curator = '0xec9a7fb6cbc2e41926127929c2dce6e9c5d33bec' AS is_gns
FROM positions GROUP BY 1;
-- ---------------------------------------------------------------------------------------------
-- Provisions, per (indexer, verifier): the subgraph's `Provision`. Created plus increased, less
-- deprovisioned and slashed; thawing is what has been thawed and not yet deprovisioned. Allocation
-- and reward figures only mean anything for the subgraph service's provision, and are joined for
-- that verifier only (the address is the nest's `subgraph_service` contract).
-- ---------------------------------------------------------------------------------------------
CREATE VIEW lodestar_provisions AS
WITH created AS (
SELECT LOWER("serviceProvider") AS indexer, LOWER(verifier) AS verifier,
CAST("maxVerifierCut" AS HUGEINT) AS max_verifier_cut,
CAST("thawingPeriod" AS HUGEINT) AS thawing_period,
block_timestamp AS created_at, block_number AS created_at_block,
ROW_NUMBER() OVER (PARTITION BY LOWER("serviceProvider"), LOWER(verifier) ORDER BY block_number DESC, log_index DESC) AS rn
FROM staking__provision_created
),
flows AS (
SELECT indexer, verifier, SUM(t) AS tokens_provisioned, GREATEST(SUM(th), 0) AS tokens_thawing FROM (
SELECT LOWER("serviceProvider") AS indexer, LOWER(verifier) AS verifier, CAST(tokens AS HUGEINT) AS t, CAST(0 AS HUGEINT) AS th FROM staking__provision_created
UNION ALL SELECT LOWER("serviceProvider"), LOWER(verifier), CAST(tokens AS HUGEINT), 0 FROM staking__provision_increased
UNION ALL SELECT LOWER("serviceProvider"), LOWER(verifier), -CAST(tokens AS HUGEINT), -CAST(tokens AS HUGEINT) FROM staking__tokens_deprovisioned
UNION ALL SELECT LOWER("serviceProvider"), LOWER(verifier), -CAST(tokens AS HUGEINT), 0 FROM staking__provision_slashed
UNION ALL SELECT LOWER("serviceProvider"), LOWER(verifier), 0, CAST(tokens AS HUGEINT) FROM staking__provision_thawed
) GROUP BY 1, 2
),
service_allocs AS (
SELECT LOWER(indexer) AS indexer,
SUM(allocated_tokens) FILTER (WHERE status = 'Active') AS tokens_allocated,
COUNT(*) FILTER (WHERE status = 'Active') AS allocation_count,
SUM(indexing_rewards) AS rewards_earned,
SUM(query_fees_collected) AS query_fees_collected
FROM lodestar_allocations GROUP BY 1
)
SELECT f.indexer || '-' || f.verifier AS id,
f.indexer,
f.verifier AS data_service,
f.tokens_provisioned,
f.tokens_thawing,
c.max_verifier_cut,
c.thawing_period,
c.created_at,
c.created_at_block,
CASE WHEN f.verifier = '0xb2bb92d0de618878e438b55d5846cfecd9301105' THEN COALESCE(sa.tokens_allocated, 0) END AS tokens_allocated,
CASE WHEN f.verifier = '0xb2bb92d0de618878e438b55d5846cfecd9301105' THEN COALESCE(sa.allocation_count, 0) END AS allocation_count,
CASE WHEN f.verifier = '0xb2bb92d0de618878e438b55d5846cfecd9301105' THEN COALESCE(sa.rewards_earned, 0) END AS rewards_earned,
CASE WHEN f.verifier = '0xb2bb92d0de618878e438b55d5846cfecd9301105' THEN COALESCE(sa.query_fees_collected, 0) END AS query_fees_collected
FROM flows f
LEFT JOIN (SELECT * FROM created WHERE rn = 1) c ON c.indexer = f.indexer AND c.verifier = f.verifier
LEFT JOIN service_allocs sa ON sa.indexer = f.indexer;lodestar_networkviews/91-lodestar-network.sql
-- Lodestar's `graphNetwork` singleton (RFC-0011), assembled from events instead of the gateway.
--
-- This is the one module in the migration that is genuinely an *aggregate* rather than a table, which
-- is why it was ranked last. Fourteen protocol-wide numbers, each a fold over a different contract's
-- events, and one that is not derivable at all without indexing millions of token transfers - so that
-- one is a pinned `eth_call` instead (RFC-0023 tier 3, `total_supply` and `issuance_per_block`).
CREATE VIEW lodestar_network AS
WITH staked AS (
-- Netted **per entity** before counting. Counting rows with `t > 0` instead answers "who ever
-- had an inflow", which is the same set as "who ever appears" - that is why this used to return
-- n_positive == n_all exactly, a number that agreed with itself and with nothing else.
SELECT SUM(net) AS total, COUNT(*) AS n_all,
COUNT(*) FILTER (WHERE net > 0) AS n_positive
FROM (
SELECT sp, SUM(t) AS net FROM (
-- Legacy era. Horizon renamed these wholesale at block ~408,847,369, so both halves are needed
-- and neither alone is the protocol's history: the Horizon table starts 366M blocks in.
SELECT indexer AS sp, CAST(tokens AS HUGEINT) AS t FROM staking_legacy__stake_deposited
UNION ALL
SELECT indexer, -CAST(tokens AS HUGEINT) FROM staking_legacy__stake_withdrawn
UNION ALL
SELECT "serviceProvider", CAST(tokens AS HUGEINT) FROM staking__horizon_stake_deposited
UNION ALL
SELECT "serviceProvider", -CAST(tokens AS HUGEINT) FROM staking__horizon_stake_withdrawn
UNION ALL
-- Slashing, and it is the whole of #649 gap 3. Deposits minus withdrawals left three indexers
-- holding 97k-247k GRT that the subgraph reports at zero; each had been slashed for *exactly*
-- its remaining balance, to the decimal. `tokens` is the full amount removed - the `reward`
-- beside it is the informer's cut of that same sum, not an additional debit, so subtracting
-- both would double-count.
--
-- Legacy only. `ProvisionSlashed` exists on the Horizon ABI and has never fired: 0 logs across
-- 408,000,000..496,956,109, checked against a control on the same address and range returning
-- 129,387. The legacy signature had to be confirmed the same way - the first attempt guessed a
-- five-parameter form, got 0 logs, and that zero looked exactly like an answer.
SELECT indexer, -CAST(tokens AS HUGEINT) FROM staking_legacy__stake_slashed
) GROUP BY 1
)
),
-- Delegation, across both eras, and the two eras leave the pool at different moments.
--
-- Legacy: undelegating emits `StakeDelegatedLocked`, which burns the shares and moves the tokens out
-- of the pool immediately into a thawing lock; `StakeDelegatedWithdrawn` merely releases what already
-- left. So the legacy subtraction is at *lock*, and subtracting the withdrawal instead would
-- double-count nothing but would lag the pool by the thawing period.
--
-- Horizon: `DelegatedTokensWithdrawn` is the exit. Whether the Horizon pool should also shrink at
-- `TokensUndelegated` is the one modelling choice here that the gateway comparison arbitrates rather
-- than reasoning - stated plainly because it is the likeliest thing to be wrong.
-- Delegation. Two corrections live here, both from reading the subgraph's mappings rather than
-- reasoning about the protocol.
--
-- **The exit event.** A Horizon delegation leaves the pool at `TokensUndelegated`, which burns the
-- shares; `DelegatedTokensWithdrawn` only releases tokens that already left and are done thawing.
-- Subtracting at withdrawal counts a thawing stake as still delegated, which is what left
-- `totalDelegatedTokens` 16.3% high. The legacy era has the same shape with different names:
-- `StakeDelegatedLocked` is the exit, `StakeDelegatedWithdrawn` the release.
--
-- **Shares, not tokens.** `activeDelegatorCount` tracks `delegatedStake.shareAmount` reaching zero
-- (`staking.ts:249`, `horizonStaking.ts:510`), never the token balance. Tokens accrue rewards while
-- shares do not, so the two diverge and netting tokens reports the wrong people as active.
delegation AS (
SELECT delegator, sp, SUM(tok) AS net_tokens, SUM(sh) AS net_shares FROM (
SELECT delegator, indexer AS sp,
CAST(tokens AS HUGEINT) AS tok, CAST(shares AS HUGEINT) AS sh
FROM staking_legacy__stake_delegated
UNION ALL
SELECT delegator, indexer,
-CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT)
FROM staking_legacy__stake_delegated_locked
UNION ALL
SELECT delegator, "serviceProvider",
CAST(tokens AS HUGEINT), CAST(shares AS HUGEINT)
FROM staking__tokens_delegated
UNION ALL
SELECT delegator, "serviceProvider",
-CAST(tokens AS HUGEINT), -CAST(shares AS HUGEINT)
FROM staking__tokens_undelegated
) GROUP BY 1, 2
),
delegated AS (
SELECT (SELECT SUM(net_tokens) FROM delegation) AS total,
(SELECT COUNT(DISTINCT delegator) FROM delegation) AS n_all,
(SELECT COUNT(DISTINCT delegator) FROM delegation WHERE net_shares > 0) AS n_positive
),
-- Curators, per the network subgraph's own rule (#649). `curatorCount` is distinct addresses ever
-- passed to `createOrLoadCurator`, which fires from Curation `Signalled`/`Burned` *and* from the GNS
-- name-signal handlers. Counting Curation alone gave 50 against the gateway's 1,819, because on L2
-- most signal is routed through GNS and the Curation-level `curator` is then the GNS contract itself.
--
-- `activeCuratorCount` is a different question and is not a distinct count: the subgraph increments
-- it only when a curator's `activeCombinedSignalCount` reaches 1, so it means "curators holding at
-- least one live position". A position is (curator, deployment) for curation signal and
-- (curator, subgraphID) for name signal, netted separately and only then folded together.
curator_positions AS (
SELECT curator, position, SUM(sig) AS net FROM (
SELECT curator, 'v:' || CAST("subgraphDeploymentID" AS VARCHAR) AS position,
CAST(signal AS HUGEINT) AS sig FROM curation__signalled
UNION ALL
SELECT curator, 'v:' || CAST("subgraphDeploymentID" AS VARCHAR),
-CAST(signal AS HUGEINT) FROM curation__burned
UNION ALL
SELECT curator, 'n:' || CAST("subgraphID" AS VARCHAR),
CAST("nSignalCreated" AS HUGEINT) FROM gns__signal_minted
UNION ALL
SELECT curator, 'n:' || CAST("subgraphID" AS VARCHAR),
-CAST("nSignalBurnt" AS HUGEINT) FROM gns__signal_burned
-- `GRTWithdrawn` is deliberately absent. It is one of the eight handlers that create a Curator
-- in the subgraph, but it never fired on L2 - the event belongs to the L1 migration path - so
-- nuthatch created no table for it, and a view referencing a table that does not exist fails to
-- load *in its entirety*, taking the other thirteen fields with it. See issue #663.
) GROUP BY 1, 2
),
signalled AS (
SELECT (SELECT SUM(t) FROM (
SELECT CAST(tokens AS HUGEINT) AS t FROM curation__signalled
UNION ALL SELECT -CAST(tokens AS HUGEINT) FROM curation__burned
)) AS total,
(SELECT COUNT(DISTINCT curator) FROM curator_positions) AS n_all,
(SELECT COUNT(DISTINCT curator) FROM curator_positions WHERE net > 0) AS n_positive
),
allocated AS (
SELECT SUM(allocated_tokens) AS total FROM lodestar_allocations WHERE status = 'Active'
),
subgraphs AS (
-- The active count as its own query rather than an aggregate FILTER holding a subquery, which not
-- every engine plans; the `NOT IN` keeps its meaning (a NULL deprecated id counts none as active).
SELECT (SELECT COUNT(DISTINCT "subgraphID") FROM gns__subgraph_published) AS n_all,
(SELECT COUNT(DISTINCT "subgraphID") FROM gns__subgraph_published
WHERE "subgraphID" NOT IN (SELECT "subgraphID" FROM gns__subgraph_deprecated)) AS n_active
),
-- The newest pinned reads. `reverted` would mean the call failed at that block, which is a fact about
-- chain state rather than a zero, so it is excluded rather than counted as none. `issuance_per_block`
-- reverts on every block before 75,279,040, where the getter did not yet exist, so on a nest that has
-- not reached that height the column is null rather than wrong.
--
-- `result` is the raw 32-byte return word, kept as bytes because a call result has no ABI type on
-- this side. Both of these are `uint256`, so the decode is the last 32 hex characters folded
-- base-16 into a HUGEINT: 128 bits, which holds anything up to 3.4e38 and so cannot overflow on a
-- token whose supply is 3.5e27. The `_raw` column stays as it was, because it is what a caller
-- checks the decode against.
supply AS (
-- `list_reduce(..., acc * 16 + d)` written out as the sum it computes, so an engine without
-- DuckDB's list lambdas reads the same number (the same fold as `lodestar_network_params`).
SELECT raw,
CAST(strpos('0123456789abcdef', substr(h, 1, 1)) - 1 AS HUGEINT) * CAST('21267647932558653966460912964485513216' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 2, 1)) - 1 AS HUGEINT) * CAST('1329227995784915872903807060280344576' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 3, 1)) - 1 AS HUGEINT) * CAST('83076749736557242056487941267521536' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 4, 1)) - 1 AS HUGEINT) * CAST('5192296858534827628530496329220096' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 5, 1)) - 1 AS HUGEINT) * CAST('324518553658426726783156020576256' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 6, 1)) - 1 AS HUGEINT) * CAST('20282409603651670423947251286016' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 7, 1)) - 1 AS HUGEINT) * CAST('1267650600228229401496703205376' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 8, 1)) - 1 AS HUGEINT) * CAST('79228162514264337593543950336' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 9, 1)) - 1 AS HUGEINT) * CAST('4951760157141521099596496896' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 10, 1)) - 1 AS HUGEINT) * CAST('309485009821345068724781056' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 11, 1)) - 1 AS HUGEINT) * CAST('19342813113834066795298816' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 12, 1)) - 1 AS HUGEINT) * CAST('1208925819614629174706176' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 13, 1)) - 1 AS HUGEINT) * CAST('75557863725914323419136' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 14, 1)) - 1 AS HUGEINT) * CAST('4722366482869645213696' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 15, 1)) - 1 AS HUGEINT) * CAST('295147905179352825856' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 16, 1)) - 1 AS HUGEINT) * CAST('18446744073709551616' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 17, 1)) - 1 AS HUGEINT) * CAST('1152921504606846976' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 18, 1)) - 1 AS HUGEINT) * CAST('72057594037927936' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 19, 1)) - 1 AS HUGEINT) * CAST('4503599627370496' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 20, 1)) - 1 AS HUGEINT) * CAST('281474976710656' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 21, 1)) - 1 AS HUGEINT) * CAST('17592186044416' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 22, 1)) - 1 AS HUGEINT) * CAST('1099511627776' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 23, 1)) - 1 AS HUGEINT) * CAST('68719476736' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 24, 1)) - 1 AS HUGEINT) * CAST('4294967296' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 25, 1)) - 1 AS HUGEINT) * CAST('268435456' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 26, 1)) - 1 AS HUGEINT) * CAST('16777216' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 27, 1)) - 1 AS HUGEINT) * CAST('1048576' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 28, 1)) - 1 AS HUGEINT) * CAST('65536' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 29, 1)) - 1 AS HUGEINT) * CAST('4096' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 30, 1)) - 1 AS HUGEINT) * CAST('256' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 31, 1)) - 1 AS HUGEINT) * CAST('16' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 32, 1)) - 1 AS HUGEINT) * CAST('1' AS HUGEINT) AS value,
block_number
FROM (SELECT CAST(result AS VARCHAR) AS raw, right(lower(CAST(result AS VARCHAR)), 32) AS h, block_number
FROM total_supply WHERE reverted = false
ORDER BY block_number DESC LIMIT 1)
),
issuance AS (
SELECT CAST(strpos('0123456789abcdef', substr(h, 1, 1)) - 1 AS HUGEINT) * CAST('21267647932558653966460912964485513216' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 2, 1)) - 1 AS HUGEINT) * CAST('1329227995784915872903807060280344576' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 3, 1)) - 1 AS HUGEINT) * CAST('83076749736557242056487941267521536' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 4, 1)) - 1 AS HUGEINT) * CAST('5192296858534827628530496329220096' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 5, 1)) - 1 AS HUGEINT) * CAST('324518553658426726783156020576256' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 6, 1)) - 1 AS HUGEINT) * CAST('20282409603651670423947251286016' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 7, 1)) - 1 AS HUGEINT) * CAST('1267650600228229401496703205376' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 8, 1)) - 1 AS HUGEINT) * CAST('79228162514264337593543950336' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 9, 1)) - 1 AS HUGEINT) * CAST('4951760157141521099596496896' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 10, 1)) - 1 AS HUGEINT) * CAST('309485009821345068724781056' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 11, 1)) - 1 AS HUGEINT) * CAST('19342813113834066795298816' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 12, 1)) - 1 AS HUGEINT) * CAST('1208925819614629174706176' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 13, 1)) - 1 AS HUGEINT) * CAST('75557863725914323419136' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 14, 1)) - 1 AS HUGEINT) * CAST('4722366482869645213696' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 15, 1)) - 1 AS HUGEINT) * CAST('295147905179352825856' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 16, 1)) - 1 AS HUGEINT) * CAST('18446744073709551616' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 17, 1)) - 1 AS HUGEINT) * CAST('1152921504606846976' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 18, 1)) - 1 AS HUGEINT) * CAST('72057594037927936' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 19, 1)) - 1 AS HUGEINT) * CAST('4503599627370496' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 20, 1)) - 1 AS HUGEINT) * CAST('281474976710656' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 21, 1)) - 1 AS HUGEINT) * CAST('17592186044416' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 22, 1)) - 1 AS HUGEINT) * CAST('1099511627776' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 23, 1)) - 1 AS HUGEINT) * CAST('68719476736' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 24, 1)) - 1 AS HUGEINT) * CAST('4294967296' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 25, 1)) - 1 AS HUGEINT) * CAST('268435456' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 26, 1)) - 1 AS HUGEINT) * CAST('16777216' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 27, 1)) - 1 AS HUGEINT) * CAST('1048576' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 28, 1)) - 1 AS HUGEINT) * CAST('65536' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 29, 1)) - 1 AS HUGEINT) * CAST('4096' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 30, 1)) - 1 AS HUGEINT) * CAST('256' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 31, 1)) - 1 AS HUGEINT) * CAST('16' AS HUGEINT) +
CAST(strpos('0123456789abcdef', substr(h, 32, 1)) - 1 AS HUGEINT) * CAST('1' AS HUGEINT) AS value,
block_number
FROM (SELECT right(lower(CAST(result AS VARCHAR)), 32) AS h, block_number
FROM issuance_per_block WHERE reverted = false
ORDER BY block_number DESC LIMIT 1)
),
-- Bridge flow, and it is *not* the subgraph's `totalGRTMinted` / `totalGRTBurned`. Those count every
-- mint and burn, including the indexing rewards minted as a `Transfer` from the zero address; this
-- pair is the L1<->L2 bridge alone. `Transfer` is deliberately not indexed - see the comment on the
-- `graph_token` contract - so the bridge half is what a nest can state exactly today.
bridge AS (
SELECT (SELECT SUM(CAST(amount AS HUGEINT)) FROM graph_token__bridge_minted) AS minted,
(SELECT SUM(CAST(amount AS HUGEINT)) FROM graph_token__bridge_burned) AS burned
),
-- All-time totals, from the events. They were folded from `lodestar_epochs`, but `epoch_boundaries`
-- begins at epoch 1104 (the first `EpochRun` the nest holds) and 631M GRT of legacy `RewardsAssigned`
-- sit in epochs 113..1103, so the fold read 196M against the gateway's 837M. Summed from the events
-- both eras give 837,303,089 GRT, exact. Query fees follow the subgraph's own counter, which is gross
-- for the legacy era (`RebateCollected.tokens`, `AllocationCollected.tokens`) and net of only the 1%
-- protocol cut for Horizon (`tokensCollected - tokensCollected // 100`, truncated per event) - not the
-- indexer's net that `lodestar_epochs` and the deployments state. Measured to four digits, then exact.
epoch_totals AS (
SELECT (SELECT SUM(CAST(amount AS HUGEINT)) FROM rewards__rewards_assigned)
+ (SELECT SUM(CAST(amount AS HUGEINT)) FROM rewards__horizon_rewards_assigned) AS indexing_rewards,
(SELECT SUM(CAST(tokens AS HUGEINT)) FROM staking_legacy__rebate_collected)
+ (SELECT SUM(CAST(tokens AS HUGEINT)) FROM staking_legacy__allocation_collected)
+ (SELECT SUM(CAST("tokensCollected" AS HUGEINT) - CAST("tokensCollected" AS HUGEINT) // 100) FROM subgraph_service__query_fees_collected) AS query_fees
)
SELECT (SELECT total FROM staked) AS total_tokens_staked,
-- the subgraph's `totalDelegatedTokens` is the pools net of thawing, and summing `lodestar_indexers`
-- that way reproduced it to the wei (1,074,963,614 GRT); this view's own `delegation` netting
-- lacks the rewards the pools accrue and read 626M. `delegated` still serves the delegator counts.
(SELECT SUM(delegated_tokens - delegated_thawing_tokens) FROM lodestar_indexers) AS total_delegated_tokens,
(SELECT total FROM signalled) AS total_tokens_signalled,
(SELECT total FROM allocated) AS total_tokens_allocated,
(SELECT raw FROM supply) AS total_supply_raw,
(SELECT block_number FROM supply) AS total_supply_at_block,
(SELECT value FROM supply) AS total_supply,
(SELECT value FROM issuance) AS issuance_per_block,
(SELECT block_number FROM issuance) AS issuance_per_block_at_block,
(SELECT minted FROM bridge) AS bridge_minted,
(SELECT burned FROM bridge) AS bridge_burned,
(SELECT indexing_rewards FROM epoch_totals) AS total_indexing_rewards,
(SELECT query_fees FROM epoch_totals) AS total_query_fees,
(SELECT n_all FROM staked) AS indexer_count,
(SELECT n_positive FROM staked) AS staked_indexers_count,
(SELECT n_all FROM delegated) AS delegator_count,
(SELECT n_positive FROM delegated) AS active_delegator_count,
(SELECT n_all FROM signalled) AS curator_count,
(SELECT n_positive FROM signalled) AS active_curator_count,
(SELECT n_all FROM subgraphs) AS subgraph_count,
(SELECT n_active FROM subgraphs) AS active_subgraph_count,
(SELECT MAX(epoch) FROM epoch_boundaries) AS current_epoch;lodestar_deploymentsviews/95-lodestar-deployments.sql
-- The subgraph's `SubgraphDeployment` figures, one row per deployment that ever had an allocation or
-- any signal (nuthatch#1160, group B: `api/subgraph-deployments`, `api/subgraph-fees-30d`,
-- `api/subgraph-search`, the subgraph OpenGraph image). Names live on IPFS behind graph-gns-nest's
-- metadata hashes; this view is the on-chain half and is joined to them in Lodestar.
--
-- `signalled_tokens` follows `lodestar_allocations`' signal CTE (net of the curation tax, plus query
-- fees collected into the pool, less burns). `query_fees_amount` is the indexers' net query fees on the
-- deployment, per the `fees` CTE below. `staked_tokens` is active allocation.
-- `created_at` is the first block anything touched the deployment: its first allocation or its first
-- signal, whichever came first; the subgraph dates it from its first appearance the same way.
-- `active_allocation_count` and `curator_count` are the lengths the gateway path counted (the latter
-- counting exited positions too, as the subgraph's list does).
-- `denied_since` is the latest RewardsManager denylist update's sinceBlock. An update with zero
-- clears denial; an allocation-level RewardsDenied event cannot answer deployment eligibility.
CREATE VIEW lodestar_deployments AS
WITH signal AS (
SELECT dep, SUM(tok) AS signalled_tokens, MIN(ts) AS first_ts FROM (
SELECT LOWER("subgraphDeploymentID") AS dep, CAST(tokens AS HUGEINT) - CAST("curationTax" AS HUGEINT) AS tok, CAST(block_timestamp AS BIGINT) AS ts FROM curation__signalled
UNION ALL SELECT LOWER("subgraphDeploymentID"), -CAST(tokens AS HUGEINT), CAST(block_timestamp AS BIGINT) FROM curation__burned
UNION ALL SELECT LOWER("subgraphDeploymentID"), CAST(tokens AS HUGEINT), CAST(block_timestamp AS BIGINT) FROM curation__collected
) GROUP BY 1
),
-- The subgraph's `queryFeesAmount` is what the deployment's indexers were paid, after the curators' cut:
-- `QueryFeesCollected.tokensCollected - tokensCurators - tokensCollected // 100` (the 1% protocol cut) for
-- the Horizon era, `RebateCollected.queryFees` before it. Summing curation `Collected` instead gave exactly
-- one ninth of the gateway's figure on every deployment measured (the curators' 10% against the 90%).
fees AS (
SELECT dep, SUM(t) AS query_fees_amount FROM (
SELECT LOWER("subgraphDeploymentId") AS dep, CAST("tokensCollected" AS HUGEINT) - CAST("tokensCurators" AS HUGEINT) - (CAST("tokensCollected" AS HUGEINT) // 100) AS t FROM subgraph_service__query_fees_collected
UNION ALL SELECT LOWER("subgraphDeploymentID"), CAST("queryFees" AS HUGEINT) FROM staking_legacy__rebate_collected
UNION ALL SELECT LOWER("subgraphDeploymentID"), CAST(tokens AS HUGEINT) - CAST("curationFees" AS HUGEINT) FROM staking_legacy__allocation_collected
) GROUP BY 1
),
allocs AS (
SELECT LOWER(subgraph_deployment) AS dep,
SUM(allocated_tokens) FILTER (WHERE status = 'Active') AS staked_tokens,
COUNT(*) FILTER (WHERE status = 'Active') AS active_allocation_count,
MIN(created_at) AS first_ts
FROM lodestar_allocations GROUP BY 1
),
curators AS (
-- every position ever, exited ones included: that is the length of the subgraph's `curatorSignals`
SELECT LOWER(subgraph_deployment) AS dep, COUNT(*) AS curator_count FROM lodestar_curator_signals GROUP BY 1
),
denylist AS (
SELECT dep, since_block FROM (
SELECT LOWER("subgraphDeploymentID") AS dep,
CAST("sinceBlock" AS BIGINT) AS since_block,
ROW_NUMBER() OVER (PARTITION BY LOWER("subgraphDeploymentID") ORDER BY block_number DESC, log_index DESC) AS rank
FROM rewards__rewards_denylist_updated
) WHERE rank = 1
),
deps AS (SELECT dep FROM signal UNION SELECT dep FROM allocs)
SELECT d.dep AS id,
COALESCE(s.signalled_tokens, 0) AS signalled_tokens,
COALESCE(a.staked_tokens, 0) AS staked_tokens,
COALESCE(f.query_fees_amount, 0) AS query_fees_amount,
LEAST(COALESCE(s.first_ts, 9223372036854775807), COALESCE(a.first_ts, 9223372036854775807)) AS created_at,
COALESCE(a.active_allocation_count, 0) AS active_allocation_count,
COALESCE(c.curator_count, 0) AS curator_count,
NULLIF(dl.since_block, 0) AS denied_since
FROM deps d
LEFT JOIN signal s ON s.dep = d.dep
LEFT JOIN fees f ON f.dep = d.dep
LEFT JOIN allocs a ON a.dep = d.dep
LEFT JOIN curators c ON c.dep = d.dep
LEFT JOIN denylist dl ON dl.dep = d.dep;lodestar_indexer_deployment_dailyviews/96-lodestar-indexer-deployment-daily.sql
-- One row per indexer, deployment and UTC day: indexing rewards split between the indexer and its
-- delegators, and query fees taken apart the way the contracts took them. The definition behind
-- Lodestar's P&L and Daily Trends (nightswatchhq/lodestar#228); `lodestar_indexer_daily` is its sum.
--
-- Dated by the event that paid, never by allocation close: Horizon collects on every POI, so an
-- allocation can be paid for months before it closes, and a close-dated series books it all on one day.
--
-- Rewards are `IndexingRewardsCollected`, legacy `RewardsAssigned`, and `HorizonRewardsAssigned` on
-- legacy allocations only. On a Horizon allocation `HorizonRewardsAssigned` fires in the same
-- transaction as `IndexingRewardsCollected` for the same amount (235,408 of 235,408 pairs on the live
-- nest, 2026-09-13), so taking both counts every Horizon reward twice.
--
-- Query fees, Horizon: `GraphPayments.collect` takes the protocol cut rounded up, then the data
-- service's cut of the remainder rounded up (which SubgraphService forwards to curation and reports as
-- `tokensCurators`), then, when the pool has shares, the delegation fee cut of what is left rounded up.
-- The indexer gets the rest. `fees_net` is that rest. Checked against `GraphPaymentCollected` receipts.
-- Legacy rebates state it outright: `queryRebates` is emitted after the delegators' share is taken, and
-- whatever the exponential rebate withheld was burned. `AllocationCollected` predates both and pays at
-- a later claim, so its net and delegator figures are NULL rather than guessed.
CREATE VIEW lodestar_indexer_deployment_daily AS
-- `PROTOCOL_PAYMENT_CUT` is immutable in GraphPayments, so the newest sample is every collection's cut.
-- Decoded here rather than read from `lodestar_network_params`, which has no row until an epoch length
-- update exists. No sample leaves the protocol cut, and so the net, NULL rather than an assumed 1%.
WITH protocol_cut AS (
-- The newest sample's last 32 hex characters as a HUGEINT, read as `lodestar_network_params` reads
-- them: two 64-bit halves, for engines without DuckDB's list comprehensions.
SELECT CAST(CAST('0x' || substr(h, 1, 16) AS UBIGINT) AS HUGEINT) * CAST('18446744073709551616' AS HUGEINT)
+ CAST(CAST('0x' || substr(h, 17, 16) AS UBIGINT) AS HUGEINT) AS ppm
FROM (SELECT right(lower(CAST(result AS VARCHAR)), 32) AS h
FROM protocol_payment_cut WHERE reverted = false ORDER BY block_number DESC LIMIT 1)
),
-- The pool SubgraphService pays into is the legacy pool, so both eras' shares count, and every Horizon
-- delegation on this nest names SubgraphService as its verifier.
pool_shares AS (
SELECT sp, k, SUM(sh) OVER (PARTITION BY sp ORDER BY k ROWS UNBOUNDED PRECEDING) AS cum_shares FROM (
SELECT LOWER(indexer) AS sp, block_number * 100000 + log_index AS k, CAST(shares AS HUGEINT) AS sh FROM staking_legacy__stake_delegated
UNION ALL SELECT LOWER(indexer), block_number * 100000 + log_index, -CAST(shares AS HUGEINT) FROM staking_legacy__stake_delegated_locked
UNION ALL SELECT LOWER("serviceProvider"), block_number * 100000 + log_index, CAST(shares AS HUGEINT) FROM staking__tokens_delegated
UNION ALL SELECT LOWER("serviceProvider"), block_number * 100000 + log_index, -CAST(shares AS HUGEINT) FROM staking__tokens_undelegated
)
),
-- Query fees are payment type 0. No `DelegationFeeCutSet` means the stored cut is zero.
query_fee_cuts AS (
SELECT LOWER("serviceProvider") AS sp, CAST("feeCut" AS HUGEINT) AS cut, block_number * 100000 + log_index AS k
FROM staking__delegation_fee_cut_set
WHERE CAST("paymentType" AS BIGINT) = 0 AND LOWER(verifier) = '0xb2bb92d0de618878e438b55d5846cfecd9301105'
),
legacy_deployment AS (
SELECT LOWER("allocationID") AS a, LOWER("subgraphDeploymentID") AS dep FROM staking_legacy__allocation_created
),
legacy_rewards AS (
SELECT r.sp, r.dep, r.ts, r.amount, COALESCE(l.pool_delta, 0) AS to_delegators
FROM (
SELECT LOWER(ra.indexer) AS sp, d.dep, CAST(ra.block_timestamp AS BIGINT) AS ts, ra.block_number * 100000 + ra.log_index AS k, CAST(ra.amount AS HUGEINT) AS amount
FROM rewards__rewards_assigned ra LEFT JOIN legacy_deployment d ON d.a = LOWER(ra."allocationID")
UNION ALL
SELECT LOWER(h.indexer), d.dep, CAST(h.block_timestamp AS BIGINT), h.block_number * 100000 + h.log_index, CAST(h.amount AS HUGEINT)
FROM rewards__horizon_rewards_assigned h JOIN legacy_deployment d ON d.a = LOWER(h."allocationID")
) r
LEFT JOIN lodestar_indexer_ledger l
ON l.indexer = r.sp AND l.k = r.k AND l.kind IN ('legacy_reward_share', 'legacy_path_share')
),
horizon_fees AS (
SELECT q.sp, q.dep, q.ts, q.gross, q.curators, q.protocol,
CASE WHEN COALESCE(ps.cum_shares, 0) > 0
THEN (q.gross - q.protocol - q.curators) - (q.gross - q.protocol - q.curators) * (1000000 - COALESCE(c.cut, 0)) // 1000000
ELSE 0 END AS delegators
-- The cut and the pool shares in force at each collection: the newest `query_fee_cuts` and
-- `pool_shares` rows at or before its key. That was `ASOF LEFT JOIN`, which only DuckDB spells; as in
-- `lodestar_indexer_ledger`, each newest key is carried forward over the three streams in key order,
-- a change at the collection's own key sorting first as `>=` had it, and the collection joins on
-- that exact key. `k` is unique per event.
FROM (
SELECT sp, dep, ts, gross, curators, protocol, ck, pk FROM (
SELECT sp, dep, ts, gross, curators, protocol, src,
MAX(CASE WHEN src = 0 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS ck,
MAX(CASE WHEN src = 1 THEN k END) OVER (PARTITION BY sp ORDER BY k, src ROWS UNBOUNDED PRECEDING) AS pk
FROM (
SELECT sp, k, 0 AS src, CAST(NULL AS VARCHAR) AS dep, CAST(NULL AS BIGINT) AS ts,
CAST(NULL AS HUGEINT) AS gross, CAST(NULL AS HUGEINT) AS curators, CAST(NULL AS HUGEINT) AS protocol
FROM query_fee_cuts
UNION ALL SELECT sp, k, 1, CAST(NULL AS VARCHAR), CAST(NULL AS BIGINT), CAST(NULL AS HUGEINT), CAST(NULL AS HUGEINT), CAST(NULL AS HUGEINT)
FROM pool_shares
UNION ALL SELECT LOWER("serviceProvider"), block_number * 100000 + log_index, 2, LOWER("subgraphDeploymentId"),
CAST(block_timestamp AS BIGINT),
CAST("tokensCollected" AS HUGEINT), CAST("tokensCurators" AS HUGEINT),
CAST("tokensCollected" AS HUGEINT)
- CAST("tokensCollected" AS HUGEINT) * (1000000 - (SELECT ppm FROM protocol_cut)) // 1000000
FROM subgraph_service__query_fees_collected
)
) WHERE src = 2
) q
LEFT JOIN query_fee_cuts c ON c.sp = q.sp AND c.k = q.ck
LEFT JOIN pool_shares ps ON ps.sp = q.sp AND ps.k = q.pk
),
events AS (
SELECT LOWER(indexer) AS indexer, LOWER("subgraphDeploymentId") AS deployment, CAST(block_timestamp AS BIGINT) AS ts,
CAST("tokensRewards" AS HUGEINT) AS total, CAST("tokensIndexerRewards" AS HUGEINT) AS to_indexer,
CAST("tokensDelegationRewards" AS HUGEINT) AS to_delegators, 1 AS rewarded,
CAST(0 AS HUGEINT) AS gross, CAST(0 AS HUGEINT) AS curators, CAST(0 AS HUGEINT) AS protocol,
CAST(0 AS HUGEINT) AS fee_delegators, CAST(0 AS HUGEINT) AS burned, CAST(0 AS HUGEINT) AS net, 0 AS collected
FROM subgraph_service__indexing_rewards_collected
UNION ALL SELECT sp, dep, ts, amount, amount - to_delegators, to_delegators, 1, 0, 0, 0, 0, 0, 0, 0 FROM legacy_rewards
UNION ALL SELECT sp, dep, ts, 0, 0, 0, 0, gross, curators, protocol, delegators, 0, gross - protocol - curators - delegators, 1 FROM horizon_fees
UNION ALL SELECT LOWER(indexer), LOWER("subgraphDeploymentID"), CAST(block_timestamp AS BIGINT), 0, 0, 0, 0,
CAST(tokens AS HUGEINT), CAST("curationFees" AS HUGEINT), CAST("protocolTax" AS HUGEINT),
CAST("delegationRewards" AS HUGEINT),
CAST("queryFees" AS HUGEINT) - CAST("queryRebates" AS HUGEINT) - CAST("delegationRewards" AS HUGEINT),
CAST("queryRebates" AS HUGEINT), 1
FROM staking_legacy__rebate_collected
UNION ALL SELECT LOWER(indexer), LOWER("subgraphDeploymentID"), CAST(block_timestamp AS BIGINT), 0, 0, 0, 0,
CAST(tokens AS HUGEINT), CAST("curationFees" AS HUGEINT),
CAST(tokens AS HUGEINT) - CAST("curationFees" AS HUGEINT) - CAST("rebateFees" AS HUGEINT),
NULL, 0, NULL, 1
FROM staking_legacy__allocation_collected
)
SELECT indexer,
deployment,
ts // 86400 * 86400 AS day_start,
SUM(total) AS total_rewards,
SUM(to_indexer) AS indexer_rewards,
SUM(to_delegators) AS delegator_rewards,
SUM(rewarded) AS reward_count,
SUM(gross) AS fees_gross,
SUM(curators) AS fees_curators,
SUM(protocol) AS fees_protocol,
SUM(fee_delegators) AS fees_delegators,
SUM(burned) AS fees_burned,
SUM(net) AS fees_net,
SUM(collected) AS fee_count
FROM events
GROUP BY 1, 2, 3;lodestar_indexer_dailyviews/97-lodestar-indexer-daily.sql
-- One row per indexer per UTC day: `lodestar_indexer_deployment_daily` summed over deployments, and
-- nothing else, so the P&L's per-deployment table and its daily totals cannot disagree.
CREATE VIEW lodestar_indexer_daily AS
SELECT indexer,
day_start,
SUM(total_rewards) AS total_rewards,
SUM(indexer_rewards) AS indexer_rewards,
SUM(delegator_rewards) AS delegator_rewards,
SUM(reward_count) AS reward_count,
SUM(fees_gross) AS fees_gross,
SUM(fees_curators) AS fees_curators,
SUM(fees_protocol) AS fees_protocol,
SUM(fees_delegators) AS fees_delegators,
SUM(fees_burned) AS fees_burned,
SUM(fees_net) AS fees_net,
SUM(fee_count) AS fee_count
FROM lodestar_indexer_deployment_daily
GROUP BY 1, 2;Tables on the mirror
| table | rows | segments | from block | to block |
|---|---|---|---|---|
| l1_blocks | 999,063 | 857 | 42,449,227 | 523,328,607 |
| staking_legacy__allocation_created | 591,071 | 455 | 59,486,400 | 408,904,657 |
| staking_legacy__allocation_closed_f672 | 583,507 | 442 | 129,953,655 | 455,198,600 |
| rewards__rewards_assigned | 510,692 | 392 | 70,286,400 | 408,904,657 |
| subgraph_service__indexing_rewards_collected | 354,061 | 310 | 408,818,201 | 523,328,607 |
| staking_legacy__stake_delegated | 346,288 | 270 | 60,586,400 | 408,818,200 |
| rewards__horizon_rewards_assigned | 328,980 | 298 | 408,818,201 | 523,328,607 |
| subgraph_service__allocation_created | 315,447 | 304 | 408,818,201 | 523,328,607 |
| subgraph_service__allocation_closed | 300,242 | 294 | 408,818,201 | 523,328,607 |
| curation__collected | 257,706 | 274 | 79,786,400 | 523,328,607 |
| staking__tokens_to_delegation_pool_added | 228,255 | 235 | 409,165,574 | 523,328,607 |
| staking__horizon_stake_deposited | 194,949 | 201 | 408,818,201 | 523,328,607 |
| staking_legacy__rebate_collected | 193,263 | 150 | 130,040,056 | 477,282,629 |
| staking_legacy__stake_deposited | 182,178 | 159 | 59,486,400 | 408,818,200 |
| staking__provision_increased | 155,908 | 178 | 408,818,201 | 523,328,607 |
| staking_legacy__stake_delegated_locked | 154,575 | 140 | 60,586,400 | 408,818,200 |
| staking__stake_delegated_withdrawn | 102,814 | 104 | 90,982,952 | 522,152,578 |
| escrow__escrow_collected | 84,671 | 142 | 408,904,658 | 523,328,607 |
| tally__payment_collected | 84,650 | 142 | 408,904,658 | 523,328,607 |
| tally__r_a_v_collected | 84,650 | 142 | 408,904,658 | 523,328,607 |
| subgraph_service__query_fees_collected | 84,575 | 142 | 408,904,658 | 523,328,607 |
| subgraph_service__allocation_resized | 47,418 | 86 | 441,355,903 | 523,241,657 |
| curation__signalled | 25,925 | 67 | 58,286,400 | 523,328,607 |
| gns__signal_minted | 18,397 | 44 | 58,286,400 | 523,241,657 |
| gns__subgraph_published | 17,663 | 51 | 52,186,400 | 523,328,607 |
| curation__burned | 12,337 | 50 | 58,586,400 | 523,328,607 |
| graph_token__bridge_minted | 12,276 | 37 | 42,986,400 | 523,147,058 |
| escrow__deposit | 8,509 | 81 | 408,554,471 | 523,328,607 |
| staking_legacy__allocation_closed_7203 | 7,564 | 8 | 66,186,400 | 129,953,654 |
| gns__signal_burned | 5,050 | 26 | 58,586,400 | 523,328,607 |
| curation_tax_percentage | 4,808 | 62 | 42,449,227 | 523,241,657 |
| total_supply | 4,808 | 62 | 42,449,227 | 523,241,657 |
| issuance_per_block | 4,480 | 62 | 75,270,365 | 523,241,657 |
| staking__tokens_delegated | 2,757 | 45 | 409,694,965 | 523,241,657 |
| staking__thaw_request_created | 2,253 | 51 | 409,426,894 | 523,328,607 |
| staking_legacy__rebate_claimed | 2,227 | 3 | 82,186,400 | 129,520,409 |
| staking__tokens_undelegated | 2,167 | 49 | 409,426,894 | 523,147,058 |
| staking_legacy__allocation_collected | 2,127 | 3 | 79,786,400 | 129,953,654 |
| graph_token__bridge_burned | 1,913 | 11 | 43,786,400 | 521,712,102 |
| staking__thaw_request_fulfilled | 1,635 | 44 | 410,057,725 | 523,328,607 |
| staking__thaw_requests_fulfilled | 1,512 | 44 | 410,057,725 | 523,328,607 |
| staking__delegated_tokens_withdrawn | 1,444 | 41 | 410,057,725 | 523,328,607 |
| staking_legacy__delegation_parameters_updated | 1,227 | 2 | 59,486,400 | 408,818,200 |
| rewards__rewards_denylist_updated | 1,223 | 4 | 63,186,400 | 521,015,596 |
| gns__subgraph_deprecated | 1,149 | 11 | 82,472,859 | 523,328,607 |
| delegation_ratio | 1,144 | 59 | 408,818,201 | 523,241,657 |
| max_thawing_period | 1,144 | 59 | 408,818,201 | 523,241,657 |
| protocol_payment_cut | 1,144 | 59 | 408,818,201 | 523,241,657 |
| staking__horizon_stake_withdrawn | 489 | 25 | 410,861,426 | 523,328,607 |
| staking__delegation_fee_cut_set | 404 | 6 | 409,165,574 | 522,868,233 |
| service_registry__service_registered | 394 | 1 | 59,486,400 | 487,005,119 |
| staking_legacy__set_operator | 262 | 1 | 59,486,400 | 407,213,846 |
| rewards__rewards_denied_07b6 | 260 | 1 | 68,486,400 | 408,904,657 |
| staking_legacy__stake_locked | 171 | 1 | 105,164,609 | 407,477,440 |
| subgraph_service__service_provider_registered | 163 | 4 | 408,818,201 | 521,712,102 |
| staking_legacy__stake_withdrawn | 145 | 1 | 119,086,400 | 407,477,440 |
| gns__g_r_t_withdrawn | 115 | 2 | 206,084,605 | 521,537,718 |
| staking_legacy__set_rewards_destination | 114 | 1 | 60,486,400 | 402,034,207 |
| staking__provision_created | 95 | 1 | 408,818,201 | 496,242,280 |
| rewards__rewards_denied_9b13 | 89 | 1 | 408,904,658 | 493,273,826 |
| staking__provision_thawed | 85 | 7 | 410,240,288 | 523,241,657 |
| staking__tokens_deprovisioned | 67 | 7 | 423,575,460 | 520,748,950 |
| escrow__thaw | 55 | 1 | 456,913,300 | 507,829,323 |
| staking__operator_set | 35 | 2 | 417,370,049 | 516,316,161 |
| staking__horizon_stake_locked | 27 | 1 | 409,165,574 | 423,124,717 |
| staking_legacy__stake_slashed | 24 | 1 | 261,698,254 | 392,973,452 |
| disputes__indexing_dispute_created | 10 | 2 | 420,703,694 | 517,029,123 |
| rewards__parameter_updated | 10 | 1 | 42,449,227 | 498,348,749 |
| disputes__dispute_drawn | 9 | 2 | 424,845,064 | 519,431,623 |
| disputes__query_dispute_created | 4 | 2 | 516,942,724 | 521,451,239 |
| disputes__dispute_linked | 2 | 2 | 516,942,724 | 521,451,239 |
| epochs__epoch_length_update | 2 | 1 | 42,449,227 | 63,886,399 |
| service_registry__service_unregistered | 2 | 1 | 486,914,933 | 487,005,119 |
| epochs__epoch_run | 1 | 1 | 70,475,201 | 70,585,578 |
| escrow__cancel_thaw | 1 | 1 | 456,913,300 | 457,002,997 |