Layout of new SQLite descriptor wallets in v0.21 and v22.0?

Question

I'm trying to explore the database format of the new descriptor wallets in Bitcoin Core v0.21 and v22.0, but I can't quite figure it out. I've created a new descriptor wallet and dumped it's contents using SQLite viewers:

{wallet.dat}.main.{minversion} = ac970200
{wallet.dat}.main.{walletdescriptorkey, 5bc2366434...be2be5636f} = d63081d302...eb80b8a63f
{wallet.dat}.main.{walletdescriptorcache, 5bc2366434...bc00000000} = 4a0481d4f2...e2b5797bc6
{wallet.dat}.main.{walletdescriptorlhcache, 5bc2366434...bc00000000} = 4a0334157e...3cd72210c0
{wallet.dat}.main.{activeexternalspk, 00} = 5bc2366434...17e16409bc
{wallet.dat}.main.{walletdescriptorkey, 179b711c2e...be2be5636f} = d63081d302...eb80b8a63f
{wallet.dat}.main.{walletdescriptorcache, 179b711c2e...3a00000000} = 4a04c1b8ae...534fc80e4a
{wallet.dat}.main.{walletdescriptorlhcache, 179b711c2e...3a00000000} = 4a03774319...6b8a432640
{wallet.dat}.main.{activeexternalspk, 01} = 179b711c2e...5fffee163a
{wallet.dat}.main.{walletdescriptorkey, 7cf4a0f984...be2be5636f} = d63081d302...eb80b8a63f
{wallet.dat}.main.{walletdescriptorcache, 7cf4a0f984...4b00000000} = 4a045ffe0a...0d113a8596
{wallet.dat}.main.{walletdescriptorlhcache, 7cf4a0f984...4b00000000} = 4a03673c1f...e647a32c18
{wallet.dat}.main.{activeexternalspk, 02} = 7cf4a0f984...8c3dd80a4b
{wallet.dat}.main.{walletdescriptorkey, 7457e8edcb...be2be5636f} = d63081d302...eb80b8a63f
{wallet.dat}.main.{walletdescriptorcache, 7457e8edcb...d300000000} = 4a0481d4f2...c713790faf
{wallet.dat}.main.{walletdescriptorlhcache, 7457e8edcb...d300000000} = 4a0334157e...3cd72210c0
{wallet.dat}.main.{activeinternalspk, 00} = 7457e8edcb...d76c288ed3
{wallet.dat}.main.{walletdescriptorkey, ccd36c1209...be2be5636f} = d63081d302...eb80b8a63f
{wallet.dat}.main.{walletdescriptorcache, ccd36c1209...9400000000} = 4a04c1b8ae...18821059e5
{wallet.dat}.main.{walletdescriptorlhcache, ccd36c1209...9400000000} = 4a03774319...6b8a432640
{wallet.dat}.main.{activeinternalspk, 01} = ccd36c1209...ef094d8e94
{wallet.dat}.main.{walletdescriptorkey, db2cf1cdc6...be2be5636f} = d63081d302...eb80b8a63f
{wallet.dat}.main.{walletdescriptorcache, db2cf1cdc6...6d00000000} = 4a045ffe0a...497189e5c3
{wallet.dat}.main.{walletdescriptorlhcache, db2cf1cdc6...6d00000000} = 4a03673c1f...e647a32c18
{wallet.dat}.main.{flags} = 0000000004000000
{wallet.dat}.main.{activeinternalspk, 02} = db2cf1cdc6...0129f3106d
{wallet.dat}.main.{walletdescriptor, db2cf1cdc6...0129f3106d} = wpkh(tpubD...)#znsakxap, 13159b6100...00e8030000
{wallet.dat}.main.{walletdescriptor, 7457e8edcb...d76c288ed3} = pkh(tpubD6...)#25g5mpq6, 12159b6100...00e8030000
{wallet.dat}.main.{walletdescriptor, 5bc2366434...17e16409bc} = pkh(tpubD6...)#mqd4x5sz, 11159b6100...00e8030000
{wallet.dat}.main.{walletdescriptor, 179b711c2e...5fffee163a} = sh(wpkh(tp...)#0v4dg3a0, 11159b6100...00e8030000
{wallet.dat}.main.{walletdescriptor, 7cf4a0f984...8c3dd80a4b} = wpkh(tpubD...)#n84utnde, 12159b6100...00e8030000
{wallet.dat}.main.{walletdescriptor, ccd36c1209...ef094d8e94} = sh(wpkh(tp...)#f0agnukm, 13159b6100...00e8030000
{wallet.dat}.main.{bestblock} = 605b030000
{wallet.dat}.main.{bestblock_nomerkle} = 605b030021...0900000000

The descriptors fields clearly show the tpubs, and there may be private keys in the *spk fields. But in order to do BIP32 derivations, you need both a private key and a chain-code.

I can't seem to create the tprvs from the tpubs, even given private keys. I've looked through the code at commit 4018e23, and have some vague ideas on how it is constructed, but can't quite seem to figure out which fields hold either a private key and chain-code, or simply a tprv.

There is also the possibility that the key value itself is actually encrypted at rest in the database, but I had assumed that these would use the walletdescriptorckey field for crypted-key as opposed to the walletdescriptorkey fields. Notice my dump only contains *rkey fields and no rckey fields.

I realize that the next release will expose the tprv in the future listdescriptors command, but I was trying to figure out how PR#21500 did it.

The code to create the SQL dump file is... essentially:

from sqlite_utils import Database

if __name__ == "__main__":
    db=Database("wallet.dat")
    for row in db['main'].rows:
        key = fmt_key(row['key'])
        value = fmt_key(row['value'])
        
        pref = "{wallet.dat}.main."
        print(f"{pref}{{{key}}} = {value}")

An unabridged copy of the the code and the data can be found here:

• https://github.com/brianddk/reddit/blob/master/python/sqldump.py
• https://github.com/brianddk/reddit/blob/master/python/sqldump.txt

If you lack sufficent Karma to comment or answer, I've XPosted the question on reddit

Answer 1

An extended public key contains the chaincode, so it is not stored separately. So the tpubs that you have found will contain the chaincode.

The private keys are not encrypted, and unencrypted private keys are stored using DER serialization. This method produces a private key that is 214 bytes, with the vast majority of the data being irrelevant. Private keys will always begin with the bytes d63081d30201010420. Then the next 32 bytes are the private key's bytes. Everything after that can be discarded.

Once you have the private key, concatenate it with 00 (so it will be 00<private key>), and insert it into the decoded tpub in the place of the public key. Re-encode it as a tprv and you should have the corresponding extended private key for the public key that you have found.