"""Role curation, request groups, scope requests, default budgets. Revision ID: 068 Revises: 067 Create Date: 2026-04-26 Three changes that together make the cloud-access-request flow production-grade for an end user who isn't a cloud administrator: * ``cmdb_cloud_roles.is_promoted`` (BOOL, default FALSE) — the role-catalog dropdown surfaces ``is_promoted=TRUE`` rows by default (admin-curated "predefined" entries + every customer-defined / custom role). Built-in vendor roles a curator hasn't promoted (e.g. the ~380 Azure built-in RBAC roles, the ~250 Aliyun system policies, the ~200 Tencent presets) are still synced + persisted but only visible behind an "include all built-ins" toggle. This avoids the Azure-39-builtins / Aliyun-200+ "选择困难" problem the request modal had pre-068. Defaults to TRUE for existing rows: the curated catalog at the time of 068 is small enough that promoting all of them is the right starting point. ``cmdb.roles_sync`` writes the per-row truth on the next run. * ``cmdb_cloud_access_grants.request_group_id`` (VARCHAR(36)) — the request modal now lets an employee pick a primary role + 0..N extra roles in one submission. Backend explodes that into N+1 grant rows sharing one ``request_group_id``. Approval / rejection / revoke act on the whole group atomically — picking "Reader + Storage Blob Data Reader" is one approval decision, not two. Indexed (``request_group_id``) so the group lookup on approve/reject is an O(group-size) seek instead of a scan. * ``cmdb_cloud_scope_requests`` — new table backing the self-service "申请新子账号 / 订阅" flow. Employee submits provider + name + description + monthly budget; once Feishu + scope-owner approval is done, a worker calls the driver's ``create_scope`` (on supported providers — AWS Organizations today; Azure / GCP / Aliyun raise ``CapabilityNotSupported`` until those are productionized — see ``docs/ops/cmdb-ops.md``), inserts a ``cmdb_cloud_scopes`` row with the requester as owner, applies the platform-default alert rules + budget, and grants the requester an "owner-equivalent" role. If the cloud-side create fails the request stays in ``failed`` so an admin can retry or dismiss. Status state machine (read top-to-bottom, never skip): pending → feishu_sent → feishu_approved → provisioning → provisioned (terminal, ``created_scope_id`` populated) or pending → rejected / withdrawn (terminal) provisioning → failed (terminal-but-retryable; keeps the attempt's error in ``failure_reason``) Idempotent. Downgrade drops the new column / table; the existing scopes / grants rows are untouched. """ from __future__ import annotations import sqlalchemy as sa from alembic import op revision = "068" down_revision = "067" branch_labels = None depends_on = None def _column_exists(bind, table: str, col: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.COLUMNS " "WHERE TABLE_SCHEMA = DATABASE() " "AND TABLE_NAME = :t AND COLUMN_NAME = :c" ), {"t": table, "c": col}, ).scalar() is not None def _index_exists(bind, table: str, idx: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.STATISTICS " "WHERE TABLE_SCHEMA = DATABASE() " "AND TABLE_NAME = :t AND INDEX_NAME = :i" ), {"t": table, "i": idx}, ).scalar() is not None def _table_exists(bind, table: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.TABLES " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t" ), {"t": table}, ).scalar() is not None def upgrade() -> None: bind = op.get_bind() # ── cmdb_cloud_roles.is_promoted ────────────────────────────── if not _column_exists(bind, "cmdb_cloud_roles", "is_promoted"): op.add_column( "cmdb_cloud_roles", sa.Column( "is_promoted", sa.Boolean(), nullable=False, server_default=sa.text("1"), ), ) # ``server_default`` keeps the migration safe on a hot table; # the next ``cmdb.roles_sync`` run will overwrite the per-row # truth based on the driver's curated-vs-builtin classification. if not _index_exists(bind, "cmdb_cloud_roles", "ix_cmdb_cloud_roles_promoted"): op.create_index( "ix_cmdb_cloud_roles_promoted", "cmdb_cloud_roles", ["integration_id", "scope_id", "is_promoted"], ) # ── cmdb_cloud_access_grants.request_group_id ───────────────── if not _column_exists( bind, "cmdb_cloud_access_grants", "request_group_id", ): op.add_column( "cmdb_cloud_access_grants", sa.Column("request_group_id", sa.String(36), nullable=True), ) if not _index_exists( bind, "cmdb_cloud_access_grants", "ix_cmdb_cloud_grants_request_group", ): op.create_index( "ix_cmdb_cloud_grants_request_group", "cmdb_cloud_access_grants", ["request_group_id"], ) # ── cmdb_cloud_scope_requests ───────────────────────────────── # # FK columns are declared as plain ``String(36)`` here and the # actual FOREIGN KEY constraints are added separately in a # try / except block below — same pattern as migration 052. # # The original cut of this migration declared the FKs inline in # ``create_table``. On TiDB Cloud (prod) that produced # ``error 3780: Referencing column 'requester_person_id' and # referenced column 'id' in foreign key constraint 'fk_1' are # incompatible`` because SQLAlchemy's ``Uuid`` type compiles to # CHAR(32) on the parent table while the inline declaration here # is VARCHAR(36); TiDB rejects the type mismatch where MySQL 8.0 # silently coerces. Splitting the FK out + wrapping in try/except # mirrors what 052 already does for cmdb_cloud_scopes / # cmdb_cloud_access_grants and lets the table create successfully # whether or not the engine accepts the FK. if not _table_exists(bind, "cmdb_cloud_scope_requests"): op.create_table( "cmdb_cloud_scope_requests", sa.Column("id", sa.String(36), primary_key=True), sa.Column( "requester_person_id", sa.String(36), nullable=False, ), sa.Column( "integration_id", sa.String(36), nullable=False, ), sa.Column("provider", sa.String(32), nullable=False), sa.Column("name", sa.String(255), nullable=False), sa.Column("description", sa.Text, nullable=True), sa.Column( "monthly_budget_usd", sa.Numeric(14, 2), nullable=False, ), sa.Column( "status", sa.String(32), nullable=False, server_default=sa.text("'pending'"), ), sa.Column( "feishu_instance_code", sa.String(64), nullable=True, ), sa.Column( "approver_person_id", sa.String(36), nullable=True, ), sa.Column("approved_at", sa.DateTime(timezone=True)), sa.Column("rejected_at", sa.DateTime(timezone=True)), sa.Column("rejection_reason", sa.String(500), nullable=True), sa.Column( "created_scope_id", sa.String(36), nullable=True, ), sa.Column("provisioned_at", sa.DateTime(timezone=True)), sa.Column("failure_reason", sa.String(1000), nullable=True), sa.Column("metadata_json", sa.JSON, nullable=True), 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, ), sa.Index( "ix_cmdb_scope_requests_requester", "requester_person_id", "status", ), sa.Index( "ix_cmdb_scope_requests_integration_status", "integration_id", "status", ), ) # Foreign keys — declared separately so a TiDB type-mismatch error # on any one of them doesn't leave us with no table at all. Each # ``create_foreign_key`` is wrapped so the migration can proceed # in environments that reject the FK; the ORM still works without # the DB-side constraint (the integrity checks live in the service # layer). for fk_spec in [ ( "fk_cmdb_scope_requests_requester", "requester_person_id", "identity_persons", "id", None, ), ( "fk_cmdb_scope_requests_integration", "integration_id", "integrations", "id", None, ), ( "fk_cmdb_scope_requests_approver", "approver_person_id", "identity_persons", "id", "SET NULL", ), ( "fk_cmdb_scope_requests_scope", "created_scope_id", "cmdb_cloud_scopes", "id", "SET NULL", ), ]: try: op.create_foreign_key( fk_spec[0], "cmdb_cloud_scope_requests", fk_spec[2], [fk_spec[1]], [fk_spec[3]], ondelete=fk_spec[4], ) except Exception: # noqa: BLE001 # Already exists OR engine rejects the FK on type # grounds — both paths are survivable; the table itself # is the load-bearing change. Logged here for forensics. pass def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, "cmdb_cloud_scope_requests"): op.drop_table("cmdb_cloud_scope_requests") if _index_exists( bind, "cmdb_cloud_access_grants", "ix_cmdb_cloud_grants_request_group", ): op.drop_index( "ix_cmdb_cloud_grants_request_group", table_name="cmdb_cloud_access_grants", ) if _column_exists( bind, "cmdb_cloud_access_grants", "request_group_id", ): op.drop_column("cmdb_cloud_access_grants", "request_group_id") if _index_exists( bind, "cmdb_cloud_roles", "ix_cmdb_cloud_roles_promoted", ): op.drop_index( "ix_cmdb_cloud_roles_promoted", table_name="cmdb_cloud_roles", ) if _column_exists(bind, "cmdb_cloud_roles", "is_promoted"): op.drop_column("cmdb_cloud_roles", "is_promoted")