Change Bookkeeper's sql queries to rpc calls with data filtering/grouping

Key Decision Factors:

Criteria                               SQL plugin                                          RPCs
Coupling                               Tightly coupled with DB schema and field name       Not directly dependent upon schema
Simplicity                             Native join/group by/filter                         Extra Code for join/group by/filter in TypeScript
Scalability                            Less scalable                                       Easy to add caching/pagination
Performance                            SQL optimized                                       Slightly Slower due to client-side processing

Sats Flow:

Current:

SELECT account, tag, credit_msat, debit_msat, currency, timestamp, description, outpoint, txid, payment_id FROM bkpr_income WHERE bkpr_income.timestamp BETWEEN 1746383400 AND 1749061799;

CLI Equivalent: (Issue #8318)

lightning-cli bkpr-listincome -k start_time=1743445800 end_time=1746037799

Account Snapshot:

Current:

SELECT peerchannels.short_channel_id, nodes.alias, bkpr_accountevents.credit_msat, bkpr_accountevents.debit_msat, bkpr_accountevents.account, bkpr_accountevents.timestamp FROM bkpr_accountevents LEFT JOIN peerchannels ON upper(bkpr_accountevents.account)=hex(peerchannels.channel_id) LEFT JOIN nodes ON peerchannels.peer_id=nodes.nodeid WHERE bkpr_accountevents.type != 'onchain_fee' AND bkpr_accountevents.account != 'external';

CLI Equivalent:

lightning-cli bkpr-listaccountevents | jq '[.events[] | select(.type != "onchain_fee" and .account != "external")]'

Enhancement: Add start and end time filtering like bkpr-listincome like below (Issue #8319 and #8355)

SELECT peerchannels.short_channel_id, nodes.alias, bkpr_accountevents.credit_msat, bkpr_accountevents.debit_msat, bkpr_accountevents.account, bkpr_accountevents.timestamp FROM bkpr_accountevents LEFT JOIN peerchannels ON upper(bkpr_accountevents.account)=hex(peerchannels.channel_id) LEFT JOIN nodes ON peerchannels.peer_id=nodes.nodeid WHERE bkpr_accountevents.type != 'onchain_fee' AND bkpr_accountevents.account != 'external' AND bkpr_accountevents.timestamp BETWEEN 1746383400 AND 1749061799;

Volume Chart:

Current:

SELECT in_channel, (SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=in_channel) AS in_channel_peerid, (SELECT nodes.alias FROM nodes WHERE nodes.nodeid=(SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=in_channel)) AS in_channel_peer_alias, SUM(in_msat), out_channel, (SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=out_channel) AS out_channel_peerid, (SELECT nodes.alias FROM nodes WHERE nodes.nodeid=(SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=out_channel)) AS out_channel_peer_alias, SUM(out_msat), SUM(fee_msat) FROM forwards WHERE forwards.status='settled' GROUP BY in_channel, out_channel;

CLI Equivalent:

lightning-cli listforwards -k status="settled" | jq '.forwards | group_by(.in_channel, .out_channel)'

Enhancement: Add start and end time filtering like bkpr-listincome like below (Issue #8355)

SELECT in_channel, (SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=in_channel) AS in_channel_peerid, (SELECT nodes.alias FROM nodes WHERE nodes.nodeid=(SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=in_channel)) AS in_channel_peer_alias, SUM(in_msat), out_channel, (SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=out_channel) AS out_channel_peerid, (SELECT nodes.alias FROM nodes WHERE nodes.nodeid=(SELECT peer_id FROM peerchannels WHERE peerchannels.short_channel_id=out_channel)) AS out_channel_peer_alias, SUM(out_msat), SUM(fee_msat) FROM forwards WHERE forwards.status='settled' AND forwards.resolved_time BETWEEN 1738000000 AND 1749061799 GROUP BY in_channel, out_channel;