wallet: add migrate_backfill_bwatch_tables

diff --git a/wallet/Makefile b/wallet/Makefile
index 34ea6d4..1dc4364 100644
--- a/wallet/Makefile
+++ b/wallet/Makefile
@@ -43,6 +43,7 @@ WALLET_SQL_FILES := 			\
 	wallet/test/run-wallet.c	\
 	wallet/test/run-chain_moves_duplicate-detect.c	\
 	wallet/test/run-migrate_remove_chain_moves_duplicates.c \
+	wallet/test/run-migrate_backfill_bwatch_tables.c \
 	tools/lightning-downgrade.c \
 
 
diff --git a/wallet/migrations.c b/wallet/migrations.c
index cb6a666..16b663a 100644
--- a/wallet/migrations.c
+++ b/wallet/migrations.c
@@ -10,6 +10,7 @@
 #include <wallet/account_migration.h>
 #include <wallet/db.h>
 #include <wallet/migrations.h>
+#include <wallet/wallet.h>
 
 static const char *revert_too_early(const tal_t *ctx, struct db *db)
 {
@@ -52,6 +53,58 @@ static const char *revert_withheld_column(const tal_t *ctx, struct db *db)
 	return NULL;
 }
 
+/* Backfill the new bwatch-driven tables (our_outputs, our_txs) from the
+ * legacy outputs / transactions tables, so the bwatch path sees pre-existing
+ * wallet UTXOs and txs without needing a full rescan.
+ *
+ * We intentionally source from outputs instead of utxoset:
+ * - outputs already contains only wallet-owned rows (HD + onchaind closes)
+ * - it carries wallet-only metadata (reserved_til, close_info columns)
+ * - it avoids expensive script->keyindex re-derivation over the full chain UTXO set
+ */
+void migrate_backfill_bwatch_tables(struct lightningd *ld UNNEEDED, struct db *db)
+{
+	struct db_stmt *stmt;
+
+	stmt = db_prepare_v2(db,
+		SQL("INSERT INTO our_outputs "
+		    "(txid, outnum, blockheight, txindex, scriptpubkey, satoshis, "
+		    " spendheight, reserved_til, keyindex, channel_dbid, peer_id, "
+		    " commitment_point, option_anchors, csv) "
+		    "SELECT "
+		    "  prev_out_tx, "
+		    "  prev_out_index, "
+		    "  COALESCE(confirmation_height, 0), "
+		    "  CASE "
+		    "    WHEN confirmation_height IS NULL THEN 0 "
+		    "    WHEN is_in_coinbase = 1 THEN 0 "
+		    "    ELSE 1 "
+		    "  END, "
+		    "  scriptpubkey, "
+		    "  value, "
+		    "  spend_height, "
+		    "  COALESCE(reserved_til, 0), "
+		    "  CASE WHEN channel_id IS NULL THEN keyindex ELSE NULL END, "
+		    "  channel_id, "
+		    "  peer_id, "
+		    "  commitment_point, "
+		    "  option_anchor_outputs, "
+		    "  csv_lock "
+		    "FROM outputs "
+		    "WHERE scriptpubkey IS NOT NULL "
+		    "ON CONFLICT(txid,outnum) DO NOTHING;"));
+	db_exec_prepared_v2(take(stmt));
+
+	stmt = db_prepare_v2(db,
+		SQL("INSERT INTO our_txs "
+		    "(txid, blockheight, txindex, rawtx) "
+		    "SELECT id, blockheight, COALESCE(txindex, 0), rawtx "
+		    "FROM transactions "
+		    "WHERE blockheight IS NOT NULL AND rawtx IS NOT NULL "
+		    "ON CONFLICT(txid) DO NOTHING;"));
+	db_exec_prepared_v2(take(stmt));
+}
+
 /* Do not reorder or remove elements from this array, it is used to
  * migrate existing databases from a previous state, based on the
  * string indices */
@@ -1123,6 +1176,12 @@ static const struct db_migration dbmigrations[] = {
 	 "  rawtx BLOB"
 	 ")"), NULL,
      SQL("DROP TABLE our_txs"), NULL},
+    /* The wallet now reads our_outputs/our_txs, but every write is still
+     * mirrored into the legacy outputs/transactions tables so a downgraded
+     * binary finds them fully up to date (no rescan needed).  The mirror
+     * writes stop in the release that removes chaintopology, freezing all
+     * the legacy tables at the same height. */
+    {NULL, migrate_backfill_bwatch_tables, NULL, NULL},
 };
 
 const struct db_migration *get_db_migrations(size_t *num)
diff --git a/wallet/migrations.h b/wallet/migrations.h
index dd11f00..7105bae 100644
--- a/wallet/migrations.h
+++ b/wallet/migrations.h
@@ -63,4 +63,5 @@ void migrate_from_account_db(struct lightningd *ld, struct db *db);
 void migrate_datastore_commando_runes(struct lightningd *ld, struct db *db);
 void migrate_runes_idfix(struct lightningd *ld, struct db *db);
 void migrate_fix_payments_faildetail_type(struct lightningd *ld, struct db *db);
+void migrate_backfill_bwatch_tables(struct lightningd *ld, struct db *db);
 #endif /* LIGHTNING_WALLET_MIGRATIONS_H */
diff --git a/wallet/test/run-migrate_backfill_bwatch_tables.c b/wallet/test/run-migrate_backfill_bwatch_tables.c
new file mode 100644
index 0000000..e5d0936
--- /dev/null
+++ b/wallet/test/run-migrate_backfill_bwatch_tables.c
@@ -0,0 +1,452 @@
+#include "config.h"
+
+#include <common/setup.h>
+#include <common/status_levels.h>
+#include <common/utils.h>
+#include <stdio.h>
+#include <unistd.h>
+
+#include "lightningd/log.h"
+
+#include "wallet/migrations.c"
+
+#include "db/bindings.c"
+#include "db/db_sqlite3.c"
+#include "db/exec.c"
+#include "db/utils.c"
+
+/* AUTOGENERATED MOCKS START */
+/* Generated stub for fillin_missing_channel_blockheights */
+void fillin_missing_channel_blockheights(struct lightningd *ld UNNEEDED,
+					 struct db *db UNNEEDED)
+{ fprintf(stderr, "fillin_missing_channel_blockheights called!\n"); abort(); }
+/* Generated stub for fillin_missing_channel_id */
+void fillin_missing_channel_id(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "fillin_missing_channel_id called!\n"); abort(); }
+/* Generated stub for fillin_missing_lease_satoshi */
+void fillin_missing_lease_satoshi(struct lightningd *ld UNNEEDED,
+				  struct db *db UNNEEDED)
+{ fprintf(stderr, "fillin_missing_lease_satoshi called!\n"); abort(); }
+/* Generated stub for fillin_missing_local_basepoints */
+void fillin_missing_local_basepoints(struct lightningd *ld UNNEEDED,
+				     struct db *db UNNEEDED)
+{ fprintf(stderr, "fillin_missing_local_basepoints called!\n"); abort(); }
+/* Generated stub for fillin_missing_scriptpubkeys */
+void fillin_missing_scriptpubkeys(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "fillin_missing_scriptpubkeys called!\n"); abort(); }
+/* Generated stub for insert_addrtype_to_addresses */
+void insert_addrtype_to_addresses(struct lightningd *ld UNNEEDED,
+				  struct db *db UNNEEDED)
+{ fprintf(stderr, "insert_addrtype_to_addresses called!\n"); abort(); }
+/* Generated stub for migrate_channels_scids_as_integers */
+void migrate_channels_scids_as_integers(struct lightningd *ld UNNEEDED,
+					struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_channels_scids_as_integers called!\n"); abort(); }
+/* Generated stub for migrate_convert_old_channel_keyidx */
+void migrate_convert_old_channel_keyidx(struct lightningd *ld UNNEEDED,
+					struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_convert_old_channel_keyidx called!\n"); abort(); }
+/* Generated stub for migrate_datastore_commando_runes */
+void migrate_datastore_commando_runes(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_datastore_commando_runes called!\n"); abort(); }
+/* Generated stub for migrate_fail_pending_payments_without_htlcs */
+void migrate_fail_pending_payments_without_htlcs(struct lightningd *ld UNNEEDED,
+						 struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_fail_pending_payments_without_htlcs called!\n"); abort(); }
+/* Generated stub for migrate_fill_in_channel_type */
+void migrate_fill_in_channel_type(struct lightningd *ld UNNEEDED,
+				  struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_fill_in_channel_type called!\n"); abort(); }
+/* Generated stub for migrate_fix_payments_faildetail_type */
+void migrate_fix_payments_faildetail_type(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_fix_payments_faildetail_type called!\n"); abort(); }
+/* Generated stub for migrate_forwards_add_rowid */
+void migrate_forwards_add_rowid(struct lightningd *ld UNNEEDED,
+				struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_forwards_add_rowid called!\n"); abort(); }
+/* Generated stub for migrate_from_account_db */
+void migrate_from_account_db(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_from_account_db called!\n"); abort(); }
+/* Generated stub for migrate_inflight_last_tx_to_psbt */
+void migrate_inflight_last_tx_to_psbt(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_inflight_last_tx_to_psbt called!\n"); abort(); }
+/* Generated stub for migrate_initialize_alias_local */
+void migrate_initialize_alias_local(struct lightningd *ld UNNEEDED,
+				    struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_initialize_alias_local called!\n"); abort(); }
+/* Generated stub for migrate_initialize_channel_htlcs_wait_indexes_and_fixup_forwards */
+void migrate_initialize_channel_htlcs_wait_indexes_and_fixup_forwards(struct lightningd *ld UNNEEDED,
+								      struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_initialize_channel_htlcs_wait_indexes_and_fixup_forwards called!\n"); abort(); }
+/* Generated stub for migrate_initialize_forwards_wait_indexes */
+void migrate_initialize_forwards_wait_indexes(struct lightningd *ld UNNEEDED,
+					      struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_initialize_forwards_wait_indexes called!\n"); abort(); }
+/* Generated stub for migrate_initialize_invoice_wait_indexes */
+void migrate_initialize_invoice_wait_indexes(struct lightningd *ld UNNEEDED,
+					     struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_initialize_invoice_wait_indexes called!\n"); abort(); }
+/* Generated stub for migrate_initialize_payment_wait_indexes */
+void migrate_initialize_payment_wait_indexes(struct lightningd *ld UNNEEDED,
+					     struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_initialize_payment_wait_indexes called!\n"); abort(); }
+/* Generated stub for migrate_invalid_last_tx_psbts */
+void migrate_invalid_last_tx_psbts(struct lightningd *ld UNNEEDED,
+				   struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_invalid_last_tx_psbts called!\n"); abort(); }
+/* Generated stub for migrate_invoice_created_index_var */
+void migrate_invoice_created_index_var(struct lightningd *ld UNNEEDED,
+				       struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_invoice_created_index_var called!\n"); abort(); }
+/* Generated stub for migrate_last_tx_to_psbt */
+void migrate_last_tx_to_psbt(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_last_tx_to_psbt called!\n"); abort(); }
+/* Generated stub for migrate_normalize_invstr */
+void migrate_normalize_invstr(struct lightningd *ld UNNEEDED,
+			      struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_normalize_invstr called!\n"); abort(); }
+/* Generated stub for migrate_our_funding */
+void migrate_our_funding(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_our_funding called!\n"); abort(); }
+/* Generated stub for migrate_payments_scids_as_integers */
+void migrate_payments_scids_as_integers(struct lightningd *ld UNNEEDED,
+					struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_payments_scids_as_integers called!\n"); abort(); }
+/* Generated stub for migrate_pr2342_feerate_per_channel */
+void migrate_pr2342_feerate_per_channel(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_pr2342_feerate_per_channel called!\n"); abort(); }
+/* Generated stub for migrate_remove_chain_moves_duplicates */
+void migrate_remove_chain_moves_duplicates(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_remove_chain_moves_duplicates called!\n"); abort(); }
+/* Generated stub for migrate_runes_idfix */
+void migrate_runes_idfix(struct lightningd *ld UNNEEDED, struct db *db UNNEEDED)
+{ fprintf(stderr, "migrate_runes_idfix called!\n"); abort(); }
+/* AUTOGENERATED MOCKS END */
+
+/* Legacy tables as they look after every pre-bwatch migration ran, plus the
+ * new empty our_outputs/our_txs tables the backfill writes into.  FK
+ * references (blocks etc.) are omitted: the backfill doesn't rely on them
+ * and this keeps the fixture self-contained. */
+static const char *setup_stmts[] = {
+	SQL("CREATE TABLE vars ("
+	    "  name VARCHAR(32)"
+	    ", val VARCHAR(255)"
+	    ", PRIMARY KEY (name)"
+	    ");"),
+	SQL("ALTER TABLE vars ADD COLUMN intval INTEGER"),
+	SQL("ALTER TABLE vars ADD COLUMN blobval BLOB"),
+	SQL("CREATE TABLE outputs ("
+	    "  prev_out_tx BLOB"
+	    ", prev_out_index INTEGER"
+	    ", value BIGINT"
+	    ", type INTEGER"
+	    ", status INTEGER"
+	    ", keyindex INTEGER"
+	    ", channel_id BIGINT"
+	    ", peer_id BLOB"
+	    ", commitment_point BLOB"
+	    ", confirmation_height INTEGER"
+	    ", spend_height INTEGER"
+	    ", scriptpubkey BLOB"
+	    ", reserved_til INTEGER DEFAULT NULL"
+	    ", option_anchor_outputs INTEGER DEFAULT 0"
+	    ", csv_lock INTEGER DEFAULT 1"
+	    ", is_in_coinbase INTEGER DEFAULT 0"
+	    ", PRIMARY KEY (prev_out_tx, prev_out_index));"),
+	SQL("CREATE TABLE transactions ("
+	    "  id BLOB"
+	    ", blockheight INTEGER"
+	    ", txindex INTEGER"
+	    ", rawtx BLOB"
+	    ", PRIMARY KEY (id));"),
+	SQL("CREATE TABLE our_outputs ("
+	    "  txid BLOB NOT NULL,"
+	    "  outnum INTEGER NOT NULL,"
+	    "  blockheight INTEGER NOT NULL,"
+	    "  txindex INTEGER NOT NULL DEFAULT 0,"
+	    "  scriptpubkey BLOB NOT NULL,"
+	    "  satoshis BIGINT NOT NULL,"
+	    "  spendheight INTEGER,"
+	    "  keyindex INTEGER,"
+	    "  reserved_til INTEGER NOT NULL DEFAULT 0,"
+	    "  channel_dbid BIGINT,"
+	    "  peer_id BLOB,"
+	    "  commitment_point BLOB,"
+	    "  option_anchors INTEGER,"
+	    "  csv INTEGER,"
+	    "  PRIMARY KEY (txid, outnum)"
+	    ")"),
+	SQL("CREATE TABLE our_txs ("
+	    "  txid BLOB NOT NULL PRIMARY KEY,"
+	    "  blockheight INTEGER NOT NULL,"
+	    "  txindex INTEGER NOT NULL DEFAULT 0,"
+	    "  rawtx BLOB"
+	    ")"),
+
+	/* 1: confirmed HD output at height 100 (non-coinbase). */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0101010101010101010101010101010101010101010101010101010101010101', 0, "
+	    " 1000000, 0, 0, 1, NULL, NULL, NULL, 100, NULL, "
+	    " X'0014010101010101010101010101010101010101ff01', NULL, 0, 1, 0);"),
+	/* 2: unconfirmed HD output. */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0202020202020202020202020202020202020202020202020202020202020202', 1, "
+	    " 2000000, 0, 0, 2, NULL, NULL, NULL, NULL, NULL, "
+	    " X'0014020202020202020202020202020202020202ff02', NULL, 0, 1, 0);"),
+	/* 3: confirmed coinbase output. */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0303030303030303030303030303030303030303030303030303030303030303', 0, "
+	    " 3000000, 0, 0, 3, NULL, NULL, NULL, 101, NULL, "
+	    " X'0014030303030303030303030303030303030303ff03', NULL, 0, 1, 1);"),
+	/* 4: spent output (confirmed 100, spent 105). */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0404040404040404040404040404040404040404040404040404040404040404', 0, "
+	    " 4000000, 0, 2, 4, NULL, NULL, NULL, 100, 105, "
+	    " X'0014040404040404040404040404040404040404ff04', NULL, 0, 1, 0);"),
+	/* 5: reserved output. */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0505050505050505050505050505050505050505050505050505050505050505', 0, "
+	    " 5000000, 0, 0, 5, NULL, NULL, NULL, 100, NULL, "
+	    " X'0014050505050505050505050505050505050505ff05', 150, 0, 1, 0);"),
+	/* 6: channel-close output: close-info columns copied, keyindex
+	 * dropped (it's meaningless for per-channel keys). */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0606060606060606060606060606060606060606060606060606060606060606', 0, "
+	    " 6000000, 0, 0, 42, 7, "
+	    " X'020606060606060606060606060606060606060606060606060606060606060606', "
+	    " X'030606060606060606060606060606060606060606060606060606060606060606', "
+	    " 102, NULL, X'0014060606060606060606060606060606060606ff06', NULL, 1, 5, 0);"),
+	/* 7: NULL scriptpubkey: not backfilled at all. */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0707070707070707070707070707070707070707070707070707070707070707', 0, "
+	    " 7000000, 0, 0, 7, NULL, NULL, NULL, 100, NULL, NULL, NULL, 0, 1, 0);"),
+	/* 8: legacy row whose (txid, outnum) already exists in our_outputs
+	 * (inserted below): backfill must not clobber it. */
+	SQL("INSERT INTO outputs (prev_out_tx, prev_out_index, value, type, status, keyindex, "
+	    " channel_id, peer_id, commitment_point, confirmation_height, spend_height, "
+	    " scriptpubkey, reserved_til, option_anchor_outputs, csv_lock, is_in_coinbase) "
+	    "VALUES (X'0808080808080808080808080808080808080808080808080808080808080808', 0, "
+	    " 8000000, 0, 0, 8, NULL, NULL, NULL, 100, NULL, "
+	    " X'0014080808080808080808080808080808080808ff08', NULL, 0, 1, 0);"),
+	SQL("INSERT INTO our_outputs (txid, outnum, blockheight, txindex, scriptpubkey, "
+	    " satoshis, keyindex) "
+	    "VALUES (X'0808080808080808080808080808080808080808080808080808080808080808', 0, "
+	    " 100, 1, X'0014080808080808080808080808080808080808ff08', 999, 8);"),
+
+	/* Confirmed tx with rawtx: copied to our_txs. */
+	SQL("INSERT INTO transactions (id, blockheight, txindex, rawtx) VALUES ("
+	    " X'0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a', "
+	    " 100, 1, X'deadbeef');"),
+	/* Unconfirmed tx: skipped. */
+	SQL("INSERT INTO transactions (id, blockheight, txindex, rawtx) VALUES ("
+	    " X'0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b', "
+	    " NULL, NULL, X'deadbeef');"),
+	/* Missing rawtx: skipped. */
+	SQL("INSERT INTO transactions (id, blockheight, txindex, rawtx) VALUES ("
+	    " X'0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c', "
+	    " 100, 2, NULL);"),
+};
+
+static void populate_db(struct db *db)
+{
+	struct db_stmt *stmt;
+
+	for (size_t i = 0; i < ARRAY_SIZE(setup_stmts); i++){
+		stmt = db_prepare_v2(db, setup_stmts[i]);
+		db_exec_prepared_v2(take(stmt));
+	}
+}
+
+static void test_error(void *arg, bool fatal, const char *fmt, va_list ap)
+{
+	vfprintf(stderr, fmt, ap);
+	abort();
+}
+
+static size_t count_rows(struct db *db, const char *query)
+{
+	struct db_stmt *stmt;
+	size_t ret;
+
+	stmt = db_prepare_v2(db, query);
+	db_query_prepared(stmt);
+	db_step(stmt);
+	ret = db_col_int(stmt, "COUNT(*)");
+	tal_free(stmt);
+	return ret;
+}
+
+/* Fetch our_outputs row by first txid byte (all our txids are repeats). */
+static struct db_stmt *get_our_output(struct db *db, u8 txid_byte, u32 outnum)
+{
+	struct db_stmt *stmt;
+	u8 txid[32];
+
+	memset(txid, txid_byte, sizeof(txid));
+	stmt = db_prepare_v2(db,
+		SQL("SELECT blockheight, txindex, satoshis, spendheight,"
+		    "       reserved_til, keyindex, channel_dbid,"
+		    "       option_anchors, csv"
+		    " FROM our_outputs WHERE txid = ? AND outnum = ?"));
+	db_bind_blob(stmt, txid, sizeof(txid));
+	db_bind_int(stmt, outnum);
+	db_query_prepared(stmt);
+	if (!db_step(stmt))
+		abort();
+	return stmt;
+}
+
+int main(int argc, const char *argv[])
+{
+	char *dsn, *filename;
+	struct db *db;
+	struct db_stmt *stmt;
+
+	common_setup(argv[0]);
+	close(tmpdir_mkstemp(tmpctx, "ldb-XXXXXX", &filename));
+
+	chainparams = chainparams_for_network("bitcoin");
+	dsn = tal_fmt(tmpctx, "sqlite3://%s", filename);
+	db = db_open(tmpctx, dsn, true, true, test_error, NULL);
+	db->report_changes_fn = NULL;
+
+	db_begin_transaction(db);
+	populate_db(db);
+	db->data_version = 0;
+	db_set_intvar(db, "data_version", db->data_version);
+
+	migrate_backfill_bwatch_tables(NULL, db);
+
+	/* Rows 1-6 backfilled, 7 skipped (NULL script), 8 pre-existing:
+	 * 7 our_outputs rows in total. */
+	assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_outputs")) == 7);
+
+	/* 1: confirmed non-coinbase: blockheight kept, txindex is the
+	 * "confirmed, not coinbase" 1 sentinel. */
+	stmt = get_our_output(db, 0x01, 0);
+	assert(db_col_int(stmt, "blockheight") == 100);
+	assert(db_col_int(stmt, "txindex") == 1);
+	assert(db_col_u64(stmt, "satoshis") == 1000000);
+	assert(db_col_is_null(stmt, "spendheight"));
+	assert(db_col_int(stmt, "reserved_til") == 0);
+	assert(db_col_int(stmt, "keyindex") == 1);
+	assert(db_col_is_null(stmt, "channel_dbid"));
+	db_col_ignore(stmt, "option_anchors");
+	db_col_ignore(stmt, "csv");
+	tal_free(stmt);
+
+	/* 2: unconfirmed: NULL height becomes the 0 sentinel. */
+	stmt = get_our_output(db, 0x02, 1);
+	assert(db_col_int(stmt, "blockheight") == 0);
+	assert(db_col_int(stmt, "txindex") == 0);
+	db_col_ignore(stmt, "satoshis");
+	db_col_ignore(stmt, "spendheight");
+	db_col_ignore(stmt, "reserved_til");
+	db_col_ignore(stmt, "keyindex");
+	db_col_ignore(stmt, "channel_dbid");
+	db_col_ignore(stmt, "option_anchors");
+	db_col_ignore(stmt, "csv");
+	tal_free(stmt);
+
+	/* 3: coinbase: confirmed, txindex 0 marks it a coinbase. */
+	stmt = get_our_output(db, 0x03, 0);
+	assert(db_col_int(stmt, "blockheight") == 101);
+	assert(db_col_int(stmt, "txindex") == 0);
+	db_col_ignore(stmt, "satoshis");
+	db_col_ignore(stmt, "spendheight");
+	db_col_ignore(stmt, "reserved_til");
+	db_col_ignore(stmt, "keyindex");
+	db_col_ignore(stmt, "channel_dbid");
+	db_col_ignore(stmt, "option_anchors");
+	db_col_ignore(stmt, "csv");
+	tal_free(stmt);
+
+	/* 4: spend_height carried across. */
+	stmt = get_our_output(db, 0x04, 0);
+	assert(!db_col_is_null(stmt, "spendheight"));
+	assert(db_col_int(stmt, "spendheight") == 105);
+	db_col_ignore(stmt, "blockheight");
+	db_col_ignore(stmt, "txindex");
+	db_col_ignore(stmt, "satoshis");
+	db_col_ignore(stmt, "reserved_til");
+	db_col_ignore(stmt, "keyindex");
+	db_col_ignore(stmt, "channel_dbid");
+	db_col_ignore(stmt, "option_anchors");
+	db_col_ignore(stmt, "csv");
+	tal_free(stmt);
+
+	/* 5: reserved_til carried across. */
+	stmt = get_our_output(db, 0x05, 0);
+	assert(db_col_int(stmt, "reserved_til") == 150);
+	db_col_ignore(stmt, "blockheight");
+	db_col_ignore(stmt, "txindex");
+	db_col_ignore(stmt, "satoshis");
+	db_col_ignore(stmt, "spendheight");
+	db_col_ignore(stmt, "keyindex");
+	db_col_ignore(stmt, "channel_dbid");
+	db_col_ignore(stmt, "option_anchors");
+	db_col_ignore(stmt, "csv");
+	tal_free(stmt);
+
+	/* 6: channel-close output: close-info columns copied, keyindex
+	 * dropped in favour of channel_dbid. */
+	stmt = get_our_output(db, 0x06, 0);
+	assert(db_col_is_null(stmt, "keyindex"));
+	assert(db_col_u64(stmt, "channel_dbid") == 7);
+	assert(db_col_int(stmt, "option_anchors") == 1);
+	assert(db_col_int(stmt, "csv") == 5);
+	db_col_ignore(stmt, "blockheight");
+	db_col_ignore(stmt, "txindex");
+	db_col_ignore(stmt, "satoshis");
+	db_col_ignore(stmt, "spendheight");
+	db_col_ignore(stmt, "reserved_til");
+	tal_free(stmt);
+
+	/* 7: NULL scriptpubkey row was skipped. */
+	assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_outputs"
+				  " WHERE txid = X'0707070707070707070707070707070707070707070707070707070707070707'")) == 0);
+
+	/* 8: pre-existing our_outputs row untouched (ON CONFLICT). */
+	stmt = get_our_output(db, 0x08, 0);
+	assert(db_col_u64(stmt, "satoshis") == 999);
+	db_col_ignore(stmt, "blockheight");
+	db_col_ignore(stmt, "txindex");
+	db_col_ignore(stmt, "spendheight");
+	db_col_ignore(stmt, "reserved_til");
+	db_col_ignore(stmt, "keyindex");
+	db_col_ignore(stmt, "channel_dbid");
+	db_col_ignore(stmt, "option_anchors");
+	db_col_ignore(stmt, "csv");
+	tal_free(stmt);
+
+	/* Only the confirmed tx with a rawtx makes it into our_txs. */
+	assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_txs")) == 1);
+	assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_txs"
+				  " WHERE txid = X'0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a'"
+				  " AND blockheight = 100 AND txindex = 1")) == 1);
+
+	/* Backfill is idempotent: re-running changes nothing. */
+	migrate_backfill_bwatch_tables(NULL, db);
+	assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_outputs")) == 7);
+	assert(count_rows(db, SQL("SELECT COUNT(*) FROM our_txs")) == 1);
+
+	db_commit_transaction(db);
+	unlink(filename);
+	common_shutdown();
+	trace_cleanup();
+	return 0;
+}