Mempool transactions never expire

[rnbrady]

Chaingraph has no mechanism to expire stale node_transaction rows. BCHN evicts transactions from its mempool after 336 hours (14 days), but the P2P protocol has no "transaction removed" message, so Chaingraph never learns about these evictions.

Current behavior

When a transaction enters node_transaction, it can only be removed by:

1. Block confirmation: trigger_node_block_insert deletes the row
2. Conflict with a block transaction: trigger_node_block_insert deletes the row
3. Mempool double-spend replacement: trigger_node_transaction_insert deletes the row (rare on BCH due to first-seen rule)

If none of these occur (e.g. a low-fee transaction that no miner includes), the node_transaction row persists indefinitely.

Impact

• node_transaction contains invalid transactions
• Queries for unconfirmed balances or UTXOs return results for transactions that the node itself no longer considers valid

On demo.chaingraph.cash, this issue combined with #72 has left thousands of transactions in node_transaction with validated_at older than 14 days (the maximum mempool lifetime in BCHN):

• bchn-mainnet: 123,157
• bchn-chipnet: 105
• bchn-testnet: 30
• Oldest: January 29, 2023

For some example expired transactions, run the following GraphQL query on demo.chaingraph.cash:

query OrphanMempoolTransactions {
  node_transaction(
    where: { validated_at: { _lt: "2026-03-20" } }
    order_by: [{ validated_at: asc }]
  ) {
    transaction {
      hash
    }
    validated_at
  }
}

Proposed solution

A periodic cleanup that removes node_transaction rows older than the node's mempool expiry time (14 days for BCHN). For example pg_cron job running once per minute (or hour) to remove transactions older than 14 days.