feat: add missing DB indexes for signals, orders, trades, snapshots

1 files changed, 35 insertions, 0 deletions

diff --git a/shared/alembic/versions/003_add_missing_indexes.py b/shared/alembic/versions/003_add_missing_indexes.py
new file mode 100644
index 0000000..ff08789
--- /dev/null
+++ b/shared/alembic/versions/003_add_missing_indexes.py
@@ -0,0 +1,35 @@
+"""Add missing indexes for common query patterns.
+
+Revision ID: 003
+Revises: 002
+Create Date: 2026-04-02
+"""
+
+from typing import Sequence, Union
+
+from alembic import op
+
+revision: str = "003"
+down_revision: Union[str, None] = "002"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_index("idx_signals_symbol_created", "signals", ["symbol", "created_at"])
+    op.create_index(
+        "idx_orders_symbol_status_created", "orders", ["symbol", "status", "created_at"]
+    )
+    op.create_index("idx_trades_order_id", "trades", ["order_id"])
+    op.create_index("idx_trades_symbol_traded", "trades", ["symbol", "traded_at"])
+    op.create_index("idx_portfolio_snapshots_at", "portfolio_snapshots", ["snapshot_at"])
+    op.create_index("idx_symbol_scores_symbol", "symbol_scores", ["symbol"], unique=True)
+
+
+def downgrade() -> None:
+    op.drop_index("idx_symbol_scores_symbol", table_name="symbol_scores")
+    op.drop_index("idx_portfolio_snapshots_at", table_name="portfolio_snapshots")
+    op.drop_index("idx_trades_symbol_traded", table_name="trades")
+    op.drop_index("idx_trades_order_id", table_name="trades")
+    op.drop_index("idx_orders_symbol_status_created", table_name="orders")
+    op.drop_index("idx_signals_symbol_created", table_name="signals")