List payments of a CLN lightning node greater than 20000sat
LNROOM #7・December 09, 2022
In this episode we see how to list the payments that are greater than 20000sat of a node running on regtest with CLN using two methods: (1) sqlite and (2) the command listpays combined with the utility jq.
Transcript with corrections and improvements
Hi guys, welcome to the LN Room, I'm Tony Aldon and today in this episode 7 we are going to see how to list the payments of a node that are greater than 20000sat using two methods:
using
sqliteandusing the sub-command
listpaysoflightning-clicombined with the utilityjq.
This episode is break down in 3 parts:
we make 3 invoices and 3 payments,
we list the payments greater than 20000sat using
sqliteandwe list the payments greater than 20000sat using
listpaysandjq.
Let's go.
Start 2 Lightning nodes running on regtest, connect them and open a channel
Let's jump into our terminal.
We use as in the previous episode the script
contrib/startup_regtest.sh that provides commands and aliases that
facilitate testing CLN with the nodes running on regtest.
First we source it:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ source contrib/startup_regtest.sh
lightning-cli is /usr/bin/lightning-cli
lightningd is /usr/bin/lightningdThen with this following command we start bitcoind on regtest with one
wallet named default and also two lightning nodes l1 and l2:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ start_ln 2
Bitcoin Core starting
awaiting bitcoind...
Making "default" bitcoind wallet.
[1] 12642
[2] 12674
Commands:
l1-cli, l1-log,
l2-cli, l2-log,
bt-cli, stop_ln, fund_nodesWe can check that the base directory of the node l1 is /tmp/l1-regtest
by running the following command:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ alias l1-cli
alias l1-cli='lightning-cli --lightning-dir=/tmp/l1-regtest'Now we connect the two nodes:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ connect 1 2
{
"id": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"features": "08a000080269a2",
"direction": "out",
"address": {
"type": "ipv4",
"address": "127.0.0.1",
"port": 7272
}
}and we opened a channel from the node l1 to the node l2 with an amount
of 1000000sat like this:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ fund_nodes 1 2
Mining into address bcrt1qy033sqv02ndlgu6p2kwh0ap6qec9w57fehtuq6... done.
bitcoind balance: 50.00000000
Waiting for lightning node funds... found.
Funding channel from node 1 to node 2. Waiting for confirmation... done.With the command listfunds we can check that we've opened a channel
with the node l2:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listfunds
{
"outputs": [
{
"txid": "7cc98b7397429e86af93ea95172a3fdccec2b129081897a0640bc35865fb9cb5",
"output": 0,
"amount_msat": 98999846000,
"scriptpubkey": "00147fc11f2348e4ad570c5850310f5c5ddf81a668ab",
"address": "bcrt1q0lq37g6gujk4wrzc2qcs7hzam7q6v69tatyuac",
"status": "confirmed",
"blockheight": 103,
"reserved": false
}
],
"channels": [
{
"peer_id": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"connected": true,
"state": "CHANNELD_NORMAL",
"short_channel_id": "103x1x1",
"our_amount_msat": 1000000000,
"amount_msat": 1000000000,
"funding_txid": "7cc98b7397429e86af93ea95172a3fdccec2b129081897a0640bc35865fb9cb5",
"funding_output": 1
}
]
}Make 3 invoices and 3 payments
In this section, the node l2 create 3 invoices with the following
amounts 20000sat, 40000sat and 60000sat. And the node l1 pay those 3
invoices. To do so we use the sub-command invoice and pay of
lightning-cli. (You can check LN Room #1 for more detail on invoices)
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l2-cli invoice 20000sat inv-1 "pizza-1"
{
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"expires_at": 1671214214,
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"payment_secret": "aae3498127708721658f6c035a56b783085f0974c48a133bf9201511c11b9b4d",
"warning_deadends": "Insufficient incoming capacity, once dead-end peers were excluded"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli pay lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc
{
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"created_at": 1670609436.548,
"parts": 1,
"amount_msat": 20000000,
"amount_sent_msat": 20000000,
"payment_preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"status": "complete"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l2-cli invoice 40000sat inv-2 "pizza-2"
{
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"expires_at": 1671214257,
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"payment_secret": "9c4ecdc9235284eda4a627acdacf781a8149dad3dcf4d3181d6e90a7fc675e71",
"warning_deadends": "Insufficient incoming capacity, once dead-end peers were excluded"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli pay lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u
{
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"created_at": 1670609472.101,
"parts": 1,
"amount_msat": 40000000,
"amount_sent_msat": 40000000,
"payment_preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"status": "complete"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l2-cli invoice 60000sat inv-3 "pizza-3"
{
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"expires_at": 1671214284,
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"payment_secret": "94f1b80421adadecffb1749e71483d652900abb0b17465c1014ff4986a48840c",
"warning_deadends": "Insufficient incoming capacity, once dead-end peers were excluded"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli pay lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh
{
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"created_at": 1670609502.859,
"parts": 1,
"amount_msat": 60000000,
"amount_sent_msat": 60000000,
"payment_preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"status": "complete"
}We can list those 3 payments using the sub-command listpays like this:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays
{
"pays": [
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"status": "complete",
"created_at": 1670609436,
"preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"amount_msat": 20000000,
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"status": "complete",
"created_at": 1670609472,
"preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"amount_msat": 40000000,
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"status": "complete",
"created_at": 1670609502,
"preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"amount_msat": 60000000,
"amount_sent_msat": 60000000
}
]
}List the payments greater than 20000sat using sqlite
This "experiment" we do in this video is good occasion to start playing with one of the databases on CLN.
All the data related to the node l1 are in the base directory
/tmp/l1-regtest as we can see again printing out the alias l1-cli:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ alias l1-cli
alias l1-cli='lightning-cli --lightning-dir=/tmp/l1-regtest'We can use the command tree to see its organization and note that
there is two databases accounts.sqlite3 and lightningd.sqlite3 for our
lightning node:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ tree /tmp/l1-regtest/
/tmp/l1-regtest/
├── config
├── lightningd-regtest.pid
├── log
└── regtest
├── accounts.sqlite3
├── emergency.recover
├── gossip_store
├── hsm_secret
├── lightningd.sqlite3
└── lightning-rpc
1 directory, 9 filesNow, let's start an sqlite shell loaded with the database
lightningd.sqlite3 and list its tables:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ sqlite3 /tmp/l1-regtest/regtest/lightningd.sqlite3
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite> .tables
blocks invoices
channel_blockheights offers
channel_configs outputs
channel_feerates payments
channel_funding_inflights peers
channel_htlcs penalty_bases
channel_state_changes shachain_known
channels shachains
channeltxs transaction_annotations
datastore transactions
db_upgrades utxoset
forwarded_payments vars
htlc_sigs versionThe table we are interested in is named payments. We first inspect
its schema then show all its content (we've replace binaries with
<BLOB>):
sqlite> .schema payments
CREATE TABLE payments ( id INTEGER, timestamp INTEGER, status INTEGER, payment_hash BLOB, destination BLOB, msatoshi INTEGER, payment_preimage BLOB, path_secrets BLOB, route_nodes BLOB, route_channels BLOB, failonionreply BLOB, faildestperm INTEGER, failindex INTEGER, failcode INTEGER, failnode BLOB, failchannel TEXT, failupdate BLOB, msatoshi_sent INTEGER, faildetail TEXT, description TEXT, faildirection INTEGER, bolt11 TEXT, total_msat INTEGER, partid INTEGER, groupid INTEGER NOT NULL DEFAULT 0, local_offer_id BLOB DEFAULT NULL REFERENCES offers(offer_id), paydescription TEXT, PRIMARY KEY (id), UNIQUE (payment_hash, partid, groupid));
sqlite> SELECT * FROM payments;
<BLOB>|||||||||||20000000||||lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc|0|0|1||
2|1670609472|1|<BLOB>|<BLOB>
<BLOB>|40000000|<BLOB>|||||||||||40000000||||lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u|0|0|1||
<BLOB>|60000000|<BLOB>|||||||||||60000000||||lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh|0|0|1||Now we just kept the field bolt11 and msatoshi_sent:
sqlite> SELECT bolt11, msatoshi_sent from payments;
lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc|20000000
lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u|40000000
lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh|60000000And finally we only list the payments greater than 20000sat like this and exit the sqlite shell:
sqlite> SELECT bolt11, msatoshi_sent from payments WHERE (msatoshi_sent > 20000000);
lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u|40000000
lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh|60000000
sqlite> .exitList the payments greater than 20000sat using listpays and jq
In this last section we are going to list the payments greater than
20000sat using listpays and jq.
We are going to construct the jq filter step by step to see exactly
how does it works.
First we note that listpays return an object with one attribute being
pays that has an array as value:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays | jq
{
"pays": [
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"status": "complete",
"created_at": 1670609436,
"preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"amount_msat": 20000000,
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"status": "complete",
"created_at": 1670609472,
"preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"amount_msat": 40000000,
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"status": "complete",
"created_at": 1670609502,
"preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"amount_msat": 60000000,
"amount_sent_msat": 60000000
}
]
}We can use the filter .[] on that object, this will return all the
values of the object, which our case is just the array of the payment
as we can see below:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays | jq '.[]'
[
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"status": "complete",
"created_at": 1670609436,
"preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"amount_msat": 20000000,
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"status": "complete",
"created_at": 1670609472,
"preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"amount_msat": 40000000,
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"status": "complete",
"created_at": 1670609502,
"preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"amount_msat": 60000000,
"amount_sent_msat": 60000000
}
]Now, we pipe the jq filter .[] to the filter
map({bolt11,amount_sent_msat}) which apply on each object of the
previous array and keep only the attributes with values for the
attributes bolt11 and amount_sent_msat as we can see below:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays | jq '.[] | map({bolt11,amount_sent_msat})'
[
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"amount_sent_msat": 60000000
}
]Finally we pipe the previous jq filters to another using select that
will only keep the payment greater than 20000sat:
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays | jq '.[] | map({bolt11,amount_sent_msat}) | map(select(.amount_sent_msat > 20000000))'
[
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"amount_sent_msat": 60000000
}
]This is what I wanted to share with you today.
I hope you get value from this video.
See you next time.
Terminal sessions
We ran the following commands in this order:
$ source contrib/startup_regtest.sh
$ start_ln 2
$ alias l1-cli
$ connect 1 2
$ fund_nodes 1 2
$ l1-cli listfunds
$ l2-cli help invoice
$ l2-cli invoice 20000sat inv-1 "pizza-1"
$ l1-cli pay lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc
$ l2-cli invoice 40000sat inv-2 "pizza-2"
$ l1-cli pay lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u
$ l2-cli invoice 60000sat inv-3 "pizza-3"
$ l1-cli pay lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh
$ l1-cli listpays
$ alias l1-cli
$ tree /tmp/l1-regtest/
$ sqlite3 /tmp/l1-regtest/regtest/lightningd.sqlite3
$ l1-cli listpays
$ l1-cli listpays | jq '.[]'
$ l1-cli listpays | jq '.[] | map({bolt11,amount_sent_msat})'
$ l1-cli listpays | jq '.[] | map({bolt11,amount_sent_msat}) | map(select(.amount_sent_msat > 20000000))'And below you can read the terminal session (command lines and outputs):
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ source contrib/startup_regtest.sh
lightning-cli is /usr/bin/lightning-cli
lightningd is /usr/bin/lightningd
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ start_ln 2
Bitcoin Core starting
awaiting bitcoind...
Making "default" bitcoind wallet.
[1] 12642
[2] 12674
Commands:
l1-cli, l1-log,
l2-cli, l2-log,
bt-cli, stop_ln, fund_nodes
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ alias l1-cli
alias l1-cli='lightning-cli --lightning-dir=/tmp/l1-regtest'
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ connect 1 2
{
"id": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"features": "08a000080269a2",
"direction": "out",
"address": {
"type": "ipv4",
"address": "127.0.0.1",
"port": 7272
}
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ fund_nodes 1 2
Mining into address bcrt1qy033sqv02ndlgu6p2kwh0ap6qec9w57fehtuq6... done.
bitcoind balance: 50.00000000
Waiting for lightning node funds... found.
Funding channel from node 1 to node 2. Waiting for confirmation... done.
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listfunds
{
"outputs": [
{
"txid": "7cc98b7397429e86af93ea95172a3fdccec2b129081897a0640bc35865fb9cb5",
"output": 0,
"amount_msat": 98999846000,
"scriptpubkey": "00147fc11f2348e4ad570c5850310f5c5ddf81a668ab",
"address": "bcrt1q0lq37g6gujk4wrzc2qcs7hzam7q6v69tatyuac",
"status": "confirmed",
"blockheight": 103,
"reserved": false
}
],
"channels": [
{
"peer_id": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"connected": true,
"state": "CHANNELD_NORMAL",
"short_channel_id": "103x1x1",
"our_amount_msat": 1000000000,
"amount_msat": 1000000000,
"funding_txid": "7cc98b7397429e86af93ea95172a3fdccec2b129081897a0640bc35865fb9cb5",
"funding_output": 1
}
]
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l2-cli help invoice
LIGHTNING-INVOICE(7) LIGHTNING-INVOICE(7)
NAME
lightning-invoice -- Command for accepting payments
SYNOPSIS
invoice amount_msat label description [expiry] [fallbacks] [preimage] [exposeprivate-
channels] [cltv] [deschashonly]
DESCRIPTION
The invoice RPC command creates the expectation of a payment of a given amount of
milli-satoshi: it returns a unique token which another lightning daemon can use to
pay this invoice. This token includes a route hint description of an incoming channel
with capacity to pay the invoice, if any exists.
The amount_msat parameter can be the string "any", which creates an invoice that can
be paid with any amount. Otherwise it is a positive value in millisatoshi precision;
it can be a whole number, or a whole number ending in msat or sat, or a number with
three decimal places ending in sat, or a number with 1 to 11 decimal places ending in
btc.
The label must be a unique string or number (which is treated as a string, so "01" is
different from "1"); it is never revealed to other nodes on the lightning network,
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l2-cli invoice 20000sat inv-1 "pizza-1"
{
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"expires_at": 1671214214,
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"payment_secret": "aae3498127708721658f6c035a56b783085f0974c48a133bf9201511c11b9b4d",
"warning_deadends": "Insufficient incoming capacity, once dead-end peers were excluded"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli pay lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc
{
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"created_at": 1670609436.548,
"parts": 1,
"amount_msat": 20000000,
"amount_sent_msat": 20000000,
"payment_preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"status": "complete"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l2-cli invoice 40000sat inv-2 "pizza-2"
{
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"expires_at": 1671214257,
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"payment_secret": "9c4ecdc9235284eda4a627acdacf781a8149dad3dcf4d3181d6e90a7fc675e71",
"warning_deadends": "Insufficient incoming capacity, once dead-end peers were excluded"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli pay lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u
{
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"created_at": 1670609472.101,
"parts": 1,
"amount_msat": 40000000,
"amount_sent_msat": 40000000,
"payment_preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"status": "complete"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l2-cli invoice 60000sat inv-3 "pizza-3"
{
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"expires_at": 1671214284,
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"payment_secret": "94f1b80421adadecffb1749e71483d652900abb0b17465c1014ff4986a48840c",
"warning_deadends": "Insufficient incoming capacity, once dead-end peers were excluded"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli pay lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh
{
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"created_at": 1670609502.859,
"parts": 1,
"amount_msat": 60000000,
"amount_sent_msat": 60000000,
"payment_preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"status": "complete"
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays
{
"pays": [
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"status": "complete",
"created_at": 1670609436,
"preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"amount_msat": 20000000,
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"status": "complete",
"created_at": 1670609472,
"preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"amount_msat": 40000000,
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"status": "complete",
"created_at": 1670609502,
"preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"amount_msat": 60000000,
"amount_sent_msat": 60000000
}
]
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ alias l1-cli
alias l1-cli='lightning-cli --lightning-dir=/tmp/l1-regtest'
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ tree /tmp/l1-regtest/
/tmp/l1-regtest/
├── config
├── lightningd-regtest.pid
├── log
└── regtest
├── accounts.sqlite3
├── emergency.recover
├── gossip_store
├── hsm_secret
├── lightningd.sqlite3
└── lightning-rpc
1 directory, 9 files
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ sqlite3 /tmp/l1-regtest/regtest/lightningd.sqlite3
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite> .tables
blocks invoices
channel_blockheights offers
channel_configs outputs
channel_feerates payments
channel_funding_inflights peers
channel_htlcs penalty_bases
channel_state_changes shachain_known
channels shachains
channeltxs transaction_annotations
datastore transactions
db_upgrades utxoset
forwarded_payments vars
htlc_sigs version
sqlite> .schema payments
CREATE TABLE payments ( id INTEGER, timestamp INTEGER, status INTEGER, payment_hash BLOB, destination BLOB, msatoshi INTEGER, payment_preimage BLOB, path_secrets BLOB, route_nodes BLOB, route_channels BLOB, failonionreply BLOB, faildestperm INTEGER, failindex INTEGER, failcode INTEGER, failnode BLOB, failchannel TEXT, failupdate BLOB, msatoshi_sent INTEGER, faildetail TEXT, description TEXT, faildirection INTEGER, bolt11 TEXT, total_msat INTEGER, partid INTEGER, groupid INTEGER NOT NULL DEFAULT 0, local_offer_id BLOB DEFAULT NULL REFERENCES offers(offer_id), paydescription TEXT, PRIMARY KEY (id), UNIQUE (payment_hash, partid, groupid));
sqlite> SELECT * FROM payments;
<BLOB>|||||||||||20000000||||lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc|0|0|1||
2|1670609472|1|<BLOB>|<BLOB>
<BLOB>|40000000|<BLOB>|||||||||||40000000||||lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u|0|0|1||
<BLOB>|60000000|<BLOB>|||||||||||60000000||||lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh|0|0|1||
sqlite> SELECT bolt11,msatoshi_sent from payments;
lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc|20000000
lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u|40000000
lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh|60000000
sqlite> SELECT bolt11,msatoshi_sent from payments WHERE (msatoshi_sent > 20000000);
lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u|40000000
lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh|60000000
sqlite> .exit
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays
{
"pays": [
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"status": "complete",
"created_at": 1670609436,
"preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"amount_msat": 20000000,
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"status": "complete",
"created_at": 1670609472,
"preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"amount_msat": 40000000,
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"status": "complete",
"created_at": 1670609502,
"preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"amount_msat": 60000000,
"amount_sent_msat": 60000000
}
]
}
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays | jq '.[]'
[
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "c1162161653f282ef154853942a5af3a035c7e0cf42421948e61557bae1cbddc",
"status": "complete",
"created_at": 1670609436,
"preimage": "e5f718d438da1b05d616b2a9d480a5685fd9886f42c367b34523a370097c0ac8",
"amount_msat": 20000000,
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "fcdf81dd3e4e3ad3d2e66e942ce4006ba8a0286276eb1d222d23174de5913476",
"status": "complete",
"created_at": 1670609472,
"preimage": "4946c1d645a9dba292b16950323b46605fd94c8e319b16198001976ab31f523c",
"amount_msat": 40000000,
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"destination": "02f4f43c624fb866320a43b05e686db3e2906a3d96ff13b33630b7d74c514553ca",
"payment_hash": "50ec6d849f635fb829256fed6b28c43c51052e9d2c41555069e924d964c86f7f",
"status": "complete",
"created_at": 1670609502,
"preimage": "4d8215d9a4603ef305b3b2d612d993c87e9e85b841deadb8abf5ee3b2be03921",
"amount_msat": 60000000,
"amount_sent_msat": 60000000
}
]
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays | jq '.[] | map({bolt11,amount_sent_msat})'
[
{
"bolt11": "lnbcrt200u1p3ex7sxsp54t35nqf8wzrjzev0dsp4544hsvy97zt5cj9pxwleyq23rsgmndxspp5cytzzct98u5zau25s5u59fd08gp4clsv7sjzr9ywv92hhtsuhhwqdqvwp5h57np95csxqyjw5qcqp29qyysgq3pxz45a8065xdpuk49pccyf4z94qrtpk0l06hs8z02hafel3257pueeltlcmpc30npaquqr3ayk6s598lqv4j5rrjtktk0wyy4azwnqp38m6nc",
"amount_sent_msat": 20000000
},
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"amount_sent_msat": 60000000
}
]
◉ tony@tony:~/lnroom/lightning:[git»(HEAD detached at v0.12.1)]
$ l1-cli listpays | jq '.[] | map({bolt11,amount_sent_msat}) | map(select(.amount_sent_msat > 20000000))'
[
{
"bolt11": "lnbcrt400u1p3ex733sp5n38vmjfr22zwmf9xy7kd4nmcr2q5nkknmn6dxxqad6g20lr8tecspp5ln0crhf7fcad85hxd62zeeqqdw52q2rzwm436g3dyvt5mev3x3mqdqvwp5h57np95eqxqyjw5qcqp29qyysgqvkzvye5uf4985lcymghekh328w50y7y0qkkls2sg26vnm977x05j49snl942u04cm5d6kn4kl08rnrmpz9xu50d08m3r4kswgzstqagp36ra4u",
"amount_sent_msat": 40000000
},
{
"bolt11": "lnbcrt600u1p3ex7jvsp5jncmsppp4kk7ela3wj08zjpav55sp2ask96xtsgpfl6fs6jgssxqpp52rkxmpylvd0ms2f9dlkkk2xy83gs2t5a93q425rfayjdjexgdalsdqvwp5h57np95esxqyjw5qcqp29qyysgqvaeklnze6u4w9pkanx6zajk9s9wn909y7fjz3rmfq5daal503a8stzvcjzacfqcp9vlwzc4hf936qm3wjz2ld7as32xa7qnh6nlkxtsq0erjjh",
"amount_sent_msat": 60000000
}
]
