yigen-statement.csv file is missing some cj tx

[tajava2006]

These are the transaction list extracted using the wallet_fetch_history function.

This is Earnings Report page of Jam. As you can see here, the Earnings Report in Jam is missing one transaction, which is causing the final earnings information to be incorrect. At the time the screenshot was taken, the transaction was definitely included in a block, but it was not recorded in the yigen-statement.csv and therefore did not appear in JAM.

I understand that JoinMarket and Jam are separate projects. However, I still believe that it is a problem if '/wallet/yieldgen/report' API provides inaccurate information. It is suspected that this issue arises from underestimating the fee for a specific CoinJoin transaction, leading to delays in confirmation. Therefore, I think this is a fairly frequent error.

Therefore, instead of sending the yigen-statement.csv file as is, it would be better to generate a new report for each request. It seems feasible to create a new function by slightly modifying the existing wallet_fetch_history function for this purpose. Looking at the function, there is some concern about the need to insert data into SQLite3 every time a request comes in, but making such extensive changes might be too large of an undertaking. Therefore, I think this approach would be acceptable.

If my words sound a bit odd, please excuse me as it may be due to the use of a translation tool.

[tajava2006]

#1623

I just saw the linked thread now. I wasn’t aware that there had already been discussions about these issues. If the history function takes several minutes for a heavy wallet, then it doesn't seem like a viable solution.

It’s reported that implementing a persistent SQLite gettransaction cache still results in slow performance. Instead of just using a gettransaction cache, what about recording each completed transaction directly into a separate SQLite file as it occurs, and then retrieving the data via select queries from that SQLite file when needed? This could include �some metadata, even if it means sacrificing some data consistency.

For example,

transaction table
txid,
block_height(or block_timestamp or both),
fee,
tx_type,
cj_amount,
wallet_id,
my_input_amout(if needed),
my_input_utxo_count(if needed),
my_output_count(if needed),
my_output_amount(if needed)

transaction_row table (unique: txid+sequence)
txid,
sequence(minus when is_input is true),
is_input,
is_ours,
address,
amount,
prev_txid(not null when is_input is true),
prev_tx_index(not null when is_input is true)

All the information here can be obtained and filled in when the tx is detected for the first time, and there is no modification in the future except for block_height and block_timestamp. We could track transactions where blocktime is null to check if they’ve been confirmed and update them accordingly. This approach should minimize the risk of significant data inconsistency.

In fact, even if we don't save it in this detail, I think we can save only txid, block_height, tx_type, and wallet_id.
Even if there is no my_blahblah column(and also transaction_row table), we can specify certain number of tx by querying like this

select * from transaction where wallet_id = 'asdf' and tx_type = 'cj internal' and block_height is not null order by block_height desc limit xx offset yy

then we can search detail information of that only xx number of tx not all transaction.