cardano-cli and db-sync show different number of rewards from the epoch 243

[dmitrystas]

Starting from the 243 epoch for some reward addresses cardano-cli and db-sync show a different number of rewards. For example stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0

cardano-cli

$ cardano-cli query stake-address-info --cardano-mode --mainnet --allegra-era --address stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0
[
    {
        "address": "stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0",
        "delegation": "pool14ku0z9rx9yx34zl00jtxtpyv6ehytfl8fewztavx7qvju7nrtr8",
        "rewardAccountBalance": 13258955846
    }
]

and db-sync

# SELECT SUM(reward.amount)
# FROM stake_address
# LEFT JOIN reward ON reward.addr_id = stake_address.id
# WHERE stake_address.view = 'stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0';
     sum
-------------
 13222415370
(1 row)

The difference is 36.540476 ₳ , which is approximately equal to the vote reward amount.

mainnet, cardano-cli 1.24.2, cardano-db-sync 7.1.0

[dmitrystas]

It looks like these voting rewards are in the 'treasury' table now

# SELECT treasury.amount
# FROM stake_address
# LEFT JOIN treasury ON treasury.addr_id = stake_address.id
# WHERE stake_address.view = 'stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0';
  amount
----------
 36540476
(1 row)

[erikd]

It looks like these voting rewards are in the 'treasury' table now

Oops, that looks like a mistake. Apparently not.

[dcoutts]

We need to somehow clarify (in the schema and/or related docs) that there's more than one table of events that affect the balance reward accounts:

• per-epoch pool rewards
• account withdrawals
• special MIR events. Special MIR events come from MIR certs, but are scheduled for block boundaries.

So people writing queries to track the total balance will need to take the union of the events.

The MIR certs have been used for various high level purposes (but the purpose is of course not posted on the chain), including: the rewards due to the ITN, treasury payouts for winning projects, treasury payouts for taking part in Catalyst.

[erikd]

I think part of the problem is that a table named reward could reasonably be expected to contain all rewards, but that is actually not the case. This table only contains staking rewards. It might be desirable to rename this table to reflect that.

This is made more confusing in that the withdrawal table contains all withdrawals from the rewards account.

[erikd]

I have been trying to come up with the neatest approach to this I can find.

First we create a view:

# create view reward_payment_view as
    select addr_id, amount, 'treasury' as source
    from treasury union select addr_id, amount, 'reward_epoch_' || epoch_no as source from reward ; 

the we can query this view as:

# select stake_address.view, reward_payment_view.amount, reward_payment_view.source
    from reward_payment_view inner join stake_address on reward_payment_view.addr_id = stake_address.id
    where stake_address.view = 'stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0' ; 
                            view                             |   amount   |      source      
-------------------------------------------------------------+------------+------------------
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |   36540476 | treasury
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  373554942 | reward_epoch_226
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  381550960 | reward_epoch_222
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  394285344 | reward_epoch_225
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  394335813 | reward_epoch_223
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  437204214 | reward_epoch_236
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  442787046 | reward_epoch_233
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  450575175 | reward_epoch_238
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  463125272 | reward_epoch_228
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  481675128 | reward_epoch_224
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  495852458 | reward_epoch_227
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  571559879 | reward_epoch_234
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  599359585 | reward_epoch_232
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  612352961 | reward_epoch_237
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 |  658610556 | reward_epoch_231
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 | 1319746538 | reward_epoch_219
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 | 1342842199 | reward_epoch_218
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 | 1374758087 | reward_epoch_216
 stake1u9z07jwyrlyh57dsvhmmdlt555vpvwdrky0ykf4le4k7zuc3pvuy0 | 2428239213 | reward_epoch_220
(19 rows)

Is that a view that I should ship predefined in db-sync ?

[erikd]

@dmitrystas Can this be closed now?

[dmitrystas]

I think so, thanks