"""Add cloud-resource governance, findings, rules and indefinite rollups. Revision ID: 530 Revises: 529 """ from __future__ import annotations import json import uuid import sqlalchemy as sa from alembic import op from app.core.migration_helpers import id_type_for revision = "530" down_revision = "529" branch_labels = None depends_on = None _JOB = "cmdb.resource_governance_evaluate" _RULES = ( ("resource.missing_owner", "资源缺少责任人", "ownership", "medium", "missing_owner", "为资源或所属云账号指定明确责任人。"), ("resource.public_exposure", "资源存在公网暴露", "exposure", "high", "public_exposure", "确认公网暴露是预期设计，并限制来源、端口与访问策略。"), ("resource.stale_observation", "资源扫描数据过期", "freshness", "medium", "stale_observation", "检查集成、只读权限、API 配额和最近同步结果。"), ("resource.restricted_review", "敏感资源需要治理复核", "sensitivity", "high", "restricted_review", "核对最小权限、责任人和数据分级，并记录复核结论。"), ("resource.production_unclassified", "生产资源尚未完成关键性分级", "classification", "medium", "production_unclassified", "补充关键性、数据分级、生命周期与合规状态。"), ) def upgrade() -> None: identity_id = id_type_for("identity_persons") integration_id = id_type_for("integrations") op.create_table( "cmdb_resource_governance", sa.Column("id", integration_id, primary_key=True), sa.Column("resource_id", integration_id, nullable=False), sa.Column("business_owner_person_id", identity_id), sa.Column("technical_owner_person_id", identity_id), sa.Column("governance_status", sa.String(24), nullable=False, server_default="unreviewed"), sa.Column("criticality", sa.String(16)), sa.Column("data_classification", sa.String(24)), sa.Column("risk_level", sa.String(16)), sa.Column("lifecycle_stage", sa.String(24)), sa.Column("compliance_status", sa.String(24)), sa.Column("exception_reason", sa.Text()), sa.Column("exception_expires_at", sa.DateTime(timezone=True)), sa.Column("notes", sa.Text()), sa.Column("custom_fields", sa.JSON), sa.Column("version", sa.Integer, nullable=False, server_default="1"), sa.Column("updated_by_person_id", identity_id), 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.ForeignKeyConstraint(["resource_id"], ["cmdb_cloud_resources.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["business_owner_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["technical_owner_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["updated_by_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.UniqueConstraint("resource_id", name="uq_cmdb_resource_governance_resource"), ) op.create_index("ix_cmdb_resource_governance_risk", "cmdb_resource_governance", ["risk_level", "governance_status"]) op.create_table( "cmdb_resource_governance_rules", sa.Column("id", integration_id, primary_key=True), sa.Column("rule_key", sa.String(96), nullable=False), sa.Column("name", sa.String(192), nullable=False), sa.Column("description", sa.Text()), sa.Column("category", sa.String(64), nullable=False), sa.Column("severity", sa.String(16), nullable=False), sa.Column("evaluator", sa.String(64), nullable=False), sa.Column("applies_to", sa.JSON), sa.Column("parameters", sa.JSON), sa.Column("recommendation", sa.Text()), sa.Column("enabled", sa.Boolean, nullable=False, server_default=sa.true()), sa.Column("built_in", sa.Boolean, nullable=False, server_default=sa.false()), sa.Column("version", sa.Integer, nullable=False, server_default="1"), sa.Column("created_by_person_id", identity_id), sa.Column("updated_by_person_id", identity_id), 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.ForeignKeyConstraint(["created_by_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["updated_by_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.UniqueConstraint("rule_key", name="uq_cmdb_resource_governance_rule_key"), ) op.create_index("ix_cmdb_resource_governance_rules_enabled", "cmdb_resource_governance_rules", ["enabled", "severity"]) op.create_table( "cmdb_resource_findings", sa.Column("id", integration_id, primary_key=True), sa.Column("resource_id", integration_id, nullable=False), sa.Column("rule_id", integration_id, nullable=False), sa.Column("fingerprint", sa.String(64), nullable=False), sa.Column("severity", sa.String(16), nullable=False), sa.Column("status", sa.String(24), nullable=False, server_default="open"), sa.Column("title", sa.String(255), nullable=False), sa.Column("description", sa.Text()), sa.Column("evidence", sa.JSON), sa.Column("recommendation", sa.Text()), sa.Column("assigned_to_person_id", identity_id), sa.Column("due_at", sa.DateTime(timezone=True)), sa.Column("first_seen_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("last_seen_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("acknowledged_at", sa.DateTime(timezone=True)), sa.Column("acknowledged_by_person_id", identity_id), sa.Column("resolved_at", sa.DateTime(timezone=True)), sa.Column("resolution_note", sa.Text()), 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.ForeignKeyConstraint(["resource_id"], ["cmdb_cloud_resources.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["rule_id"], ["cmdb_resource_governance_rules.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["assigned_to_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["acknowledged_by_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.UniqueConstraint("fingerprint", name="uq_cmdb_resource_finding_fingerprint"), ) op.create_index("ix_cmdb_resource_findings_status_severity", "cmdb_resource_findings", ["status", "severity"]) op.create_index("ix_cmdb_resource_findings_resource_status", "cmdb_resource_findings", ["resource_id", "status"]) op.create_index("ix_cmdb_resource_findings_rule_seen", "cmdb_resource_findings", ["rule_id", "last_seen_at"]) op.create_table( "cmdb_cloud_resource_daily_rollups", sa.Column("id", integration_id, primary_key=True), sa.Column("snapshot_date", sa.Date, nullable=False), sa.Column("dimension_key", sa.String(64), nullable=False), sa.Column("provider", sa.String(64)), sa.Column("category", sa.String(64)), sa.Column("resource_type", sa.String(192)), sa.Column("scope_external_id", sa.String(256)), sa.Column("total_count", sa.Integer, nullable=False, server_default="0"), sa.Column("active_count", sa.Integer, nullable=False, server_default="0"), sa.Column("missing_count", sa.Integer, nullable=False, server_default="0"), sa.Column("unowned_count", sa.Integer, nullable=False, server_default="0"), sa.Column("restricted_count", sa.Integer, nullable=False, server_default="0"), sa.Column("high_risk_count", sa.Integer, nullable=False, server_default="0"), sa.Column("open_finding_count", sa.Integer, nullable=False, server_default="0"), 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("snapshot_date", "dimension_key", name="uq_cmdb_resource_daily_dimension"), ) op.create_index("ix_cmdb_resource_daily_provider_date", "cmdb_cloud_resource_daily_rollups", ["provider", "snapshot_date"]) op.create_index("ix_cmdb_resource_daily_category_date", "cmdb_cloud_resource_daily_rollups", ["category", "snapshot_date"]) bind = op.get_bind() for rule_key, name, category, severity, evaluator, recommendation in _RULES: exists = bind.execute( sa.text("SELECT 1 FROM cmdb_resource_governance_rules WHERE rule_key=:key LIMIT 1"), {"key": rule_key}, ).scalar() if not exists: bind.execute( sa.text( """INSERT INTO cmdb_resource_governance_rules (id, rule_key, name, category, severity, evaluator, recommendation, enabled, built_in, version, created_at, updated_at) VALUES (:id, :key, :name, :category, :severity, :evaluator, :recommendation, 1, 1, 1, UTC_TIMESTAMP(), UTC_TIMESTAMP())""" ), { "id": str(uuid.uuid4()), "key": rule_key, "name": name, "category": category, "severity": severity, "evaluator": evaluator, "recommendation": recommendation, }, ) job_exists = bind.execute( sa.text("SELECT 1 FROM scheduled_jobs WHERE name=:name LIMIT 1"), {"name": _JOB}, ).scalar() if not job_exists: bind.execute( sa.text( """INSERT INTO scheduled_jobs (id, name, display_name, description, handler_key, trigger_type, interval_seconds, cron_expr, timezone, enabled, config, run_count, failure_count, created_at, updated_at) VALUES (:id, :name, '云资源治理规则评估', '评估统一云资源目录治理规则并生成长期每日聚合；只读云资源。', :name, 'cron', NULL, '43 1 * * *', 'Asia/Shanghai', 1, :config, 0, 0, UTC_TIMESTAMP(), UTC_TIMESTAMP())""" ), {"id": str(uuid.uuid4()), "name": _JOB, "config": json.dumps({})}, ) def downgrade() -> None: bind = op.get_bind() bind.execute(sa.text("DELETE FROM scheduled_jobs WHERE name=:name"), {"name": _JOB}) op.drop_table("cmdb_cloud_resource_daily_rollups") op.drop_table("cmdb_resource_findings") op.drop_table("cmdb_resource_governance_rules") op.drop_table("cmdb_resource_governance")