Crosslink: Incentivized Feature Net

Hey everyone, I’ll probably try and do a proper write up about the functionality of this tool that I built to help with analytics on Crosslink, but I built an indexer that really targets exposing some of the staking and finalizer data, that are really outside of the reach of the RPC commands.
This indexer runs alongside your existing crosslink node and generates a SQLite db for you to query against.

Domain Where the node keeps it Reachable by RPC?
Finalizer behaviour the pos.chain file on disk tip only — no history
Mining / PoW blocks block store, via getblock yes
Staking actions inside raw transaction bodies no — not exposed at all

I’ll be doing some other looks and slices of the testnet to see other interesting datapoints, as well as modeling some of the behaviors from how the rewards splits between PoS/PoW can potentially lead to. Looking into how does this drive behaviors from miners and/or users that aren’t running their own node and how they can make educated assumptions as to whom will they choose to stake to.

This is the Github Repo for it: GitHub - JustShieldMe/crosslink_indexer: Builds a queryable SQLite database of a Crosslink node's history, for analytics on mining, staking actions, and finalizer behavior. · GitHub
There is a section in the docs in the repo for how the schema for the db is setup, how to use the cli of the indexer, and some example queries. In the CLI you can run the stats command and it gives you a current state of the chain like below:
crosslink-indexer --db ~/crosslink-indexer/crosslink.sqlite stats
== BFT / finalizer ==
bft blocks : 101605
height range : 0 .. 101604
signatures : 1396427
roster rows : 3974550
distinct signers : 33
roster size : 1 .. 48
mean signed power: 79.9% (BFT needs > 66.7%)
== PoW / mining ==
blocks : 521341
height range : 0 .. 521340
distinct miners : 73
== Staking ==
actions : 8890
CreateNewDelegationBond 8593 2191625.1925 ctaz
RetargetDelegationBond 281 0.0000 ctaz
BeginDelegationUnbonding 10 0.0000 ctaz
WithdrawDelegationBond 6 824.1882 ctaz
NOTE: 30 actions outside the staking window that consensus does not exempt
height 352270 (offset 70) f2cff876f7fe54223213c2db06b0a4416e065cc21ada67099e294a14b7857ebd
height 352570 (offset 70) eee37331b82e63f04ee8de8441a04f2e5b0f6fe3d89c05f4b45f2401406ccd81
height 354671 (offset 71) 2748dc9cdd2d2d5748ffd1c1c6a1b635adcda13db9af62e68cc3d10110ee01ca
height 364277 (offset 77) d901fe57ce29517d2c90f7333eea48d9d0f882010de7407134b57fef40ff0fff
height 365170 (offset 70) c280c903faa65d605732fa1cc5fd4d211a37c845a28f0ec1409c0ac951338d46

Please, play around with it, make issues/PRs if it’s something that will help you out, but a couple of things that I’ve already played around with are:

Finality Stalls

Looking at how many rounds it took to get enough voting power signed to continue finality. In the plot below you can see in the earlier days there were more issues as some of the bugs and issues were being resolved, but in the last ~50,000 BFT blocks things have been much more tame and finality maintained without additional rounds of signers. A (maybe obvious) and interesting fact is that times when there were 10+ rounds needed to reach quorums it was usually tied to some of the larger weighted finalizers needing to restart and make themselves available.

The associated SQL queries to do this from the indexer db is:

Finding the stalls

Everything needing 10+ rounds, joined to the PoW block it finalized for a calendar date.

SELECT b.bft_height, b.cert_round, b.roster_size,
       ROUND(b.roster_power/1e8,2) AS roster_ctaz, b.signer_count,
       ROUND(100.0*b.signer_power/b.roster_power,2) AS pct,
       datetime(p.time,'unixepoch') AS approx_time
FROM bft_block b
LEFT JOIN pow_block p ON p.hash = b.candidate_hash
WHERE b.cert_round >= 10
ORDER BY b.bft_height;

Ruling out roster churn

Run once per flagged height, against the height before and after. Both returned zero rows for all 5.

SELECT 'joined' AS chg, hex(pub_key) FROM roster_entry
WHERE bft_height = :h
  AND pub_key NOT IN (SELECT pub_key FROM roster_entry WHERE bft_height = :h - 1)
UNION ALL
SELECT 'left', hex(pub_key) FROM roster_entry
WHERE bft_height = :h - 1
  AND pub_key NOT IN (SELECT pub_key FROM roster_entry WHERE bft_height = :h);

Staking Flows

Right now through RPC commands, you can only generate the current tip through get_tfl_fat_pointer_to_bft_chain_tip, now you can query the flows through time as in the plot below. A couple of interesting facts is that only 10 times has anyone used the BeginDelegationUnbonding command to start the unstaking from a finalizer, and then only 6 times has stake actually been withdrawn using WithdrawDelegationBond. Probably another place that should have a few more tests done in this phase of the testnet.

SQL code to get the results:

Daily cumulative stake bonded

Calendar-filled the same way as the mining chart, so the line stays continuous through days with no new bond.

WITH RECURSIVE
bounds AS (
  SELECT date(MIN(p.time),'unixepoch') d0, date(MAX(p.time),'unixepoch') d1
  FROM staking_action s JOIN pow_block p ON p.height = s.height
),
calendar(day) AS (
  SELECT d0 FROM bounds
  UNION ALL
  SELECT date(day, '+1 day') FROM calendar, bounds WHERE day < d1
),
created AS (
  SELECT date(p.time,'unixepoch') AS day, SUM(s.amount_zats) AS z
  FROM staking_action s JOIN pow_block p ON p.height = s.height
  WHERE s.kind_name = 'CreateNewDelegationBond'
  GROUP BY day
)
SELECT c.day,
       SUM(COALESCE(cr.z, 0)) OVER (ORDER BY c.day) / 1e8 AS cumulative_ctaz
FROM calendar c LEFT JOIN created cr ON cr.day = c.day
ORDER BY c.day;

Mining Behavior/Concentration

The last thing wanted to share since it is relatively proportional to the incentives for the testnet is the mining concentration from the top 10 miners since genesis of the testnet. So far a total of 2,579,050 ctaz has been mined and the top 10 miners claim about ~81% of that amount. Interesting things to point out on the plots is some miners who have either pointed GPUs/ASICs to the testnet in mid-August, based on the rate of change of mining rewards for a couple of the top miners (notably the gold, teal, and green colored lines that prior to August had almost no mining rewards associated with those addresses.

SQL Code to get the results:

WITH RECURSIVE
top_miners AS (
  SELECT miner_address, SUM(subsidy_zats) AS total_zats
  FROM pow_block
  WHERE miner_address IS NOT NULL AND height > 0
  GROUP BY miner_address
  ORDER BY total_zats DESC
  LIMIT 10
),
bounds AS (
  SELECT date(MIN(time),'unixepoch') AS d0, date(MAX(time),'unixepoch') AS d1
  FROM pow_block WHERE height > 0
),
calendar(day) AS (
  SELECT d0 FROM bounds
  UNION ALL
  SELECT date(day, '+1 day') FROM calendar, bounds WHERE day < d1
),
daily AS (
  SELECT date(time,'unixepoch') AS day, miner_address, SUM(subsidy_zats) AS day_zats
  FROM pow_block
  WHERE height > 0 AND miner_address IN (SELECT miner_address FROM top_miners)
  GROUP BY day, miner_address
),
grid AS (
  SELECT c.day, m.miner_address
  FROM calendar c CROSS JOIN top_miners m
),
filled AS (
  SELECT g.day, g.miner_address, COALESCE(d.day_zats, 0) AS day_zats
  FROM grid g LEFT JOIN daily d ON d.day = g.day AND d.miner_address = g.miner_address
)
SELECT day, miner_address,
       ROUND(SUM(day_zats) OVER (PARTITION BY miner_address ORDER BY day) / 1e8, 4)
         AS cumulative_ctaz
FROM filled
ORDER BY miner_address, day;
7 Likes