wallet: Store transactions in a separate sqlite table #33034

The wallet uses SQLite as a key-value store even though SQLite is a powerful relational database engine. This causes us numerous headaches due to the need to serialize multiple fields together, and since record read from the database during loading can come in any order.

Notably, for transactions, if we were able to load them in the transaction order assigned by the wallet, PRs like #27865 would be a bit simpler and easier to reason about.

This PR makes it possible for us to do that by storing transactions in a separate table. This table has a column for each field in CWalletTx, which lets us use a SQL statement like SELECT * FROM transactions ORDER BY pos which guarantees us the order in which the transactions are loaded into the wallet.

The eventual goal is to use SQLite as a relational database with tables that store our keys and other metadata that can reference each other so that the wallet doesn't just use the database as a storage mechanism. But that is still a work in progress.

This PR makes use of the last client opened features introduced in #32895 to determine when the old record style needs to be upgraded to the new transactions table. This should give us sufficient upgrade-downgrade-upgrade handling to not lose any funds.

Depends on #32763, #32895, #32985, #33032, #33033

<!--e57a25ab6845829454e8d69fc972939a-->

The following sections might be updated with supplementary metadata relevant to reviewers and maintainers.

<!--006a51241073e994b41acfe9ec718e94-->

Code Coverage & Benchmarks

For details see: https://corecheck.dev/bitcoin/bitcoin/pulls/33034.

<!--021abf342d371248e50ceaed478a90ca-->

Reviews

See the guideline for information on the review process.

If your review is incorrectly listed, please copy-paste <code>&lt;!--meta-tag:bot-skip--&gt;</code> into the comment that the bot should ignore.

<!--174a7506f384e20aa4161008e828411d-->

Conflicts

Reviewers, this pull request conflicts with the following ones:

• #35018 (wallet, bench: Use Nanobench setup() for wallet benchmarks, and remove DuplicateMockDatabase by achow101)
• #34909 (wallet, refactor: modularise wallet by extracting out legacy wallet migration by rkrux)
• #34838 (interfaces: typed receive-request APIs by MkDev11)
• #34502 (wallet: remove most asserts of WALLET_FLAG_DESCRIPTORS flag by rkrux)
• #34490 (wallet: Follow-ups to create/load split (#32636) by davidgumberg)
• #33160 (bench: Add more realistic Coin Selection Bench by murchandamus)
• #33033 (wallet, sqlite: Encapsulate SQLite statements in a RAII class by achow101)
• #33032 (wallet, test: Replace MockableDatabase with in-memory SQLiteDatabase by achow101)
• #33008 (wallet: support bip388 policy with external signer by Sjors)
• #32895 (wallet: Prepare for future upgrades by recording versions of last client to open and decrypt by achow101)
• #32489 (wallet: Add exportwatchonlywallet RPC to export a watchonly version of a wallet by achow101)
• #31260 (scripted-diff: Type-safe settings retrieval by ryanofsky)
• #27865 (wallet: Track no-longer-spendable TXOs separately by achow101)
• #25722 (refactor: Use util::Result class for wallet loading by ryanofsky)

If you consider this pull request important, please also help to review the conflicting pull requests. Ideally, start with the one that should be merged first.

<!--5faf32d7da4f0f540f40219e4f7537a3-->

LLM Linter (✨ experimental)

Possible places where named args for integral literals may be used (e.g. func(x, /*named_arg=*/0) in C++, and func(x, named_arg=0) in Python):

• SQLiteDatabase(fs::PathFromString("mock/"), fs::PathFromString("mock/wallet.dat"), DatabaseOptions(), SQLITE_OPEN_MEMORY) in src/wallet/test/util.cpp

^{2026-04-02 20:56:05}

<!--85328a0da195eb286784d51f73fa0af9-->

🚧 At least one of the CI tasks failed. _{Task lint: https://github.com/bitcoin/bitcoin/runs/46429203587} _{LLM reason (✨ experimental): The CI failure is caused by Python lint errors due to unresolved 'self' references in test files.}

<details><summary>Hints</summary>

Try to run the tests locally, according to the documentation. However, a CI failure may still happen due to a number of reasons, for example:

• Possibly due to a silent merge conflict (the changes in this pull request being incompatible with the current code in the target branch). If so, make sure to rebase on the latest commit of the target branch.

• A sanitizer issue, which can only be found by compiling with the sanitizer and running the affected test.

• An intermittent issue.

Leave a comment here, if you need help tracking down a confusing failure.

</details>

Concept ACK

Concept ACK a25557a

I have gone through the PR description and have skimmed over the commit messages as of now. At the outset, I find myself agreeing with the intent here. I am not aware of a strong enough reason to keep using SQLite as a KVS for all purposes, using its relational database properties for wallet transactions seem like a good start. I did notice some serialisation specifics of transaction properties in a couple previous PRs that I didn't fully understand why were required; it could be a lot cleaner and explicit in implementation if using SQLite as an RDBMS can avoid the need for doing those specific serialisations.

<!--85328a0da195eb286784d51f73fa0af9-->

🚧 At least one of the CI tasks failed. _{Task test max 6 ancestor commits: https://github.com/bitcoin/bitcoin/actions/runs/20110042688/job/57704599161} _{LLM reason (✨ experimental): CMake build failed with exit code 2 during project compilation (gmake reported an error).}

<details><summary>Hints</summary>

Try to run the tests locally, according to the documentation. However, a CI failure may still happen due to a number of reasons, for example:

• Possibly due to a silent merge conflict (the changes in this pull request being incompatible with the current code in the target branch). If so, make sure to rebase on the latest commit of the target branch.

• A sanitizer issue, which can only be found by compiling with the sanitizer and running the affected test.

• An intermittent issue.

Leave a comment here, if you need help tracking down a confusing failure.

</details>

<!--85328a0da195eb286784d51f73fa0af9-->

🚧 At least one of the CI tasks failed. _{Task test max 6 ancestor commits: https://github.com/bitcoin/bitcoin/actions/runs/21148471093/job/60819287525} _{LLM reason (✨ experimental): Build failed: the CI cmake/make run exited with non-zero status (gmake: all target) after linking bitcoinkernel, indicating a generic build error.}

<details><summary>Hints</summary>

Try to run the tests locally, according to the documentation. However, a CI failure may still happen due to a number of reasons, for example:

• Possibly due to a silent merge conflict (the changes in this pull request being incompatible with the current code in the target branch). If so, make sure to rebase on the latest commit of the target branch.

• A sanitizer issue, which can only be found by compiling with the sanitizer and running the affected test.

• An intermittent issue.

Leave a comment here, if you need help tracking down a confusing failure.

</details>