clboss
clboss copied to clipboard
ZmnSCPxj
Add time buckets and forwarded and rebalanced volume
This PR (includes and) extends #228 ; only the final commits of this PR are unique ...
Addresses #229 Addresses #227
IMPORTANT - This PR makes a schema change and migrates data!
Abstract
This PR modifies the EarningsTracker schema to hold data in "buckets", one per day. This PR only modifies the collection and storage, the strategies and existing status continue to use earnings data from all time for now.
Motivation
As described in #227 there are several shortcomings w/ the current storage of EarningsTracker data:
- Ancient data can dilute/overwhelm current data
- Users would like income/expense reports for specific periods of time (especially recent periods)
- The data doesn't "heal" as bugs are fixed and strategies are improved.
This PR enables time-based data earnings collection, future PR's should investigate modifying the strategies and reports to take advantage of the ability to filter by time range.
Specification
This PR updates the EarningsTracker table to have a "time bucket" column. The timestamps of incoming fee and balance events are quantized to a time bucket, currently one day.
The existing strategies and reports use the sql SUM to return the same answers that they would with the current schema.
The time and nodeid columns are indexed for efficient operations.
CREATE TABLE IF NOT EXISTS EarningsTracker
( node TEXT NOT NULL
, time_bucket REAL NOT NULL
, in_earnings INTEGER NOT NULL
, in_expenditures INTEGER NOT NULL
, out_earnings INTEGER NOT NULL
, out_expenditures INTEGER NOT NULL
, PRIMARY KEY (node, time_bucket)
);
CREATE INDEX IF NOT EXISTS
idx_earnings_tracker_node_time ON EarningsTracker (node, time_bucket);
CREATE INDEX IF NOT EXISTS
idx_earnings_tracker_time_node ON EarningsTracker (time_bucket, node);
Existing legacy data is migrated into the table with a time bucket value of 0. This allows it to be considered and used but eventually be aged out.
Two new CLI commands are added to allow inspecting the new data:
-
clboss-recent-earnings:-
Purpose: Returns a data structure equivalent to the
offchain_earnings_trackercollection inclboss-status, but only includes recent earnings and expenditures. -
Arguments:
-
days(optional): Specifies the number of days to include in the report. Defaults to a fortnight (14 days) if not provided.
-
-
Purpose: Returns a data structure equivalent to the
-
clboss-earnings-history:- Purpose: Provides a daily breakdown of earnings and expenditures.
-
Arguments:
-
nodeid(optional): Limits the history to a particular node if provided. Without this argument, the values are accumulated across all peers.
-
-
Output:
- The history consists of an array of records showing the earnings and expenditures for each day.
- The history includes an initial record with a time value of 0, which contains any legacy earnings and expenditures collected by CLBOSS before daily tracking was implemented.
This allows effective feerates (PPM) to be computed for earnings and expenses.
This PR updates the schema automatically. Downgrading to previous will require manual DB migration (but is possible).
Adds two scripts which summarize earnings data. The first summarizes earning history by date:
cln@prod0:~$ ./clboss-earnings-history
+------------+---------------+------+------------+---------------+-------+-----------+--------------+
| Date | Forwarded | Rate | Earnings | Rebalanced | Rate | Expense | Net Earnings |
+------------+---------------+------+------------+---------------+-------+-----------+--------------+
| Legacy | 0 | 0 | 14_518_178 | 0 | 0 | 2_353_034 | 12_165_144 |
| 2024-08-10 | 0 | 0 | 26_029 | 0 | 0 | 0 | 26_029 |
| 2024-08-11 | 0 | 0 | 47_357 | 0 | 0 | 0 | 47_357 |
| 2024-08-12 | 0 | 0 | 30_054 | 0 | 0 | 76_458 | -46_404 |
| 2024-08-13 | 0 | 0 | 2_798_957 | 0 | 0 | 0 | 2_798_957 |
| 2024-08-14 | 0 | 0 | 79_788 | 0 | 0 | 1_327_020 | -1_247_232 |
| 2024-08-15 | 0 | 0 | 24_827 | 0 | 0 | 81_179 | -56_352 |
| 2024-08-16 | 5_716_719_980 | 351 | 2_006_680 | 649_422_200 | 1,190 | 772_855 | 1_233_825 |
| 2024-08-17 | 2_178_796_021 | 394 | 857_664 | 429_497_800 | 156 | 66_936 | 790_728 |
| ---- | --------- | ---- | -------- | ---------- | ---- | ------- | ------------ |
| TOTAL | 7_895_516_001 | | 20_389_534 | 1_078_920_000 | | 4_677_482 | 15_712_052 |
+------------+---------------+------+------------+---------------+-------+-----------+--------------+
The second summarizes the recent earnings data:
cln@prod0:~$ ./clboss-recent-earnings 1
+--------------------+---------------+-------+-----------+---------------+------+-----------+---------------+-------+---------+---------------+-------+---------+-----------+
| Alias | In Forwarded | Rate | In Earn | Out Forwarded | Rate | Out Earn | In Rebal | Rate | In Exp | Out Rebal | Rate | Out Exp | Net Earn |
+--------------------+---------------+-------+-----------+---------------+------+-----------+---------------+-------+---------+---------------+-------+---------+-----------+
| SilentBob | 0 | 0 | 0 | 3_923_897_717 | 696 | 2_732_514 | 0 | 0 | 0 | 640_091_200 | 1,203 | 769_885 | 1_962_629 |
| Luna | 1_050_300_100 | 791 | 831_251 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 831_251 |
| U got IT | 841_647_852 | 958 | 806_085 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 806_085 |
| LQwD-Indonesia | 1_707_846_541 | 441 | 752_371 | 2_253_593_891 | 29 | 66_246 | 0 | 0 | 0 | 412_235_400 | 150 | 61_816 | 756_801 |
| NodeMcNodyface2000 | 952_854_280 | 164 | 156_665 | 1_310_011_343 | 16 | 20_830 | 0 | 0 | 0 | 26_593_400 | 304 | 8_090 | 169_405 |
| PinsLNN_01 | 225_026_794 | 443 | 99_680 | 202_919_000 | 23 | 4_700 | 0 | 0 | 0 | 0 | 0 | 0 | 104_380 |
| p.zax.cloud | 80_680_801 | 1,019 | 82_180 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 82_180 |
| Play-Asia.com | 149_076_430 | 442 | 65_818 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 65_818 |
| Start9 Donations | 0 | 0 | 0 | 50_000_000 | 536 | 26_775 | 0 | 0 | 0 | 0 | 0 | 0 | 26_775 |
| silent.link | 58_717_000 | 21 | 1_230 | 48_566_440 | 120 | 5_843 | 0 | 0 | 0 | 0 | 0 | 0 | 7_073 |
| LQwD-Japan-Osaka | 72_933_625 | 22 | 1_595 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1_595 |
| pay.p2sh.co | 0 | 0 | 0 | 89_926_148 | 13 | 1_134 | 0 | 0 | 0 | 0 | 0 | 0 | 1_134 |
| STACKER.NEWS | 25_440_000 | 21 | 539 | 10_000_000 | 42 | 422 | 0 | 0 | 0 | 0 | 0 | 0 | 961 |
| DeutscheBank|CLN | 21_186_250 | 41 | 869 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 869 |
| sats_distributor | 22_807_000 | 22 | 507 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 507 |
| MindlinerTre | 17_807_347 | 13 | 226 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 226 |
| aybabtu | 0 | 0 | 0 | 6_601_462 | 12 | 79 | 0 | 0 | 0 | 0 | 0 | 0 | 79 |
| slicksparks.ky | 2_107_096_453 | 23 | 47_635 | 0 | 0 | 0 | 346_639_000 | 1,193 | 413_391 | 0 | 0 | 0 | -365_756 |
| EverNukeNode | 562_095_528 | 31 | 17_693 | 0 | 0 | 5_801 | 732_281_000 | 582 | 426_400 | 0 | 0 | 0 | -402_906 |
| ----- | ------------ | ---- | ------- | ------------- | ---- | -------- | -------- | ---- | ------ | --------- | ---- | ------- | -------- |
| TOTAL | 7_895_516_001 | 363 | 2_864_344 | 7_895_516_001 | 363 | 2_864_344 | 1_078_920_000 | 778 | 839_791 | 1_078_920_000 | 778 | 839_791 | 2_024_553 |
+--------------------+---------------+-------+-----------+---------------+------+-----------+---------------+-------+---------+---------------+-------+---------+-----------+
