MaverickMCP

"""Add essential performance indexes Revision ID: f0696e2cac15 Revises: 007_enhance_audit_logging Create Date: 2025-06-25 17:28:38.473307 """ import sqlalchemy as sa from alembic import op # revision identifiers, used by Alembic. revision = "f0696e2cac15" down_revision = "007_enhance_audit_logging" branch_labels = None depends_on = None def upgrade() -> None: """Add essential performance indexes for existing tables only.""" print("Creating essential performance indexes...") # 1. Stock data performance indexes (for large stocks_pricecache table) try: op.create_index( "idx_stocks_pricecache_stock_date", "stocks_pricecache", ["stock_id", "date"], postgresql_using="btree", if_not_exists=True, ) print("✓ Created stock price cache index") except Exception as e: print(f"Warning: Could not create stock price cache index: {e}") # 2. Stock lookup optimization try: op.execute( "CREATE INDEX IF NOT EXISTS idx_stocks_stock_ticker_lower " "ON stocks_stock (LOWER(ticker_symbol))" ) print("✓ Created case-insensitive ticker lookup index") except Exception as e: print(f"Warning: Could not create ticker lookup index: {e}") # 3. MCP API keys performance (critical for authentication) try: op.create_index( "idx_mcp_api_keys_active_lookup", "mcp_api_keys", ["is_active", "expires_at"], postgresql_using="btree", if_not_exists=True, ) print("✓ Created API keys performance index") except Exception as e: print(f"Warning: Could not create API keys index: {e}") # 4. Requests tracking performance try: op.create_index( "idx_mcp_requests_user_time", "mcp_requests", ["user_id", sa.text("created_at DESC")], postgresql_using="btree", if_not_exists=True, ) print("✓ Created requests tracking index") except Exception as e: print(f"Warning: Could not create requests index: {e}") # 5. Auth audit log performance try: op.create_index( "idx_mcp_auth_audit_log_user_time", "mcp_auth_audit_log", ["user_id", sa.text("created_at DESC")], postgresql_using="btree", if_not_exists=True, ) print("✓ Created auth audit log index") except Exception as e: print(f"Warning: Could not create auth audit index: {e}") # 6. Screening tables performance (if they exist) try: op.create_index( "idx_maverick_stocks_combined_score", "maverick_stocks", [sa.text('"COMBINED_SCORE" DESC')], postgresql_using="btree", if_not_exists=True, ) print("✓ Created maverick stocks performance index") except Exception as e: print(f"Warning: Could not create maverick stocks index: {e}") try: op.create_index( "idx_maverick_bear_stocks_score", "maverick_bear_stocks", [sa.text('"SCORE" DESC')], postgresql_using="btree", if_not_exists=True, ) print("✓ Created maverick bear stocks performance index") except Exception as e: print(f"Warning: Could not create maverick bear stocks index: {e}") try: op.create_index( "idx_supply_demand_breakouts_rs_rating", "supply_demand_breakouts", [sa.text('"RS_RATING" DESC')], postgresql_using="btree", if_not_exists=True, ) print("✓ Created supply/demand breakouts performance index") except Exception as e: print(f"Warning: Could not create supply/demand breakouts index: {e}") print("Essential performance indexes creation completed!") def downgrade() -> None: """Remove essential performance indexes.""" print("Removing essential performance indexes...") # Remove indexes (order doesn't matter for drops) indexes_to_drop = [ ("idx_stocks_pricecache_stock_date", "stocks_pricecache"), ("idx_mcp_api_keys_active_lookup", "mcp_api_keys"), ("idx_mcp_requests_user_time", "mcp_requests"), ("idx_mcp_auth_audit_log_user_time", "mcp_auth_audit_log"), ("idx_maverick_stocks_combined_score", "maverick_stocks"), ("idx_maverick_bear_stocks_score", "maverick_bear_stocks"), ("idx_supply_demand_breakouts_rs_rating", "supply_demand_breakouts"), ] for index_name, table_name in indexes_to_drop: try: op.drop_index(index_name, table_name, if_exists=True) print(f"✓ Dropped {index_name}") except Exception as e: print(f"Warning: Could not drop {index_name}: {e}") # Drop special indexes try: op.execute("DROP INDEX IF EXISTS idx_stocks_stock_ticker_lower") print("✓ Dropped ticker lookup index") except Exception as e: print(f"Warning: Could not drop ticker lookup index: {e}") print("Essential performance indexes removal completed!")