"""Idle software-seat reclaim — schema, scheduled job, default settings. Revision ID: 204 Revises: 203 Create Date: 2026-05-27 Ships the ``software_seat.idle_reclaim`` capability: a daily job that warns the holder of an Atlassian (Jira Software / Jira Service Management / Confluence) seat that has gone unused on the paid production sites for longer than the configured threshold, waits out a grace buffer with a self-service "keep my seat" opt-out, then reclaims the seat (removes the bound product group — it does NOT disable the Atlassian account, that's the offboarding line) if still idle. Three pieces, all idempotent: 1. Schema — three nullable DATETIME(6) columns on ``software_seat_allocations``: * ``idle_warned_at`` — start of the grace buffer. * ``idle_kept_until`` — self-service keep opt-out; while set and in the future the job skips the seat. Keeping clears idle_warned_at. * ``idle_manual_review_at`` — when the job punted to a human because removing the bound group alone would not fully revoke the product (multi-group holder). Dedups the admin notification. Index on ``idle_warned_at`` keeps the daily "seats currently in the buffer" sweep cheap. 2. Scheduled job — ``software_seat.idle_reclaim``, cron ``0 2 * * *`` (UTC 02:00 = Asia/Shanghai 10:00). Seeded **disabled** (enabled=0): the operator enables it after a one-time production-worker probe confirms the Atlassian Org Admin API ``product_access`` JSON shape matches the parser (CREDENTIAL_ENCRYPTION_KEY is production-only, so the live JSON can only be confirmed there). Warnings are user-facing and must not fire against an unverified shape. 3. Default platform settings (threshold / buffer / keep window / admin groups / production sites) so they're discoverable + editable in the admin settings UI. The handler also has hard-coded fallbacks so a deleted row degrades to the same defaults. """ from __future__ import annotations import json import logging import uuid import sqlalchemy as sa from alembic import op logger = logging.getLogger(__name__) revision = "204" down_revision = "203" branch_labels = None depends_on = None _TABLE = "software_seat_allocations" _INDEX = "ix_software_seat_allocations_idle_warned_at" _JOB_NAME = "software_seat.idle_reclaim" _NEW_COLUMNS: tuple[tuple[str, str], ...] = ( ("idle_warned_at", "DATETIME(6) NULL"), ("idle_kept_until", "DATETIME(6) NULL"), ("idle_manual_review_at", "DATETIME(6) NULL"), ) # (key, json-value, description) for the default platform settings. _DEFAULT_SETTINGS: tuple[tuple[str, object, str], ...] = ( ( "software_seat.idle_reclaim.idle_threshold_days", 90, "闲置坐席回收:连续未使用天数阈值,超过即进入警告流程。", ), ( "software_seat.idle_reclaim.buffer_days", 14, "闲置坐席回收:警告后的缓冲期天数,期满仍闲置且未保留则回收。", ), ( "software_seat.idle_reclaim.keep_days", 180, "闲置坐席回收:员工点『保留坐席』后跳过回收的天数。", ), ( "software_seat.idle_reclaim.admin_group_names", [ "jira-administrators", "site-admins", "confluence-administrators", "org-admins", ], "闲置坐席回收:这些 Atlassian 组的成员永不自动回收(管理员护栏)。", ), ( "software_seat.idle_reclaim.production_sites", { "jira-software": ["tidb.atlassian.net"], "jira-servicedesk": ["tidb.atlassian.net"], "confluence": ["tidb.atlassian.net", "pingcap-us.atlassian.net"], }, ( "闲置坐席回收:每个产品的『付费生产站点』白名单。只有这些站点的 " "last_active 算数,sandbox / 外部个人站点一律忽略。" ), ), ) 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", _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} (idle_warned_at)" )) 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', 0, " " :cfg, 0, 0, NOW(), NOW())" ), { "id": str(uuid.uuid4()), "n": _JOB_NAME, "dn": "软件坐席 — 闲置坐席自动回收 (Jira 三件套)", "d": ( "每天 UTC 02:00 扫描 Helix 跟踪的 Jira Software / " "JSM / Confluence active 坐席,交叉 Atlassian 生产站 " "last_active(每人每产品取生产站 MAX,排除 sandbox / " "外部站点)。idle_days>阈值(默认90)且 last_active 非空 → " "警告 + 缓冲(默认14天)+ 自助『保留坐席』;缓冲后仍闲置 → " "移除该产品组(不停账号)+ 通知 + 审计。护栏:管理员、" "离职员工、last_active 为空、多组授权一律不自动回收。" "默认 enabled=0 — 生产 worker 确认 Org Admin API " "product_access JSON 形状后再开启。" ), "hk": _JOB_NAME, "ce": "0 2 * * *", "cfg": json.dumps({}), }, ) if _table_exists(bind, "platform_settings"): for key, value, description in _DEFAULT_SETTINGS: present = bind.execute( sa.text( "SELECT 1 FROM platform_settings WHERE `key` = :k LIMIT 1" ), {"k": key}, ).scalar() if present: continue bind.execute( sa.text( "INSERT INTO platform_settings " "(`key`, value, description, created_at, updated_at) " "SELECT :k, CAST(:v AS JSON), :d, NOW(), NOW() FROM DUAL " "WHERE NOT EXISTS " "(SELECT 1 FROM platform_settings WHERE `key` = :k)" ), { "k": key, "v": json.dumps(value, ensure_ascii=False), "d": description, }, ) def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, "platform_settings"): for key, _value, _desc in _DEFAULT_SETTINGS: bind.execute( sa.text("DELETE FROM platform_settings WHERE `key` = :k"), {"k": key}, ) 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}" ))