"""Software seat self-service cancellation at end of period. Revision ID: 096 Revises: 095 Create Date: 2026-05-01 ═══════════════════════════════════════════════════════════════════════ What this migration adds ═══════════════════════════════════════════════════════════════════════ Schema (``software_seat_allocations``) -------------------------------------- Four nullable columns capture an employee-initiated "cancel at next billing boundary" intent without disturbing the existing immediate- revoke path: * ``cancel_at_period_end`` DATETIME(6) — when set, the cron job ``software_seat.auto_revoke_pending_cancels`` picks the row up at or after this instant and runs the standard revoke. NULL means no pending cancellation. * ``cancel_requested_at`` DATETIME(6) — when the request was made (audit trail, also displayed in the UI as "申请于 …"). * ``cancel_requested_by`` CHAR(36) — person.id who requested it (employee themselves or an admin-on-their-behalf). The cron logs the original requester as the audit actor, NOT the system actor. * ``cancel_reason`` TEXT — optional free-text reason the employee enters at request time. Index on ``cancel_at_period_end`` keeps the cron's nightly sweep cheap: the query is ``WHERE cancel_at_period_end <= NOW() AND status='active'`` and the index narrows it from a full scan to just the rows due now. Scheduled job (``scheduled_jobs``) ---------------------------------- * ``software_seat.auto_revoke_pending_cancels`` cron ``5 0 1 * *`` (UTC) — fires at 00:05 on the first day of every calendar month. Picks up every active allocation whose ``cancel_at_period_end <= NOW()`` and runs the existing revoke path (sets ``status='revoked'``, ``revoked_at=now()``, fires the connector if any). Each revoke writes: - audit log row (``software_seat.auto_revoke_self_service``) keyed to the original ``cancel_requested_by`` so the trail says "Employee X cancelled, system revoked at month boundary". - notification outbox row to the employee (cancellation confirmed) and to the IT software owner (a paid seat freed up). Idempotent: re-running the same tick is safe — already-revoked rows are filtered out by the ``status='active'`` clause. ═══════════════════════════════════════════════════════════════════════ Idempotency ═══════════════════════════════════════════════════════════════════════ Schema add: every column is added with a column-existence guard so a re-run of ``alembic upgrade head`` is a no-op. The index is similarly guarded by checking ``information_schema.STATISTICS``. Scheduled job: ``INSERT INTO scheduled_jobs`` is preceded by an existence check on ``name``. """ from __future__ import annotations import json import logging import uuid import sqlalchemy as sa from alembic import op logger = logging.getLogger(__name__) revision = "096" down_revision = "095" branch_labels = None depends_on = None _TABLE = "software_seat_allocations" _INDEX = "ix_software_seat_allocations_cancel_at_period_end" _JOB_NAME = "software_seat.auto_revoke_pending_cancels" # New columns we're adding. Tuple form so the existence-guard loop and # the downgrade DROP loop stay symmetrical. _NEW_COLUMNS: tuple[tuple[str, str], ...] = ( ("cancel_at_period_end", "DATETIME(6) NULL"), ("cancel_requested_at", "DATETIME(6) NULL"), ("cancel_requested_by", "CHAR(36) NULL"), ("cancel_reason", "TEXT NULL"), ) 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 _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" ), {"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 _table_exists(bind, _TABLE): logger.warning( "%s table missing — migration is a no-op (fresh DB before " "migration 019?)", _TABLE, ) return for col_name, col_def in _NEW_COLUMNS: if _column_exists(bind, _TABLE, col_name): continue bind.execute(sa.text( f"ALTER TABLE {_TABLE} ADD COLUMN {col_name} {col_def}" )) if not _index_exists(bind, _TABLE, _INDEX): bind.execute(sa.text( f"CREATE INDEX {_INDEX} ON {_TABLE} (cancel_at_period_end)" )) if _table_exists(bind, "scheduled_jobs"): exists = bind.execute( sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"), {"n": _JOB_NAME}, ).scalar() if not exists: bind.execute( sa.text( "INSERT INTO scheduled_jobs " "(id, name, display_name, description, " " handler_key, trigger_type, cron_expr, " " timezone, enabled, config, " " run_count, failure_count, " " created_at, updated_at) " "VALUES (:id, :n, :dn, :d, :hk, 'cron', :ce, " " 'UTC', 1, :cfg, 0, 0, NOW(), NOW())" ), { "id": str(uuid.uuid4()), "n": _JOB_NAME, "dn": "Software seat — auto-revoke pending self-cancellations", "d": ( "Sweep every active software seat allocation whose " "cancel_at_period_end is in the past and run the " "standard revoke. Fires at 00:05 UTC on the first " "day of every calendar month. Each revoke writes " "an audit row (actor = original requester) and " "notifies the employee + the software owner. " "Idempotent — re-running a tick is a no-op for " "rows already revoked." ), "hk": _JOB_NAME, "ce": "5 0 1 * *", "cfg": json.dumps({}), }, ) def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, "scheduled_jobs"): bind.execute( sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": _JOB_NAME}, ) if not _table_exists(bind, _TABLE): return if _index_exists(bind, _TABLE, _INDEX): bind.execute(sa.text(f"DROP INDEX {_INDEX} ON {_TABLE}")) for col_name, _ in reversed(_NEW_COLUMNS): if _column_exists(bind, _TABLE, col_name): bind.execute(sa.text( f"ALTER TABLE {_TABLE} DROP COLUMN {col_name}" ))