notes from outputs investigation

dot.txt

wallet/db.c     -> table schemas + migrations
wallet/wallet.c -> SQL statements 

table: outputs
question: what are the values for 'status'
	output_state_available= 0,
	output_state_reserved = 1,
	output_state_spent = 2,
	/* Output has been included in a signed funding tx that we've shared
	 * with a peer; not yet mempooled. Eligible for burning */
	output_state_shared = 3,
	/* We've definitely sent/seen this utxo in the mempool;
	 * not yet spent (confirmed). Eligible for RBF */
	output_state_mempooled = 4,
	/* Special status used to express that we don't care in
	 * queries */
	output_state_any = 255
  - from wallet/wallet.h#75-82
  - the are 'available', 'reserved', 'spent'
question: what are the values for 'type'
  - see wallet/wallet.h#103-116
question: what is a 'keyindex'
question: what is 'commitment_point' for an output
  - clue: only gets added if we have 'close_info'
question: what is a 'confirmation_height'
  - also known as 'blockheight'
  - it is the block it got mined in (output)
  - aside: spend_height is block it's an (input)

question: what is the difference in uses for 'outputs' and 'utxoset'
  - utxoset is used to build the utxoset_outpoints filter
  - utxoset is pruned 24hrs (144 blocks) after it is spent
  - the utxoset's txindex is used to derive the scid
  - the utxoset is every p2wsh output that we see on the blockchain
  - outputs are our utxos

	{ "CREATE TABLE outputs ( \
       prev_out_tx CHAR(64),			 \
       prev_out_index INTEGER,			 \
       value INTEGER,				 \
       type INTEGER,				 \
       status INTEGER,				 \
       keyindex INTEGER,			 \
       PRIMARY KEY (prev_out_tx, prev_out_index) \
    );", NULL },
    { "ALTER TABLE outputs ADD COLUMN channel_id INTEGER;", NULL },
    { "ALTER TABLE outputs ADD COLUMN peer_id BLOB;", NULL },
    { "ALTER TABLE outputs ADD COLUMN commitment_point BLOB;", NULL },
	{ "ALTER TABLE outputs ADD COLUMN confirmation_height INTEGER REFERENCES blocks(height) ON DELETE SET NULL;", NULL },
	{ "ALTER TABLE outputs ADD COLUMN spend_height INTEGER REFERENCES blocks(height) ON DELETE SET NULL;", NULL },
	{ "CREATE INDEX output_height_idx ON outputs (confirmation_height, spend_height);", NULL },
	{ "ALTER TABLE outputs ADD scriptpubkey BLOB;", NULL },

table: utxoset
question: what is the 'txindex'
  - txindex is the index of the transaction inside the block it was mined in

	{ "CREATE TABLE utxoset ("
	  " txid BLOB,"
	  " outnum INT,"
	  " blockheight INT REFERENCES blocks(height) ON DELETE CASCADE,"
	  " spendheight INT REFERENCES blocks(height) ON DELETE SET NULL,"
	  " txindex INT,"
	  " scriptpubkey BLOB,"
	  " satoshis BIGINT,"
	  " PRIMARY KEY(txid, outnum));", NULL },

table: transactions
notable: there is no 'timestamp' field (last time updated?)
notable: there is a 'state' field -> 'type' (added later)

what goes in here? 
assumption: any transaction that we create (?)
   - htlc's
   - commitments
   - funding -> linked to channeltxs
   - withdraws?
   - closes
   - 'burn' txs (new with this module)

	{ "CREATE TABLE transactions ("
	  "  id BLOB"
	  ", blockheight INTEGER REFERENCES blocks(height) ON DELETE SET NULL"
	  ", txindex INTEGER"
	  ", rawtx BLOB"
	  ", PRIMARY KEY (id)"
	  ");", NULL },
	{ "ALTER TABLE transactions ADD type INTEGER;", NULL },
	{ "ALTER TABLE transactions ADD channel_id INTEGER;", NULL},


table: channeltxs
notable: there is a 'state' field -> 'type'
question: what is 'input_num'
question: what is 'txo_watch'

	{ "CREATE TABLE channeltxs ("
	  /* The id serves as insertion order and short ID */
	  "  id INTEGER"
	  ", channel_id INTEGER REFERENCES channels(id) ON DELETE CASCADE"
	  ", type INTEGER"
	  ", transaction_id BLOB REFERENCES transactions(id) ON DELETE CASCADE"
	  /* The input_num is only used by the txo_watch, 0 if txwatch */
	  ", input_num INTEGER"
	  /* The height at which we sent the depth notice */
	  ", blockheight INTEGER REFERENCES blocks(height) ON DELETE CASCADE"
	  ", PRIMARY KEY(id)"
	  ");", NULL },