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