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 28. This schema is include for reference purposes and cannot be used to 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.

Database schema

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