"""Seed Zoom integration + software catalog + scheduled job + settings. Revision ID: 051 Revises: 050 Create Date: 2026-04-23 End-to-end seed so a fresh Helix instance (local or production) ships with Zoom wired up the moment the admin supplies the S2S OAuth credentials. What lands: * ``software_license_plans.over_quota_policy`` column added (defaults to ``reject`` — the existing behaviour — so no other plan is affected). ``evict_lru`` is the new value Zoom uses. * One ``integrations`` row (``slug='zoom-primary'``, ``enabled=False``, empty secrets). Admin fills ``account_id`` / ``client_id`` / ``client_secret`` via ``/admin/integrations`` or the ``scripts/seed_zoom_integration.py`` helper (which can also be run with ``HELIX_ZOOM_*`` env vars for automation). * One ``asset_software_catalog`` row ``name='Zoom'``, ``offboarding_policy='sso_cascade'`` per user decision 2026-04-23: leaver's Google SSO revocation is enough, the seat itself is reclaimed lazily via ``zoom.seat_sync`` + LRU eviction when a new request comes in. * One ``software_license_plans`` row ``name='Zoom Licensed'`` with ``over_quota_policy='evict_lru'`` and ``max_seats=NULL`` (the scheduled sync auto-populates this from Zoom's subscription API). * One ``software_connectors`` binding pointing the Zoom catalog at the Zoom integration with ``plan_type='licensed'`` and ``action_on_offboard='disassociate'``. * One ``scheduled_jobs`` row ``zoom.seat_sync`` on a daily 03:00 UTC cron (≈ 11:00 CST / 20:00 PT — off-peak in every TZ we staff). Disabled on first boot so the admin can plug credentials in before it tries to call Zoom. * ``platform_settings`` defaults: empty eviction whitelist + role-based fallback list + an ``email_template.zoom.seat_evicted`` the eviction coordinator renders when notifying the displaced user. Idempotent on the slug / software name / job name / setting key so re-running on an install that already has a Zoom catalog row (e.g. the admin created one manually before we shipped this) is a no-op. """ from __future__ import annotations import json import uuid import sqlalchemy as sa from alembic import op revision = "051" down_revision = "050" branch_labels = None depends_on = None _SOFTWARE_NAME = "Zoom" _PLAN_NAME = "Zoom Licensed" _INTEGRATION_SLUG = "zoom-primary" _JOB_NAME = "zoom.seat_sync" _EVICTION_EMAIL_TEMPLATE = { "subject": "[Helix] 您的 Zoom 付费坐席已被回收", "body_text": ( "Hi {person_name},\n\n" "由于团队 Zoom 付费坐席 (License) 已达上限,而您是当前所有付费" "用户中最久未登录的一位 (最近登录: {last_login_time}),系统已" "将您的 {plan_name} 坐席回收以分配给同事 {new_requester_email}。\n\n" "影响说明\n" "-----------\n" " · 您仍然可以使用 Zoom Basic (免费档) 主持最长 40 分钟的会议,\n" " 加入他人会议不受时长限制;\n" " · 会议历史、云录制、个人设置不会被删除,下次被分配坐席后即可继续使用;\n" " · 您的 Google SSO 登录方式不变。\n\n" "如何重新获得坐席\n" "-----------\n" " 1. 打开 {portal_url}/catalog/software\n" " 2. 选择软件「Zoom」并点击「申请」,注明用途\n" " 3. 审批通过后,系统会在下次有空位或再次触发回收时自动分配\n\n" "如有疑问请联系 IT 团队。\n\n" "— Helix IT 平台" ), } def upgrade() -> None: bind = op.get_bind() # ─── Schema change: over_quota_policy column ─────────── # Safe on existing rows: default 'reject' preserves today's # behaviour (request routed through check_plan_quota). # Skip silently if this migration re-runs after a manual add. cols = bind.execute( sa.text( "SELECT COLUMN_NAME FROM information_schema.COLUMNS " "WHERE TABLE_SCHEMA = DATABASE() " "AND TABLE_NAME = 'software_license_plans' " "AND COLUMN_NAME = 'over_quota_policy'" ) ).scalar() if not cols: op.add_column( "software_license_plans", sa.Column( "over_quota_policy", sa.String(length=16), nullable=False, server_default="reject", ), ) # ─── Seed: integration row ─────────────────────────────── integration_id = bind.execute( sa.text("SELECT id FROM integrations WHERE slug = :s"), {"s": _INTEGRATION_SLUG}, ).scalar() if integration_id is None: integration_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO integrations " "(id, kind, provider, name, slug, description, " " enabled, is_primary, " " config_public, secrets_encrypted, secrets_nonce) " "VALUES " "(:id, 'software_provisioning', 'zoom', :name, :slug, :desc, " " 0, 1, '{}', NULL, NULL)" ), { "id": integration_id, "name": "Zoom", "slug": _INTEGRATION_SLUG, "desc": ( "Zoom Meetings — Server-to-Server OAuth app。填 Account ID " "+ Client ID + Client Secret 后启用;启用后再把 " "zoom.seat_sync 定时任务打开即可开始坐席同步。" ), }, ) # ─── Seed: software catalog row ───────────────────────── software_id = bind.execute( sa.text( "SELECT id FROM asset_software_catalog WHERE name = :n LIMIT 1" ), {"n": _SOFTWARE_NAME}, ).scalar() if software_id is None: software_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO asset_software_catalog " "(id, name, asset_type, status, remark, sso_provider, " " offboarding_policy, offboarding_grace_days) " "VALUES " "(:id, :name, 'SaaS', 'active', :remark, 'google', " " 'sso_cascade', 0)" ), { "id": software_id, "name": _SOFTWARE_NAME, "remark": ( "Zoom Meetings。离职时走 sso_cascade (Google SSO 挂掉" "用户就进不去);付费坐席配额由 zoom.seat_sync 维护," "坐席不够时新申请会自动挤出最久未登录的付费用户。" ), }, ) # ─── Seed: license plan row ───────────────────────────── plan_id = bind.execute( sa.text( "SELECT id FROM software_license_plans " "WHERE software_id = :sid AND name = :n LIMIT 1" ), {"sid": software_id, "n": _PLAN_NAME}, ).scalar() if plan_id is None: plan_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO software_license_plans " "(id, software_id, name, price, billing_cycle, " " description, status, max_seats, over_quota_policy) " "VALUES " "(:id, :sid, :name, 0, 'monthly', :desc, 'active', " " NULL, 'evict_lru')" ), { "id": plan_id, "sid": software_id, "name": _PLAN_NAME, "desc": ( "Zoom Licensed (付费主持人坐席)。max_seats 由 " "zoom.seat_sync 从 Zoom 订阅自动回填;over_quota_policy=" "evict_lru 表示坐席已满时新审批通过后会挤掉最久未登录者。" ), }, ) else: # Plan exists (admin created manually before this migration); # make sure over_quota_policy is set so eviction still kicks in. bind.execute( sa.text( "UPDATE software_license_plans SET over_quota_policy='evict_lru' " "WHERE id = :id AND (over_quota_policy IS NULL " "OR over_quota_policy = '' OR over_quota_policy = 'reject')" ), {"id": plan_id}, ) # ─── Seed: software connector binding ─────────────────── connector_exists = bind.execute( sa.text( "SELECT 1 FROM software_connectors WHERE software_id = :sid LIMIT 1" ), {"sid": software_id}, ).scalar() if not connector_exists: bind.execute( sa.text( "INSERT INTO software_connectors " "(id, software_id, integration_id, binding_config, is_active) " "VALUES (:id, :sid, :iid, :cfg, 1)" ), { "id": str(uuid.uuid4()), "sid": software_id, "iid": integration_id, "cfg": json.dumps( {"plan_type": "licensed", "action_on_offboard": "disassociate"}, ensure_ascii=False, ), }, ) # ─── Seed: scheduled job ──────────────────────────────── job_exists = bind.execute( sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"), {"n": _JOB_NAME}, ).scalar() if not job_exists: bind.execute( sa.text( """ INSERT INTO scheduled_jobs (id, name, display_name, description, handler_key, trigger_type, interval_seconds, cron_expr, timezone, enabled, config, run_count, failure_count, created_at, updated_at) VALUES (UUID(), :name, :display_name, :description, :handler_key, 'cron', NULL, '0 3 * * *', 'UTC', 0, '{}', 0, 0, NOW(), NOW()) """ ), { "name": _JOB_NAME, "display_name": "Zoom · 坐席与订阅同步", "description": ( "每天 03:00 UTC 拉 Zoom 用户列表和订阅计划,镜像到 " "software_seat_allocations,同步 max_seats。接入 Zoom " "集成并填好 credentials 后,把该任务 enabled 置 1 即可启用。" ), "handler_key": _JOB_NAME, }, ) # ─── Seed: platform settings defaults ─────────────────── _seed_setting( bind, key="zoom.seat_eviction.whitelist_emails", value=[], description=( "Zoom 坐席不够时额外永远不会被挤的邮箱白名单 (优先级最高)。" "列表形式,例如 [\"ceo@pingcap.com\"]。空列表退回内置规则" "(protected_roles 对应的用户)。" ), ) _seed_setting( bind, key="zoom.seat_eviction.protected_roles", value=["global_admin", "asset_admin"], description=( "Zoom 坐席回收时永远保护的角色名列表 (OR 关系)。" "这里列出的角色下的活跃用户不会被自动挤掉坐席。" "默认 global_admin + asset_admin。" ), ) _seed_setting( bind, key="email_template.zoom.seat_evicted", value=_EVICTION_EMAIL_TEMPLATE, description=( "发给被回收 Zoom 坐席用户的邮件模板。可用变量: " "{person_name} {plan_name} {last_login_time} " "{new_requester_email} {portal_url}。" ), ) def downgrade() -> None: """Conservative downgrade — we don't drop data rows admins might have already customised. We only remove the scheduled job + settings keys (those are cheap to re-seed) and drop the new column if it's still at its default state. """ bind = op.get_bind() bind.execute( sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": _JOB_NAME}, ) for key in ( "zoom.seat_eviction.whitelist_emails", "zoom.seat_eviction.protected_roles", "email_template.zoom.seat_evicted", ): bind.execute( sa.text("DELETE FROM platform_settings WHERE `key` = :k"), {"k": key}, ) # Only drop over_quota_policy if no plan uses a non-default value. in_use = bind.execute( sa.text( "SELECT 1 FROM software_license_plans " "WHERE over_quota_policy IS NOT NULL " "AND over_quota_policy != 'reject' LIMIT 1" ) ).scalar() if not in_use: with op.batch_alter_table("software_license_plans") as batch: batch.drop_column("over_quota_policy") def _seed_setting(bind, *, key: str, value, description: str) -> None: exists = bind.execute( sa.text("SELECT 1 FROM platform_settings WHERE `key` = :k"), {"k": key}, ).scalar() if exists: return bind.execute( sa.text( "INSERT INTO platform_settings (`key`, value, description) " "VALUES (:k, :v, :d)" ), { "k": key, "v": json.dumps(value, ensure_ascii=False), "d": description, }, )