Changes required to `bkpr-dumpincomecsv` output

[madelinevibes]

We need to refine how we bridge Lightning infrastructure to financial reporting.
The raw output of Core Lightning’s bookkeeper plugin should map to the journal entries, GL accounts, and audit controls required for US GAAP-compliant financial statements. The current QuickBooks export has 4 columns; finance requires a 9-column format that the updated bkpr-dumpincomecsv command must produce.

New .csv format requirement

• Column 1: Date column: No change
• Column 2: Separate Tag columns: Column name: Tag.
  Right now the event type (invoice, routed, onchain_fee, etc.) is buried inside the Description string. This needs to be its own column to map transactions to general ledger accounts automatically. Need to create a new column and extract the event type from the existing 'description' column: invoice, invoice_fee, routed, deposit, withdrawal, onchain_fee, channel_open, channel_close, lease_fee, penalty, etc.
• Column 3: The channel ID or "wallet": Column name: Account.
  This is currently buried in Description as "(channelid...)". Needed for per-channel reconciliation and sub-ledger mapping.
• Column 4: Currently: "invoice (channelid...) btc: description text".
  Remove the tag prefix, account ID, and "btc:" prefix. Keep just the human-readable text. When no description exists, include the payment_id or txid as the fallback identifier.
• Column 5: Credit (BTC): Already converts from msat to BTC.
  Fix scientific notation issue — some routing fees export as '2E-11' which breaks QuickBooks import. Always use decimal format.
• Column 6: Debit (BTC): Same scientific notation fix needed as Column 5
• Column 7: New Column: Column Name BTC/USD Price
  The BTC/USD spot price at the time of the transaction.
  (bookkeeper needs to record the currency conversion rate at the time of each event #8915 )
• Column 8: New Column: Column Name Credit (USD)
  Credit (BTC) × BTC/USD Price. Pre-calculated USD value. Under ASU 2023-08 and ASC 830, all BTC transactions must be translated to USD for the GL.
• Column 9: New Column: Column Name Debit (USD)
  Debit (BTC) × BTC/USD Price. Same as above for debits.

[madelinevibes]

I have some more analysis information to help with the next steps for this, if required :)

[madelinevibes]

The solution for including BTC/USD price in CSV export is almost there thanks to the work @daywalker90 has done with this API already #8842 However, issue #8915 relates to 'stamping' the currency conversion, at the time of the bookkeeper entry, which is another piece of work.

[rustyrussell]

OK. Comments one at a time:

Column 2: Separate Tag columns: Column name: Tag.
Right now the event type (invoice, routed, onchain_fee, etc.) is buried inside the Description string. This needs to be its own column to map transactions to general ledger accounts automatically. Need to create a new column and extract the event type from the existing 'description' column: invoice, invoice_fee, routed, deposit, withdrawal, onchain_fee, channel_open, channel_close, lease_fee, penalty, etc.

Easy, we call this 'primary tag' in the code, but "Event type" is nicer.

Column 3: The channel ID or "wallet": Column name: Account.
This is currently buried in Description as "(channelid...)". Needed for per-channel reconciliation and sub-ledger mapping.

Yep, simpler.

Column 4: Currently: "invoice (channelid...) btc: description text".
Remove the tag prefix, account ID, and "btc:" prefix. Keep just the human-readable text. When no description exists, include the payment_id or txid as the fallback identifier.

I'll have to play with this. We won't have a description for everything, it will depend on the event type. txid & outnum might have to be a separate column, but only one would be populated normally. For some (lightning forwards) there is no useful description to include.

Column 5: Credit (BTC): Already converts from msat to BTC.
Column 6: Debit (BTC): Same scientific notation fix needed as Column 5
Fix scientific notation issue — some routing fees export as '2E-11' which breaks QuickBooks import. Always use decimal format.

Good catch. Yes, it should always be exactly 8 decimal places.

Column 7: New Column: Column Name BTC/USD Price
The BTC/USD spot price at the time of the transaction.
(#8915 )

This is the big lift, but it's also very important in real life. Definitely a value add, so it's in my queue.

Column 8: New Column: Column Name Credit (USD)
Credit (BTC) × BTC/USD Price. Pre-calculated USD value. Under ASU 2023-08 and ASC 830, all BTC transactions must be translated to USD for the GL.
Column 9: New Column: Column Name Debit (USD)
Debit (BTC) × BTC/USD Price. Same as above for debits.

These ones I'm reluctant to add. My reasoning is subtle, but these columns are qualitatively different from the credit/debit columns in BTC which are exact and reproducible.

The spot price is obtained in practice by taking a median of various different price sources. This is not well-defined to start with, and then in practice there's lag (prices from 10 minutes ago), network issues beyond, or third party reliability/security issues. It cannot be confirmed externally, it's just the result of that particular nodes' polling of the 7 sources.

In the worst case, it's possible it would be totally missing, in which case you would have to use your judgement to establish a value at reconciliation time.

This all said, the spot price is so useful it's clearly worth the compromises! However, since deriving the other columns is trivial I'm reluctant to add it.

[madelinevibes]

Finance write:
Really appreciate the thoughtfulness here, you’re right that the spot price is qualitatively different from the BTC amounts. The BTC values are exact and deterministic; the price is a best-effort estimate from the node’s polling and delayed. That distinction is real and we won’t lose sight of it.

That said, we do need all three price/USD columns in the export.

Here’s why:
Under GAAP (ASU 2023-08), every BTC transaction must be recorded in USD at the time it occurs. We can’t wait until month-end to price them as we need a transaction-level USD amount to post to the GL as each event is processed. The node’s spot price gives us that working value.

You’re correct that it won’t be perfectly accurate. That’s expected and accounted for. At month-end (or more frequently at scale), finance runs a reconciliation. A simple example would be, we take the actual BTC balance in the node, multiply by the official rate source (likely CME CF BRR or similar), and compare to the sum of all the transaction-level USD postings. The difference flows through as an unrealized gain/loss or tracking adjustment. This is standard mark-to-market accounting and the same process any fund or treasury operation uses when holding assets that trade 24/7.

So the imprecision you’re flagging isn’t a bug, it’s a known input to a reconciliation process that’s already built into the accounting framework. If this is acceptable to the team, we would like to keep these columns in the build out.