"""initial schema Revision ID: 0001 Revises: Create Date: 2026-09-07 Hand-written to match app/models/*.py exactly (see docs/DATA_MODEL.md for the narrative reference). Regenerate/verify with `alembic revision --autogenerate` against a real Postgres instance before relying on this in production — this file was authored without the ability to run Alembic in the build environment (see TROUBLESHOOTING.md). """ from alembic import op import sqlalchemy as sa revision = "0001" down_revision = None branch_labels = None depends_on = None UUID = sa.String(36) def _ts_cols(): return [ sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), ] def upgrade() -> None: op.create_table( "countries", sa.Column("id", UUID, primary_key=True), sa.Column("iso2", sa.String(2), nullable=False, unique=True), sa.Column("iso3", sa.String(3), nullable=False, unique=True), sa.Column("name", sa.String(128), nullable=False), sa.Column("region", sa.String(64), nullable=False), sa.Column("currency", sa.String(3), nullable=False), sa.Column("risk_free_rate_override", sa.Float, nullable=True), sa.Column("equity_risk_premium_override", sa.Float, nullable=True), ) op.create_index("ix_countries_iso2", "countries", ["iso2"]) op.create_index("ix_countries_region", "countries", ["region"]) op.create_table( "exchanges", sa.Column("id", UUID, primary_key=True), sa.Column("mic", sa.String(16), nullable=False, unique=True), sa.Column("name", sa.String(128), nullable=False), sa.Column("country_id", UUID, sa.ForeignKey("countries.id"), nullable=False), sa.Column("timezone", sa.String(64), nullable=False), ) op.create_index("ix_exchanges_country_id", "exchanges", ["country_id"]) op.create_table( "sectors", sa.Column("id", UUID, primary_key=True), sa.Column("code", sa.String(32), nullable=False, unique=True), sa.Column("name", sa.String(128), nullable=False), ) op.create_table( "industries", sa.Column("id", UUID, primary_key=True), sa.Column("code", sa.String(64), nullable=False, unique=True), sa.Column("name", sa.String(128), nullable=False), sa.Column("sector_id", UUID, sa.ForeignKey("sectors.id"), nullable=False), ) op.create_index("ix_industries_sector_id", "industries", ["sector_id"]) op.create_table( "users", sa.Column("id", UUID, primary_key=True), sa.Column("email", sa.String(256), nullable=False, unique=True), sa.Column("hashed_password", sa.String(256), nullable=False), sa.Column("display_name", sa.String(128), nullable=True), sa.Column("is_active", sa.Boolean, nullable=False, server_default=sa.true()), sa.Column("is_admin", sa.Boolean, nullable=False, server_default=sa.false()), *_ts_cols(), ) op.create_table( "sources", sa.Column("id", UUID, primary_key=True), sa.Column("provider", sa.String(32), nullable=False), sa.Column("provider_tier", sa.String(16), nullable=False), sa.Column("is_demo", sa.Boolean, nullable=False, server_default=sa.false()), sa.Column("retrieved_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("endpoint", sa.String(256), nullable=True), sa.Column("raw_payload", sa.JSON, nullable=True), sa.Column("content_hash", sa.String(64), nullable=True), *_ts_cols(), ) op.create_index("ix_sources_provider", "sources", ["provider"]) op.create_index("ix_sources_is_demo", "sources", ["is_demo"]) op.create_index("ix_sources_content_hash", "sources", ["content_hash"]) op.create_table( "companies", sa.Column("id", UUID, primary_key=True), sa.Column("legal_name", sa.String(256), nullable=False), sa.Column("display_name", sa.String(128), nullable=False), sa.Column("country_id", UUID, sa.ForeignKey("countries.id"), nullable=False), sa.Column("sector_id", UUID, sa.ForeignKey("sectors.id"), nullable=False), sa.Column("industry_id", UUID, sa.ForeignKey("industries.id"), nullable=False), sa.Column("website", sa.String(256), nullable=True), sa.Column("description", sa.Text, nullable=True), sa.Column("is_active", sa.Boolean, nullable=False, server_default=sa.true()), *_ts_cols(), ) op.create_index("ix_companies_legal_name", "companies", ["legal_name"]) op.create_index("ix_companies_display_name", "companies", ["display_name"]) op.create_index("ix_companies_country_id", "companies", ["country_id"]) op.create_index("ix_companies_sector_id", "companies", ["sector_id"]) op.create_index("ix_companies_industry_id", "companies", ["industry_id"]) op.create_table( "securities", sa.Column("id", UUID, primary_key=True), sa.Column("company_id", UUID, sa.ForeignKey("companies.id"), nullable=False), sa.Column("exchange_id", UUID, sa.ForeignKey("exchanges.id"), nullable=False), sa.Column("ticker", sa.String(32), nullable=False), sa.Column("isin", sa.String(12), nullable=True), sa.Column("cusip", sa.String(9), nullable=True), sa.Column("security_type", sa.String(16), nullable=False, server_default="COMMON"), sa.Column("currency", sa.String(3), nullable=False), sa.Column("is_primary_listing", sa.Boolean, nullable=False, server_default=sa.true()), sa.Column("listing_date", sa.Date, nullable=True), sa.Column("delisting_date", sa.Date, nullable=True), sa.Column("status", sa.String(16), nullable=False, server_default="ACTIVE"), sa.Column("discovery_status", sa.String(24), nullable=True), *_ts_cols(), sa.UniqueConstraint("ticker", "exchange_id", name="uq_security_ticker_exchange"), ) op.create_index("ix_securities_company_id", "securities", ["company_id"]) op.create_index("ix_securities_exchange_id", "securities", ["exchange_id"]) op.create_index("ix_securities_ticker", "securities", ["ticker"]) op.create_index("ix_securities_isin", "securities", ["isin"]) op.create_table( "financial_periods", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("period_end", sa.Date, nullable=False), sa.Column("period_type", sa.String(8), nullable=False), sa.Column("filing_date", sa.Date, nullable=True), sa.Column("published_at", sa.Date, nullable=True), sa.Column("retrieved_at", sa.Date, nullable=True), sa.Column("currency", sa.String(3), nullable=False), sa.Column("source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), sa.Column("methodology_version", sa.String(16), nullable=False, server_default="v1"), *_ts_cols(), sa.UniqueConstraint("security_id", "period_end", "period_type", "filing_date", name="uq_financial_period"), ) op.create_index("ix_financial_periods_security_id", "financial_periods", ["security_id"]) op.create_index("ix_financial_periods_period_end", "financial_periods", ["period_end"]) op.create_index("ix_financial_periods_filing_date", "financial_periods", ["filing_date"]) def _statement_table(name, extra_cols): op.create_table( name, sa.Column("id", UUID, primary_key=True), sa.Column("financial_period_id", UUID, sa.ForeignKey("financial_periods.id"), nullable=False, unique=True), *extra_cols, *_ts_cols(), ) op.create_index(f"ix_{name}_financial_period_id", name, ["financial_period_id"]) _statement_table("income_statements", [ sa.Column("revenue", sa.Float, nullable=True), sa.Column("cogs", sa.Float, nullable=True), sa.Column("gross_profit", sa.Float, nullable=True), sa.Column("operating_income", sa.Float, nullable=True), sa.Column("ebit", sa.Float, nullable=True), sa.Column("ebitda", sa.Float, nullable=True), sa.Column("net_income", sa.Float, nullable=True), sa.Column("eps_basic", sa.Float, nullable=True), sa.Column("eps_diluted", sa.Float, nullable=True), sa.Column("tax_expense", sa.Float, nullable=True), sa.Column("pretax_income", sa.Float, nullable=True), sa.Column("interest_expense", sa.Float, nullable=True), sa.Column("depreciation_and_amortization", sa.Float, nullable=True), sa.Column("stock_based_compensation", sa.Float, nullable=True), sa.Column("adjusted_net_income", sa.Float, nullable=True), ]) _statement_table("balance_sheets", [ sa.Column("cash_and_equivalents", sa.Float, nullable=True), sa.Column("short_term_investments", sa.Float, nullable=True), sa.Column("total_debt", sa.Float, nullable=True), sa.Column("short_term_debt", sa.Float, nullable=True), sa.Column("long_term_debt", sa.Float, nullable=True), sa.Column("lease_liabilities", sa.Float, nullable=True), sa.Column("total_assets", sa.Float, nullable=True), sa.Column("current_assets", sa.Float, nullable=True), sa.Column("current_liabilities", sa.Float, nullable=True), sa.Column("shareholders_equity", sa.Float, nullable=True), sa.Column("minority_interest", sa.Float, nullable=True), sa.Column("preferred_equity", sa.Float, nullable=True), sa.Column("goodwill", sa.Float, nullable=True), sa.Column("intangible_assets", sa.Float, nullable=True), sa.Column("receivables", sa.Float, nullable=True), sa.Column("inventory", sa.Float, nullable=True), sa.Column("working_capital", sa.Float, nullable=True), ]) _statement_table("cash_flows", [ sa.Column("operating_cash_flow", sa.Float, nullable=True), sa.Column("capital_expenditure", sa.Float, nullable=True), sa.Column("free_cash_flow", sa.Float, nullable=True), sa.Column("dividends_paid", sa.Float, nullable=True), sa.Column("buybacks", sa.Float, nullable=True), sa.Column("stock_issuance", sa.Float, nullable=True), ]) _statement_table("shares", [ sa.Column("shares_outstanding", sa.Float, nullable=True), sa.Column("diluted_shares", sa.Float, nullable=True), ]) op.create_table( "dividends", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("ex_date", sa.Date, nullable=False), sa.Column("pay_date", sa.Date, nullable=True), sa.Column("amount_per_share", sa.Float, nullable=False), sa.Column("currency", sa.String(3), nullable=False), sa.Column("is_special", sa.Boolean, nullable=False, server_default=sa.false()), sa.Column("source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), *_ts_cols(), ) op.create_index("ix_dividends_security_id", "dividends", ["security_id"]) op.create_index("ix_dividends_ex_date", "dividends", ["ex_date"]) op.create_table( "buybacks", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("period_end", sa.Date, nullable=False), sa.Column("gross_amount", sa.Float, nullable=True), sa.Column("shares_repurchased", sa.Float, nullable=True), sa.Column("source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), *_ts_cols(), ) op.create_index("ix_buybacks_security_id", "buybacks", ["security_id"]) op.create_index("ix_buybacks_period_end", "buybacks", ["period_end"]) op.create_table( "estimates", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("period_end", sa.Date, nullable=False), sa.Column("metric", sa.String(32), nullable=False), sa.Column("consensus_value", sa.Float, nullable=True), sa.Column("num_analysts", sa.Integer, nullable=True), sa.Column("as_of_date", sa.Date, nullable=False), sa.Column("source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), *_ts_cols(), sa.UniqueConstraint("security_id", "period_end", "metric", "as_of_date", name="uq_estimate"), ) op.create_index("ix_estimates_security_id", "estimates", ["security_id"]) op.create_index("ix_estimates_period_end", "estimates", ["period_end"]) op.create_index("ix_estimates_as_of_date", "estimates", ["as_of_date"]) op.create_table( "guidance", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("period_end", sa.Date, nullable=False), sa.Column("metric", sa.String(32), nullable=False), sa.Column("low", sa.Float, nullable=True), sa.Column("high", sa.Float, nullable=True), sa.Column("issued_date", sa.Date, nullable=False), sa.Column("revision_direction", sa.String(8), nullable=True), sa.Column("source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), *_ts_cols(), ) op.create_index("ix_guidance_security_id", "guidance", ["security_id"]) op.create_index("ix_guidance_period_end", "guidance", ["period_end"]) op.create_index("ix_guidance_issued_date", "guidance", ["issued_date"]) op.create_table( "prices", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("date", sa.Date, nullable=False), sa.Column("open", sa.Float, nullable=True), sa.Column("high", sa.Float, nullable=True), sa.Column("low", sa.Float, nullable=True), sa.Column("close", sa.Float, nullable=False), sa.Column("adjusted_close", sa.Float, nullable=True), sa.Column("volume", sa.Float, nullable=True), sa.Column("currency", sa.String(3), nullable=False), sa.Column("source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), *_ts_cols(), sa.UniqueConstraint("security_id", "date", name="uq_price_security_date"), ) op.create_index("ix_prices_security_id", "prices", ["security_id"]) op.create_index("ix_prices_date", "prices", ["date"]) op.create_table( "corporate_actions", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("action_type", sa.String(24), nullable=False), sa.Column("effective_date", sa.Date, nullable=False), sa.Column("ratio", sa.Float, nullable=True), sa.Column("details", sa.Text, nullable=True), sa.Column("source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), *_ts_cols(), ) op.create_index("ix_corporate_actions_security_id", "corporate_actions", ["security_id"]) op.create_index("ix_corporate_actions_action_type", "corporate_actions", ["action_type"]) op.create_index("ix_corporate_actions_effective_date", "corporate_actions", ["effective_date"]) op.create_table( "metrics", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("industry_id", UUID, sa.ForeignKey("industries.id"), nullable=False), sa.Column("metric_key", sa.String(48), nullable=False), sa.Column("value", sa.Float, nullable=True), sa.Column("status", sa.String(16), nullable=False), sa.Column("applicability", sa.String(24), nullable=False), sa.Column("formula_version", sa.String(16), nullable=False), sa.Column("as_of", sa.Date, nullable=False), sa.Column("inputs_used", sa.JSON, nullable=True), sa.Column("note", sa.Text, nullable=True), *_ts_cols(), sa.UniqueConstraint("security_id", "metric_key", name="uq_metric_security_key"), ) op.create_index("ix_metrics_security_id", "metrics", ["security_id"]) op.create_index("ix_metrics_industry_id", "metrics", ["industry_id"]) op.create_index("ix_metrics_metric_key", "metrics", ["metric_key"]) op.create_index("ix_metrics_value", "metrics", ["value"]) op.create_index("ix_metrics_industry_key_value", "metrics", ["industry_id", "metric_key", "value"]) op.create_table( "metric_history", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("metric_key", sa.String(48), nullable=False), sa.Column("value", sa.Float, nullable=True), sa.Column("status", sa.String(16), nullable=False), sa.Column("applicability", sa.String(24), nullable=False), sa.Column("formula_version", sa.String(16), nullable=False), sa.Column("as_of", sa.Date, nullable=False), sa.Column("calculation_date", sa.Date, nullable=False), *_ts_cols(), sa.UniqueConstraint("security_id", "metric_key", "as_of", "formula_version", name="uq_metric_history"), ) op.create_index("ix_metric_history_security_id", "metric_history", ["security_id"]) op.create_index("ix_metric_history_metric_key", "metric_history", ["metric_key"]) op.create_index("ix_metric_history_as_of", "metric_history", ["as_of"]) op.create_index("ix_metric_history_security_key_asof", "metric_history", ["security_id", "metric_key", "as_of"]) op.create_table( "valuation", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("calculation_date", sa.Date, nullable=False), sa.Column("wacc", sa.Float, nullable=True), sa.Column("wacc_inputs", sa.JSON, nullable=True), sa.Column("dcf_bear_fair_value", sa.Float, nullable=True), sa.Column("dcf_base_fair_value", sa.Float, nullable=True), sa.Column("dcf_bull_fair_value", sa.Float, nullable=True), sa.Column("dcf_assumptions", sa.JSON, nullable=True), sa.Column("multiples_fair_values", sa.JSON, nullable=True), sa.Column("business_profile", sa.String(24), nullable=True), sa.Column("bear_fair_value", sa.Float, nullable=True), sa.Column("base_fair_value", sa.Float, nullable=True), sa.Column("bull_fair_value", sa.Float, nullable=True), sa.Column("weighted_fair_value", sa.Float, nullable=True), sa.Column("fair_value_confidence", sa.Float, nullable=True), sa.Column("price_at_calculation", sa.Float, nullable=True), sa.Column("margin_of_safety", sa.Float, nullable=True), sa.Column("strong_buy_price", sa.Float, nullable=True), sa.Column("buy_price", sa.Float, nullable=True), sa.Column("overvalued_price", sa.Float, nullable=True), sa.Column("expected_return_3y", sa.Float, nullable=True), sa.Column("expected_return_5y", sa.Float, nullable=True), sa.Column("expected_return_10y", sa.Float, nullable=True), sa.Column("expected_return_components", sa.JSON, nullable=True), sa.Column("methodology_version", sa.String(16), nullable=False, server_default="v1"), *_ts_cols(), sa.UniqueConstraint("security_id", "calculation_date", name="uq_valuation_security_date"), ) op.create_index("ix_valuation_security_id", "valuation", ["security_id"]) op.create_index("ix_valuation_calculation_date", "valuation", ["calculation_date"]) op.create_table( "scores", sa.Column("id", UUID, primary_key=True), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("calculation_date", sa.Date, nullable=False), sa.Column("quality_score", sa.Float, nullable=True), sa.Column("financial_health_score", sa.Float, nullable=True), sa.Column("growth_score", sa.Float, nullable=True), sa.Column("competitive_advantage_score", sa.Float, nullable=True), sa.Column("valuation_score", sa.Float, nullable=True), sa.Column("overall_score", sa.Float, nullable=True), sa.Column("risk_score", sa.Float, nullable=True), sa.Column("weights_used", sa.JSON, nullable=True), sa.Column("subscore_detail", sa.JSON, nullable=True), sa.Column("recommendation", sa.String(16), nullable=True), sa.Column("recommendation_confidence", sa.Float, nullable=True), sa.Column("triggers_fired", sa.JSON, nullable=True), sa.Column("methodology_version", sa.String(16), nullable=False, server_default="v1"), *_ts_cols(), sa.UniqueConstraint("security_id", "calculation_date", name="uq_score_security_date"), ) op.create_index("ix_scores_security_id", "scores", ["security_id"]) op.create_index("ix_scores_calculation_date", "scores", ["calculation_date"]) op.create_index("ix_scores_overall_score", "scores", ["overall_score"]) op.create_index("ix_scores_quality_score", "scores", ["quality_score"]) op.create_index("ix_scores_recommendation", "scores", ["recommendation"]) op.create_table( "data_quality", sa.Column("id", UUID, primary_key=True), sa.Column("entity_table", sa.String(64), nullable=False), sa.Column("entity_id", sa.String(64), nullable=False), sa.Column("field", sa.String(64), nullable=False), sa.Column("status", sa.String(16), nullable=False), sa.Column("source_a_id", UUID, sa.ForeignKey("sources.id"), nullable=True), sa.Column("value_a", sa.Float, nullable=True), sa.Column("source_b_id", UUID, sa.ForeignKey("sources.id"), nullable=True), sa.Column("value_b", sa.Float, nullable=True), sa.Column("selected_value", sa.Float, nullable=True), sa.Column("selected_source_id", UUID, sa.ForeignKey("sources.id"), nullable=True), sa.Column("selection_reason", sa.Text, nullable=True), *_ts_cols(), ) op.create_index("ix_data_quality_entity_id", "data_quality", ["entity_id"]) op.create_table( "screeners", sa.Column("id", UUID, primary_key=True), sa.Column("owner_user_id", UUID, sa.ForeignKey("users.id"), nullable=True), sa.Column("key", sa.String(48), nullable=True, unique=True), sa.Column("name", sa.String(128), nullable=False), sa.Column("filter_json", sa.JSON, nullable=False), sa.Column("weights", sa.JSON, nullable=True), sa.Column("is_system", sa.Boolean, nullable=False, server_default=sa.false()), *_ts_cols(), ) op.create_index("ix_screeners_owner_user_id", "screeners", ["owner_user_id"]) op.create_table( "screen_results", sa.Column("id", UUID, primary_key=True), sa.Column("screener_id", UUID, sa.ForeignKey("screeners.id"), nullable=False), sa.Column("run_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("security_ids", sa.JSON, nullable=False), *_ts_cols(), ) op.create_index("ix_screen_results_screener_id", "screen_results", ["screener_id"]) op.create_index("ix_screen_results_run_at", "screen_results", ["run_at"]) op.create_table( "watchlists", sa.Column("id", UUID, primary_key=True), sa.Column("owner_user_id", UUID, sa.ForeignKey("users.id"), nullable=False), sa.Column("name", sa.String(128), nullable=False, server_default="My Watchlist"), *_ts_cols(), ) op.create_index("ix_watchlists_owner_user_id", "watchlists", ["owner_user_id"]) op.create_table( "watchlist_items", sa.Column("id", UUID, primary_key=True), sa.Column("watchlist_id", UUID, sa.ForeignKey("watchlists.id"), nullable=False), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("note", sa.Text, nullable=True), *_ts_cols(), sa.UniqueConstraint("watchlist_id", "security_id", name="uq_watchlist_item"), ) op.create_index("ix_watchlist_items_watchlist_id", "watchlist_items", ["watchlist_id"]) op.create_index("ix_watchlist_items_security_id", "watchlist_items", ["security_id"]) op.create_table( "alerts", sa.Column("id", UUID, primary_key=True), sa.Column("owner_user_id", UUID, sa.ForeignKey("users.id"), nullable=False), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("alert_type", sa.String(32), nullable=False), sa.Column("is_active", sa.Boolean, nullable=False, server_default=sa.true()), sa.Column("last_triggered_at", sa.DateTime(timezone=True), nullable=True), sa.Column("delivery_channel", sa.String(16), nullable=False, server_default="IN_APP"), *_ts_cols(), ) op.create_index("ix_alerts_owner_user_id", "alerts", ["owner_user_id"]) op.create_index("ix_alerts_security_id", "alerts", ["security_id"]) op.create_table( "portfolios", sa.Column("id", UUID, primary_key=True), sa.Column("owner_user_id", UUID, sa.ForeignKey("users.id"), nullable=False), sa.Column("name", sa.String(128), nullable=False, server_default="My Portfolio"), sa.Column("base_currency", sa.String(3), nullable=False, server_default="USD"), *_ts_cols(), ) op.create_index("ix_portfolios_owner_user_id", "portfolios", ["owner_user_id"]) op.create_table( "portfolio_positions", sa.Column("id", UUID, primary_key=True), sa.Column("portfolio_id", UUID, sa.ForeignKey("portfolios.id"), nullable=False), sa.Column("security_id", UUID, sa.ForeignKey("securities.id"), nullable=False), sa.Column("shares", sa.Float, nullable=False), sa.Column("average_cost_basis", sa.Float, nullable=True), sa.Column("target_allocation_pct", sa.Float, nullable=True), *_ts_cols(), sa.UniqueConstraint("portfolio_id", "security_id", name="uq_portfolio_position"), ) op.create_index("ix_portfolio_positions_portfolio_id", "portfolio_positions", ["portfolio_id"]) op.create_index("ix_portfolio_positions_security_id", "portfolio_positions", ["security_id"]) op.create_table( "audit_log", sa.Column("id", UUID, primary_key=True), sa.Column("occurred_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("actor_user_id", UUID, sa.ForeignKey("users.id"), nullable=True), sa.Column("event_type", sa.String(64), nullable=False), sa.Column("detail", sa.JSON, nullable=True), sa.Column("ip_address", sa.String(64), nullable=True), ) op.create_index("ix_audit_log_occurred_at", "audit_log", ["occurred_at"]) op.create_index("ix_audit_log_event_type", "audit_log", ["event_type"]) def downgrade() -> None: for table in [ "audit_log", "portfolio_positions", "portfolios", "alerts", "watchlist_items", "watchlists", "screen_results", "screeners", "data_quality", "scores", "valuation", "metric_history", "metrics", "corporate_actions", "prices", "guidance", "estimates", "buybacks", "dividends", "shares", "cash_flows", "balance_sheets", "income_statements", "financial_periods", "securities", "companies", "sources", "users", "industries", "sectors", "exchanges", "countries", ]: op.drop_table(table)