332 lines
18 KiB
Python
332 lines
18 KiB
Python
"""Read-only ORM mapping for the immutable baseline product catalogue."""
|
|
|
|
from datetime import date, datetime
|
|
from decimal import Decimal
|
|
|
|
from sqlalchemy import JSON, BigInteger, Date, DateTime, Numeric, String, Text
|
|
from sqlalchemy.orm import Mapped, mapped_column
|
|
|
|
from app.model.base import Base
|
|
|
|
|
|
class FinancialProduct(Base):
|
|
__tablename__ = "fin_product"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_code: Mapped[str] = mapped_column(String(32), nullable=False)
|
|
product_name: Mapped[str] = mapped_column(String(128), nullable=False)
|
|
exchange_code: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
product_category: Mapped[str] = mapped_column(String(32), nullable=False)
|
|
risk_level: Mapped[str] = mapped_column(String(8), nullable=False)
|
|
fund_manager: Mapped[str | None] = mapped_column(String(64))
|
|
current_nav: Mapped[Decimal | None] = mapped_column(Numeric(18, 6))
|
|
open_start_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
open_end_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
open_period_start: Mapped[date | None] = mapped_column(Date)
|
|
open_period_end: Mapped[date | None] = mapped_column(Date)
|
|
risk_disclosure_required: Mapped[bool] = mapped_column(nullable=False)
|
|
second_confirmation_required: Mapped[bool] = mapped_column(nullable=False)
|
|
recording_required: Mapped[bool] = mapped_column(nullable=False)
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
|
|
|
|
class ProductIndustryExposure(Base):
|
|
"""Versioned, reference-data industry exposures for exchange-traded products."""
|
|
|
|
__tablename__ = "advisor_product_industry_exposure"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
industry_code: Mapped[str] = mapped_column(String(32), nullable=False)
|
|
industry_name: Mapped[str] = mapped_column(String(128), nullable=False)
|
|
exposure_weight_pct: Mapped[Decimal] = mapped_column(Numeric(7, 4), nullable=False)
|
|
as_of_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class FinancialHolding(Base):
|
|
"""Read-only mapping of the baseline current-holdings fact table."""
|
|
|
|
__tablename__ = "fin_holding"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
customer_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
total_quantity: Mapped[Decimal] = mapped_column(Numeric(18, 4), nullable=False)
|
|
average_cost: Mapped[Decimal | None] = mapped_column(Numeric(18, 6))
|
|
cost_amount: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
|
|
market_value: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
|
|
profit_loss: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
|
|
profit_loss_ratio: Mapped[Decimal | None] = mapped_column(Numeric(10, 4))
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
first_acquired_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class PortfolioProjectionCheckpoint(Base):
|
|
"""Additive watermark for asynchronous, non-authoritative graph projection."""
|
|
|
|
__tablename__ = "advisor_portfolio_projection_checkpoint"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
customer_id: Mapped[int] = mapped_column(BigInteger, nullable=False, unique=True)
|
|
holding_updated_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
exposure_updated_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class FinancialMarketPrice(Base):
|
|
"""Read-only baseline market-price history used for advisory metric calculation."""
|
|
|
|
__tablename__ = "fin_market_price"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
trade_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
close_price: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
|
|
turnover_amount: Mapped[Decimal | None] = mapped_column(Numeric(24, 2))
|
|
|
|
|
|
class AdvisorProductPriceHistory(Base):
|
|
"""Forward-compatible daily price history that coexists with baseline tables."""
|
|
|
|
__tablename__ = "advisor_product_price_history"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
trade_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
price_kind: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
close_price: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
|
|
turnover_amount: Mapped[Decimal | None] = mapped_column(Numeric(24, 2))
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
source_updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductMetricSnapshot(Base):
|
|
"""Additive, reproducible historical metrics; not a forecast or trading signal."""
|
|
|
|
__tablename__ = "advisor_product_metric_snapshot"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
as_of_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
trailing_20d_return_pct: Mapped[Decimal | None] = mapped_column(Numeric(10, 4))
|
|
trailing_120d_return_pct: Mapped[Decimal | None] = mapped_column(Numeric(10, 4))
|
|
annualized_volatility_pct: Mapped[Decimal | None] = mapped_column(Numeric(10, 4))
|
|
max_drawdown_pct: Mapped[Decimal | None] = mapped_column(Numeric(10, 4))
|
|
average_daily_turnover_amount: Mapped[Decimal | None] = mapped_column(Numeric(24, 2))
|
|
observation_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
calculation_version: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductDataQualitySnapshot(Base):
|
|
"""Reproducible quality gate for advisory history and derived metrics."""
|
|
|
|
__tablename__ = "advisor_product_data_quality_snapshot"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
as_of_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
observation_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
expected_trading_days: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
price_coverage_pct: Mapped[Decimal] = mapped_column(Numeric(7, 4), nullable=False)
|
|
turnover_coverage_pct: Mapped[Decimal] = mapped_column(Numeric(7, 4), nullable=False)
|
|
max_abs_daily_return_pct: Mapped[Decimal | None] = mapped_column(Numeric(10, 4))
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
reason_codes: Mapped[list[str]] = mapped_column(JSON, nullable=False)
|
|
rule_version: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductAssetClassification(Base):
|
|
"""Additive reference snapshot for allocating exchange-traded fund holdings."""
|
|
|
|
__tablename__ = "advisor_product_asset_classification"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
as_of_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
asset_class: Mapped[str] = mapped_column(String(32), nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductSuitabilityReference(Base):
|
|
"""Sales-institution-specific, versioned product suitability evidence."""
|
|
|
|
__tablename__ = "advisor_product_suitability_reference"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
sales_institution: Mapped[str] = mapped_column(String(128), nullable=False)
|
|
risk_level: Mapped[str] = mapped_column(String(8), nullable=False)
|
|
effective_from: Mapped[date] = mapped_column(Date, nullable=False)
|
|
effective_until: Mapped[date | None] = mapped_column(Date)
|
|
source_url: Mapped[str] = mapped_column(String(1024), nullable=False)
|
|
document_title: Mapped[str] = mapped_column(String(256), nullable=False)
|
|
document_published_at: Mapped[date | None] = mapped_column(Date)
|
|
document_sha256: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
review_status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
verified_by: Mapped[str | None] = mapped_column(String(128))
|
|
verified_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductContractSnapshot(Base):
|
|
"""Immutable, source-linked fund-contract facts for advisory explanations."""
|
|
|
|
__tablename__ = "advisor_product_contract_snapshot"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
effective_from: Mapped[date] = mapped_column(Date, nullable=False)
|
|
effective_until: Mapped[date | None] = mapped_column(Date)
|
|
fund_type: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
investment_scope: Mapped[str] = mapped_column(Text, nullable=False)
|
|
performance_benchmark: Mapped[str | None] = mapped_column(String(256))
|
|
risk_return_characteristics: Mapped[str] = mapped_column(Text, nullable=False)
|
|
custodian_name: Mapped[str | None] = mapped_column(String(128))
|
|
management_fee_rate_pct: Mapped[Decimal | None] = mapped_column(Numeric(9, 6))
|
|
custodian_fee_rate_pct: Mapped[Decimal | None] = mapped_column(Numeric(9, 6))
|
|
inception_date: Mapped[date | None] = mapped_column(Date)
|
|
source_url: Mapped[str] = mapped_column(String(1024), nullable=False)
|
|
document_title: Mapped[str] = mapped_column(String(256), nullable=False)
|
|
document_published_at: Mapped[date | None] = mapped_column(Date)
|
|
document_sha256: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
review_status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
verified_by: Mapped[str | None] = mapped_column(String(128))
|
|
verified_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductGovernanceCandidateSnapshot(Base):
|
|
"""Official-source changes awaiting an explicit compliance decision."""
|
|
|
|
__tablename__ = "advisor_product_governance_candidate_snapshot"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
data_kind: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
sales_institution: Mapped[str | None] = mapped_column(String(128))
|
|
observed_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
effective_from: Mapped[date] = mapped_column(Date, nullable=False)
|
|
risk_level: Mapped[str | None] = mapped_column(String(8))
|
|
payload: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
|
|
source_url: Mapped[str] = mapped_column(String(1024), nullable=False)
|
|
document_title: Mapped[str] = mapped_column(String(256), nullable=False)
|
|
document_published_at: Mapped[date | None] = mapped_column(Date)
|
|
document_sha256: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
review_status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
reviewed_by: Mapped[int | None] = mapped_column(BigInteger)
|
|
reviewed_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
review_comment: Mapped[str | None] = mapped_column(String(1000))
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductGovernanceSyncRun(Base):
|
|
"""Durable evidence of each official-source monitoring pass."""
|
|
|
|
__tablename__ = "advisor_product_governance_sync_run"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
run_no: Mapped[str] = mapped_column(String(36), nullable=False, unique=True)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
product_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
change_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
error_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
detail: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
|
|
started_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
completed_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class ProductMarketQuoteSnapshot(Base):
|
|
"""Additive intraday exchange quote and liquidity evidence."""
|
|
|
|
__tablename__ = "advisor_product_market_quote_snapshot"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
observed_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
last_price: Mapped[Decimal] = mapped_column(Numeric(18, 6), nullable=False)
|
|
previous_close: Mapped[Decimal | None] = mapped_column(Numeric(18, 6))
|
|
change_pct: Mapped[Decimal | None] = mapped_column(Numeric(10, 4))
|
|
volume: Mapped[Decimal | None] = mapped_column(Numeric(24, 4))
|
|
turnover_amount: Mapped[Decimal | None] = mapped_column(Numeric(24, 2))
|
|
quote_status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class MarketQuoteSourceRun(Base):
|
|
"""Durable execution result for an individual market quote source."""
|
|
|
|
__tablename__ = "advisor_market_quote_source_run"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
sync_run_no: Mapped[str] = mapped_column(String(36), nullable=False)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
priority_order: Mapped[int] = mapped_column(nullable=False)
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
requested_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
quote_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
error_type: Mapped[str | None] = mapped_column(String(128))
|
|
started_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
completed_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class MarketQuoteSourceHealth(Base):
|
|
"""Current availability state for each configured public quote source."""
|
|
|
|
__tablename__ = "advisor_market_quote_source_health"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
consecutive_failure_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
last_success_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
last_failure_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
last_error_type: Mapped[str | None] = mapped_column(String(128))
|
|
last_requested_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
last_quote_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
|
|
|
|
class MarketQuoteAlert(Base):
|
|
"""Open and resolved operational alerts for market data availability."""
|
|
|
|
__tablename__ = "advisor_market_quote_alert"
|
|
|
|
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
|
|
alert_no: Mapped[str] = mapped_column(String(36), nullable=False, unique=True)
|
|
source: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
alert_type: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
severity: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
status: Mapped[str] = mapped_column(String(16), nullable=False)
|
|
occurrence_count: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
detail: Mapped[dict[str, object]] = mapped_column(JSON, nullable=False)
|
|
first_observed_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
last_observed_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
resolved_at: Mapped[datetime | None] = mapped_column(DateTime)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)
|