"""Cloud grant renewal requests — owner-approved renewal + revival. Revision ID: 437 Revises: 436 Create Date: 2026-07-29 Why this exists --------------- Renewal was self-service: the holder pressed 「一键续期」 (or the button on the expiry reminder card) and the grant's clock reset on the spot, with no second party involved. Two consequences, both measured in production on 2026-07-29: * "90-day expiry recycling" was unenforceable. Of 763 renewals in three months, every single one was executed by the holder alone. * Recycling could be undone unilaterally. ``_renew_grant_locked`` accepted any row in status ``expired`` with no age limit, so 839 already-recycled grants across 135 people (oldest recycled 32 days earlier) could be revived by their former holder with one click and no approval. Recycling therefore only downgraded re-acquisition from "request + approval" to "press a button". Renewal is now a request the scope owner decides on, and reviving a recycled grant goes through the same gate. TTL above ``GRANT_TTL_OWNER_APPROVABLE_DAYS`` additionally needs a cloud admin's second signature (``pending_admin``). Shape notes ----------- ``open_for_grant_id`` mirrors ``grant_id`` while the request is open and is NULLed on every terminal status. The UNIQUE index over it is what enforces "at most one open request per grant" in the database rather than in a service-layer read-then-write, which two clicks racing on the reminder card would defeat. MySQL and TiDB both permit repeated NULLs in a unique index, so closed rows accumulate freely. FK columns are typed via ``id_type_for`` and the table takes ``table_kw_for`` so the charset/collation matches the parent tables — without it TiDB rejects the FK with a collation mismatch (see the migration FK checklist in DECISIONS). This table MIXES parents: the table default comes from ``cmdb_cloud_access_grants`` (``utf8mb4_bin`` on prod TiDB) while four columns FK to ``identity_persons`` (``utf8mb4_unicode_ci``). A table default can only satisfy one of them, so ``person_id`` inherited the wrong collation and the first prod deploy died on ``fk_3`` (error 3780) — invisible locally, where both parents share one collation. ``id_type_for`` now returns the parent's collation with the type, which is what makes the mixed-parent case work at all; do not replace those types with bare ``sa.CHAR(36)``. Idempotent: ``CREATE TABLE IF NOT EXISTS`` via ``_table_exists``. """ from __future__ import annotations import sqlalchemy as sa from alembic import op from app.core.migration_helpers import id_type_for, table_kw_for revision = "437" down_revision = "436" branch_labels = None depends_on = None TABLE = "cmdb_cloud_grant_renewal_requests" 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() if _table_exists(bind, TABLE): return person_t = id_type_for("identity_persons") grant_t = id_type_for("cmdb_cloud_access_grants") scope_t = id_type_for("cmdb_cloud_scopes") kw = table_kw_for("cmdb_cloud_access_grants") op.create_table( TABLE, # Own PK, not an FK — so it deliberately does NOT borrow a # parent's type/collation. It inherits the table default (binary, # from ``kw``), which is what a UUID string wants: exact matching, # no case folding. sa.Column("id", sa.CHAR(36), primary_key=True), sa.Column( "grant_id", grant_t, sa.ForeignKey("cmdb_cloud_access_grants.id", ondelete="CASCADE"), nullable=False, ), # NULL once terminal — see module docstring. sa.Column("open_for_grant_id", grant_t, nullable=True), sa.Column( "scope_id", scope_t, sa.ForeignKey("cmdb_cloud_scopes.id", ondelete="CASCADE"), nullable=False, ), sa.Column( "person_id", person_t, sa.ForeignKey("identity_persons.id"), nullable=False, ), sa.Column( "requested_by_person_id", person_t, sa.ForeignKey("identity_persons.id"), nullable=False, ), sa.Column("requested_ttl_days", sa.Integer(), nullable=False), sa.Column("reason", sa.String(1000), nullable=False), sa.Column( "is_revival", sa.Boolean(), nullable=False, server_default=sa.text("0"), ), sa.Column( "status", sa.String(32), nullable=False, server_default="pending_owner", ), sa.Column("request_group_id", person_t, nullable=True), sa.Column("lapse_at", sa.DateTime(timezone=True), nullable=False), # Idempotency marker for the "about to lapse" nudge to the # approver — without it the daily sweep re-sends every morning of # the final window. sa.Column( "owner_reminded_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column( "owner_decided_by_person_id", person_t, sa.ForeignKey("identity_persons.id", ondelete="SET NULL"), nullable=True, ), sa.Column( "owner_decided_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column( "admin_decided_by_person_id", person_t, sa.ForeignKey("identity_persons.id", ondelete="SET NULL"), nullable=True, ), sa.Column( "admin_decided_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column("decision_note", sa.String(1000), nullable=True), sa.Column( "applied_expires_at", sa.DateTime(timezone=True), 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( "open_for_grant_id", name="uq_cmdb_grant_renewal_open_per_grant", ), **kw, ) op.create_index( "ix_cmdb_grant_renewal_scope_status", TABLE, ["scope_id", "status"], ) op.create_index( "ix_cmdb_grant_renewal_person_status", TABLE, ["person_id", "status"], ) op.create_index( "ix_cmdb_grant_renewal_lapse", TABLE, ["status", "lapse_at"], ) op.create_index( "ix_cmdb_grant_renewal_group", TABLE, ["request_group_id"], ) def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, TABLE): op.drop_table(TABLE)