Database Structure Optimization and Transaction Data Indexing

[will-bitlightlabs]

Issue Overview

While reviewing the BP Node codebase, I've identified several limitations in the current database structure that need to be addressed to properly support transaction indexing and querying requirements, especially for RGB v0.12's reorg detection capabilities.

Current Implementation

The current database structure defines several tables:

pub const TABLE_MAIN: TableDefinition<&'static str, &[u8]> = TableDefinition::new("main");
pub const TABLE_BLKS: TableDefinition<[u8; 32], DbBlockHeader> = TableDefinition::new("blocks");
pub const TABLE_TXIDS: TableDefinition<[u8; 32], TxNo> = TableDefinition::new("txids");
pub const TABLE_TXES: TableDefinition<TxNo, DbTx> = TableDefinition::new("transactions");
pub const TABLE_OUTS: TableDefinition<TxNo, Vec<TxNo>> = TableDefinition::new("spends");
pub const TABLE_SPKS: TableDefinition<&[u8], TxNo> = TableDefinition::new("scripts");

Currently, the implementation in process_block() only:

1. Stores block headers in TABLE_BLKS
2. Maps transaction IDs to internal transaction numbers in TABLE_TXIDS
3. Stores full transaction data in TABLE_TXES

Issues Identified

1. TABLE_SPKS Design Limitation

The current TABLE_SPKS design maps a script pubkey to only one transaction number:

pub const TABLE_SPKS: TableDefinition<&[u8], TxNo> = TableDefinition::new("scripts");

This design is insufficient because the same address (script pubkey) can receive funds in multiple transactions. The current design only allows indexing a single transaction per script, making it impossible to look up all transactions related to a particular address.

2. Missing Transaction-Block Association

There's no way to quickly determine which block a transaction belongs to, which is essential for determining confirmation status and handling reorgs.

3. Missing UTXO Tracking

The implementation lacks proper UTXO tracking, which is crucial for wallet functionality and reorg handling.

Proposed Solutions

1. Modify TABLE_SPKS Design

pub const TABLE_SPKS: TableDefinition<&[u8], Vec<TxNo>> = TableDefinition::new("scripts");

2. Add Additional Tables

// UTXO index table
pub const TABLE_UTXOS: TableDefinition<(TxNo, u32), ()> = TableDefinition::new("utxos");

// Transaction-block association table
pub const TABLE_TX_BLOCKS: TableDefinition<TxNo, [u8; 32]> = TableDefinition::new("tx_blocks");

// ...etc

I plan to implement the logic required by the adjusted table structure.

These are the points I wanted to confirm with you before development. If there are any omissions or redundancies in my approach, I will promptly correct them. I look forward to your feedback.

[dr-orlovsky]

The current TABLE_SPKS design maps a script pubkey to only one transaction number:

Yes, that has to be a map to a list of transaction numbers; max to 2^24 transactions (i.e. MediumVec)

There's no way to quickly determine which block a transaction belongs to, which is essential for determining confirmation status and handling reorgs.

Yes, we need a table for that. But to save space we also need BlockId like TxNo made with a counter, and map TxNo to a vector (up to 2^16, i.e. SmallVec) of these BlockIds.

The implementation lacks proper UTXO tracking, which is crucial for wallet functionality and reorg handling.

Right now TABLE_SPKS maps TxNo to a txid of transactions spending its outputs (position-wise). If some output is not spent, then its TxNo is zero (reserved value). This helps to track UTXOs.

However if one needs not to track whether a specific output is spent, but a complete set of UTXOs, that might be tricky to compute. I am fine with adding another table for that.

pub const TABLE_UTXOS: TableDefinition<(TxNo, u32), ()> = TableDefinition::new("utxos");

Yes, something like that

---

Probably we also need a table with:

1. List of UTXOs spent in a specific block
2. List of all blocks representing valid chain (excluding the ones which got re-org) and list of orphaned blocks

[dr-orlovsky]

Closed in #11