1use ahash::AHashMap;
17use nautilus_common::signal::Signal;
18#[cfg(feature = "defi")]
19use nautilus_common::{cache::CacheSnapshotRef, defi::cache::InstrumentMigration};
20use nautilus_model::{
21 accounts::AccountAny,
22 data::{
23 Bar, CustomData, DataType, HasTsInit, InstrumentClose, QuoteTick, TradeTick,
24 greeks::{GreeksData, YieldCurve},
25 },
26 events::{
27 AccountState, OrderEvent, OrderEventAny, OrderFilled, OrderInitialized, OrderSnapshot,
28 position::snapshot::PositionSnapshot,
29 },
30 identifiers::{AccountId, ClientId, ClientOrderId, InstrumentId, PositionId, TraderId},
31 instruments::{Instrument, InstrumentAny, SyntheticInstrument},
32 orders::OrderAny,
33 position::Position,
34 types::Currency,
35};
36#[cfg(feature = "defi")]
37use nautilus_model::{
38 defi::{
39 Blockchain,
40 currency::{self, TokenCurrency},
41 },
42 enums::OrderStatus,
43 orders::Order,
44};
45use sqlx::{FromRow, PgPool, Postgres, Row, Transaction};
46#[cfg(feature = "defi")]
47use ustr::Ustr;
48
49use super::models::{orders::OrderSnapshotRow, positions::PositionSnapshotRow, types::SignalRow};
50#[cfg(feature = "defi")]
51use crate::defi::{contract_conflict, currency_references, integrity_error, validate_marker};
52use crate::sql::models::{
53 accounts::AccountEventRow,
54 data::{BarRow, InstrumentCloseRow, QuoteTickRow, TradeTickRow},
55 enums::{
56 AggregationSourcePg, AggressorSidePg, AssetClassPg, BarAggregationPg, CurrencyTypePg,
57 PriceTypePg, TrailingOffsetTypePg,
58 },
59 general::{GeneralRow, OrderEventOrderClientIdCombination, OrderPositionIndexRow},
60 instruments::InstrumentAnyRow,
61 orders::{OrderEventAnyRow, OrderFilledRow},
62 types::CurrencyRow,
63};
64
65pub(super) const CACHE_DATABASE_KEY_PREFIX: &str = "cache-database:";
66
67#[derive(Debug)]
68pub struct DatabaseQueries;
69
70impl DatabaseQueries {
71 pub async fn truncate(pool: &PgPool) -> anyhow::Result<()> {
77 sqlx::query("SELECT truncate_all_tables()")
78 .execute(pool)
79 .await
80 .map(|_| ())
81 .map_err(|e| anyhow::anyhow!("Failed to truncate tables: {e}"))
82 }
83
84 pub async fn delete_trader(pool: &PgPool, trader_id: &TraderId) -> anyhow::Result<()> {
94 let mut transaction = pool.begin().await?;
95
96 let statements = [
97 r#"DELETE FROM "order_position_index" WHERE trader_id = $1"#,
98 r#"DELETE FROM "order_event" WHERE trader_id = $1"#,
99 r#"DELETE FROM "order" WHERE trader_id = $1"#,
100 r#"DELETE FROM "position_event" WHERE trader_id = $1"#,
101 r#"DELETE FROM "position" WHERE trader_id = $1"#,
102 r#"DELETE FROM "account_event" WHERE trader_id = $1"#,
103 ];
104
105 for statement in statements {
106 sqlx::query(statement)
107 .bind(trader_id.to_string())
108 .execute(&mut *transaction)
109 .await
110 .map_err(|e| anyhow::anyhow!("Failed to delete rows for {trader_id}: {e}"))?;
111 }
112
113 sqlx::query("DELETE FROM general WHERE starts_with(id, $1)")
114 .bind(trader_key_prefix(*trader_id))
115 .execute(&mut *transaction)
116 .await?;
117
118 transaction
119 .commit()
120 .await
121 .map_err(|e| anyhow::anyhow!("Failed to commit delete for {trader_id}: {e}"))
122 }
123
124 pub async fn add(pool: &PgPool, key: String, value: Vec<u8>) -> anyhow::Result<()> {
130 sqlx::query(
131 "INSERT INTO general (id, value) VALUES ($1, $2) \
132 ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value",
133 )
134 .bind(key)
135 .bind(value)
136 .execute(pool)
137 .await
138 .map(|_| ())
139 .map_err(|e| anyhow::anyhow!("Failed to insert into general table: {e}"))
140 }
141
142 pub async fn delete(pool: &PgPool, key: &str) -> anyhow::Result<()> {
148 sqlx::query("DELETE FROM general WHERE id = $1")
149 .bind(key)
150 .execute(pool)
151 .await?;
152 Ok(())
153 }
154
155 pub async fn delete_order(
162 pool: &PgPool,
163 trader_id: TraderId,
164 client_order_id: ClientOrderId,
165 ) -> anyhow::Result<()> {
166 let mut transaction = pool.begin().await?;
167
168 for statement in [
169 "DELETE FROM order_position_index WHERE trader_id = $1 AND client_order_id = $2",
170 "DELETE FROM order_event WHERE trader_id = $1 AND client_order_id = $2",
171 "DELETE FROM \"order\" WHERE trader_id = $1 AND id = $2",
172 ] {
173 sqlx::query(statement)
174 .bind(trader_id.to_string())
175 .bind(client_order_id.to_string())
176 .execute(&mut *transaction)
177 .await?;
178 }
179
180 transaction.commit().await?;
181 Ok(())
182 }
183
184 pub async fn delete_position(
191 pool: &PgPool,
192 trader_id: TraderId,
193 position_id: PositionId,
194 ) -> anyhow::Result<()> {
195 let mut transaction = pool.begin().await?;
196
197 for statement in [
198 "DELETE FROM order_position_index WHERE trader_id = $1 AND position_id = $2",
199 "DELETE FROM position_event WHERE trader_id = $1 AND position_id = $2",
200 "DELETE FROM position WHERE trader_id = $1 AND id = $2",
201 ] {
202 sqlx::query(statement)
203 .bind(trader_id.to_string())
204 .bind(position_id.to_string())
205 .execute(&mut *transaction)
206 .await?;
207 }
208
209 transaction.commit().await?;
210 Ok(())
211 }
212
213 pub(super) async fn load_synthetic(
214 pool: &PgPool,
215 instrument_id: &InstrumentId,
216 ) -> anyhow::Result<Option<SyntheticInstrument>> {
217 let key = format!("{CACHE_DATABASE_KEY_PREFIX}synthetic:{instrument_id}");
218 let value = Self::load_value(pool, &key).await?;
219 value
220 .map(|value| serde_json::from_slice(&value).map_err(Into::into))
221 .transpose()
222 }
223
224 pub(super) async fn load_value(pool: &PgPool, key: &str) -> anyhow::Result<Option<Vec<u8>>> {
225 Ok(
226 sqlx::query_scalar("SELECT value FROM general WHERE id = $1")
227 .bind(key)
228 .fetch_optional(pool)
229 .await?,
230 )
231 }
232
233 pub async fn load(pool: &PgPool) -> anyhow::Result<AHashMap<String, Vec<u8>>> {
239 sqlx::query_as::<_, GeneralRow>("SELECT * FROM general")
240 .fetch_all(pool)
241 .await
242 .map(|rows| {
243 let mut cache: AHashMap<String, Vec<u8>> = AHashMap::new();
244 for row in rows {
245 cache.insert(row.id, row.value);
246 }
247 cache
248 })
249 .map_err(|e| anyhow::anyhow!("Failed to load general table: {e}"))
250 }
251
252 pub async fn add_currency(pool: &PgPool, currency: Currency) -> anyhow::Result<()> {
258 #[cfg(feature = "defi")]
259 if currency::check(currency)
260 .map_err(integrity_error)?
261 .is_some()
262 {
263 let mut transaction = pool.begin().await?;
264 Self::write_token_currency(&mut transaction, currency).await?;
265 transaction.commit().await?;
266 return Ok(());
267 }
268
269 #[cfg(feature = "defi")]
270 if currency.code.contains('@') {
271 let mut transaction = pool.begin().await?;
272 Self::insert_currency(&mut *transaction, currency).await?;
273 let tagged: bool = sqlx::query_scalar(
274 "SELECT EXISTS (SELECT 1 FROM defi_currency WHERE currency_id = $1)",
275 )
276 .bind(currency.code.as_str())
277 .fetch_one(&mut *transaction)
278 .await?;
279
280 if tagged {
281 return Err(integrity_error(format!(
282 "Missing token metadata for {}",
283 currency.code
284 )));
285 }
286
287 transaction.commit().await?;
288 return Ok(());
289 }
290
291 Self::insert_currency(pool, currency).await.map(|_| ())
292 }
293
294 async fn insert_currency<'e>(
295 executor: impl sqlx::Executor<'e, Database = Postgres>,
296 currency: Currency,
297 ) -> anyhow::Result<u64> {
298 sqlx::query(
299 "INSERT INTO currency (id, precision, iso4217, name, currency_type) VALUES ($1, $2, $3, $4, $5::currency_type) ON CONFLICT (id) DO NOTHING"
300 )
301 .bind(currency.code.as_str())
302 .bind(i32::from(currency.precision))
303 .bind(i32::from(currency.iso4217))
304 .bind(currency.name.as_str())
305 .bind(CurrencyTypePg(currency.currency_type))
306 .execute(executor)
307 .await
308 .map(|result| result.rows_affected())
309 .map_err(|e| anyhow::anyhow!("Failed to insert into currency table: {e}"))
310 }
311
312 pub async fn load_currencies(pool: &PgPool) -> anyhow::Result<Vec<Currency>> {
318 let currencies = sqlx::query_as::<_, CurrencyRow>("SELECT * FROM currency ORDER BY id ASC")
319 .fetch_all(pool)
320 .await
321 .map_err(|e| anyhow::anyhow!("Failed to load currencies: {e}"))?;
322 #[cfg(feature = "defi")]
323 let mut metadata = Self::load_token_metadata(pool, None).await?;
324 currencies
325 .into_iter()
326 .map(|row| {
327 #[cfg(feature = "defi")]
328 return Self::restore_token_currency(row.0, metadata.remove(&row.0.code));
329 #[cfg(not(feature = "defi"))]
330 Ok(row.0)
331 })
332 .collect()
333 }
334
335 pub async fn load_currency(pool: &PgPool, code: &str) -> anyhow::Result<Option<Currency>> {
341 let currency = sqlx::query_as::<_, CurrencyRow>("SELECT * FROM currency WHERE id = $1")
342 .bind(code)
343 .fetch_optional(pool)
344 .await
345 .map_err(|e| anyhow::anyhow!("Failed to load currency: {e}"))?;
346
347 #[cfg(feature = "defi")]
348 if let Some(row) = currency {
349 let metadata = Self::load_token_metadata(pool, Some(code))
350 .await?
351 .remove(&row.0.code);
352 Self::restore_token_currency(row.0, metadata).map(Some)
353 } else {
354 Ok(None)
355 }
356
357 #[cfg(not(feature = "defi"))]
358 Ok(currency.map(|row| row.0))
359 }
360
361 pub async fn add_instrument(
367 pool: &PgPool,
368 kind: &str,
369 instrument: Box<dyn Instrument>,
370 ) -> anyhow::Result<()> {
371 #[cfg(feature = "defi")]
372 {
373 let codes = currency::instrument_codes(instrument.as_ref()).map_err(integrity_error)?;
374 return Self::write_token_instrument(pool, kind, instrument, &codes, None).await;
375 }
376
377 #[cfg(not(feature = "defi"))]
378 Self::write_instrument(pool, kind, instrument).await
379 }
380
381 async fn write_instrument<'e>(
382 executor: impl sqlx::Executor<'e, Database = Postgres>,
383 kind: &str,
384 instrument: Box<dyn Instrument>,
385 ) -> anyhow::Result<()> {
386 sqlx::query(r#"
387 INSERT INTO "instrument" (
388 id, kind, raw_symbol, base_currency, underlying, quote_currency, settlement_currency, isin, asset_class, exchange,
389 strategy_type, multiplier, option_kind, is_inverse, strike_price, activation_ns, expiration_ns, price_precision, size_precision,
390 price_increment, size_increment, margin_init, margin_maint, lot_size, max_quantity, min_quantity, max_notional,
391 min_notional, max_price, min_price, ts_init, ts_event, info, created_at, updated_at
392 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::asset_class, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33::json, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
393 ON CONFLICT (id)
394 DO UPDATE
395 SET
396 kind = $2, raw_symbol = $3, base_currency= $4, underlying = $5, quote_currency = $6, settlement_currency = $7, isin = $8, asset_class = $9, exchange = $10,
397 strategy_type = $11, multiplier = $12, option_kind = $13, is_inverse = $14, strike_price = $15, activation_ns = $16, expiration_ns = $17 , price_precision = $18, size_precision = $19,
398 price_increment = $20, size_increment = $21, margin_init = $22, margin_maint = $23, lot_size = $24, max_quantity = $25,
399 min_quantity = $26, max_notional = $27, min_notional = $28, max_price = $29, min_price = $30, ts_init = $31, ts_event = $32, info = $33::json, updated_at = CURRENT_TIMESTAMP
400 "#)
401 .bind(instrument.id().to_string())
402 .bind(kind)
403 .bind(instrument.raw_symbol().to_string())
404 .bind(instrument.base_currency().map(|x| x.code.as_str()))
405 .bind(instrument.underlying().map(|x| x.to_string()))
406 .bind(instrument.quote_currency().code.as_str())
407 .bind(instrument.settlement_currency().code.as_str())
408 .bind(instrument.isin().map(|x| x.to_string()))
409 .bind(AssetClassPg(instrument.asset_class()))
410 .bind(instrument.exchange().map(|x| x.to_string()))
411 .bind(instrument.strategy_type().map(|x| x.to_string()))
412 .bind(instrument.multiplier().to_string())
413 .bind(instrument.option_kind().map(|x| x.to_string()))
414 .bind(instrument.is_inverse())
415 .bind(instrument.strike_price().map(|x| x.to_string()))
416 .bind(instrument.activation_ns().map(|x| x.to_string()))
417 .bind(instrument.expiration_ns().map(|x| x.to_string()))
418 .bind(i32::from(instrument.price_precision()))
419 .bind(i32::from(instrument.size_precision()))
420 .bind(instrument.price_increment().to_string())
421 .bind(instrument.size_increment().to_string())
422 .bind(instrument.margin_init().to_string())
423 .bind(instrument.margin_maint().to_string())
424 .bind(instrument.lot_size().map(|x| x.to_string()))
425 .bind(instrument.max_quantity().map(|x| x.to_string()))
426 .bind(instrument.min_quantity().map(|x| x.to_string()))
427 .bind(instrument.max_notional().map(|x| x.to_string()))
428 .bind(instrument.min_notional().map(|x| x.to_string()))
429 .bind(instrument.max_price().map(|x| x.to_string()))
430 .bind(instrument.min_price().map(|x| x.to_string()))
431 .bind(instrument.ts_init().to_string())
432 .bind(instrument.ts_event().to_string())
433 .bind(instrument.info().map(serde_json::to_string).transpose()?)
434 .execute(executor)
435 .await
436 .map(|_| ())
437 .map_err(|e| anyhow::anyhow!("Failed to insert item {} into instrument table: {:?}", instrument.id(), e))
438 }
439
440 #[cfg(feature = "defi")]
441 async fn load_token_metadata(
442 pool: &PgPool,
443 code: Option<&str>,
444 ) -> anyhow::Result<AHashMap<Ustr, TokenCurrency>> {
445 let exists: bool = sqlx::query_scalar("SELECT to_regclass('defi_currency') IS NOT NULL")
446 .fetch_one(pool)
447 .await
448 .map_err(integrity_error)?;
449
450 if !exists {
451 return Ok(AHashMap::new());
452 }
453
454 let rows =
455 sqlx::query("SELECT * FROM defi_currency WHERE ($1::TEXT IS NULL OR currency_id = $1)")
456 .bind(code)
457 .fetch_all(pool)
458 .await
459 .map_err(integrity_error)?;
460 rows.iter()
461 .map(|row| {
462 let code: String = row.try_get("currency_id").map_err(integrity_error)?;
463 Ok((Ustr::from(&code), Self::token_metadata(row)?))
464 })
465 .collect()
466 }
467
468 #[cfg(feature = "defi")]
469 fn token_metadata(row: &sqlx::postgres::PgRow) -> anyhow::Result<TokenCurrency> {
470 let chain_id: i64 = row.try_get("chain_id").map_err(integrity_error)?;
471 let name: String = row.try_get("chain_name").map_err(integrity_error)?;
472 Ok(TokenCurrency {
473 chain_id: u32::try_from(chain_id).map_err(integrity_error)?,
474 address: row.try_get("address").map_err(integrity_error)?,
475 symbol: row.try_get("symbol").map_err(integrity_error)?,
476 chain_name: name.parse::<Blockchain>().map_err(integrity_error)?,
477 })
478 }
479
480 #[cfg(feature = "defi")]
481 fn restore_token_currency(
482 currency: Currency,
483 metadata: Option<TokenCurrency>,
484 ) -> anyhow::Result<Currency> {
485 if let Some(metadata) = metadata {
486 return currency::register(currency, metadata).map_err(integrity_error);
487 }
488
489 if currency::lookup(currency.code).is_some() {
490 return Err(integrity_error(format!(
491 "Missing token metadata for {}",
492 currency.code
493 )));
494 }
495
496 Ok(currency)
497 }
498
499 #[cfg(feature = "defi")]
500 fn instrument_marker(row: &sqlx::postgres::PgRow) -> anyhow::Result<Vec<String>> {
501 match row.try_get::<Option<Vec<String>>, _>("defi_currency_codes") {
502 Ok(Some(codes)) if codes.is_empty() => {
503 Err(integrity_error("Empty DeFi instrument dependency marker"))
504 }
505 Ok(codes) => Ok(codes.unwrap_or_default()),
506 Err(sqlx::Error::ColumnNotFound(_)) => Ok(Vec::new()),
507 Err(e) => Err(integrity_error(e)),
508 }
509 }
510
511 #[cfg(feature = "defi")]
512 async fn restore_instrument_dependencies(
513 pool: &PgPool,
514 row: &sqlx::postgres::PgRow,
515 metadata: &AHashMap<Ustr, TokenCurrency>,
516 ) -> anyhow::Result<()> {
517 let codes = Self::instrument_marker(row)?;
518 let mut expected = Vec::new();
519 let mut references = Vec::new();
520
521 for field in ["base_currency", "quote_currency", "settlement_currency"] {
522 if let Some(code) = row
523 .try_get::<Option<String>, _>(field)
524 .map_err(integrity_error)?
525 {
526 if metadata.contains_key(&Ustr::from(&code))
527 || currency::lookup(Ustr::from(&code)).is_some()
528 {
529 expected.push(code.clone());
530 }
531
532 references.push(code);
533 }
534 }
535
536 expected.sort_unstable();
537 expected.dedup();
538 if expected != codes {
539 return Err(integrity_error(
540 "Instrument token dependencies differ from its marker",
541 ));
542 }
543
544 for code in &codes {
545 let currency = Self::load_currency(pool, code)
546 .await
547 .map_err(integrity_error)?
548 .ok_or_else(|| integrity_error(format!("Missing token currency {code}")))?;
549
550 if currency::lookup(currency.code).is_none() {
551 return Err(integrity_error(format!(
552 "Missing token metadata for {code}"
553 )));
554 }
555 }
556
557 if !codes.is_empty()
558 && references
559 .iter()
560 .any(|code| Currency::try_from_str(code).is_none())
561 {
562 return Err(integrity_error(
563 "A marked instrument has an unresolved currency",
564 ));
565 }
566
567 Ok(())
568 }
569
570 #[cfg(feature = "defi")]
571 async fn write_token_currency(
572 transaction: &mut Transaction<'_, Postgres>,
573 currency: Currency,
574 ) -> anyhow::Result<()> {
575 let metadata = currency::check(currency)
576 .map_err(integrity_error)?
577 .ok_or_else(|| {
578 integrity_error(format!("Missing token definition for {}", currency.code))
579 })?;
580
581 let inserted = Self::insert_currency(&mut **transaction, currency).await?;
582 let row = sqlx::query("SELECT * FROM currency WHERE id = $1 FOR UPDATE")
583 .bind(currency.code.as_str())
584 .fetch_one(&mut **transaction)
585 .await?;
586 let row = CurrencyRow::from_row(&row).map_err(integrity_error)?;
587
588 if inserted == 1 {
589 sqlx::query("INSERT INTO defi_currency (currency_id, chain_id, address, symbol, chain_name) VALUES ($1,$2,$3,$4,$5) ON CONFLICT (chain_id, address) DO NOTHING")
590 .bind(currency.code.as_str()).bind(i64::from(metadata.chain_id)).bind(&metadata.address)
591 .bind(&metadata.symbol).bind(metadata.chain_name.to_string())
592 .execute(&mut **transaction).await?;
593 }
594
595 let bound_code: Option<String> = sqlx::query_scalar(
596 "SELECT currency_id FROM defi_currency WHERE chain_id = $1 AND address = $2",
597 )
598 .bind(i64::from(metadata.chain_id))
599 .bind(&metadata.address)
600 .fetch_optional(&mut **transaction)
601 .await?;
602
603 if let Some(code) = bound_code
604 && code != currency.code.as_str()
605 {
606 return Err(contract_conflict(&code));
607 }
608
609 let tag = sqlx::query("SELECT * FROM defi_currency WHERE currency_id = $1")
610 .bind(currency.code.as_str())
611 .fetch_optional(&mut **transaction)
612 .await?
613 .ok_or_else(|| {
614 integrity_error(format!(
615 "Legacy currency or missing metadata for {}",
616 currency.code
617 ))
618 })?;
619
620 let stored_metadata = Self::token_metadata(&tag)?;
621 currency::register(row.0, stored_metadata).map_err(integrity_error)?;
622 Ok(())
623 }
624
625 #[cfg(feature = "defi")]
626 pub(crate) async fn add_defi_instrument(
627 pool: &PgPool,
628 instrument: InstrumentAny,
629 migration: Option<InstrumentMigration>,
630 ) -> anyhow::Result<()> {
631 let codes = currency::instrument_codes(&instrument).map_err(integrity_error)?;
632 if codes.is_empty() {
633 return Self::add_instrument_any(pool, instrument).await;
634 }
635
636 match instrument {
637 InstrumentAny::CurrencyPair(pair) => {
638 Self::write_token_instrument(
639 pool,
640 "CURRENCY_PAIR",
641 Box::new(pair),
642 &codes,
643 migration.as_ref(),
644 )
645 .await
646 }
647 _ => Err(integrity_error("DeFi persistence supports currency pairs")),
648 }
649 }
650
651 pub(crate) async fn add_instrument_any(
652 pool: &PgPool,
653 instrument_any: InstrumentAny,
654 ) -> anyhow::Result<()> {
655 match instrument_any {
656 InstrumentAny::Betting(instrument) => {
657 Self::add_instrument(pool, "BETTING", Box::new(instrument)).await
658 }
659 InstrumentAny::BinaryOption(instrument) => {
660 Self::add_instrument(pool, "BINARY_OPTION", Box::new(instrument)).await
661 }
662 InstrumentAny::CryptoFuture(instrument) => {
663 Self::add_instrument(pool, "CRYPTO_FUTURE", Box::new(instrument)).await
664 }
665 InstrumentAny::CryptoFuturesSpread(instrument) => {
666 Self::add_instrument(pool, "CRYPTO_FUTURES_SPREAD", Box::new(instrument)).await
667 }
668 InstrumentAny::CryptoOption(instrument) => {
669 Self::add_instrument(pool, "CRYPTO_OPTION", Box::new(instrument)).await
670 }
671 InstrumentAny::CryptoOptionSpread(instrument) => {
672 Self::add_instrument(pool, "CRYPTO_OPTION_SPREAD", Box::new(instrument)).await
673 }
674 InstrumentAny::CryptoPerpetual(instrument) => {
675 Self::add_instrument(pool, "CRYPTO_PERPETUAL", Box::new(instrument)).await
676 }
677 InstrumentAny::CurrencyPair(instrument) => {
678 Self::add_instrument(pool, "CURRENCY_PAIR", Box::new(instrument)).await
679 }
680 InstrumentAny::Equity(equity) => {
681 Self::add_instrument(pool, "EQUITY", Box::new(equity)).await
682 }
683 InstrumentAny::FuturesContract(instrument) => {
684 Self::add_instrument(pool, "FUTURES_CONTRACT", Box::new(instrument)).await
685 }
686 InstrumentAny::FuturesSpread(instrument) => {
687 Self::add_instrument(pool, "FUTURES_SPREAD", Box::new(instrument)).await
688 }
689 InstrumentAny::OptionContract(instrument) => {
690 Self::add_instrument(pool, "OPTION_CONTRACT", Box::new(instrument)).await
691 }
692 InstrumentAny::Commodity(instrument) => {
693 Self::add_instrument(pool, "COMMODITY", Box::new(instrument)).await
694 }
695 InstrumentAny::IndexInstrument(instrument) => {
696 Self::add_instrument(pool, "INDEX_INSTRUMENT", Box::new(instrument)).await
697 }
698 InstrumentAny::Cfd(instrument) => {
699 Self::add_instrument(pool, "CFD", Box::new(instrument)).await
700 }
701 InstrumentAny::OptionSpread(instrument) => {
702 Self::add_instrument(pool, "OPTION_SPREAD", Box::new(instrument)).await
703 }
704 InstrumentAny::PerpetualContract(instrument) => {
705 Self::add_instrument(pool, "PERPETUAL_CONTRACT", Box::new(instrument)).await
706 }
707 InstrumentAny::TokenizedAsset(instrument) => {
708 Self::add_instrument(pool, "TOKENIZED_ASSET", Box::new(instrument)).await
709 }
710 }
711 }
712
713 #[cfg(feature = "defi")]
714 async fn check_legacy_replacement(
715 transaction: &mut Transaction<'_, Postgres>,
716 id: &str,
717 ) -> anyhow::Result<()> {
718 let history: bool = sqlx::query_scalar("SELECT EXISTS (SELECT 1 FROM position WHERE instrument_id = $1 UNION ALL SELECT 1 FROM position_event WHERE instrument_id = $1)")
719 .bind(id).fetch_one(&mut **transaction).await?;
720
721 let archives: Vec<Vec<u8>> =
722 sqlx::query_scalar("SELECT value FROM general WHERE id LIKE $1")
723 .bind(format!("{}%", CacheSnapshotRef::POSITION_PREFIX))
724 .fetch_all(&mut **transaction)
725 .await?;
726 let mut history = history;
727 for blob in archives {
728 history |= CacheSnapshotRef::position_instrument_id(&blob)
729 .map_err(integrity_error)?
730 .to_string()
731 == id;
732 }
733
734 if history {
735 return Err(integrity_error(format!(
736 "Instrument {id} has position history; retain its legacy currencies"
737 )));
738 }
739
740 let rows = sqlx::query("SELECT * FROM order_event WHERE instrument_id = $1 ORDER BY trader_id, client_order_id, created_at ASC")
741 .bind(id).fetch_all(&mut **transaction).await?;
742 let mut events: AHashMap<(String, String), Vec<OrderEventAny>> = AHashMap::new();
743
744 for row in rows {
745 let trader = row.try_get("trader_id").map_err(integrity_error)?;
746 let order = row.try_get("client_order_id").map_err(integrity_error)?;
747 events
748 .entry((trader, order))
749 .or_default()
750 .push(OrderEventAnyRow::from_row(&row).map_err(integrity_error)?.0);
751 }
752
753 for events in events.values() {
754 let order = OrderAny::from_events(events.clone()).map_err(integrity_error)?;
755 if !order.is_closed() {
756 return Err(integrity_error(format!(
757 "Instrument {id} has nonterminal orders"
758 )));
759 }
760 }
761
762 let snapshots =
763 sqlx::query(r#"SELECT trader_id, id, status FROM "order" WHERE instrument_id = $1"#)
764 .bind(id)
765 .fetch_all(&mut **transaction)
766 .await?;
767
768 for row in snapshots {
769 let key = (
770 row.try_get::<String, _>("trader_id")
771 .map_err(integrity_error)?,
772 row.try_get::<String, _>("id").map_err(integrity_error)?,
773 );
774
775 if !events.contains_key(&key) {
776 let status: String = row.try_get("status").map_err(integrity_error)?;
777 if !status
778 .parse::<OrderStatus>()
779 .map_err(integrity_error)?
780 .is_closed()
781 {
782 return Err(integrity_error(format!(
783 "Instrument {id} has nonterminal orders"
784 )));
785 }
786 }
787 }
788
789 Ok(())
790 }
791
792 #[cfg(feature = "defi")]
793 async fn write_token_instrument(
794 pool: &PgPool,
795 kind: &str,
796 instrument: Box<dyn Instrument>,
797 codes: &[String],
798 migration: Option<&InstrumentMigration>,
799 ) -> anyhow::Result<()> {
800 let references = currency_references(instrument.as_ref());
801 let id = instrument.id().to_string();
802 let mut transaction = pool.begin().await?;
803 sqlx::query("SELECT pg_advisory_xact_lock(hashtextextended($1, 0))")
805 .bind(&id)
806 .execute(&mut *transaction)
807 .await?;
808 let previous = sqlx::query("SELECT * FROM instrument WHERE id = $1 FOR UPDATE")
809 .bind(&id)
810 .fetch_optional(&mut *transaction)
811 .await?;
812
813 if let Some(row) = previous {
814 let previous_codes = Self::instrument_marker(&row)?;
815 if previous_codes.is_empty() {
816 if !codes.is_empty() {
817 migration
818 .ok_or_else(|| {
819 integrity_error(
820 "Historical replacement requires the cache migration check",
821 )
822 })?
823 .validate()
824 .map_err(integrity_error)?;
825
826 Self::check_legacy_replacement(&mut transaction, &id).await?;
827 }
828 } else if previous_codes != codes {
829 return Err(integrity_error(
830 "Cannot change a persisted token instrument's currency identities",
831 ));
832 } else {
833 let base: Option<String> = row.try_get("base_currency").map_err(integrity_error)?;
834 let quote: String = row.try_get("quote_currency").map_err(integrity_error)?;
835 let settlement: Option<String> = row
836 .try_get("settlement_currency")
837 .map_err(integrity_error)?;
838
839 if base
840 != instrument
841 .base_currency()
842 .map(|currency| currency.code.to_string())
843 || quote != instrument.quote_currency().code.as_str()
844 || settlement != Some(instrument.settlement_currency().code.to_string())
845 {
846 return Err(integrity_error(
847 "Cannot change a persisted token instrument's currency identities",
848 ));
849 }
850 }
851 }
852
853 for code in codes {
854 let (currency, _) = currency::lookup(Ustr::from(code))
855 .ok_or_else(|| integrity_error(format!("Missing token definition for {code}")))?;
856 Self::write_token_currency(&mut transaction, currency).await?;
857 }
858
859 Self::write_instrument(&mut *transaction, kind, instrument).await?;
860 let mut persisted: Vec<String> =
861 sqlx::query_scalar("SELECT currency_id FROM defi_currency WHERE currency_id = ANY($1)")
862 .bind(&references)
863 .fetch_all(&mut *transaction)
864 .await?;
865
866 persisted.sort_unstable();
867 if persisted != codes {
868 return Err(integrity_error(
869 "Instrument token dependencies differ from persisted currency definitions",
870 ));
871 }
872
873 sqlx::query("UPDATE instrument SET defi_currency_codes = $2 WHERE id = $1")
874 .bind(id)
875 .bind((!codes.is_empty()).then_some(codes))
876 .execute(&mut *transaction)
877 .await?;
878 transaction.commit().await?;
879 Ok(())
880 }
881
882 pub async fn load_instrument(
888 pool: &PgPool,
889 instrument_id: &InstrumentId,
890 ) -> anyhow::Result<Option<InstrumentAny>> {
891 let row = sqlx::query("SELECT * FROM instrument WHERE id = $1")
892 .bind(instrument_id.to_string())
893 .fetch_optional(pool)
894 .await?;
895
896 let Some(row) = row else {
897 return Ok(None);
898 };
899
900 #[cfg(feature = "defi")]
901 {
902 let metadata = Self::load_token_metadata(pool, None).await?;
903 Self::restore_instrument_dependencies(pool, &row, &metadata).await?;
904 }
905
906 let instrument = InstrumentAnyRow::from_row(&row)?.0;
907 #[cfg(feature = "defi")]
908 validate_marker(&instrument, &Self::instrument_marker(&row)?)?;
909 Ok(Some(instrument))
910 }
911
912 pub async fn load_instruments(pool: &PgPool) -> anyhow::Result<Vec<InstrumentAny>> {
918 let rows = sqlx::query("SELECT * FROM instrument")
919 .fetch_all(pool)
920 .await?;
921 let mut instruments = Vec::with_capacity(rows.len());
922 #[cfg(feature = "defi")]
923 let metadata = Self::load_token_metadata(pool, None).await?;
924
925 for row in rows {
926 #[cfg(feature = "defi")]
927 Self::restore_instrument_dependencies(pool, &row, &metadata).await?;
928 let instrument = InstrumentAnyRow::from_row(&row)?.0;
929 #[cfg(feature = "defi")]
930 validate_marker(&instrument, &Self::instrument_marker(&row)?)?;
931 instruments.push(instrument);
932 }
933
934 Ok(instruments)
935 }
936
937 pub async fn add_instrument_close(
943 pool: &PgPool,
944 close: &InstrumentClose,
945 ) -> anyhow::Result<()> {
946 sqlx::query(
947 r#"
948 INSERT INTO "instrument_close" (
949 instrument_id, close_price, close_type, ts_event, ts_init, created_at
950 ) VALUES ($1, $2, $3, $4, $5, CURRENT_TIMESTAMP)
951 ON CONFLICT (instrument_id) DO UPDATE
952 SET close_price = EXCLUDED.close_price,
953 close_type = EXCLUDED.close_type,
954 ts_event = EXCLUDED.ts_event,
955 ts_init = EXCLUDED.ts_init
956 "#,
957 )
958 .bind(close.instrument_id.to_string())
959 .bind(close.close_price.to_string())
960 .bind(close.close_type.to_string())
961 .bind(close.ts_event.to_string())
962 .bind(close.ts_init.to_string())
963 .execute(pool)
964 .await
965 .map(|_| ())
966 .map_err(|e| anyhow::anyhow!("Failed to insert instrument close: {e}"))
967 }
968
969 pub async fn load_instrument_closes(pool: &PgPool) -> anyhow::Result<Vec<InstrumentClose>> {
975 sqlx::query_as::<_, InstrumentCloseRow>(
976 "SELECT * FROM instrument_close ORDER BY instrument_id ASC",
977 )
978 .fetch_all(pool)
979 .await
980 .map(|rows| rows.into_iter().map(|row| row.0).collect())
981 .map_err(|e| anyhow::anyhow!("Failed to load instrument closes: {e}"))
982 }
983
984 pub(crate) async fn add_greeks(pool: &PgPool, greeks: &GreeksData) -> anyhow::Result<()> {
985 let value = serde_json::to_value(greeks)?;
986 sqlx::query(
987 "INSERT INTO greeks (instrument_id, value) VALUES ($1, $2) \
988 ON CONFLICT (instrument_id) DO UPDATE SET value = EXCLUDED.value",
989 )
990 .bind(greeks.instrument_id.to_string())
991 .bind(value)
992 .execute(pool)
993 .await?;
994 Ok(())
995 }
996
997 pub(crate) async fn load_greeks(
998 pool: &PgPool,
999 ) -> anyhow::Result<AHashMap<InstrumentId, GreeksData>> {
1000 let rows = sqlx::query("SELECT instrument_id, value FROM greeks")
1001 .fetch_all(pool)
1002 .await?;
1003 let mut greeks = AHashMap::with_capacity(rows.len());
1004 for row in rows {
1005 let key: String = row.try_get("instrument_id")?;
1006 let instrument_id = key.parse::<InstrumentId>()?;
1007 let data: GreeksData = serde_json::from_value(row.try_get("value")?)?;
1008 anyhow::ensure!(
1009 data.instrument_id == instrument_id,
1010 "Greeks key ID {instrument_id} did not match payload ID {}",
1011 data.instrument_id
1012 );
1013 greeks.insert(instrument_id, data);
1014 }
1015
1016 Ok(greeks)
1017 }
1018
1019 pub(crate) async fn add_yield_curve(pool: &PgPool, curve: &YieldCurve) -> anyhow::Result<()> {
1020 let value = serde_json::to_value(curve)?;
1021 sqlx::query(
1022 "INSERT INTO yield_curve (curve_name, value) VALUES ($1, $2) \
1023 ON CONFLICT (curve_name) DO UPDATE SET value = EXCLUDED.value",
1024 )
1025 .bind(&curve.curve_name)
1026 .bind(value)
1027 .execute(pool)
1028 .await?;
1029 Ok(())
1030 }
1031
1032 pub(crate) async fn load_yield_curves(
1033 pool: &PgPool,
1034 ) -> anyhow::Result<AHashMap<String, YieldCurve>> {
1035 let rows = sqlx::query("SELECT curve_name, value FROM yield_curve")
1036 .fetch_all(pool)
1037 .await?;
1038 let mut curves = AHashMap::with_capacity(rows.len());
1039 for row in rows {
1040 let name: String = row.try_get("curve_name")?;
1041 let curve: YieldCurve = serde_json::from_value(row.try_get("value")?)?;
1042 anyhow::ensure!(
1043 curve.curve_name == name,
1044 "Yield curve key {name} did not match payload name {}",
1045 curve.curve_name
1046 );
1047 curve.validate()?;
1048 curves.insert(name, curve);
1049 }
1050
1051 Ok(curves)
1052 }
1053
1054 pub async fn add_order(
1060 pool: &PgPool,
1061 event: OrderInitialized,
1062 client_id: Option<ClientId>,
1063 ) -> anyhow::Result<()> {
1064 Self::add_order_event(pool, Box::new(event), client_id).await
1065 }
1066
1067 #[expect(
1077 clippy::too_many_lines,
1078 reason = "order snapshot persistence maps the full database schema in one transaction"
1079 )]
1080 pub async fn add_order_snapshot(pool: &PgPool, snapshot: OrderSnapshot) -> anyhow::Result<()> {
1081 let mut transaction = pool.begin().await?;
1082
1083 sqlx::query(
1086 r#"
1087 INSERT INTO "trader" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
1088 "#,
1089 )
1090 .bind(snapshot.trader_id.to_string())
1091 .execute(&mut *transaction)
1092 .await
1093 .map(|_| ())
1094 .map_err(|e| anyhow::anyhow!("Failed to insert into trader table: {e}"))?;
1095
1096 sqlx::query(
1097 r#"
1098 INSERT INTO "order" (
1099 id, trader_id, strategy_id, instrument_id, client_order_id, venue_order_id, position_id,
1100 account_id, last_trade_id, order_type, order_side, quantity, price, trigger_price,
1101 trigger_type, limit_offset, trailing_offset, trailing_offset_type, time_in_force,
1102 expire_time, filled_qty, liquidity_side, avg_px, slippage, commissions, status,
1103 is_post_only, is_reduce_only, is_quote_quantity, display_qty, emulation_trigger,
1104 trigger_instrument_id, contingency_type, order_list_id, linked_order_ids,
1105 parent_order_id, exec_algorithm_id, exec_algorithm_params, exec_spawn_id, tags, init_id, ts_init, ts_last,
1106 activation_price, created_at, updated_at
1107 ) VALUES (
1108 $1, $2, $3, $4, $1, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16,
1109 $17::TRAILING_OFFSET_TYPE, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28,
1110 $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43,
1111 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
1112 )
1113 ON CONFLICT (trader_id, id)
1114 DO UPDATE SET
1115 strategy_id = $3,
1116 instrument_id = $4,
1117 venue_order_id = $5,
1118 position_id = $6,
1119 account_id = $7,
1120 last_trade_id = $8,
1121 order_type = $9,
1122 order_side = $10,
1123 quantity = $11,
1124 price = $12,
1125 trigger_price = $13,
1126 trigger_type = $14,
1127 limit_offset = $15,
1128 trailing_offset = $16,
1129 trailing_offset_type = $17::TRAILING_OFFSET_TYPE,
1130 time_in_force = $18,
1131 expire_time = $19,
1132 filled_qty = $20,
1133 liquidity_side = $21,
1134 avg_px = $22,
1135 slippage = $23,
1136 commissions = $24,
1137 status = $25,
1138 is_post_only = $26,
1139 is_reduce_only = $27,
1140 is_quote_quantity = $28,
1141 display_qty = $29,
1142 emulation_trigger = $30,
1143 trigger_instrument_id = $31,
1144 contingency_type = $32,
1145 order_list_id = $33,
1146 linked_order_ids = $34,
1147 parent_order_id = $35,
1148 exec_algorithm_id = $36,
1149 exec_algorithm_params = $37,
1150 exec_spawn_id = $38,
1151 tags = $39,
1152 init_id = $40,
1153 ts_init = $41,
1154 ts_last = $42,
1155 activation_price = $43,
1156 updated_at = CURRENT_TIMESTAMP
1157 "#)
1158 .bind(snapshot.client_order_id.to_string()) .bind(snapshot.trader_id.to_string())
1160 .bind(snapshot.strategy_id.to_string())
1161 .bind(snapshot.instrument_id.to_string())
1162 .bind(snapshot.venue_order_id.map(|x| x.to_string()))
1163 .bind(snapshot.position_id.map(|x| x.to_string()))
1164 .bind(snapshot.account_id.map(|x| x.to_string()))
1165 .bind(snapshot.last_trade_id.map(|x| x.to_string()))
1166 .bind(snapshot.order_type.to_string())
1167 .bind(snapshot.order_side.to_string())
1168 .bind(snapshot.quantity.to_string())
1169 .bind(snapshot.price.map(|x| x.to_string()))
1170 .bind(snapshot.trigger_price.map(|x| x.to_string()))
1171 .bind(snapshot.trigger_type.map(|x| x.to_string()))
1172 .bind(snapshot.limit_offset.map(|x| x.to_string()))
1173 .bind(snapshot.trailing_offset.map(|x| x.to_string()))
1174 .bind(
1175 snapshot
1176 .trailing_offset_type
1177 .map(|value| TrailingOffsetTypePg(Some(value))),
1178 )
1179 .bind(snapshot.time_in_force.to_string())
1180 .bind(snapshot.expire_time.map(|x| x.to_string()))
1181 .bind(snapshot.filled_qty.to_string())
1182 .bind(snapshot.liquidity_side.map(|x| x.to_string()))
1183 .bind(snapshot.avg_px)
1184 .bind(snapshot.slippage)
1185 .bind(snapshot.commissions.iter().map(ToString::to_string).collect::<Vec<String>>())
1186 .bind(snapshot.status.to_string())
1187 .bind(snapshot.is_post_only)
1188 .bind(snapshot.is_reduce_only)
1189 .bind(snapshot.is_quote_quantity)
1190 .bind(snapshot.display_qty.map(|x| x.to_string()))
1191 .bind(
1192 snapshot
1193 .emulation_trigger
1194 .map_or_else(|| "NO_TRIGGER".to_string(), |value| value.to_string()),
1195 )
1196 .bind(snapshot.trigger_instrument_id.map(|x| x.to_string()))
1197 .bind(snapshot.contingency_type.map_or_else(
1198 || "NO_CONTINGENCY".to_string(),
1199 |value| value.to_string(),
1200 ))
1201 .bind(snapshot.order_list_id.map(|x| x.to_string()))
1202 .bind(snapshot.linked_order_ids.map(|x| x.iter().map(ToString::to_string).collect::<Vec<String>>()))
1203 .bind(snapshot.parent_order_id.map(|x| x.to_string()))
1204 .bind(snapshot.exec_algorithm_id.map(|x| x.to_string()))
1205 .bind(snapshot.exec_algorithm_params.map(|x| serde_json::to_value(x).unwrap()))
1206 .bind(snapshot.exec_spawn_id.map(|x| x.to_string()))
1207 .bind(snapshot.tags.map(|x| x.iter().map(ToString::to_string).collect::<Vec<String>>()))
1208 .bind(snapshot.init_id.to_string())
1209 .bind(snapshot.ts_init.to_string())
1210 .bind(snapshot.ts_last.to_string())
1211 .bind(snapshot.activation_price.map(|x| x.to_string()))
1212 .execute(&mut *transaction)
1213 .await
1214 .map(|_| ())
1215 .map_err(|e| anyhow::anyhow!("Failed to insert into order table: {e}"))?;
1216
1217 transaction
1218 .commit()
1219 .await
1220 .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
1221 }
1222
1223 pub async fn load_order_snapshot(
1229 pool: &PgPool,
1230 client_order_id: &ClientOrderId,
1231 trader_id: &TraderId,
1232 ) -> anyhow::Result<Option<OrderSnapshot>> {
1233 sqlx::query_as::<_, OrderSnapshotRow>(
1234 r#"SELECT * FROM "order" WHERE client_order_id = $1 AND trader_id = $2"#,
1235 )
1236 .bind(client_order_id.to_string())
1237 .bind(trader_id.to_string())
1238 .fetch_optional(pool)
1239 .await
1240 .map(|row| row.map(|row| row.0))
1241 .map_err(|e| anyhow::anyhow!("Failed to load order snapshot: {e}"))
1242 }
1243
1244 pub async fn add_position_snapshot(
1250 pool: &PgPool,
1251 snapshot: PositionSnapshot,
1252 ) -> anyhow::Result<()> {
1253 let mut transaction = pool.begin().await?;
1254
1255 sqlx::query(
1258 r#"
1259 INSERT INTO "trader" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
1260 "#,
1261 )
1262 .bind(snapshot.trader_id.to_string())
1263 .execute(&mut *transaction)
1264 .await
1265 .map(|_| ())
1266 .map_err(|e| anyhow::anyhow!("Failed to insert into trader table: {e}"))?;
1267
1268 sqlx::query(r#"
1269 INSERT INTO "position" (
1270 id, trader_id, strategy_id, instrument_id, account_id, opening_order_id, closing_order_id, entry, side, signed_qty, quantity, peak_qty,
1271 quote_currency, base_currency, settlement_currency, avg_px_open, avg_px_close, realized_return, realized_pnl, unrealized_pnl, commissions,
1272 duration_ns, ts_opened, ts_closed, ts_init, ts_last, replay_state, created_at, updated_at
1273 ) VALUES (
1274 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
1275 $21, $22, $23, $24, $25, $26, $27, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
1276 )
1277 ON CONFLICT (trader_id, id)
1278 DO UPDATE
1279 SET
1280 strategy_id = $3, instrument_id = $4, account_id = $5, opening_order_id = $6, closing_order_id = $7, entry = $8, side = $9, signed_qty = $10, quantity = $11,
1281 peak_qty = $12, quote_currency = $13, base_currency = $14, settlement_currency = $15, avg_px_open = $16, avg_px_close = $17, realized_return = $18, realized_pnl = $19, unrealized_pnl = $20,
1282 commissions = $21, duration_ns = $22, ts_opened = $23, ts_closed = $24, ts_init = $25, ts_last = $26,
1283 replay_state = $27, updated_at = CURRENT_TIMESTAMP
1284 "#)
1285 .bind(snapshot.position_id.to_string())
1286 .bind(snapshot.trader_id.to_string())
1287 .bind(snapshot.strategy_id.to_string())
1288 .bind(snapshot.instrument_id.to_string())
1289 .bind(snapshot.account_id.to_string())
1290 .bind(snapshot.opening_order_id.to_string())
1291 .bind(snapshot.closing_order_id.map(|x| x.to_string()))
1292 .bind(snapshot.entry.to_string())
1293 .bind(snapshot.side.to_string())
1294 .bind(snapshot.signed_qty)
1295 .bind(snapshot.quantity.to_string())
1296 .bind(snapshot.peak_qty.to_string())
1297 .bind(snapshot.quote_currency.to_string())
1298 .bind(snapshot.base_currency.map(|x| x.to_string()))
1299 .bind(snapshot.settlement_currency.to_string())
1300 .bind(snapshot.avg_px_open)
1301 .bind(snapshot.avg_px_close)
1302 .bind(snapshot.realized_return)
1303 .bind(snapshot.realized_pnl.map(|x| x.to_string()))
1304 .bind(snapshot.unrealized_pnl.map(|x| x.to_string()))
1305 .bind(snapshot.commissions.iter().map(ToString::to_string).collect::<Vec<String>>())
1306 .bind(snapshot.duration_ns.map(|x| x.to_string()))
1307 .bind(snapshot.ts_opened.to_string())
1308 .bind(snapshot.ts_closed.map(|x| x.to_string()))
1309 .bind(snapshot.ts_init.to_string())
1310 .bind(snapshot.ts_last.to_string())
1311 .bind(snapshot.replay_state)
1312 .execute(&mut *transaction)
1313 .await
1314 .map(|_| ())
1315 .map_err(|e| anyhow::anyhow!("Failed to insert into position table: {e}"))?;
1316 transaction
1317 .commit()
1318 .await
1319 .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
1320 }
1321
1322 pub async fn load_position_snapshot(
1328 pool: &PgPool,
1329 position_id: &PositionId,
1330 trader_id: &TraderId,
1331 ) -> anyhow::Result<Option<PositionSnapshot>> {
1332 sqlx::query_as::<_, PositionSnapshotRow>(
1333 r#"SELECT * FROM "position" WHERE id = $1 AND trader_id = $2"#,
1334 )
1335 .bind(position_id.to_string())
1336 .bind(trader_id.to_string())
1337 .fetch_optional(pool)
1338 .await
1339 .map(|row| row.map(|row| row.0))
1340 .map_err(|e| anyhow::anyhow!("Failed to load position snapshot: {e}"))
1341 }
1342
1343 pub async fn check_if_order_initialized_exists(
1349 pool: &PgPool,
1350 client_order_id: ClientOrderId,
1351 trader_id: &TraderId,
1352 ) -> anyhow::Result<bool> {
1353 sqlx::query(
1354 r#"
1355 SELECT EXISTS(
1356 SELECT 1 FROM "order_event"
1357 WHERE client_order_id = $1 AND trader_id = $2 AND kind = 'OrderInitialized'
1358 )
1359 "#,
1360 )
1361 .bind(client_order_id.to_string())
1362 .bind(trader_id.to_string())
1363 .fetch_one(pool)
1364 .await
1365 .map(|row| row.get(0))
1366 .map_err(|e| anyhow::anyhow!("Failed to check if order initialized exists: {e}"))
1367 }
1368
1369 pub async fn check_if_account_event_exists(
1375 pool: &PgPool,
1376 account_id: AccountId,
1377 trader_id: &TraderId,
1378 ) -> anyhow::Result<bool> {
1379 sqlx::query(
1380 r#"
1381 SELECT EXISTS(
1382 SELECT 1 FROM "account_event"
1383 WHERE account_id = $1 AND trader_id = $2
1384 )
1385 "#,
1386 )
1387 .bind(account_id.to_string())
1388 .bind(trader_id.to_string())
1389 .fetch_one(pool)
1390 .await
1391 .map(|row| row.get(0))
1392 .map_err(|e| anyhow::anyhow!("Failed to check if account event exists: {e}"))
1393 }
1394
1395 #[expect(
1404 clippy::too_many_lines,
1405 reason = "order event persistence maps the full database schema in one transaction"
1406 )]
1407 pub async fn add_order_event(
1408 pool: &PgPool,
1409 order_event: Box<dyn OrderEvent>,
1410 client_id: Option<ClientId>,
1411 ) -> anyhow::Result<()> {
1412 let mut transaction = pool.begin().await?;
1413
1414 sqlx::query(
1417 r#"
1418 INSERT INTO "trader" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
1419 "#,
1420 )
1421 .bind(order_event.trader_id().to_string())
1422 .execute(&mut *transaction)
1423 .await
1424 .map(|_| ())
1425 .map_err(|e| anyhow::anyhow!("Failed to insert into trader table: {e}"))?;
1426
1427 if let Some(client_id) = client_id {
1430 sqlx::query(
1431 r#"
1432 INSERT INTO "client" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
1433 "#,
1434 )
1435 .bind(client_id.to_string())
1436 .execute(&mut *transaction)
1437 .await
1438 .map(|_| ())
1439 .map_err(|e| anyhow::anyhow!("Failed to insert into client table: {e}"))?;
1440 }
1441
1442 let exec_algorithm_params = order_event
1443 .exec_algorithm_params()
1444 .map(serde_json::to_value)
1445 .transpose()
1446 .map_err(|e| anyhow::anyhow!("Failed to serialize exec algorithm params: {e}"))?;
1447 let info = order_event
1448 .info()
1449 .map(serde_json::to_value)
1450 .transpose()
1451 .map_err(|e| anyhow::anyhow!("Failed to serialize order event info: {e}"))?;
1452
1453 let inserted = sqlx::query(r#"
1454 INSERT INTO "order_event" (
1455 id, kind, client_order_id, order_type, order_side, trader_id, client_id, reason, strategy_id, instrument_id, trade_id, currency, quantity, time_in_force, liquidity_side,
1456 post_only, reduce_only, quote_quantity, reconciliation, price, last_px, last_qty, trigger_price, trigger_type, limit_offset, trailing_offset,
1457 trailing_offset_type, expire_time, display_qty, emulation_trigger, trigger_instrument_id, contingency_type,
1458 order_list_id, linked_order_ids, parent_order_id,
1459 exec_algorithm_id, exec_spawn_id, venue_order_id, account_id, position_id, commission, ts_event, ts_init, activation_price, exec_algorithm_params, tags,
1460 released_price, protection_price, due_post_only, correction_id, is_reopened, info, causation_id, created_at, updated_at
1461 ) VALUES (
1462 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
1463 $21, $22, $23, $24, $25, $26::trailing_offset_type, $27, $28, $29, $30, $31, $32, $33, $34,
1464 $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46,
1465 $47, $48, $49, $50, $51, $52, $53, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
1466 )
1467 ON CONFLICT (id)
1468 DO UPDATE
1469 SET
1470 kind = $2, client_order_id = $3, order_type = $4, order_side=$5, client_id = $7, reason = $8, strategy_id = $9, instrument_id = $10, trade_id = $11, currency = $12,
1471 quantity = $13, time_in_force = $14, liquidity_side = $15, post_only = $16, reduce_only = $17, quote_quantity = $18, reconciliation = $19, price = $20, last_px = $21,
1472 last_qty = $22, trigger_price = $23, trigger_type = $24, limit_offset = $25, trailing_offset = $26, trailing_offset_type = $27, expire_time = $28, display_qty = $29,
1473 emulation_trigger = $30, trigger_instrument_id = $31, contingency_type = $32, order_list_id = $33, linked_order_ids = $34, parent_order_id = $35, exec_algorithm_id = $36,
1474 exec_spawn_id = $37, venue_order_id = $38, account_id = $39, position_id = $40, commission = $41, ts_event = $42, ts_init = $43, activation_price = $44,
1475 exec_algorithm_params = $45, tags = $46, released_price = $47, protection_price = $48, due_post_only = $49, correction_id = $50,
1476 is_reopened = $51, info = $52, causation_id = $53, updated_at = CURRENT_TIMESTAMP
1477 WHERE "order_event".trader_id = $6
1478 "#)
1479 .bind(order_event.id().to_string())
1480 .bind(order_event.type_name())
1481 .bind(order_event.client_order_id().to_string())
1482 .bind(order_event.order_type().map(|x| x.to_string()))
1483 .bind(order_event.order_side().map(|x| x.to_string()))
1484 .bind(order_event.trader_id().to_string())
1485 .bind(client_id.map(|x| x.to_string()))
1486 .bind(order_event.reason().map(|x| x.to_string()))
1487 .bind(order_event.strategy_id().to_string())
1488 .bind(order_event.instrument_id().to_string())
1489 .bind(order_event.trade_id().map(|x| x.to_string()))
1490 .bind(order_event.currency().map(|x| x.code.as_str()))
1491 .bind(order_event.quantity().map(|x| x.to_string()))
1492 .bind(order_event.time_in_force().map(|x| x.to_string()))
1493 .bind(order_event.liquidity_side().map(|x| x.to_string()))
1494 .bind(order_event.post_only())
1495 .bind(order_event.reduce_only())
1496 .bind(order_event.quote_quantity())
1497 .bind(order_event.reconciliation())
1498 .bind(order_event.price().map(|x| x.to_string()))
1499 .bind(order_event.last_px().map(|x| x.to_string()))
1500 .bind(order_event.last_qty().map(|x| x.to_string()))
1501 .bind(order_event.trigger_price().map(|x| x.to_string()))
1502 .bind(order_event.trigger_type().map(|x| x.to_string()))
1503 .bind(order_event.limit_offset().map(|x| x.to_string()))
1504 .bind(order_event.trailing_offset().map(|x| x.to_string()))
1505 .bind(
1506 order_event
1507 .trailing_offset_type()
1508 .map(|value| TrailingOffsetTypePg(Some(value))),
1509 )
1510 .bind(order_event.expire_time().map(|x| x.to_string()))
1511 .bind(order_event.display_qty().map(|x| x.to_string()))
1512 .bind(
1513 order_event
1514 .emulation_trigger()
1515 .map_or_else(|| "NO_TRIGGER".to_string(), |value| value.to_string()),
1516 )
1517 .bind(order_event.trigger_instrument_id().map(|x| x.to_string()))
1518 .bind(order_event.contingency_type().map_or_else(
1519 || "NO_CONTINGENCY".to_string(),
1520 |value| value.to_string(),
1521 ))
1522 .bind(order_event.order_list_id().map(|x| x.to_string()))
1523 .bind(order_event.linked_order_ids().map(|x| x.iter().map(ToString::to_string).collect::<Vec<String>>()))
1524 .bind(order_event.parent_order_id().map(|x| x.to_string()))
1525 .bind(order_event.exec_algorithm_id().map(|x| x.to_string()))
1526 .bind(order_event.exec_spawn_id().map(|x| x.to_string()))
1527 .bind(order_event.venue_order_id().map(|x| x.to_string()))
1528 .bind(order_event.account_id().map(|x| x.to_string()))
1529 .bind(order_event.position_id().map(|x| x.to_string()))
1530 .bind(order_event.commission().map(|x| x.to_string()))
1531 .bind(order_event.ts_event().to_string())
1532 .bind(order_event.ts_init().to_string())
1533 .bind(order_event.activation_price().map(|x| x.to_string()))
1534 .bind(exec_algorithm_params)
1535 .bind(order_event.tags().map(|x| x.iter().map(ToString::to_string).collect::<Vec<String>>()))
1536 .bind(order_event.released_price().map(|x| x.to_string()))
1537 .bind(order_event.protection_price().map(|x| x.to_string()))
1538 .bind(order_event.due_post_only())
1539 .bind(order_event.correction_id().map(|x| x.to_string()))
1540 .bind(order_event.is_reopened())
1541 .bind(info)
1542 .bind(order_event.causation_id().map(|x| x.to_string()))
1543 .execute(&mut *transaction)
1544 .await
1545 .map_err(|e| anyhow::anyhow!("Failed to insert into order_event table: {e}"))?;
1546
1547 if inserted.rows_affected() == 0 {
1550 anyhow::bail!(
1551 "Order event {} is already persisted for another trader, not {}",
1552 order_event.id(),
1553 order_event.trader_id()
1554 );
1555 }
1556
1557 transaction
1558 .commit()
1559 .await
1560 .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
1561 }
1562
1563 pub async fn load_order_events(
1569 pool: &PgPool,
1570 client_order_id: &ClientOrderId,
1571 trader_id: &TraderId,
1572 ) -> anyhow::Result<Vec<OrderEventAny>> {
1573 sqlx::query_as::<_, OrderEventAnyRow>(
1574 r#"
1575 SELECT * FROM "order_event" event
1576 WHERE event.client_order_id = $1 AND event.trader_id = $2
1577 ORDER BY created_at ASC
1578 "#,
1579 )
1580 .bind(client_order_id.to_string())
1581 .bind(trader_id.to_string())
1582 .fetch_all(pool)
1583 .await
1584 .map(|rows| rows.into_iter().map(|row| row.0).collect())
1585 .map_err(|e| anyhow::anyhow!("Failed to load order events: {e}"))
1586 }
1587
1588 pub async fn load_order(
1594 pool: &PgPool,
1595 client_order_id: &ClientOrderId,
1596 trader_id: &TraderId,
1597 ) -> anyhow::Result<Option<OrderAny>> {
1598 let order_events = Self::load_order_events(pool, client_order_id, trader_id).await;
1599
1600 match order_events {
1601 Ok(order_events) => {
1602 if order_events.is_empty() {
1603 return Ok(None);
1604 }
1605 let order = OrderAny::from_events(order_events).map_err(|e| {
1606 anyhow::anyhow!("Failed to assemble order {client_order_id} from events: {e}")
1607 })?;
1608 Ok(Some(order))
1609 }
1610 Err(e) => anyhow::bail!("Failed to load order events: {e}"),
1611 }
1612 }
1613
1614 pub async fn load_orders(pool: &PgPool, trader_id: &TraderId) -> anyhow::Result<Vec<OrderAny>> {
1622 let mut orders: Vec<OrderAny> = Vec::new();
1623 let client_order_ids: Vec<ClientOrderId> = sqlx::query(
1624 r#"
1625 SELECT DISTINCT client_order_id FROM "order_event"
1626 WHERE trader_id = $1
1627 "#,
1628 )
1629 .bind(trader_id.to_string())
1630 .fetch_all(pool)
1631 .await
1632 .map(|rows| {
1633 rows.into_iter()
1634 .map(|row| ClientOrderId::from(row.get::<&str, _>(0)))
1635 .collect()
1636 })
1637 .map_err(|e| anyhow::anyhow!("Failed to load order ids: {e}"))?;
1638 for id in client_order_ids {
1639 let order = Self::load_order(pool, &id, trader_id).await?;
1640 if let Some(order) = order {
1641 orders.push(order);
1642 }
1643 }
1644 Ok(orders)
1645 }
1646
1647 pub async fn add_position(
1653 pool: &PgPool,
1654 position_id: PositionId,
1655 event: &OrderFilled,
1656 ) -> anyhow::Result<()> {
1657 let event_position_id = Self::event_position_id(event)?;
1658 if event_position_id != position_id {
1659 anyhow::bail!(
1660 "Cannot persist position event {} for mismatched position_id: expected {}, was {}",
1661 event.event_id,
1662 position_id,
1663 event_position_id
1664 );
1665 }
1666
1667 let mut transaction = pool.begin().await?;
1668
1669 sqlx::query(r#"DELETE FROM "position_event" WHERE position_id = $1 AND trader_id = $2"#)
1672 .bind(position_id.to_string())
1673 .bind(event.trader_id.to_string())
1674 .execute(&mut *transaction)
1675 .await
1676 .map(|_| ())
1677 .map_err(|e| anyhow::anyhow!("Failed to delete position_event rows: {e}"))?;
1678
1679 Self::insert_position_event(&mut transaction, event).await?;
1680 transaction
1681 .commit()
1682 .await
1683 .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
1684 }
1685
1686 pub async fn update_position(pool: &PgPool, event: &OrderFilled) -> anyhow::Result<()> {
1692 let mut transaction = pool.begin().await?;
1693
1694 Self::insert_position_event(&mut transaction, event).await?;
1695 transaction
1696 .commit()
1697 .await
1698 .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
1699 }
1700
1701 pub async fn add_position_event(pool: &PgPool, event: &OrderFilled) -> anyhow::Result<()> {
1707 let mut transaction = pool.begin().await?;
1708
1709 Self::insert_position_event(&mut transaction, event).await?;
1710 transaction
1711 .commit()
1712 .await
1713 .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
1714 }
1715
1716 pub async fn load_position_events(
1722 pool: &PgPool,
1723 position_id: &PositionId,
1724 trader_id: &TraderId,
1725 ) -> anyhow::Result<Vec<OrderFilled>> {
1726 sqlx::query_as::<_, OrderFilledRow>(
1727 r#"
1728 SELECT *
1729 FROM "position_event"
1730 WHERE position_id = $1 AND trader_id = $2
1731 ORDER BY event_sequence ASC
1732 "#,
1733 )
1734 .bind(position_id.to_string())
1735 .bind(trader_id.to_string())
1736 .fetch_all(pool)
1737 .await
1738 .map(|rows| rows.into_iter().map(|row| row.0).collect())
1739 .map_err(|e| anyhow::anyhow!("Failed to load position events: {e}"))
1740 }
1741
1742 pub async fn load_position(
1748 pool: &PgPool,
1749 position_id: &PositionId,
1750 trader_id: &TraderId,
1751 ) -> anyhow::Result<Option<Position>> {
1752 if let Some(snapshot) = Self::load_position_snapshot(pool, position_id, trader_id).await?
1753 && let Some(replay_state) = snapshot.replay_state
1754 {
1755 let position: Position = serde_json::from_value(replay_state)
1756 .map_err(|e| anyhow::anyhow!("Failed to decode position replay state: {e}"))?;
1757 position.check_accounting()?;
1758 return Ok(Some(position));
1759 }
1760
1761 let fills = Self::load_position_events(pool, position_id, trader_id).await?;
1762 let Some((first_fill, remaining_fills)) = fills.split_first() else {
1763 return Ok(None);
1764 };
1765 let Some(instrument) = Self::load_instrument(pool, &first_fill.instrument_id).await? else {
1766 log::error!(
1767 "Instrument not found for position {position_id}: {}",
1768 first_fill.instrument_id
1769 );
1770 return Ok(None);
1771 };
1772
1773 let mut position = Position::new_checked(&instrument, first_fill.clone())?;
1774 for fill in remaining_fills {
1775 if position.trade_ids.contains(&fill.trade_id) {
1776 anyhow::bail!(
1777 "Duplicate fill event for position {position_id}: {}",
1778 fill.trade_id
1779 );
1780 }
1781
1782 position.try_apply(fill)?;
1783 }
1784
1785 Ok(Some(position))
1786 }
1787
1788 pub async fn load_positions(
1796 pool: &PgPool,
1797 trader_id: &TraderId,
1798 ) -> anyhow::Result<Vec<Position>> {
1799 let position_ids: Vec<PositionId> = sqlx::query(
1800 r#"
1801 SELECT DISTINCT position_id
1802 FROM "position_event"
1803 WHERE trader_id = $1
1804 ORDER BY position_id ASC
1805 "#,
1806 )
1807 .bind(trader_id.to_string())
1808 .fetch_all(pool)
1809 .await
1810 .map(|rows| {
1811 rows.into_iter()
1812 .map(|row| PositionId::from(row.get::<&str, _>(0)))
1813 .collect()
1814 })
1815 .map_err(|e| anyhow::anyhow!("Failed to load position ids: {e}"))?;
1816
1817 let mut positions = Vec::new();
1818
1819 for id in position_ids {
1820 match Self::load_position(pool, &id, trader_id).await {
1821 Ok(Some(position)) => positions.push(position),
1822 Ok(None) => log::error!("Position not found: {id}"),
1823 Err(e) => log::error!("Failed to load position {id}: {e}"),
1824 }
1825 }
1826
1827 Ok(positions)
1828 }
1829
1830 async fn insert_position_event(
1831 transaction: &mut Transaction<'_, Postgres>,
1832 event: &OrderFilled,
1833 ) -> anyhow::Result<()> {
1834 let position_id = Self::event_position_id(event)?;
1835
1836 sqlx::query(
1837 r#"
1838 INSERT INTO "trader" (id)
1839 VALUES ($1)
1840 ON CONFLICT (id) DO NOTHING
1841 "#,
1842 )
1843 .bind(event.trader_id.to_string())
1844 .execute(&mut **transaction)
1845 .await
1846 .map(|_| ())
1847 .map_err(|e| anyhow::anyhow!("Failed to insert into trader table: {e}"))?;
1848
1849 let position_event_info = event
1850 .info
1851 .clone()
1852 .map(serde_json::to_value)
1853 .transpose()
1854 .map_err(|e| anyhow::anyhow!("Failed to serialize fill info: {e}"))?;
1855
1856 sqlx::query(
1857 r#"
1858 INSERT INTO "position_event" (
1859 id, kind, trader_id, strategy_id, instrument_id, client_order_id, venue_order_id,
1860 account_id, trade_id, currency, order_type, order_side, last_px, last_qty,
1861 liquidity_side, position_id, commission, reconciliation, info, causation_id,
1862 ts_event, ts_init, created_at, updated_at
1863 ) VALUES (
1864 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17,
1865 $18, $19, $20, $21, $22, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
1866 )
1867 "#,
1868 )
1869 .bind(event.event_id.to_string())
1870 .bind("OrderFilled")
1871 .bind(event.trader_id.to_string())
1872 .bind(event.strategy_id.to_string())
1873 .bind(event.instrument_id.to_string())
1874 .bind(event.client_order_id.to_string())
1875 .bind(event.venue_order_id.to_string())
1876 .bind(event.account_id.to_string())
1877 .bind(event.trade_id.to_string())
1878 .bind(event.currency.code.as_str())
1879 .bind(event.order_type.to_string())
1880 .bind(event.order_side.to_string())
1881 .bind(event.last_px.to_string())
1882 .bind(event.last_qty.to_string())
1883 .bind(event.liquidity_side.to_string())
1884 .bind(position_id.to_string())
1885 .bind(event.commission.map(|commission| commission.to_string()))
1886 .bind(event.reconciliation)
1887 .bind(position_event_info)
1888 .bind(
1889 event
1890 .causation_id
1891 .map(|causation_id| causation_id.to_string()),
1892 )
1893 .bind(event.ts_event.to_string())
1894 .bind(event.ts_init.to_string())
1895 .execute(&mut **transaction)
1896 .await
1897 .map(|_| ())
1898 .map_err(|e| anyhow::anyhow!("Failed to insert into position_event table: {e}"))
1899 }
1900
1901 fn event_position_id(event: &OrderFilled) -> anyhow::Result<PositionId> {
1902 event.position_id.ok_or_else(|| {
1903 anyhow::anyhow!(
1904 "Cannot persist position event with no position_id: {}",
1905 event.event_id
1906 )
1907 })
1908 }
1909
1910 pub async fn add_account(
1919 pool: &PgPool,
1920 updated: bool,
1921 account_event: AccountState,
1922 trader_id: &TraderId,
1923 ) -> anyhow::Result<()> {
1924 if updated {
1925 let exists =
1926 Self::check_if_account_event_exists(pool, account_event.account_id, trader_id)
1927 .await?;
1928
1929 if !exists {
1930 anyhow::bail!(
1931 "Account event does not exist for account: {}",
1932 account_event.account_id
1933 );
1934 }
1935 }
1936
1937 let mut transaction = pool.begin().await?;
1938 let event = serde_json::to_value(&account_event)
1939 .map_err(|e| anyhow::anyhow!("Failed to serialize account event: {e}"))?;
1940 let balances = event
1941 .get("balances")
1942 .cloned()
1943 .ok_or_else(|| anyhow::anyhow!("Serialized account event has no balances"))?;
1944 let margins = event
1945 .get("margins")
1946 .cloned()
1947 .ok_or_else(|| anyhow::anyhow!("Serialized account event has no margins"))?;
1948
1949 sqlx::query(
1950 r#"
1951 INSERT INTO "trader" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
1952 "#,
1953 )
1954 .bind(trader_id.to_string())
1955 .execute(&mut *transaction)
1956 .await
1957 .map(|_| ())
1958 .map_err(|e| anyhow::anyhow!("Failed to insert into trader table: {e}"))?;
1959
1960 sqlx::query(
1961 r#"
1962 INSERT INTO "account" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
1963 "#,
1964 )
1965 .bind(account_event.account_id.to_string())
1966 .execute(&mut *transaction)
1967 .await
1968 .map(|_| ())
1969 .map_err(|e| anyhow::anyhow!("Failed to insert into account table: {e}"))?;
1970
1971 let inserted = sqlx::query(r#"
1972 INSERT INTO "account_event" (
1973 id, kind, account_id, trader_id, base_currency, balances, margins, is_reported, ts_event, ts_init, created_at, updated_at
1974 ) VALUES (
1975 $1, $2, $3, $10, $4, $5, $6, $7, $8, $9, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
1976 )
1977 ON CONFLICT (id)
1978 DO UPDATE
1979 SET
1980 kind = $2, account_id = $3,
1981 base_currency = $4, balances = $5, margins = $6, is_reported = $7,
1982 ts_event = $8, ts_init = $9, updated_at = CURRENT_TIMESTAMP
1983 WHERE "account_event".trader_id = $10
1984 "#)
1985 .bind(account_event.event_id.to_string())
1986 .bind(account_event.account_type.to_string())
1987 .bind(account_event.account_id.to_string())
1988 .bind(account_event.base_currency.map(|x| x.code.as_str()))
1989 .bind(balances)
1990 .bind(margins)
1991 .bind(account_event.is_reported)
1992 .bind(account_event.ts_event.to_string())
1993 .bind(account_event.ts_init.to_string())
1994 .bind(trader_id.to_string())
1995 .execute(&mut *transaction)
1996 .await
1997 .map_err(|e| anyhow::anyhow!("Failed to insert into account_event table: {e}"))?;
1998
1999 if inserted.rows_affected() == 0 {
2002 anyhow::bail!(
2003 "Account event {} for {} is already persisted for another trader, not {trader_id}",
2004 account_event.event_id,
2005 account_event.account_id
2006 );
2007 }
2008
2009 transaction
2010 .commit()
2011 .await
2012 .map_err(|e| anyhow::anyhow!("Failed to commit add_account transaction: {e}"))
2013 }
2014
2015 pub async fn load_account_events(
2021 pool: &PgPool,
2022 account_id: &AccountId,
2023 trader_id: &TraderId,
2024 ) -> anyhow::Result<Vec<AccountState>> {
2025 sqlx::query_as::<_, AccountEventRow>(
2026 r#"
2027 SELECT * FROM "account_event"
2028 WHERE account_id = $1 AND trader_id = $2
2029 ORDER BY created_at ASC
2030 "#,
2031 )
2032 .bind(account_id.to_string())
2033 .bind(trader_id.to_string())
2034 .fetch_all(pool)
2035 .await
2036 .map(|rows| rows.into_iter().map(|row| row.0).collect())
2037 .map_err(|e| anyhow::anyhow!("Failed to load account events: {e}"))
2038 }
2039
2040 pub async fn load_account(
2046 pool: &PgPool,
2047 account_id: &AccountId,
2048 trader_id: &TraderId,
2049 ) -> anyhow::Result<Option<AccountAny>> {
2050 let account_events = Self::load_account_events(pool, account_id, trader_id).await;
2051 match account_events {
2052 Ok(account_events) => {
2053 if account_events.is_empty() {
2054 return Ok(None);
2055 }
2056 let account = AccountAny::from_events(&account_events).map_err(|e| {
2057 anyhow::anyhow!("Failed to assemble account {account_id} from events: {e}")
2058 })?;
2059 Ok(Some(account))
2060 }
2061 Err(e) => anyhow::bail!("Failed to load account events: {e}"),
2062 }
2063 }
2064
2065 pub async fn load_accounts(
2073 pool: &PgPool,
2074 trader_id: &TraderId,
2075 ) -> anyhow::Result<Vec<AccountAny>> {
2076 let mut accounts: Vec<AccountAny> = Vec::new();
2077 let account_ids: Vec<AccountId> = sqlx::query(
2078 r#"
2079 SELECT DISTINCT account_id FROM "account_event"
2080 WHERE trader_id = $1
2081 "#,
2082 )
2083 .bind(trader_id.to_string())
2084 .fetch_all(pool)
2085 .await
2086 .map(|rows| {
2087 rows.into_iter()
2088 .map(|row| AccountId::from(row.get::<&str, _>(0)))
2089 .collect()
2090 })
2091 .map_err(|e| anyhow::anyhow!("Failed to load account ids: {e}"))?;
2092 for id in account_ids {
2093 let account = Self::load_account(pool, &id, trader_id).await?;
2094 if let Some(account) = account {
2095 accounts.push(account);
2096 }
2097 }
2098 Ok(accounts)
2099 }
2100
2101 pub async fn load_unassigned_account_ids(pool: &PgPool) -> anyhow::Result<Vec<AccountId>> {
2110 sqlx::query_scalar::<_, String>(
2111 r#"
2112 SELECT DISTINCT account_id FROM "account_event"
2113 WHERE trader_id IS NULL AND account_id IS NOT NULL
2114 ORDER BY account_id
2115 "#,
2116 )
2117 .fetch_all(pool)
2118 .await
2119 .map(|ids| ids.iter().map(|id| AccountId::from(id.as_str())).collect())
2120 .map_err(|e| anyhow::anyhow!("Failed to load unassigned account ids: {e}"))
2121 }
2122
2123 pub async fn assign_account_trader(
2132 pool: &PgPool,
2133 account_id: &AccountId,
2134 trader_id: &TraderId,
2135 ) -> anyhow::Result<u64> {
2136 let mut transaction = pool.begin().await?;
2137
2138 let has_events: bool = sqlx::query_scalar(
2139 r#"SELECT EXISTS(SELECT 1 FROM "account_event" WHERE account_id = $1)"#,
2140 )
2141 .bind(account_id.to_string())
2142 .fetch_one(&mut *transaction)
2143 .await
2144 .map_err(|e| anyhow::anyhow!("Failed to check account events for {account_id}: {e}"))?;
2145
2146 if !has_events {
2147 anyhow::bail!("No account events found for {account_id}");
2148 }
2149
2150 sqlx::query(
2151 r#"
2152 INSERT INTO "trader" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
2153 "#,
2154 )
2155 .bind(trader_id.to_string())
2156 .execute(&mut *transaction)
2157 .await
2158 .map(|_| ())
2159 .map_err(|e| anyhow::anyhow!("Failed to insert into trader table: {e}"))?;
2160
2161 let assigned = sqlx::query(
2162 r#"
2163 UPDATE "account_event"
2164 SET trader_id = $2, updated_at = CURRENT_TIMESTAMP
2165 WHERE account_id = $1 AND trader_id IS NULL
2166 "#,
2167 )
2168 .bind(account_id.to_string())
2169 .bind(trader_id.to_string())
2170 .execute(&mut *transaction)
2171 .await
2172 .map_err(|e| anyhow::anyhow!("Failed to assign {account_id} to {trader_id}: {e}"))?
2173 .rows_affected();
2174
2175 transaction
2176 .commit()
2177 .await
2178 .map_err(|e| anyhow::anyhow!("Failed to commit account assignment: {e}"))?;
2179
2180 Ok(assigned)
2181 }
2182
2183 pub async fn add_trade(pool: &PgPool, trade: &TradeTick) -> anyhow::Result<()> {
2189 sqlx::query(r#"
2190 INSERT INTO "trade" (
2191 instrument_id, price, quantity, aggressor_side, venue_trade_id,
2192 ts_event, ts_init, created_at, updated_at
2193 ) VALUES (
2194 $1, $2, $3, $4::aggressor_side, $5, $6, $7, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
2195 )
2196 ON CONFLICT (id)
2197 DO UPDATE
2198 SET
2199 instrument_id = $1, price = $2, quantity = $3, aggressor_side = $4, venue_trade_id = $5,
2200 ts_event = $6, ts_init = $7, updated_at = CURRENT_TIMESTAMP
2201 "#)
2202 .bind(trade.instrument_id.to_string())
2203 .bind(trade.price.to_string())
2204 .bind(trade.size.to_string())
2205 .bind(AggressorSidePg(trade.aggressor_side))
2206 .bind(trade.trade_id.to_string())
2207 .bind(trade.ts_event.to_string())
2208 .bind(trade.ts_init.to_string())
2209 .execute(pool)
2210 .await
2211 .map(|_| ())
2212 .map_err(|e| anyhow::anyhow!("Failed to insert into trade table: {e}"))
2213 }
2214
2215 pub async fn load_trades(
2221 pool: &PgPool,
2222 instrument_id: &InstrumentId,
2223 ) -> anyhow::Result<Vec<TradeTick>> {
2224 sqlx::query_as::<_, TradeTickRow>(
2225 r#"SELECT * FROM "trade" WHERE instrument_id = $1 ORDER BY ts_event ASC"#,
2226 )
2227 .bind(instrument_id.to_string())
2228 .fetch_all(pool)
2229 .await
2230 .map(|rows| rows.into_iter().map(|row| row.0).collect())
2231 .map_err(|e| anyhow::anyhow!("Failed to load trades: {e}"))
2232 }
2233
2234 pub async fn add_quote(pool: &PgPool, quote: &QuoteTick) -> anyhow::Result<()> {
2240 sqlx::query(r#"
2241 INSERT INTO "quote" (
2242 instrument_id, bid_price, ask_price, bid_size, ask_size, ts_event, ts_init, created_at, updated_at
2243 ) VALUES (
2244 $1, $2, $3, $4, $5, $6, $7, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
2245 )
2246 ON CONFLICT (id)
2247 DO UPDATE
2248 SET
2249 instrument_id = $1, bid_price = $2, ask_price = $3, bid_size = $4, ask_size = $5,
2250 ts_event = $6, ts_init = $7, updated_at = CURRENT_TIMESTAMP
2251 "#)
2252 .bind(quote.instrument_id.to_string())
2253 .bind(quote.bid_price.to_string())
2254 .bind(quote.ask_price.to_string())
2255 .bind(quote.bid_size.to_string())
2256 .bind(quote.ask_size.to_string())
2257 .bind(quote.ts_event.to_string())
2258 .bind(quote.ts_init.to_string())
2259 .execute(pool)
2260 .await
2261 .map(|_| ())
2262 .map_err(|e| anyhow::anyhow!("Failed to insert into quote table: {e}"))
2263 }
2264
2265 pub async fn load_quotes(
2271 pool: &PgPool,
2272 instrument_id: &InstrumentId,
2273 ) -> anyhow::Result<Vec<QuoteTick>> {
2274 sqlx::query_as::<_, QuoteTickRow>(
2275 r#"SELECT * FROM "quote" WHERE instrument_id = $1 ORDER BY ts_event ASC"#,
2276 )
2277 .bind(instrument_id.to_string())
2278 .fetch_all(pool)
2279 .await
2280 .map(|rows| rows.into_iter().map(|row| row.0).collect())
2281 .map_err(|e| anyhow::anyhow!("Failed to load quotes: {e}"))
2282 }
2283
2284 pub async fn add_bar(pool: &PgPool, bar: &Bar) -> anyhow::Result<()> {
2290 if bar.bar_type.is_composite() {
2291 anyhow::bail!(
2292 "Cannot persist bar with composite bar type {}: the bar table stores only \
2293 the standard form; standardize the bar type before persisting",
2294 bar.bar_type,
2295 );
2296 }
2297
2298 let bar_step = i32::try_from(bar.bar_type.spec().step.get())
2299 .map_err(|e| anyhow::anyhow!("invalid bar step: {e}"))?;
2300
2301 sqlx::query(r#"
2302 INSERT INTO "bar" (
2303 instrument_id, step, bar_aggregation, price_type, aggregation_source, open, high, low, close, volume, ts_event, ts_init, created_at, updated_at
2304 ) VALUES (
2305 $1, $2, $3::bar_aggregation, $4::price_type, $5::aggregation_source, $6, $7, $8, $9, $10, $11, $12, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
2306 )
2307 ON CONFLICT (id)
2308 DO UPDATE
2309 SET
2310 instrument_id = $1, step = $2, bar_aggregation = $3::bar_aggregation, price_type = $4::price_type, aggregation_source = $5::aggregation_source,
2311 open = $6, high = $7, low = $8, close = $9, volume = $10, ts_event = $11, ts_init = $12, updated_at = CURRENT_TIMESTAMP
2312 "#)
2313 .bind(bar.bar_type.instrument_id().to_string())
2314 .bind(bar_step)
2315 .bind(BarAggregationPg(bar.bar_type.spec().aggregation))
2316 .bind(PriceTypePg(bar.bar_type.spec().price_type))
2317 .bind(AggregationSourcePg(bar.bar_type.aggregation_source()))
2318 .bind(bar.open.to_string())
2319 .bind(bar.high.to_string())
2320 .bind(bar.low.to_string())
2321 .bind(bar.close.to_string())
2322 .bind(bar.volume.to_string())
2323 .bind(bar.ts_event.to_string())
2324 .bind(bar.ts_init.to_string())
2325 .execute(pool)
2326 .await
2327 .map(|_| ())
2328 .map_err(|e| anyhow::anyhow!("Failed to insert into bar table: {e}"))
2329 }
2330
2331 pub async fn load_bars(
2337 pool: &PgPool,
2338 instrument_id: &InstrumentId,
2339 ) -> anyhow::Result<Vec<Bar>> {
2340 sqlx::query_as::<_, BarRow>(
2341 r#"SELECT * FROM "bar" WHERE instrument_id = $1 ORDER BY ts_event ASC"#,
2342 )
2343 .bind(instrument_id.to_string())
2344 .fetch_all(pool)
2345 .await
2346 .map(|rows| rows.into_iter().map(|row| row.0).collect())
2347 .map_err(|e| anyhow::anyhow!("Failed to load bars: {e}"))
2348 }
2349
2350 pub async fn load_distinct_order_event_client_ids(
2358 pool: &PgPool,
2359 trader_id: &TraderId,
2360 ) -> anyhow::Result<AHashMap<ClientOrderId, ClientId>> {
2361 let mut map: AHashMap<ClientOrderId, ClientId> = AHashMap::new();
2362 let result = sqlx::query_as::<_, OrderEventOrderClientIdCombination>(
2363 r#"
2364 SELECT DISTINCT ON (client_order_id)
2365 client_order_id AS "client_order_id",
2366 client_id AS "client_id"
2367 FROM "order_event"
2368 WHERE client_id IS NOT NULL AND trader_id = $1
2369 ORDER BY client_order_id, created_at DESC
2370 "#,
2371 )
2372 .bind(trader_id.to_string())
2373 .fetch_all(pool)
2374 .await
2375 .map_err(|e| anyhow::anyhow!("Failed to load account ids: {e}"))?;
2376
2377 for id in result {
2378 map.insert(id.client_order_id, id.client_id);
2379 }
2380 Ok(map)
2381 }
2382
2383 pub async fn index_order_clients(
2390 pool: &PgPool,
2391 trader_id: &TraderId,
2392 claims: &[(ClientOrderId, ClientId)],
2393 ) -> anyhow::Result<()> {
2394 if claims.is_empty() {
2395 return Ok(());
2396 }
2397
2398 let mut transaction = pool.begin().await?;
2399
2400 for (client_order_id, client_id) in claims {
2401 let conflicting_client_id = sqlx::query_scalar::<_, String>(
2402 r#"
2403 SELECT client_id
2404 FROM "order_event"
2405 WHERE client_order_id = $1
2406 AND trader_id = $3
2407 AND client_id IS NOT NULL
2408 AND client_id <> $2
2409 LIMIT 1
2410 "#,
2411 )
2412 .bind(client_order_id.to_string())
2413 .bind(client_id.to_string())
2414 .bind(trader_id.to_string())
2415 .fetch_optional(&mut *transaction)
2416 .await
2417 .map_err(|e| anyhow::anyhow!("Failed to validate order client origin: {e}"))?;
2418
2419 if let Some(conflicting_client_id) = conflicting_client_id {
2420 anyhow::bail!(
2421 "Order {client_order_id} is already claimed by execution client \
2422 {conflicting_client_id} and cannot be claimed by {client_id}"
2423 );
2424 }
2425
2426 sqlx::query(
2427 r#"
2428 INSERT INTO "client" (id)
2429 VALUES ($1)
2430 ON CONFLICT (id) DO NOTHING
2431 "#,
2432 )
2433 .bind(client_id.to_string())
2434 .execute(&mut *transaction)
2435 .await
2436 .map_err(|e| anyhow::anyhow!("Failed to persist execution client {client_id}: {e}"))?;
2437
2438 let result = sqlx::query(
2439 r#"
2440 UPDATE "order_event"
2441 SET client_id = $2
2442 WHERE client_order_id = $1
2443 AND trader_id = $3
2444 AND (client_id IS NULL OR client_id = $2)
2445 "#,
2446 )
2447 .bind(client_order_id.to_string())
2448 .bind(client_id.to_string())
2449 .bind(trader_id.to_string())
2450 .execute(&mut *transaction)
2451 .await
2452 .map_err(|e| anyhow::anyhow!("Failed to index order client origin: {e}"))?;
2453
2454 if result.rows_affected() == 0 {
2455 anyhow::bail!("No persisted order events found for {client_order_id}");
2456 }
2457 }
2458
2459 transaction
2460 .commit()
2461 .await
2462 .map_err(|e| anyhow::anyhow!("Failed to commit order client origins: {e}"))
2463 }
2464
2465 pub async fn index_order_position(
2471 pool: &PgPool,
2472 trader_id: &TraderId,
2473 client_order_id: ClientOrderId,
2474 position_id: PositionId,
2475 ) -> anyhow::Result<()> {
2476 let mut transaction = pool.begin().await?;
2477
2478 sqlx::query(
2479 r#"
2480 INSERT INTO "trader" (id) VALUES ($1) ON CONFLICT (id) DO NOTHING
2481 "#,
2482 )
2483 .bind(trader_id.to_string())
2484 .execute(&mut *transaction)
2485 .await
2486 .map(|_| ())
2487 .map_err(|e| anyhow::anyhow!("Failed to insert into trader table: {e}"))?;
2488
2489 sqlx::query(
2490 r#"
2491 INSERT INTO "order_position_index" (
2492 trader_id, client_order_id, position_id, created_at, updated_at
2493 ) VALUES (
2494 $1, $2, $3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
2495 )
2496 ON CONFLICT (trader_id, client_order_id)
2497 DO UPDATE
2498 SET
2499 position_id = $3, updated_at = CURRENT_TIMESTAMP
2500 "#,
2501 )
2502 .bind(trader_id.to_string())
2503 .bind(client_order_id.to_string())
2504 .bind(position_id.to_string())
2505 .execute(&mut *transaction)
2506 .await
2507 .map(|_| ())
2508 .map_err(|e| anyhow::anyhow!("Failed to insert into order_position_index table: {e}"))?;
2509
2510 transaction
2511 .commit()
2512 .await
2513 .map_err(|e| anyhow::anyhow!("Failed to commit order position index: {e}"))
2514 }
2515
2516 pub async fn load_index_order_position(
2524 pool: &PgPool,
2525 trader_id: &TraderId,
2526 ) -> anyhow::Result<AHashMap<ClientOrderId, PositionId>> {
2527 let mut map: AHashMap<ClientOrderId, PositionId> = AHashMap::new();
2528 let result = sqlx::query_as::<_, OrderPositionIndexRow>(
2529 r#"
2530 SELECT
2531 client_order_id AS "client_order_id",
2532 position_id AS "position_id"
2533 FROM "order_position_index"
2534 WHERE trader_id = $1
2535 "#,
2536 )
2537 .bind(trader_id.to_string())
2538 .fetch_all(pool)
2539 .await
2540 .map_err(|e| anyhow::anyhow!("Failed to load order position index: {e}"))?;
2541
2542 for row in result {
2543 map.insert(row.client_order_id, row.position_id);
2544 }
2545 Ok(map)
2546 }
2547
2548 pub async fn add_signal(pool: &PgPool, signal: &Signal) -> anyhow::Result<()> {
2554 sqlx::query(
2555 r#"
2556 INSERT INTO "signal" (
2557 name, value, ts_event, ts_init, created_at, updated_at
2558 ) VALUES (
2559 $1, $2, $3, $4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
2560 )
2561 ON CONFLICT (id)
2562 DO UPDATE
2563 SET
2564 name = $1, value = $2, ts_event = $3, ts_init = $4,
2565 updated_at = CURRENT_TIMESTAMP
2566 "#,
2567 )
2568 .bind(signal.name.to_string())
2569 .bind(signal.value.clone())
2570 .bind(signal.ts_event.to_string())
2571 .bind(signal.ts_init.to_string())
2572 .execute(pool)
2573 .await
2574 .map(|_| ())
2575 .map_err(|e| anyhow::anyhow!("Failed to insert into signal table: {e}"))
2576 }
2577
2578 pub async fn load_signals(pool: &PgPool, name: &str) -> anyhow::Result<Vec<Signal>> {
2584 sqlx::query_as::<_, SignalRow>(
2585 r#"SELECT * FROM "signal" WHERE name = $1 ORDER BY ts_init ASC"#,
2586 )
2587 .bind(name)
2588 .fetch_all(pool)
2589 .await
2590 .map(|rows| rows.into_iter().map(|row| row.0).collect())
2591 .map_err(|e| anyhow::anyhow!("Failed to load signals: {e}"))
2592 }
2593
2594 pub async fn add_custom_data(pool: &PgPool, data: &CustomData) -> anyhow::Result<()> {
2602 let json_bytes = serde_json::to_vec(data)
2603 .map_err(|e| anyhow::anyhow!("CustomData must be valid JSON: {e}"))?;
2604 let value_json: serde_json::Value = serde_json::from_slice(&json_bytes)
2605 .map_err(|e| anyhow::anyhow!("CustomData value must be valid JSON: {e}"))?;
2606 let data_type_obj = value_json
2607 .get("data_type")
2608 .and_then(|v| v.as_object())
2609 .ok_or_else(|| anyhow::anyhow!("CustomData JSON missing data_type"))?;
2610 let data_type_name = data_type_obj
2611 .get("type_name")
2612 .and_then(|v| v.as_str())
2613 .unwrap_or("");
2614 let metadata_json = data_type_obj
2615 .get("metadata")
2616 .cloned()
2617 .unwrap_or_else(|| serde_json::Value::Object(serde_json::Map::new()));
2618 let identifier = data_type_obj
2619 .get("identifier")
2620 .and_then(|v| v.as_str())
2621 .unwrap_or("");
2622 sqlx::query(
2623 r#"
2624 INSERT INTO "custom" (data_type, metadata, identifier, value, ts_event, ts_init, created_at, updated_at)
2625 VALUES ($1, $2, $3, $4, $5, $6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
2626 ON CONFLICT (id)
2627 DO UPDATE SET
2628 data_type = EXCLUDED.data_type,
2629 metadata = EXCLUDED.metadata,
2630 identifier = EXCLUDED.identifier,
2631 value = EXCLUDED.value,
2632 ts_event = EXCLUDED.ts_event,
2633 ts_init = EXCLUDED.ts_init,
2634 updated_at = CURRENT_TIMESTAMP
2635 "#,
2636 )
2637 .bind(data_type_name)
2638 .bind(&metadata_json)
2639 .bind(identifier)
2640 .bind(&value_json)
2641 .bind(
2642 value_json
2643 .get("ts_event")
2644 .and_then(serde_json::Value::as_u64)
2645 .unwrap_or_else(|| data.ts_init().as_u64())
2646 .to_string(),
2647 )
2648 .bind(data.ts_init().to_string())
2649 .execute(pool)
2650 .await
2651 .map(|_| ())
2652 .map_err(|e| anyhow::anyhow!("Failed to insert into custom table: {e}"))
2653 }
2654
2655 pub async fn load_custom_data(
2663 pool: &PgPool,
2664 data_type: &DataType,
2665 ) -> anyhow::Result<Vec<CustomData>> {
2666 let metadata_json = data_type.metadata().as_ref().map_or(
2667 Ok(serde_json::Value::Object(serde_json::Map::new())),
2668 serde_json::to_value,
2669 )?;
2670
2671 let type_name = data_type.type_name();
2672 let short_type = type_name.rsplit([':', '.']).next().unwrap_or(type_name);
2673
2674 let rows = match data_type.identifier() {
2675 Some(identifier) => {
2676 sqlx::query(
2677 r#"SELECT value, ts_event, ts_init FROM "custom"
2678 WHERE (data_type = $1 OR data_type = $2)
2679 AND metadata = $3
2680 AND identifier = $4
2681 ORDER BY ts_init ASC"#,
2682 )
2683 .bind(type_name)
2684 .bind(short_type)
2685 .bind(&metadata_json)
2686 .bind(identifier)
2687 .fetch_all(pool)
2688 .await
2689 }
2690 None => {
2691 sqlx::query(
2692 r#"SELECT value, ts_event, ts_init FROM "custom"
2693 WHERE (data_type = $1 OR data_type = $2)
2694 AND metadata = $3
2695 AND identifier = ''
2696 ORDER BY ts_init ASC"#,
2697 )
2698 .bind(type_name)
2699 .bind(short_type)
2700 .bind(&metadata_json)
2701 .fetch_all(pool)
2702 .await
2703 }
2704 }
2705 .map_err(|e| anyhow::anyhow!("Failed to load custom data: {e}"))?;
2706
2707 let mut results = Vec::with_capacity(rows.len());
2708 for row in rows {
2709 let value_json: serde_json::Value = row.try_get("value")?;
2710 let json_bytes = serde_json::to_vec(&value_json)
2711 .map_err(|e| anyhow::anyhow!("Failed to serialize JSON: {e}"))?;
2712 let custom =
2713 CustomData::from_json_bytes(&json_bytes).map_err(|e| anyhow::anyhow!("{e}"))?;
2714 results.push(custom);
2715 }
2716 Ok(results)
2717 }
2718}
2719
2720pub(super) fn trader_key_prefix(trader_id: TraderId) -> String {
2721 format!(
2722 "{CACHE_DATABASE_KEY_PREFIX}trader:{}:{trader_id}:",
2723 trader_id.as_str().len()
2724 )
2725}
2726
2727#[cfg(all(test, feature = "defi"))]
2728mod tests {
2729 use nautilus_model::instruments::stubs::audusd_sim;
2730 use sqlx::{Error, postgres::PgPoolOptions};
2731
2732 use super::DatabaseQueries;
2733 use crate::defi::IntegrityError;
2734
2735 #[tokio::test]
2736 async fn test_token_write_closed_pool_preserves_database_error() {
2737 let pool = PgPoolOptions::new()
2738 .connect_lazy("postgres://localhost/unused")
2739 .unwrap();
2740 pool.close().await;
2741 let error = DatabaseQueries::write_token_instrument(
2742 &pool,
2743 "CurrencyPair",
2744 Box::new(audusd_sim()),
2745 &[],
2746 None,
2747 )
2748 .await
2749 .unwrap_err();
2750 assert!(!error.is::<IntegrityError>());
2751 assert!(matches!(
2752 error.downcast_ref::<Error>(),
2753 Some(Error::PoolClosed)
2754 ));
2755 }
2756}