# Documentation of SQL Schema
Knowing the SQL schema is useful in three situations:
- You want to query the schema directly as NBXplorer's API doesn't provide an API endpoint for your needs
- You want to do your own indexer on top of it (in a different language or for a crypto we don't support)
- You want to create your own API on top of it for features not exposed by NBXplorer's API.
This model is contained in this SQL script (opens new window). There are additional comments there about the meaning of columns and views.
Here is a diagram:
The SQL Schema of NBXplorer is multi-tenant, multi-asset, multi-chain. It is divided in several parts:
- Blocks and transactions:
blks,blks_txsandtxs - Outputs, Inputs and Scripts:
ins,outs,ins_outs,scripts - Descriptors:
descriptors,descriptors_scripts - Wallets:
wallets,wallets_wallets,wallets_descriptors,wallets_scripts - Double spending detection:
spent_outs
Most if the data in those tables is denormalized for better query performance. The denormalization is handled automatically by triggers.
# Using wallets tables
A wallet is a collection of wallets_descriptors and wallets_scripts.
If a descriptor belongs to a wallet, then any descriptors_scripts will be added by trigger in the wallets_scripts.
A descriptor is also called output descriptor in the Bitcoin jargon, it represents a way to derive a collection of addresses deterministically. How to represent and interpret a descriptor is up to the indexer. (see next part)
It is also possible to add directly scripts to wallets_scripts.
On top of it, a wallet can have children by using wallets_wallets.
Any script from a child wallet will be added to the scripts of the parent wallet.
This allow you to define a hierarchy of wallet.
# Making your own indexer
An indexer typical flow is the following:
- Insert block in blks with
confirmed='f' - Call
fetch_matcheswith all the ins and outs of a block - This will create temporary tables
matched_outs,matched_insandmatched_conflictsthe indexer can use to inspect what has been matched - Call save_matches to instruct the database to insert all matched ins/outs.
- Turn
confirmed='t'of the block.
On top of this, an indexer is responsible for creating descriptors_scripts. It can do so by monitoring descriptors.gap, and if the gap become too low, the indexer need to insert more scripts from the descriptor into descriptors_scripts.
This allow indexer to define their own descriptors.
# Views and functions
# Function: to_btc
to_btc(v NUMERIC)
Helper function format a satoshi based value into numeric bitcoin.
SELECT to_btc(150000000);
Output:
1.50000000
# Function: get_wallets_recent
get_wallets_recent(in_wallet_id TEXT, in_interval INTERVAL, in_limit INT, in_offset INT)
get_wallets_recent(in_wallet_id TEXT, in_code TEXT, in_interval INTERVAL, in_limit INT, in_offset INT)
See what has going on recently in a wallet.
Better to use on Postgres 13+, as it greatly benefits from incremental sort.
For example, this returns the 5 most recent KPrAFh3ZOIS5umpbwuYkU0sF8JW+ that happened for one year.
SELECT * FROM get_wallets_recent('KPrAFh3ZOIS5umpbwuYkU0sF8JW+', interval '1 year', 5, 0);
Output:
| "code" | "asset_id" | "tx_id" | "seen_at" | "balance_change" | "balance_total" |
|---|---|---|---|---|---|
| "BTC" | "" | "0e42124df086ef05b0d8cb3a5b2028828b3af27b31d7c5045803cc8541410a9f" | "2022-03-07 17:14:27.269382+00" | -199996.0 | 55020 |
| "BTC" | "" | "529d92bcdba7545befa4b1101256544def3ca9726ebbe4a6bd8122d7762129a2" | "2022-03-06 21:15:48.505111+00" | -229995.0 | 255016 |
| "BTC" | "" | "e9ce704adc9c64e099c2513169d6856bbf2fcdb2de92b465d4d63b67961b2bd2" | "2022-03-04 15:07:08.923081+00" | 200000 | 485011 |
| "BTC" | "" | "0581c1545ce58ecf93d70e97321fa27c7d409bb0666a124470491403cb0d91f4" | "2022-03-03 10:59:44.259948+00" | 230000 | 285011 |
| "BTC" | "" | "c12d1339295d6a69af8dc0fb411b67d5571d31265b385586341a4126bcda76da" | "2022-02-22 17:00:02.697965+00" | -519989.0 | 55011 |
# View: utxos
List all the UTXOs available:
Warning: It also returns the UTXO that are confirmed but spent in the mempool, as well as immature utxos and utxos that are being double spent.
If you want the available UTXOs which can be spent use WHERE input_mempool IS FALSE AND immature IS FALSE.
SELECT * FROM utxos
WHERE input_mempool IS FALSE AND
immature IS FALSE;
Output:
| "code" | "tx_id" | "idx" | "script" | "value" | "asset_id" | "input_tx_id" | "input_idx" | "input_mempool" | "immature" | "blk_id" | "blk_idx" | "blk_height" | "mempool" | "replaced_by" | "seen_at" |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| "BTC" | "6bf5a95c11c819ff22da3933bef2931977b4906c5820bb48ab5336a77502e4a6" | 0 | "00144d60ec59ed28267bf897543a659662cddabd384b" | 19490 | "" | NULL | NULL | False | False | "0000010e1bc83fa20421880db7424740392a459c6ac699cefe7eac5004247e46" | NULL | 58943 | False | NULL | "2021-10-08 14:25:17.96779+00" |
| "BTC" | "6c43019cb584391a766b1c7d5c67f09174d98b4306edeedf8586c8fdbf1048d6" | 0 | "0014f8a89fd6d6d256df24e05b01cade70d2abcdb6f6" | 113872 | "" | NULL | NULL | False | False | "00000088cb1683d609054866ff72131af46dd7f6ee51136fdc2184f5ad1bbbf6" | NULL | 58502 | False | NULL | "2021-10-05 10:21:39.483849+00" |
| "BTC" | "1457ce82a3eb87a8982429e5185896ddee09c773519fa198de3bba4d6c9b42b7" | 0 | "0014002ca9212872715f0a5789d52084a9daecb5dce7" | 3899577 | "" | NULL | NULL | False | False | "000001090d8f4c50c77021026d5ec5fbe2072cb95a1f5592635f70dfc30297d0" | NULL | 59348 | False | NULL | "2021-10-11 12:30:12.008471+00" |
| "BTC" | "75b7ba9a0718a95234e0b72edd2c721f3066be96eb8076f5b8f3f7160498d07f" | 1 | "0014ca76914ddc4fca3c37768e599302765ae633b62c" | 119727 | "" | NULL | NULL | False | False | "0000009d64990b4a2b4bff9511d5c9713124905a0932026db5737a3ad44f0138" | NULL | 60492 | False | NULL | "2021-10-19 10:08:54.301111+00" |
| "BTC" | "9fc4bc1b9dc2d60202fe4e483b54afb5da7efbbfe6966e5e51f4dc6dbc2363b5" | 0 | "0014ad054e107c046e69b5ab5abc1e44d25ac9378ff6" | 9900 | "" | NULL | NULL | False | False | "0000010003e91e0f56787b9e02b5ae424bb681f09f2ec82f8e23b19a7a93b6e6" | NULL | 53506 | False | NULL | "2021-09-01 02:13:21.469658+00" |
# View: descriptors_scripts_unused
List all the unused scripts from descriptors.
Example: Get the next unused address:
SELECT * FROM descriptors_scripts_unused
WHERE code='BTC' AND descriptor='7obopunl9/6GVYkej++YMw6DHWXk'
ORDER BY idx LIMIT 1 OFFSET 0;
Output:
| code | descriptor | script | idx | addr | d_metadata | ds_metadata |
|---|---|---|---|---|---|---|
| BTC | 7obopunl9/6GVYkej++YMw6DHWXk | 001482137a4bbc144f8eb5a02fd2acda36c999a96242 | 3261 | tb1qsgfh5jauz38caddq9lf2ek3kexv6jcjzxu9sfw | {"type": "NBXv1-Derivation", "feature": "Deposit", "derivation": "tpubDCtUtuEgpKLQJMu5uUUuDQub7D4CHjCJsfGhwrA3HV6X6CuV7Zj6RizRFwLPNKqkLrd8TM1Xt3QmwumqRRZAUTrVAA9T8uhyMfodChhFykD", "keyPathTemplate": "0/*"} |
# View: wallets_utxos
List all the wallets UTXOs available:
Warning: It also returns the UTXO that are confirmed but spent in the mempool, as well as immature utxos and utxos that are being double spent.
If you want the available UTXOs which can be spent use WHERE input_mempool IS FALSE AND immature IS FALSE.
Also, keep in mind that if an UTXO belong to two wallets, then you will see two entries for this UTXO.
SELECT * FROM wallets_utxos
WHERE input_mempool IS FALSE AND
immature IS FALSE;
Output:
| "wallet_id" | "code" | "tx_id" | "idx" | "script" | "value" | "asset_id" | "input_tx_id" | "input_idx" | "input_mempool" | "immature" | "blk_id" | "blk_idx" | "blk_height" | "mempool" | "replaced_by" | "seen_at" |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| "XObUaAdU2HBJk4XHGhIRya3ZObB3" | "BTC" | "6bf5a95c11c819ff22da3933bef2931977b4906c5820bb48ab5336a77502e4a6" | 0 | "00144d60ec59ed28267bf897543a659662cddabd384b" | 19490 | "" | NULL | NULL | False | False | "0000010e1bc83fa20421880db7424740392a459c6ac699cefe7eac5004247e46" | NULL | 58943 | False | NULL | "2021-10-08 14:25:17.96779+00" |
| "XObUaAdU2HBJk4XHGhIRya3ZObB3" | "BTC" | "6c43019cb584391a766b1c7d5c67f09174d98b4306edeedf8586c8fdbf1048d6" | 0 | "0014f8a89fd6d6d256df24e05b01cade70d2abcdb6f6" | 113872 | "" | NULL | NULL | False | False | "00000088cb1683d609054866ff72131af46dd7f6ee51136fdc2184f5ad1bbbf6" | NULL | 58502 | False | NULL | "2021-10-05 10:21:39.483849+00" |
| "ZDP5CJ1pOWLITR1API3aJkEpdQz+" | "BTC" | "1457ce82a3eb87a8982429e5185896ddee09c773519fa198de3bba4d6c9b42b7" | 0 | "0014002ca9212872715f0a5789d52084a9daecb5dce7" | 3899577 | "" | NULL | NULL | False | False | "000001090d8f4c50c77021026d5ec5fbe2072cb95a1f5592635f70dfc30297d0" | NULL | 59348 | False | NULL | "2021-10-11 12:30:12.008471+00" |
| "ZDP5CJ1pOWLITR1API3aJkEpdQz+" | "BTC" | "75b7ba9a0718a95234e0b72edd2c721f3066be96eb8076f5b8f3f7160498d07f" | 1 | "0014ca76914ddc4fca3c37768e599302765ae633b62c" | 119727 | "" | NULL | NULL | False | False | "0000009d64990b4a2b4bff9511d5c9713124905a0932026db5737a3ad44f0138" | NULL | 60492 | False | NULL | "2021-10-19 10:08:54.301111+00" |
| "ZDP5CJ1pOWLITR1API3aJkEpdQz+" | "BTC" | "9fc4bc1b9dc2d60202fe4e483b54afb5da7efbbfe6966e5e51f4dc6dbc2363b5" | 0 | "0014ad054e107c046e69b5ab5abc1e44d25ac9378ff6" | 9900 | "" | NULL | NULL | False | False | "0000010003e91e0f56787b9e02b5ae424bb681f09f2ec82f8e23b19a7a93b6e6" | NULL | 53506 | False | NULL | "2021-09-01 02:13:21.469658+00" |
| "ZDP5CJ1pOWLITR1API3aJkEpdQz+" | "BTC" | "fec5c75b87378068f1d7d3276e90b2579e5fad8854a7002e14c275f3da9823be" | 0 | "00145674d00638e0da30ae216df4bb539e94cd2d13d1" | 1168447 | "" | NULL | NULL | False | False | "000000b43e91de514fec8f9fc418ecb1e0a2b1d14027b01abaad9d4e39e19e8d" | NULL | 60590 | False | NULL | "2021-10-20 02:18:16.980641+00" |
# View: wallets_balances
See the current balance of wallets.
confirmed_balanceonly include the sum of value of all currently confirmed UTXOs.unconfirmed_balancewill become the nextconfirmed_balanceof the wallet if all non-double spent transactions get confirmed.immature_balancethe sum of utxos that can't be spent before they are immature (ie, the UTXO comes from a coinbase that is less than 100 blocks old)available_balanceis the balance that can be spent. (unconfirmed_balance-immature_balance)
SELECT * FROM wallets_balances
Output:
| "wallet_id" | "code" | "asset_id" | "unconfirmed_balance" | "confirmed_balance" | "available_balance" | "immature_balance" |
|---|---|---|---|---|---|---|
| "4Tdfhh5kK8TFKcDVvsp6xdKOFdmA" | "BTC" | "" | 389520 | 389520 | 389520 | 0 |
| "7ERM4iRjhk1gmeyHxEJQp0fhzh8z" | "BTC" | "" | 420000000 | 420000000 | 420000000 | 0 |
| "IIKKVY87qBLjbaa/KAfUfcNP9HeC" | "BTC" | "" | 100000 | 100000 | 100000 | 0 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | 55020 | 55020 | 55020 | 0 |
| "Q+z/7E0DCjazcm5Q7t9wZSoEJ0IP" | "BTC" | "" | 200000 | 200000 | 200000 | 0 |
| "XObUaAdU2HBJk4XHGhIRya3ZObB3" | "BTC" | "" | 133362 | 133362 | 133362 | 0 |
| "ZDP5CJ1pOWLITR1API3aJkEpdQz+" | "BTC" | "" | 5197651 | 5197651 | 5197651 | 0 |
# Materialized view: wallets_history
Provide an history of wallets (time ordered list of wallet_id, code, asset_id, balance-change, total-balance), it only takes into account confirmed transactions.
This materialized view is intensive to refresh (for 220K transactions, it takes around 5 seconds)
This is meant to be used for reports and histograms.
If you want the latest history of a wallet, use get_wallets_recent instead.
To refresh the materialized view, use SELECT wallets_history_refresh();. This will return true if the view has been refreshed.
SELECT * FROM wallets_history WHERE wallet_id='KPrAFh3ZOIS5umpbwuYkU0sF8JW+';
Output:
| "wallet_id" | "code" | "asset_id" | "tx_id" | "seen_at" | "balance_change" | "balance_total" | "nth" |
|---|---|---|---|---|---|---|---|
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "0e42124df086ef05b0d8cb3a5b2028828b3af27b31d7c5045803cc8541410a9f" | "2022-03-07 17:14:27.269382+00" | -199996 | 55020 | 10 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "529d92bcdba7545befa4b1101256544def3ca9726ebbe4a6bd8122d7762129a2" | "2022-03-06 21:15:48.505111+00" | -229995 | 255016 | 9 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "e9ce704adc9c64e099c2513169d6856bbf2fcdb2de92b465d4d63b67961b2bd2" | "2022-03-04 15:07:08.923081+00" | 200000 | 485011 | 8 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "0581c1545ce58ecf93d70e97321fa27c7d409bb0666a124470491403cb0d91f4" | "2022-03-03 10:59:44.259948+00" | 230000 | 285011 | 7 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "c12d1339295d6a69af8dc0fb411b67d5571d31265b385586341a4126bcda76da" | "2022-02-22 17:00:02.697965+00" | -519989 | 55011 | 6 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "081466d46632b56d28260b928fea87addeed24cfd25e3d6504b5ddfacf6176e1" | "2022-02-04 10:42:31.059182+00" | 310000 | 575000 | 5 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "f0165974071064b32122449dbf72a67ab884723923e8536efb5cf4301bcb7469" | "2022-02-04 10:22:04.47652+00" | 164000 | 265000 | 4 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "a4e165729e5fb406d878b3c9c8dfabd1a3465084c2c2b63602fd8704c41442ac" | "2022-02-04 09:40:00.427892+00" | 101000 | 101000 | 3 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "61247ae11a770963ffa26a63bb4ac03a14f759f7691eda8ce10175db6b6aaa77" | "2021-10-11 13:09:22.977488+00" | -850000 | 0 | 2 |
| "KPrAFh3ZOIS5umpbwuYkU0sF8JW+" | "BTC" | "" | "6bf5a95c11c819ff22da3933bef2931977b4906c5820bb48ab5336a77502e4a6" | "2021-10-08 14:25:17.96779+00" | 850000 | 850000 | 1 |
# Function: get_wallets_histogram
get_wallets_histogram(in_wallet_id TEXT, in_code TEXT, in_asset_id TEXT, in_from TIMESTAMPTZ, in_to TIMESTAMPTZ, in_interval INTERVAL)
This function depends on wallets_history, as such, you should make sure the materialized view is refreshed time for up-to-date histogram.
For example, this will show how the balance of KPrAFh3ZOIS5umpbwuYkU0sF8JW+ changed every week from 2022-01-23 to now.
SELECT * FROM get_wallets_histogram('KPrAFh3ZOIS5umpbwuYkU0sF8JW+', 'BTC', '', '2022-01-23', CURRENT_TIMESTAMP, interval '1 week');
Output:
| "date" | "balance_change" | "balance" |
|---|---|---|
| "2022-01-23 00:00:00+00" | 0 | 0 |
| "2022-01-30 00:00:00+00" | 575000 | 575000 |
| "2022-02-06 00:00:00+00" | 0 | 575000 |
| "2022-02-13 00:00:00+00" | 0 | 575000 |
| "2022-02-20 00:00:00+00" | -519989 | 55011 |
| "2022-02-27 00:00:00+00" | 430000 | 485011 |
| "2022-03-06 00:00:00+00" | -429991 | 55020 |
| "2022-03-13 00:00:00+00" | 0 | 55020 |
| "2022-03-20 00:00:00+00" | 0 | 55020 |
# Function: nbxv1_get_wallet_id
nbxv1_get_wallet_id(in_code TEXT, in_scheme_or_address TEXT) RETURNS TEXT
This function is specific to the current API of NBXplorer. It calculates the wallet_id based on the chain code (ie. BTC) and an address or derivation scheme.
# Function: nbxv1_get_descriptor_id
nbxv1_get_descriptor_id(in_code TEXT, in_scheme TEXT, in_feature TEXT) RETURNS TEXT
This function is specific to the current API of NBXplorer. It calculates the descriptor_id based on the chain code (ie. BTC), a derivation scheme, and the feature (ie. Deposit or Change).