upsertUTXO method

@override Future<void> upsertUTXO(String walletId, BitcoinUtxo utxo)

Inserts or updates the (walletId, txid, vout) row.

updated_at is the UTXO's BitcoinUtxo.updatedAt; is_available follows the status (available only) and says nothing more than that — it was called is_spendable until migration v024 (bead libspiffy-p8qc), a name that promised WalletBalances.isSpendable, a rule over the whole wallet state that no per-row column can hold. The spend history is only ever added to (audit S-20, data retention): spent_at records the first store as spent and is never cleared, and spent_in_tx_id is never overwritten once set. A block height, plugin metadata or derivation index the update lacks keeps the stored value. The reservation columns follow the UTXO (a release clears them; bead libspiffy-viy).

Implementation

@override
Future<void> upsertUTXO(String walletId, BitcoinUtxo utxo) async {
  _ensureInitialized();

  final spent = utxo.status == UTXOStatus.spent;
  await _pool!.execute(
    Sql.named('''
      INSERT INTO bitcoin_utxos (
        wallet_id, txid, vout, utxo_key, satoshis, script_pub_key, address,
        block_height, confirmations, status, created_at, updated_at, spent_at,
        spent_in_tx_id, script_type, is_available, category, plugin_metadata,
        derivation_index, reserved_by_tx_id, reservation_reason,
        reservation_expires_at, reservation_priority, status_before_reservation
      ) VALUES (
        @walletId, @txid, @vout, @utxoKey, @satoshis, @scriptPubKey, @address,
        @blockHeight, @confirmations, @status, @createdAt, @updatedAt, @spentAt,
        @spentInTxId, @scriptType, @isAvailable, @category,
        CAST(@pluginMetadata AS JSONB),
        @derivationIndex, @reservedByTxId, @reservationReason,
        @reservationExpiresAt, @reservationPriority, @statusBeforeReservation
      )
      ON CONFLICT (wallet_id, txid, vout) DO UPDATE SET
        satoshis = EXCLUDED.satoshis,
        script_pub_key = EXCLUDED.script_pub_key,
        address = EXCLUDED.address,
        -- A zero-confirmation update (reorg, audit 3b0) clears the height.
        block_height = CASE WHEN EXCLUDED.confirmations > 0
            THEN COALESCE(EXCLUDED.block_height, bitcoin_utxos.block_height)
            ELSE EXCLUDED.block_height END,
        confirmations = EXCLUDED.confirmations,
        status = EXCLUDED.status,
        updated_at = EXCLUDED.updated_at,
        spent_at = COALESCE(bitcoin_utxos.spent_at, EXCLUDED.spent_at),
        is_available = EXCLUDED.is_available,
        plugin_metadata = COALESCE(EXCLUDED.plugin_metadata, bitcoin_utxos.plugin_metadata),
        spent_in_tx_id = COALESCE(bitcoin_utxos.spent_in_tx_id, EXCLUDED.spent_in_tx_id),
        derivation_index = COALESCE(EXCLUDED.derivation_index, bitcoin_utxos.derivation_index),
        reserved_by_tx_id = EXCLUDED.reserved_by_tx_id,
        reservation_reason = EXCLUDED.reservation_reason,
        reservation_expires_at = EXCLUDED.reservation_expires_at,
        reservation_priority = EXCLUDED.reservation_priority,
        status_before_reservation = EXCLUDED.status_before_reservation
    '''),
    parameters: {
      'walletId': walletId,
      'txid': utxo.txid,
      'vout': utxo.vout,
      'utxoKey': '${utxo.txid}:${utxo.vout}',
      'satoshis': utxo.satoshis.toInt(),
      'scriptPubKey': utxo.scriptPubKey,
      'address': utxo.address,
      'blockHeight': utxo.blockHeight,
      'confirmations': utxo.confirmations ?? 0,
      'status': utxo.status.name,
      'createdAt': utxo.createdAt,
      'updatedAt': utxo.updatedAt,
      'spentAt': spent ? utxo.updatedAt : null,
      'scriptType': 'p2pkh',
      'isAvailable': utxo.status == UTXOStatus.available,
      'category': 'funding',
      'pluginMetadata': utxo.pluginMetadata == null
          ? null
          : jsonEncode(utxo.pluginMetadata),
      'spentInTxId': utxo.spentInTxId,
      'derivationIndex': utxo.derivationIndex,
      'reservedByTxId': utxo.reservedByTxId,
      'reservationReason': utxo.reservationReason,
      'reservationExpiresAt': utxo.reservationExpiresAt,
      'reservationPriority': utxo.reservationPriority,
      'statusBeforeReservation': utxo.statusBeforeReservation?.name,
    },
  );
}