ePoints Indexer

Currently tracking:

• eBABY
  • Babylon
    • HODL points
    • DEFI LP (TowerFi) points (DISABLED)
    • DEFI SWAP (TowerFi) points (DISABLED)
    • Lottery tickets (DISABLED)
  • Ethereum
    • HODL points
    • DEFI LP (Uniswap V3) points (DISABLED)
    • Lottery tickets (DISABLED)
• eU
  • Ethereum
    • HODL points
    • DEFI LP (Uniswap V3) points
    • Lottery tickets (DISABLED)

• All points ($eP$) are tracked and updated every ~1 hour
  • 360 blocks on Babylon
  • 300 blocks on Ethereum
• All fetched prices ($p$) are also roughly hourly, not exact (the closest hour to that transaction)
• All lottery tickets ($eT$) are tracked and updated every ~24 hours, same thing with the prices used there
  • 8640 blocks on Babylon
  • 7200 blocks on Ethereum
• Check config.json for the current values mentioned bellow (e.g. minimum_usd and multiplier, etc.)

====
HODL points if USER bonds and receives eBABY worth 110 USD
====

87459                       87819                       88179                       88539                         ...
|------- 360 blocks -------||------- 360 blocks -------||------- 360 blocks -------||------- 360 blocks -------|| ...
0 eP        ^               0 eP                      1.1 eP                      2.2 eP
0 eBABY     ^             110 USD in eBABY            110 USD in eBABY            110 USD in eBABY
            ^
            Bond @ 87535
            Receives X eBABY ~ 110 USD

• Tracked between block ranges: eBABY balance
• $B_{old}$ User's eBABY balance @ the start of the block range
• $B_{new}$ User's eBABY balance @ the end of the block range
• $m$ minimum_usd
• $M$ multiplier
• $p$ eBABY price in USD

$$ \begin{aligned} A &= \min(B_{old}, B_{new}) \\ X &= \frac{ A \cdot p }{ m } \\ eP_{HODL} &= \begin{cases} M \cdot X, & \text{if } X \geq 1 \\ 0, & \text{otherwise} \end{cases} \end{aligned} $$

• Tracked between block ranges: LPT position (for each tracked pool)
• $L_{old}$ User's LPT position @ the start of the block range
• $L_{new}$ User's LPT position @ the end of the block range
• $r$ Ratio between the both tokens balance of the pool (eBABY/tokenOther ratio)
• $m$ minimum_usd
• $M$ multiplier
• $p$ eBABY price in USD

$$ \begin{aligned} A &= \min(L_{old}, L_{new}) \cdot (1 + r) \\ X &= \frac{ A \cdot p }{ m } \\ eP_{LP} &= \begin{cases} M \cdot X, & \text{if } X \geq 1 \\ 0, & \text{otherwise} \end{cases} \end{aligned} $$

• Tracked between block ranges: LPT position (for each tracked pool)
• $L_{old}$ User's LPT position @ the start of the block range
• $L_{new}$ User's LPT position @ the end of the block range
• $T_{e}$ Total eBABY in the pool @ time of tx
• $T_{x}$ Total tokenOther in the pool @ time of tx
• $p_{e}$ eBABY price in USD
• $p_{x}$ tokenOther price in USD
• $T_{LPT}$ LPT's total supply @ the end of the block range
• $P$ Calculated using the $T_{e}$ and $T_{x}$ @ the end of the block range
• $m$ minimum_usd
• $M$ multiplier

$$ \begin{aligned} A &= \frac{ \min(L_{old}, L_{new}) }{ T_{LPT} } \\ P &= (T_{e} \cdot p_{e}) + (T_{x} \cdot p_{x}) \\ X &= \frac{ A \cdot P }{ m } \\ eP_{LP} &= \begin{cases} M \cdot X, & \text{if } X \geq 1 \\ 0, & \text{otherwise} \end{cases} \end{aligned} $$

• All swaps of the user between the ranged are parsed
• $net_{in}$ Sum of the user's swapped amount in to the pool
• $net_{out}$ Sum of the user's swapped amount out of the pool
• $m$ minimum_usd
• $M$ multiplier
• $p$ eBABY price in USD

$$ \begin{aligned} A &= net_{in} + net_{out} \\ X &= \frac{ A \cdot p }{ m } \\ eP_{SWAP} &= \begin{cases} M \cdot X, & \text{if } X \geq 1 \\ 0, & \text{otherwise} \end{cases} \end{aligned} $$

• Tracked between block ranges: eBABY USD values
• $i$ Refers to the kind of values used to compute the tickets
  • $i=0$ HODL USD value
  • $i=1$ DEFI_LP USD value
• $B_{ih}$ User's eBABY computed $i$-th USD value @ $h$-th relative block range
• $m_{i}$ $i$-th minimum_usd
• $b_{i}$ $i$-th base_usd
• $w_{i}$ $i$-th whale_usd
• $d_{i}$ $i$-th whale_damping_factor
• $s$ step_multiplier
• $p$ eBABY price in USD

$$ \begin{aligned} A_{i} &= \frac{1}{s} \cdot \sum_{h=-s-1}^{0} B_{ih} \\ X_{i} &= A_{i} \cdot p \\ eT &= \sum_{i=0}^{1} \begin{cases} 0, & \text{if } X \lt m_{i} \\ 1 + \left\lfloor \dfrac{ (X_{i} - m_{i}) }{ b_{i} } \right\rfloor, & \text{if } X \leq w_{i} \\ 1 + \left\lfloor \dfrac{ w_{i} - m_{i} }{ b_{i} } \right\rfloor + \left\lfloor \left(\dfrac{ X_{i} - w_{i} }{ b_{i} }\right)^{d_{i}} \right\rfloor, & \text{otherwise} \end{cases} \end{aligned} $$

cargo b --release

cargo t

cargo sqlx prepare

docker build -t points-indexer .

select
  coalesce(baby_lp_agg.user_address, baby_hodl.user_address, eth_hodl.user_address, eth_lp_agg.user_address, extra.user_address) as user_address,
  coalesce(baby_lp_agg.total_defi_lp, 0) + coalesce(baby_hodl.total_hodl, 0) + coalesce(eth_hodl.total_hodl, 0) + coalesce(eth_lp_agg.total_defi_lp, 0) + coalesce(extra.total_extra, 0) as total_points
from
  (
  select
      user_address,
      SUM(total_defi_lp) as total_defi_lp
  from
      babylon_user_point_defi_lp
  where
      lp_address in ('bbn1hs95lgvuy0p6jn4v7js5x8plfdqw867lsuh5xv6d2ua20jprkgeslpzjvl', 'bbn1jwd3e9smv9n7p20fud8ll9erz6ave95hn7e4w25sv9n450tpg3vqsqeg3d')
  group by
      user_address
  ) baby_lp_agg
full outer join babylon_user_point_hodl baby_hodl
  on
  baby_lp_agg.user_address = baby_hodl.user_address
full outer join ethereum_user_point_hodl eth_hodl
  on
  coalesce(baby_lp_agg.user_address, baby_hodl.user_address) = eth_hodl.user_address
full outer join (
  select
    user_address,
    SUM(total_defi_lp) as total_defi_lp
  from
    ethereum_user_point_defi_lp
  where
    lp_address in ('0xd4c29179bcf2a835d404dabbbe71880010e50ce0', '0xb759f938814c8b7a24344d75fa3fa4add89bdad2')
  group by
    user_address
) eth_lp_agg
  on
  coalesce(baby_lp_agg.user_address, baby_hodl.user_address, eth_hodl.user_address) = eth_lp_agg.user_address
full outer join user_point_extra extra
  on
  coalesce(baby_lp_agg.user_address, baby_hodl.user_address, eth_hodl.user_address, eth_lp_agg.user_address) = extra.user_address
order by
  total_points desc
limit 10;

select
  coalesce(baby_lp_agg.total_defi_lp, 0) + coalesce(baby_hodl.total_hodl, 0) + coalesce(eth_hodl.total_hodl, 0) + coalesce(eth_lp_agg.total_defi_lp, 0) + coalesce(extra.total_extra, 0) as total_points
from
  (
  select
      SUM(total_defi_lp) as total_defi_lp
  from
      babylon_user_point_defi_lp
  where
      lp_address in ('bbn1hs95lgvuy0p6jn4v7js5x8plfdqw867lsuh5xv6d2ua20jprkgeslpzjvl', 'bbn1jwd3e9smv9n7p20fud8ll9erz6ave95hn7e4w25sv9n450tpg3vqsqeg3d')
    and user_address = '{BABYLON_USER_ADDRESS}'
  ) baby_lp_agg
left join (
  select
    total_hodl
  from
    babylon_user_point_hodl
  where
    user_address = '{BABYLON_USER_ADDRESS}'
) baby_hodl on
  true
left join (
  select
    total_hodl
  from
    ethereum_user_point_hodl
  where
    user_address = '{ETHEREUM_USER_ADDRESS}'
) eth_hodl on
  true
left join (
  select
    SUM(total_defi_lp) as total_defi_lp
  from
    ethereum_user_point_defi_lp
  where
    lp_address in ('0xd4c29179bcf2a835d404dabbbe71880010e50ce0', '0xb759f938814c8b7a24344d75fa3fa4add89bdad2')
      and user_address = '{ETHEREUM_USER_ADDRESS}'
) eth_lp_agg on
  true
left join (
  select
    SUM(total_extra) as total_extra
  from
    user_point_extra
  where
    user_address in ('{BABYLON_USER_ADDRESS}', '{ETHEREUM_USER_ADDRESS}')
) extra on
  true;

select SUM(tickets) as total_tickets from user_lottery_ticket;

select
  DATE(datetime) as ticket_date,
  SUM(tickets) as total_tickets,
  COUNT(distinct user_address) as unique_users
from
  user_lottery_ticket
group by
  DATE(datetime)
order by
  ticket_date desc;

select
  user_address,
  SUM(tickets) as total_tickets
from
  user_lottery_ticket
group by
  user_address
order by
  total_tickets desc;