Document the purpose of `node_transaction` (mempool) vs. `block_transaction` + `node_block` (accepted tip)

[elderapo]

The graphql query:

query GetTx {
  node_transaction(
    where: {
      transaction: {
        hash: {
          _eq: "\\x45fdd101bce6b63c1c128f0fcbec111d93f304a3e09db4706229d60a417109c6"
        }
      }
    }
  ) {
    transaction {
      hash
    }
  }
}

returns:

{
  "data": {
    "node_transaction": []
  }
}

even though the transaction has been confirmed . Maybe because it's the coinbase?

---

@Edit1: It's possible to find this transaction by looking up the block it was included in:

query GetTxFromBlock {
  node_block(
    where: {
      block: {
        height: {
          _eq: 784836
        }
      }
    }
  ) {
    block {
      hash
      height

      transactions(
        where: {
          transaction: {
            hash: {
              _eq: "\\x45fdd101bce6b63c1c128f0fcbec111d93f304a3e09db4706229d60a417109c6"
            }
          }
        }
      ) {
        transaction {
          hash
        }
      }
    }
  }
}

returns:

{
  "data": {
    "node_block": [
      {
        "block": {
          "hash": "\\x000000000000000003ab734df9176286ea22513052eb4ee1e239cfa6249ee514",
          "height": "784836",
          "transactions": [
            {
              "transaction": {
                "hash": "\\x45fdd101bce6b63c1c128f0fcbec111d93f304a3e09db4706229d60a417109c6"
              }
            }
          ]
        }
      }
    ]
  }
}

---

@edit2: The following query also works:

query GetTx {
  transaction(
    where: {
      hash: {
        _eq: "\\x45fdd101bce6b63c1c128f0fcbec111d93f304a3e09db4706229d60a417109c6"
      }
    }
  ) {
    hash
  }
}

[bitjson]

Thanks for opening an issue. It looks like you got this figured out?

I know the flow of transactions from node_transaction to block_transaction (immutable) + node_block isn't very obvious – Chaingraph probably needs some better guides to introduce how the database models chain activity. This data model sets us up well for long-term scaling considerations though (avoiding some duplication and enabling time-based partitioning).

node_transaction is essentially the observed "mempool" for each connected node. The transactions then get organized into block_transactions, which are immutable even when particular nodes switch between accepted chain tips. Only node_block is changed when a particular node changes its mind about the state of the network, and the matching _history tables record changes to the various temporary tables.