← Watch feed
wallet: mirror bwatch writes into legacy outputs table
Public commit record
73/100 · Adequate
What the developer wrote
Authored by Sangbida Chaudhuri
wallet: mirror bwatch writes into legacy outputs table
The bwatch path writes wallet UTXOs to our_outputs while the legacy
`outputs` table is what a downgraded binary reads. Mirror every
our_outputs write (insert, spend, unspend-on-spend-revert, reservation)
into `outputs` so a downgrade for one release needs no copy-back or
rescan. Reorg demotion needs no mirror: the legacy rows are demoted by
their blocks(height) foreign keys when chaintopology removes the block.
The mirroring stops in the release that removes chaintopology, freezing
all the legacy tables at the same height.
The spend mirror guards its spend_height the same way the insert guards
confirmation_height: outputs.spend_height has a foreign key on
blocks(height), which only chaintopology populates, and bwatch can
deliver the spend before chaintopology has processed that block.
Record NULL in that case (status already marks the row spent) and let
chaintopology's own spend pass fill in the height.
The wallet still reads from `outputs`; switching reads over to
our_outputs comes next.
Co-authored-by: Cursor <cursoragent@cursor.com>
✓ Specific, descriptive subject✓ Names a concrete action or component✓ Provides detailed explanatory context
What changed, and why it matters
This change is a behind-the-scenes bookkeeping patch for Core Lightning's wallet database. The project is moving to a new table (`our_outputs`) for tracking spendable coins, but older versions of the software still read from the old table (`outputs`). The patch copies every new-table write back into the old table so that if a user downgrades to the previous release, their wallet still sees the correct coins and balances. It is not a fix for an externally exploitable bug; it is a compatibility/migration safeguard.
Recommended action
Treat as a normal code-quality/compatibility commit. Review the follow-up commit that switches wallet reads from `outputs` to `our_outputs` to ensure the legacy table is no longer relied on once mirroring ends. No urgent security action is required.
Security signals we found
01
Race-condition guard for foreign-key-backed confirmation_height and spend_height when bwatch is ahead of chaintopology
02
Temporary mirroring to keep legacy outputs table consistent with new our_outputs table
03
ON CONFLICT DO NOTHING used for legacy insert to avoid duplicate-key failures
04
No input validation changes; relies on existing db binding primitives
Evidence from the diff
The commit adds mirroring logic so that writes made by the new bwatch/watchman path into our_outputs are also applied to the legacy outputs table. New helper functions legacy_outputs_mark_spent and legacy_outputs_mark_unspent handle spend and reorg-revert cases. The insert path in wallet_add_our_output now also inserts into outputs, guarding against the race where bwatch sees a block before chaintopology has populated the blocks(height) foreign key by using a subselect and a follow-up conditional UPDATE. The wallet still reads from outputs; read-side migration is deferred. The patch is explicitly framed as temporary downgrade-compatibility scaffolding.
Changed components
wallet/wallet.cwallet/wallet.hlegacy `outputs` SQLite tablenew `our_outputs` SQLite tablebwatch/watchman wallet UTXO dispatch pathInspect captured patch +101 / −6
diff --git a/wallet/wallet.c b/wallet/wallet.c
index b17de31..c3b2737 100644
--- a/wallet/wallet.c
+++ b/wallet/wallet.c
@@ -276,6 +276,47 @@ static u64 move_accounts_id(struct db *db, const char *name, bool create)
return db_last_insert_id_v2(take(stmt));
}
+/* Every writer to our_outputs also mirrors the change into the legacy
+ * `outputs` table so a downgraded binary (which reads only `outputs`)
+ * finds it up to date. The mirroring goes away when chaintopology is
+ * removed and the legacy tables freeze wholesale. */
+static void legacy_outputs_mark_spent(struct wallet *w,
+ const struct bitcoin_outpoint *outpoint,
+ u32 blockheight)
+{
+ /* spend_height references blocks(height), which only chaintopology
+ * populates, and bwatch can be ahead of it. If the block isn't
+ * known yet, record NULL: the status column already excludes the
+ * row from coin selection, and chaintopology's own spend pass
+ * (wallet_outpoint_spend) re-runs this once it processes that
+ * block, filling in the height. Same race guard as
+ * confirmation_height in wallet_add_our_output. */
+ struct db_stmt *stmt = db_prepare_v2(w->db,
+ SQL("UPDATE outputs SET "
+ "spend_height = (SELECT height FROM blocks WHERE height = ?), "
+ "status = ? "
+ "WHERE prev_out_tx = ? AND prev_out_index = ?"));
+ db_bind_int(stmt, blockheight);
+ db_bind_int(stmt, output_status_in_db(OUTPUT_STATE_SPENT));
+ db_bind_txid(stmt, &outpoint->txid);
+ db_bind_int(stmt, outpoint->n);
+ db_exec_prepared_v2(take(stmt));
+}
+
+/* Mirror of the reorg case: the spend was reverted, so the output is
+ * unspent again. */
+static void legacy_outputs_mark_unspent(struct wallet *w,
+ const struct bitcoin_outpoint *outpoint)
+{
+ struct db_stmt *stmt = db_prepare_v2(w->db,
+ SQL("UPDATE outputs SET spend_height = NULL, status = ? "
+ "WHERE prev_out_tx = ? AND prev_out_index = ?"));
+ db_bind_int(stmt, output_status_in_db(OUTPUT_STATE_AVAILABLE));
+ db_bind_txid(stmt, &outpoint->txid);
+ db_bind_int(stmt, outpoint->n);
+ db_exec_prepared_v2(take(stmt));
+}
+
/**
* wallet_add_utxo - Register an UTXO which we (partially) own
*
@@ -7959,10 +8000,10 @@ void migrate_remove_chain_moves_duplicates(struct lightningd *ld, struct db *db)
* When bwatch reports that a wallet-owned scriptpubkey appeared in a block
* (or that a previously-seen output was reorged away), the dispatch table
* in lightningd/watchman calls into the helpers below. They write to the
- * `our_outputs` and `our_txs` tables, which are independent of the
- * `utxoset` / `transactions` tables populated by the legacy chaintopology
- * path. Both sets of tables coexist for one release so a node can
- * downgrade cleanly.
+ * `our_outputs` and `our_txs` tables; every write is also mirrored into
+ * the legacy `outputs` / `transactions` tables so a downgraded binary
+ * finds them up to date. The mirroring (and the legacy tables) go away
+ * once chaintopology does.
* ==================================================================== */
/* Map a wallet output script to its address type. Returns false if it's
@@ -8061,6 +8102,56 @@ void wallet_add_our_output(struct wallet *w,
db_bind_int(stmt, blockheight);
db_exec_prepared_v2(take(stmt));
}
+
+ /* Mirror into legacy `outputs` for downgrade (see
+ * legacy_outputs_mark_spent). Bwatch may be ahead of chaintopology,
+ * so only set the FK-backed confirmation_height once blocks has it;
+ * chaintopology's later pass promotes the row. Coinbase convention
+ * matches ours: confirmed at txindex 0. */
+ stmt = db_prepare_v2(w->db,
+ SQL("INSERT INTO outputs ("
+ " prev_out_tx"
+ ", prev_out_index"
+ ", value"
+ ", type"
+ ", status"
+ ", keyindex"
+ ", confirmation_height"
+ ", spend_height"
+ ", scriptpubkey"
+ ", is_in_coinbase"
+ ") VALUES (?, ?, ?, ?, ?, ?, "
+ "(SELECT height FROM blocks WHERE height = ?), ?, ?, ?) "
+ "ON CONFLICT(prev_out_tx,prev_out_index) DO NOTHING;"));
+ db_bind_txid(stmt, &outpoint->txid);
+ db_bind_int(stmt, outpoint->n);
+ db_bind_amount_sat(stmt, sat);
+ db_bind_int(stmt, wallet_output_type_in_db(
+ is_p2sh(script, script_len, NULL)
+ ? WALLET_OUTPUT_P2SH_WPKH
+ : WALLET_OUTPUT_OUR_CHANGE));
+ db_bind_int(stmt, output_status_in_db(OUTPUT_STATE_AVAILABLE));
+ db_bind_int(stmt, keyindex);
+ db_bind_int(stmt, blockheight);
+ db_bind_null(stmt);
+ db_bind_blob(stmt, script, script_len);
+ db_bind_int(stmt, blockheight != 0 && txindex == 0);
+ db_exec_prepared_v2(take(stmt));
+
+ if (blockheight != 0) {
+ stmt = db_prepare_v2(w->db,
+ SQL("UPDATE outputs SET confirmation_height = ? "
+ "WHERE prev_out_tx = ? AND prev_out_index = ? "
+ "AND EXISTS (SELECT 1 FROM blocks WHERE height = ?) "
+ "AND (confirmation_height IS NULL "
+ " OR confirmation_height < ?);"));
+ db_bind_int(stmt, blockheight);
+ db_bind_txid(stmt, &outpoint->txid);
+ db_bind_int(stmt, outpoint->n);
+ db_bind_int(stmt, blockheight);
+ db_bind_int(stmt, blockheight);
+ db_exec_prepared_v2(take(stmt));
+ }
}
/* Insert (or replace) a wallet-relevant transaction in our_txs. */
@@ -8402,6 +8493,8 @@ void wallet_utxo_spent_watch_found(struct lightningd *ld,
db_bind_int(stmt, outpoint.n);
db_exec_prepared_v2(take(stmt));
+ legacy_outputs_mark_spent(ld->wallet, &outpoint, blockheight);
+
/* The spending tx is wallet-relevant, so it goes into our_txs (like
* the legacy transactions table) for listtransactions. */
wallet_add_our_tx(ld->wallet, tx->wtx, blockheight, txindex);
@@ -8432,6 +8525,8 @@ void wallet_utxo_spent_watch_revert(struct lightningd *ld,
db_bind_int(stmt, outpoint.n);
db_exec_prepared_v2(take(stmt));
+ legacy_outputs_mark_unspent(ld->wallet, &outpoint);
+
/* The withdrawal movement recorded by watch_found stays: coin
* movements are append-only, and wallet_save_chain_mvt won't record
* a duplicate if the spend re-confirms. */
diff --git a/wallet/wallet.h b/wallet/wallet.h
index da9e133..149c424 100644
--- a/wallet/wallet.h
+++ b/wallet/wallet.h
@@ -2034,8 +2034,8 @@ void migrate_setup_coinmoves(struct lightningd *ld, struct db *db);
* These functions are invoked from lightningd/watchman's dispatch table
* when bwatch reports activity on a wallet-owned scriptpubkey. They
* persist outputs and transactions in the `our_outputs` and `our_txs`
- * tables, which run in parallel to the legacy `utxoset` / `transactions`
- * tables so a node can downgrade cleanly for one release.
+ * tables, mirroring every write into the legacy `outputs` /
+ * `transactions` tables so a node can downgrade cleanly for one release.
* ==================================================================== */
/* Insert a wallet-owned UTXO row into our_outputs. If the same outpoint
Risk score
Our methodology →Why this scored 27/100
Human-validated context
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
No validated notes yet.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.