The wallet database

Each wallet is stored in it’s own SQLite database. The current version of ElectrumSV at the time of writing, 1.4.0b1, uses the database schema version 29. This schema is include for reference purposes and cannot be used to a create a working wallet.

Each version of ElectrumSV includes migration code that applies any needed changes to older versions of the wallet. This database format is pretty solid at this point, but it is a work in progress. There are many other things ElectrumSV will need to support in the future.

Transactions and atomicity

Between how SQLite works, how the Python sqlite3 module works and how ElectrumSV builds upon both of these some elaboration is needed.

We pass the isolation_level=None parameter to the Python sqlite3 function that opens a database connection. This overrides the custom way the Python sqlite3 module overrides how SQLite works and returns it to the autocommit mode. This mode means that statements that modify the database take effect immediately. Use of a BEGIN and SAVEPOINT statement takes Sqlite out of autocommit mode, and the outermost COMMIT, ROLLBACK or RELEASE statement returns Sqlite to autocommit mode.

ElectrumSV does all of it’s database writes in custom transactions starting with the BEGIN statement, disabling the autocommit mode, and bundling the writes into groups with the ability to commit them all or roll them all back. Additionally as SQLite does not allow multiple connections to do concurrent writes, ElectrumSV takes a well known approach of having a sequential writer thread. All writes happen in a dedicated writer thread one after the other as managed transactions.

The following logic is used to wrap each ElectrumSV transaction:

def __call__(self, db: sqlite3.Connection) -> None:
    if not self._future.set_running_or_notify_cancel():
        return

    db.execute("BEGIN")
    try:
        result = self._fn(db, *self._args, **self._kwargs)
    except BaseException as exc:
        db.execute("ROLLBACK")
        self._future.set_exception(exc)
        # Break a reference cycle with the exception 'exc'
        self = None # type: ignore
    else:
        db.execute("COMMIT")
        self._future.set_result(result)

Synchronous writes

The Python concurrent.futures module is used in synchronous logic to do database writes in a non-blocking manner. The calling thread can block until the write is complete by calling the Future.result method. Or the calling thread can request a callback through the use of the Future.add_done_callback.

Caution

Futures catch and log exceptions in their callbacks, preventing ElectrumSV from catching and reporting them. This means that the Future callbacks need to be certain they know about all possible exceptions and to catch and handle them all. Developers should be very sure they understand the code they are calling.

Synchronous database calls are performed in this manner:

def on_db_call_done(future: concurrent.futures.Future[bool]) -> None:
    # Skip if the operation was cancelled.
    if future.cancelled():
        return
    # Raise any exception if it errored or get the result if completed successfully.
    future.result()
    self.events.trigger_callback(WalletEvent.TRANSACTION_DELETED, self._id, tx_hash)

future = db_functions.remove_transaction(self.get_db_context(), tx_hash)
future.add_done_callback(on_db_call_done)

Asynchronous writes

How ElectrumSV wraps asynchronous calls is done in the DatabaseContext.run_in_thread_async method. If you wish to see how it works, you can look in the sqlite_support.py file.

Asynchronous database calls are performed in this manner:

if await update_transaction_flags_async(db_context, [
        (TxFlags.MASK_STATELESS, TxFlags.STATE_SETTLED, tx_hash) ]):
    ...

Database schema

This is version 29 of our database schema. It should be correct for the ElectrumSV version this documentation is intended for, but if it is not, please let us know.

  1BEGIN TRANSACTION;
  2
  3CREATE TABLE IF NOT EXISTS "MasterKeys" (
  4    "masterkey_id" INTEGER,
  5    "parent_masterkey_id" INTEGER DEFAULT NULL,
  6    "derivation_type" INTEGER NOT NULL,
  7    "derivation_data" BLOB NOT NULL,
  8    "date_created" INTEGER NOT NULL,
  9    "date_updated" INTEGER NOT NULL,
 10    "flags" INTEGER NOT NULL DEFAULT 0,
 11    FOREIGN KEY("parent_masterkey_id") REFERENCES "MasterKeys"("masterkey_id"),
 12    PRIMARY KEY("masterkey_id")
 13);
 14
 15CREATE TABLE IF NOT EXISTS "Accounts" (
 16    "account_id" INTEGER,
 17    "default_masterkey_id" INTEGER DEFAULT NULL,
 18    "default_script_type" INTEGER NOT NULL,
 19    "account_name" TEXT NOT NULL,
 20    "date_created" INTEGER NOT NULL,
 21    "date_updated" INTEGER NOT NULL,
 22    "flags" INTEGER NOT NULL DEFAULT 0,
 23    FOREIGN KEY("default_masterkey_id") REFERENCES "MasterKeys"("masterkey_id"),
 24    PRIMARY KEY("account_id")
 25);
 26
 27CREATE TABLE IF NOT EXISTS "Transactions" (
 28    "tx_hash" BLOB,
 29    "tx_data" BLOB DEFAULT NULL,
 30    "proof_data" BLOB DEFAULT NULL,
 31    "block_height" INTEGER DEFAULT NULL,
 32    "block_position" INTEGER DEFAULT NULL,
 33    "fee_value" INTEGER DEFAULT NULL,
 34    "flags" INTEGER NOT NULL DEFAULT 0,
 35    "description" TEXT DEFAULT NULL,
 36    "date_created" INTEGER NOT NULL,
 37    "date_updated" INTEGER NOT NULL,
 38    "locktime" INTEGER DEFAULT NULL,
 39    "version" INTEGER DEFAULT NULL,
 40    "block_hash" BLOB DEFAULT NULL,
 41    PRIMARY KEY("tx_hash")
 42);
 43
 44CREATE TABLE IF NOT EXISTS "WalletData" (
 45    "key" TEXT NOT NULL,
 46    "value" TEXT NOT NULL,
 47    "date_created" INTEGER NOT NULL,
 48    "date_updated" INTEGER NOT NULL
 49);
 50
 51CREATE TABLE IF NOT EXISTS "WalletEvents" (
 52    "event_id" INTEGER,
 53    "event_type" INTEGER NOT NULL,
 54    "event_flags" INTEGER NOT NULL,
 55    "account_id" INTEGER,
 56    "date_created" INTEGER NOT NULL,
 57    "date_updated" INTEGER NOT NULL,
 58    FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
 59    PRIMARY KEY("event_id")
 60);
 61
 62CREATE TABLE IF NOT EXISTS "Invoices" (
 63    "invoice_id" INTEGER,
 64    "account_id" INTEGER NOT NULL,
 65    "tx_hash" BLOB DEFAULT NULL,
 66    "payment_uri" TEXT NOT NULL,
 67    "description" TEXT,
 68    "invoice_flags" INTEGER NOT NULL,
 69    "value" INTEGER NOT NULL,
 70    "invoice_data" BLOB NOT NULL,
 71    "date_expires" INTEGER DEFAULT NULL,
 72    "date_created" INTEGER NOT NULL,
 73    "date_updated" INTEGER NOT NULL,
 74    FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash"),
 75    FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
 76    PRIMARY KEY("invoice_id")
 77);
 78
 79CREATE TABLE IF NOT EXISTS "AccountTransactions" (
 80    "tx_hash" BLOB NOT NULL,
 81    "account_id" INTEGER NOT NULL,
 82    "flags" INTEGER NOT NULL DEFAULT 0,
 83    "description" TEXT DEFAULT NULL,
 84    "date_created" INTEGER NOT NULL,
 85    "date_updated" INTEGER NOT NULL,
 86    FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
 87    FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash")
 88);
 89
 90CREATE TABLE IF NOT EXISTS "TransactionOutputs" (
 91    "tx_hash" BLOB NOT NULL,
 92    "txo_index" INTEGER NOT NULL,
 93    "value" INTEGER NOT NULL,
 94    "keyinstance_id" INTEGER DEFAULT NULL,
 95    "flags" INTEGER NOT NULL,
 96    "script_type" INTEGER DEFAULT 0,
 97    "script_hash" BLOB NOT NULL DEFAULT x '',
 98    "script_offset" INTEGER DEFAULT 0,
 99    "script_length" INTEGER DEFAULT 0,
100    "spending_tx_hash" BLOB,
101    "spending_txi_index" INTEGER,
102    "date_created" INTEGER NOT NULL,
103    "date_updated" INTEGER NOT NULL,
104    FOREIGN KEY("keyinstance_id") REFERENCES "KeyInstances"("keyinstance_id"),
105    FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash")
106);
107
108CREATE TABLE IF NOT EXISTS "TransactionInputs" (
109    "tx_hash" BLOB NOT NULL,
110    "txi_index" INTEGER NOT NULL,
111    "spent_tx_hash" BLOB NOT NULL,
112    "spent_txo_index" INTEGER NOT NULL,
113    "sequence" INTEGER NOT NULL,
114    "flags" INTEGER NOT NULL,
115    "script_offset" INTEGER,
116    "script_length" INTEGER,
117    "date_created" INTEGER NOT NULL,
118    "date_updated" INTEGER NOT NULL,
119    FOREIGN KEY("tx_hash") REFERENCES "Transactions"("tx_hash")
120);
121
122CREATE TABLE IF NOT EXISTS "KeyInstances" (
123    "keyinstance_id" INTEGER,
124    "account_id" INTEGER NOT NULL,
125    "masterkey_id" INTEGER DEFAULT NULL,
126    "derivation_type" INTEGER NOT NULL,
127    "derivation_data" BLOB NOT NULL,
128    "derivation_data2" BLOB DEFAULT NULL,
129    "flags" INTEGER NOT NULL,
130    "description" TEXT DEFAULT NULL,
131    "date_created" INTEGER NOT NULL,
132    "date_updated" INTEGER NOT NULL,
133    FOREIGN KEY("masterkey_id") REFERENCES "MasterKeys"("masterkey_id"),
134    FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
135    PRIMARY KEY("keyinstance_id")
136);
137
138CREATE TABLE IF NOT EXISTS "KeyInstanceScripts" (
139    "keyinstance_id" INTEGER NOT NULL,
140    "script_type" INTEGER NOT NULL,
141    "script_hash" BLOB NOT NULL,
142    "date_created" INTEGER NOT NULL,
143    "date_updated" INTEGER NOT NULL,
144    FOREIGN KEY("keyinstance_id") REFERENCES "KeyInstances"("keyinstance_id")
145);
146
147CREATE TABLE IF NOT EXISTS "MAPIBroadcastCallbacks" (
148    "tx_hash" BLOB,
149    "peer_channel_id" VARCHAR(1024) NOT NULL,
150    "broadcast_date" INTEGER NOT NULL,
151    "encrypted_private_key" BLOB NOT NULL,
152    "server_id" INTEGER NOT NULL,
153    "status_flags" INTEGER NOT NULL,
154    PRIMARY KEY("tx_hash")
155);
156
157CREATE TABLE IF NOT EXISTS "Servers" (
158    "server_id" INTEGER,
159    "server_type" INTEGER NOT NULL,
160    "url" TEXT NOT NULL,
161    "account_id" INTEGER DEFAULT NULL,
162    "server_flags" INTEGER NOT NULL DEFAULT 0,
163    "api_key_template" TEXT DEFAULT NULL,
164    "encrypted_api_key" TEXT DEFAULT NULL,
165    "fee_quote_json" TEXT DEFAULT NULL,
166    "date_last_connected" INTEGER DEFAULT 0,
167    "date_last_tried" INTEGER DEFAULT 0,
168    "date_created" INTEGER NOT NULL,
169    "date_updated" INTEGER NOT NULL,
170    FOREIGN KEY("account_id") REFERENCES "Accounts"("account_id"),
171    PRIMARY KEY("server_id")
172);
173
174CREATE TABLE IF NOT EXISTS "PaymentRequests" (
175    "paymentrequest_id" INTEGER,
176    "keyinstance_id" INTEGER NOT NULL,
177    "state" INTEGER NOT NULL,
178    "description" TEXT,
179    "expiration" INTEGER,
180    "value" INTEGER,
181    "script_type" INTEGER NOT NULL,
182    "pushdata_hash" BLOB NOT NULL,
183    "date_created" INTEGER NOT NULL,
184    "date_updated" INTEGER NOT NULL,
185    FOREIGN KEY("keyinstance_id") REFERENCES "KeyInstances"("keyinstance_id"),
186    PRIMARY KEY("paymentrequest_id")
187);
188
189CREATE UNIQUE INDEX IF NOT EXISTS "idx_WalletData_unique" ON "WalletData" ("key");
190
191CREATE UNIQUE INDEX IF NOT EXISTS "idx_Invoices_unique" ON "Invoices" ("payment_uri");
192
193CREATE UNIQUE INDEX IF NOT EXISTS "idx_AccountTransactions_unique" ON "AccountTransactions" ("tx_hash", "account_id");
194
195CREATE UNIQUE INDEX IF NOT EXISTS "idx_TransactionOutputs_unique" ON "TransactionOutputs" ("tx_hash", "txo_index");
196
197CREATE UNIQUE INDEX IF NOT EXISTS "idx_TransactionInputs_unique" ON "TransactionInputs" ("tx_hash", "txi_index");
198
199CREATE UNIQUE INDEX IF NOT EXISTS "idx_KeyInstanceScripts_unique" ON "KeyInstanceScripts" ("keyinstance_id", "script_type");
200
201CREATE UNIQUE INDEX IF NOT EXISTS "idx_Servers_unique" ON "Servers" ("server_type", "url", "account_id");
202
203CREATE VIEW TransactionReceivedValues (account_id, tx_hash, keyinstance_id, value) AS
204SELECT
205    ATX.account_id,
206    ATX.tx_hash,
207    TXO.keyinstance_id,
208    TXO.value
209FROM
210    AccountTransactions ATX
211    INNER JOIN TransactionOutputs TXO ON TXO.tx_hash = ATX.tx_hash
212    INNER JOIN KeyInstances KI ON KI.keyinstance_id = TXO.keyinstance_id
213WHERE
214    TXO.keyinstance_id IS NOT NULL
215    AND KI.account_id = ATX.account_id;
216
217CREATE VIEW TransactionSpentValues (account_id, tx_hash, keyinstance_id, value) AS
218SELECT
219    ATX.account_id,
220    ATX.tx_hash,
221    PTXO.keyinstance_id,
222    PTXO.value
223FROM
224    AccountTransactions ATX
225    INNER JOIN TransactionInputs TXI ON TXI.tx_hash = ATX.tx_hash
226    INNER JOIN TransactionOutputs PTXO ON PTXO.tx_hash = TXI.spent_tx_hash
227    AND PTXO.txo_index = TXI.spent_txo_index
228    INNER JOIN KeyInstances KI ON KI.keyinstance_id = PTXO.keyinstance_id
229WHERE
230    PTXO.keyinstance_id IS NOT NULL
231    AND KI.account_id = ATX.account_id;
232
233CREATE VIEW TransactionValues (account_id, tx_hash, keyinstance_id, value) AS
234SELECT
235    account_id,
236    tx_hash,
237    keyinstance_id,
238    value
239FROM
240    TransactionReceivedValues
241UNION
242ALL
243SELECT
244    account_id,
245    tx_hash,
246    keyinstance_id,
247    - value
248FROM
249    TransactionSpentValues;
250
251COMMIT;

Details

For now various details about the database schema are kept below, but as we flesh it out it should end up being restructured.

Transaction table

block_hash

This column stores the block hash for the block the transaction was mined in. It is expected there is a matching row to the transaction hash and block hash in the TransactionProofs table.

block_height / block_position

These columns are intended to track the block height and block position of a transaction, once it has been mined, into the long term future. In theory, it is possible to map the block hash and transaction hash to the TransactionProofs table obtain this information. In practice, there are two reasons we may not want to do this.

  • We may want to delete proofs for transactions once coins have been spent.

  • We may not have the proof for older transactions, which have unspent coins from before proofs were retained. If these are unspent and present in migrated wallets, we will need to obtain the proofs to do an SPV payment.

flags

STATE_SIGNED

A fully signed transaction that is expected to not have been shared with external parties.

STATE_DISPATCHED

A transaction that has been shared with external parties, but is not expected to have been broadcast to the P2P network.

If this is determined to have been broadcast, then additional as yet implemented handling should be done to reconcile how to react to this event.

STATE_RECEIVED

A transaction that an external party has shared, but is not expected to have been broadcast to the P2P network.

If this is determined to have been broadcast, then additional as yet implemented handling should be done to reconcile how to react to this event.

STATE_CLEARED

A cleared transaction is one that is known to have been broadcast to the P2P network.

Nuances:

  • block_hash will be NULL for transactions that have been broadcast. block_position and proof_data will also be NULL.

  • block_hash will be non-NULL to represent knowledge that it has been mined (via non-MAPI channels) and that we should fetch a merkle proof to verify it is in a given block. block_position and proof_data will be NULL.

  • block_hash, block_position and proof_data will all have valid unprocessed values if the application headers do not include the given block height yet.

STATE_SETTLED

A settled transaction is one that is known to have been mined in a block, and has been verified as being in that block through checking the merkle proof.

Nuances:

  • block_hash will NULL for transactions that the legacy ElectrumX proof data was not retained

    for. These will need to be obtained, if they contain unspent coins (UTXOs).

  • block_hash will be non non-NULL for transactions that have been mined and which we have the proof for, block_height will be the height of the block with the given hash and block_position will be the index of the transaction in the block. There will be a proof row in the TransactionProofs table mapped to the tx_hash and block_hash columns of the Transactions table.