wallet: add our_outputs + our_txs schema migrations
What the developer wrote
Authored by Sangbida Chaudhuri
wallet: add our_outputs + our_txs schema migrations
The next commits move wallet UTXO and tx tracking off chaintopology and
onto bwatch. bwatch doesn't maintain a blocks table, but the legacy
utxoset, transactions and channeltxs tables all have FOREIGN KEY
references into blocks(height) (CASCADE / SET NULL), so we can't just
retarget the existing tables.
Instead, introduce parallel tables (our_outputs, our_txs) without the
blocks(height) FK. The new bwatch-driven code writes only to these,
the legacy tables stay populated by the existing code path during this
release so downgrade still works, and a future release can drop them
once we're past the downgrade window.
Losing the FK also changes what NULL means. In the legacy tables a
NULL blockheight was never written by hand: the ON DELETE SET NULL
trigger produced it when a reorg deleted the block row. These tables
have no such trigger, so unconfirmed is stored as blockheight 0
(NOT NULL) instead, for three reasons:
- everything feeding these tables already speaks u32-with-0: watchman
notifications carry blockheight as a required JSON number, and
wallet_transaction_height() has always returned 0 for unconfirmed,
so values bind straight through without a NULL/non-NULL branch at
every read and write site;
- integer comparisons keep working: the unconfirmed->confirmed
promotion is a single "WHERE blockheight < ?" (0 sorts below any
real height) and reorg rollback is "SET blockheight = 0 WHERE
blockheight >= ?", where a NULL row would match neither;
- it removes the footgun the legacy code warned about ("Note:
blockheight=NULL is not the same as is NULL!"), where lookups had
to branch between "= ?" and "IS NULL".
The same logic gives txindex 0 = unconfirmed/unknown (a *confirmed*
txindex of 0 means coinbase, which blockheight disambiguates) and
reserved_til 0 = not reserved. NULL survives only where 0 is a real
value or genuinely ambiguous: spendheight (NULL = unspent),
channel_dbid, commitment_point.
Schema only here — wallet handlers that write into these tables and the
backfill from outputs/transactions land in subsequent commits.
Co-authored-by: Cursor <cursoragent@cursor.com>
What changed, and why it matters
This commit only adds two new empty database tables (our_outputs and our_txs) to Core Lightning's wallet. It is a preparatory schema change for a future feature; no code reads from or writes to these tables yet, and no existing behavior is changed. There is no direct security vulnerability here, though any new table design could later affect how safely wallet data is tracked.
No immediate action required. Treat as normal infrastructure/schema refactoring. When reviewing the follow-up commits that write to and read from these tables, verify that reorg handling, unconfirmed-to-confirmed promotion, and NULL/sentinel comparisons are implemented correctly and consistently.
Security signals we found
Schema-only migration with no runtime code
New tables are not yet populated or queried by any code path
Design note: sentinel 0 replaces NULL for blockheight/txindex/reserved_til, which could reduce NULL-handling bugs in future code
No FOREIGN KEY to blocks(height), removing CASCADE/SET NULL reorg semantics; future code must handle reorg rollback explicitly
Evidence from the diff
The commit appends two SQLite CREATE TABLE statements to wallet/migrations.c as forward migrations, with corresponding DROP TABLE rollback statements. The tables mirror legacy utxoset/transactions structures but drop the FOREIGN KEY into blocks(height) so that a planned bwatch-based backend can operate without a blocks table. The schema uses 0 as a sentinel for unconfirmed/unknown/not-reserved where NULL was previously used, and keeps NULL only where it is semantically necessary (spendheight, channel_dbid, commitment_point). No handlers, queries, or runtime logic are added; this is schema-only groundwork.
Changed components
wallet/migrations.cwallet database schema (new tables our_outputs, our_txs)Inspect captured patch +38 / −0
diff --git a/wallet/migrations.c b/wallet/migrations.c
index 1f5d3b5..cb6a666 100644
--- a/wallet/migrations.c
+++ b/wallet/migrations.c
@@ -1085,6 +1085,44 @@ static const struct db_migration dbmigrations[] = {
{SQL("ALTER TABLE offers ADD COLUMN force_paths INTEGER DEFAULT 0;"), NULL,
SQL("ALTER TABLE offers DROP COLUMN force_paths"), NULL},
/* ^v26.04 */
+
+ /* Parallel wallet tables without the blocks(height) FK that
+ * utxoset/transactions carry, so bwatch-driven writes don't need a
+ * blocks table. Legacy tables stay for one release to keep downgrade
+ * working.
+ *
+ * Sentinels instead of NULLs wherever 0 is unambiguous:
+ * blockheight 0 = unconfirmed, txindex 0 = unconfirmed/unknown
+ * (a *confirmed* txindex of 0 means coinbase), reserved_til 0 = not
+ * reserved. NULL remains only where it carries meaning a sentinel
+ * can't: spendheight (NULL = unspent), channel_dbid (NULL = HD wallet
+ * output, set = channel-close output owned via the channel columns),
+ * commitment_point (NULL = option_static_remotekey). */
+ {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)"
+ ")"), NULL,
+ SQL("DROP TABLE our_outputs"), NULL},
+ {SQL("CREATE TABLE our_txs ("
+ " txid BLOB NOT NULL PRIMARY KEY,"
+ " blockheight INTEGER NOT NULL,"
+ " txindex INTEGER NOT NULL DEFAULT 0,"
+ " rawtx BLOB"
+ ")"), NULL,
+ SQL("DROP TABLE our_txs"), NULL},
};
const struct db_migration *get_db_migrations(size_t *num)
Why this scored 18/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.