Additional improvements for the stats RPCs

[jamaljsr]

There are currently two universe RPC methods (QueryAssetStats and UniverseStats) which return stats about the assets the node is aware of. As I am working on exposing this information in Terminal, I've discovered some improvements that could be made to improve the dev/user experience.

1. These two endpoints currently require a macaroon to access them. We want to be able to query the LL universe server without requiring the user to authenticate. There are two options that I can think of to solve this, but I'm open to other suggestions.
   1. add a config flag which allows anyone to access these specific RPC methods without needing to supply a macaroon
   2. bake a custom macaroon with limited access to these RPCs only and hard-code that into the Terminal app
2. QueryAssetStats can currently sort by asset id, name, and type. We also would like to be able to sort by total_syncs, total_proofs, genesis_height
3. In QueryAssetStats, also include the fields genesis_point and group_key for each asset.
4. In QueryAssetStats, each asset has a genesis_height field. Would it be possible to also include the timestamp of this block so that the web app doesn't need to make N more API requests to convert the block height to a timestamp? We'd like to display the date/time of when the asset was created.
5. We'd like to display a chart showing the number of total assets over time in daily increments. Given a start and end date, the RPC would return an array of objects containing the fields: timestamp, total_assets, total_syncs, total_proofs. There would be one entry for each day between the supplied dates.

[Roasbeef]

For #5, here's the queries we had for sqlite + postgres, we need to add a slim abstraction so we an define an interfaces and have an implementation for sqlite vs postges:

sqlite

SELECT
    strftime('%Y-%m-%d %H:%M:00', event_time, '-5 minutes') AS interval_start,
    SUM(CASE WHEN event_type = 'SYNC' THEN 1 ELSE 0 END) AS total_sync_events,
    SUM(CASE WHEN event_type = 'NEW_PROOF' THEN 1 ELSE 0 END) AS total_new_proof_events
FROM
    universe_events
WHERE
    event_type IN ('SYNC', 'NEW_PROOF') AND -- Specify the event types you're interested in
    event_time >= @start_time AND event_time <= @end_time -- Filter events based on the time window
GROUP BY
    strftime('%Y-%m-%d %H:%M:00', event_time, '-5 minutes') / 5 * 5,
    strftime('%Y-%m-%d %H', event_time)
ORDER BY
    interval_start;

postgres:

    SELECT
    date_trunc('minute', event_time) + INTERVAL '5 minutes' * FLOOR(EXTRACT(MINUTE FROM event_time) / 5) AS interval_start,
    SUM(CASE WHEN event_type = 'SYNC' THEN 1 ELSE 0 END) AS total_sync_events,
    SUM(CASE WHEN event_type = 'NEW_PROOF' THEN 1 ELSE 0 END) AS total_new_proof_events
FROM
    universe_events
WHERE
    event_type IN ('SYNC', 'NEW_PROOF') AND -- Specify the event types you're interested in
    event_time >= @start_time AND event_time <= @end_time -- Filter events based on the time window
GROUP BY
    interval_start
ORDER BY
    interval_start;

[jamaljsr]

The first item appears to already be resolved. I recently learned that there is a testnet universe REST server setup already which doesn't require a macaroon to access.

[guggero]

I pushed up a (very) early WIP branch here: https://github.com/lightninglabs/taproot-assets/tree/asset-stats-improvements

Basically it's a bit more involved than initially thought:
We currently don't have the genesis_height, as we don't store that when importing a minting proof from a universe (because the proof itself doesn't have the genesis block height). So this PR depends on the same change we need for the re-org safety changes: We need to store the block height (and not just the block hash) in asset proofs.

But fortunately the whole "different queries for SQLite and Postgres" worked out pretty well. Added a commit for detecting the database backend at runtime. Also, the queries are a bit simpler as shown above as we can just use the unix timestamp directly.

[guggero]

We'd like to display a chart showing the number of total assets over time in daily increments. Given a start and end date, the RPC would return an array of objects containing the fields: timestamp, total_assets, total_syncs, total_proofs. There would be one entry for each day between the supplied dates.

@jamaljsr in #396 I did not include total_assets yet, because I was not really sure what value to pick. What did you expect this to contain? The number of assets created during a certain day? Then I think this would be equal to total_proofs, as we only currently store genesis proofs in the universe (so each genesis proof is equal to one new asset). Or do you expect total_assets to contain the total number of assets that existed at the start of that day? So an absolute number?

[jamaljsr]

@guggero Ah, I thought that a proof represented a transfer of an asset even after the genesis. So total_proofs would be the total number of transfers of all assets for that day. This is what we were hoping to display on the website. Is this data not available?

I expected total_assets to be the total number of unique assets that the universe server was aware of at the start of that day.

[Roasbeef]

Ah, I thought that a proof represented a transfer of an asset even after the genesis.

Once we finish the multi-verse feature (cc @ffranr) that'll be the case. Until then, it's just the total amount of total assets created in that interval. We have an event type (rn just: new proof + new log), so we can use that to expose transfers in the future.

[jamaljsr]

Ah ok. Thanks for that clarification.