Bitcoin

db_btc

Bronze layer

The raw Bitcoin tables

Blocks

tbl_prod_br_blocks This table encapsulates comprehensive details on Bitcoin blocks, encompassing mining data, block identification, and transaction specifics. It records the difficulty level, mining rewards, the unique hash identifying each block, its position within the blockchain, and the Merkle root for transaction integrity verification. Additionally, it includes metrics such as the block’s size, the total number of transactions it contains, and the cumulative work or “chainwork” up to that point, expressed in hexadecimal. This table not only provides insights into the mining process, including the nonce and the compact target “bits,” but also captures the economic aspects of mining through fields like mint reward, total fees, and the overall reward. The tbl_dev_br_blocks serves as a pivotal resource for analyzing the blockchain’s structural and economic dynamics, offering a granular view of each block’s contribution to the ledger.

col_name data_type comment
bits string The encoded form of the target threshold this block’s header hash must be less than or equal to.
chainwork string The total amount of work done on this block’s chain, in hexadecimal.
coinbase string The transaction containing the miner’s reward and any fees paid by transactions included in this block.
date string The date and time when this block was mined.
difficulty string The difficulty target for this block.
hash string The hash of this block’s header.
height bigint The height of this block in the blockchain.
merkle_root string The Merkle root of the transactions included in this block.
mint_reward double The total amount of new bitcoins generated by mining this block.
nonce string A random number used to try to find a valid block hash.
previous_block_hash string The hash of the previous block in the blockchain.
size bigint The size of this block in bytes.
stripped_size bigint The size of this block without witness data in bytes.
time timestamp The Unix timestamp when this block was mined.
total_fees double The total amount of fees paid by transactions included in this block.
total_reward double The total amount of bitcoins generated by mining this block, including both the mint reward and transaction fees.
transaction_count bigint The number of transactions included in this block.
weight bigint The weight of this block, used to calculate transaction fees.

Transactions

tbl_prod_br_transactions This table provides a detailed ledger of transactions within the Bitcoin chain, capturing both the technical aspects and the economic activities of each transaction. It records the transaction’s metadata, such as its unique identifier (id), the block_hash it belongs to, its block_height and timing (block_date and block_time), and its placement within the block (index). The table delves into the composition of transactions, detailing the number and total value of inputs (input_count and input_value) and outputs (output_count and output_value), alongside arrays of input and output objects for granular transaction analysis. It also highlights whether a transaction is a coinbase transaction, indicating a miner’s reward (is_coinbase), the transaction fees involved (fee), and technical parameters like hex representation, lock_time, size, version, and virtual_size. This comprehensive transactional overview aids in understanding the flow of bitcoins, transaction costs, and the operational dynamics of the Bitcoin blockchain.

col_name data_type comment
block_date date The date when the block was added to the blockchain
block_hash string The unique identifier of the block
block_height bigint The height of the block in the blockchain
block_time timestamp The time when the block was added to the blockchain
coinbase string The address that received the block reward for mining the block
fee bigint The transaction fee paid by the sender
hex string The hexadecimal representation of the transaction
id string The unique identifier of the transaction
index bigint The index of the transaction in the block
input_count bigint The number of inputs in the transaction
input_value bigint The total value of all inputs in the transaction
inputs array An array of input objects containing information about each input in the transaction
is_coinbase boolean A boolean indicating whether the transaction is a coinbase transaction
lock_time bigint The lock time of the transaction
output_count bigint The number of outputs in the transaction
output_value bigint The total value of all outputs in the transaction
outputs array An array of output objects containing information about each output in the transaction
size bigint The size of the transaction in bytes
version bigint The version number of the transaction
virtual_size bigint The virtual size of the transaction

Silver layer

The Silver layer on Bitcoin will focus on exploding the inputs and outputs from the raw data as well as focus on all the L2’s, Rollups and Sidechains that are building on Bitcoin

Base tables

Outputs

vw_prod_sl_outputs The vw_prod_sl_outputs table is a structured representation of transaction outputs derived from the raw transactional data in the Bronze Layer of the Ortege Lakehouse. This table breaks down the transaction outputs, which are the results of Bitcoin transactions, into a format that is easier to analyze and query.

col_name data_type comment
id string The unique identifier of the transaction from which the output is derived.
block_height bigint The height of the block in the blockchain that includes the transaction.
block_date date The date when the block containing the transaction was added to the blockchain.
is_coinbase boolean Indicates whether the transaction output is from a coinbase transaction, signifying new bitcoins entering circulation as a miner’s reward.
address string The Bitcoin address that is the recipient of this output.
index bigint The position of this output within the transaction; the first output is indexed as 0.
required_signatures bigint The number of signatures required to spend this output.
script_asm string The assembly notation of the script that dictates how this output can be spent.
script_hex string The hexadecimal encoding of the output script.
type string The type of script used in the transaction output (e.g., ‘pubkeyhash’, ‘scripthash’).
value bigint The value of bitcoins held in this output, represented in Satoshis (the smallest unit of bitcoin).

Inputs

vw_prod_sl_inputs The vw_prod_sl_inputs table is an expanded view of the inputs from the raw transaction data within the Bronze Layer of the Ortege Lakehouse. This table breaks down individual transaction inputs, providing a granular look at the data that constitutes the transactional elements of the Bitcoin network.

col_name data_type comment
id string The unique identifier for the transaction containing this input.
block_height bigint The height of the block in the blockchain that includes this transaction.
block_date date The date when the block was added to the blockchain.
is_coinbase boolean A flag indicating whether the input is part of a coinbase transaction.
address string The Bitcoin address associated with this input.
index bigint The position of this input within the transaction.
required_signatures bigint The number of required signatures for the transaction this input is part of.
script_asm string The assembly notation of the script used in the input.
script_hex string The hexadecimal encoding of the script used in the input.
sequence bigint A sequence number associated with this input, used for replacement transactions.
spent_output_index bigint The index of the output that this input is spending from.
spent_transaction_hash string The hash of the transaction from which this input spends.
type string The type of script used in this input (e.g., ‘pubkeyhash’, ‘scripthash’).
value bigint The value of bitcoins being input, typically matching the value of the spent output.

Wallet transactions

tbl_prod_sl_wallet_transactions The tbl_prod_s1_wallet_transactions table is designed to store transactional data related to Bitcoin wallets. This data helps in monitoring and auditing the balance changes in each wallet associated with the system.

This table is utilized by Ortege to:

  • Track incoming and outgoing transactions for each wallet.
  • Calculate the real-time balance of each wallet.
  • Audit transactions for compliance and reporting purposes.
  • Detect patterns and analyze wallet activity.

Layer 2’s

In the blockchain community, terminology such as “layer-2 solutions,” “sidechains,” and “rollups” can often lead to confusion due to overlapping features and capabilities. For the sake of clarity and convenience within our documentation, we will refer to these various technologies collectively as “L2 solutions.” This encompasses any scalability and extensibility solutions that are built on top of the Bitcoin network to enhance its performance and functionality.

Currently, our data platform focuses exclusively on the Stacks L2 solution, which brings smart contract capabilities and decentralized applications to Bitcoin. As an integral part of our data ecosystem, the Stacks-related tables and views provide users with detailed information about transactions and interactions specific to the Stacks blockchain. Our dedication to thorough data representation ensures users have comprehensive insights into this innovative L2, bolstering their understanding and analysis of its growing impact on the Bitcoin network.

Stacks

tbl_prod_sl_l2_stacks Tracks and catalogues every transaction on the Bitcoin network that pertains to the Stacks Layer-2 solution. This is achieved by scanning for the distinctive “X2” magic code, which is a unique identifier embedded within the OP_RETURN opcode of Bitcoin transaction outputs. The presence of this magic code signals a transaction that is related to the Stacks ecosystem.

Each transaction captured by our table is then systematically classified and decoded. This process transforms the raw data into a structured and user-friendly format, thereby allowing our users to effortlessly discern the nature and details of Stacks-related activities taking place on the Bitcoin blockchain.

The table serves as a powerful tool for users who are interested in monitoring the interactions between Bitcoin and the Stacks L2 solution.

Field Description
id Represents the transaction hash.
block_height The block height the transaction occurred in.
block_date The date the transaction occurred.
address Address of the sender.
index The sequence number of the output in a transaction, starting at 0.
script_asm The script of the transaction. An OP_RETURN followed by specific codes at index 0 indicates a non-spendable output embedding different types of data.
value The amount associated with the output, typically zero for OP_RETURN outputs as they are not intended for transfer.
transaction_type - vrf - VRF key registrations relating to OP_RETURN 58325e (X2^). - commit - Transactions relating to OP_RETURN 58325b (X2[): Block commit. Every output after the index 0, represents an amount that the Stacks miner commits in Satoshi’s to be paid to Stacks stackers. The final transaction is the unspent change to send back to the miner. - stack-stx - Transaction relating to OP_RETURN 583278 (X2x). - transfer-stx - Transactions relating OP_RETURN 583224 (X2$). - preSTX - Transactions relating to OP_RETURN 583270 (X2p).
commit A boolean reflecting true if the value is an amount of Satoshi’s committed to STX miners.
burnt Highlights transactions that send BTC to the burn address 1111111111111111111114oLvT2, indicating that miners burn BTC.
commit_details Contains details related to the proof-of-transfer commitment. The commit_details structure includes the following fields: block_hash: The hash of the corresponding block in the Bitcoin blockchain. parent_block: The hash of the parent Bitcoin block. parent_txoff: The transaction offset in the parent block. key_block: The hash of the key block for leader election in Stacks. key_txoff: The transaction offset in the key block. burn_modulus: The modulus for the burn amount.
stack_stx_details Contains details about the STX tokens locked for Stacks mining. The stack_stx_details structure encompasses: uSTX_to_lock: The amount of microSTX (uSTX) tokens that are locked. cycles: The number of cycles for which the tokens are locked.

Stacks Rewards

tbl_prod_sl_l2_stacks_rewards Captures the rewards earned by Stacks (STX) token stackers, expressed in satoshis, which is the smallest unit of Bitcoin. It logs the unique identifier of the reward transaction (id), the block height at which the reward was recorded (block_height), the date of the block (block_date), the recipient address (address), and the amount of the reward (input_value).

Column Name Type Description
id Integer The unique identifier for the stacking reward record.
block_height Integer The height of the block in the Stacks blockchain.
block_date Date The date when the block was mined.
address String The Stacks wallet address that received the reward.
value Float The reward amount earned for stacking, denominated in satoshis.
commit Boolean Indicates whether the row is an actual BTC committment
burnt Boolean Indicates whether the transaction was sent to the burn address
cycle Integer The cycle the transaction occurred in
avg_btc_price Float The average of the BTC price for the duration of the Stacks cylce
usd_committed Float The value mulitplied by the average BTC price to determine the amount of USD committed for rewards

MetaProtocols

Bitcoin metaprotocols, such as Ordinals and BRC-20 tokens, represent innovative layers or protocols built on top of the Bitcoin blockchain that extend its functionality beyond simple financial transactions. These metaprotocols leverage the security and decentralization of Bitcoin while introducing new capabilities, such as the creation of smart contracts, tokens, and unique digital assets.

Ordinals

tbl_prod_sl_ordinals Ordinals introduce the concept of “digital artifacts” to the Bitcoin ecosystem. Unlike traditional Bitcoin transactions that are purely financial, Ordinals allow for the inscription of arbitrary data directly onto individual satoshis (the smallest unit of Bitcoin). This innovation means that any piece of digital content, whether it be text, images, audio, or even small programs, can be inscribed onto the Bitcoin blockchain. Each inscribed satoshi becomes a unique, non-fungible token (NFT) due to its specific ordinal number, making it possible to own, trade, and collect digital artifacts in a way that’s secured by the Bitcoin network.

Key Features:

  • Uniqueness: Each inscription is unique, tied to a specific satoshi.
  • Decentralization: Utilizes the secure and decentralized nature of Bitcoin.
  • Versatility: Allows for a wide range of digital content to be stored directly on the blockchain.

BRC20’s

tbl_prod_sl_brc20 BRC-20 tokens represent a standard for issuing fungible tokens on the Bitcoin blockchain, analogous to Ethereum’s ERC-20 standard. By utilizing Bitcoin sidechains or layers such as the Liquid Network or smart contract platforms built on top of Bitcoin, BRC-20 tokens enable the creation of custom tokens that can represent various assets, from traditional securities to new forms of digital assets. These tokens can be used for a multitude of purposes, including decentralized finance (DeFi), governance, and digital collectibles, all while benefiting from the security and liquidity of the Bitcoin network.

Key Features:

  • Fungibility: Tokens conforming to the BRC-20 standard are interchangeable, with each token being identical to another in value and properties.
  • Smart Contracts: Enables complex financial operations and agreements to be executed automatically.
  • Bitcoin Security: Leverages the robust security features of the Bitcoin blockchain.