feat(btc): Duplicate coinbase transaction handling (#7)

* feat(btc): tx_hash can duplicated in block v1

Co-authored-by: Gaze <dev@gaze.network>

* feat(btc): duplicate tx  will use same txin/txout from previous tx

Co-authored-by: Gaze <dev@gaze.network>

* feat(btc): prevent revert block v1 data

if you really want to revert the data before the block version 2, you should reset the database and reindex the data instead.

Co-authored-by: Gaze <dev@gaze.network>

* doc(btc): update list duplicate tx hash

Co-authored-by: Gaze <dev@gaze.network>

* doc(btc): update docs

Co-authored-by: Gaze <dev@gaze.network>

* fix(btc): use last v1 block instead

Co-authored-by: Gaze <dev@gaze.network>

---------

Co-authored-by: Gaze <gazenw@users.noreply.github.com>

modules/bitcoin/constants.go

@@ -1,6 +1,8 @@
 package bitcoin
 
 import (
+	"github.com/Cleverse/go-utilities/utils"
+	"github.com/btcsuite/btcd/chaincfg/chainhash"
 	"github.com/gaze-network/indexer-network/common"
 	"github.com/gaze-network/indexer-network/core/types"
 )
@@ -10,8 +12,15 @@ const (
 	DBVersion = 1
 )
 
-// DefaultCurrentBlockHeight is the default value for the current block height for first time indexing
-var defaultCurrentBlock = types.BlockHeader{
-	Hash:   common.ZeroHash,
-	Height: -1,
-}
+var (
+	// defaultCurrentBlockHeight is the default value for the current block height for first time indexing
+	defaultCurrentBlock = types.BlockHeader{
+		Hash:   common.ZeroHash,
+		Height: -1,
+	}
+
+	lastV1Block = types.BlockHeader{
+		Hash:   *utils.Must(chainhash.NewHashFromStr("00000000000001aa077d7aa84c532a4d69bdbff519609d1da0835261b7a74eb6")),
+		Height: 227835,
+	}
+)

modules/bitcoin/database/postgresql/migrations/000001_initialize_table.down.sql

@@ -2,8 +2,8 @@ BEGIN;
 
 -- DROP INDEX
 DROP INDEX IF EXISTS bitcoin_blocks_block_hash_idx;
+DROP INDEX IF EXISTS bitcoin_transactions_tx_hash_idx;
 DROP INDEX IF EXISTS bitcoin_transactions_block_hash_idx;
-DROP INDEX IF EXISTS bitcoin_transactions_block_height_idx;
 DROP INDEX IF EXISTS bitcoin_transaction_txouts_pkscript_idx;
 DROP INDEX IF EXISTS bitcoin_transaction_txins_prevout_idx;
 

modules/bitcoin/database/postgresql/migrations/000001_initialize_table.up.sql

@@ -32,16 +32,17 @@ CREATE TABLE IF NOT EXISTS "bitcoin_blocks" (
 CREATE INDEX IF NOT EXISTS bitcoin_blocks_block_hash_idx ON "bitcoin_blocks" USING HASH ("block_hash");
 
 CREATE TABLE IF NOT EXISTS "bitcoin_transactions" (
-	"tx_hash" TEXT NOT NULL PRIMARY KEY,
+	"tx_hash" TEXT NOT NULL, -- can't use as primary key because block v1 has duplicate tx hashes (coinbase tx). See: https://github.com/bitcoin/bitcoin/commit/a206b0ea12eb4606b93323268fc81a4f1f952531
 	"version" INT NOT NULL,
 	"locktime" BIGINT NOT NULL,
 	"block_height" INT NOT NULL,
 	"block_hash" TEXT NOT NULL,
-	"idx" INT NOT NULL
+	"idx" INT NOT NULL,
+	PRIMARY KEY ("block_height", "idx")
 );
 
-CREATE INDEX IF NOT EXISTS bitcoin_transactions_block_height_idx ON "bitcoin_transactions" USING BTREE ("block_height");
-CREATE INDEX IF NOT EXISTS bitcoin_transactions_block_hash_idx ON "bitcoin_transactions" USING BTREE ("block_hash");
+CREATE INDEX IF NOT EXISTS bitcoin_transactions_tx_hash_idx ON "bitcoin_transactions" USING HASH ("tx_hash");
+CREATE INDEX IF NOT EXISTS bitcoin_transactions_block_hash_idx ON "bitcoin_transactions" USING HASH ("block_hash");
 
 CREATE TABLE IF NOT EXISTS "bitcoin_transaction_txouts" (
 	"tx_hash" TEXT NOT NULL,
@@ -61,7 +62,7 @@ CREATE TABLE IF NOT EXISTS "bitcoin_transaction_txins" (
 	"prevout_tx_idx" INT NOT NULL,
 	"prevout_pkscript" TEXT NULL, -- Hex String, Can be NULL if the prevout is a coinbase transaction
 	"scriptsig" TEXT NOT NULL, -- Hex String
-	"witness" TEXT, -- Hex String
+	"witness" TEXT NOT NULL DEFAULT '', -- Hex String
 	"sequence" BIGINT NOT NULL,
 	PRIMARY KEY ("tx_hash", "tx_idx")
 );

modules/bitcoin/database/postgresql/queries/data.sql

@@ -4,21 +4,61 @@ SELECT * FROM bitcoin_blocks ORDER BY block_height DESC LIMIT 1;
 -- name: InsertBlock :exec
 INSERT INTO bitcoin_blocks ("block_height","block_hash","version","merkle_root","prev_block_hash","timestamp","bits","nonce") VALUES ($1, $2, $3, $4, $5, $6, $7, $8);
 
--- name: InsertTransaction :exec
-INSERT INTO bitcoin_transactions ("tx_hash","version","locktime","block_height","block_hash","idx") VALUES ($1, $2, $3, $4, $5, $6);
+-- name: BatchInsertBlocks :exec
+INSERT INTO bitcoin_blocks ("block_height","block_hash","version","merkle_root","prev_block_hash","timestamp","bits","nonce")
+VALUES (
+	unnest(@block_height_arr::INT[]),
+	unnest(@block_hash_arr::TEXT[]),
+	unnest(@version_arr::INT[]),
+	unnest(@merkle_root_arr::TEXT[]),
+	unnest(@prev_block_hash_arr::TEXT[]),
+	unnest(@timestamp_arr::TIMESTAMP WITH TIME ZONE[]), -- or use TIMESTAMPTZ
+	unnest(@bits_arr::BIGINT[]),
+	unnest(@nonce_arr::BIGINT[])
+);
 
--- name: InsertTransactionTxOut :exec
-INSERT INTO bitcoin_transaction_txouts ("tx_hash","tx_idx","pkscript","value") VALUES ($1, $2, $3, $4);
+-- name: BatchInsertTransactions :exec
+INSERT INTO bitcoin_transactions ("tx_hash","version","locktime","block_height","block_hash","idx")
+VALUES (
+	unnest(@tx_hash_arr::TEXT[]),
+	unnest(@version_arr::INT[]),
+	unnest(@locktime_arr::BIGINT[]),
+	unnest(@block_height_arr::INT[]),
+	unnest(@block_hash_arr::TEXT[]),
+	unnest(@idx_arr::INT[])
+);
 
--- name: InsertTransactionTxIn :exec
+-- name: BatchInsertTransactionTxIns :exec
 WITH update_txout AS (
 	UPDATE "bitcoin_transaction_txouts"
 	SET "is_spent" = true
-	WHERE "tx_hash" = $3 AND "tx_idx" = $4 AND "is_spent" = false -- TODO: should throw an error if already spent
-	RETURNING "pkscript"
+	FROM (SELECT unnest(@prevout_tx_hash_arr::TEXT[]) as tx_hash, unnest(@prevout_tx_idx_arr::INT[]) as tx_idx) as txin
+	WHERE "bitcoin_transaction_txouts"."tx_hash" = txin.tx_hash AND "bitcoin_transaction_txouts"."tx_idx" = txin.tx_idx AND "is_spent" = false
+	RETURNING "bitcoin_transaction_txouts"."tx_hash", "bitcoin_transaction_txouts"."tx_idx", "pkscript"
+), prepare_insert AS (
+	SELECT input.tx_hash, input.tx_idx, prevout_tx_hash, prevout_tx_idx, update_txout.pkscript as prevout_pkscript, scriptsig, witness, sequence
+		FROM (
+			SELECT 
+				unnest(@tx_hash_arr::TEXT[]) as tx_hash,
+				unnest(@tx_idx_arr::INT[]) as tx_idx,
+				unnest(@prevout_tx_hash_arr::TEXT[]) as prevout_tx_hash,
+				unnest(@prevout_tx_idx_arr::INT[]) as prevout_tx_idx,
+				unnest(@scriptsig_arr::TEXT[]) as scriptsig,
+				unnest(@witness_arr::TEXT[]) as witness,
+				unnest(@sequence_arr::INT[]) as sequence
+		) input LEFT JOIN update_txout ON "update_txout"."tx_hash" = "input"."prevout_tx_hash" AND "update_txout"."tx_idx" = "input"."prevout_tx_idx"
 )
-INSERT INTO bitcoin_transaction_txins ("tx_hash","tx_idx","prevout_tx_hash","prevout_tx_idx","prevout_pkscript","scriptsig","witness","sequence") 
-VALUES ($1, $2, $3, $4, (SELECT "pkscript" FROM update_txout), $5, $6, $7);
+INSERT INTO bitcoin_transaction_txins ("tx_hash","tx_idx","prevout_tx_hash","prevout_tx_idx", "prevout_pkscript","scriptsig","witness","sequence") 
+SELECT "tx_hash", "tx_idx", "prevout_tx_hash", "prevout_tx_idx", "prevout_pkscript", "scriptsig", "witness", "sequence" FROM prepare_insert;
+
+-- name: BatchInsertTransactionTxOuts :exec
+INSERT INTO bitcoin_transaction_txouts ("tx_hash","tx_idx","pkscript","value")
+VALUES (
+	unnest(@tx_hash_arr::TEXT[]),
+	unnest(@tx_idx_arr::INT[]),
+	unnest(@pkscript_arr::TEXT[]),
+	unnest(@value_arr::BIGINT[])
+);
 
 -- name: RevertData :exec
 WITH delete_tx AS (

modules/bitcoin/datagateway/bitcoin_data.go

@@ -13,7 +13,7 @@ type BitcoinDataGateway interface {
 }
 
 type BitcoinWriterDataDataGateway interface {
-	InsertBlock(context.Context, *types.Block) error
+	InsertBlocks(ctx context.Context, blocks []*types.Block) error
 	RevertBlocks(context.Context, int64) error
 }
 

modules/bitcoin/processor.go

@@ -1,10 +1,7 @@
 package bitcoin
 
 import (
-	"cmp"
 	"context"
-	"log/slog"
-	"slices"
 
 	"github.com/cockroachdb/errors"
 	"github.com/gaze-network/indexer-network/common/errs"
@@ -12,8 +9,6 @@ import (
 	"github.com/gaze-network/indexer-network/core/types"
 	"github.com/gaze-network/indexer-network/internal/config"
 	"github.com/gaze-network/indexer-network/modules/bitcoin/datagateway"
-	"github.com/gaze-network/indexer-network/pkg/logger"
-	"github.com/gaze-network/indexer-network/pkg/logger/slogx"
 )
 
 // Make sure to implement the BitcoinProcessor interface
@@ -42,36 +37,15 @@ func (p *Processor) Process(ctx context.Context, inputs []*types.Block) error {
 		return nil
 	}
 
-	// Sort ASC by block height
-	slices.SortFunc(inputs, func(t1, t2 *types.Block) int {
-		return cmp.Compare(t1.Header.Height, t2.Header.Height)
-	})
-
-	latestBlock, err := p.CurrentBlock(ctx)
+	// Process the given blocks before inserting to the database
+	inputs, err := p.process(ctx, inputs)
 	if err != nil {
-		return errors.Wrap(err, "failed to get latest indexed block header")
-	}
-
-	// check if the given blocks are continue from the latest indexed block
-	// return an error to prevent inserting out-of-order blocks or duplicate blocks
-	if inputs[0].Header.Height != latestBlock.Height+1 {
-		return errors.New("given blocks are not continue from the latest indexed block")
-	}
-
-	// check if the given blocks are in sequence and not missing any block
-	for i := 1; i < len(inputs); i++ {
-		if inputs[i].Header.Height != inputs[i-1].Header.Height+1 {
-			return errors.New("given blocks are not in sequence")
-		}
+		return errors.WithStack(err)
 	}
 
 	// Insert blocks
-	for _, b := range inputs {
-		err := p.bitcoinDg.InsertBlock(ctx, b)
-		if err != nil {
-			return errors.Wrapf(err, "failed to insert block, height: %d, hash: %s", b.Header.Height, b.Header.Hash)
-		}
-		logger.InfoContext(ctx, "Block inserted", slog.Int64("height", b.Header.Height), slogx.Stringer("hash", b.Header.Hash))
+	if err := p.bitcoinDg.InsertBlocks(ctx, inputs); err != nil {
+		return errors.Wrapf(err, "error during insert blocks, from: %d, to: %d", inputs[0].Header.Height, inputs[len(inputs)-1].Header.Height)
 	}
 
 	return nil
@@ -97,6 +71,12 @@ func (p *Processor) GetIndexedBlock(ctx context.Context, height int64) (types.Bl
 }
 
 func (p *Processor) RevertData(ctx context.Context, from int64) error {
+	// to prevent remove txin/txout of duplicated coinbase transaction in the blocks 91842 and 91880
+	// if you really want to revert the data before the block `227835`, you should reset the database and reindex the data instead.
+	if from <= lastV1Block.Height {
+		return errors.Wrapf(errs.InvalidArgument, "can't revert data before block version 2, height: %d", lastV1Block.Height)
+	}
+
 	if err := p.bitcoinDg.RevertBlocks(ctx, from); err != nil {
 		return errors.WithStack(err)
 	}

modules/bitcoin/processor_process.go

@@ -0,0 +1,91 @@
+package bitcoin
+
+import (
+	"cmp"
+	"context"
+	"slices"
+
+	"github.com/cockroachdb/errors"
+	"github.com/gaze-network/indexer-network/core/types"
+)
+
+// process is a processing rules for the given blocks before inserting to the database
+//
+// this function will modify the given data directly.
+func (p *Processor) process(ctx context.Context, blocks []*types.Block) ([]*types.Block, error) {
+	if len(blocks) == 0 {
+		return blocks, nil
+	}
+
+	// Sort ASC by block height
+	slices.SortFunc(blocks, func(t1, t2 *types.Block) int {
+		return cmp.Compare(t1.Header.Height, t2.Header.Height)
+	})
+
+	if !p.isContinueFromLatestIndexedBlock(ctx, blocks[0]) {
+		return nil, errors.New("given blocks are not continue from the latest indexed block")
+	}
+
+	if !p.isBlocksSequential(blocks) {
+		return nil, errors.New("given blocks are not in sequence")
+	}
+
+	p.removeDuplicateCoinbaseTxInputsOutputs(blocks)
+
+	return blocks, nil
+}
+
+// check if the given blocks are continue from the latest indexed block
+// to prevent inserting out-of-order blocks or duplicate blocks
+func (p *Processor) isBlocksSequential(blocks []*types.Block) bool {
+	if len(blocks) == 0 {
+		return true
+	}
+
+	for i, block := range blocks {
+		if i == 0 {
+			continue
+		}
+
+		if block.Header.Height != blocks[i-1].Header.Height+1 {
+			return false
+		}
+	}
+
+	return true
+}
+
+// check if the given blocks are continue from the latest indexed block
+// to prevent inserting out-of-order blocks or duplicate blocks
+func (p *Processor) isContinueFromLatestIndexedBlock(ctx context.Context, block *types.Block) bool {
+	latestBlock, err := p.CurrentBlock(ctx)
+	if err != nil {
+		return false
+	}
+
+	return block.Header.Height == latestBlock.Height+1
+}
+
+// there 2 coinbase transaction that are duplicated in the blocks 91842 and 91880.
+// if the given block version is v1 and height is `91842` or `91880`,
+// then remove transaction inputs/outputs to prevent duplicate txin/txout error when inserting to the database.
+//
+// Theses duplicated coinbase transactions are having the same transaction input(from coinbase)/output and
+// utxo from these 2 duplicated coinbase txs can redeem only once), so, it's safe to remove them and can
+// use inputs/outputs from the previous block.
+//
+// Duplicate Coinbase Transactions:
+//   - `454279874213763724535987336644243549a273058910332236515429488599` in blocks 91812, 91842
+//   - `e3bf3d07d4b0375638d5f1db5255fe07ba2c4cb067cd81b84ee974b6585fb468` in blocks 91722, 91880
+//
+// This function will modify the given data directly.
+func (p *Processor) removeDuplicateCoinbaseTxInputsOutputs(blocks []*types.Block) {
+	for _, block := range blocks {
+		header := block.Header
+		if header.Version == 1 && (header.Height == 91842 || header.Height == 91880) {
+			// remove transaction inputs/outputs from coinbase transaction (first transaction)
+			block.Transactions[0].TxIn = nil
+			block.Transactions[0].TxOut = nil
+		}
+	}
+}

modules/bitcoin/processor_process_test.go

@@ -0,0 +1,144 @@
+package bitcoin
+
+import (
+	"fmt"
+	"testing"
+
+	"github.com/gaze-network/indexer-network/core/types"
+	"github.com/stretchr/testify/assert"
+)
+
+func TestDuplicateCoinbaseTxHashHandling(t *testing.T) {
+	processor := Processor{}
+	generator := func() []*types.Block {
+		return []*types.Block{
+			{
+				Header: types.BlockHeader{Height: 91842, Version: 1},
+				Transactions: []*types.Transaction{
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+				},
+			},
+			{
+				Header: types.BlockHeader{Height: 91880, Version: 1},
+				Transactions: []*types.Transaction{
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+				},
+			},
+		}
+	}
+
+	t.Run("all_duplicated_txs", func(t *testing.T) {
+		blocks := generator()
+		processor.removeDuplicateCoinbaseTxInputsOutputs(blocks)
+
+		assert.Len(t, blocks, 2, "should not remove any blocks")
+		for _, block := range blocks {
+			assert.Len(t, block.Transactions, 2, "should not remove any transactions")
+			assert.Len(t, block.Transactions[0].TxIn, 0, "should remove tx inputs from coinbase transaction")
+			assert.Len(t, block.Transactions[0].TxOut, 0, "should remove tx outputs from coinbase transaction")
+		}
+	})
+
+	t.Run("not_duplicated_txs", func(t *testing.T) {
+		blocks := []*types.Block{
+			{
+				Header: types.BlockHeader{Height: 91812, Version: 1},
+				Transactions: []*types.Transaction{
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+				},
+			},
+			{
+				Header: types.BlockHeader{Height: 91722, Version: 1},
+				Transactions: []*types.Transaction{
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+				},
+			},
+		}
+		processor.removeDuplicateCoinbaseTxInputsOutputs(blocks)
+
+		assert.Len(t, blocks, 2, "should not remove any blocks")
+		for _, block := range blocks {
+			assert.Len(t, block.Transactions, 2, "should not remove any transactions")
+			assert.Len(t, block.Transactions[0].TxIn, 4, "should not remove tx inputs from coinbase transaction")
+			assert.Len(t, block.Transactions[0].TxOut, 4, "should not remove tx outputs from coinbase transaction")
+		}
+	})
+
+	t.Run("mixed", func(t *testing.T) {
+		blocks := []*types.Block{
+			{
+				Header: types.BlockHeader{Height: 91812, Version: 1},
+				Transactions: []*types.Transaction{
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+					{
+						TxIn:  []*types.TxIn{{}, {}, {}, {}},
+						TxOut: []*types.TxOut{{}, {}, {}, {}},
+					},
+				},
+			},
+		}
+		blocks = append(blocks, generator()...)
+		blocks = append(blocks, &types.Block{
+			Header: types.BlockHeader{Height: 91722, Version: 1},
+			Transactions: []*types.Transaction{
+				{
+					TxIn:  []*types.TxIn{{}, {}, {}, {}},
+					TxOut: []*types.TxOut{{}, {}, {}, {}},
+				},
+				{
+					TxIn:  []*types.TxIn{{}, {}, {}, {}},
+					TxOut: []*types.TxOut{{}, {}, {}, {}},
+				},
+			},
+		})
+		processor.removeDuplicateCoinbaseTxInputsOutputs(blocks)
+
+		assert.Len(t, blocks, 4, "should not remove any blocks")
+
+		// only 2nd and 3rd blocks should be modified
+		for i, block := range blocks {
+			t.Run(fmt.Sprint(i), func(t *testing.T) {
+				if i == 1 || i == 2 {
+					assert.Len(t, block.Transactions, 2, "should not remove any transactions")
+					assert.Len(t, block.Transactions[0].TxIn, 0, "should remove tx inputs from coinbase transaction")
+					assert.Len(t, block.Transactions[0].TxOut, 0, "should remove tx outputs from coinbase transaction")
+				} else {
+					assert.Len(t, block.Transactions, 2, "should not remove any transactions")
+					assert.Lenf(t, block.Transactions[0].TxIn, 4, "should not remove tx inputs from coinbase transaction")
+					assert.Len(t, block.Transactions[0].TxOut, 4, "should not remove tx outputs from coinbase transaction")
+				}
+			})
+		}
+	})
+}

modules/bitcoin/repository/postgres/block.go

@@ -28,8 +28,12 @@ func (r *Repository) GetLatestBlockHeader(ctx context.Context) (types.BlockHeade
 	return data, nil
 }
 
-func (r *Repository) InsertBlock(ctx context.Context, block *types.Block) error {
-	blockParams, txParams, txoutParams, txinParams := mapBlockTypeToParams(block)
+func (r *Repository) InsertBlocks(ctx context.Context, blocks []*types.Block) error {
+	if len(blocks) == 0 {
+		return nil
+	}
+
+	blockParams, txParams, txoutParams, txinParams := mapBlocksTypeToParams(blocks)
 
 	tx, err := r.db.Begin(ctx)
 	if err != nil {
@@ -39,28 +43,22 @@ func (r *Repository) InsertBlock(ctx context.Context, block *types.Block) error
 
 	queries := r.queries.WithTx(tx)
 
-	if err := queries.InsertBlock(ctx, blockParams); err != nil {
-		return errors.Wrapf(err, "failed to insert block, height: %d, hash: %s", blockParams.BlockHeight, blockParams.BlockHash)
+	if err := queries.BatchInsertBlocks(ctx, blockParams); err != nil {
+		return errors.Wrap(err, "failed to batch insert block headers")
 	}
 
-	for _, params := range txParams {
-		if err := queries.InsertTransaction(ctx, params); err != nil {
-			return errors.Wrapf(err, "failed to insert transaction, hash: %s", params.TxHash)
-		}
+	if err := queries.BatchInsertTransactions(ctx, txParams); err != nil {
+		return errors.Wrap(err, "failed to batch insert transactions")
 	}
 
 	// Should insert txout first, then txin
 	// Because txin references txout
-	for _, params := range txoutParams {
-		if err := queries.InsertTransactionTxOut(ctx, params); err != nil {
-			return errors.Wrapf(err, "failed to insert transaction txout, %v:%v", params.TxHash, params.TxIdx)
-		}
+	if err := queries.BatchInsertTransactionTxOuts(ctx, txoutParams); err != nil {
+		return errors.Wrap(err, "failed to batch insert transaction txins")
 	}
 
-	for _, params := range txinParams {
-		if err := queries.InsertTransactionTxIn(ctx, params); err != nil {
-			return errors.Wrapf(err, "failed to insert transaction txin, %v:%v", params.TxHash, params.TxIdx)
-		}
+	if err := queries.BatchInsertTransactionTxIns(ctx, txinParams); err != nil {
+		return errors.Wrap(err, "failed to batch insert transaction txins")
 	}
 
 	if err := tx.Commit(ctx); err != nil {

modules/bitcoin/repository/postgres/gen/data.sql.go

@@ -11,6 +11,152 @@ import (
 	"github.com/jackc/pgx/v5/pgtype"
 )
 
+const batchInsertBlocks = `-- name: BatchInsertBlocks :exec
+INSERT INTO bitcoin_blocks ("block_height","block_hash","version","merkle_root","prev_block_hash","timestamp","bits","nonce")
+VALUES (
+	unnest($1::INT[]),
+	unnest($2::TEXT[]),
+	unnest($3::INT[]),
+	unnest($4::TEXT[]),
+	unnest($5::TEXT[]),
+	unnest($6::TIMESTAMP WITH TIME ZONE[]), -- or use TIMESTAMPTZ
+	unnest($7::BIGINT[]),
+	unnest($8::BIGINT[])
+)
+`
+
+type BatchInsertBlocksParams struct {
+	BlockHeightArr   []int32
+	BlockHashArr     []string
+	VersionArr       []int32
+	MerkleRootArr    []string
+	PrevBlockHashArr []string
+	TimestampArr     []pgtype.Timestamptz
+	BitsArr          []int64
+	NonceArr         []int64
+}
+
+func (q *Queries) BatchInsertBlocks(ctx context.Context, arg BatchInsertBlocksParams) error {
+	_, err := q.db.Exec(ctx, batchInsertBlocks,
+		arg.BlockHeightArr,
+		arg.BlockHashArr,
+		arg.VersionArr,
+		arg.MerkleRootArr,
+		arg.PrevBlockHashArr,
+		arg.TimestampArr,
+		arg.BitsArr,
+		arg.NonceArr,
+	)
+	return err
+}
+
+const batchInsertTransactionTxIns = `-- name: BatchInsertTransactionTxIns :exec
+WITH update_txout AS (
+	UPDATE "bitcoin_transaction_txouts"
+	SET "is_spent" = true
+	FROM (SELECT unnest($1::TEXT[]) as tx_hash, unnest($2::INT[]) as tx_idx) as txin
+	WHERE "bitcoin_transaction_txouts"."tx_hash" = txin.tx_hash AND "bitcoin_transaction_txouts"."tx_idx" = txin.tx_idx AND "is_spent" = false
+	RETURNING "bitcoin_transaction_txouts"."tx_hash", "bitcoin_transaction_txouts"."tx_idx", "pkscript"
+), prepare_insert AS (
+	SELECT input.tx_hash, input.tx_idx, prevout_tx_hash, prevout_tx_idx, update_txout.pkscript as prevout_pkscript, scriptsig, witness, sequence
+		FROM (
+			SELECT 
+				unnest($3::TEXT[]) as tx_hash,
+				unnest($4::INT[]) as tx_idx,
+				unnest($1::TEXT[]) as prevout_tx_hash,
+				unnest($2::INT[]) as prevout_tx_idx,
+				unnest($5::TEXT[]) as scriptsig,
+				unnest($6::TEXT[]) as witness,
+				unnest($7::INT[]) as sequence
+		) input LEFT JOIN update_txout ON "update_txout"."tx_hash" = "input"."prevout_tx_hash" AND "update_txout"."tx_idx" = "input"."prevout_tx_idx"
+)
+INSERT INTO bitcoin_transaction_txins ("tx_hash","tx_idx","prevout_tx_hash","prevout_tx_idx", "prevout_pkscript","scriptsig","witness","sequence") 
+SELECT "tx_hash", "tx_idx", "prevout_tx_hash", "prevout_tx_idx", "prevout_pkscript", "scriptsig", "witness", "sequence" FROM prepare_insert
+`
+
+type BatchInsertTransactionTxInsParams struct {
+	PrevoutTxHashArr []string
+	PrevoutTxIdxArr  []int32
+	TxHashArr        []string
+	TxIdxArr         []int32
+	ScriptsigArr     []string
+	WitnessArr       []string
+	SequenceArr      []int32
+}
+
+func (q *Queries) BatchInsertTransactionTxIns(ctx context.Context, arg BatchInsertTransactionTxInsParams) error {
+	_, err := q.db.Exec(ctx, batchInsertTransactionTxIns,
+		arg.PrevoutTxHashArr,
+		arg.PrevoutTxIdxArr,
+		arg.TxHashArr,
+		arg.TxIdxArr,
+		arg.ScriptsigArr,
+		arg.WitnessArr,
+		arg.SequenceArr,
+	)
+	return err
+}
+
+const batchInsertTransactionTxOuts = `-- name: BatchInsertTransactionTxOuts :exec
+INSERT INTO bitcoin_transaction_txouts ("tx_hash","tx_idx","pkscript","value")
+VALUES (
+	unnest($1::TEXT[]),
+	unnest($2::INT[]),
+	unnest($3::TEXT[]),
+	unnest($4::BIGINT[])
+)
+`
+
+type BatchInsertTransactionTxOutsParams struct {
+	TxHashArr   []string
+	TxIdxArr    []int32
+	PkscriptArr []string
+	ValueArr    []int64
+}
+
+func (q *Queries) BatchInsertTransactionTxOuts(ctx context.Context, arg BatchInsertTransactionTxOutsParams) error {
+	_, err := q.db.Exec(ctx, batchInsertTransactionTxOuts,
+		arg.TxHashArr,
+		arg.TxIdxArr,
+		arg.PkscriptArr,
+		arg.ValueArr,
+	)
+	return err
+}
+
+const batchInsertTransactions = `-- name: BatchInsertTransactions :exec
+INSERT INTO bitcoin_transactions ("tx_hash","version","locktime","block_height","block_hash","idx")
+VALUES (
+	unnest($1::TEXT[]),
+	unnest($2::INT[]),
+	unnest($3::BIGINT[]),
+	unnest($4::INT[]),
+	unnest($5::TEXT[]),
+	unnest($6::INT[])
+)
+`
+
+type BatchInsertTransactionsParams struct {
+	TxHashArr      []string
+	VersionArr     []int32
+	LocktimeArr    []int64
+	BlockHeightArr []int32
+	BlockHashArr   []string
+	IdxArr         []int32
+}
+
+func (q *Queries) BatchInsertTransactions(ctx context.Context, arg BatchInsertTransactionsParams) error {
+	_, err := q.db.Exec(ctx, batchInsertTransactions,
+		arg.TxHashArr,
+		arg.VersionArr,
+		arg.LocktimeArr,
+		arg.BlockHeightArr,
+		arg.BlockHashArr,
+		arg.IdxArr,
+	)
+	return err
+}
+
 const getBlockByHeight = `-- name: GetBlockByHeight :one
 SELECT block_height, block_hash, version, merkle_root, prev_block_hash, timestamp, bits, nonce FROM bitcoin_blocks WHERE block_height = $1
 `
@@ -235,86 +381,6 @@ func (q *Queries) InsertBlock(ctx context.Context, arg InsertBlockParams) error
 	return err
 }
 
-const insertTransaction = `-- name: InsertTransaction :exec
-INSERT INTO bitcoin_transactions ("tx_hash","version","locktime","block_height","block_hash","idx") VALUES ($1, $2, $3, $4, $5, $6)
-`
-
-type InsertTransactionParams struct {
-	TxHash      string
-	Version     int32
-	Locktime    int64
-	BlockHeight int32
-	BlockHash   string
-	Idx         int32
-}
-
-func (q *Queries) InsertTransaction(ctx context.Context, arg InsertTransactionParams) error {
-	_, err := q.db.Exec(ctx, insertTransaction,
-		arg.TxHash,
-		arg.Version,
-		arg.Locktime,
-		arg.BlockHeight,
-		arg.BlockHash,
-		arg.Idx,
-	)
-	return err
-}
-
-const insertTransactionTxIn = `-- name: InsertTransactionTxIn :exec
-WITH update_txout AS (
-	UPDATE "bitcoin_transaction_txouts"
-	SET "is_spent" = true
-	WHERE "tx_hash" = $3 AND "tx_idx" = $4 AND "is_spent" = false -- TODO: should throw an error if already spent
-	RETURNING "pkscript"
-)
-INSERT INTO bitcoin_transaction_txins ("tx_hash","tx_idx","prevout_tx_hash","prevout_tx_idx","prevout_pkscript","scriptsig","witness","sequence") 
-VALUES ($1, $2, $3, $4, (SELECT "pkscript" FROM update_txout), $5, $6, $7)
-`
-
-type InsertTransactionTxInParams struct {
-	TxHash        string
-	TxIdx         int32
-	PrevoutTxHash string
-	PrevoutTxIdx  int32
-	Scriptsig     string
-	Witness       pgtype.Text
-	Sequence      int64
-}
-
-func (q *Queries) InsertTransactionTxIn(ctx context.Context, arg InsertTransactionTxInParams) error {
-	_, err := q.db.Exec(ctx, insertTransactionTxIn,
-		arg.TxHash,
-		arg.TxIdx,
-		arg.PrevoutTxHash,
-		arg.PrevoutTxIdx,
-		arg.Scriptsig,
-		arg.Witness,
-		arg.Sequence,
-	)
-	return err
-}
-
-const insertTransactionTxOut = `-- name: InsertTransactionTxOut :exec
-INSERT INTO bitcoin_transaction_txouts ("tx_hash","tx_idx","pkscript","value") VALUES ($1, $2, $3, $4)
-`
-
-type InsertTransactionTxOutParams struct {
-	TxHash   string
-	TxIdx    int32
-	Pkscript string
-	Value    int64
-}
-
-func (q *Queries) InsertTransactionTxOut(ctx context.Context, arg InsertTransactionTxOutParams) error {
-	_, err := q.db.Exec(ctx, insertTransactionTxOut,
-		arg.TxHash,
-		arg.TxIdx,
-		arg.Pkscript,
-		arg.Value,
-	)
-	return err
-}
-
 const revertData = `-- name: RevertData :exec
 WITH delete_tx AS (
 	DELETE FROM "bitcoin_transactions" WHERE "block_height" >= $1

modules/bitcoin/repository/postgres/gen/models.go

@@ -48,7 +48,7 @@ type BitcoinTransactionTxin struct {
 	PrevoutTxIdx    int32
 	PrevoutPkscript pgtype.Text
 	Scriptsig       string
-	Witness         pgtype.Text
+	Witness         string
 	Sequence        int64
 }
 

modules/bitcoin/repository/postgres/mappers.go

@@ -55,10 +55,7 @@ func mapBlockHeaderModelToType(src gen.BitcoinBlock) (types.BlockHeader, error)
 	}, nil
 }
 
-func mapBlockTypeToParams(src *types.Block) (gen.InsertBlockParams, []gen.InsertTransactionParams, []gen.InsertTransactionTxOutParams, []gen.InsertTransactionTxInParams) {
-	txs := make([]gen.InsertTransactionParams, 0, len(src.Transactions))
-	txouts := make([]gen.InsertTransactionTxOutParams, 0)
-	txins := make([]gen.InsertTransactionTxInParams, 0)
+func mapBlockTypeToParams(src *types.Block) (gen.InsertBlockParams, gen.BatchInsertTransactionsParams, gen.BatchInsertTransactionTxOutsParams, gen.BatchInsertTransactionTxInsParams) {
 	block := gen.InsertBlockParams{
 		BlockHeight:   int32(src.Header.Height),
 		BlockHash:     src.Header.Hash.String(),
@@ -72,48 +69,155 @@ func mapBlockTypeToParams(src *types.Block) (gen.InsertBlockParams, []gen.Insert
 		Bits:  int64(src.Header.Bits),
 		Nonce: int64(src.Header.Nonce),
 	}
+	txs := gen.BatchInsertTransactionsParams{
+		TxHashArr:      []string{},
+		VersionArr:     []int32{},
+		LocktimeArr:    []int64{},
+		BlockHeightArr: []int32{},
+		BlockHashArr:   []string{},
+		IdxArr:         []int32{},
+	}
+	txouts := gen.BatchInsertTransactionTxOutsParams{
+		TxHashArr:   []string{},
+		TxIdxArr:    []int32{},
+		PkscriptArr: []string{},
+		ValueArr:    []int64{},
+	}
+	txins := gen.BatchInsertTransactionTxInsParams{
+		PrevoutTxHashArr: []string{},
+		PrevoutTxIdxArr:  []int32{},
+		TxHashArr:        []string{},
+		TxIdxArr:         []int32{},
+		ScriptsigArr:     []string{},
+		WitnessArr:       []string{},
+		SequenceArr:      []int32{},
+	}
+
 	for txIdx, srcTx := range src.Transactions {
-		tx := gen.InsertTransactionParams{
-			TxHash:      srcTx.TxHash.String(),
-			Version:     srcTx.Version,
-			Locktime:    int64(srcTx.LockTime),
-			BlockHeight: int32(src.Header.Height),
-			BlockHash:   src.Header.Hash.String(),
-			Idx:         int32(txIdx),
-		}
-		txs = append(txs, tx)
+		txHash := srcTx.TxHash.String()
+		// Batch insert transactions
+		txs.TxHashArr = append(txs.TxHashArr, txHash)
+		txs.VersionArr = append(txs.VersionArr, srcTx.Version)
+		txs.LocktimeArr = append(txs.LocktimeArr, int64(srcTx.LockTime))
+		txs.BlockHeightArr = append(txs.BlockHeightArr, int32(src.Header.Height))
+		txs.BlockHashArr = append(txs.BlockHashArr, src.Header.Hash.String())
+		txs.IdxArr = append(txs.IdxArr, int32(txIdx))
 
+		// Batch insert txins
 		for idx, txin := range srcTx.TxIn {
-			var witness pgtype.Text
+			var witness string
 			if len(txin.Witness) > 0 {
-				witness = pgtype.Text{
-					String: btcutils.WitnessToString(txin.Witness),
-					Valid:  true,
-				}
+				witness = btcutils.WitnessToString(txin.Witness)
 			}
-			txins = append(txins, gen.InsertTransactionTxInParams{
-				TxHash:        tx.TxHash,
-				TxIdx:         int32(idx),
-				PrevoutTxHash: txin.PreviousOutTxHash.String(),
-				PrevoutTxIdx:  int32(txin.PreviousOutIndex),
-				Scriptsig:     hex.EncodeToString(txin.SignatureScript),
-				Witness:       witness,
-				Sequence:      int64(txin.Sequence),
-			})
+			txins.TxHashArr = append(txins.TxHashArr, txHash)
+			txins.TxIdxArr = append(txins.TxIdxArr, int32(idx))
+			txins.PrevoutTxHashArr = append(txins.PrevoutTxHashArr, txin.PreviousOutTxHash.String())
+			txins.PrevoutTxIdxArr = append(txins.PrevoutTxIdxArr, int32(txin.PreviousOutIndex))
+			txins.ScriptsigArr = append(txins.ScriptsigArr, hex.EncodeToString(txin.SignatureScript))
+			txins.WitnessArr = append(txins.WitnessArr, witness)
+			txins.SequenceArr = append(txins.SequenceArr, int32(txin.Sequence))
 		}
 
+		// Batch insert txouts
 		for idx, txout := range srcTx.TxOut {
-			txouts = append(txouts, gen.InsertTransactionTxOutParams{
-				TxHash:   tx.TxHash,
-				TxIdx:    int32(idx),
-				Pkscript: hex.EncodeToString(txout.PkScript),
-				Value:    txout.Value,
-			})
+			txouts.TxHashArr = append(txouts.TxHashArr, txHash)
+			txouts.TxIdxArr = append(txouts.TxIdxArr, int32(idx))
+			txouts.PkscriptArr = append(txouts.PkscriptArr, hex.EncodeToString(txout.PkScript))
+			txouts.ValueArr = append(txouts.ValueArr, txout.Value)
 		}
 	}
 	return block, txs, txouts, txins
 }
 
+func mapBlocksTypeToParams(src []*types.Block) (gen.BatchInsertBlocksParams, gen.BatchInsertTransactionsParams, gen.BatchInsertTransactionTxOutsParams, gen.BatchInsertTransactionTxInsParams) {
+	blocks := gen.BatchInsertBlocksParams{
+		BlockHeightArr:   make([]int32, 0, len(src)),
+		BlockHashArr:     make([]string, 0, len(src)),
+		VersionArr:       make([]int32, 0, len(src)),
+		MerkleRootArr:    make([]string, 0, len(src)),
+		PrevBlockHashArr: make([]string, 0, len(src)),
+		TimestampArr:     make([]pgtype.Timestamptz, 0, len(src)),
+		BitsArr:          make([]int64, 0, len(src)),
+		NonceArr:         make([]int64, 0, len(src)),
+	}
+	txs := gen.BatchInsertTransactionsParams{
+		TxHashArr:      []string{},
+		VersionArr:     []int32{},
+		LocktimeArr:    []int64{},
+		BlockHeightArr: []int32{},
+		BlockHashArr:   []string{},
+		IdxArr:         []int32{},
+	}
+	txouts := gen.BatchInsertTransactionTxOutsParams{
+		TxHashArr:   []string{},
+		TxIdxArr:    []int32{},
+		PkscriptArr: []string{},
+		ValueArr:    []int64{},
+	}
+	txins := gen.BatchInsertTransactionTxInsParams{
+		PrevoutTxHashArr: []string{},
+		PrevoutTxIdxArr:  []int32{},
+		TxHashArr:        []string{},
+		TxIdxArr:         []int32{},
+		ScriptsigArr:     []string{},
+		WitnessArr:       []string{},
+		SequenceArr:      []int32{},
+	}
+
+	for _, block := range src {
+		blockHash := block.Header.Hash.String()
+
+		// Batch insert blocks
+		blocks.BlockHeightArr = append(blocks.BlockHeightArr, int32(block.Header.Height))
+		blocks.BlockHashArr = append(blocks.BlockHashArr, blockHash)
+		blocks.VersionArr = append(blocks.VersionArr, block.Header.Version)
+		blocks.MerkleRootArr = append(blocks.MerkleRootArr, block.Header.MerkleRoot.String())
+		blocks.PrevBlockHashArr = append(blocks.PrevBlockHashArr, block.Header.PrevBlock.String())
+		blocks.TimestampArr = append(blocks.TimestampArr, pgtype.Timestamptz{
+			Time:  block.Header.Timestamp,
+			Valid: true,
+		})
+		blocks.BitsArr = append(blocks.BitsArr, int64(block.Header.Bits))
+		blocks.NonceArr = append(blocks.NonceArr, int64(block.Header.Nonce))
+
+		for txIdx, srcTx := range block.Transactions {
+			txHash := srcTx.TxHash.String()
+
+			// Batch insert transactions
+			txs.TxHashArr = append(txs.TxHashArr, txHash)
+			txs.VersionArr = append(txs.VersionArr, srcTx.Version)
+			txs.LocktimeArr = append(txs.LocktimeArr, int64(srcTx.LockTime))
+			txs.BlockHeightArr = append(txs.BlockHeightArr, int32(block.Header.Height))
+			txs.BlockHashArr = append(txs.BlockHashArr, blockHash)
+			txs.IdxArr = append(txs.IdxArr, int32(txIdx))
+
+			// Batch insert txins
+			for idx, txin := range srcTx.TxIn {
+				var witness string
+				if len(txin.Witness) > 0 {
+					witness = btcutils.WitnessToString(txin.Witness)
+				}
+				txins.TxHashArr = append(txins.TxHashArr, txHash)
+				txins.TxIdxArr = append(txins.TxIdxArr, int32(idx))
+				txins.PrevoutTxHashArr = append(txins.PrevoutTxHashArr, txin.PreviousOutTxHash.String())
+				txins.PrevoutTxIdxArr = append(txins.PrevoutTxIdxArr, int32(txin.PreviousOutIndex))
+				txins.ScriptsigArr = append(txins.ScriptsigArr, hex.EncodeToString(txin.SignatureScript))
+				txins.WitnessArr = append(txins.WitnessArr, witness)
+				txins.SequenceArr = append(txins.SequenceArr, int32(txin.Sequence))
+			}
+
+			// Batch insert txouts
+			for idx, txout := range srcTx.TxOut {
+				txouts.TxHashArr = append(txouts.TxHashArr, txHash)
+				txouts.TxIdxArr = append(txouts.TxIdxArr, int32(idx))
+				txouts.PkscriptArr = append(txouts.PkscriptArr, hex.EncodeToString(txout.PkScript))
+				txouts.ValueArr = append(txouts.ValueArr, txout.Value)
+			}
+		}
+	}
+	return blocks, txs, txouts, txins
+}
+
 func mapTransactionModelToType(src gen.BitcoinTransaction, txInModel []gen.BitcoinTransactionTxin, txOutModels []gen.BitcoinTransactionTxout) (types.Transaction, error) {
 	blockHash, err := chainhash.NewHashFromStr(src.BlockHash)
 	if err != nil {
@@ -146,13 +250,9 @@ func mapTransactionModelToType(src gen.BitcoinTransaction, txInModel []gen.Bitco
 			return types.Transaction{}, errors.Wrap(err, "failed to parse prevout tx hash")
 		}
 
-		var witness [][]byte
-		if txInModel.Witness.Valid {
-			w, err := btcutils.WitnessFromString(txInModel.Witness.String)
-			if err != nil {
-				return types.Transaction{}, errors.Wrap(err, "failed to parse witness from hex string")
-			}
-			witness = w
+		witness, err := btcutils.WitnessFromString(txInModel.Witness)
+		if err != nil {
+			return types.Transaction{}, errors.Wrap(err, "failed to parse witness from hex string")
 		}
 
 		txIns = append(txIns, &types.TxIn{

modules/runes/api/httphandler/get_transactions.go

@@ -1,13 +1,12 @@
 package httphandler
 
 import (
-	"bytes"
 	"encoding/hex"
+	"slices"
 
 	"github.com/btcsuite/btcd/chaincfg/chainhash"
 	"github.com/cockroachdb/errors"
 	"github.com/gaze-network/indexer-network/common/errs"
-	"github.com/gaze-network/indexer-network/modules/runes/internal/entity"
 	"github.com/gaze-network/indexer-network/modules/runes/runes"
 	"github.com/gaze-network/uint128"
 	"github.com/gofiber/fiber/v2"
@@ -84,6 +83,7 @@ type amountWithDecimal struct {
 type transaction struct {
 	TxHash      chainhash.Hash               `json:"txHash"`
 	BlockHeight uint64                       `json:"blockHeight"`
+	Index       uint32                       `json:"index"`
 	Timestamp   int64                        `json:"timestamp"`
 	Inputs      []txInputOutput              `json:"inputs"`
 	Outputs     []txInputOutput              `json:"outputs"`
@@ -116,15 +116,6 @@ func (h *HttpHandler) GetTransactions(ctx *fiber.Ctx) (err error) {
 		}
 	}
 
-	blockHeight := req.BlockHeight
-	if blockHeight == 0 {
-		blockHeader, err := h.usecase.GetLatestBlock(ctx.UserContext())
-		if err != nil {
-			return errors.Wrap(err, "error during GetLatestBlock")
-		}
-		blockHeight = uint64(blockHeader.Height)
-	}
-
 	var runeId runes.RuneId
 	if req.Id != "" {
 		var ok bool
@@ -134,68 +125,23 @@ func (h *HttpHandler) GetTransactions(ctx *fiber.Ctx) (err error) {
 		}
 	}
 
-	txs, err := h.usecase.GetTransactionsByHeight(ctx.UserContext(), blockHeight)
-	if err != nil {
-		return errors.Wrap(err, "error during GetTransactionsByHeight")
+	blockHeight := req.BlockHeight
+	// set blockHeight to the latest block height blockHeight, pkScript, and runeId are not provided
+	if blockHeight == 0 && pkScript == nil && runeId == (runes.RuneId{}) {
+		blockHeader, err := h.usecase.GetLatestBlock(ctx.UserContext())
+		if err != nil {
+			return errors.Wrap(err, "error during GetLatestBlock")
+		}
+		blockHeight = uint64(blockHeader.Height)
 	}
 
-	filteredTxs := make([]*entity.RuneTransaction, 0)
-	isTxContainPkScript := func(tx *entity.RuneTransaction) bool {
-		for _, input := range tx.Inputs {
-			if bytes.Equal(input.PkScript, pkScript) {
-				return true
-			}
-		}
-		for _, output := range tx.Outputs {
-			if bytes.Equal(output.PkScript, pkScript) {
-				return true
-			}
-		}
-		return false
-	}
-	isTxContainRuneId := func(tx *entity.RuneTransaction) bool {
-		for _, input := range tx.Inputs {
-			if input.RuneId == runeId {
-				return true
-			}
-		}
-		for _, output := range tx.Outputs {
-			if output.RuneId == runeId {
-				return true
-			}
-		}
-		for mintedRuneId := range tx.Mints {
-			if mintedRuneId == runeId {
-				return true
-			}
-		}
-		for burnedRuneId := range tx.Burns {
-			if burnedRuneId == runeId {
-				return true
-			}
-		}
-		if tx.Runestone != nil {
-			if tx.Runestone.Mint != nil && *tx.Runestone.Mint == runeId {
-				return true
-			}
-			// returns true if this tx etched this runeId
-			if tx.RuneEtched && tx.BlockHeight == runeId.BlockHeight && tx.Index == runeId.TxIndex {
-				return true
-			}
-		}
-		return false
-	}
-	for _, tx := range txs {
-		if pkScript != nil && !isTxContainPkScript(tx) {
-			continue
-		}
-		if runeId != (runes.RuneId{}) && !isTxContainRuneId(tx) {
-			continue
-		}
-		filteredTxs = append(filteredTxs, tx)
+	txs, err := h.usecase.GetRuneTransactions(ctx.UserContext(), pkScript, runeId, blockHeight)
+	if err != nil {
+		return errors.Wrap(err, "error during GetRuneTransactions")
 	}
+
 	var allRuneIds []runes.RuneId
-	for _, tx := range filteredTxs {
+	for _, tx := range txs {
 		for id := range tx.Mints {
 			allRuneIds = append(allRuneIds, id)
 		}
@@ -215,11 +161,12 @@ func (h *HttpHandler) GetTransactions(ctx *fiber.Ctx) (err error) {
 		return errors.Wrap(err, "error during GetRuneEntryByRuneIdBatch")
 	}
 
-	txList := make([]transaction, 0, len(filteredTxs))
-	for _, tx := range filteredTxs {
+	txList := make([]transaction, 0, len(txs))
+	for _, tx := range txs {
 		respTx := transaction{
 			TxHash:      tx.Hash,
 			BlockHeight: tx.BlockHeight,
+			Index:       tx.Index,
 			Timestamp:   tx.Timestamp.Unix(),
 			Inputs:      make([]txInputOutput, 0, len(tx.Inputs)),
 			Outputs:     make([]txInputOutput, 0, len(tx.Outputs)),
@@ -309,6 +256,13 @@ func (h *HttpHandler) GetTransactions(ctx *fiber.Ctx) (err error) {
 		}
 		txList = append(txList, respTx)
 	}
+	// sort by block height ASC, then index ASC
+	slices.SortFunc(txList, func(t1, t2 transaction) int {
+		if t1.BlockHeight != t2.BlockHeight {
+			return int(t1.BlockHeight - t2.BlockHeight)
+		}
+		return int(t1.Index - t2.Index)
+	})
 
 	resp := getTransactionsResponse{
 		Result: &getTransactionsResult{

modules/runes/database/postgresql/migrations/000001_initialize_tables.up.sql

@@ -72,6 +72,7 @@ CREATE TABLE IF NOT EXISTS "runes_transactions" (
 	"rune_etched" BOOLEAN NOT NULL
 );
 CREATE INDEX IF NOT EXISTS runes_transactions_block_height_idx ON "runes_transactions" USING BTREE ("block_height");
+CREATE INDEX IF NOT EXISTS runes_transactions_jsonb_idx ON "runes_transactions" USING GIN ("inputs", "outputs", "mints", "burns");
 
 CREATE TABLE IF NOT EXISTS "runes_runestones" (
 	"tx_hash" TEXT NOT NULL PRIMARY KEY,

modules/runes/database/postgresql/queries/data.sql

@@ -40,10 +40,23 @@ SELECT * FROM runes_entries
 -- name: GetRuneIdFromRune :one
 SELECT rune_id FROM runes_entries WHERE rune = $1;
 
--- name: GetRuneTransactionsByHeight :many
-SELECT * FROM runes_transactions 
-  LEFT JOIN runes_runestones ON runes_transactions.hash = runes_runestones.tx_hash
-  WHERE runes_transactions.block_height = $1;
+-- name: GetRuneTransactions :many
+SELECT * FROM runes_transactions
+	LEFT JOIN runes_runestones ON runes_transactions.hash = runes_runestones.tx_hash
+	WHERE (
+    @filter_pk_script::BOOLEAN = FALSE -- if @filter_pk_script is TRUE, apply pk_script filter
+    OR runes_transactions.outputs @> @pk_script_param::JSONB 
+    OR runes_transactions.inputs @> @pk_script_param::JSONB
+  ) AND (
+    @filter_rune_id::BOOLEAN = FALSE -- if @filter_rune_id is TRUE, apply rune_id filter
+    OR runes_transactions.outputs @> @rune_id_param::JSONB 
+    OR runes_transactions.inputs @> @rune_id_param::JSONB 
+    OR runes_transactions.mints ? @rune_id 
+    OR runes_transactions.burns ? @rune_id
+    OR (runes_transactions.rune_etched = TRUE AND runes_transactions.block_height = @rune_id_block_height AND runes_transactions.index = @rune_id_tx_index)
+  ) AND (
+    @block_height::INT = 0 OR runes_transactions.block_height = @block_height::INT -- if @block_height > 0, apply block_height filter
+  );
 
 -- name: CountRuneEntries :one
 SELECT COUNT(*) FROM runes_entries;

modules/runes/datagateway/runes.go

@@ -26,7 +26,8 @@ type RunesDataGatewayWithTx interface {
 type RunesReaderDataGateway interface {
 	GetLatestBlock(ctx context.Context) (types.BlockHeader, error)
 	GetIndexedBlockByHeight(ctx context.Context, height int64) (*entity.IndexedBlock, error)
-	GetRuneTransactionsByHeight(ctx context.Context, height uint64) ([]*entity.RuneTransaction, error)
+	// GetRuneTransactions returns the runes transactions, filterable by pkScript, runeId and height. If pkScript, runeId or height is zero value, that filter is ignored.
+	GetRuneTransactions(ctx context.Context, pkScript []byte, runeId runes.RuneId, height uint64) ([]*entity.RuneTransaction, error)
 
 	GetRunesBalancesAtOutPoint(ctx context.Context, outPoint wire.OutPoint) (map[runes.RuneId]*entity.OutPointBalance, error)
 	GetUnspentOutPointBalancesByPkScript(ctx context.Context, pkScript []byte, blockHeight uint64) ([]*entity.OutPointBalance, error)

modules/runes/repository/postgres/gen/data.sql.go

@@ -631,13 +631,37 @@ func (q *Queries) GetRuneIdFromRune(ctx context.Context, rune string) (string, e
 	return rune_id, err
 }
 
-const getRuneTransactionsByHeight = `-- name: GetRuneTransactionsByHeight :many
-SELECT hash, runes_transactions.block_height, index, timestamp, inputs, outputs, mints, burns, rune_etched, tx_hash, runes_runestones.block_height, etching, etching_divisibility, etching_premine, etching_rune, etching_spacers, etching_symbol, etching_terms, etching_terms_amount, etching_terms_cap, etching_terms_height_start, etching_terms_height_end, etching_terms_offset_start, etching_terms_offset_end, etching_turbo, edicts, mint, pointer, cenotaph, flaws FROM runes_transactions 
-  LEFT JOIN runes_runestones ON runes_transactions.hash = runes_runestones.tx_hash
-  WHERE runes_transactions.block_height = $1
+const getRuneTransactions = `-- name: GetRuneTransactions :many
+SELECT hash, runes_transactions.block_height, index, timestamp, inputs, outputs, mints, burns, rune_etched, tx_hash, runes_runestones.block_height, etching, etching_divisibility, etching_premine, etching_rune, etching_spacers, etching_symbol, etching_terms, etching_terms_amount, etching_terms_cap, etching_terms_height_start, etching_terms_height_end, etching_terms_offset_start, etching_terms_offset_end, etching_turbo, edicts, mint, pointer, cenotaph, flaws FROM runes_transactions
+	LEFT JOIN runes_runestones ON runes_transactions.hash = runes_runestones.tx_hash
+	WHERE (
+    $1::BOOLEAN = FALSE -- if @filter_pk_script is TRUE, apply pk_script filter
+    OR runes_transactions.outputs @> $2::JSONB 
+    OR runes_transactions.inputs @> $2::JSONB
+  ) AND (
+    $3::BOOLEAN = FALSE -- if @filter_rune_id is TRUE, apply rune_id filter
+    OR runes_transactions.outputs @> $4::JSONB 
+    OR runes_transactions.inputs @> $4::JSONB 
+    OR runes_transactions.mints ? $5 
+    OR runes_transactions.burns ? $5
+    OR (runes_transactions.rune_etched = TRUE AND runes_transactions.block_height = $6 AND runes_transactions.index = $7)
+  ) AND (
+    $8::INT = 0 OR runes_transactions.block_height = $8::INT -- if @block_height > 0, apply block_height filter
+  )
 `
 
-type GetRuneTransactionsByHeightRow struct {
+type GetRuneTransactionsParams struct {
+	FilterPkScript    bool
+	PkScriptParam     []byte
+	FilterRuneID      bool
+	RuneIDParam       []byte
+	RuneID            []byte
+	RuneIDBlockHeight int32
+	RuneIDTxIndex     int32
+	BlockHeight       int32
+}
+
+type GetRuneTransactionsRow struct {
 	Hash                    string
 	BlockHeight             int32
 	Index                   int32
@@ -670,15 +694,24 @@ type GetRuneTransactionsByHeightRow struct {
 	Flaws                   pgtype.Int4
 }
 
-func (q *Queries) GetRuneTransactionsByHeight(ctx context.Context, blockHeight int32) ([]GetRuneTransactionsByHeightRow, error) {
-	rows, err := q.db.Query(ctx, getRuneTransactionsByHeight, blockHeight)
+func (q *Queries) GetRuneTransactions(ctx context.Context, arg GetRuneTransactionsParams) ([]GetRuneTransactionsRow, error) {
+	rows, err := q.db.Query(ctx, getRuneTransactions,
+		arg.FilterPkScript,
+		arg.PkScriptParam,
+		arg.FilterRuneID,
+		arg.RuneIDParam,
+		arg.RuneID,
+		arg.RuneIDBlockHeight,
+		arg.RuneIDTxIndex,
+		arg.BlockHeight,
+	)
 	if err != nil {
 		return nil, err
 	}
 	defer rows.Close()
-	var items []GetRuneTransactionsByHeightRow
+	var items []GetRuneTransactionsRow
 	for rows.Next() {
-		var i GetRuneTransactionsByHeightRow
+		var i GetRuneTransactionsRow
 		if err := rows.Scan(
 			&i.Hash,
 			&i.BlockHeight,

modules/runes/repository/postgres/mapper.go

@@ -306,7 +306,7 @@ func mapRuneTransactionTypeToParams(src entity.RuneTransaction) (gen.CreateRuneT
 	}, runestoneParams, nil
 }
 
-func extractModelRuneTxAndRunestone(src gen.GetRuneTransactionsByHeightRow) (gen.RunesTransaction, *gen.RunesRunestone, error) {
+func extractModelRuneTxAndRunestone(src gen.GetRuneTransactionsRow) (gen.RunesTransaction, *gen.RunesRunestone, error) {
 	var runestone *gen.RunesRunestone
 	if src.TxHash.Valid {
 		// these fields should never be null

modules/runes/repository/postgres/runes.go

@@ -3,6 +3,7 @@ package postgres
 import (
 	"context"
 	"encoding/hex"
+	"fmt"
 
 	"github.com/btcsuite/btcd/chaincfg/chainhash"
 	"github.com/btcsuite/btcd/wire"
@@ -61,8 +62,21 @@ func (r *Repository) GetIndexedBlockByHeight(ctx context.Context, height int64)
 	return indexedBlock, nil
 }
 
-func (r *Repository) GetRuneTransactionsByHeight(ctx context.Context, height uint64) ([]*entity.RuneTransaction, error) {
-	rows, err := r.queries.GetRuneTransactionsByHeight(ctx, int32(height))
+func (r *Repository) GetRuneTransactions(ctx context.Context, pkScript []byte, runeId runes.RuneId, height uint64) ([]*entity.RuneTransaction, error) {
+	pkScriptParam := []byte(fmt.Sprintf(`[{"pkScript":"%s"}]`, hex.EncodeToString(pkScript)))
+	runeIdParam := []byte(fmt.Sprintf(`[{"runeId":"%s"}]`, runeId.String()))
+	rows, err := r.queries.GetRuneTransactions(ctx, gen.GetRuneTransactionsParams{
+		FilterPkScript: pkScript != nil,
+		PkScriptParam:  pkScriptParam,
+
+		FilterRuneID:      runeId != runes.RuneId{},
+		RuneIDParam:       runeIdParam,
+		RuneID:            []byte(runeId.String()),
+		RuneIDBlockHeight: int32(runeId.BlockHeight),
+		RuneIDTxIndex:     int32(runeId.TxIndex),
+
+		BlockHeight: int32(height),
+	})
 	if err != nil {
 		return nil, errors.Wrap(err, "error during query")
 	}

modules/runes/usecase/get_transactions.go

@@ -5,10 +5,11 @@ import (
 
 	"github.com/cockroachdb/errors"
 	"github.com/gaze-network/indexer-network/modules/runes/internal/entity"
+	"github.com/gaze-network/indexer-network/modules/runes/runes"
 )
 
-func (u *Usecase) GetTransactionsByHeight(ctx context.Context, height uint64) ([]*entity.RuneTransaction, error) {
-	txs, err := u.runesDg.GetRuneTransactionsByHeight(ctx, height)
+func (u *Usecase) GetRuneTransactions(ctx context.Context, pkScript []byte, runeId runes.RuneId, height uint64) ([]*entity.RuneTransaction, error) {
+	txs, err := u.runesDg.GetRuneTransactions(ctx, pkScript, runeId, height)
 	if err != nil {
 		return nil, errors.Wrap(err, "error during GetTransactionsByHeight")
 	}