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