Skip to main content

nautilus_infrastructure/sql/
queries.rs

1// -------------------------------------------------------------------------------------------------
2//  Copyright (C) 2015-2026 Nautech Systems Pty Ltd. All rights reserved.
3//  https://nautechsystems.io
4//
5//  Licensed under the GNU Lesser General Public License Version 3.0 (the "License");
6//  You may not use this file except in compliance with the License.
7//  You may obtain a copy of the License at https://www.gnu.org/licenses/lgpl-3.0.en.html
8//
9//  Unless required by applicable law or agreed to in writing, software
10//  distributed under the License is distributed on an "AS IS" BASIS,
11//  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12//  See the License for the specific language governing permissions and
13//  limitations under the License.
14// -------------------------------------------------------------------------------------------------
15
16use 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    /// Truncates all tables in the cache database via the provided Postgres `pool`.
72    ///
73    /// # Errors
74    ///
75    /// Returns an error if the TRUNCATE operation fails.
76    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    /// Deletes the order, position and account rows of `trader_id` via the provided `pool`.
85    ///
86    /// Shared reference data (currencies, instruments, instrument closes), market data, the
87    /// user general entries, signals, custom data, and blockchain tables are left untouched.
88    /// This trader's saved component state and heartbeat are removed; shared synthetics remain.
89    ///
90    /// # Errors
91    ///
92    /// Returns an error if any DELETE operation fails. Any error rolls back the complete flush.
93    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    /// Inserts or replaces a raw key-value entry in the `general` table via the provided `pool`.
125    ///
126    /// # Errors
127    ///
128    /// Returns an error if the INSERT or UPDATE operation fails.
129    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    /// Deletes a raw key-value entry.
143    ///
144    /// # Errors
145    ///
146    /// Returns an error if the database operation fails.
147    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    /// Deletes an order's events, snapshot, and order-position mapping for one trader.
156    /// Position fill histories remain available independently of the order.
157    ///
158    /// # Errors
159    ///
160    /// Returns an error if any database operation fails. A failure rolls back the deletion.
161    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    /// Deletes a position's fill history, snapshot, and order-position mappings for one trader.
185    /// Order event histories remain available independently of the position.
186    ///
187    /// # Errors
188    ///
189    /// Returns an error if any database operation fails. A failure rolls back the deletion.
190    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    /// Loads all entries from the `general` table via the provided `pool`.
234    ///
235    /// # Errors
236    ///
237    /// Returns an error if the SELECT operation fails.
238    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    /// Inserts or ignores a `Currency` row via the provided `pool`.
253    ///
254    /// # Errors
255    ///
256    /// Returns an error if the INSERT operation fails.
257    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    /// Loads all `Currency` entries via the provided `pool`.
313    ///
314    /// # Errors
315    ///
316    /// Returns an error if the SELECT operation fails.
317    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    /// Loads a single `Currency` entry by `code` via the provided `pool`.
336    ///
337    /// # Errors
338    ///
339    /// Returns an error if the SELECT operation fails.
340    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    /// Inserts or updates an `InstrumentAny` entry via the provided `pool`.
362    ///
363    /// # Errors
364    ///
365    /// Returns an error if the INSERT or UPDATE operation fails.
366    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        // Serialize changes to one instrument before checking its previous currency identities
804        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    /// Loads a single `InstrumentAny` entry by `instrument_id` via the provided `pool`.
883    ///
884    /// # Errors
885    ///
886    /// Returns an error if the SELECT operation fails.
887    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    /// Loads all `InstrumentAny` entries via the provided `pool`.
913    ///
914    /// # Errors
915    ///
916    /// Returns an error if the SELECT operation fails.
917    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    /// Inserts or replaces an `InstrumentClose`.
938    ///
939    /// # Errors
940    ///
941    /// Returns an error if the SQL INSERT or UPDATE fails.
942    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    /// Loads all `InstrumentClose` entries.
970    ///
971    /// # Errors
972    ///
973    /// Returns an error if the SQL SELECT or row decoding fails.
974    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    /// Inserts an `OrderInitialized` event via the provided `pool`.
1055    ///
1056    /// # Errors
1057    ///
1058    /// Returns an error if the SQL INSERT or UPDATE operation fails.
1059    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    /// Inserts an `OrderSnapshot` entry via the provided `pool`.
1068    ///
1069    /// # Errors
1070    ///
1071    /// Returns an error if the SQL INSERT operation fails.
1072    ///
1073    /// # Panics
1074    ///
1075    /// Panics if serialization of `snapshot.exec_algorithm_params` fails.
1076    #[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        // Insert trader if it does not exist
1084        // TODO remove this when node and trader initialization is implemented
1085        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())  // Used for both id and client_order_id
1159            .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    /// Loads an `OrderSnapshot` entry by client order ID via the provided `pool`.
1224    ///
1225    /// # Errors
1226    ///
1227    /// Returns an error if the SQL SELECT or deserialization fails.
1228    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    /// Inserts or updates a `PositionSnapshot` entry via the provided `pool`.
1245    ///
1246    /// # Errors
1247    ///
1248    /// Returns an error if the SQL INSERT or UPDATE operation fails, or if beginning the transaction fails.
1249    pub async fn add_position_snapshot(
1250        pool: &PgPool,
1251        snapshot: PositionSnapshot,
1252    ) -> anyhow::Result<()> {
1253        let mut transaction = pool.begin().await?;
1254
1255        // Insert trader if it does not exist
1256        // TODO remove this when node and trader initialization is implemented
1257        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    /// Loads a `PositionSnapshot` entry by `position_id` via the provided `pool`.
1323    ///
1324    /// # Errors
1325    ///
1326    /// Returns an error if the SQL SELECT or deserialization fails.
1327    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    /// Checks if an `OrderInitialized` event exists for the given `client_order_id` via the provided `pool`.
1344    ///
1345    /// # Errors
1346    ///
1347    /// Returns an error if the SQL SELECT operation fails.
1348    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    /// Checks if any account event exists for the given `account_id` via the provided `pool`.
1370    ///
1371    /// # Errors
1372    ///
1373    /// Returns an error if the SQL SELECT operation fails.
1374    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    /// Inserts or updates an order event entry via the provided `pool`.
1396    ///
1397    /// An event already persisted for another trader is left unchanged.
1398    ///
1399    /// # Errors
1400    ///
1401    /// Returns an error if the SQL INSERT or UPDATE operation fails, if serialization of
1402    /// `exec_algorithm_params` fails, or if the event is already persisted for another trader.
1403    #[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        // Insert trader if it does not exist
1415        // TODO remove this when node and trader initialization is implemented
1416        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        // Insert client if it does not exist
1428        // TODO remove this when client initialization is implemented
1429        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        // Event IDs are shared when two traders persist the same event, and the existing row
1548        // belongs to its first writer
1549        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    /// Loads all order events for a `client_order_id` via the provided `pool`.
1564    ///
1565    /// # Errors
1566    ///
1567    /// Returns an error if the SQL SELECT or deserialization fails.
1568    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    /// Loads and assembles a complete `OrderAny` for a `client_order_id` via the provided `pool`.
1589    ///
1590    /// # Errors
1591    ///
1592    /// Returns an error if assembling events or SQL operations fail.
1593    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    /// Loads and assembles all `OrderAny` entries via the provided `pool`.
1615    ///
1616    /// Only orders written by `trader_id` are loaded.
1617    ///
1618    /// # Errors
1619    ///
1620    /// Returns an error if loading events or SQL operations fail.
1621    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    /// Replaces the fill event log for a `position_id` via the provided `pool`.
1648    ///
1649    /// # Errors
1650    ///
1651    /// Returns an error if the fill is invalid or if the SQL operations fail.
1652    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        // Position IDs are only unique per trader (NETTING IDs are `{instrument_id}-{strategy_id}`),
1670        // so the replaced event log is the fill trader's alone
1671        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    /// Appends a fill event for a `Position` via the provided `pool`.
1687    ///
1688    /// # Errors
1689    ///
1690    /// Returns an error if the fill is invalid or if the SQL operations fail.
1691    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    /// Appends an `OrderFilled` event to the position event log via the provided `pool`.
1702    ///
1703    /// # Errors
1704    ///
1705    /// Returns an error if the SQL INSERT operation fails.
1706    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    /// Loads all fill events for a `position_id` via the provided `pool`.
1717    ///
1718    /// # Errors
1719    ///
1720    /// Returns an error if the SQL SELECT or deserialization fails.
1721    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    /// Loads and replays a complete `Position` for a `position_id` via the provided `pool`.
1743    ///
1744    /// # Errors
1745    ///
1746    /// Returns an error if loading events, loading instruments, or replaying fills fails.
1747    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    /// Loads and replays all `Position` entries via the provided `pool`.
1789    ///
1790    /// Only positions written by `trader_id` are loaded.
1791    ///
1792    /// # Errors
1793    ///
1794    /// Returns an error if loading position IDs or replaying any position fails.
1795    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    /// Inserts or updates an `AccountState` event for `trader_id` via the provided `pool`.
1911    ///
1912    /// An event already persisted for another trader, or awaiting assignment, is left unchanged.
1913    ///
1914    /// # Errors
1915    ///
1916    /// Returns an error if the SQL INSERT or UPDATE operation fails, or if the event is already
1917    /// persisted for another trader or awaiting assignment.
1918    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        // Event IDs are shared when two traders persist the same account state, and the existing
2000        // row belongs to its first writer (or awaits assignment if it has no trader)
2001        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    /// Loads all account events for `account_id` via the provided `pool`.
2016    ///
2017    /// # Errors
2018    ///
2019    /// Returns an error if the SQL SELECT or deserialization fails.
2020    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    /// Loads and assembles a complete `AccountAny` for `account_id` via the provided `pool`.
2041    ///
2042    /// # Errors
2043    ///
2044    /// Returns an error if assembling events or SQL operations fail.
2045    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    /// Loads and assembles all `AccountAny` entries via the provided `pool`.
2066    ///
2067    /// Only accounts with events written by `trader_id` are loaded.
2068    ///
2069    /// # Errors
2070    ///
2071    /// Returns an error if loading events or SQL operations fail.
2072    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    /// Loads the IDs of accounts that have events with no trader via the provided `pool`.
2102    ///
2103    /// These events predate trader-scoped account persistence, so no trader-scoped cache loads
2104    /// them until they are assigned with [`Self::assign_account_trader`].
2105    ///
2106    /// # Errors
2107    ///
2108    /// Returns an error if the SQL SELECT operation fails.
2109    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    /// Assigns the account events of `account_id` that have no trader to `trader_id` via the
2124    /// provided `pool`, returning the number of events assigned.
2125    ///
2126    /// Events already stamped with a trader are left unchanged.
2127    ///
2128    /// # Errors
2129    ///
2130    /// Returns an error if the account has no events, or if any SQL operation fails.
2131    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    /// Inserts a `TradeTick` entry via the provided `pool`.
2184    ///
2185    /// # Errors
2186    ///
2187    /// Returns an error if the SQL INSERT operation fails.
2188    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    /// Loads all `TradeTick` entries for `instrument_id` via the provided `pool`.
2216    ///
2217    /// # Errors
2218    ///
2219    /// Returns an error if the SQL SELECT or deserialization fails.
2220    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    /// Inserts a `QuoteTick` entry via the provided `pool`.
2235    ///
2236    /// # Errors
2237    ///
2238    /// Returns an error if the SQL INSERT operation fails.
2239    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    /// Loads all `QuoteTick` entries for `instrument_id` via the provided `pool`.
2266    ///
2267    /// # Errors
2268    ///
2269    /// Returns an error if the SQL SELECT or deserialization fails.
2270    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    /// Inserts a `Bar` entry via the provided `pool`.
2285    ///
2286    /// # Errors
2287    ///
2288    /// Returns an error if the SQL INSERT operation fails.
2289    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    /// Loads all `Bar` entries for `instrument_id` via the provided `pool`.
2332    ///
2333    /// # Errors
2334    ///
2335    /// Returns an error if the SQL SELECT or deserialization fails.
2336    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    /// Loads all distinct client order IDs from order events via the provided `pool`.
2351    ///
2352    /// Only order events written by `trader_id` are read.
2353    ///
2354    /// # Errors
2355    ///
2356    /// Returns an error if the SQL SELECT or iteration fails.
2357    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    /// Claims execution-client origins for existing order events in one transaction.
2384    ///
2385    /// # Errors
2386    ///
2387    /// Returns an error if an order has no persisted events, an order is already claimed by a
2388    /// different client, or any SQL operation fails. Any error rolls back the complete batch.
2389    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    /// Inserts or updates an order ID to position ID index entry via the provided `pool`.
2466    ///
2467    /// # Errors
2468    ///
2469    /// Returns an error if the SQL INSERT or UPDATE operation fails.
2470    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    /// Loads the order ID to position ID index via the provided `pool`.
2517    ///
2518    /// Only entries written by `trader_id` are loaded.
2519    ///
2520    /// # Errors
2521    ///
2522    /// Returns an error if the SQL SELECT or iteration fails.
2523    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    /// Inserts a `Signal` entry via the provided `pool`.
2549    ///
2550    /// # Errors
2551    ///
2552    /// Returns an error if the SQL INSERT operation fails.
2553    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    /// Loads all `Signal` entries by `name` via the provided `pool`.
2579    ///
2580    /// # Errors
2581    ///
2582    /// Returns an error if the SQL SELECT or deserialization fails.
2583    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    /// Inserts a `CustomData` entry via the provided `pool`.
2595    ///
2596    /// Serializes the model `CustomData` to full JSON and stores it in the JSONB `value` column.
2597    ///
2598    /// # Errors
2599    ///
2600    /// Returns an error if the SQL INSERT operation fails.
2601    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    /// Loads all `CustomData` entries of `data_type` via the provided `pool`.
2656    ///
2657    /// Filters by `data_type`, `metadata`, and `identifier` columns to match the requested data type.
2658    ///
2659    /// # Errors
2660    ///
2661    /// Returns an error if the SQL SELECT or deserialization fails.
2662    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}