feat: add SQLAlchemy ORM models for news, scores, selections

3 files changed, 94 insertions, 0 deletions

diff --git a/shared/src/shared/sa_models.py b/shared/src/shared/sa_models.py
index 8386ba8..1bd92c2 100644
--- a/shared/src/shared/sa_models.py
+++ b/shared/src/shared/sa_models.py
@@ -3,6 +3,7 @@
 from datetime import datetime
 from decimal import Decimal
 
+import sqlalchemy as sa
 from sqlalchemy import DateTime, ForeignKey, Numeric, Text
 from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
 
@@ -83,3 +84,63 @@ class PortfolioSnapshotRow(Base):
     realized_pnl: Mapped[Decimal] = mapped_column(Numeric, nullable=False)
     unrealized_pnl: Mapped[Decimal] = mapped_column(Numeric, nullable=False)
     snapshot_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
+
+
+class NewsItemRow(Base):
+    __tablename__ = "news_items"
+
+    id: Mapped[str] = mapped_column(Text, primary_key=True)
+    source: Mapped[str] = mapped_column(Text, nullable=False)
+    headline: Mapped[str] = mapped_column(Text, nullable=False)
+    summary: Mapped[str | None] = mapped_column(Text)
+    url: Mapped[str | None] = mapped_column(Text)
+    published_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
+    symbols: Mapped[str | None] = mapped_column(Text)  # JSON-encoded list
+    sentiment: Mapped[float] = mapped_column(sa.Float, nullable=False)
+    category: Mapped[str] = mapped_column(Text, nullable=False)
+    raw_data: Mapped[str | None] = mapped_column(Text)  # JSON string
+    created_at: Mapped[datetime] = mapped_column(
+        DateTime(timezone=True), nullable=False, server_default=sa.func.now()
+    )
+
+
+class SymbolScoreRow(Base):
+    __tablename__ = "symbol_scores"
+
+    id: Mapped[str] = mapped_column(Text, primary_key=True)
+    symbol: Mapped[str] = mapped_column(Text, nullable=False, unique=True)
+    news_score: Mapped[float] = mapped_column(sa.Float, nullable=False, server_default="0")
+    news_count: Mapped[int] = mapped_column(sa.Integer, nullable=False, server_default="0")
+    social_score: Mapped[float] = mapped_column(sa.Float, nullable=False, server_default="0")
+    policy_score: Mapped[float] = mapped_column(sa.Float, nullable=False, server_default="0")
+    filing_score: Mapped[float] = mapped_column(sa.Float, nullable=False, server_default="0")
+    composite: Mapped[float] = mapped_column(sa.Float, nullable=False, server_default="0")
+    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
+
+
+class MarketSentimentRow(Base):
+    __tablename__ = "market_sentiment"
+
+    id: Mapped[str] = mapped_column(Text, primary_key=True)
+    fear_greed: Mapped[int] = mapped_column(sa.Integer, nullable=False)
+    fear_greed_label: Mapped[str] = mapped_column(Text, nullable=False)
+    vix: Mapped[float | None] = mapped_column(sa.Float)
+    fed_stance: Mapped[str] = mapped_column(Text, nullable=False, server_default="neutral")
+    market_regime: Mapped[str] = mapped_column(Text, nullable=False, server_default="neutral")
+    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
+
+
+class StockSelectionRow(Base):
+    __tablename__ = "stock_selections"
+
+    id: Mapped[str] = mapped_column(Text, primary_key=True)
+    trade_date: Mapped[datetime] = mapped_column(sa.Date, nullable=False)
+    symbol: Mapped[str] = mapped_column(Text, nullable=False)
+    side: Mapped[str] = mapped_column(Text, nullable=False)
+    conviction: Mapped[float] = mapped_column(sa.Float, nullable=False)
+    reason: Mapped[str] = mapped_column(Text, nullable=False)
+    key_news: Mapped[str | None] = mapped_column(Text)  # JSON string
+    sentiment_snapshot: Mapped[str | None] = mapped_column(Text)  # JSON string
+    created_at: Mapped[datetime] = mapped_column(
+        DateTime(timezone=True), nullable=False, server_default=sa.func.now()
+    )
diff --git a/shared/tests/test_sa_models.py b/shared/tests/test_sa_models.py
index 67c3c82..dc6355e 100644
--- a/shared/tests/test_sa_models.py
+++ b/shared/tests/test_sa_models.py
@@ -14,6 +14,10 @@ def test_base_metadata_has_all_tables():
         "trades",
         "positions",
         "portfolio_snapshots",
+        "news_items",
+        "symbol_scores",
+        "market_sentiment",
+        "stock_selections",
     }
     assert expected == table_names
 
diff --git a/shared/tests/test_sa_news_models.py b/shared/tests/test_sa_news_models.py
new file mode 100644
index 0000000..91e6d4a
--- /dev/null
+++ b/shared/tests/test_sa_news_models.py
@@ -0,0 +1,29 @@
+"""Tests for news-related SQLAlchemy models."""
+
+from shared.sa_models import NewsItemRow, SymbolScoreRow, MarketSentimentRow, StockSelectionRow
+
+
+def test_news_item_row_tablename():
+    assert NewsItemRow.__tablename__ == "news_items"
+
+
+def test_symbol_score_row_tablename():
+    assert SymbolScoreRow.__tablename__ == "symbol_scores"
+
+
+def test_market_sentiment_row_tablename():
+    assert MarketSentimentRow.__tablename__ == "market_sentiment"
+
+
+def test_stock_selection_row_tablename():
+    assert StockSelectionRow.__tablename__ == "stock_selections"
+
+
+def test_news_item_row_columns():
+    cols = {c.name for c in NewsItemRow.__table__.columns}
+    assert cols >= {"id", "source", "headline", "published_at", "sentiment", "category"}
+
+
+def test_symbol_score_row_columns():
+    cols = {c.name for c in SymbolScoreRow.__table__.columns}
+    assert cols >= {"id", "symbol", "news_score", "composite", "updated_at"}