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

[ShahanaFarooqui]

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;

[evansmj]

Interesting @ShahanaFarooqui so the main reason to switch is for pagination?

[ShahanaFarooqui]

Interesting @ShahanaFarooqui so the main reason to switch is for pagination?

Beyond the four reasons already discussed, my main motivation for transitioning to RPCs is:

• Maintainability and Developer Accessibility: The SQL plugin serves niche (application specific queries) use cases well, but widely applicable functionality should be standardised in CLN’s RPC suite. Our three bookkeeping queries appear generic enough to benefit the wider developer community, making them ideal candidates for inclusion in Core Lightning's native RPC suite rather than requiring custom SQL.

[evansmj]

o ok i see, its so others can call the rpc's and inspect them or make other apps use these calls, whereas now they have to come up with sql and further calculations. sounds good

[ShahanaFarooqui]

Also, cln-grpc doesn't allow sql queries.