"""api_key_assignments — revoke metadata columns + ``pending_release`` status. Revision ID: 189 Revises: 188 Create Date: 2026-05-19 Hotfix re-anchor (2026-05-20): originally landed in PR #408 as ``revision = "187"`` chained off the now-deleted merge node 186. PR #409 (alembic renumber) and PR #408 collided on revision ids 186/187/188; the post-merge ECR image was lost to a concurrency cancel, so production never received the broken chain. This file is renumbered to ``revision = "189"`` chained off the upstream office-held tip ``188_backfill_asset_holder_kind`` so the chain is linear again. No schema change vs the PR #408 version. Why this exists --------------- Round-38 (PR1 of the API-Key lifecycle closure): admin revoke already exists at ``POST /assignments/{id}/revoke`` and lands the row in ``status='revoked'`` with the cloud-side IAM user / DashScope key / AOAI account torn down. What's missing is *forensic metadata on the row itself* — who revoked, when, why, and through which surface. Today the revocation actor + reason live only in ``audit_logs.detail`` JSON, which means: * The admin table can't show "revoked by X on YYYY-MM-DD because Y" inline; every drill-in is an audit log query. * "Show me every key revoked because of offboarding last quarter" is a JSON predicate scan over audit_logs rather than a normal index range scan over the assignment table. * Employees who self-release their own keys (new flow) and the eventual offboarding cascade (PR2) need to write distinct ``actor_role`` values so reports can separate "user no longer needs it" from "IT pulled the plug for compliance" from "system swept on leaver". This migration adds five columns + the new ``pending_release`` status the self-service release flow parks in while waiting for the leader's Feishu approval. Cloud teardown remains gated to the actual approved transition — landing in ``pending_release`` does NOT touch AWS / Aliyun / Azure. Column reference ---------------- ``revoked_at`` : DateTime, nullable UTC timestamp of the status flip into ``revoked``. ``updated_at`` is not sufficient because future edits (e.g. clearing a stale ``notify_recipients`` list on an already-revoked row) bump it; this column is set exactly once and never touched again. ``revoked_by`` : UUID FK to ``identity_persons.id``, nullable The actor. For ``admin`` revocations: the admin's person id. For ``self_with_approval`` releases: the assignee's own id (NOT the leader's — the leader only authorised, the employee initiated the operation). For ``system_offboarding`` (PR2): NULL. ``revoke_reason_code`` : VARCHAR(32), nullable Enum-flavoured slug. Values today: * ``no_longer_needed`` (self) * ``project_ended`` (self) * ``wrong_provider`` (self) * ``other`` (self — requires ``revoke_reason_note``) * ``admin_revoke`` (admin — superset bucket; free-text note carries the specific reason) * ``leaked`` (admin, planned) * ``offboarding`` (system, PR2) Stored as a string column not an ENUM so adding values in a later migration is a code-only change (TiDB ALTER TABLE for ENUM is heavy). ``revoke_reason_note`` : TEXT, nullable Free-text supplement. ``other`` requires it (validated at the schema layer); other codes treat it as optional context. ``revoke_actor_role`` : VARCHAR(24), nullable Disambiguator: ``admin`` | ``self_with_approval`` | ``system_offboarding``. Drives the admin-table source-of-revocation tag + lets reports filter by surface without joining audit_logs. Backfill -------- For rows already in ``status='revoked'`` at migration time we backfill: * ``revoked_at = updated_at`` (best-available proxy — the audit row has the true timestamp but the row's updated_at is the closest cheap approximation and is monotonically <= the audit row by construction) * ``revoke_actor_role = 'admin'`` (only admin revoke existed before this PR; the self-service surface didn't ship) * ``revoked_by`` / ``revoke_reason_code`` / ``revoke_reason_note`` stay NULL — we deliberately do NOT join audit_logs.detail to extract a backfilled actor or reason, because: 1. audit_logs.detail is a JSON blob whose schema can drift between releases — a backfill that misparses one prod row silently writes wrong data. 2. The truth still lives in audit_logs; the row-level columns are a denormalised cache for new writes. Old data stays queryable via the audit log; new data lives in both. Idempotency ----------- Column existence is probed via ``information_schema.COLUMNS`` (same shape as migrations 165 / 178 / 180 / 181) so a partial replay is safe. """ from __future__ import annotations import sqlalchemy as sa from alembic import op revision = "189" down_revision = "188" branch_labels = None depends_on = None def _column_exists(bind, table: str, column: 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 LIMIT 1" ), {"t": table, "c": column}, ).scalar() is not None def _index_exists(bind, table: str, index: 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 LIMIT 1" ), {"t": table, "i": index}, ).scalar() is not None def upgrade() -> None: bind = op.get_bind() if not _column_exists(bind, "api_key_assignments", "revoked_at"): op.add_column( "api_key_assignments", sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True), ) if not _column_exists(bind, "api_key_assignments", "revoked_by"): op.add_column( "api_key_assignments", sa.Column("revoked_by", sa.CHAR(36), nullable=True), ) if not _column_exists(bind, "api_key_assignments", "revoke_reason_code"): op.add_column( "api_key_assignments", sa.Column("revoke_reason_code", sa.String(32), nullable=True), ) if not _column_exists(bind, "api_key_assignments", "revoke_reason_note"): op.add_column( "api_key_assignments", sa.Column("revoke_reason_note", sa.Text(), nullable=True), ) if not _column_exists(bind, "api_key_assignments", "revoke_actor_role"): op.add_column( "api_key_assignments", sa.Column("revoke_actor_role", sa.String(24), nullable=True), ) # Best-effort backfill — only rows already in ``revoked`` get a # synthetic ``revoked_at`` (= updated_at) and a default actor_role # tag of ``admin`` (only path that existed pre-this-PR). We don't # touch revoked_by / reason_code / reason_note — see module docstring # for why. bind.execute( sa.text( """ UPDATE api_key_assignments SET revoked_at = COALESCE(revoked_at, updated_at), revoke_actor_role = COALESCE(revoke_actor_role, 'admin') WHERE status = 'revoked' """ ) ) # Index used by the admin "revocations last 90d" report + by the # planned governance dashboard. Composite (actor_role, revoked_at) # so "show me self-releases this week" is a single-segment scan, # not a full-status scan plus a date filter. if not _index_exists( bind, "api_key_assignments", "ix_apikey_assignments_revoke_actor_at", ): op.create_index( "ix_apikey_assignments_revoke_actor_at", "api_key_assignments", ["revoke_actor_role", "revoked_at"], ) def downgrade() -> None: bind = op.get_bind() if _index_exists( bind, "api_key_assignments", "ix_apikey_assignments_revoke_actor_at", ): op.drop_index( "ix_apikey_assignments_revoke_actor_at", table_name="api_key_assignments", ) for col in ( "revoke_actor_role", "revoke_reason_note", "revoke_reason_code", "revoked_by", "revoked_at", ): if _column_exists(bind, "api_key_assignments", col): op.drop_column("api_key_assignments", col)