clementine_core/database/
verifier.rs

1//! # Verifier Related Database Operations
2//!
3//! This module includes database functions which are mainly used by a verifier.
4
5use std::ops::DerefMut;
6
7use super::{
8    wrapper::{BlockHashDB, TxidDB, XOnlyPublicKeyDB},
9    Database, DatabaseTransaction,
10};
11use crate::{errors::BridgeError, execute_query_with_tx};
12use bitcoin::{BlockHash, OutPoint, Txid, XOnlyPublicKey};
13use eyre::Context;
14use sqlx::QueryBuilder;
15
16impl Database {
17    /// Returns the last deposit index.
18    /// If no deposits exist, returns None
19    pub async fn get_last_deposit_idx(
20        &self,
21        tx: Option<DatabaseTransaction<'_, '_>>,
22    ) -> Result<Option<u32>, BridgeError> {
23        let query = sqlx::query_as::<_, (i32,)>("SELECT COALESCE(MAX(idx), -1) FROM withdrawals");
24        let result = execute_query_with_tx!(self.connection, tx, query, fetch_one)?;
25        if result.0 == -1 {
26            Ok(None)
27        } else {
28            Ok(Some(result.0 as u32))
29        }
30    }
31
32    /// Returns the last withdrawal index where withdrawal_utxo_txid exists.
33    /// If no withdrawals with UTXOs exist, returns None.
34    pub async fn get_last_withdrawal_idx(
35        &self,
36        tx: Option<DatabaseTransaction<'_, '_>>,
37    ) -> Result<Option<u32>, BridgeError> {
38        let query = sqlx::query_as::<_, (i32,)>(
39            "SELECT COALESCE(MAX(idx), -1) FROM withdrawals WHERE withdrawal_utxo_txid IS NOT NULL",
40        );
41        let result = execute_query_with_tx!(self.connection, tx, query, fetch_one)?;
42        if result.0 == -1 {
43            Ok(None)
44        } else {
45            Ok(Some(result.0 as u32))
46        }
47    }
48
49    pub async fn upsert_move_to_vault_txid_from_citrea_deposit(
50        &self,
51        tx: Option<DatabaseTransaction<'_, '_>>,
52        citrea_idx: u32,
53        move_to_vault_txid: &Txid,
54    ) -> Result<(), BridgeError> {
55        let query = sqlx::query(
56            "INSERT INTO withdrawals (idx, move_to_vault_txid)
57             VALUES ($1, $2)
58             ON CONFLICT (idx) DO UPDATE
59             SET move_to_vault_txid = $2",
60        )
61        .bind(i32::try_from(citrea_idx).wrap_err("Failed to convert citrea index to i32")?)
62        .bind(TxidDB(*move_to_vault_txid));
63
64        execute_query_with_tx!(self.connection, tx, query, execute)?;
65        Ok(())
66    }
67
68    pub async fn get_move_to_vault_txid_from_citrea_deposit(
69        &self,
70        tx: Option<DatabaseTransaction<'_, '_>>,
71        citrea_idx: u32,
72    ) -> Result<Option<Txid>, BridgeError> {
73        let query = sqlx::query_as::<_, (TxidDB,)>(
74            "SELECT move_to_vault_txid FROM withdrawals WHERE idx = $1",
75        )
76        .bind(i32::try_from(citrea_idx).wrap_err("Failed to convert citrea index to i32")?);
77
78        let result: Option<(TxidDB,)> =
79            execute_query_with_tx!(self.connection, tx, query, fetch_optional)?;
80
81        Ok(result.map(|(move_to_vault_txid,)| move_to_vault_txid.0))
82    }
83
84    pub async fn update_replacement_deposit_move_txid(
85        &self,
86        tx: DatabaseTransaction<'_, '_>,
87        idx: u32,
88        new_move_txid: Txid,
89    ) -> Result<(), BridgeError> {
90        let query = sqlx::query(
91            "UPDATE withdrawals
92             SET move_to_vault_txid = $2
93             WHERE idx = $1
94             RETURNING idx",
95        )
96        .bind(i32::try_from(idx).wrap_err("Failed to convert idx to i32")?)
97        .bind(TxidDB(new_move_txid))
98        .fetch_optional(tx.deref_mut())
99        .await?;
100
101        if query.is_none() {
102            return Err(eyre::eyre!("Replacement move txid not found: {}", idx).into());
103        }
104        Ok(())
105    }
106
107    pub async fn update_withdrawal_utxo_from_citrea_withdrawal(
108        &self,
109        tx: Option<DatabaseTransaction<'_, '_>>,
110        citrea_idx: u32,
111        withdrawal_utxo: OutPoint,
112        withdrawal_batch_proof_bitcoin_block_height: u32,
113    ) -> Result<(), BridgeError> {
114        let query = sqlx::query(
115            "UPDATE withdrawals
116             SET withdrawal_utxo_txid = $2,
117                 withdrawal_utxo_vout = $3,
118                 withdrawal_batch_proof_bitcoin_block_height = $4
119             WHERE idx = $1",
120        )
121        .bind(i32::try_from(citrea_idx).wrap_err("Failed to convert citrea index to i32")?)
122        .bind(TxidDB(withdrawal_utxo.txid))
123        .bind(
124            i32::try_from(withdrawal_utxo.vout)
125                .wrap_err("Failed to convert withdrawal utxo vout to i32")?,
126        )
127        .bind(
128            i32::try_from(withdrawal_batch_proof_bitcoin_block_height)
129                .wrap_err("Failed to convert withdrawal batch proof bitcoin block height to i32")?,
130        );
131
132        execute_query_with_tx!(self.connection, tx, query, execute)?;
133        Ok(())
134    }
135
136    /// For the given deposit index, returns the withdrawal utxo associated with it
137    /// If there is no withdrawal utxo set for the deposit, an error is returned
138    pub async fn get_withdrawal_utxo_from_citrea_withdrawal(
139        &self,
140        tx: Option<DatabaseTransaction<'_, '_>>,
141        citrea_idx: u32,
142    ) -> Result<OutPoint, BridgeError> {
143        let query = sqlx::query_as::<_, (Option<TxidDB>, Option<i32>)>(
144            "SELECT w.withdrawal_utxo_txid, w.withdrawal_utxo_vout
145             FROM withdrawals w
146             WHERE w.idx = $1",
147        )
148        .bind(i32::try_from(citrea_idx).wrap_err("Failed to convert citrea index to i32")?);
149
150        let results = execute_query_with_tx!(self.connection, tx, query, fetch_optional)?;
151
152        match results {
153            None => Err(eyre::eyre!("Deposit with id {} is not set", citrea_idx).into()),
154            Some((txid, vout)) => match (txid, vout) {
155                (Some(txid), Some(vout)) => Ok(OutPoint {
156                    txid: txid.0,
157                    vout: u32::try_from(vout)
158                        .wrap_err("Failed to convert withdrawal utxo vout to u32")?,
159                }),
160                _ => {
161                    Err(eyre::eyre!("Withdrawal utxo is not set for deposit {}", citrea_idx).into())
162                }
163            },
164        }
165    }
166
167    /// Returns the withdrawal indexes and their spending txid for the given
168    /// block id.
169    pub async fn get_payout_txs_for_withdrawal_utxos(
170        &self,
171        tx: Option<DatabaseTransaction<'_, '_>>,
172        block_id: u32,
173    ) -> Result<Vec<(u32, Txid)>, BridgeError> {
174        let query = sqlx::query_as::<_, (i32, TxidDB)>(
175            "SELECT w.idx, bsu.spending_txid
176             FROM withdrawals w
177             JOIN bitcoin_syncer_spent_utxos bsu
178                ON bsu.txid = w.withdrawal_utxo_txid
179                AND bsu.vout = w.withdrawal_utxo_vout
180             WHERE bsu.block_id = $1",
181        )
182        .bind(i32::try_from(block_id).wrap_err("Failed to convert block id to i32")?);
183
184        let results = execute_query_with_tx!(self.connection, tx, query, fetch_all)?;
185
186        results
187            .into_iter()
188            .map(|(idx, txid)| {
189                Ok((
190                    u32::try_from(idx).wrap_err("Failed to convert withdrawal index to u32")?,
191                    txid.0,
192                ))
193            })
194            .collect()
195    }
196
197    /// Sets the given payout txs' txid and operator index for the given index.
198    pub async fn update_payout_txs_and_payer_operator_xonly_pk(
199        &self,
200        tx: Option<DatabaseTransaction<'_, '_>>,
201        payout_txs_and_payer_operator_xonly_pk: Vec<(
202            u32,
203            Txid,
204            Option<XOnlyPublicKey>,
205            bitcoin::BlockHash,
206        )>,
207    ) -> Result<(), BridgeError> {
208        if payout_txs_and_payer_operator_xonly_pk.is_empty() {
209            return Ok(());
210        }
211        // Convert all values first, propagating any errors
212        let converted_values: Result<Vec<_>, BridgeError> = payout_txs_and_payer_operator_xonly_pk
213            .iter()
214            .map(|(idx, txid, operator_xonly_pk, block_hash)| {
215                Ok((
216                    i32::try_from(*idx).wrap_err("Failed to convert payout index to i32")?,
217                    TxidDB(*txid),
218                    operator_xonly_pk.map(XOnlyPublicKeyDB),
219                    BlockHashDB(*block_hash),
220                ))
221            })
222            .collect();
223        let converted_values = converted_values?;
224
225        let mut query_builder = QueryBuilder::new(
226            "UPDATE withdrawals AS w SET
227                payout_txid = c.payout_txid,
228                payout_payer_operator_xonly_pk = c.payout_payer_operator_xonly_pk,
229                payout_tx_blockhash = c.payout_tx_blockhash
230                FROM (",
231        );
232
233        query_builder.push_values(
234            converted_values.into_iter(),
235            |mut b, (idx, txid, operator_xonly_pk, block_hash)| {
236                b.push_bind(idx)
237                    .push_bind(txid)
238                    .push_bind(operator_xonly_pk)
239                    .push_bind(block_hash);
240            },
241        );
242
243        query_builder
244            .push(") AS c(idx, payout_txid, payout_payer_operator_xonly_pk, payout_tx_blockhash) WHERE w.idx = c.idx");
245
246        let query = query_builder.build();
247        execute_query_with_tx!(self.connection, tx, query, execute)?;
248
249        Ok(())
250    }
251
252    pub async fn get_payout_info_from_move_txid(
253        &self,
254        tx: Option<DatabaseTransaction<'_, '_>>,
255        move_to_vault_txid: Txid,
256    ) -> Result<Option<(Option<XOnlyPublicKey>, BlockHash, Txid, i32)>, BridgeError> {
257        let query = sqlx::query_as::<_, (Option<XOnlyPublicKeyDB>, BlockHashDB, TxidDB, i32)>(
258            "SELECT w.payout_payer_operator_xonly_pk, w.payout_tx_blockhash, w.payout_txid, w.idx
259             FROM withdrawals w
260             WHERE w.move_to_vault_txid = $1
261               AND w.payout_txid IS NOT NULL
262               AND w.payout_tx_blockhash IS NOT NULL",
263        )
264        .bind(TxidDB(move_to_vault_txid));
265
266        let result: Option<(Option<XOnlyPublicKeyDB>, BlockHashDB, TxidDB, i32)> =
267            execute_query_with_tx!(self.connection, tx, query, fetch_optional)?;
268
269        result
270            .map(|(operator_xonly_pk, block_hash, txid, deposit_idx)| {
271                Ok((
272                    operator_xonly_pk.map(|pk| pk.0),
273                    block_hash.0,
274                    txid.0,
275                    deposit_idx,
276                ))
277            })
278            .transpose()
279    }
280
281    pub async fn get_first_unhandled_payout_by_operator_xonly_pk(
282        &self,
283        tx: Option<DatabaseTransaction<'_, '_>>,
284        operator_xonly_pk: XOnlyPublicKey,
285    ) -> Result<Option<(u32, Txid, BlockHash)>, BridgeError> {
286        let query = sqlx::query_as::<_, (i32, Option<TxidDB>, Option<BlockHashDB>)>(
287            "SELECT w.idx, w.move_to_vault_txid, w.payout_tx_blockhash
288             FROM withdrawals w
289             WHERE w.payout_txid IS NOT NULL
290                AND w.is_payout_handled = FALSE
291                AND w.payout_payer_operator_xonly_pk = $1
292                ORDER BY w.idx ASC
293             LIMIT 1",
294        )
295        .bind(XOnlyPublicKeyDB(operator_xonly_pk));
296
297        let results = execute_query_with_tx!(self.connection, tx, query, fetch_optional)?;
298
299        results
300            .map(|(citrea_idx, move_to_vault_txid, payout_tx_blockhash)| {
301                Ok((
302                    u32::try_from(citrea_idx).wrap_err("Failed to convert citrea index to u32")?,
303                    move_to_vault_txid
304                        .expect("move_to_vault_txid Must be Some")
305                        .0,
306                    payout_tx_blockhash
307                        .expect("payout_tx_blockhash Must be Some")
308                        .0,
309                ))
310            })
311            .transpose()
312    }
313
314    pub async fn get_payer_xonly_pk_blockhash_and_kickoff_txid_from_deposit_id(
315        &self,
316        tx: Option<DatabaseTransaction<'_, '_>>,
317        deposit_id: u32,
318    ) -> Result<(Option<XOnlyPublicKey>, Option<BlockHash>, Option<Txid>), BridgeError> {
319        let query = sqlx::query_as::<
320            _,
321            (
322                Option<XOnlyPublicKeyDB>,
323                Option<BlockHashDB>,
324                Option<TxidDB>,
325            ),
326        >(
327            "SELECT w.payout_payer_operator_xonly_pk, w.payout_tx_blockhash, w.kickoff_txid
328             FROM withdrawals w
329             INNER JOIN deposits d ON d.move_to_vault_txid = w.move_to_vault_txid
330             WHERE d.deposit_id = $1",
331        )
332        .bind(i32::try_from(deposit_id).wrap_err("Failed to convert deposit id to i32")?);
333
334        let result: (
335            Option<XOnlyPublicKeyDB>,
336            Option<BlockHashDB>,
337            Option<TxidDB>,
338        ) = execute_query_with_tx!(self.connection, tx, query, fetch_one)?;
339
340        Ok((
341            result.0.map(|pk| pk.0),
342            result.1.map(|block_hash| block_hash.0),
343            result.2.map(|txid| txid.0),
344        ))
345    }
346
347    pub async fn mark_payout_handled(
348        &self,
349        tx: Option<DatabaseTransaction<'_, '_>>,
350        citrea_idx: u32,
351        kickoff_txid: Txid,
352    ) -> Result<(), BridgeError> {
353        let query = sqlx::query(
354            "UPDATE withdrawals SET is_payout_handled = TRUE, kickoff_txid = $2 WHERE idx = $1",
355        )
356        .bind(i32::try_from(citrea_idx).wrap_err("Failed to convert citrea index to i32")?)
357        .bind(TxidDB(kickoff_txid));
358
359        execute_query_with_tx!(self.connection, tx, query, execute)?;
360        Ok(())
361    }
362
363    pub async fn get_handled_payout_kickoff_txid(
364        &self,
365        tx: Option<DatabaseTransaction<'_, '_>>,
366        payout_txid: Txid,
367    ) -> Result<Option<Txid>, BridgeError> {
368        let query = sqlx::query_as::<_, (Option<TxidDB>,)>(
369            "SELECT kickoff_txid FROM withdrawals WHERE payout_txid = $1 AND is_payout_handled = TRUE",
370        )
371        .bind(TxidDB(payout_txid));
372
373        let result: Option<(Option<TxidDB>,)> =
374            execute_query_with_tx!(self.connection, tx, query, fetch_optional)?;
375
376        Ok(result
377            .map(|(kickoff_txid,)| kickoff_txid.expect("If handled, kickoff_txid must exist").0))
378    }
379}
380
381#[cfg(test)]
382mod tests {
383    use crate::{
384        database::Database,
385        test::common::{create_test_config_with_thread_name, generate_random_xonly_pk},
386    };
387    use bitcoin::{hashes::Hash, BlockHash, Txid};
388
389    #[tokio::test]
390    async fn update_get_payout_txs_from_citrea_withdrawal() {
391        let config = create_test_config_with_thread_name().await;
392        let db = Database::new(&config).await.unwrap();
393
394        let txid = Txid::from_byte_array([0x45; 32]);
395        let index = 0x1F;
396        let operator_xonly_pk = generate_random_xonly_pk();
397        let utxo = bitcoin::OutPoint {
398            txid: bitcoin::Txid::from_byte_array([0x45; 32]),
399            vout: 0,
400        };
401
402        let mut dbtx = db.begin_transaction().await.unwrap();
403
404        let block_id = db
405            .insert_block_info(
406                Some(&mut dbtx),
407                &BlockHash::all_zeros(),
408                &BlockHash::all_zeros(),
409                utxo.vout,
410            )
411            .await
412            .unwrap();
413        db.insert_txid_to_block(&mut dbtx, block_id, &txid)
414            .await
415            .unwrap();
416        db.insert_spent_utxo(&mut dbtx, block_id, &txid, &utxo.txid, utxo.vout.into())
417            .await
418            .unwrap();
419
420        assert!(db
421            .get_withdrawal_utxo_from_citrea_withdrawal(Some(&mut dbtx), index)
422            .await
423            .is_err());
424        db.upsert_move_to_vault_txid_from_citrea_deposit(Some(&mut dbtx), index, &txid)
425            .await
426            .unwrap();
427        db.update_withdrawal_utxo_from_citrea_withdrawal(Some(&mut dbtx), index, utxo, block_id)
428            .await
429            .unwrap();
430
431        let block_hash = BlockHash::all_zeros();
432
433        db.update_payout_txs_and_payer_operator_xonly_pk(
434            Some(&mut dbtx),
435            vec![(index, txid, Some(operator_xonly_pk), block_hash)],
436        )
437        .await
438        .unwrap();
439
440        let txs = db
441            .get_payout_txs_for_withdrawal_utxos(Some(&mut dbtx), block_id)
442            .await
443            .unwrap();
444
445        assert_eq!(txs.len(), 1);
446        assert_eq!(txs[0].0, index);
447        assert_eq!(txs[0].1, txid);
448
449        let withdrawal_utxo = db
450            .get_withdrawal_utxo_from_citrea_withdrawal(Some(&mut dbtx), index)
451            .await
452            .unwrap();
453        assert_eq!(withdrawal_utxo, utxo);
454
455        let move_txid = db
456            .get_move_to_vault_txid_from_citrea_deposit(Some(&mut dbtx), index)
457            .await
458            .unwrap()
459            .unwrap();
460        assert_eq!(move_txid, txid);
461
462        // Test payout info retrieval with Some operator xonly pk
463        let payout_info = db
464            .get_payout_info_from_move_txid(Some(&mut dbtx), move_txid)
465            .await
466            .unwrap()
467            .unwrap();
468        assert_eq!(payout_info.0, Some(operator_xonly_pk));
469        assert_eq!(payout_info.1, block_hash);
470        assert_eq!(payout_info.2, txid);
471        assert_eq!(payout_info.3, index as i32);
472
473        // Test with None operator xonly pk (optimistic payout or incorrect payout)
474        let index2 = 0x2F;
475        let txid2 = Txid::from_byte_array([0x55; 32]);
476        let utxo2 = bitcoin::OutPoint {
477            txid: bitcoin::Txid::from_byte_array([0x55; 32]),
478            vout: 1,
479        };
480
481        db.insert_txid_to_block(&mut dbtx, block_id, &txid2)
482            .await
483            .unwrap();
484        db.insert_spent_utxo(&mut dbtx, block_id, &txid2, &utxo2.txid, utxo2.vout.into())
485            .await
486            .unwrap();
487
488        db.upsert_move_to_vault_txid_from_citrea_deposit(Some(&mut dbtx), index2, &txid2)
489            .await
490            .unwrap();
491        db.update_withdrawal_utxo_from_citrea_withdrawal(Some(&mut dbtx), index2, utxo2, block_id)
492            .await
493            .unwrap();
494
495        // Set payout with None operator xonly pk
496        db.update_payout_txs_and_payer_operator_xonly_pk(
497            Some(&mut dbtx),
498            vec![(index2, txid2, None, block_hash)],
499        )
500        .await
501        .unwrap();
502
503        // Test payout info retrieval with None operator xonly pk
504        let payout_info2 = db
505            .get_payout_info_from_move_txid(Some(&mut dbtx), txid2)
506            .await
507            .unwrap()
508            .unwrap();
509        assert_eq!(payout_info2.0, None); // No operator xonly pk
510        assert_eq!(payout_info2.1, block_hash);
511        assert_eq!(payout_info2.2, txid2);
512        assert_eq!(payout_info2.3, index2 as i32);
513
514        // Verify we now have 2 payout transactions
515        let all_txs = db
516            .get_payout_txs_for_withdrawal_utxos(Some(&mut dbtx), block_id)
517            .await
518            .unwrap();
519        assert_eq!(all_txs.len(), 2);
520    }
521}
clementine_core/database/verifier.rs

clementine_core/database/
verifier.rs
