"""Cloud server utilization rollup + efficiency profile. Revision ID: 419 Revises: 418 Create Date: 2026-07-20 Why this exists --------------- Helix synced cloud *inventory* (``cmdb_servers``: vcpu / memory_mb / disk) and per-resource *cost* (``cmdb_cost_facts``), but had zero visibility into runtime *utilization*. Colleagues flagged chronically underused AWS/cloud instances with no data to act on. This migration adds the two tables that back the cloud cost-optimization / efficiency feature: ``cmdb_server_metrics_daily`` Per-(server × day) rollup of normalized utilization, written daily by the ``cmdb.server_metrics_sync`` handler from each cloud's monitoring service (CloudWatch / Aliyun CloudMonitor / Azure Monitor / GCP Cloud Monitoring). Idempotent on ``(server_id, metric_date)``; pruned to ~90 days. CPU + network land everywhere agent-free; memory / disk usage need a guest agent and stay NULL otherwise (NULL ≠ 0). ``cmdb_server_efficiency`` One row per server: rolling-window aggregate + rule-engine verdict (idle / oversized / healthy / busy / insufficient_data / no_metrics) + advisory recommendation + estimated monthly savings (joined from ``cmdb_cost_facts``) + an optional AI narrative (filled later). Recomputed daily by ``cmdb.efficiency_compute``. The scheduled_jobs rows for both handlers are NOT seeded here — they are auto-provisioned per (cloud integration supporting the ``metrics`` capability) by ``cmdb.scheduled_jobs_provisioning`` at worker startup, mirroring how resource_sync / lifecycle rows work. Rule thresholds live in code defaults (overridable via ``platform_settings``), so nothing to seed. Idempotency ----------- ``CREATE TABLE IF NOT EXISTS`` semantics via ``_table_exists``. """ from __future__ import annotations import sqlalchemy as sa from alembic import op from app.core.migration_helpers import ( id_type_for, normalize_fk_column_collation, table_kw_for, ) revision = "419" down_revision = "418" branch_labels = None depends_on = None METRICS_TABLE = "cmdb_server_metrics_daily" EFFICIENCY_TABLE = "cmdb_server_efficiency" def _table_exists(bind, name: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.TABLES " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n" ), {"n": name}, ).scalar() is not None def upgrade() -> None: bind = op.get_bind() server_id_t = id_type_for("cmdb_servers") integ_id = id_type_for("integrations") kw = table_kw_for("cmdb_servers") if not _table_exists(bind, METRICS_TABLE): op.create_table( METRICS_TABLE, sa.Column("id", server_id_t, primary_key=True), sa.Column("server_id", server_id_t, nullable=False), sa.Column("integration_id", integ_id, nullable=False), sa.Column("metric_date", sa.Date(), nullable=False), # Percentages 0–100 (Float, not Numeric — engine math is float). sa.Column("cpu_pct_avg", sa.Float(), nullable=True), sa.Column("cpu_pct_p95", sa.Float(), nullable=True), sa.Column("cpu_pct_max", sa.Float(), nullable=True), sa.Column("mem_pct_avg", sa.Float(), nullable=True), sa.Column("mem_pct_p95", sa.Float(), nullable=True), sa.Column("mem_pct_max", sa.Float(), nullable=True), sa.Column("disk_used_pct_avg", sa.Float(), nullable=True), sa.Column("disk_used_pct_max", sa.Float(), nullable=True), sa.Column("net_in_bytes_avg", sa.BigInteger(), nullable=True), sa.Column("net_out_bytes_avg", sa.BigInteger(), nullable=True), sa.Column("disk_read_bytes_avg", sa.BigInteger(), nullable=True), sa.Column("disk_write_bytes_avg", sa.BigInteger(), nullable=True), sa.Column( "sample_count", sa.Integer(), nullable=False, server_default=sa.text("0"), ), sa.Column("source", sa.String(32), nullable=True), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.Column( "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.UniqueConstraint( "server_id", "metric_date", name="uq_server_metrics_daily", ), **kw, ) normalize_fk_column_collation( METRICS_TABLE, "server_id", "cmdb_servers", nullable=False, ) op.create_foreign_key( "fk_server_metrics_daily_server", METRICS_TABLE, "cmdb_servers", ["server_id"], ["id"], ondelete="CASCADE", ) normalize_fk_column_collation( METRICS_TABLE, "integration_id", "integrations", nullable=False, ) op.create_foreign_key( "fk_server_metrics_daily_integration", METRICS_TABLE, "integrations", ["integration_id"], ["id"], ondelete="CASCADE", ) op.create_index( "ix_server_metrics_daily_int_date", METRICS_TABLE, ["integration_id", "metric_date"], ) if not _table_exists(bind, EFFICIENCY_TABLE): op.create_table( EFFICIENCY_TABLE, sa.Column("id", server_id_t, primary_key=True), sa.Column("server_id", server_id_t, nullable=False), sa.Column("integration_id", integ_id, nullable=False), sa.Column( "window_days", sa.Integer(), nullable=False, server_default=sa.text("14"), ), sa.Column( "data_days", sa.Integer(), nullable=False, server_default=sa.text("0"), ), sa.Column("cpu_avg", sa.Float(), nullable=True), sa.Column("cpu_p95", sa.Float(), nullable=True), sa.Column("cpu_max", sa.Float(), nullable=True), sa.Column("mem_avg", sa.Float(), nullable=True), sa.Column("mem_p95", sa.Float(), nullable=True), sa.Column("mem_max", sa.Float(), nullable=True), sa.Column("net_in_avg", sa.BigInteger(), nullable=True), sa.Column("net_out_avg", sa.BigInteger(), nullable=True), sa.Column( "verdict", sa.String(32), nullable=False, server_default="insufficient_data", ), sa.Column("verdict_reasons", sa.JSON(), nullable=True), sa.Column( "recommendation", sa.String(32), nullable=False, server_default="none", ), sa.Column("recommended_action_detail", sa.JSON(), nullable=True), sa.Column("monthly_cost_usd", sa.Numeric(14, 2), nullable=True), sa.Column( "estimated_monthly_savings_usd", sa.Numeric(14, 2), nullable=True, ), sa.Column("cost_currency", sa.String(8), nullable=True), sa.Column("ai_narrative", sa.Text(), nullable=True), sa.Column("ai_narrative_at", sa.DateTime(timezone=True), nullable=True), sa.Column("rule_version", sa.String(16), nullable=True), sa.Column( "computed_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.Column( "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.UniqueConstraint("server_id", name="uq_server_efficiency"), **kw, ) normalize_fk_column_collation( EFFICIENCY_TABLE, "server_id", "cmdb_servers", nullable=False, ) op.create_foreign_key( "fk_server_efficiency_server", EFFICIENCY_TABLE, "cmdb_servers", ["server_id"], ["id"], ondelete="CASCADE", ) normalize_fk_column_collation( EFFICIENCY_TABLE, "integration_id", "integrations", nullable=False, ) op.create_foreign_key( "fk_server_efficiency_integration", EFFICIENCY_TABLE, "integrations", ["integration_id"], ["id"], ondelete="CASCADE", ) op.create_index( "ix_server_efficiency_int_verdict", EFFICIENCY_TABLE, ["integration_id", "verdict"], ) def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, EFFICIENCY_TABLE): op.drop_table(EFFICIENCY_TABLE) if _table_exists(bind, METRICS_TABLE): op.drop_table(METRICS_TABLE)