Transaction Sorting and Querying

[allisonmoyer]

Describe the enhancement
We'd like to be able to sort transactions at the DB layer and be able to paginate (and maybe in the future search) queries to the transactions.

Use case
We want to display transactions to our customers in a sorted manner. We can do this by first querying all transactions and then sorting them after the fact, but this won't perform well once customers have a large number of transactions.

Currently we are thinking of sorting based on confirmation time for confirmed transactions and no sorting for unconfirmed transactions, but we may explore associating metadata with transactions (i.e. "initiation date" for when the transaction was initiated) and would want to be able to sort transactions based on that (this would help us sort unconfirmed transactions).

In the future, we may also allow customers to filter / search their transactions – ideally we could associate some sort of metadata here that we could filter / search by, but in a simpler use case could also imagine looking up date ranges (i.e. searching / grouping by confirmation time).

[LLFourn]

Hi @allisonmoyer I can give some commentary about where we're going with this. BDK is moving towards a v1.0 release. I made the first PR for this yesterday #793. The good news is that in v1.0 the (i) default sorting will be in ascending confirmation time and (ii) all the transactions will be stored in memory rather than in a database (with the ability to write out changes to persistent storage as you see fit). See: sparse_chain.

We have the ability to search for transactions in a height range already: https://github.com/LLFourn/bdk_core_staging/blob/c4de0235bae61d9e51dbe16f13a3c1c658444676/bdk_core/src/sparse_chain.rs#L386

We are planning to allow attaching some associated data to transactions in the SparseChain. I think it's likely that you will have to persist it and index yourself though. The database in v1.0 will be very much decoupled from the wallet itself (it will no longer have a "database" in it) so you should be free to store data in whatever format you want and then read it into memory whenever you want to use it.

[notmandatory]

Need to confirm how this should be done with the new wallet API. I don't think the best way to do this will be via queries directly on the DB, it should be more efficient to do with the new in memory view of the wallet transactions.

[LLFourn]

see also #974, #776, #1333.

I have rough plan in #1333 for how to sort transaction in the way I think applications should display them which is by the first time they saw the transaction i.e. transactions don't move place in the list. Other indexes can of course be implemented by applications separately but that seems like the default one that will satisfy most people. This would also keep transactions that are not in the chain in the list but allow you to tell what happened to them (RBF, re-org) etc.

I would consider including this as the last task for v1.0 after #1194